How to add datetime stamp to a file being exported using bcp

Question

How to add datetime stamp to a file being exported using bcp

NineIron

SSChampion

Points: 12537
More actions
March 28, 2017 at 5:29 am

#323250

Here's a piece of the bcp command to export the file. I want to replace, "WantTheDateHere", with getdate(). I've tried using, + getdate() +, but it didn't work.
PatientFile_WantTheDateHere.txt" -c -T -t,' +' -SLOCALHOST" '

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

bcp , because it is actually a command line utility,, only takes static strings, and not variables for parameters.
there's a few ways to do this.
you could always write to a standard filename, and simply rename the file after the bcp completes.
SSIS is really excellent at writing files, and is designed so that expressions can be substituted for things like that.
if you are really sticking with bcp, you could use bcp with xp_cmdshell and dynamic SQL,

--===== These could be parameters in a stored procedure DECLARE @Directory VARCHAR(256) DECLARE @FileName VARCHAR(256) DECLARE @Header VARCHAR(8000) DECLARE @Query VARCHAR(8000)

--===== These hold the necessary DOS commands for BCP and COPY DECLARE @HeaderDosCmd VARCHAR(8000) DECLARE @QueryDosCmd VARCHAR(8000) DECLARE @FilesDosCmd VARCHAR(8000)

SET NOCOUNT ON

SELECT @Directory = 'C:\Temp\', @FileName = 'Test' + CONVERT(VARCHAR(30),GETDATE(),112) + LEFT(REPLACE(CONVERT(VARCHAR(30),GETDATE(),108),':',''),4), @Header = 'SELECT ''AddressID'',''AddressLine1'',''AddressLine2'',''City'',''StateProvinceID'',''PostalCode''', @Query = 'SELECT AddressID,AddressLine1,AddressLine2,City,StateProvinceID,PostalCode FROM AdventureWorks.Person.Address', @HeaderDosCmd = 'BCP "'+@Header+'" QUERYOUT "'+@Directory+@FileName+'.hdr" -S"'+@@SERVERNAME+'" -c -t"" -T', @QueryDosCmd = 'BCP "'+@Query +'" QUERYOUT "'+@Directory+@FileName+'.txt" -S"'+@@SERVERNAME+'" -c -t"" -T', @FilesDosCmd = 'COPY "'+@Directory+@FileName+'.hdr"+"'+@Directory+@FileName+'.txt" "'+@Directory+@FileName+'.csv"'

--===== Just shows what the commands end up looking like PRINT @HeaderDosCmd PRINT @QueryDosCmd PRINT @FilesDosCmd

--===== Do the work of exporting and combining files. -- I intentionally did not include a delete on the hdr and txt file. -- I didn't want to make anyone nervous ;-) EXEC Master.dbo.xp_CmdShell @HeaderDosCmd EXEC Master.dbo.xp_CmdShell @QueryDosCmd EXEC Master.dbo.xp_CmdShell @FilesDosCmd

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

NineIron SSChampion Points: 12537 More actions · Answer 2

I tried to simplify the code a bit for my sake. I think there is still a problem with double and single quotes.

DECLARE @cmd VARCHAR(2048)
DECLARE @FileName VARCHAR(500)

SET @FileName="PatientFile_" + CONVERT(VARCHAR(30),GETDATE(),112)

SET @cmd = 'bcp "EXEC SigHealth..spRadialAnalyticsDataExtract_PatientFile" queryout' +' "\\xx-sftp\dddd$\RadAns\"' + @FileName + ".txt" -c -T -t,' +' -SLOCALHOST" '
print @cmd
EXEC master..xp_cmdshell @cmd, NO_OUTPUT