May 20, 2010 at 6:21 am
Hi All.
I apologise in advance if this post belongs in another section.
Can anyone suggest why the following code within a trigger is being cancelled by the DTC on our production server?
select Inserted.id, Table2.id
from Inserted
left join LinkedServer.dbo.Table2 on Inserted.id = LinkedServer.dbo.Table2.id
where LinkedServer.dbo.Table2.ID is null
The exact same code works without a problem on our testing server.
The problem seems to be related to the use of NULL values because if i do an inner join the code executes without a problem and if i do a left join where table2.id is not null the code again works without a problem.
I'm really struggling here; has anybody come across a similar issue before?
May 20, 2010 at 6:41 am
You're trying to join on NULL. You can't. NULL does not equal NULL.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 20, 2010 at 6:53 am
I'm not joining on a null; i'm performing a left join on the ID column.
A left join returns all rows from the left table, even if there are no matches in the right table.
By adding the clause "where ID is null" will leave me with only those rows in the left hand table where the ID is not present in the right hand table.
The query works fine on the dev server so i'm sure it isn't a syntax problem.
May 20, 2010 at 7:10 am
I can get the trigger to work by changing the code to the following:
select Inserted.id, Table2.id
from Inserted
left join (select ID from LinkedServer.dbo.Table2) as Table2
on Inserted.id = Table2.id
where LinkedServer.dbo.Table2.ID is null
So if i use a sub query to return the data from the linked server and then join to that it works without a problem.
Not sure if this helps anyone diagnose the problem at all?
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply