Posted by Erland Sommarskog on 10/01/64 11:27

laurenq uantrell (laurenquantrell@hotmail.com) writes:
> Whew. That's a hefty amount of code, which seems much more complex
> than:
> "Insert the result of sp_who2 into a table
> Delete everything where BlkBy is empty
> Loop thru this table and insert the DBCC inputbuffer result into
> another table
> Join this last table with sysprocesses"
>
> Thanks for that.
> Is there a way to force some blocking so that I can test it?

In one window:

BEGIN TRANSACTION
CREATE TABLE #tmp (a int NOT NULL)

In other another

SELECT * FROM tempdb..sysobjects

> The next step is to run this every 60 seconds and export the results to
> a text file using bcp...

BCP with queryout would work in theory, but queryout is known to be
troublesome. The fact the column lengths may very from execution to
execution may also be aggrevate things.

You could also create a table that matches the output from aba_lockinfo,
and then run INSERT EXEC to that table. Again, the dynamic field lengths
could cause some problems, but just make your columns wide enough.

But I would rather just run it from OSQL with output directed to a file.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Back to original message]