Getting Error when executing DTS from Stored Procedure

  • Hai .....,

              I have created a DTS package in the Server machine (Server=machine7 and my machine is machine3). When i execute the DTS directly by selecting it, it completes successfully. But when I try to execute the same DTS thru my stored procedure, I 'm getting the following error messageI Executed the procedure thru Query Analyzer window and got the following message)

    DTSRun:  Loading...

    DTSRun:  Executing...

    DTSRun OnStart:  DTSStep_DTSActiveScriptTask_1

    DTSRun OnError:  DTSStep_DTSActiveScriptTask_1, Error = -2147220482 (800403FE)

       Error string:  Error Code: 0

    Error Source= msxml3.dll

    Error Description: A connection with the server could not be established 

     Error on Line 10 

       Error source:  Microsoft Data Transformation Services (DTS) Package

       Help file:  sqldts80.hlp

       Help context:  4500 

     Error Detail Records: 

     Error:  -2147220482 (800403FE); Provider Error:  0 (0) 

      Error string:  Error Code: 0

    Error Source= msxml3.dll

    Error Description: A connection with the server could not be established

     

     

    Error on Line 10

     

       Error source:  Microsoft Data Transformation Services (DTS) Package

       Help file:  sqldts80.hlp

       Help context:  4500

     

     

    Error:  -2147467259 (80004005); Provider Error:  0 (0)

       Error string:  A connection with the server could not be established

     

       Error source:  msxml3.dll

       Help file:  

       Help context:  0

     

    DTSRun OnFinish:  DTSStep_DTSActiveScriptTask_1

    DTSRun:  Package execution complete.

    NULL

     

    Can anyone help me solve this problem please

  • What code are you using in the stored procedure?


    ------------------------------
    The Users are always right - when I'm not wrong!

  • When you say you are selecting it, is it on your machine that you select it?

    Keep in mind that if you run Enterprise Manager and execute a DTS package, it is executing FROM YOUR MACHINE, not the server. If you schedule it or use some stored procedure to start a job or shell out for DTSRUN, then it is running from that machine, in that security context.

  • Hi Steve,

                No, the DTS is on the server machine (i.e., machine7). I have registered the server in my client (by right clicking SQL Server Group and doing New Sql Server Registration... from the context menu) and I 'm succeeding when executing it by selecting the package and then from the context menu, doing Execute Package

  • OK...

           Now the package seems to execute but not getting the same result that I get by executing it directly .

           The stored procedure that calls the DTS is as follows;

    CREATE PROCEDURE ProcessAutomation_TriggerDTS

    AS

     DECLARE @err int

     DECLARE @cmd varchar(1000)

     SET @err = 0

     SET @cmd = 'dtsrun /S machine7 /E /N DTS_MeterNotification /M thePwd'

     EXECUTE @err = master..xp_cmdshell @cmd

     SELECT @err = COALESCE(NULLIF(@err, 0), @@ERROR)

     IF @err <> 0 RETURN @err

    GO

    The DTS executes an ActiveX script that invokes a method of a web service and the code is as follows;

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

    '  Visual Basic ActiveX Script

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

    Function Main()

     Dim oXmlHttp

     Set oXmlHttp = CreateObject ("MSXML2.ServerXMLHTTP.3.0")

     oXmlHttp.open "POST", "http://machine3/WebInterface/WebServices/EventService.asmx/DispatchMeterReadingsInformation", False

     oXmlHttp.setRequestHeader "Content-Type", "application/x-www-form-urlencoded"

     oXmlHttp.send

     Set oXmlHttp = Nothing

     Main = DTSTaskExecResult_Success

    End Function

                 The method DispatchMeterReadingsInformation writes to the event log. When I execute the DTS package directly, I see event in my log. But when I execute it with the help of the Stored Procedure from the Query Analyzer, I don't find any event registered to the log and the procedure's result window displays that the execution is successful.

    Please help me out of this problem

Viewing 5 posts - 1 through 4 (of 4 total)

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