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.
