Cross Server Queries via SQL Linked Server

  • NOTE : I know this isn't the right way, I inherited the situation.....

    The situation is that we have two SQL 2012 Databases located on separate servers connected by a linked server.

    On previous installs the databases were located on the same server so this was not a problem, but a client wanted one located on another server, that's where things started going wrong.

    There are a number of stored procedures that do cross database querying, and more than a few MERGE statements doing the same, naturally the MERGE wont work across servers so I've rewritten these as traditional UPDATE and INSERT scripts, this has largely worked.

    However one MERGE statement Inserted data into table on the other server, and used an OUTPUT INSERTED.col1 into a Table variable on the local server, Ordinarily I'd check to see what data was going to be inserted before doing it, however one of the columns in the OUTPUT set is the IDENTITY column that is only created at the point of insert.

    I tried using the INSERT INTO <Remote Target> OUTPUT INSERTED Identitycol, col2, col3 INTO @Inserted, however I get the error

    "A remote table cannot be used as a DML target in a statement which includes an OUTPUT clause or a nested DML statement."

    Which makes sense as the output table is located on the server running the query.

    I cant use the @@SCOPE_IDENTITY() as there could be more than one row inserted.

    Any suggestions of how I can get around this situation without changing the remote database?

    This is the original code

    MERGE [LinkedServer].[Database].[Schema].[Table] AS TARG

    USING

    (

    SELECT DISTINCT

    CoL1,

    Col2,

    Col3,

    Col6,

    FROM

    #Results

    ) AS SRC

    ON

    TARG.Col1 = SRC.Col1 AND

    TARG.Col2 = SRC.Col2

    WHEN NOT MATCHED BY TARGET THEN INSERT

    (

    CoL1,

    Col2,

    Col3,

    Col6,

    )

    VALUES

    (

    SRC.CoL1,

    SRC.Col2,

    SRC.Col3,

    SRC.Col6,

    )

    WHEN MATCHED THEN UPDATE SET

    TARG.Col3 = SRC.Col3,

    TARG.Col6 = SRC.Col6,

    OUTPUT

    INSERTED.ID,

    INSERTED.Col1,

    INSERTED.Col2

    INTO @Inserts;

    This is what I tried to do.

    INSERT INTO [LinkedServer].[Database].[Schema].[Table]

    (

    CoL1,

    Col2,

    Col3,

    Col6

    )

    OUTPUT

    INSERTED.ID,

    INSERTED.Col1,

    INSERTED.Col2

    INTO @Inserts

    SELECT

    CoL1,

    Col2,

    Col3,

    Col6,

    FROM

    #Results SRC

    WHERE

    NOT EXISTS

    (

    SELECT *

    FROM [LinkedServer].[Database].[Schema].[Table] TARG

    WHERE TARG.Col1= SRC.Col1 AND

    TARG.Col2= SRC.Col2

    );

    EDIT : Added code (apologies)

    _________________________________________________________________________
    SSC Guide to Posting and Best Practices

  • One possibility would be to grab the max identity value out of the target table on the linked server before running your insert. Then run the insert without the OUTPUT INSERTED and then run a select from the target table where the identity is > than the max value you originally grabbed.

    Definitely not a great solution but it would work if the target table only has one process using it at a time.

  • That's an option that I'm implementing, I'm not happy as there is the possibility that there are concurrent users doing the same process.

    I cant even wrap it in a TRANSACTION as in effect its a distributed transaction, so requires the DTC to be set up and running, if I'm not mistaken.

    I can filter on the Data that was updated and pray for the best.

    _________________________________________________________________________
    SSC Guide to Posting and Best Practices

  • Ouch! I feel your pain.

    My experience is that trying to do remote queries within an application causes all sorts of support issues when one server and/or the link either slows down or goes down.

    I am not quite sure what you are doing but it might be an idea to keep a copy of both databases on both servers, or at least a copy of the tables used in remote querying. The tables could then be asynchronously kept up to date with triggers and service broker. I know this is a pain to setup and would require service broker to be monitored but it could be less hassle in the long term.

  • I know It doesn't help that the original developer was a little too enamoured with the 'MERGE' I've found examples of code that use MERGE just to do an INSERT.

    The real issue is that the system was never written to be distributed over servers and has never been until this client, and the company didn't test it in that type of environment before installation.

    We are where we are.....

    _________________________________________________________________________
    SSC Guide to Posting and Best Practices

  • Part of the problem is the IDENTITY.

    Can you switch to using SEQUENCEs with a different range for each server?

  • Are the MERGE statements going in both directions? I mean, are there queries on server A that update server B and vice versa?

    If the updates are only from server A to server B, an option might be to create a replica of the server A database on server B and to use that instead of the server A db.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • How about a slightly different pattern?

    Send the #results to the LinkedServer.

    Run the Merge, OUTPUT the desired data

    store the results locally

    Something like this:(untested)

    INSERT INTO [LinkedServer].tempdb.dbo.Results

    SELECT

    CoL1,

    Col2,

    Col3,

    Col6,

    FROM

    #Results SRC;

    INSERT @Inserts(ID,Col1,Col2)

    EXEC('MERGE [Database].[Schema].[Table] AS TARG

    USING

    (

    SELECT DISTINCT

    CoL1,

    Col2,

    Col3,

    Col6,

    FROM

    tempdb.dbo.Results

    ) AS SRC

    ON

    TARG.Col1 = SRC.Col1 AND

    TARG.Col2 = SRC.Col2

    WHEN NOT MATCHED BY TARGET THEN INSERT

    (

    CoL1,

    Col2,

    Col3,

    Col6,

    )

    VALUES

    (

    SRC.CoL1,

    SRC.Col2,

    SRC.Col3,

    SRC.Col6,

    )

    WHEN MATCHED THEN UPDATE SET

    TARG.Col3 = SRC.Col3,

    TARG.Col6 = SRC.Col6

    OUTPUT

    INSERTED.ID,

    INSERTED.Col1,

    INSERTED.Col2') AT [LinkedServer];

    MM



    select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

  • Forum Etiquette: How to post Reporting Services problems
  • [/url]
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • [/url]
  • How to Post Performance Problems - by Gail Shaw
  • [/url]

  • @ken, Thanks for the suggestion of using SEQUENCE but I cant change the object definitions as they are called in numerous places.

    @Phil, Its data push rather than push and pull.

    @Mister.Magoo, Interesting solution I will look at that in more detail. One thought was also to place the merge on the destination and have a wrapper SP something like.

    CREATE PROCEDURE my.Wrapper

    AS

    EXEC my.Remotesp AT MyLinkedServer

    And then change it to a PULL rather than a push.

    _________________________________________________________________________
    SSC Guide to Posting and Best Practices

  • Viewing 9 posts - 1 through 8 (of 8 total)

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