Update statment failed - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Update statment failed

Author

Topic

skybvi
Posting Yak Master

193 Posts

Posted - 2012-02-23 : 12:17:13

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 [ip address].FrontOff.dbo.PLU1

SET
a.COST_CASE_PRC = b.GP_CP,
DT_PLU_LAST_UPDATE =GETDATE(),
DATE_COST_CASE_PRC =GETDATE()

from
[ip address].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,
Sushant
DBA
Virgin Islands(U.K)

skybvi
Posting Yak Master

193 Posts

Posted - 2012-02-23 : 15:35:49

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,
Sushant
DBA
Virgin Islands(U.K)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-23 : 15:36:38

might be this?

http://blogs.msdn.com/b/sqlblog/archive/2011/02/22/executing-an-insert-statement-on-a-view-in-linked-server.aspx

try implementing it as a procedure and executing it remotely

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

vinu7866
Starting Member

1 Post

Posted - 2012-06-12 : 03:22:01

how to do this???

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-12 : 15:20:48

quote:
Originally posted by vinu7866

how to do this???

how to do what?
If scenario is same as OP refer to suggestion given

if its different, post scenario as a separate thread with sample data and required output

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources