When I have to investigate problems caused by IMPLICIT_TRANSACTIONS ON, can I focus on Client tools setting IMPLICIT_TRANSACTIONS ON or ANSI_DEFAULTS ON or can this be set for the whole database affecting all clients?
I can avoid the problem by changing my standard script header to
SET ANSI_DEFAULTS ON SET IMPLICIT_TRANSACTIONS OFF
but at which levels session, database, server can this setting be influenced?
I found this in msdn
it seems to be possible to set this on server level.
In the Server Properties dialog (Connections Page) I found the option changed and scripted it:
implicit transactions on:
EXEC sys.sp_configure N'user options', N'2' GO RECONFIGURE WITH OVERRIDE GO
implicit transactions off:
EXEC sys.sp_configure N'user options', N'0' GO RECONFIGURE WITH OVERRIDE GO
in SSMS didn't reflect the change. The server setting seems to be somehow overwritten for such connections.
Now I have to repose the question,
are there clients which do not overwrite the server setting and can crash when the server setting is changed.