It's most likely the server, but I'd gather some information to back yourself up. Use profiler to get a days worth of data, hopefully including a timeout or two. Go...

Have you run Profiler to identify the poor performing processes? If not, I'd do that right away. You say it's a third party vendor. Once you've identified badly behaved procedures,...

Gail gave it to you

SET NOEXEC ON

Put that in front of the query with a semi-colon seperating it from your code. This works against Adventureworks:

SET NOEXEC ON;

SELECT * FROM...

Just a quick clarification, 2008 isn't supposed to ship until Q2 2008. Personally, I wish they'd wait until Q4, 2009.

If you want to parse it from your client, use the code that Gail provided from there.

First try running DBCC CHECKDB(). From the sounds of things, that'll probably fix it. If not, you've got work to do. Can you restore it from backup? If not, you...

And wrap it all in a transaction.

Also, make sure you include the old data values in the update statement. That way, if the second call manages to get the same...

It's already in the script, but it doesn't look right to my (very untrained regarding PIVOT/UNPIVOT) eye.

A deadlock is when two processes are in contention for the...

I think you mis-pasted the results there. You should see a session_id. I know you can identify the blocked session id. From that, you look at the wait_type column to...

Good one. I hadn't thought about looking at the cache.

You're getting wait states, or contention in your system. For a quick & dirty look at what's happening, query the DMV sys.dm_os_waiting_tasks while the suspended status is going on. That'll...

Is it "as unvpt"?

I'm not very acquainted at all with the PIVOT & UNPIVOT operators, so that's a guess.

You can automate the use of SQL Profiler (also called Trace) either by using the correct stored procedures (sp_trace_create is the first of many, look 'em up in the BOL)...

Actually, SCOPE_IDENTITY should work perfectly well in the way you've described it. Is it possible you're getting insert errors, deadlocks or something, because that would cause bad results from SCOPE_IDENTITY.