Insert query hanging for no obvious reason

[aolb]

I have written a procedure that generates queries and executes them. The problem is that I have 2 insert queries that are generated that insert into the same table and are basically the same except I that am using a different view for each.

The first insert query, which is the Computers query works fine but the second, which is the Printers query is hanging for some reason.

I have determined that if I run the Printers query as a select query it returns the expected results and I run it as an insert query with the “DuplicateAssetNo is null” commented out it updates the table as expected.

There are no transaction issues and the order that I run them makes no difference

Any ideas would be greatly welcome.

Thanks


1. Computer Query that works

insert into tblAssetDataSourceIdentifier(
ADSI_A_Id, ADSI_AssetIdentifier, ADSI_Datasource, ADSI_ValidateDT)

select A_Id, AIR2_Id, 'AIR', 'Feb 1 2005 9:18AM'
from tblAsset
left join tblAssetDataSourceIdentifier on ADSI_A_Id = A_Id
inner join AIR.dbo.vZZComputer on A_AssetNo = AssetNo
left join (
select dbo.fUnknown(AssetNo) as DuplicateAssetNo,
count(dbo.fUnknown(AssetNo)) as DuplicateAssetNoCount
from AIR.dbo.vZZComputer
where dbo.fUnknown(AssetNo) <> 'Unknown'
group by dbo.fUnknown(AssetNo)
having count(dbo.fUnknown(AssetNo)) > 1) as DuplicateAssetNo
on DuplicateAssetNo = AssetNo
left join (
select dbo.fUnknown(SerialNo) as DuplicateSerialNo,
count(dbo.fUnknown(SerialNo)) as DuplicateSerialNoCount
from AIR.dbo.vZZComputer
where dbo.fUnknown(SerialNo) <> 'Unknown'
group by dbo.fUnknown(SerialNo)
having count(dbo.fUnknown(SerialNo)) > 1) as DuplicateSerialNo
on DuplicateSerialNo = SerialNo

where ADSI_Id is null and dbo.fUnknown(AssetNo) <> 'Unknown' and
DuplicateAssetNo is null and DuplicateSerialNo is null


2. Printer Query that hangs

insert into tblAssetDataSourceIdentifier(
ADSI_A_Id, ADSI_AssetIdentifier, ADSI_Datasource, ADSI_ValidateDT)
select A_Id, AIR2_Id, 'AIR', 'Feb 1 2005 9:19AM'
from tblAsset
left join tblAssetDataSourceIdentifier on ADSI_A_Id = A_Id
inner join AIR.dbo.vZZPrinters on A_AssetNo = AssetNo
left join (
select dbo.fUnknown(AssetNo) as DuplicateAssetNo,
count(dbo.fUnknown(AssetNo)) as DuplicateAssetNoCount
from AIR.dbo.vZZPrinters
where dbo.fUnknown(AssetNo) <> 'Unknown'
group by dbo.fUnknown(AssetNo)
having count(dbo.fUnknown(AssetNo)) > 1) as DuplicateAssetNo
on DuplicateAssetNo = AssetNo
left join (
select dbo.fUnknown(SerialNo) as DuplicateSerialNo,
count(dbo.fUnknown(SerialNo)) as DuplicateSerialNoCount
from AIR.dbo.vZZPrinters
where dbo.fUnknown(SerialNo) <> 'Unknown'
group by dbo.fUnknown(SerialNo)
having count(dbo.fUnknown(SerialNo)) > 1) as DuplicateSerialNo
on DuplicateSerialNo = SerialNo

where ADSI_Id is null and dbo.fUnknown(AssetNo) <> 'Unknown' and
DuplicateAssetNo is null and DuplicateSerialNo is null

 

[vongrunt]

So far I've seen similar problems caused by:

- blocks/deadlocks (OK, transaction issues)
- parallelism in execution plan (multi-CPU/hyperthreading CPU server)
- automatic growing of .mdf/.ldf files (happens rarely)

Without extra digging it's hard to say. When that happens (2nd query hangs indefinitely), is there any sign of blocking visible in EM (Management -> Current Activity)?

 

[aolb]

- blocks/deadlocks (OK, transaction issues) Nope not the problem here
- automatic growing of .mdf/.ldf files (happens rarely) Nope not the issue here

- parallelism in execution plan (multi-CPU/hyperthreading CPU server)

I will look the execution plan to see if it sheds any light on the problem but I doubt it will.

Thank for your thoughts, anymore thoughts are welcome

 

[SQLSister]

You say you are running the insert against a view. Perhaps the printers query is not an updateable view or you are trying to insert into two tables simultaneously which can't be done. I personally would never use a view for an insert (or update). It is better to write the insert statements against the table(s) directly.

Questions about posting. See faq183-874
Click here to learn Ways to help with Tsunami Relief

 

[aolb]

The insert query will not change the results in the source view. The view is in another database and the data there is static.

I am only trying to update one table and there is nothing special about this table.

I have the same problem if I truncate the table I am inserting the data into before the insert or if I do the insert when there is already data in it.

A mystery I think...

 

[SQLSister]

So does the user the connection is using have rights to insert into the table?

Questions about posting. See faq183-874
Click here to learn Ways to help with Tsunami Relief

 

[aolb]

yep.

Logged on as the system administrator and have no problems updating the same table if I comment out part of the where clause!

where ADSI_Id is null and dbo.fUnknown(AssetNo) <> 'Unknown' and
-- DuplicateAssetNo is null and
DuplicateSerialNo is null