ADODB open recordset fails / "Operation is not allowed when object is closed"

I have the following UDF in excel which uses ADO to connect to my MSSQL server. There it should execute the scalar udf "D100601RVDATABearingAllow".

For some reason the parameters that I try to append are not send to the sql server. At the server only:

SELECT dbo.D100601RVDATABearingAllow

arrives.

MY EXCEL UDF:

   Function RVDATA(Fastener) As Long

    Dim cnt As ADODB.Connection
    Dim rst As ADODB.Recordset
    Dim Cmd1 As ADODB.Command
    Dim stSQL As String

Const stADO As String = "Provider=SQLOLEDB.1;Data ................"
'----------------------------------------------------------
Set cnt = New ADODB.Connection
 With cnt
    .ConnectionTimeout = 3
    .CursorL开发者_如何学Goocation = adUseClient
    .Open stADO
    .CommandTimeout = 3
 End With
'----------------------------------------------------------
Set Cmd1 = New ADODB.Command
    Cmd1.ActiveConnection = cnt
    Cmd1.CommandText = "dbo.D100601RVDATABearingAllow"
    Cmd1.CommandType = adCmdStoredProc
'----------------------------------------------------------
Set Param1 = Cmd1.CreateParameter("Fastener", adInteger, adParamInput, 5)
Param1.Value = Fastener
Cmd1.Parameters.Append Param1
Set Param1 = Nothing
'----------------------------------------------------------
Set rst = Cmd1.Execute()
RVDATA = rst.Fields(0).Value    
'----------------------------------------------------------
    rst.Close
    cnt.Close
    Set rst = Nothing
    Set cnt = Nothing
'----------------------------------------------------------
End Function

When I use adCmdStoredProc the whole thing fails and in the vba debugger the properties of the recordset has a lot of "Operation is not allowed when object is closed" (may sound a bit different, the message is translated)

When I don't use adCmdStoredProc I get the message that the variable Fastener was not provided.

I think that maybe something is wrong in the way I open the recordset. In other treads I read about using the "SET NOCOUNT ON" option, but that did not work either.

Does anyone have a idea? Regards Lumpi

Ran into this error as well (in my case I am using a Stored Procedure to retrieve some information). I had made some changes which caused the execution to malfunction.

The error disappeared when I put SET NOCOUNT ON as the first statement of the Stored Procedure.

You do not need to SELECT the server side function, just provide its name ("[tra-CAE400-1].dbo.D100601RVDATABearingAllow") in the .CommandText property. Also you should set the .CommandType property to "stored-procedure" (property reference on w3schools.com).

Then adodb will know that you are talking about calling a function, and not trying to send a plain sql-command.

Chances are that it will then allow you to define the parameters on the command object. But the parameters you define on the command object should correspond exactly (in name and type) to the ones that are defined as the arguments of the function in the sql server.

An example from microsoft.com on using the command-object with a stored procedure

ADO Reference on microsoft.com

Another possible cause of this is debug statements. I just spent far too long trying to work out why this wouldn't work for me, the Proc on the database worked fine, the data it was supposed to insert was inserted, the VBA code worked fine, but there was nothing in the recordset. Final solution was to go through the procs that had been built and remove the PRINT statements. To test if this is the problem, run your proc on SQL Server manually, then look at the messages tab of the results, if there's anything there other than "Command(s) completed successfully." you need to eliminate those messages. "SET NOCOUNT ON" will get rid of the row count messages, but there may be others.

I'm assuming that after 5 years the OP has solved this particular problem, so this is just for anyone like me that finds this while searching for the same problem.

I also ran into this with a stored procedure. Did you SET NOCOUNT = OFF; at the bottom of your code? That is what worked for me after lots of googling. Also, if you have any other code that runs, you have to wrap it in Nocount = on/off, INCLUDING insert and update statements. You would think that an insert statement wouldn't matter but wrapping the code that way is what kept me from committing suicide today.

In our shop we often use lines like this in our stored procedures to assist with debugging:

RAISERROR('Debug message here',0,1) WITH NOWAIT;

This also breaks opening a recordset in Excel vba. I believe the complete answer for this question is, in the stored procedure:

1. use SET ROWCOUNT OFF
2. remove all PRINT statements
3. remove all RAISEERROR statements used for debugging (ie severity of 0)