Current Date - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Current Date

Author

Topic

divan
Posting Yak Master

153 Posts

Posted - 2012-10-25 : 07:47:08

I have the following copy statement in my sql scheduler

COPY C:\Reports\TRIBUTE_DETAIL_IND.xls D:\TEMP\TDC\TRIBUTE_DETAIL_IND.xls

how do I append the date the file is being copied to the end of the file name so the file name looks like

TRIBUTE_DETAIL_IND_102512.xls

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-10-25 : 08:02:57

something like

copy C:\Reports\TRIBUTE_DETAIL_IND.xls D:\TEMP\TDC\TRIBUTE_DETAIL_IND_%date:~3,2%%date:~0,2%%date:~8,2%.xls

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

divan
Posting Yak Master

153 Posts

Posted - 2012-10-25 : 08:14:08

did not like the command.. "The syntax of the command is incorrect. Process Exit Code 1. The step failed."

PS: I am using sql 2005

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-10-25 : 08:25:32

DECLARE @var1 varchar(100) = 'D:\SQLServer\Projects\Walmart\Scripts\DUMP\MedChive_Data.xls'
DECLARE @var2 varchar(100) = left(@var1, len(@var1)-4) + '_' + REPLACE(CONVERT(VARCHAR(8), SYSDATETIME(), 1), '/', '') + '.xls'
DECLARE @SQL1 VARCHAR(300)
--COPY A NEW ONE IN
SET @SQL1 = 'xp_cmdshell ' + CHAR(39) + 'COPY ' + @var1 + ' ' + @var2 + CHAR(39)
PRINT @sQL1
EXEC (@SQL1)

--
Chandu

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-25 : 10:10:30

xp_cmdshell is disabled by default so make sure you check if its enabled before you use it.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-10-25 : 10:47:57

How are you running it?
That should work from a bat file or command window - maybe it's being parsed before executing.

Try it from a command window first.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-10-26 : 02:43:23

To enable xp_cmdshell feature, follow these steps

EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
-- To enable the feature.
EXEC sp_configure 'xp_cmdshell', 1
GO
-- To update the currently configured value for this feature.
RECONFIGURE
GO

--
Chandu

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources