Post reply

Reference Table in DB on Named Instance

  • July 3, 2009 at 4:14 am

    #132471

    Hi, I know how to reference a table in a database on the same server, however I am unsure if it is possible to reference a table in a database on a names instance?

    SELECT

    p.Product,p.ProductId

    FROM Products AS p

    I want to Join the above query to TableA in DB1 in MYSERVER\INSTANCE2

    LEFT OUTER JOIN INSTANCE\DB\Table.Product = p.Product

    Is this possible?

    Thanks,

    Phil.

    -------------------------------------------------------------------------------------
    A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."

    Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '

    Tommy Cooper

  • July 3, 2009 at 4:22 am

    #1019698

    Yes you have to add the "MYSERVER\INSTANCE2" as linked server to "MYSERVER\INSTANCE1"

    SELECT * FROM [MYSERVER\INSTANCE2].Database.owner.tablename

    Tanx 😀

  • July 3, 2009 at 6:34 am

    #1019744

    You should use sp_addlinkedserver 'name of instance'

    You can only use a linkedserver as the source of the data (From Clause)

    The insert should be done running your query on the server itself.

    There is 1trick and that is the use of a synonym. You create a synonym to the linked server

    eg. dbo.a > 'linkedserver.db1.dbo.NonExistingTable'

    then you can use select .... into dbo.a from ..

    and this will actually create a new table on the linked server, very cool stuff.

    But i don't recommend this approach.

    Greets

  • July 3, 2009 at 9:41 am

    #1019809

    Thanks guys, linked servers is way to go then.....

    Many Thanks,

    Phil.

    -------------------------------------------------------------------------------------
    A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."

    Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '

    Tommy Cooper

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

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