sql server dts: Hello,

On the SQL Enterprise Manager (SQL Server 2000), I'm trying to set the
"Enforce distributed tansactions (MTS)" setting on the Connections tab of the
SQL Server properties. I can "set" the configured values for this property,
but when I view the Running values, this flag is cleared. According to the
help, I should stop and restart the SQL Server to pick up the changes. I've
tried this, but the "Enforce distributed tansactions (MTS)" setting is always
cleared.

In fact, if I simply set this property in the Configured values, dismiss the
SQL Server's properties dialog, and reopen the dialog, the Configured value
for "Enforce distributed tansactions (MTS)" is cleared. I tried this on a
different SQL Server (which is SQL Server 7, rather than SQL Server 2000), on
a different machine the setting seems to take.

What could I be doing wrong? Any suggestions on getting this setting
observed?

Thanks,
Notre

Hello Notre,

Did you try to use sp_configure to set "remote proc trans" option to test?

Also, please enable Network DTC on the server in Add/Remove Programs.

Best Regards,

Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.

=====================================================



This posting is provided "AS IS" with no warranties, and confers no rights.

Hi Peter,

Could you please provide more info on how to use sp_configure to set the
"remote proc trans" option on?

I found the Network DTC option on Windows 2003 machine. In my case, the
machine that's running SQL Server is Window 2000. I don't believe there's
such a setting on Windows 2000?

Thanks,

Hello,

You could try the following statements:

sp_configure 'remote proc trans', 1
reconfigure with override


Per your question, network DTC is only available to install in Win2003. In
Win2000, it is enabled by default. You could try to reinstall MSDTC to test
the situation

c:\winnt\system32\dtcsetup.exe

Also, please check in event log to see if there is any related errors.

Best Regards,

Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.

=====================================================



This posting is provided "AS IS" with no warranties, and confers no rights.

I have no clue what happened, but the machine now allows me to set the
"Enforce distributed tansactions (MTS)" setting in Configured values. I was
about to execute the statements you sent me, but decided to make sure it was
still "broken". But it is now working correctly. As I don't own the SQL
Server (it's a communal SQL Server in our support environment), I'm not sure
what changed but the net result is it now works.

Thanks for your help,

Welcome! :-)

Regards,

Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.

=====================================================


This posting is provided "AS IS" with no warranties, and confers no rights.