remote transaction on SQL Server

[T.S.Negi]

To support remote transaction on SQL Server i have configured user
options to 16384. However if i make changes in other properties of SQL
Server, user option setting reverts to its original value. I would
like to know 1. Is it possible to protect this setting so that change
in other properties do not effect this setting? 2. What are the
properties of SQL Server that are linked with user option settings
(i.e. Changes in those properties cause reverting in user options)?

[Dan Guzman]

Re: remote transaction on SQL Server

> However if i make changes in other properties of SQL[color=blue]
> Server, user option setting reverts to its original value. I would
> like to know 1. Is it possible to protect this setting so that change
> in other properties do not effect this setting?[/color]

The 'user options' configuration option is a bitmask specification. You
need to perform a bitwise OR in order to leave the other options intact.
The script below will turn on the specified option on and retain the other
option settings.

DECLARE
@run_value int,
@new_run_value int
CREATE TABLE #UserOptions
(
name varchar(40) NOT NULL,
minimun int,
maximmun int,
config_value int,
run_value int
)
INSERT INTO #UserOptions
EXEC sp_configure 'user options'

SELECT @new_run_value = run_value | 16384
FROM #UserOptions

EXEC sp_configure 'user options', @new_run_value
RECONFIGURE WITH OVERRIDE
DROP TABLE #UserOptions
GO

--
Hope this helps.

Dan Guzman
SQL Server MVP

"T.S.Negi" <tilak.negi@min d-infotech.com> wrote in message
news:a1930058.0 402152114.6d399 d0c@posting.goo gle.com...[color=blue]
> To support remote transaction on SQL Server i have configured user
> options to 16384. However if i make changes in other properties of SQL
> Server, user option setting reverts to its original value. I would
> like to know 1. Is it possible to protect this setting so that change
> in other properties do not effect this setting? 2. What are the
> properties of SQL Server that are linked with user option settings
> (i.e. Changes in those properties cause reverting in user options)?[/color]