Using multiple servers in a query

Question

Post reply

Using multiple servers in a query

Investor_Ennui

SSC Veteran

Points: 272
More actions
April 22, 2009 at 12:00 pm

#208657

I have three servers that I constantly use for work. Unfortunately, sometimes I need information that is on two different servers (don't ask me why it was designed this way). This has been a great road block, and it seems that if I had a way to take information from one server- put it in a temp table and use it with information from another server.
Is this possible at all?

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply

jjtetzlo SSCrazy Points: 2511 More actions · Answer 1

Yes, very possible.

Take a look at Linked Servers:

http://msdn.microsoft.com/en-us/library/ms188279(SQL.90).aspx

http://msdn.microsoft.com/en-us/library/ms190479(SQL.90).aspx

- Jeff

Investor_Ennui SSC Veteran Points: 272 More actions · Answer 2

Thanks for the information, but sadly I do not have the permission to perform such an action. Any way around this (other than getting the premission, which is slim)?

jjtetzlo SSCrazy Points: 2511 More actions · Answer 3

You may need to have someone create the Linked Server initially if you don't have rights to do this, but a Linked Server can be customized to only allow specific logins through, to allay any security concerns they might have.

You may also take a look at sqlcmd, and see if that can be of any help.

http://msdn.microsoft.com/en-us/library/ms170207(SQL.90).aspx

- Jeff

Investor_Ennui SSC Veteran Points: 272 More actions · Answer 4

I'm thinking about trying to get permission from the IT department. Is linking servers something I do once, then run my queries all I want - or do I do it on a per-query basis?

jjtetzlo SSCrazy Points: 2511 More actions · Answer 5

Once it's set up it'll be there until someone deletes it.

- Jeff

Investor_Ennui SSC Veteran Points: 272 More actions · Answer 6

Now I am wondering if one is already set up, and I just don't know about it (or anyone else in my area). Is there a little script I can run to check these things?

jjtetzlo SSCrazy Points: 2511 More actions · Answer 7

Try this:

select

LinkedSrvName = case

when ll.server_id = 0 and ll.local_principal_id = 0 and ll.uses_self_credential = 1 then 'Undefined'

else ss.name end,

LocalName = case

when spn.name is null then 'Undefined Login'

else spn.name end,

RemoteName = case

when spn.name is null and ll.remote_name is not null then ll.remote_name

when spn.name is null then 'Current Login'

else isnull(ll.remote_name, spn.name) end

from [master].[sys].[servers] ss

join [master].[sys].[linked_logins] ll on ss.server_id = ll.server_id

left join [master].[sys].[server_principals] spn on ll.local_principal_id = spn.principal_id

- Jeff

Investor_Ennui SSC Veteran Points: 272 More actions · Answer 8

That worked, but came up with zero results.

I also tried sp_linkedservers - and that came up with a good size list, but I can't tell what servers are being linked.

jjtetzlo SSCrazy Points: 2511 More actions · Answer 9

Do you see the server you need in the output from sp_linkedserver?

If one has already been created, try something like:

select name from [YourServer].[Master].[sys].[databases]

You may already be set up and not know it. If not, contact the DBA and have them add you to that Linked Server definition.

- Jeff

Investor_Ennui SSC Veteran Points: 272 More actions · Answer 10

Getting a lot closer. THANKS!

select name from [AKA_LEOPARD].[Master].[sys].[databases]

What I got from that was:

The OLE DB provider "SQLNCLI" for linked server "AKA_LEOPARD" does not contain the table ""Master"."sys"."databases"". The table either does not exist or the current user does not have permissions on that table.

So I'm thinking that I need to fill in Master, sys, databases (don't laugh). Not sure what goes in any of them.

jjtetzlo SSCrazy Points: 2511 More actions · Answer 11

So it looks like the Linked Server is there, you probably just need to have rights to the Linked Server. You'll probably need to talk to the DBA at this point.

- Jeff

Investor_Ennui SSC Veteran Points: 272 More actions · Answer 12

😀 Excellent - if this works, it will world shaking for my department.

Thank you so much for your help. If anything develops, I'll be back.

Investor_Ennui SSC Veteran Points: 272 More actions · Answer 13

So I got a response from my IT, and tried:

select name from [aka_cssrepl].[Master].[sys].[databases]

However, this time the error message is:

Server: Msg 7416, Level 16, State 1, Line 1

Access to the remote server is denied because no login-mapping exists.

Back to IT for this one?

jjtetzlo SSCrazy Points: 2511 More actions · Answer 14

Looks like it. They'll need to check the Security Properties of the Linked Server, to make sure that either your login is added explicitly, or that any connections to the linked server will be made using the current login's security context.

- Jeff