Adventures in Software Development … by David Young
Back in 2013, I was on the winning team for the City of Houston’s Open Innovation Hackathon, and we produced a replacement for the previous fee schedule. The new fee schedule is now on the public City of Houston website!
Update: The new fee schedule, which was at http://www.houstontx.gov/finance/feeschedule/, is not available as of 2015-09-10.
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.
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);
}