Post reply

Performing queries on multiple servers

  • April 6, 2011 at 4:40 pm

    #249101

    I have 2 SQL Servers. One is running SQL Server 2008. The other - SQL Server 2005.

    I need to do a query with a join on tables in the 2 different servers. Can this be done? What do I need to do?

  • April 6, 2011 at 11:25 pm

    #1308353

    if both are in same server just use complete path. if those are in two different server, create a linked server and use the join.

  • April 7, 2011 at 11:41 am

    #1308714

    OK, I got though the process of Linking the servers, and all of the hijinks involved with getting that security right (I think).

    Could you please give me a sample of the syntax for a SELECT - JOIN using tables in the 2 servers

  • April 8, 2011 at 6:54 am

    #1309113

    Check out the FROM clause in Books-OnLine (BOL). The table on the report server will need to be specified in 4-part naming (Server.Database.Schema.Table).

    Edit: Where Server = the Linked Server name for the remote server on the local server.

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes

    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • April 8, 2011 at 6:59 am

    #1309117

    a simple example:

    SELECT

    t1.*,

    t2.*

    FROM Databasename.dbo.Table t1

    INNER JOIN MyLinkedServer.DbName.dbo.OtherTable t2

    ON t1.ID = t2.ID

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • April 8, 2011 at 12:55 pm

    #1309360

    Good so far. Now I am receiving an error which is known per http://support.microsoft.com/kb/906954.

    The fix is to run instcat.sql. Before I do this, I want to make sure that I completely understand the syntax of the recommended command.

    Command:

    osql -U <AdminLogin> -P <AdminPassword> -S <LinkedServerName> -i <Location>\instcat.sql

    Example:

    osql -U sa -P MyPassWord -S Database.IpAddress.com -i “C:\Program Files\Microsoft SQL Server\MSSQL\Install\instcat.sql”

    I think this needs to be run on the older SQL 2000 server, but what do I enter for the <LinkedServerName>, the SQL 2000, or the SQL 2008 server?

  • April 9, 2011 at 8:33 am

    #1309544

    doug 37293 (4/8/2011)

    Good so far. Now I am receiving an error which is known per http://support.microsoft.com/kb/906954.

    The fix is to run instcat.sql. Before I do this, I want to make sure that I completely understand the syntax of the recommended command.

    Command:

    osql -U <AdminLogin> -P <AdminPassword> -S <LinkedServerName> -i <Location>\instcat.sql

    Example:

    osql -U sa -P MyPassWord -S Database.IpAddress.com -i “C:\Program Files\Microsoft SQL Server\MSSQL\Install\instcat.sql”

    I think this needs to be run on the older SQL 2000 server, but what do I enter for the <LinkedServerName>, the SQL 2000, or the SQL 2008 server?

    Yes, instcat.sql needs to be run on the SQL 2000 32 bit box. You can do it via osql (above) or just connect to the sql server via SSMS and then run the script there.

  • April 9, 2011 at 7:42 pm

    #1309582

    But what do I enter for the <LinkedServerName>, the SQL 2000, or the SQL 2008 server?

  • April 10, 2011 at 7:16 am

    #1309607

    The SQL 2000 server, where you need to run instcat.sql against.

  • April 11, 2011 at 2:49 pm

    #1310118

    Got it working!

    Many thanx to all.

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

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