Post reply

Query on Linked Server fails when using a table alias?? Help!

  • July 26, 2011 at 4:19 pm

    #244858

    Ok I've broken it down as simple as possible, and now it really looks like a bug:

    SELECT [NUM_UNITS] FROM [(server ip)].[GUONMFGPRD].[PROD].[CONTAINER_DETAIL]

    Works perfectly, and why shouldn't it, it's a totally normal query to a linked server.

    However, when I try to add an alias to the table like so:

    SELECT CD.[NUM_UNITS] FROM [(server ip)].[GUONMFGPRD].[PROD].[CONTAINER_DETAIL] CD

    Then I get the error message:

    Msg 4104, Level 16, State 1, Line 1

    The multi-part identifier "CONTAINER_DETAIL.NUM_UNITS" could not be bound.

    It makes no freakin' sense! And don't even suggest that it's a typo, it's not. You can clearly see it's all

    spelled exactly the same, except for the extra alias name 'CD'

    I tried using different aliases, and tried querying different columns, and I tried variations of the above query

    without the square brackets, I added column aliases, and I tried leaving out the schema, I even tried other

    tables and I always get the above error when aliases are introduced into the mix.

    I would love to just go ahead and write the whole query without using any aliases, but who the hell would

    do that to themselves? I have so many joins and conditions that the query would literally be 10 pages long,

    and quite impossible to understand & debug.

    So any help with this issue would be greatly appreciated!!

    ~B

    [Edit]Other linked servers don't give me this issue so I suspect that the fault is either on the 'link' or on the other server.

  • July 27, 2011 at 2:51 am

    #1359014

    What version of SQLServer you are running?

    I have tried:

    select cd.* from [SERVERNAME].[master].[sys].[tables] cd

    And it does work fine as expected.

    Actually, should mention the following if your query uses joins between tables from linked server and other, every table in the query should be given an aliase.

    _____________________________________________
    "The only true wisdom is in knowing you know nothing"
    "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
    (So many miracle inventions provided by MS to us...)

    How to post your question to get the best and quick help[/url]

  • July 27, 2011 at 5:13 am

    #1359070

    Your problem is "Server ip".

    An IP address has 3 periods (aka decimal points) in it. That is the 3 periods used by a linked server. Hence, the rest of your table name database.owner.table is being read as the alias or is screwing up SQL's ability to understand what the alias is.

    You need to changed your linked server name to a user friendly, period free, linked server name. Then it should work.

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

  • July 27, 2011 at 5:29 am

    #1359087

    He used square brackets arround server IP, so it should not be a problem for SQL Server to read it as a single name...

    Also the error he would have in this case should be - Incorrect Syntax error

    _____________________________________________
    "The only true wisdom is in knowing you know nothing"
    "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
    (So many miracle inventions provided by MS to us...)

    How to post your question to get the best and quick help[/url]

  • July 27, 2011 at 7:39 am

    #1359248

    Thanks for the replies.

    Brandie Tarvin:

    You need to changed your linked server name to a user friendly, period free, linked server name. Then it should work

    The ip address definitely works in the top query, I've done it that way many times. And

    I already stated that the top query does work.

    Eugene Elutin:

    What version of SQLServer you are running?

    Now that's a good question. The server I'm running the query from is version 10.0.4000, but the linked

    server is version 10.50.1600 I just noticed.

    I wonder if the difference in version would cause any issues?

    In my queries I always use aliases on tables, but it doesn't even work with the simplest query

    like I have above.

    I have done queries on linked servers before without much trouble, but in previous cases

    I did not set up the linked server. In this case I did using this command, with all the defaults:

    execute sp_addlinkedserver (server ip)

    so I'm wondering if I could have possibly linked the server incorrectly, and whether or

    not that would cause an issue like this...

    thanks!

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

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