Side Clash in distributed transaction Statement

19 Aug 2006 8:27 PM

Bishoy George

Hi,
I have a distributed transaction statement that updates an SQL server
instance on Windows Server 2003 from another SQL server instance on Windows
XP SP2.

Both servers a linked to each other.

The DB is the same on server and client.

I use SQL Server 2000 with SP3 on both server and client.

Update statement from client side:
update re
set re.[Password] = le.[Password]
from [ServerName].DBName.dbo.Employees as re inner join dbo.Employees as le
on re.EmployeeId = le.EmployeeId
where le.IsUpdated = 1

Update statement from Server Side:
update re
set re.[Password] = le.[Password]
from dbo.Employees as re inner join [ClientName].DBName.dbo.Employees as le
on re.EmployeeId = le.EmployeeId
where le.IsUpdated = 1

When I try to execute the statement from the server side it runs
successfully.

When I try to execute the statement from the client side it gives me this
error:
Server: Msg 7306, Level 16, State 2, Line 1
Could not open table '"DBName"."dbo"."Employees"' 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...

Thank you.
Bishoy

19 Aug 2006 11:01 PM

Erland Sommarskog

Bishoy George (bis***@bishoy.com) writes:

Show quote

> When I try to execute the statement from the server side it runs
> successfully.
>
> When I try to execute the statement from the client side it gives me this
> error:
> Server: Msg 7306, Level 16, State 2, Line 1
> Could not open table '"DBName"."dbo"."Employees"' 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...

Troubleshooting linked-server problems is often very difficult, not the
least because the error messages are very obscure, and also, as in this
case, truncated.

Can you update the password for a single user from the client-side?

Is EmployeeID defined as a primary key in the target table?

If you try:

   UPDATE [ServerName].DBName.dbo.Employees
   SET    Password = (SELECT ls.Psssword
                      FROM   dbo.Employees AS le
                      WHERE re.EmployeeId = le.EmployeeId
                        AND le.IsUpdated = 1)
   FROM [ServerName].DBName.dbo.Employees AS re

this have any better success?

You could also try running a Profiler trace against the target server,
if that gives any clues. (Not very likely, but...)

--
Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

20 Aug 2006 9:21 PM

Bishoy George

Show quote

"Erland Sommarskog" <esq***@sommarskog.se> wrote in message
news:Xns9825A563BF7Yazorman@127.0.0.1...
> Bishoy George (bis***@bishoy.com) writes:
>
>> When I try to execute the statement from the server side it runs
>> successfully.
>>
>> When I try to execute the statement from the client side it gives me this
>> error:
>> Server: Msg 7306, Level 16, State 2, Line 1
>> Could not open table '"DBName"."dbo"."Employees"' 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...
>
> Troubleshooting linked-server problems is often very difficult, not the
> least because the error messages are very obscure, and also, as in this
> case, truncated.
>
> Can you update the password for a single user from the client-side?
>
> Is EmployeeID defined as a primary key in the target table?
>
> If you try:
>
>   UPDATE [ServerName].DBName.dbo.Employees
>   SET    Password = (SELECT ls.Psssword
>                      FROM   dbo.Employees AS le
>                      WHERE re.EmployeeId = le.EmployeeId
>                        AND le.IsUpdated = 1)
>   FROM [ServerName].DBName.dbo.Employees AS re
>
> this have any better success?
>
> You could also try running a Profiler trace against the target server,
> if that gives any clues. (Not very likely, but...)
>
> --
> Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se
>
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

--------------------------------------------------------------------------------

Thanks alot, you helped me very much.
Once I set the primary key in the Server Table, The query run successfully.

Many thanks again.
Bishoy