| Author |
 Topic  |
|
|
divan
Posting Yak Master
123 Posts |
 Posted - 10/25/2012 : 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
Flowing Fount of Yak Knowledge
United Kingdom
3328 Posts |
Posted - 10/25/2012 : 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
123 Posts |
Posted - 10/25/2012 : 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
Flowing Fount of Yak Knowledge
India
1430 Posts |
Posted - 10/25/2012 : 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 |
Edited by - bandi on 10/25/2012 08:28:05 |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47125 Posts |
Posted - 10/25/2012 : 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/
|
Edited by - visakh16 on 10/25/2012 10:10:40 |
|
|
|
nigelrivett
Flowing Fount of Yak Knowledge
United Kingdom
3328 Posts |
Posted - 10/25/2012 : 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
Flowing Fount of Yak Knowledge
India
1430 Posts |
Posted - 10/26/2012 : 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 |
|
|
| |
Topic |
|
Current Date
