Monday, March 19, 2012

Error 7306 when trying to do an update on a linked server

Here is my query run on one server against a Linked server:
UPDATE [Linked-Name].dbname.dbo.[Table name]
SET TimeStamp = '1900-01-01 00:00:00'
WHERE ISDATE(TimeStamp) = 0
I get the following error message:
Could not open table '"dbname"."dbo"."Table name"' from OLE DB provider
'SQLOLEDB'. The provider could not support a row lookup position. 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' IOpenRowset::OpenRowset
returned 0x80040e21: [PROPID=DBPROP_BOOKMARKS VALUE=True
STATUS=DBPROPSTATUS_CONFLICTING], [PROPID=DBPROP_COMMANDTIMEOUT
VALUE=600 STATUS=DBPROPSTATUS_OK], [PROPID=Unknown PropertyID
VALUE=True STATUS=DBPROPSTATUS_OK], [PROPID=DBPROP_IRowsetLocate
VALUE=True STATUS=DBPROPSTATUS_CONFLICTING],
[PROPID=DBPROP_IRowsetChange VA...
Please help!
Thanks.What platform is the linked server?
<ryan_willow@.hotmail.com> wrote in message
news:1150831576.021477.128570@.p79g2000cwp.googlegroups.com...
> Here is my query run on one server against a Linked server:
> UPDATE [Linked-Name].dbname.dbo.[Table name]
> SET TimeStamp = '1900-01-01 00:00:00'
> WHERE ISDATE(TimeStamp) = 0
> I get the following error message:
> Could not open table '"dbname"."dbo"."Table name"' from OLE DB provider
> 'SQLOLEDB'. The provider could not support a row lookup position. 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' IOpenRowset::OpenRowset
> returned 0x80040e21: [PROPID=DBPROP_BOOKMARKS VALUE=True
> STATUS=DBPROPSTATUS_CONFLICTING], [PROPID=DBPROP_COMMANDTIMEOUT
> VALUE=600 STATUS=DBPROPSTATUS_OK], [PROPID=Unknown PropertyID
> VALUE=True STATUS=DBPROPSTATUS_OK], [PROPID=DBPROP_IRowsetLocate
> VALUE=True STATUS=DBPROPSTATUS_CONFLICTING],
> [PROPID=DBPROP_IRowsetChange VA...
> Please help!
> Thanks.
>|||SQL Server 2000.
I believe that this is the problem:
http://msdn.microsoft.com/library/d...serr_2_4gqh.asp
i.e. UPDATE, and DELETE statements are not supported on the remote
table if the table does not have a unique index defined on it.
So now I'm here:
I've done DELETEs remotely and had no problem, only errors on UPDATE.
I don't want to modify this established database by defining unique
indices.
How can I get around this?
Mike C# wrote:
> What platform is the linked server?
> <ryan_willow@.hotmail.com> wrote in message
> news:1150831576.021477.128570@.p79g2000cwp.googlegroups.com...|||ryan_willow@.hotmail.com wrote:
> SQL Server 2000.
> I believe that this is the problem:
> http://msdn.microsoft.com/library/d...serr_2_4gqh.asp
> i.e. UPDATE, and DELETE statements are not supported on the remote
> table if the table does not have a unique index defined on it.
> So now I'm here:
> I've done DELETEs remotely and had no problem, only errors on UPDATE.
> I don't want to modify this established database by defining unique
> indices.
> How can I get around this?
> Mike C# wrote:
What is version of MDAC?
and Service Pack?
We have also a problem like this and after updating to latest version
problem was solved
Regards
Amish Shah

No comments:

Post a Comment