Hi guys -
I have a MERGE statement I'm trying to fight with and losing. I'm calling data from 2 linked servers and one of them has a user defined functions on it..
I have database A on one server (2008 R2) - the one with the UDFs and the source of the MERGE command.
And database B on the other server (2008 sp1) - the target.
The source of the MERGE does contain a call to a table function in server A.
When both databases are on the same server, it runs fine and I'm not entirely sure how to fix it so I can get it to run between linked servers.
I've tried both using OPENQUERY and just a normal statement...on the "normal" statement (Query #1) I get
"Incorrect syntax near 'MERGE'. You may need to set the compatibility level of the current database" when I run it "normally" . Which the compatibility of both 2008 and 2008R2 are set to 100.
When i try with OPENQUERY (#2) I get "Incorrect syntax near the keyword 'AS" .
It is a dynamic query because I need to pass in my database names.
@A and @B are the linked server names.
Query #1 (the "normal" one just using MERGE)
DECLARE @A VARCHAR(50)
DECLARE @B VARCHAR(50)
DECLARE @LastUpdated DATETIME
DECLARE @query VARCHAR(8000)
SET @A = ?
SET @B = ?
SET @LastUpdated = ?
SET @query = '
MERGE '+@B+'.database.dbo.tblFieldVersion as target
SELECT c.x, st
View Complete Post