Selecting data from several servers. Is it possible?
-
I understand the theory that to select data from a server that you are not logged into is to fully qualify the path. IE: "select * from server.db..table". When I have tried that I get this message: "Server 'server' is not configured for DATA ACCESS." Of course, when I am logged into that server I can select the data. Does this mean that the server is not set up for this type of query? If so is there a work around? What steps need to happen so that data can be selected across servers. Any comments on this issue would be greatly appreciated.
Thank You.
cwiney -
You need to create Linked Servers. Refer to the Books OnLine for information on how to link servers. -SQLBill (BOL - Start>Programs>Microsoft SQL Server>Books OnLine)
-
For your syntax to work, you must 1st "Link" the remote server....
See BOL for other options to "link". You can look at the following sp's to start with.
Examples below are just that. You'll need to substitute the correct names etc.
EXEC sp_addlinkedserver
@server = 'LocalNameYouWantForOtherServer'
, @provider = 'MSDASQL'
, @provstr = 'DRIVER={SQL Server};SERVER=OtherServerName;UID=user;PWD=pw;'-- substite IP for OtherServerName if needed
, @srvproduct = 'any'
EXEC sp_addlinkedsrvlogin 'LocalNameYouWantForOtherServer', 'false', 'Localuser', 'user', 'pw'
See also sp_serveroption
You may also use EM - Security - Linked Servers for GUI to do the same kind of stuff
Once you understand the BITs, all the pieces come together
-
SQLBill, you got me by a minute.... I had to correct more typing errors than you though (that's my excuse).
Once you understand the BITs, all the pieces come together -
ThomasH, I appreciate you taking that extra minute to give me a more detailed answer.
Thank you.
cwiney
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply