OPENROWSET Update Error

• brij_rauthan

  SSCommitted

  Points: 1521

  More actions

  August 8, 2006 at 9:13 am

  #115020

  Hi guys

  I can insert using OPENROWSET with::

  insert into OPENROWSET

  ('SQLOLEDB','xxx.xxx.xx.xxx';'user';'PWD',

  'Select * from MyDB.dbo.TEST'

  )

  select * from Mytable

  But when I try to update i am getting error:::

  Update OPENROWSET ('SQLOLEDB','xxx.xxx.xx.xxx';'user';'PWD','Select * from MyDB.dbo.TEST')

  SET myCol = 1234

  WHERE mycol=2222

  Server: Msg 7320, Level 16, State 2, Line 1

  Could not execute query against OLE DB provider 'SQLOLEDB'. The provider could not support a required row lookup interface. The provider indicates that conflicts occurred with other properties or requirements.

  [OLE/DB provider returned message: Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.]

  OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ICommandText::Execute returned 0x80040e21: Select * from CDE2.dbo.TEST[PROPID=DBPROP_IRowsetLocate VALUE=True STATUS=DBPROPSTATUS_CONFLICTING], [PROPID=DBPROP_BOOKMARKS VALUE=True STATUS=DBPROPSTATUS_CONFLICTING]].

  Can somebody please help....

  thanks]

  Brij

• brij_rauthan

  SSCommitted

  Points: 1521

  More actions

  August 9, 2006 at 4:48 am

  #654022

  ahy help will be appreciated...

  thanks

  Brij

• David Burrows

  SSC Guru

  Points: 65142

  More actions

  August 9, 2006 at 7:37 am

  #654068

  Check the table that is being updated has a uniqure index on it

  Plus I thought updates had to be like

  UPDATE OPENROWSET('SQLOLEDB','xxx.xxx.xx.xxx';'user';'PWD',

  'SELECT myCol FROM MyDB.dbo.TEST WHERE mycol=2222')

  SET myCol = 1234

  or

  UPDATE OPENROWSET('SQLOLEDB','xxx.xxx.xx.xxx';'user';'PWD',

  'SELECT * FROM MyDB.dbo.TEST WHERE mycol=2222')

  SET myCol = 1234

  however I could be wrong

  Far away is close at hand in the images of elsewhere.
  Anon.