SQL Server Performance

Error in DTS packages

Discussion in 'General DBA Questions' started by rajeev_id, Aug 20, 2004.

  1. rajeev_id New Member

    --I am executing DTS packages from stored procedures <br />--Here is the Code<br />--***************<br />--------------------------------------------------------------------<br />Declare <br />@Server varchar(255),<br />@PkgName varchar(255),<br />@ServerPWD varchar(255),<br />@IntSecurity bit ,<br />@PkgPWD varchar(255)<br /><br /><br />set @Server='111.111.111.111'<br />set @PkgName='New Package'<br />set @ServerPWD='xxxxx'<br />set @IntSecurity=0<br />set @PkgPWD=''<br /><br />DECLARE @hr int, @ret int, @oPKG int, @Cmd varchar(1000)<br /><br />-- Create a Pkg Object<br />EXEC @hr = sp_OACreate 'DTS.Package', @oPKG OUTPUT <br />IF @hr &lt;&gt; 0<br />BEGIN<br />PRINT '*** Create Package object failed'<br />select 1,'*** Create Package object failed'<br />END<br /><br />-- Evaluate Security and Build LoadFromSQLServer Statement<br />IF @IntSecurity = 0 <br />SET @Cmd = 'LoadFromSQLServer("' + @Server +'", "' + SUSER_SNAME() + '", "' + @ServerPWD + '", 0, "' + @PkgPWD + '", , , "' + @PkgName + '")'<br />ELSE<br />SET @Cmd = 'LoadFromSQLServer("' + @Server +'", "", "", 256, "' + @PkgPWD + '", , , "' + @PkgName + '")'<br /><br />--=====[<img src='/community/emoticons/emotion-2.gif' alt=':D' />]============<b>Here is problem</b>============[8D]=====<br />EXEC @hr = sp_OAMethod @oPKG, @Cmd, NULL<br /><br />IF @hr &lt;&gt; 0<br />BEGIN<br /> PRINT '*** LoadFromSQLServer failed'<br /> select 1,'*** LoadFromSQLServer failed'<br />END<br /><br />-- Execute Pkg<br />EXEC @hr = sp_OAMethod @oPKG, 'Execute'<br />IF @hr &lt;&gt; 0<br />BEGIN<br /> PRINT '*** Execute failed'<br /> select 1,'*** Execute failed'<br />END<br /><br />-- Clean Up<br />EXEC @hr = sp_OADestroy @oPKG<br />IF @hr &lt;&gt; 0<br />BEGIN<br />select 1<br />END<br /><br />----------------------------------------------------------------------------------<br /><br />************************************************<br /><b>It gives Error: *** LoadFromSQLServer failed ***</b><br /><br />Where as if i execute from vb 6.0 it is working.<br />Here is vb 6.0 code<br />************************************************<br />Dim obj1 As New DTS.Package<br /><br />obj1.LoadFromSQLServer "111.111.111.111", "sa", "xxxxx", DTSSQLStgFlag_Default, , , , "New Package"<br />obj1.Execute<br />obj1.UnInitialize<br /><br />Set obj1 = Nothing <br />************************************************<br /><br /><br /><br />Rajeev Kumar Srivastava<br /> --ALWAYS BE POSITIVE!--

Share This Page