OSQL Help

[tlaksh]

I need to run this SQL from a OSQL command.

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

This query is saved as delete.qry

My batch file reads:
osql -U sa /P /i c:\script\delete.qry

Now when I run the batch file it tries to delete a file C:\script\Archive\9 in the given directory

and then for the *.prn tries to find the file in C:\winnt\system32.

Why is this happenning? Any suggestions?

Basically I am tring to delete the file that would have the current days date. All myu files have name like

7thICD9CodeFile
8thICD9CodeFile
9thICD9CodeFile
etc

Thanks for all ur help in advance

 

[tlbroadbent]

Embedded spaces cause the DEL command to attempt deleting a file named "9" in the given folder. It tries to delete *.PRN in the default folder because no folder was provided. The default is the system32 folder.

See my embedded comments.

DECLARE
@dt varchar(2), @cmdd1 varchar(1000),
@cmdd2 varchar(1000), @cmd varchar(1000)

-- Use varchar rather than char for @dt
-- Char will be space filled.
-- Add ltrim to remove leading spaces in @dt

SELECT @dt=trim(convert(varchar(2),DAY(getdate()))))

-- The next statment is unnecessary.
-- It will execute but not change the
-- context of the next xp_cmdshell execution.
--
-- EXEC master..xp_cmdshell 'cd\'

-- The command should now be properly formatted
SET @cmdd1 = 'DEL C:\script\Archive\'+@dt+'*.prn'

-- Check the results of the Print to
-- be sure the DEL statement is correct
PRINT @cmdd1

-- The command should now work
EXEC master..xp_cmdshell @cmdd1
Terry L. Broadbent
Programming and Computing Resources

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