Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
Other Functions
There are two other functions that are presented here as a bonus material (they are not mentioned in the online documentation, but can be very useful in certain situations).
SQL Server has supported the TSEQUAL function since the Sybase days. This function allows you to compare two TIMESTAMP values and find out whether they are equal. Recall that the TIMESTAMP data type tracks the sequence of row changes. If you allow multiple users to read the same row (as you should), you must ensure that only one user can modify the row at a time.
For instance, suppose that you have an application for selling tickets to a football game. If Joe and Jane come to the website at the same time, they should both see that Seat 20 in Section 20 is available for purchase. However, if Joe buys the ticket for this seat, Jane should get an error when she tries to buy the same seat only a few milliseconds later. So how can TSEQUAL help in such a situation? If the seat table has a TIMESTAMP column, you should store the TIMESTAMP value in a variable each time a seat is displayed as "available". Then, when a customer clicks the "purchase" button, you can read the TIMESTAMP value for the same row again and compare it to the value of your variable. If the two values are different, someone else has purchased the seat and you should return an error.
The following example compares two TIMESTAMP values and returns an error if the two values are different:
DECLARE @sequence_tracker TIMESTAMP,
@sequence_tracker1 TIMESTAMP
SELECT @sequence_tracker = 0x0000000000000069
SELECT @sequence_tracker1 = 0x000000000000006A
IF TSEQUAL(@sequence_tracker, @sequence_tracker1)
BEGIN
SELECT 'no changes'
END
Results:
Server: Msg 532, Level 16, State 2, Line 8 The timestamp (changed to 0x0000000000000069) shows that the row has
been updated by another user.
The other function introduced here is fn_get_sql. This function is available only after you install service pack 3 for SQL Server 2000, so if you don't have this service pack installed, the example shown shortly won't work. Furthermore, only the members of the SYSADMIN fixed server role have permission to use this function.
Fn_get_sql allows you to get the full text of the query executed by a particular connection, and can be very useful for troubleshooting performance issues. This function is similar to the output of DBCC INPUTBUFFER; however, if your queries have more than 255 characters or you have nested stored procedures, DBCC INPUTBUFFER won't return enough information.
Service pack 3 adds new columns to the sysprocesses system table. It is this table that fn_get_sql function can query to get the desired info.
NOTE
You must turn on trace flag 2861 prior to using fn_get_sql.
Before you can use fn_get_sql, you need to get a process handle from sysprocesses for the particular process you want to troubleshoot. To determine the process identifier, you can execute sp_who or sp_who2 system procedures (look for the column named "spid" in the output). Once you have a process id, you can use fn_get_sql to identify the statements executed by the offending process, as follows:
/*return SQL statements executed on current connection */ DECLARE @handle BINARY(20) -- 'spid' in the query below is the process identifier: SELECT @handle = sql_handle FROM master.dbo.sysprocesses WHERE spid = 51 SELECT * FROM ::fn_get_sql(@handle)
Results:
|
dbid |
ObjectId |
number |
encrypted |
Text |
|
NULL |
NULL |
NULL |
0 |
DECLARE @handle BINARY(20) -- 'spid' in the query below is the process identifier: SELECT @handle = sql_handle FROM sysprocesses WHERE spid = 51 SELECT * FROM ::fn_get_sql(@handle) |
As you can tell, the text column in this output contains the SQL statements we just executed.