Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

Edit:

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

But executing

exec('dbcc useroptions')

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.

share|improve this question

1 Answer 1

Implicit transactions is a connection setting, it'll be set by the client tools and/or in the scripts only.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.