SQL Server Performance Forum – Threads Archive
Query another serverI want to access selected data from another sql server (sql 2000) on the local network. I want to access using four part naming – Server.database.owner.table. But I am getting an error even if i linked the server using sp_addlinkedserver sp. How i can access the data.
Awaitig the response. Rgds
try this, i hope this will work as ive checked with my server.
1>add linked server to sysservers EXEC sp_addlinkedserver @server=’servername’, @srvproduct=”,
@provider=’SQLOLEDB’, @datasrc=’servername’,@catalog=’DBname’ 2>add login to linked server EXEC sp_addlinkedsrvlogin ‘servername’, ‘false’,NULL, ‘sa’, ‘password’ select top 1 * from [linkedserver].dbname.dbo.tablename
Was the linked server created correctly? If you have EM, can you click on the linked server and actually see information under it? Please post the error you are getting. That always helps in troubleshooting.
[email protected] When life gives you a lemon, fire the DBA.
Even when you link a server, you do it with a certain security context. You should read "Establishing Security for Linked Servers" in books online, specifically the section on self-mapping. When you added the linked server, what security properties did you use? (check the security tab in the linked server). Does the property you’ve selected, support your logon method to both the main and linked server?