Post reply

Using multiple servers in a query

  • 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?

  • April 22, 2009 at 12:17 pm

    #982401

    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

  • April 22, 2009 at 12:26 pm

    #982413

    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)?

  • April 22, 2009 at 12:46 pm

    #982431

    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

  • April 22, 2009 at 1:49 pm

    #982482

    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?

  • April 22, 2009 at 2:08 pm

    #982495

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

    - Jeff

  • April 22, 2009 at 2:10 pm

    #982499

    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?

  • April 22, 2009 at 2:16 pm

    #982503

    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

  • April 22, 2009 at 2:20 pm

    #982507

    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.

  • April 22, 2009 at 2:38 pm

    #982533

    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

  • April 22, 2009 at 2:41 pm

    #982540

    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.

  • April 22, 2009 at 2:46 pm

    #982546

    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

  • April 22, 2009 at 2:57 pm

    #982554

    😀 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.

  • April 23, 2009 at 11:51 am

    #983156

    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?

  • April 23, 2009 at 12:04 pm

    #983167

    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

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

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