DTS problem

[tlaksh]

I have the following code. When I run it through SQL Query it runs fine, but it does not run through a dts.

DECLARE @dt varchar(100), @cmdd1 varchar(1000)
SELECT @dt=(convert(char(20),DATEPART(dd,getdate())))
SET @cmdd1 = 'DEL \\C1467\script\Archive\'+ @dt+ '*.prn'
PRINT @cmdd1
EXEC master..xp_cmdshell @cmdd1

Any reason why?

Thanks

Lakshmi.

 

[tlbroadbent]

Does the DTS return an error or just fail to complete the task?

Most likely probablem is security and permissions. The DTS Job runs under the SQL Agent account which may or may not have the same permissions as the SQL Server login account.

I recommend that you open the DTS Job properties and edit the Step that exeutes xp_cmdshell. Then click on the Advanced tab. Abo0ut 1/2 way down the screen, locate the Output file text box. Enter a drive, folder and file on the server where the Job will run. Name the file something like DTSDelete.log.

Start the Job and after it completes open the LOG file to read the messages it contains. You should be able to determine if and why an error occurred. Terry L. Broadbent
Programming and Computing Resources

http://tlbroadbent.home.attbi.com/prog.htm

 

[tlaksh]

I did as u said and this is what I get in the log file

DTSRun: Loading...
DTSRun: Executing...
DTSRun OnStart: DTSStep_DTSExecuteSQLTask_1
DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_1
DTSRun: Package execution complete.

Even though the execution is complete it does not delete the file.

Help please....

Thanks
Lakshmi.