This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(137.6, 40%, 23%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ES%3C/text%3E%3C/svg%3E)
I set sp_configure 'user options',5498
reconfigure
but it doesn't work . the new connection are still 5496 from select @@Sirdancelot
For enabling implicit transaction the value is 2. See configure-the-user-options-server-configuration-option, there is no value as 5498
USE db_name ;
GO
EXEC sp_configure 'user options', 2 ;
GO
RECONFIGURE ;
GO
NO, EXEC sp_configure 'user options', 2 ; also can't do that globally
the later in comming connections are not IMPLICIT_TRANSACTIONS ON ,
using SSMS
what tools you are using , and how you are going to verify you have turn that on GLOBALLY ?
It does for but for For dblib network library connections, controls whether a transaction is started implicitly when a statement is executed. What is your requirement. Regarding 5496 it is a decimal value and has no meaning. You need to convert it in binary and read as mentioned in BOL document.
Regarding setting implicit_transaction on for SSMS you can do that by going to Tools(on top)...Options...Query execution..SQL Server..ANSI...and check the box IMPLICIT_TRANSACTION. You cannot enable it globally for "ALL CONNECTIONS".
PS: Do not use caps when replying. it seems you are shouting.
5496 convert to bin exactly without the option of implicitly transaction
5498 does ~~~
so sp_configure 'user options', 2 ; is ADDING implicitly transaction to the default user options globally ?
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(35.2, 30%, 13%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ETH%3C/text%3E%3C/svg%3E)
No, it doesn't add. It replaces whatever you might have with 2.
Hi @sakuraime ,
This is an old thread, please check if it could help you.
Who do @@Sirdancelot and sp_configure provide different output?
By the way, we can using the statement from this blog Determining SET Options for a Current Session in SQL Server to check what SET options are turned on. Such as below screenshot.
Best regards,
Cathy
If the response is helpful, please click "Accept Answer" and upvote it.
Hot issues October--Users always get connection timeout problem when using multi subnet AG via listener. Especially after failover to another subnet