Post reply

syntax for querying to another sql server

  • January 16, 2004 at 9:30 am

    #107293

    Hello all, I need to query a database that is on another server, and fill a table from the recordset that is brought back. I’m unsure of the syntax to query another server from the query analyzer? Is it also possible to create a table on another db server this way using the ‘create table’ syntax? Thanks

  • January 16, 2004 at 9:51 am

    #490476

    You either have to use openrowset or create a linked server to the other server. I prefer creating a linked server because with openrowset, you have to create the connection to teh server with every query.

    Once you have the linked server setup, just refer to the database objects by their full names: ServerName.DatabaseName.OwnerName.ObjectName

    for example:

    Select *

    From SQL021.SomeDB.dbo.MyTableOrView

    Another thing to bear in mind is that in some instances you can't use a 4 part name, like in an update query. So you have to use the four part name in the From clause and alias it and use the alias in the Update clause. Like so:

    Update MyObject

    Set MyField = 'Some data'

    From SQL021.SomeDB.dbo.MyTableOrView As MyObject

     

    My blog: SQL Soldier[/url]
    SQL Server Best Practices:     SQL Server Best Practices
    Twitter: @SQLSoldier
    My book: Pro SQL Server 2008 Mirroring[/url]
    Microsoft Certified Master: SQL Server, Data Platform MVP
    Database Engineer at BlueMountain Capital Management[/url]

  • January 16, 2004 at 10:08 am

    #490479

    Awsome, that should help a lot. Do you know if there is a way run a stored procedudre on the other server this way?

  • January 16, 2004 at 10:11 am

    #490481

    Yep, just do it the same way. As long as your login has permissions to do it.

    Exec SQL021.SomeDB.dbo.MyProc @Param1 = 'Somedata'

    My blog: SQL Soldier[/url]
    SQL Server Best Practices:     SQL Server Best Practices
    Twitter: @SQLSoldier
    My book: Pro SQL Server 2008 Mirroring[/url]
    Microsoft Certified Master: SQL Server, Data Platform MVP
    Database Engineer at BlueMountain Capital Management[/url]

  • January 16, 2004 at 10:44 am

    #490484

    Thanks, for the help! I do have one final question though - after adding the link do I need to drop the link or anything like that?

  • January 16, 2004 at 11:07 am

    #490495

    Just steppin' in for "offline" Rawhide....

    The link server entry remains in existance until DROPed.

    Any subsequent calls to the server from other sources

    will have access to this Linked server while it exists. Most of

    the time, the Linked server entry is part of the, let's call it

    "configuration / setup", and is used indefinitly.

    In your code, you could ADD the entry, process, DROP entry every time

    the code executes, but you have the overhead of SQL doing this work,

    and since the Link Server entry is a "named" entry, you would have problems

    when two processes try do that work at the same time.

     

    FYI you can also call a remote SP using OPENROWSET type syntax.



    Once you understand the BITs, all the pieces come together

  • January 16, 2004 at 11:38 am

    #490504

    Just another thought Matt...

    Your code could 1st check for the existance of the Link Server entry, then if it's

    not yet there, create it and leave it.

     



    Once you understand the BITs, all the pieces come together

  • January 16, 2004 at 11:44 am

    #490506

    That is something I'd like better. I know the syntax for doing that with stored proc's : IF EXISTS (SELECT name FROM sysobjects WHERE name = N'' AND type = 'P') DROP PROCEDURE But can you tell me what the syntax would be for linked servers?

  • January 16, 2004 at 11:55 am

    #490510

    Try...

      SELECT SRVName, IsRemote, * FROM Master.dbo.sysServers

    to get started.

    BOL about SysServers table has notes on IsRemote column I do not think are quite right...

    Looking deeper... will post more



    Once you understand the BITs, all the pieces come together

  • January 16, 2004 at 12:05 pm

    #490514

    BOL - SysServer - IsRemote = "1 if server is a remote server, else 0 if server is a linked server."

    hmmmm

    I added a Linked Server using sp_addlinkedserver, sp_serveroption, sp_addlinkedsrvlogin syntax and IsRemote returned 1, and my "local" server entry has IsRemote = 0... so I do not know if IsRemote column will do you any good...

    You could add a WHERE to prior posted SELECT ...WHERE SRVName <> @@Servername

    or SELECT ...WHERE SRVName = "the linked server name you are dealing with"

    There may be some other master...system tables that have the specific Linked Srv entries, but this is what I query against.



    Once you understand the BITs, all the pieces come together

  • January 16, 2004 at 12:13 pm

    #490518

    Mine too. And all of my remote servers have an IsRemote of 0.

    My blog: SQL Soldier[/url]
    SQL Server Best Practices:     SQL Server Best Practices
    Twitter: @SQLSoldier
    My book: Pro SQL Server 2008 Mirroring[/url]
    Microsoft Certified Master: SQL Server, Data Platform MVP
    Database Engineer at BlueMountain Capital Management[/url]

Viewing 11 posts - 1 through 10 (of 10 total)

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