CODEWITHSUNDEEP
sql-serverbcp
user2556480
user2556480

Reputation:

SQL Server bcp utility does not create the txt file,

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

Upvotes: 4

Views: 8487

Answers (5)

Vikas Chaudhary
Vikas Chaudhary

Reputation: 11

Put the complete bcp query in one not to change the line while writing bcp query.

You can write your query as:

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" -Usa -Ppassw0rd -c -t, -T -S'

EXEC master..xp_cmdshell @sql

Upvotes: 1

raca
raca

Reputation: 21

I have struggled with that problem almost whole yesterday and finally it comes out, that the "select" query is too complex (probably) to be processed by the xp_cmdshell command directly.

I have a query joining and aggregating many tables from different databases.
Trying to save its results to txt file directly via xp_cmdshell always resulted in the output presented by BrownEyeBoy, eventhought the select itself was working correctly.

I've bypassed this simply by inserting the results of the complex query into temporary table and then execute the xp_cmdshell on the temporary table like: 

DECLARE
@SQL    varchar(max)
, @path varchar(max) = 'D:\TMP\TMP_' + convert(varchar(10), convert(date, getdate(), 21)) + '.txt'
, @BCP  varchar(8000)
;

INSERT INTO ##TMP
{COMPLEX SELECT}
;

SET @SQL = 'SELECT * FROM ##TMP;
SET @BCP = 'bcp "' + @sql + '" queryout "' + @path + '" -c -T -S.'

EXEC xp_cmdshell @bcp;

Not nice, but easy and working.

Upvotes: 1

TT.
TT.

Reputation: 16146

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;

Upvotes: 1

Squirrel
Squirrel

Reputation: 24803

Should warpped the query in double quotes. I have removed an extra + before the -c. You can test out the BCP on command prompt first. make sure it is working before using xp_cmdshell to execute it.

And lastly, i have added a PRINT statement to print out the command for verification

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

PRINT @sql -- Print out for verification

EXEC master..xp_cmdshell @sql

Upvotes: 1

Sanu Antony
Sanu Antony

Reputation: 364

This Should work

DECLARE @sql VARCHAR(8000);
SELECT @sql = 'bcp "SELECT FirstName, LastName FROM'+
' AdventureWorks2008.Person.Person ORDER BY FirstName, LastName" queryout'+
' C:\Users\David\Desktop\yes.txt -c -t, -r \r\n -S '+@@servername+' -T';
EXEC master..xp_cmdshell @sql;

Upvotes: 1

Related Questions