bcp - Run time file name generation

Hi,
I am currently running a bcp command to dump a table at run-time. This
requirement has slightly changed and now I need to extract data based on
certain time interval (once in 5 minutes). Because of this, I need to name my
output files as <tablename>_<timestamp>.
My current bcp command looks like this
bcp "select * from asu..t_Skill_Group_Half_Hour WITH (NOLOCK) where
DbDateTime >= '2007-3-5 10:00:00' and DbDateTime < '2007-3-5 10:05:00'"
queryout "table_name.txt" -c -SMySQLServer -T
I need the table name as "table_name_<timestamp>.txt"
Can someone help me with this please?
Thank you.
Regards,
Karthik
Karthik (Karthik@.discussions.microsoft.com) writes:
> I am currently running a bcp command to dump a table at run-time. This
> requirement has slightly changed and now I need to extract data based on
> certain time interval (once in 5 minutes). Because of this, I need to
> name my output files as <tablename>_<timestamp>.
> My current bcp command looks like this
> bcp "select * from asu..t_Skill_Group_Half_Hour WITH (NOLOCK) where
> DbDateTime >= '2007-3-5 10:00:00' and DbDateTime < '2007-3-5 10:05:00'"
> queryout "table_name.txt" -c -SMySQLServer -T
> I need the table name as "table_name_<timestamp>.txt"
> Can someone help me with this please?
From where do you run this BCP command?
An CmdExec job in SQL Server Agent?
A stored procedure?
A BAT file?
Something else?
If you need to extract data as often as every five minutes, I can't escape
the reflection that it may be worth considering using replication.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
|||Hi Erland,
I run this from a SQL Server Stored procedure (which is scheduled to run
once in 5 mins).
I can't use replication as I dont need deletes happening on the publisher to
be propogated. That is why I am using this way to pull the data based on the
datetime column in tables I pull only data that has been inserted and not
deleted.
Thank you.
Regards,
Karthik
"Erland Sommarskog" wrote:

> Karthik (Karthik@.discussions.microsoft.com) writes:
> From where do you run this BCP command?
> An CmdExec job in SQL Server Agent?
> A stored procedure?
> A BAT file?
> Something else?
> If you need to extract data as often as every five minutes, I can't escape
> the reflection that it may be worth considering using replication.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
>
|||Karthik (Karthik@.discussions.microsoft.com) writes:
> I run this from a SQL Server Stored procedure (which is scheduled to run
> once in 5 mins).
So your current code goes something like this:
SELECT @.cmd = 'bcp "select * from asu..t_Skill_Group_Half_Hour WITH ' +
'(NOLOCK) where DbDateTime >= ''' + convert(char(19), @.start, 126) +
''' and DbDateTime < ''' + convert(char(19), @.stop, 126) +
'queryout "table_name.txt" -c -SMySQLServer -T'
Just change it to:
SELECT @.time = convert(char(8), @.start, 112) +
replace(convert(char(8), @.start, 108), ':', '')
SELECT @.cmd = 'bcp "select * from asu..t_Skill_Group_Half_Hour WITH ' +
'(NOLOCK) where DbDateTime >= ''' + convert(char(19), @.start, 126) +
''' and DbDateTime < ''' + convert(char(19), @.stop, 126) +
'queryout "table_name_' + @.time + '.txt" -c -SMySQLServer -T'
Or am I miassing something?

> I can't use replication as I dont need deletes happening on the publisher
> to be propogated.
The little I know of replication tells me that is possible to configure
so that it does not happen. But it's true that would make the replication
venture more complex.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx