Blocked transactions - how to identify the sql commands?

Hi SQLServer gurus :)

Just wondering if anyone can give me some info on how to find out the full syntax of the commands executed by the blocked and blocking SPID's in a locking situation.

Using sp_who or sp_who2 will give basic info on the blocked trans (such as DELETE, SELECT etc), but not the actual statement. The blocking spid's command is only showing AWAITING COMMAND.

Not really an urgent problem, but any suggestions appreciated!

Cheers,
MeganDBCC INPUTBUFFER
Displays the last statement sent from a client to Microsoft SQL Server.

Syntax
DBCC INPUTBUFFER (spid)

You can use this command to see what is the longest running command which may point to the transaction that is causing the blocking.

DBCC OPENTRAN
Displays information about the oldest active transaction and the oldest distributed and nondistributed replicated transactions, if any, within the specified database. Results are displayed only if there is an active transaction or if the database contains replication information. An informational message is displayed if there are no active transactions.

Syntax
DBCC OPENTRAN
( { 'database_name' | database_id} )
[ WITH TABLERESULTS
[ , NO_INFOMSGS ]
]|||Thanks for your response, achorozy.

Bitwise AND in Where clause?

Is there any way to use Bitwise AND in a WHERE clause?
EG:
SELECT * FROM Directory WHERE DirectoryFeatured & 4
This just gives me an incorrect syntax error? DirectoryFeatured is an int
field.
Regards,
ChrisNot sure how you store the data, but possibly this is what you are looking f
or:
SELECT * FROM Directory WHERE DirectoryFeatured & 4 = 4
Consider normalizing the table and split each attribute to one column (or an
other table, depending
on the data etc).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Chris Ashley" <chris.ashley@.SPAMblueyonder.co.uk> wrote in message news:42e64dc4.0@.entanet
..
> Is there any way to use Bitwise AND in a WHERE clause?
> EG:
> SELECT * FROM Directory WHERE DirectoryFeatured & 4
> This just gives me an incorrect syntax error? DirectoryFeatured is an int
field.
> Regards,
> Chris
>|||In SQL, WHERE clauses return one of TRUE, FALSE or UNKNOWN.
An t-SQL bitwise expression DirectoryFeatured & 4 returns an INT value which
is incompatible with the above set of values. A logical/bitwise AND between
the two expressions, taking each corresponding bit for both expressions.
Thus your expression should take the form of:
SELECT * FROM tbl WHERE col & 4 = <some val>
In general, there is not many instances when one needs to use bitwise
operators in commerical SQL applications. Though it occasionally finds its
place, quite often poorly designed schema is a prime reason for people
resorting to complex bitwise logic on integer typed columns.
Anith|||The statement is not complete. Try (untested):
SELECT * FROM Directory WHERE DirectoryFeatured & 4 = 4
The bitwise operator will return a value (not True or False); you have to
test for the expected value.
As an aside, this is a relatively inefficient thing to do, and it would
probably be best to create an encoding scheme that did not rely on low-level
bit manipulation.
"Chris Ashley" <chris.ashley@.SPAMblueyonder.co.uk> wrote in message
news:42e64dc4.0@.entanet...
> Is there any way to use Bitwise AND in a WHERE clause?
> EG:
> SELECT * FROM Directory WHERE DirectoryFeatured & 4
> This just gives me an incorrect syntax error? DirectoryFeatured is an int
> field.
> Regards,
> Chris
>