This is a little convaluted, but here goes....

SQL 7 machine
-------------
I have a stored procedure on the SQL7 machine which:
1. truncates 5 import tables
2. deletes previous copies of 5 csv files, called via xp_cmdshell
3. unzips a .zip file using Winzip command line, called via xp_cmdshell
4. calls a DTS to upload the 5 csv files into their tables, again via
xp_cmdshell

SQL 2000 machine
----------------
I have a DTS which connects to the SQL7 machine, and calls the above stored
procedure to upload the files.



Now, when I call the sp on the SQL7 it works fine.
When I call the sp from the SQL2000 machine, the dts to upload the data does
not run.
The actual xp_cmdshell to call it DOES run, but the DTS doesn't actually
kick-off.



Can anyone shed some light on this odd behaviour?
Many thanks

Set your package to "Fail On First Error"

Also remember that DTS is a client side app so executing a package brings it
to the box that requests execution.

Resources then need to be viewable from the source box.

--



Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.SQLIS.com - You thought DTS was good. here we show you the new stuff.
www.konesans.com - Consultancy from the people who know


"Beema" <none (AT) supplied (DOT) com> wrote

I thought that calling remote the stored procedure, which itself runs the
dts would solve this.

How can I call this dts remotely to get around this problem?

I tried putting the statement in a scheduled job, and instead of calling it
directly, I start the job via msdb..sp_start_job - but it says the job does
not exist...



"Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com> wrote