October 2007 Blog Posts

Conditional INSERT/UPDATE Race Condition

Conditional INSERT/UPDATE Race Condition     I often see conditional INSERT/UPDATE code like: CREATE PROCEDURE dbo.Insert_Or_Update_Foo       @ID int,       @Bar int AS   ...

Use Caution with Explicit Transactions in Stored Procedures

Use Caution with Explicit Transactions in Stored Procedures     Explicit transactions are often used within stored procedures to guarantee all-or-nothing data integrity.  However, a little known fact is that a query timeout will leave the transaction open unless non-default session settings and/or special exception handling are used.  I’ll describe how to protect your application from problems following timeouts and other unexpected errors. Consider the following stored procedure containing an explicit transaction:   ...

SQL Trace Parameter values are not always as they seem

I stumbled across surprising SQL Trace/Profiler behavior I think is worth mentioning.  Parameter values reported in trace RPC starting/completed events are not the values that SQL Server uses to execute the query.  Here’s an example I recently discovered that shows this behavior.   I ran the following C# code to execute parameterized query "SELECT @DateTime" with the parameter value set to October 11, 2007.  The console message verified that SQL Server returned the expected date.    Here is the SQL Profiler trace of the of the SQL:BatchCompleted and RPC:Completed events: I pasted the script from the trace and ran it from a SQL Server Management...

Blog Post #1

Bill Graziano invited me to start a blog on www.sqlteam.com when he spoke at our June 2007 St. Louis SQL Server User Group.  I’ve been active online for over a decade answering questions in the Microsoft SQL Server Newsgroups and truly enjoy helping out in the SQL Server community.   I’d considered blogging before and didn’t want to make the commitment but, after mulling it over for a while, I think spending some of my time blogging is worthwhile. I’m a jack-of-all-trades SQL Server DBA and developer going back to SQL Server 4.21a under Windows NT 3.1.  I expect most of my...