BCP and Line breaks in query command

/*

BCP supports a query command as the data source when exporting data to a file. However, we need to be carefull when creating the query, e.g., the following query woeks fine in the dynamic t-SQL:

*/

DECLARE

@Query nvarchar(300)

SET

@Query=‘SELECT TOP 10 EmployeeID, LastName, FirstName,

Title, TitleOfCourtesy, BirthDate, HireDate, Address, City, Region, PostalCode, Country, HomePhone, Extension, Photo, Notes, ReportsTo, PhotoPath FROM Northwind.dbo.Employees ORDER BY EmployeeID’

PRINT @Query

EXEC

(@Query)

/*

it output s the correct results. If we attache this to a BCP command:

*/

DECLARE

@SQL nvarchar(1000),@Query nvarchar(300)

SET

@Query=‘SELECT TOP 10 EmployeeID, LastName, FirstName,

Title, TitleOfCourtesy, BirthDate, HireDate, Address, City, Region, PostalCode, Country, HomePhone, Extension, Photo, Notes, ReportsTo, PhotoPath FROM Northwind.dbo.Employees ORDER BY EmployeeID’

SET

@SQL=‘BCP "’+@Query+‘" queryout "’+‘D:\TestBCP.bcp’+N‘" -c -q -T -Slocalhost’

PRINT

@SQL

exec

master..xp_cmdshell @SQL –, no_output

IF

@@ERROR<>0 PRINT ‘Failed’

/*

If your acount has all the necessary priviledges to run the script, check the D: drive, the file is not cretaed.

Remove the comment for no_output and run it again, you will see the following message:

BCP "SELECT TOP 10 EmployeeID, LastName, FirstName,

Title, TitleOfCourtesy, BirthDate, HireDate, Address, City, Region, PostalCode, Country, HomePhone, Extension, Photo, Notes, ReportsTo, PhotoPath FROM Northwind.dbo.Employees ORDER BY EmployeeID" queryout "D:\TestBCP.bcp" -c -q -T -Slocalhost

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"]

NULL

Through the @SQL seems containing the right command, the BCP utility prints out the command line options. Though no error messages are given, a console command prints out command line arguments only when the parameters are invalid.

Remove the line break in the @Query, it will fix the issue:

*/

DECLARE

@SQL nvarchar(1000),@Query nvarchar(300)

SET

@Query=‘SELECT TOP 10 EmployeeID, LastName, FirstName, Title, TitleOfCourtesy, BirthDate, HireDate, Address, City, Region, PostalCode, Country, HomePhone, Extension, Photo, Notes, ReportsTo, PhotoPath FROM Northwind.dbo.Employees ORDER BY EmployeeID’

SET @SQL=‘BCP "’+@Query+‘" queryout "’+‘D:\TestBCP.bcp’+N‘" -c -q -T -Slocalhost’

exec master..xp_cmdshell @SQL–, no_output

IF @@ERROR<>0 PRINT ‘Failed’

/*

So when use the query in BCP, be carefull not to break the command into multiple lines. SQL server 2000 and 2005 have the same behaviour.

*/

Advertisements
This entry was posted in SQL server. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s