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

How can I get modified data using timestamp columns

I am putting together an SQL script that is pulling recently modified data from 3 tables and INSERTing that data into another table.

All 3 of my input tables have a timestamp column and I have the previous values for these 3 timestamp columns at the time my SQL script was run previously. So, using the timestamp column values that I had from the previous run of my SQL script and the current timestamp columns that exist in my 3 tables, I am able to derive any recently modified rows.

So, here are my 3 input tables:

Items (has a timestamp column) and has several million rows.

Attributes1 (has a timestamp column) and has a million rows.

Attributes2 (has a timnestamp column) and has a million rows.

The Attributes1 and Attributes2 tables have attributes that describe the items in the Items table. I want to INSERT the Items rows with all of their attributes into a fourth table (that doesn't need a timestamp column).

The kicker is if any attribute changes in the Attributes1 and/or Attributes2 tables, I want to completely resummarize the entire item in the fourth table.

So, I have 3 INSERT/SELECTs in my SQL Script so that I can pickup any combination of modified data in my 3 input tables.

INSERT INTO Table4

.......

SELECT

.....

FROM Items

LEFT OUTER JOIN Attributes1 ...

LEFT OUTER JOIN Attributes2 ...

WHERE Items.TimestampColumn BETWEEN a AND b

INSERT INTO Table4

.......

SELECT

.....

FROM Items, Attributes1

LEFT OUTER JOIN Attributes1 ...

LEFT OUTER JOIN Attributes2 ...

WHERE Attributes1.TimestampColumn BETWEEN c AND d

AND (the Items row is not already in Table4)

INSERT INTO Table4

.......

SELECT

.....

FROM Items, Attributes2

LEFT OUTER JOIN Attributes1 ...

LEFT OUTER JOIN Attributes2 ...

WHERE Attributes2.TimestampColumn BETWEEN e AND f

AND (the Items row is not already in Table4)

This SQL takes a whole long time to run (more than an hour).

I would like to consense my SQL into a single INSERT/SELECT.

Does anybody know of an SQL technique that I haven't thought of...

TIA

Will this work:

INSERT INTO Table4

.......

SELECT

.....

FROM Items, Attributes1

LEFT OUTER JOIN Attributes1 ...

LEFT OUTER JOIN Attributes2 ...

WHERE (Attributes1.TimestampColumn BETWEEN a AND b

OR Attributes1.TimestampColumn BETWEEN c AND d

OR Attributes1.TimestampColumn BETWEEN e AND f)

AND (the Items row is not already in Table4)

Alternatively, you could try doing a UNION on the select statements to get them into one derived table (if the items table is empty before the first query is run, you can drop the NOT EXISTS as the UNION will remove duplicate rows), and then insert into the table in one go from the derived table (UNION statement). The latter may be quicker if the items table is empty to begin with.