≡ Menu

SQL Server Script to Check for Blocking Queries

Even though SQL Server 2000 is no longer officially supported by Microsoft, quite a few SQL 2K databases still exist in production environments. One that had been giving me trouble for a couple of days was intermittently causing a web app to fail, and the error message indicated that the failure due to either a SQL timeout or an unresponsive server. This problem was not constant, but happened every few minutes.

Several other apps that accessed the same database as the one that was only working some of the time. Each app had its own set of tables, so I figured there must be exclusive locks being set on the tables that the problem app used. To determine the cause of this problem, I had to find out what process was locking the table.

This being SQL 2000, I could not use this script, which works on SQL 2008:

USE master
GO
SELECT
db.name DBName,
tl.request_session_id,
wt.blocking_session_id,
OBJECT_NAME(p.OBJECT_ID) BlockedObjectName,
tl.resource_type,
h1.TEXT AS RequestingText,
h2.TEXT AS BlockingTest,
tl.request_mode
FROM sys.dm_tran_locks AS tl
INNER JOIN sys.databases db ON db.database_id = tl.resource_database_id
INNER JOIN sys.dm_os_waiting_tasks AS wt ON tl.lock_owner_address = wt.resource_address
INNER JOIN sys.partitions AS p ON p.hobt_id = tl.resource_associated_entity_id
INNER JOIN sys.dm_exec_connections ec1 ON ec1.session_id = tl.request_session_id
INNER JOIN sys.dm_exec_connections ec2 ON ec2.session_id = wt.blocking_session_id
CROSS APPLY sys.dm_exec_sql_text(ec1.most_recent_sql_handle) AS h1
CROSS APPLY sys.dm_exec_sql_text(ec2.most_recent_sql_handle) AS h2

For SQL 2000, try this one:

USE master
GO
EXEC master.dbo.sp_lock
GO
EXEC master.dbo.sp_who2
GO
SELECT req_spid AS 'spid', 
DB_NAME(rsc_dbid) AS 'Database', 
OBJECT_NAME(rsc_objid) AS 'Name', 
rsc_indid AS 'Index', 
rsc_text AS 'Description', 
ResourceType = CASE WHEN rsc_type = 1 THEN 'NULL Resource'
	WHEN rsc_type = 2 THEN 'Database' 
	WHEN rsc_type = 3 THEN 'File'
	WHEN rsc_type = 4 THEN 'Index' 
	WHEN rsc_type = 5 THEN 'Table' 
	WHEN rsc_type = 6 THEN 'Page'
	WHEN rsc_type = 7 THEN 'Key'
	WHEN rsc_type = 8 THEN 'Extent'
	WHEN rsc_type = 9 THEN 'RID (Row ID)'
	WHEN rsc_type = 10 THEN 'Application'
	ELSE 'Unknown'
END, 
Status = CASE WHEN req_status = 1 THEN 'Granted' 
	WHEN req_status = 2 THEN 'Converting' 
	WHEN req_status = 3 THEN 'Waiting' 
	ELSE 'Unknown' 
END, 
OwnerType = CASE WHEN req_ownertype = 1 THEN 'Transaction' 
	WHEN req_ownertype = 2 THEN 'Cursor' 
	WHEN req_ownertype = 3 THEN 'Session' 
	WHEN req_ownertype = 4 THEN 'ExSession' 
	ELSE 'Unknown' 
END, 
LockRequestMode = CASE WHEN req_mode = 0 THEN 'No access ' 
	WHEN req_mode = 1 THEN 'Sch-S (Schema stability)' 
	WHEN req_mode = 2 THEN 'Sch-M (Schema modification)'
	WHEN req_mode = 3 THEN 'S (Shared)' 
	WHEN req_mode = 4 THEN 'U (Update)' 
	WHEN req_mode = 5 THEN 'X (Exclusive)' 
	WHEN req_mode = 6 THEN 'IS (Intent Shared)' 
	WHEN req_mode = 7 THEN 'IU (Intent Update)' 
	WHEN req_mode = 8 THEN 'IX (Intent Exclusive)' 
	WHEN req_mode = 9 THEN 'SIU (Shared Intent Update)'
	WHEN req_mode = 10 THEN 'SIX (Shared Intent Exclusive)'
	WHEN req_mode = 11 THEN 'UIX (Update Intent Exclusive)' 
	WHEN req_mode = 12 THEN 'BU. (Bulk operations)' 
	WHEN req_mode = 13 THEN 'RangeS_S' 
	WHEN req_mode = 14 THEN 'RangeS_U' 
	WHEN req_mode = 15 THEN 'RangeI_N' 
	WHEN req_mode = 16 THEN 'RangeI_S' 
	WHEN req_mode = 17 THEN 'RangeI_U' 
	WHEN req_mode = 18 THEN 'RangeI_X' 
	WHEN req_mode = 19 THEN 'RangeX_S' 
	WHEN req_mode = 20 THEN 'RangeX_U' 
	WHEN req_mode = 21 THEN 'RangeX_X' 
	ELSE 'Unknown' 
END 
FROM master.dbo.syslockinfo 
GO
SELECT * FROM master.dbo.sysprocesses

This query generates several tables, and the last table is most helpful. The “blocked” column will show how many exclusive locks are being held by the process denoted by the number in the “spid” column.

If you have DBA access, you can run “DBCC INPUTBUFFER(spid)”, substituting the spid number found above, and the text of the troublemaking query will be returned.

Converting a SQL DATETIME to a JavaScript Date

It should come as no surprise to anyone who has developed for multiple browsers that each one has its own quirks when it comes to enforcing standards, whether they be CSS, HTML5, or even JavaScript. I have been working on some JavaScript that will take a date (in the DATETIME format) from SQL Server and create a new JavaScript Date object using this date. In doing most web development, I have been using Chrome to test the pages first because I find Chrome’s developer tools much better than those with Internet Explorer, even though the site will eventually have to be compatible with IE 8+.

Apparently, when taking a SQL DATETIME (which is in the format “yyyy-mm-dd hh:mm:ss.ms”) string and creating a new JS Date, Chrome will take it directly, but IE is more strict. IE enforces the JavaScript standard, and simply doesn’t recognize the DATETIME format as being a valid date. The way I solved this was to parse the DATETIME string by splitting it into arrays and instantiating the JS Date object by using the “new Date(year, month, day, hour, minute, second, millisecond)” syntax.

Below is a function that will take a string in the SQL DATETIME format and will return a JS Date object:

function sqlToJsDate(sqlDate){
    //sqlDate in SQL DATETIME format ("yyyy-mm-dd hh:mm:ss.ms")
    var sqlDateArr1 = sqlDate.split("-");
    //format of sqlDateArr1[] = ['yyyy','mm','dd hh:mm:ms']
    var sYear = sqlDateArr1[0];
    var sMonth = (Number(sqlDateArr1[1]) - 1).toString();
    var sqlDateArr2 = sqlDateArr1[2].split(" ");
    //format of sqlDateArr2[] = ['dd', 'hh:mm:ss.ms']
    var sDay = sqlDateArr2[0];
    var sqlDateArr3 = sqlDateArr2[1].split(":");
    //format of sqlDateArr3[] = ['hh','mm','ss.ms']
    var sHour = sqlDateArr3[0];
    var sMinute = sqlDateArr3[1];
    var sqlDateArr4 = sqlDateArr3[2].split(".");
    //format of sqlDateArr4[] = ['ss','ms']
    var sSecond = sqlDateArr4[0];
    var sMillisecond = sqlDateArr4[1];
    
    return new Date(sYear,sMonth,sDay,sHour,sMinute,sSecond,sMillisecond);
}

SQL Script to Find Text in all Database Tables and Views

I get new applications to work on fairly regularly, and I have found that almost none of them have databases that are normalized to the third normal form. (Shocking, I know.) One that I recently inherited appears to store email addresses in multiple tables instead of in a single table with the user’s other information. This particular gem was sending out emails even to people who thought they had unsubscribed from its notifications because of the classic update anomalies that can result in storing data in multiple places.

I decided to piece together a script for looking at all text columns in the MS SQL Server database to see where else this user’s email address might be stored. Here’s what I came up with.

-- Set database to search
USE dbname
GO
------------------------------------------------------------------------------------------------------------------------------------
-- Set test value here and set @SearchViews to 1 if views are to be included - 0 if not; no other code below changes
DECLARE @TestValue NVARCHAR(MAX) = 'text to search'
DECLARE @SearchViews BIT = 0
------------------------------------------------------------------------------------------------------------------------------------
DECLARE @SQL NVARCHAR(MAX)
DECLARE @varSQL NVARCHAR(MAX)
DECLARE @WhereClause NVARCHAR(MAX)
DECLARE @TableName NVARCHAR(128)
DECLARE @varTableName NVARCHAR(128)
DECLARE @TableSchema NVARCHAR(128)
DECLARE @ColumnName NVARCHAR(128)
	
DECLARE @SchemaObjects TABLE (
	TABLE_NAME NVARCHAR(128), 
	TABLE_SCHEMA NVARCHAR(128)
)

INSERT INTO @SchemaObjects
SELECT TABLE_NAME, TABLE_SCHEMA FROM INFORMATION_SCHEMA.TABLES
	WHERE TABLE_TYPE = 'BASE TABLE'

IF @SearchViews = 1
BEGIN
	INSERT INTO @SchemaObjects
	SELECT TABLE_NAME, TABLE_SCHEMA FROM INFORMATION_SCHEMA.VIEWS
END

DECLARE TableCursor CURSOR FAST_FORWARD FOR
SELECT TABLE_NAME, TABLE_SCHEMA FROM @SchemaObjects

OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @TableName, @TableSchema
WHILE @@FETCH_STATUS = 0
BEGIN
	SET @WhereClause = ''

	DECLARE ColumnCursor CURSOR FAST_FORWARD FOR
	SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS
		WHERE TABLE_NAME = @TableName AND TABLE_SCHEMA = @TableSchema AND ((DATA_TYPE IN ('CHAR','NCHAR','VARCHAR','NVARCHAR')) AND (CHARACTER_MAXIMUM_LENGTH IS NOT NULL) AND (CHARACTER_MAXIMUM_LENGTH >= LEN(@TestValue)) OR DATA_TYPE IN ('TEXT','NTEXT'))
	OPEN ColumnCursor
	FETCH NEXT FROM ColumnCursor INTO @ColumnName
	WHILE @@FETCH_STATUS = 0
	BEGIN
		IF LEN(@WhereClause) > 0 SET @WhereClause = @WhereClause + ' OR '
		SET @WhereClause = @WhereClause + '(CONVERT(NVARCHAR,[' + @ColumnName + ']) LIKE ''%' + @TestValue + '%'')'
		FETCH NEXT FROM ColumnCursor INTO @ColumnName
	END
	CLOSE ColumnCursor
	DEALLOCATE ColumnCursor	
	IF LEN(@WhereClause) > 0
	BEGIN		
		SET @varTableName = REPLACE(@TableName,' ','_') 
		SET @varSQL = 'DECLARE @' + @varTableName + CHAR(10) + ' TABLE (' + CHAR(10) 
		SELECT @varSQL = @varSQL + ' ' + sc.name + ' ' + 
		CASE WHEN st.name = 'TIMESTAMP' THEN 'DATETIME' ELSE UPPER(st.name) END +
		CASE WHEN st.name IN ('VARCHAR','NVARCHAR','CHAR','NCHAR') THEN '(' + 
		CASE WHEN sc.[length] > 4000 THEN 'MAX' ELSE CAST(sc.[length] AS NVARCHAR) END + ') ' ELSE ' ' END +
		CASE WHEN sc.isnullable = 1 THEN 'NULL' ELSE 'NOT NULL' END + ',' + CHAR(10)
		FROM sysobjects so
		JOIN syscolumns sc ON sc.id = so.id
		JOIN systypes st ON st.xusertype = sc.xusertype
		WHERE so.name = @TableName
		ORDER BY sc.colid
 
		SELECT @varSQL = SUBSTRING(@varSQL,1,LEN(@varSQL) - 2) + CHAR(10) + ');' + CHAR(10)
		
		SET @varSQL = @varSQL + 'INSERT INTO @' + @varTableName + CHAR(10) + 'SELECT * FROM [' + @TableSchema + '].[' + @TableName + ']' + CHAR(10) + 'WHERE ' + @WhereClause + ';' + CHAR(10)
		SET @varSQL = @varSQL + 'IF EXISTS(SELECT * FROM @' + @varTableName + ')' + CHAR(10) + 'SELECT ''[' + @TableSchema + '].[' + @TableName + ']'' AS [Table Schema/Table Name], * FROM @' + @varTableName + ';'
		SET @SQL = 'SELECT ''[' + @TableSchema + '].[' + @TableName + ']'' AS [Table Schema/Table Name], * FROM [' + @TableSchema + '].[' + @TableName + '] WHERE ' + @WhereClause		
		PRINT @SQL
		
		PRINT @varSQL
		EXEC (@varSQL)
	END
	FETCH NEXT FROM TableCursor INTO @TableName, @TableSchema
END
CLOSE TableCursor
DEALLOCATE TableCursor

Use Mr. CSV Transformer to Generate MongoDB Insert Statements

Building on the Mr. CSV Transformer project that my team developed for the recent Houston Hackathon, I have added the ability to convert the CSV data into MongoDB.

Mr. CSV Transformer with MongoDB functionality

Mr. CSV Transformer with MongoDB functionality

This past weekend, over 200 people attended the second annual Open Houston Hackathon. The project I worked on with my team proved to be very interesting, not only because of its focus – making government more efficient through the sharing of purchasing data using the Price History application developed originally by the GSA, and certainly not least because of the two men I had the privilege of collaborating with – but also because half of the project involved developing a tool that could have wide application apart from the Price History application.

The tool that we developed was forked from another similar product called “Mr. Data Converter” that would allow a CSV or tab delimited file to easily be converted into many other formats. The product I worked on has even greater functionality – the exclusion, reordering, and renaming of columns to produce one that can be uploaded into another application. The tool my team produced is called “Mr. CSV Transformer”.

open source data transformation tool

Clever, no? While many tools exist that can do this (such as SSIS) this is a Web-based tool that can easily be used by someone who may not have the time to learn or use these other more complicated tools. This is a purely HTML / JavaScript / jQuery based tool that does not require any special type of Web server, as all the work is done in the front end. No need for node.js, Rails, ColdFusion, etc. Just Apache, IIS, or whatever flavor of Web server you prefer will work just fine. Also, I am currently hosting it from a GitHub page, so you don’t even have to install it yourself.