by Debugging SQL Server stored procedures is normally a straightforward operation. Even so, it’s possible to be taken down the wrong path by the SQL Profiler and SQL Server Management Studio.
Assume a web page is receiving an exception calling a stored procedure that accepts a datetime field as a parameter. A natural thing to do to debug the scenario is to turn on the SQL Profiler, capture the SQL being executed, and analyze it. Doing so might yield a bit of SQL like the following:
declare @p6 int
set @p6=NULL
exec sp_executesql N’EXEC @RETURN_VALUE = [dbo].[spSelectEmployeeData] @Role = @p0, @InputDate = @p1, @EmployeeNbr = @p2′,N’@p0 varchar(15),@p1 datetime,@p2 char(9),@RETURN_VALUE int output’,
@p0=’EMPLOYEE’,@p1=’2010-09-15 17:18:00.9770000′,@p2=’123123123′,@RETURN_VALUE=@p6 output
select @p6
After considering this for a moment you might think, “That looks alright, why don’t I run it and see what it does from SSMS, thus eliminating the web page and application layer from the call?”
Sadly running this SQL will yield an exception:
Error converting data type varchar to datetime
Resolving the exception is simple enough. Simply remove the trailing spaces from the milliseconds portion of the date parameter supplied to the stored procedure. Now everything works fine.
The logical conclusion at this point is that the web application is passing an unacceptable value to the stored procedure. This thought makes sense because SQL datetime fields have 3 digits of precision when it comes to milliseconds, whereas the new datetime2 data type has 7.
Furthermore, if you change the datetime parameter in the SQL above to a datetime2, the query works as expected.
Now throw into the equation the fact that the web application has been running successfully for some years. The next logical leap is to question recent changes to ADO.NET or the SQL Server, particularly if the error is observed in proximity to “Patch Tuesday”. What’s happened to my servers?! You may cry.
Sadly, this is a red herring. The SQL Profiler displays datetime parameters with 7 digits worth of milliseconds regardless of what was sent to SQL Server by ADO. However, because datetime is the type of the parameter expected by the stored procedure, the wrapping SQL execution code does not use datetime2. This causes the conversion error which has nothing to do with the original problem.
So why was the web page failing? An application code error that took 10 minutes to isolate and fix, once the distraction of SQL Profiler’s improper formatting of the datetime parameter was removed from consideration.
