Post reply

Passing parameters in Stored Procedure in ActiveX Script

  • August 30, 2005 at 11:48 pm

    #66690

     

    Hi ,

              I am trying to execute a Stored Procedure from ActiveX Script in DTS.

      Here is my code

    '**********************************************************************

    '  Visual Basic ActiveX Script

    '************************************************************************

    Function Main()

        

      Dim objADORS

      Dim XMLDoc

     

      Set objADORS = CreateObject("ADODB.Recordset")

      Set XMLDoc = CreateObject("MSXML2.DOMDocument.3.0")

     

      objADORS.Open "EXEC SPR_S_FreedomTest", _

        "PROVIDER=SQLOLEDB.1;SERVER=s1501;UID=user;PWD=user;DATABASE=DEV;"

      XMLDoc.loadXML "<Company_Info/>"

      Set xmlRoot = XMLDoc.documentElement

    Main = DTSTaskExecResult_Success

    End Function

                            This works fine .Now i want to pass parameters

    in the stored procedure . I know i have to use global variables , but dont know how to pass parameters(syntax) in the stored procedure.

                  Could you please help me out.

    Regards,

        Vikram

  • September 1, 2005 at 3:18 am

    #586755

    You can pass parameters to your stored procedures at the end of your exec statement.  You just need to join the strings together.  For example,

    "EXEC SPR_S_FreedomTest '" & DTSGlobalVariables("gvParameter1").Value & "', '" & DTSGlobalVariables("gvParameter2").Value & "'", connection string ...

    This is all one statement.  Notice that you need to use single quotes to wrap your parameters.  Do that and you should be fine.

    Might be an idea to test your syntax in Query Analyzer first to make sure it executes OK.

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply