|
|
Posted by Erland Sommarskog on 03/13/06 01:15
Altemir (david.altemir@gmail.com) writes:
> I'm only working with one instance of both databases. Don't know what
> you mean by four-part notation.
An example of four-part notation:
SELECT ... FROM Server.db.dbo.tbl
In this query, we retrieve data from a second server, which could be
SQL Server, Oracle or whatever. (But since it says dbo, it's likely to
be SQL Server or Sybase.
>
> Here is the SQL statement that is generating the "'SQLOLEDB' was unable
> to begin a distributed transaction" error as I stated before. As you
> can see, I am attempting to perform an UPDATE of a table in database
> "MFG" based on the recordset generated by joining three tables
> together:
>
>
> UPDATE MFG.dbo.PURC_ORDER_LINE SET MFG.dbo.PURC_ORDER_LINE.USER_1 =
> 'Vendor'
> WHERE ROWID IN
> (SELECT MFG.dbo.PURC_ORDER_LINE.ROWID FROM
> Status.dbo.tblPOBaselineDueDates INNER JOIN MFG.dbo.PURC_ORDER_LINE ON
> Status.dbo.tblPOBaselineDueDates.PURC_ORDER_ID =
> MFG.dbo.PURC_ORDER_LINE.PURC_ORDER_ID AND
> Status.dbo.tblPOBaselineDueDates.LINE_NO =
> MFG.dbo.PURC_ORDER_LINE.LINE_NO INNER JOIN
> Status.dbo.qryPODueDates ON
> MFG.dbo.PURC_ORDER_LINE.PURC_ORDER_ID =
> Status.dbo.qryPODueDates.PURC_ORDER_ID AND
> MFG.dbo.PURC_ORDER_LINE.LINE_NO = Status.dbo.qryPODueDates.LINE_NO AND
> Status.dbo.tblPOBaselineDueDates.BaselineDueDate =
> Status.dbo.qryPODueDates.CurrentDueDate
> WHERE (MFG.dbo.PURC_ORDER_LINE.USER_1 LIKE 'Aut%'))
Since I am unable to see the forest for all the trees, I rewrote the
query as:
UPDATE MFG.dbo.PURC_ORDER_LINE
SET USER_1 = 'Vendor'
WHERE ROWID IN (SELECT MFG.ROWID
FROM Status.dbo.tblPOBaselineDueDates DD
JOIN MFG.dbo.PURC_ORDER_LINE MFG
ON DD.PURC_ORDER_ID = MFG.PURC_ORDER_ID
AND DD.LINE_NO = MFG.LINE_NO
JOIN Status.dbo.qryPODueDates qDD
ON MFG.PURC_ORDER_ID = qDD.PURC_ORDER_ID
AND MFG.LINE_NO = qDD.LINE_NO
AND DD.BaselineDueDate = qDD.CurrentDueDate
WHERE MFG.USER_1 LIKE 'Aut%')
Using aliases makes query much easier to read.
There is no apparent reference to any linked server. However, one table
in the Status database is called tbl-something and another qry-something.
Could the latter be a view that includes a table from a linked server?
--
Erland Sommarskog, SQL Server MVP, esquel@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
Navigation:
[Reply to this message]
|