|
dev
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
bcp - Run time file name generationI 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 (Kart***@discussions.microsoft.com) writes:
Show quote > I am currently running a bcp command to dump a table at run-time. This From where do you run this BCP command?> 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? 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, esq***@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 Show quote "Erland Sommarskog" wrote: > Karthik (Kart***@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, esq***@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 (Kart***@discussions.microsoft.com) writes:
> I run this from a SQL Server Stored procedure (which is scheduled to run So your current code goes something like this:> once in 5 mins). 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 The little I know of replication tells me that is possible to configure> to be propogated. so that it does not happen. But it's true that would make the replication venture more complex. -- Erland Sommarskog, SQL Server MVP, esq***@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  |
|||||||||||||||||||||||
Other interesting topics