June 15, 2016 at 9:54 am
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
June 15, 2016 at 10:19 am
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.
June 15, 2016 at 10:35 am
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
June 15, 2016 at 10:39 am
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.
June 15, 2016 at 10:49 am
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
June 15, 2016 at 10:50 am
Part of the problem is the IDENTITY.
Can you switch to using SEQUENCEs with a different range for each server?
June 15, 2016 at 10:51 am
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.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
June 15, 2016 at 11:28 am
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);June 16, 2016 at 2:12 am
@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