set default value for string_rtruncation

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.

right-truncation-of-strin

asked 08 Mar '12, 08:23

Jim Miani
211●3●4●12
accept rate: 0%

flat view

2 Answers:

active answers oldest answers newest answers popular answers

3	SET OPTION PUBLIC.string_rtruncation = 'OFF'; permanent link answered 08 Mar '12, 08:33 Breck Carter 32.5k●539●724●1050 accept rate: 20% brilliant, thanks very much (08 Mar '12, 08:40) Jim Miani flat view

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
16●1●1●2
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

flat view

Your answer

toggle preview

community wiki:

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

learn more about Markdown

Question tags:

right-truncation-of-strin ×8

question asked: 08 Mar '12, 08:23

question was seen: 3,878 times

last updated: 08 Mar '12, 11:03

SAP SQL Anywhere

set default value for string_rtruncation

Follow this question

Related questions