SQL Server Stored Procedure via VBA and ADODB

[Jaymond Flurrie]

Hi,


I'm creating my first stored procedure ever and it looks relatively good so far. The problem is with passing the parameters, more exactly:
Arguments are of wrong type, are out of acceptable range, or are in confilict with one another


The essential part of the stored procedure looks like:

CREATE PROC GetUserAuthForApp
@User varchar(7),
@application int
AS

while the essential code calling it from VBA looks like:

Dim strConn As String 'Connection string to SQL Server
Dim strSQLtoExecute As String 'SQL query string to execute
Dim oConn As ADODB.Connection 'Object for connecting
Dim rs As ADODB.Recordset 'Object for recordset
Dim cmd As ADODB.Command
Dim prmUser As ADODB.Parameter
Dim prmApplication As ADODB.Parameter
Dim stProcName As String 'Stored Procedure name


Function LoadAuthorizationInfoFromSQL(iApp As Integer, strUser As String) As Variant
    'Define connection string and open the connection
    strConn = ConnectToSQL()
    
    'Initialize variables for database connections
    Set oConn = New ADODB.Connection
    Set rs = New ADODB.Recordset
    Set cmd = New ADODB.Command
    
    oConn.Open strConn
     
    stProcName = "GetUserAuthForApp" 'Define name of Stored Procedure to execute.
    cmd.CommandType = adCmdStoredProc 'Define the ADODB command
    cmd.ActiveConnection = oConn 'Set the command connection string
    cmd.CommandText = stProcName 'Define Stored Procedure to run
    
    Set prmUser = cmd.CreateParameter("@User", adVarChar, adParamInput, 7)
    cmd.Parameters.Append prmUser
    Set prmApplication = cmd.CreateParameter("@application", adInteger, adParamInput)
    cmd.Parameters.Append prmApplication
    
    prmUser.Value = "'" & strUser & "'" 'Tried also without quotes, no difference
    prmApplication.Value = iApp
        
    'Execute stored procedure and return to a recordset
    rs.Open cmd.Execute

and in that rs.Open comes that error.

What have I done wrong, how do I fix it?

 

[Jaymond Flurrie]

This is the latest:

After quite a bit of fight, it finally works from SQL Server Management Studio - but not from VBA


In SQL Server Management Studio I can run it like this:

USE [MyDatabase]
GO


DECLARE	
@return_value int,
@application int,
@user varchar(7)
EXEC
@return_value = [dbo].[GetUserAuthForApp]
@user = 'kilar', 
@application = 1
GO

From VBA, this doesn't work:

Function LoadAuthorizationInfoFromSQL(iApp As Integer, strUser As String) As Variant


Dim oConn As ADODB.Connection 'Object for connecting
Dim rs As ADODB.Recordset 'Object for recordset
Dim cmd As ADODB.Command
Dim prmUser As ADODB.Parameter
Dim prmApplication As ADODB.Parameter
Dim stProcName As String
    
    'Define connection string and open the connection
    strConn = ConnectToSQL()
    
    'Initialize variables for database connections
    Set oConn = New ADODB.Connection
    Set rs = New ADODB.Recordset
    Set cmd = New ADODB.Command
    
    oConn.Open strConn
     
    stProcName = "[MyDatabase].[dbo].[GetUserAuthForApp]"
    
    cmd.CommandType = adCmdStoredProc 'Define the ADODB command
    cmd.ActiveConnection = oConn 'Set the command connection string
    cmd.CommandText = stProcName 'Define Stored Procedure to run
    
    Set prmUser = cmd.CreateParameter("@user", adVarChar, adParamInput, 7)
    prmUser.Value = "kilar"
    cmd.Parameters.Append prmUser
    
    Set prmApplication = cmd.CreateParameter("@application", adInteger, adParamInput)
    prmApplication.Value = 1
    cmd.Parameters.Append prmApplication
    
        
    'Execute stored procedure and return to a recordset
    rs.Open cmd.Execute
'already crashed by this point

The stored procedure looks like:

USE [MyDatabase]
SET NOCOUNT ON
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROC [dbo].[GetUserAuthForApp]
@user varchar(7),
@application int
AS
DECLARE @tempauthtable varchar(max);
DECLARE @querystring varchar(max);


SET @tempauthtable = '##UserAuthForApp_' + @user


SET @querystring =
'IF NOT EXISTS(SELECT * FROM tempdb.sys.objects WHERE name = ''' + @tempauthtable + ''') 
CREATE TABLE ' + @tempauthtable + 'tablestructurehere';
EXEC (@querystring);


--building a dynamic temp table here about 150 rows cut out
EXEC (@querystring);
SET @querystring = 'SELECT * FROM ' + @tempauthtable;
EXEC (@querystring);

Error is still:
"Arguments are of wrong type, are out of acceptable range, or are in confilict with one another"

 

[RoryA]

Your open statement is incorrect apart from anything else that might be an issue. It should be

Set rs = cmd.execute

Also your parameters appear to be both input and output so the direction should be adParamInputOutput rather than adParamInput.

 

[Jaymond Flurrie]

Your open statement is incorrect apart from anything else that might be an issue. It should be

Set rs = cmd.execute

Also your parameters appear to be both input and output so the direction should be adParamInputOutput rather than adParamInput.

Sounds logical. I'll let you know if this alone solved the whole system, but thanks already, I'm sure this will get me adForward

 

[Jaymond Flurrie]

Your open statement is incorrect apart from anything else that might be an issue. It should be

Set rs = cmd.execute

Also your parameters appear to be both input and output so the direction should be adParamInputOutput rather than adParamInput.

It got me further, but now it insists that the recordset is closed. rs.open doesn't open it either.

 

[RoryA]

At what point does it say the recordset is closed?

 

[Jaymond Flurrie]

When I try to do anything with the recordset. For example, as I have this kind of code in VBA:

Function LoadAuthorizationInfoFromSQL(iApp As Integer, strUser As String) As Variant    
    'Define connection string and open the connection
    strConn = ConnectToSQL()
    
    'Initialize variables for database connections
    Set oConn = New ADODB.Connection
    Set rs = New ADODB.Recordset
    Set cmd = New ADODB.Command
    
    'One possible error here can be caused if user has just changed the password to company network and hasn't logged off and back in after that!
    oConn.Open strConn
     
    stProcName = "thenameofmystoredprocedurehere" 'Define name of Stored Procedure to execute.
    
    cmd.CommandType = adCmdStoredProc 'Define the ADODB command
    cmd.ActiveConnection = oConn 'Set the command connection string
    cmd.CommandText = stProcName 'Define Stored Procedure to run
    
    Set prmUser = cmd.CreateParameter("@user", adVarChar, adParamInput, 7)
    prmUser.Value = strUser
    cmd.Parameters.Append prmUser
    
    Set prmApplication = cmd.CreateParameter("@application", adInteger, adParamInput)
    prmApplication.Value = iApp
    cmd.Parameters.Append prmApplication
    
    Set rs = cmd.Execute
    Range("F1").CopyFromRecordset rs

That last row gives the error. When I put there as the second to last row rs.open, no error is given, but the rs remains closed. rs.Status says same "Operation not allowed when the object is closed" and rs.state returns 0.

Just in case it matters, the end of the stored procedure looks now like

SET @querystring = 'SELECT * FROM ' + @tempauthtable;
EXEC (@querystring);

In SQL Server it lists nicely the results.

I can work around this by making that stored procedure end with a select into, running that, selecting all from there and dropping the table, but this requires a) three queries and b) an actual table instead of table variable so I would really prefer just a way to read that recordset.

Oh, also, when using that code with inputoutput like you adviced, so like this:

    Set prmUser = cmd.CreateParameter("@user", adVarChar, adParamInputOutput, 7)    prmUser.Value = strUser
    cmd.Parameters.Append prmUser
    
    Set prmApplication = cmd.CreateParameter("@application", adInteger, adParamInputOutput)
    prmApplication.Value = iApp
    cmd.Parameters.Append prmApplication

I get error "The formal parameter "@user" was not declared as an OUTPUT parameter, but the actual parameter passed in requested output"

I'm not sure what the output points to. Adodb?

 

[RoryA]

That's my fault - I didn't see the rest of the SP that you posted later. Those aren't output parameters at all.

I'm afraid there's far too much going on there for me to debug it from here. As it's an ADO/SQL issue, not an Excel one, you might be better served in a more appropriate forum.

 

[Jaymond Flurrie]

That's my fault - I didn't see the rest of the SP that you posted later. Those aren't output parameters at all.

I'm afraid there's far too much going on there for me to debug it from here. As it's an ADO/SQL issue, not an Excel one, you might be better served in a more appropriate forum.

Ok, will do. do you have any recommendations as Adodb forums?

But thanks anyway, you solved it to the point where it at least works if nothing else!

 

[RoryA]

Not specifically but you could try one of the Microsoft Answers forums.