All Forums Database
teradatauser2 236 posts Joined 04/12
20 Oct 2014
How to execute a stored procedure from vba excel

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 :


      ,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 !

teradatauser2 236 posts Joined 04/12
21 Oct 2014

Hello Everyone,
Could someone help me on this. I am stuck here for last few days.
Thanks !

teradatauser2 236 posts Joined 04/12
22 Oct 2014

Hi Diether/Feinholz
Could you look into this and help, i am really stuck here and tried as many things as i could, but no luck yet.
Thanks !

teradatauser2 236 posts Joined 04/12
07 Dec 2014

I found a solution to this problem. i ran the stored procedure in a bteq and captured the log in a log file from vba. the bteq is run in windows. The o/p success / failure message is displayed and captured in the log after the SP is executed. then in the vba code you can parse the o/p message and find if the SP failed on succeeded. i think it is not possible to get back the message from TD Sp as i initially intened. 

You must sign in to leave a comment.