set default value for string_rtruncation

[Former Member]

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.

[Breck_Carter]

SET OPTION PUBLIC.string_rtruncation = 'OFF';

[Former Member]

brilliant, thanks very much

[Former Member]

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';

[VolkerBarth]

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:)

[Former Member]

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.

[Breck_Carter]

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.

[VolkerBarth]

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:)