All Forums Connectivity
teradatauser2 236 posts Joined 04/12
21 Oct 2014
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 !

NetFx 346 posts Joined 09/06
21 Oct 2014

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

 
 

teradatauser2 236 posts Joined 04/12
22 Oct 2014

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

teradatauser2 236 posts Joined 04/12
23 Oct 2014

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 !

You must sign in to leave a comment.