SQL Server bcp utility does not create the txt file,

Question

I'm new to this feature in SQL Server and could use some help. I'm experimenting with the BCP utility and the AdventureWorks2012 database.

I'm attempting to export data to a text file with the BCP utility and the code executes but a file is not created. Can you please look at my code and tell me where the problem(s) is/are.

I'm working out of a local copy of SQL Server Express. Thank you.

Declare @sql Varchar(8000)

Select @sql = '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..xp_cmdshell @sql

Here is my output when I run the query:

output
usage: bcp {dbtable | query} {in | out | queryout | format} datafile
  [-m maxerrors]            [-f formatfile]          [-e errfile]
  [-F firstrow]             [-L lastrow]             [-b batchsize]
  [-n native type]          [-c character type]      [-w wide character type]
  [-N keep non-text native] [-V file format version] [-q quoted identifier]
  [-C code page specifier]  [-t field terminator]    [-r row terminator]
  [-i inputfile]            [-o outfile]             [-a packetsize]
  [-S server name]          [-U username]            [-P password]
  [-T trusted connection]   [-v version]             [-R regional enable]
  [-k keep null values]     [-E keep identity values]
  [-h "load hints"]         [-x generate xml format file]
  [-d database name]        [-K application intent]  [-l login timeout]
NULL

Here is the PRINT output:

bcp
    + "SELECT FirstName, LastName
    FROM AdventureWorks2012.Person.Person ORDER BY LastName, Firstname"
    + queryout C:\Users\David\Desktop\yes.txt -c -t, -T -SHOMEPC\SQLINST01

TT's code worked. Here it is:

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.csv" '+
    '-c -t, -T -S ' + @@SERVERNAME;
EXEC master.sys.xp_cmdshell @stmt_e;

The instructions for adding system permissions for database engine access can be found at the link below. I had to do this because my SQL Server Instance did not have permission to write to the path I was specifying.

https://msdn.microsoft.com/en-us/library/jj219062.aspx

Answer 1

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;