Results 1 to 8 of 8
  1. #1
    Join Date
    Jan 2008
    Posts
    21

    Unanswered: SQL query over 2 sql servers

    Hi,

    I am a newbie to SQL and learning as I go.

    I am currently trying to write a SELECT that returns the difference between 2 identical tables in 2 different SQL databases on 2 different SQL servers.

    I believe I have setup the linked servers properly.

    Here is the select I wrote;

    select *
    from [dbnameA].dbo.Item
    where not exists
    (select * from [dbnameB].dbo.ItemSD
    Where [dbnameA].dbo.Item.IMA_ItemID = [dbnameB].dbo.ItemSD.IMA_ItemID)

    The error I receive is;

    Server: Msg 208, Level 16, State 1, Line 1
    Invalid object name 'dbnameB.dbo.ItemSD'.

    Any suggestions are greatly appreciated!!
    Thanks,
    Michiel

  2. #2
    Join Date
    Jan 2008
    Posts
    21

    In addition

    I also tried to simplify the select by just executing the query below;

    select *
    from [servername].[dbnameB].dbo.ItemSD

    Then I get the following error;

    Server: Msg 7314, Level 16, State 1, Line 1
    OLE DB provider 'servername' does not contain table '"dbnameB"."dbo"."ItemSD"'. The table either does not exist or the current user does not have permissions on that table.

    OLE DB error trace [Non-interface error: OLE DB provider does not contain the table: ProviderName='servername', TableName='"dbnameB"."dbo"."ItemSD"'].

    Any help, suggestions very much appreciated!
    Thanks,
    Michiel
    Last edited by mvanmeurs; 01-24-08 at 14:52.

  3. #3
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    what did you call your linked server?
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  4. #4
    Join Date
    Jan 2008
    Posts
    21
    I called the linked server the same as the actual servname.

  5. #5
    Join Date
    Jan 2008
    Posts
    21

    Added permissions to the table

    I added permissions to the table and I think I got it working now to a certain extend.

    When I run the query below;

    select *
    from [servername].[dbnameB].dbo.ItemSD

    It gives me all the records I want.

    So the connection works I believe. But then I go back to my original query and it gives me a new error. The query is;

    select *
    from [dbnameA].dbo.Item
    where not exists
    (select * from [servername].[dbnameB].dbo.ItemSD
    Where [dbnameA].dbo.Item.IMA_ItemID = [dbnameB].dbo.ItemSD.IMA_ItemID)

    The error I get now when executing this is;

    Server: Msg 107, Level 16, State 2, Line 1
    The column prefix dbnameB.dbo.ItemSD' does not match with a table name or alias name used in the query.

    Thanks for any suggestions!!
    Michiel
    Last edited by mvanmeurs; 01-24-08 at 15:21.

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    LOOK CLOSELY
    Code:
    [servername].[dbnameB].dbo.ItemSD
    <snip>
    [dbnameB].dbo.ItemSD.IMA_ItemID
    Obvious, huh?
    George
    Home | Blog

  7. #7
    Join Date
    Jan 2008
    Posts
    21
    Hi George,

    I am new to SQL so I do not see the problem as easily as you do. If you have a second I would appreciate if you could tell me exactly where my error is in the select statement.

    When I add the servername to the where clause piece I get a message;

    Server: Msg 117, Level 15, State 2, Line 5
    The number name servername.dbnameB.dbo.ItemSD' contains more than the maximum number of prefixes. The maximum is 3.


    Thanks!
    Michiel

  8. #8
    Join Date
    Jan 2008
    Posts
    21
    I got it working. Thanks for the help!!

    Michiel

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •