HostWeb Forums » Microsoft Databases » microsoft.public.sqlserver.msde » Linked Server Error
Topic: Linked Server Error
We have set up Linked server between 2 sql server 2000 servers, service pack
4 on Windows 2003.
We have set this up using impersonation, based on dba accounts of which are
sysadmin and they exist on all of our sql server. This linked servers worked
initially for some time, but now stops working. A reboot sometimes resolve
this issue, but now and again the linked server will stop working. The script
we use to set up the linked server is as follows:
EXEC sp_addlinkedserver 'LNK_Ex', '', 'SQLOLEDB', 'Dev', NULL, NULL, 'Ex'
EXEC sp_serveroption 'LNK_Ex', 'rpc', 'TRUE'
EXEC sp_serveroption 'LNK_Ex', 'pub', 'FALSE'
EXEC sp_serveroption 'LNK_Ex', 'sub', 'FALSE'
EXEC sp_serveroption 'LNK_Ex', 'dist', 'FALSE'
EXEC sp_serveroption 'LNK_Ex', 'dpub', 'FALSE'
EXEC sp_serveroption 'LNK_Ex', 'rpc out', 'TRUE'
EXEC sp_serveroption 'LNK_Ex', 'data access', 'TRUE'
EXEC sp_serveroption 'LNK_Ex', 'collation compatible', 'FALSE'
EXEC sp_serveroption 'LNK_Ex', 'use remote collation', 'FALSE'
EXEC sp_serveroption 'LNK_Ex', 'lazy schema validation', 'FALSE'
EXEC sp_addlinkedsrvlogin @rmtsrvname = 'LNK_Ex', @useself = 'TRUE',
@locallogin = 'MPSNT\hero', @rmtuser = NULL, @rmtpassword = NULL
EXEC sp_addlinkedsrvlogin @rmtsrvname = 'LNK_Ex', @useself = 'TRUE',
@locallogin = 'MPSNT\pacesy', @rmtuser = NULL, @rmtpassword = NULL
EXEC sp_droplinkedsrvlogin @rmtsrvname = 'LNK_Ex', @locallogin = NULL
Replies below ↓