Using Bcp Utility to Export SQL Queries to a Text File

Using bcp utility to export SQL queries to a text file

bcp out exports tables.

To export a query use queryout instead - you'll need to wrap your query in "double quotes"

set @logtext = '"select name, type from master.dbo.spt_values where number=6"' 
--set @logtext = 'master.dbo.spt_values' 
SET @cmd = 'bcp ' + @logtext + ' queryout "c:\spt_values.dat" -U uId -P uPass -c' 
EXEC master..XP_CMDSHELL @cmd  

http://msdn.microsoft.com/en-us/library/ms162802.aspx

SQL Server bcp utility does not create the txt file,

The following snippet should run without problem on any SQL Server. It outputs all table information in INFORMATION_SCHEMA.TABLES as a comma separated file in C:\Temp\information_schema.csv.

Run this as a sanity check; it works without problem on my system, and it should on your system too. Run this from the AdventureWorks2012 database. If it doesn't work we'll have to delve deeper.

DECLARE @stmt_c VARCHAR(8000);
SET @stmt_c=
    'BCP '+
    '"SELECT*FROM '+QUOTENAME(DB_NAME())+'.INFORMATION_SCHEMA.TABLES" '+
    'QUERYOUT "C:\Temp\information_schema.csv" '+
    '-c -t, -T -S ' + @@SERVERNAME;
EXEC master.sys.xp_cmdshell @stmt_c;

---

Now if this works, adapt this to your query:

DECLARE @stmt_e VARCHAR(8000);
SET @stmt_e=
    'BCP '+
    '"SELECT FirstName,LastName FROM AdventureWorks2012.Person.Person ORDER BY LastName,Firstname" '+
    'QUERYOUT "C:\Users\David\Desktop\yes.txt" '+
    '-c -t, -T -S ' + @@SERVERNAME;
EXEC master.sys.xp_cmdshell @stmt_e;

Trying to save a txt file create with BCP on the local SQL Server

1. Enable XP_CMDSHELL

   -- To allow advanced options to be changed.
   
   EXEC sp_configure 'show advanced options', 1;
   
   GO
   
   -- To update the currently configured value for advanced options.
   
   RECONFIGURE;
   
   GO
   
   -- To enable the feature.
   
   EXEC sp_configure 'xp_cmdshell', 1;
   
   GO
   
   -- To update the currently configured value for this feature.
   
   RECONFIGURE;
   
   GO

2. Verify if the specified path exists
3. Verify do you have permission to write to write the directory?
4. Verify does SQL Server service account has permission to write the directory?

Thanks

Can I specify an input sql file with bcp?

As far as I'm concerned the BCP utility only supports Transact-SQL queries directly written to the command line. Ex:

bcp "SELECT Name FROM AdventureWorks.Sales.Currency" queryout Currency.Name.dat -T -c

According to its reference the "-i" option:

Specifies the name of a response file, containing the responses to the command prompt questions for each data field when a bulk copy is being performed using interactive mode (-n, -c, -w, or -N not specified).

Notice that it differs from the sqlcmd Utility "-i" option:

Identifies the file that contains a batch of SQL statements or stored procedures. Multiple files may be specified that will be read and processed in order (...)

BCP utility SQL Server Export to CSV - destination CSV file never gets created

Command lines supplied to xp_cmdshell cannot span multiple lines of text, they need to be specified completely on a single line. Try building the command as a series of concatenated strings like this:

declare @sql varchar(8000) =
    'bcp' +
    ' "select * from MyDB.dbo.MyTable WHERE Rn BETWEEN 1 AND 100 order by Rn"' +
    ' queryout' +
    ' "E:\MyFolder\MyFileName.txt"' +
    ' -c -t, -T -S' + @@servername 
exec master..xp_cmdshell @sql

When concatenating the strings be mindful of where you need to include white space between each segment so that command line parameters don't run into each other.

---

Related Topics