The forum will be down for scheduled maintenance on Saturday, March 4 beginning at 10am EST. Actual downtime is unknown but may be several hours.

I would like to set the connection property string_rtruncation to off by default at the engine or database level, so I do not need to set it explicitly on every connection. Is there a way to do this? Thanks in advance.

asked 08 Mar '12, 08:23

Jim%20Miani's gravatar image

Jim Miani
2113412
accept rate: 0%


SET OPTION PUBLIC.string_rtruncation = 'OFF';

permanent link

answered 08 Mar '12, 08:33

Breck%20Carter's gravatar image

Breck Carter
26.8k422580827
accept rate: 20%

brilliant, thanks very much

(08 Mar '12, 08:40) Jim Miani

Hi

You could define a login procedure that sets this option. Sample code:

CREATE PROCEDURE LoginProcedure()

BEGIN

SET EXISTING OPTION string_rtruncation = off;

END;

GRANT EXECUTE ON DBA.LoginProcedure TO PUBLIC;

SET OPTION PUBLIC.login_procedure='DBA.LoginProcedure';

permanent link

answered 08 Mar '12, 08:39

rmgdc's gravatar image

rmgdc
16112
accept rate: 0%

Note, that a login procedure should usually call the default one - therefore one should add a call to sp_login_environment(), cf. the docs:

This custom procedure needs to call either sp_login_environment or detect when a TDS connection occurs (see the default sp_login_environment code) and call sp_tsql_environment directly. Failure to do so can break TDS-based connections.

Yes, I do know that currently, the default procedure is a NO-OP for non TDS connections:)

(08 Mar '12, 08:55) Volker Barth
Replies hidden

You're right. Thanks.

Even so the first answer was the right one. string_rtruncation is a global database option and there is no need for a login procedure.

(08 Mar '12, 09:03) rmgdc
Comment Text Removed

IMO that is bad advice. The statement "Failure to do so can break TDS-based connections." dates to ancient times, when TDS connections were ONLY used for legacy ASE-style applications that depended on the CHAINED option being set to 'OFF' which is what sp_tsql_environment does... surely you do not claim that all TDS connections should run in unchained mode, do you? Well, that's what the default does...

The folks responsible for each and every application should know exactly what they need from their login procedure. In most cases, that is "nothing", and the login_procedure option should be set to NULL.

(08 Mar '12, 09:08) Breck Carter
Comment Text Removed

I can't claim on the TDS connection part (as I don't use those). And I surely agree that one should know what the login procedure does.

Nevertheless, if there is a default (and that is sp_login_environment() as documented), one should also know about that default - and personally, I prefer to make use of a default if I don't have a good reason not to do so. (You know, "Watcom does the things...") - Well, currently the default for non TDS is a NO-OP, so until that does change in future versions, our discussion is rather philosophical:)

(08 Mar '12, 11:03) Volker Barth
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×8

question asked: 08 Mar '12, 08:23

question was seen: 1,896 times

last updated: 08 Mar '12, 11:03