How to execute a stored procedure from vba excel

Database
Junior Supporter

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 !

3 REPLIES
Junior Supporter

Re: How to execute a stored procedure from vba excel

Hello Everyone,

Could someone help me on this. I am stuck here for last few days.

Thanks !

Junior Supporter

Re: How to execute a stored procedure from vba excel

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 !

Junior Supporter

Re: How to execute a stored procedure from vba excel

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. 

--Samir