How to execute a stored procedure from vba excel

Connectivity
Enthusiast

How to execute a stored procedure from vba excel

Hi,

I have a stored procedure to grant a role to a user wherein i pass name,rolename,request no and have a o/p variable to get any success/failure message. i have this working very fine. Now , i plan to create a wrapper around this using vba excel and have a dba input these details and call this SP internally and assign the role. I am facing problems here. I have all the things set, but when i run the SP i get an error : 

teradata odbc error : there is a mismatch between number of parameters specified and the number of parameters required.

I have the below code to set parameters :

cmdSQLData.CommandText = "GrantUser" 'We assing the query as command text

    cmdSQLData.CommandType = adCmdStoredProc  'We just say what kind of command VBA has to execute

    cmdSQLData.NamedParameters = True

    cmdSQLData.Parameters.Append cmdSQLData.CreateParameter(, adInteger, adParamReturnValue, , Null)   ' return value

    cmdSQLData.Parameters.Append cmdSQLData.CreateParameter("Inparm1", adVarChar, adParamInput, 100, rolename)

    cmdSQLData.Parameters.Append cmdSQLData.CreateParameter("Inparm2", adVarChar, adParamInput, 30, username)

    cmdSQLData.Parameters.Append cmdSQLData.CreateParameter("Inparm3", adVarChar, adParamInput, 30, SSRno)

    cmdSQLData.Parameters.Append cmdSQLData.CreateParameter("Outparm", adVarChar, adParamOutput, 200, Null)

    Set rs = cmdSQLData.Execute 'VBA just run the query and send back the result

SP defn :

REPLACE PROCEDURE SysDBA.GrantUser (       IN  ROLERIGHT         VARCHAR(100)

      ,IN  USRNAME            VARCHAR(30)

      ,IN  request                    VARCHAR(30)

      ,OUT MESSAGE         VARCHAR(200)

      )

Can anyone please help me out, i am stuck here for last 3 days. I have tried all possible options, but of no help.

Thanks !

Tags (1)
3 REPLIES
Teradata Employee

Re: How to execute a stored procedure from vba excel

The Teradata Database does not support Return - Values. It supports IN, OUT and INOUT parameters. Therefore delete the Return-Value parameter:

cmdSQLData.Parameters.Append cmdSQLData.CreateParameter(, adInteger, adParamReturnValue, , Null)   ' return value

If this change does not resolve the issue, then try setting the command text to the Call-Statement instead of the Stored Procedure name:

cmdSQLData.CommandText = "Call GrantUser(?, ?, ?, ?) " 'We assing the query as command text
cmdSQLData.CommandType = adCmdStoredProc  'We just say what kind of command VBA has to execute
cmdSQLData.NamedParameters = False
Enthusiast

Re: How to execute a stored procedure from vba excel

For your first suggestion, i get below error wile executing:

There is a mismatch between number of parameters specified and number of parameters required.

For your second suggestion, you have mentioned ? in the call statement. But how do we pass the required parameters to the SP ? i tried the below stmnt. it workds fine. But the issue is that, i want to get the message in :omsg that is populated in the SP. In the SP, this variable contains the message : successfull if queries in SP worked fine and sql error code and error message if any query fails. But here, even if any of query fails in SP, i get a Return code of 0 i.e successfull in the vba code and the omsg is not populated. my intention here is to access this retunr message from SP and pass on the messge to user.

 Query = "call GrantUser(" & rolename1 & "," & username1 & "," & request1 & "," & ":omsg );" 'This is just the query we want to execute

    cmdSQLData.CommandText = Query 'We assing the query as command text

    cmdSQLData.CommandType = adCmdText 'We just say what kind of command VBA has to execute

    cmdSQLData.CommandTimeout = 0 'With this instruction we don't set any timeout, so the query can take all the necessary time to be executed

    Set rs = cmdSQLData.Execute() 'VBA just run the query and send back the result

Enthusiast

Re: How to execute a stored procedure from vba excel

Hello Netfx,

Did you get a chance to look into this ? I would really appreciate if you could help me on this, i am stuck here for last couple of days nad tied all tricks, but in vain.

Thanks !