SQL Server Performance Forum – Threads Archive

DTC with clustered SQL servers

I have an odd situation occuring. I have two clustered instances of SQL server ( one is 2000 enterprise and 2005 enterprise, both are running on w2k3). DTC is not running on either server and from what I gather you need to have it running to use linked servers. here is where my issue is happening. I can link from the 2005 to the 2000, but i cannot link from the 2000 to the 2005. So my question is: Does DTC have to be running for linked servers and if so why would it work without it running? Thanks in advance for any input.

Post the script used to create Linked server. Also search and identify instcat.sql on your and execute on SQL 2000 instance. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.

I created the linked servers through Management Studio but I have supplied the script that MMS generates. Also the SQL used to attempt using linked server is: select * from [<linked server name>].database.owner.table as for the instcat.sql, is the possibility of damage or downtime? /* linked server script on sql 2000 linking to sql 2005 */
EXEC master.dbo.sp_addlinkedserver @server = N'<server.domain>’, @srvproduct=N’SQL Server’
GO
EXEC master.dbo.sp_serveroption @server=N'<server.domain>’, @optname=N’collation compatible’, @optvalue=N’false’
GO
EXEC master.dbo.sp_serveroption @server=N'<server.domain>’, @optname=N’data access’, @optvalue=N’true’
GO
EXEC master.dbo.sp_serveroption @server=N'<server.domain>’, @optname=N’dist’, @optvalue=N’false’
GO
EXEC master.dbo.sp_serveroption @server=N'<server.domain>’, @optname=N’pub’, @optvalue=N’false’
GO
EXEC master.dbo.sp_serveroption @server=N'<server.domain>’, @optname=N’rpc’, @optvalue=N’true’
GO
EXEC master.dbo.sp_serveroption @server=N'<server.domain>’, @optname=N’rpc out’, @optvalue=N’true’
GO
EXEC master.dbo.sp_serveroption @server=N'<server.domain>’, @optname=N’sub’, @optvalue=N’false’
GO
EXEC master.dbo.sp_serveroption @server=N'<server.domain>’, @optname=N’dpub’, @optvalue=N’false’
GO
EXEC master.dbo.sp_serveroption @server=N'<server.domain>’, @optname=N’connect timeout’, @optvalue=N’0′
GO
EXEC master.dbo.sp_serveroption @server=N'<server.domain>’, @optname=N’collation name’, @optvalue=null
GO
EXEC master.dbo.sp_serveroption @server=N'<server.domain>’, @optname=N’lazy schema validation’, @optvalue=N’false’
GO
EXEC master.dbo.sp_serveroption @server=N'<server.domain>’, @optname=N’query timeout’, @optvalue=N’0′
GO
EXEC master.dbo.sp_serveroption @server=N'<server.domain>’, @optname=N’use remote collation’, @optvalue=N’false’ /* linked server script on sql 2005 linking to sql 2000 */
EXEC master.dbo.sp_addlinkedserver @server = N'<server.domain>’, @srvproduct=N’SQL Server’
GO
EXEC master.dbo.sp_serveroption @server=N'<server.domain>’, @optname=N’collation compatible’, @optvalue=N’false’
GO
EXEC master.dbo.sp_serveroption @server=N'<server.domain>’, @optname=N’data access’, @optvalue=N’true’
GO
EXEC master.dbo.sp_serveroption @server=N'<server.domain>’, @optname=N’dist’, @optvalue=N’false’
GO
EXEC master.dbo.sp_serveroption @server=N'<server.domain>’, @optname=N’pub’, @optvalue=N’false’
GO
EXEC master.dbo.sp_serveroption @server=N'<server.domain>’, @optname=N’rpc’, @optvalue=N’false’
GO
EXEC master.dbo.sp_serveroption @server=N'<server.domain>’, @optname=N’rpc out’, @optvalue=N’false’
GO
EXEC master.dbo.sp_serveroption @server=N'<server.domain>’, @optname=N’sub’, @optvalue=N’false’
GO
EXEC master.dbo.sp_serveroption @server=N'<server.domain>’, @optname=N’connect timeout’, @optvalue=N’0′
GO
EXEC master.dbo.sp_serveroption @server=N'<server.domain>’, @optname=N’collation name’, @optvalue=null
GO
EXEC master.dbo.sp_serveroption @server=N'<server.domain>’, @optname=N’lazy schema validation’, @optvalue=N’false’
GO
EXEC master.dbo.sp_serveroption @server=N'<server.domain>’, @optname=N’query timeout’, @optvalue=N’0′
GO
EXEC master.dbo.sp_serveroption @server=N'<server.domain>’, @optname=N’use remote collation’, @optvalue=N’true’

Are you able to ping the server from 2000 machine?
Try to connect using QA from 2000 machine to 2005…. Also check the following…
http://sqlserver2000.databases.aspfaq.com/how-do-i-prevent-linked-server-errors.html
MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided â€œAS ISâ€ with no warranties for accuracy.

From 2000 to 2005: EXEC sp_addlinkedserver
@server = N’Server2005′,
@srvproduct = N”,
@provider = N’SQLNCLI’,
@provstr = N’SERVER=IPAddress;Integrated Security=True’ BTW is that SQL2005 is a x64 bit? Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.

I can ping and connect with QA from 2000 to 2005 and vice versa. I cannot use supplied script to add linked server because I do not have SQLNCLI provider on my 2000 box (Isn’t this only for 2005). Can this be added/Is it a good idea to? 2005 box is not x64. I have however added DTC as a cluster resource on the 2000 and the communication seems to work fine now. Now for my own curiosity, what is different between the 2 versions that would cause linked servers to work only one way without DTC. NOTE: I have tried in test environment where the servers are not clustered and linked servers work fine in both directions with DTC off.

]]>