Merge statement with openrowset

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » Merge statement with openrowset

Merge statement with openrowset

Rate Topic

Display Mode

Topic Options

Author

Message

S_Kumar_S

Posted Friday, February 08, 2013 12:16 AM

Old Hand

Group: General Forum Members
Last Login: Yesterday @ 7:59 AM
Points: 322, Visits: 719

HI All
Is it possible to run the MERGE statement using Openrowset or openquery? I tried with below statement:

SELECT *
FROM OPENROWSET('SQLNCLI',
'DRIVER={SQL Server};SERVER=MYSERVER;UID=sa;PWD=mypwd',
'MERGE dbo.tab1 AS a
USING (SELECT * FROM [SERVERXYZ].MyDB.dbo.tab1 with (nolock)) AS b
ON a.Id=b.id
WHEN MATCHED THEN UPDATE SET
a.ID=b.id
WHEN NOT MATCHED BY TARGET THEN
INSERT(id)
VALUES(b.id
)
WHEN NOT MATCHED BY SOURCE THEN DELETE;')

but got error:

Msg 7357, Level 16, State 2, Line 1
Cannot process the object "MERGE dbo.tab1 AS a
USING (SELECT * FROM [SERVER2].DotnetNuke2.dbo.tab1 with (nolock)) AS b
ON a.Id=b.id
WHEN MATCHED THEN UPDATE SET
a.ID=b.id
WHEN NOT MATCHED BY TARGET THEN
INSERT(id)
VALUES(b.id
)
WHEN NOT MATCHED BY SOURCE THEN DELETE;". The OLE DB provider "SQLNCLI10" for linked server "(null)" indicates that either the object has no columns or the current user does not have permissions on that object.

-----Table Proc Index Performance TSQL &&%$#@*(#@$%.......------------
Deep Into SQL Jungle

Post #1417479

arun1_m1

Posted Friday, February 08, 2013 12:24 AM

SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 8:03 AM
Points: 834, Visits: 468

Please check if you have the necessary permission to perform INSERT/UPDATE/DELETE operation on the target table.

Post #1417482

S_Kumar_S

Posted Friday, February 08, 2013 3:07 AM

Old Hand

Group: General Forum Members
Last Login: Yesterday @ 7:59 AM
Points: 322, Visits: 719

yes, permissions are there. If i run the merge statement without OPENROWSET, it works.
So i was querious if it is possible at all?

-----Table Proc Index Performance TSQL &&%$#@*(#@$%.......------------
Deep Into SQL Jungle

Post #1417550

Jason-299789

Posted Friday, February 08, 2013 3:29 AM

SSC Eights!

Group: General Forum Members
Last Login: Today @ 6:25 AM
Points: 838, Visits: 2,197

BOL states that OpenRowSet can be used as a Target in the merge but appears to be ambiguous as to whether it can be a source.

Have you tried loading the data from the openrowset into a temp table then use that as the merge, another alternative could be to use a CTE to do the OpenRowSet load, but It may cause a bottleneck.

_________________________________________________________________________
SSC Guide to Posting and Best Practices

Post #1417566

ChrisM@Work

Posted Friday, February 08, 2013 4:10 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 8:57 AM
Points: 5,705, Visits: 11,140

S_Kumar_S (2/8/2013)

HI All
Is it possible to run the MERGE statement using Openrowset or openquery? I tried with below statement:

You want to run this MERGE as a remote query on SERVER=MYSERVER.
The MERGE uses a linked server (SERVERXYZ) table as a source.
What server are you connected to when you run the openrowset query?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps

Post #1417585

S_Kumar_S

Posted Thursday, February 14, 2013 5:28 AM

Old Hand

Group: General Forum Members
Last Login: Yesterday @ 7:59 AM
Points: 322, Visits: 719

Sorry for replying late. I am connected to MYSERVER while running the openrowset

-----Table Proc Index Performance TSQL &&%$#@*(#@$%.......------------
Deep Into SQL Jungle

Post #1419997

« Prev Topic | Next Topic »

Permissions