Today’s day in SQL, volume II

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.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


%d bloggers like this: