how can i include a timestamp on a file name in a stored procedure??

ok...i give up....

can this be done? (sql server 2000)

all i want to do is have my stored procedure output some data to a file and i want the filename to include a time stamp.

here's my current working file output code:

EXEC master..xp_cmdshell 'bcp "select * from CEData..employee" queryout "c:\employees.txt" -c -Usa -P'

i'd like it to be something like this:

EXEC master..xp_cmdshell 'bcp "select * from CEData..employee" queryout "c:\employees" + datetime + ".txt" -c -Usa -P'

but nothing seems to work.

use:

GETDATE() or GETUTCDATE()

|||

You might also want to convert to string..

EXEC .... + CONVERT(Varchar, getdate()) + ...

|||

Bah, I always forget the little things :P

3 cheers for error checking!

|||

RTernier:

Bah, I always forget the little things :P

3 cheers for error checking!

I know.. been there.. plenty of times..

|||

i just can not seem to get the syntax correct.

i've tried:

EXEC master..xp_cmdshell 'bcp "select * from CEData..employee" queryout "c:\employees" + CONVERT(Varchar, getdate()) + ".txt" -c -Usa -P'

error: (unknown argument '+' on command line)

and:

EXEC master..xp_cmdshell 'bcp "select * from CEData..employee" queryout "c:\employees' + CONVERT(Varchar, getdate()) + '.txt" -c -Usa -P'

error: (Incorrect syntax near '+')

it always chokes on the '+'

|||

try:

EXEC master..xp_cmdshell 'bcp "select * from CEData..employee" queryout"c:\employees" + CONVERT(VARCHAR(50), getdate()) + ".txt" -c -Usa -P'f

But... I've never seen double quotes actually work in SQL. Would there be any errors on that front?

|||

Concatenation does not work very well with xp_cmdshell.

Declare a variable, do the concatenation and use the variable.

Declare @.Sqlvarchar(500)Set @.sql ='bcp "select * from CEData..employee" queryout "c:\employees" + CONVERT(VARCHAR(50), getdate()) + ".txt" -c -Usa -P'EXEC master..xp_cmdshell @.sql

|||

same ole error...

Unknown argument '+' on command line.

|||

this appeared to work....but in the end, no file exists:

declare @.james as varchar
set @.james = 'c:\employees' + CONVERT(VARCHAR(50), getdate()) + '.txt'
EXEC master..xp_cmdshell 'bcp "select * from CEData..employee" queryout @.james -c -Usa -P'

it runs and says "746 rows copied" but no file exists on the c drive

by the way...this works but doesn't provide a timestamp:

declare @.james as varchar
set @.james = 'c:\employees' + CONVERT(VARCHAR(50), getdate()) + '.txt'
EXEC master..xp_cmdshell 'bcp "select * from CEData..employee" queryout "c:\employees.txt" -c -Usa -P'

|||

Note: put the server name appropriately:

Declare@.Sqlvarchar(500)

Set@.sql='bcp "select * from CEData..employee" queryout "c:\employees'+CONVERT(VARCHAR(50),getdate())+'.txt" -c -Usa -P -s"ServerName"'

EXEC master..xp_cmdshell @.sql

|||

man...

sooooo close.....

i tried your suggestion, and now it runs, says "746 rows copied"...

and when i look at the file, the name is: employeesOct 24 2007 3

and has 0 bytes

so...the date is getting truncated and there is no data.

did you try this and it worked?

|||

Yes I could get it to work by changing the db/table/server names. Did you use varchar(X) like I mentioned or did you exclude the size/length part as I noticed in your earlier post?

|||

i did it exactly as you typed it

i don't know why it works for you but not me. i get a truncated filename and no data

|||

The spaces and the colon in the timestamp is whats throwing off. Try this:

Declare@.Sqlvarchar(500),@.fnamevarchar(100)

SET@.fname='c:\'+Replace(replace(CONVERT(VARCHAR(50),getdate()),' ','_'),':','_')+'.txt'

Set@.sql='bcp "select * from CEData..employee" queryout "'+@.fname+'" -c -T -S"ServerName"'

EXEC master..xp_cmdshell @.sql