Hi,

just wandered if anybody could help me out with this.

I've got a view

select distinct 'Insurance' Source, a.RecordID,a.PolicyNumber,isnull(b.AccountNumber,'n/a') ,
*** other columns ******
from Table1 a inner join Table2 b
on a.PolicyNumber = b.PolicyNumber
where a.PolicyNumber <> 'Delete'


union all

select distinct 'Mortgage' Source, a.RecordID + 10000000 RecordID,
*** Same columns again ***
from Table3 a inner join Table2 b
on a.AccountNumber = b.AccountNumber
where NonConFlag = 0 And a.AccountNumber <> 'Delete'

When i select * from this view, sometimes it runs fine and returns 150,000 rows.
But alot of the time it returns

Msg 1205, Level 13, State 2, Line 1
Transaction (Process ID 78) was deadlocked on thread | communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

I've used profiler to try and catch what is actually happening, and all i see is and Error:1205 message.

tried
DBCC TRACEON (3604)

DBCC TRACEON (1204)

and got a load of information saying there were seven nodes in the deadlock - only nothing else is using this view - or these tables to my knowledge!!!

Any help would be appreciated.

Dale

I'll have to dig out the guy who wrote the query and ask him why they use distict.

I'll try the OPTION (MAXDOP 1) and see what happens - i tried WITH (NOLOCK) on both queries in the view definition and the select * from view and it still occurs!!

Ta

Just wanted to ask if i can actually use OPTION (MAXDOP 1) inside a view definition???

When i run just the query (not including alter view) MAXDOP 1 solves the problem

but when i try to create the view with MAXDOP 1 option i get a syntax error near 'OPTION'

any ideas???

Thanks
Dale

the view definition is;

select 'Insurance' Source, a.RecordID,a.PolicyNumber,isnull(b.AccountNumber,'n/a') ,
*** other columns ******
from Table1 a inner join Table2 b
on a.PolicyNumber = b.PolicyNumber
where a.PolicyNumber <> 'Delete'


union all

select 'Mortgage' Source, a.RecordID + 10000000 RecordID,
*** Same columns again ***
from Table3 a inner join Table2 b
on a.AccountNumber = b.AccountNumber
where NonConFlag = 0 And a.AccountNumber <> 'Delete'

OPTION (MAXDOP 1)

The problem is that the application code would have to be changed to add OPTION (MAXDOP 1) - eventually i want to take out all direct SQL from the applications - but i want to do this in one distinct project not bit by bit. That's why i'd like to add the OPTION in the view definition

Aside - i may even end up indexing this view yet so i don't know if that would rule out indexing, using the MAXDOP option