I'm getting an inexplicable error with an ADO command in VB6 run against a SQL Server 2005 database.

Here's some code to demonstrate the problem:

Sub ADOCommand()
   Dim Conn As ADODB.Connection
   Dim Rs As ADODB.Recordset
   Dim Cmd As ADODB.Command

   Dim ErrorAlertID As Long
   Dim ErrorTime As Date

   Set Conn = New ADODB.Connection
   Conn.ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Initial Catalog=database;Data Source=server"
   Conn.CursorLocation = adUseClient
   Conn.Open

   Set Rs = New ADODB.Recordset
   Rs.CursorType = adOpenStatic
   Rs.LockType = adLockReadOnly

   Set Cmd = New ADODB.Command
   With Cmd
      .Prepared = False
      .CommandText = "ErrorAlertCollect"
      .CommandType = adCmdStoredProc
      .NamedParameters = True
      .Parameters.Append .CreateParameter("@ErrorAlertID", adInteger, adParamOutput)
      .Parameters.Append .CreateParameter("@CreateTime", adDate, adParamOutput)
      Set .ActiveConnection = Conn
      Rs.Open Cmd

      ErrorAlertID = .Parameters("@ErrorAlertID").Value
      ErrorTime = .Parameters("@CreateTime").Value
   End With
   Debug.Print Rs.State ' Shows 0 - Closed
   Debug.Print Rs.RecordCount ' Of course this fails since the recordset is closed
End Sub

So this code was working not too long ago but now it's failing on the last line with the error:

Run-time error '3704': Operation is not allowed when the object is closed

Why is it closed? I just opened it and the SP returns rows.

I ran a trace and this is what the ADO library is actually submitting to the server:

declare @p1 int
set @p1=1
declare @p2 datetime
set @p2=''2010-04-22 15:31:07:770''
exec ErrorAlertCollect @ErrorAlertID=@p1 output,@CreateTime=@p2 output
select @p1, @p2

Running this as a separate batch from my query editor yields:

Msg 102, Level 15, State 1, Line 4
Incorrect syntax near '2010'.

Of course there's an error. Look at the double single quotes in there. What the heck could be causing that? I tried using adDBDate and adDBTime as data types for the date parameter, and they give the same results.

When I make the parameters adParamInputOutput, then I get this:

declare @p1 int
set @p1=default
declare @p2 datetime
set @p2=default
exec ErrorAlertCollect @ErrorAlertID=@p1 output,@CreateTime=@p2 output
select @p1, @p2

Running that as a separate batch yields:

Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'default'.
Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'default'.

What the heck? SQL Server doesn't support this kind of syntax. You can only use the DEFAULT keyword in the actual SP execution statement.

I should note that removing the extra single quotes from the above statement makes the SP run fine.

... Oh my. I just figured it out. I guess it's worth posting anyway.