A personal repository of random information in compensation for a fatigued biological computer
Breaded IT » MS Access / SQL Server reminders » SQL Server 2000: Exists as alternative to IN
This query gave this error:
UPDATE Preservations
SET
ReferenceProvided =
CASE WHEN NOT ReferenceProvided IS NULL THEN ', ' ELSE '' END
+ pr.AgencyCode
, AgencyCode = ''
FROM Preservations pr
LEFT OUTER JOIN
(SELECT code, preferredname
FROM crystalcommon.dbo.AllAgenciesFromArchway() ) awag
ON pr.AgencyCode = awag.code
WHERE pr.AgencyCode IS NOT NULL AND awag.code IS NULL
GO
[OLE/DB provider returned message: New transaction cannot enlist in the specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ITransactionJoin::JoinTransaction returned 0x8004d00a].
Msg 7391, Level 16, State 1, Line 1
The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction.
Alternatives:
SELECT pr.AgencyCode
FROM Preservations pr
WHERE pr.AgencyCode IS NOT NULL
AND NOT pr.AgencyCode IN (
SELECT code, preferredname
FROM crystalcommon.dbo.AllAgenciesFromArchway()
)
Gives this:
Msg 116, Level 16, State 1, Line 1
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.
IN clause didn't like having annother where clause but we can:
SELECT pr.AgencyCode
FROM Preservations pr
WHERE pr.AgencyCode IS NOT NULL
AND NOT EXISTS (
SELECT code, preferredname
FROM crystalcommon.dbo.AllAgenciesFromArchway() awag
WHERE pr.AgencyCode = awag.code
)
Now try in the original update:
UPDATE Preservations
SET
ReferenceProvided =
CASE WHEN NOT ReferenceProvided IS NULL THEN ', ' ELSE '' END
+ pr.AgencyCode
, AgencyCode = ''
FROM Preservations pr
WHERE pr.AgencyCode IS NOT NULL
AND NOT EXISTS (
SELECT code, preferredname
FROM crystalcommon.dbo.AllAgenciesFromArchway() awag
WHERE pr.AgencyCode = awag.code
)
Bugger:
[OLE/DB provider returned message: New transaction cannot enlist in the specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ITransactionJoin::JoinTransaction returned 0x8004d00a].
Msg 7391, Level 16, State 1, Line 1
The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction.