Openrowset update issue
-
Hi
I have a problem using OPENROWSET to move data from Server A to Server B correctly.
I have the following requirement in steps that I would like OPENROWSET to do in one step.I have a service reading a collector database on serverB
All new transactions will have a StatusID of 0
The StatusID column is declared as an INT
The service needs to copy all new transaction over to ServerA.
Step 1: Update all records from ServerB, collector db table where StatusID = 0 to 1
Step 2: Select all records now with StatusID Set to 1
Step 3: Insert the selected records in db on ServerA
Step 4: Update collector table on ServerB: StatusID = 2I am trying to do steps 1 - 3 in one OPENROWSET line - Something like this:
Insert into ServerA.table Select * from OPENROWSET('Driver', 'ServerB';'username';'pwd', 'Update collectorTable set StatusID = 1 output inserted.* where StatusID = 0')The statement is executed on the connection for ServerA.
I do get the records inserted in ServerA but the Update fails in updating the collector table StatusID column value to 1 on ServerB.
Since the insert was successful, I can only assume the update was activated, the update failed causing inserted & deleted to look identical, then returning the inserted for successful insertion in the table in ServerA.I do get the records inserted in ServerA, but the Update failed in updating the collector table StatusID column to 1.Since the insert was successful, I can only assume the update was activated, the update failed causing inserted & deleted to look identical, then returning the inserted for successful insertion in the table in ServerA.
If I do the update of the StatusID locally on ServerB with the same credentials as used in the OPENROWSET, the column is updated....
Anyone has any idea why the update is failing using the OPENROWSET method ?
Thanks in advance
Wimpie -
My first guess would have been that the user that you specify for Server B does not have UPDATE rights on Server B for the table on Server B, but that should have caused the entire statement to fail, so my 2nd guess is that there's a trigger on Server B that effectively prevents the update, but doesn't cause a query failure. Check for a trigger on Server B for the table in Server B.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy) -
Hi Steve
Thanks for your reply.
Unfortunately, there are no triggers on the tables.I was thinking that as SQL Server has configuration script that allows distributed queries, there might be another configuration script that specifically addresses the updating of tables using distributed queries... but I don't see any mention of such a function on the Microsoft help either.
- wbeetge - Tuesday, May 15, 2018 1:25 AM
First check your Linked Server's "properties" in the Server Options portion of the dialog, and look to see that the setting for Data Access is "True", and the same for RPC and RPC Out and Enable Promotion of Distributed Transaction, as shown below:
Then try the following syntax for your query:
INSERT INTO ServerA.table
SELECT *
FROM OPENROWSET('Driver', 'ServerB';'username';'pwd', '
UPDATE CT
SET CT.StatusID = 1
OUTPUT inserted.*
FROM collectorTable AS CT
WHERE CT.StatusID = 0;');Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy) -
There is no way possible that I'd ever allow code to exist that had the user name and password in clear text.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) - Jeff Moden - Tuesday, May 15, 2018 11:37 AM
Neither will the auditors.
Sue
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply