T-SQL output to flat file with file naming convention
-
Hello all, I have a task that has been giving me trouble for a few days. I have a BCP out command that outputs to flat file. The problem is I need to have the name of this file change daily (ymmdd)it cannot be a hardcode.
Is there a way to insert a variable into the BCP out command, instead of hardcode?
Or is there Command line code that can do this (I can BCP using this as well)
thanks in advance.
-
Do you have to use BCP?
How many rows are you outputting?
Can you use DTS instead?
How are you executing the BCP command?
--------------------
Colt 45 - the original point and click interface
-
Hi,
Using Dynamic SQL to Create a Valid bcp Call
If it is important to have the date the file is created in its name. Dynamic SQL is used to accomplish this. A file's name is created using the technique shown here.
DECLARE @FileName varchar(50)
SET @FileName = REPLACE('c:\authors_'+CONVERT(char(8),GETDATE(),1)+'.txt','/','-')
--Results--
c:\authors_06-30-01.txt
The GETDATE() function is used to determines today's date, CONVERT changes the date into a more friendly format (e.g., 06/30/01) and REPLACE replaces the "/" characters with a "-".
Once the file name is created, it is concatenated with the bcp command to create a valid call. The following shows how this is done.
DECLARE @FileName varchar(50),
@bcpCommand varchar(2000)
SET @FileName = REPLACE('c:\authors_'+CONVERT(char(8),GETDATE(),1)+'.txt','/','-')
SET @bcpCommand = 'bcp "SELECT * FROM pubs..authors ORDER BY au_lname" queryout "'
SET @bcpCommand = @bcpCommand + @FileName + '" -U garth -P pw -c'
PRINT @bcpCommand
-- Results --
bcp "SELECT * FROM pubs..authors ORDER BY au_lname" queryout "c:\authors_06-30-01.txt" -U garth -P pw -c
Executing the Call with xp_cmdshell
EXEC master..xp_cmdshell @bcpCommand
Helen
--------------------------------
Are you a born again. He is Jehova Jirah unto me -
Basically, this is how to do it. I just want to plug for date formats...
Don't use anything else than ISO format (yyyymmdd) - it will make your life easier in the long run.(06/30/01 isn't a very friendly filename, and delimiters can, or rather will, trip you sooner rather than later,)
I usually do something like this;
declare @date char(8)
set @date = convert(char(8), getdate(), 112)
After that, the only thing you have to decide is if the date should be a prefix or suffix. (eg 20050308myFile.txt or myFile20050308.txt)
One of the deciding factors may be the need for sorting - either you want to sort and group by filename prefix, or by date.. Whatever suits the need.
/Kenneth
-
This worked like a charm, thanks. The only other detail is that I'm using VIEWS for my output. It didn't seem to like it. It works for all the other tables of course.
-
bcp shouldn't treat a view any different than a table. Are you sure you had problems just because a view was involved?
/Kenneth
-
Yes, Helen's query outputs all tables (even other VIEWS, not as complex) just not the VIEW I'm needing. It gives me COLLATION/SUBSTRING error. Very weird. I'm going to try and create a temp table, instead of the VIEW I'm using.
-
I used INSERT INTO a new TABLE (instead of VIEW) using the original VIEWS data. I placed this above the Helen's brillant commands. Works like a charm. Thanks to all. Not sure why 1 particular VIEW causes trouble though....
THANK AGAIN
-
The problem then lies within that particular view, it's not bcp's fault.
Anyway, you found a workaround
/Kenneth
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply