The Visual Studio 2005 designers decided to have different approaches between configuring a SQL Server DataSource and simply retrieving/refreshing its schema. Refreshing the schema causes a call to SQL Server like
Declare @P1 Int
Exec sp_prepare @P1 Output, 'Test @P1 Int, @P2 NVarChar'
Select @P1
and then
Set FmtOnly On
Exec sp_execute 1, '0', ' '
Set FmtOnly Off
Which is, of course, the prepared-statement version of
Set FmtOnly On
Exec Test 0, ' '
Set FmtOnly Off
No problem, really. It passes in 0 and ' ' no matter what defaults you configure on the DataSource. Which shouldn't matter, of course, since with FmtOnly SQL Server doesn't execute the procedure, it just parses it and does a dry run in order to return the structure of the result set(s) to the client. Of course Visual Studio uses this; it would be horrible to execute a long-running stored procedure, not to mention one with side-effects every time the schema is refreshed. So it doesn't matter whether you pass in defaults or not. Blanks are fine! Good job, no harm, no foul.
Weeeeeeelll, not really.
Observe.
Create Procedure Test
@YYYYMM Character Varying(6)
As
Begin
Declare
@D DateTime
If Len(@YYYYMM) = 6
Set @D = Cast(Right(@YYYYMM, 2) As Character Varying) + '/01/' + Cast(Left(@YYYYMM, 4) As Character Varying)
Select
[And the date is] = @D
End
Go
What can be simpler?
Let's see if it works:
Execute Test '200610'
Yep, returns 2006-10-01 00:00:000, like one would expect. How about some invalid input we so thoughtfully protected against?
Execute Test ' '
No sweat, returns NULL. So what if you were to put in a DataSource in Visual Studio, and refresh the schema? SQL Server gets sent something equivalent to
Set FmtOnly On
Execute Test ' '
Set FmtOnly Off
And what happens?
Msg 241, Level 16, State 1, Procedure Test, Line 17
Conversion failed when converting datetime from character string.
Ugh. So SQL Server is smart enough to actually attempt to execute the conversion, but not smart enough to see that the entire conversion would not be executed with the parameters passed? Even if the resulting value isn't even used? Try it, commenting out the Select doesn't change anything.
So we have a big, reeking bug in SQL Server 2005 SP1 here. Which only surfaces because Visual Studio 2005 does something needlessly dumb (why, oh why, oh why was it too much trouble to pass in defaults?!) Which of course cost me an hour and a half this afternoon. Which of course I shouldn't complain too much about since it keeps me gainfully employed.
And yes, it is a combination of errors:
Set FmtOnly On
Execute Test '200610'
Set FmtOnly Off
works just peachy.
Anyway, how to fix this? Sure, you can just change the parameter to an Integer:
Create Procedure Test
@DaysSince1900 Integer
As
Begin
Declare
@D DateTime
Set @Y = Left(@YYYYMM, 4)
Set @M = Right(@YYYYMM, 2)
Set @D = DateAdd(Day, @DaysSince1900, '1/1/1900')
Select
[And the date is] = @D
End
Go
Problem solved, since Visual Studio passes in 0 for Integers on a schema refresh. But let's for the moment assume that several stored procedures and r.a.d. Grids are already set up for that @YYYYMM being a string, and you pretty much as the DBA have to fix on the SQL Server end because there simply isn't enough time to redo things on the front-end. Not that situation would ever occur on my watch *cough*, but let me share how I would solve this on the database end in this purely hypothetical *cough* situation:
Create Procedure Test
@YYYYMM Character Varying(6)
As
Begin
Declare
@M Integer,
@Y Integer,
@D DateTime
Set @Y = Left(@YYYYMM, 4)
Set @M = Right(@YYYYMM, 2)
If Len(@YYYYMM) = 6
Set @D = DateAdd(Month, Abs(Coalesce(@M - 1, 0)), DateAdd(Year, Abs(Coalesce(@Y - 1900, 0)), '1/1/1900'))
Select
[And the date is] = @D
End
Go
This looks more involved than the change actually was, because the @M/@Y deduction was already in place in this purely hypothetical example. It was a single-line replacement. Well, that and a lengthy comment to not touch that line and why. I even managed to avoid being snarky.