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?

Hi

Instead of using localhost have you should use the name of the SQL Server or
instance. You may want to check out

http://www.sqldts.com/default.aspx?210
http://www.databasejournal.com/featu...le.php/1459181
http://msdn.microsoft.com/library/de...tsrun_95kp.asp

If specifying the file name then make sure that it will be in the correct
location.

John

"Cesco" <francesco.viscomi@gmail.com> wrote in message
news:1117551467.240425.15240@z14g2000cwz.googlegro ups.com...
> 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?
>

Thank you for the reply. I change the name of server from localhost to
the name of my machine and i sure that the dts is in correct location
but the stored procedure doesn't work...
There is something to set to run stored procedure?

Hi

Not really if it works at the command prompt then it should work from
xp_cmdshell. The tip on SQLDTS.com was to let SQL Server create a job for
your package (right click the package and choose the schedule menu) and that
will give you a valid DTSRUN command line.

John

"Cesco" <francesco.viscomi@gmail.com> wrote in message
news:1117612864.920721.261440@o13g2000cwo.googlegr oups.com...
> Thank you for the reply. I change the name of server from localhost to
> the name of my machine and i sure that the dts is in correct location
> but the stored procedure doesn't work...
> There is something to set to run stored procedure?
>