Post reply

Specifying different server name in query

  • July 25, 2011 at 7:14 am

    #150382

    Hi,

    Does someone know how to specify a different server name in a query ?

    I need this to work within a query below (highlighted in bold):

    --------------------------

    Select *

    Into #TempA

    From TB_ARTICLE

    Where ARTICLE_ID not in (Select ARTICLE_ID from KEULONSV105..website.TB_ARTICLE)

    set identity_insert TB_ARTICLE on

    Insert into TB_ARTICLE

    Select * From #TempA

    ---------------------------

    Thanks,

    Paul

  • July 25, 2011 at 7:19 am

    #1357749

    there's only two ways i can think of:

    1. use synonyms, and dynamically change the synonym defintion.

    the problem with that is the synonym must point to an OBJECt, like a table...it cannot be a servername, which isn't an object(sys.objects)

    2. otherwise, you have to go to dynamic SQL to do the select and insert.

    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!

  • July 25, 2011 at 7:53 am

    #1357783

    Lowell (7/25/2011)

    there's only two ways i can think of:

    1. use synonyms, and dynamically change the synonym defintion.

    the problem with that is the synonym must point to an OBJECt, like a table...it cannot be a servername, which isn't an object(sys.objects)

    2. otherwise, you have to go to dynamic SQL to do the select and insert.

    actually I have to use this query within an SSIS package to compare the 2 tables in different servers. could you give a example on how to do this by using dynamic SQL ?

  • July 26, 2011 at 1:47 pm

    #1358798

    How large of a table is TB_ARTICLE? By "compare the 2 tables in different servers" if you mean to sync data between them, i.e. from the looks of your query you want to pull articles into a table in server_secondary from a similar or same table in server_primary. If this is the case and TB_ARTICLE is or will eventually be of a non-trivial size you're asking for trouble. Linked Servers are not a great way to do this type of work.

    There are much better options besides Linked Servers, e.g. different forms of Replication, Log Shipping, ETL using SSIS, just to name a few.

    If you must use Linked Servers try to use the primary key, an identity column or an audit column on TB_ARTICLE to identify only the rows you pull from server1 over to server2.

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

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

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