Hallo to everybody. I have a DTS in SQL Server 2000 and I need to
execute it from stored procedure.
I know that there are various method fot does this but they doesn't
work.
The first method that I try to use is with the stored procedure
"xp_cmdshell".
If I write in DOS prompt
dtsrun /F c:\DTS1.dts
the DTS1 will execute well
I write in Query Analizer the command:
exec xp_cmdshell 'dtsrun /F c:\DTS1.dts'
the DTS1 doesn't execute.
Also, if I use the DOS Prompt command:
dtsrun /S localhost /U sa /P sa /N DTS1
the DTS1 work well
but if I use this command with xp_cmdshell it doesn't work:
exec xp_cmdshell 'dtsrun /S localhost /U sa /P sa
/N DTS1'
Where is the problem?
I also use a stored procedure that use sp_OA method:
CREATE procedure p_rundts as
declare @hr1 int, @hr2 int, @hr3 int, @hr4 int, @oPKG int
--creating package
EXEC @hr1 = sp_OACreate 'DTS.Package', @oPKG OUT
print @hr1
--loading package
EXEC @hr2 = sp_OAMethod @oPKG, 'LoadFromSQLServer("(local)", "",
"",256,,,, "DTS1")', null
print @hr2
--Execute package
EXEC @hr3 = sp_OAMethod @oPKG, 'exec', NULL
print @hr3
IF @hr3 <> 0
BEGIN
PRINT '*** Execute failed'
EXEC sp_displayoaerrorinfo @oPKG , @hr3
RETURN
END
else print 'Esecuzione package'
--EXEC @hr3 = sp_OAMethod @oPKG, ' EXEC (@oPKG)'
--Pulizia dell'ambiente
EXEC @hr4 = sp_OADestroy @oPKG
print @hr4
GO
but also this procedure doesn't work...
Someone can help me?
