February 23, 2012 at 12:05 pm
Hi,
I am updating a table in a db in a remote server by using LINKED SERVER.
I checked the connectivity and its good.
Here's my code :-
-- Updating
UPDATE 127.0.0.1.FrontOff.dbo.PLU1
SET
a.COST_CASE_PRC = b.GP_CP,
DT_PLU_LAST_UPDATE =GETDATE(),
DATE_COST_CASE_PRC =GETDATE()
from
127.0.0.1.FrontOff.dbo.PLU1 a
JOIN
TEMP16 b
ON
a.ITM_ID = b.Pasea_UPC
AND
a.VND_ID COLLATE DATABASE_DEFAULT=b.Pasea_itemnmbr COLLATE DATABASE_DEFAULT
where
a.COST_CASE_PRC < a.RTL_PRC
ERROR:--->
OLE DB provider "SQLNCLI" for linked server "ip address" returned message "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.".
Msg 16955, Level 16, State 2, Line 1
Could not create an acceptable cursor.
Its a simple update statemnt, why is sql server understands as a cursor??
Regards,
Skybvi
Regards
Sushant Kumar
MCTS,MCP
February 23, 2012 at 1:34 pm
For anyone interested I've now resolved this problem.
The issue is due to there being no unique index on the target table, as soon as I created an index on the table and set it as unique it allowed me to run the update.
Its hard to guess that from the error message shown 🙁
Regards,
Skybvi
Regards
Sushant Kumar
MCTS,MCP
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply