Post reply

How to "Select [server1].db.dbo.table.field, [server2].db.dbo.table.field . . . Inner Join . . ." Yada Yada Yada

  • November 14, 2005 at 12:43 pm

    #166634

    Hi all,

    I need to do a select statement joining two tables across two instances(servers) . In my select statement, I have

    SELECT     field1, field2, field3, field4 from local_db.dbo.table1 LEFT OUTER JOIN

                          [remote_server].remote_db.dbo.table2 ON field1 = field2 where . . .

     

    I get the error message

    Server: Msg 117, Level 15, State 2, Line 4

    The number name 'remote_server.remote_db.dbo.table2' contains more than the maximum number of prefixes. The maximum is 3.

    If I take out the owner, I get

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

    Invalid object name 'remote_server.remote_db.table2'.

    Thanks for your help.

     

     

     

  • November 14, 2005 at 1:00 pm

    #603739

    Answered my own question. When referencing fields in my statement, I was qualifying

    [server_name].[database_name].[owner].[field_name]

    Too many prefixes. Once I had the tables referenced, all I had to do was specify field_name by itself as long as it was unique.

     

  • November 14, 2005 at 1:44 pm

    #603754

    That is why if find the use of table aliases critical to readability and debugging of SQL statements.  In case you haven't used them your SQL could change to:

    SELECT     t1.field1, t2.field2, t1.field3, t1.field4 from local_db.dbo.table1 t1 LEFT OUTER JOIN

                          [remote_server].remote_db.dbo.table2 t2 on t1.field1 = t2.field2

     

    just my 2 cents.

     

    If the phone doesn't ring...It's me.

  • November 14, 2005 at 1:53 pm

    #603757

    Thanks, big help.

     

Viewing 4 posts - 1 through 3 (of 3 total)

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