Learn how to a build a cloud-first strategyRegister Now


Add variable to data_file in bcp.exe

Posted on 2008-11-12
Medium Priority
Last Modified: 2012-05-05
Hello Experts,

I have a bcp commend to export data from a query.  Is it possible to add a variable to the data_file (output file)?


wanting 20081112 to be a variable declared above.

"\\servername\folder\file_" + @run_date + ".txt"

Question by:thewayne73
LVL 39

Expert Comment

ID: 22943559
how are you executing the bcp command?

Accepted Solution

thewayne73 earned 0 total points
ID: 22945646
using EXEC master..xp_cmdshell.  I did find a solution here:  http://www.sqlteam.com/article/exporting-data-programatically-with-bcp-and-xp_cmdshell

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

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question