bcp queryout not working with line break

Question

bcp queryout not working with line break

APA0876

Hall of Fame

Points: 3853
More actions
March 2, 2015 at 9:37 pm

#298237

Hi
I have SQL Server 2012, and I'm trying to execute a bcp with a queryout and it is not working with line break, I will send you an example of a simple query my real case is a very long query that contains line breaks but this example behaves the same:
DECLARE @StoredProcedure AS NVARCHAR(4000) ,
@sql AS NVARCHAR(4000)
SET @StoredProcedure = N'"SELECT *
from clarity.[dbo].[ACCESS_DEP]"'
SET @sql = 'bcp ' + @StoredProcedure
+ ' queryout C:\EmployeeData.dat -T -c'
PRINT @sql
EXEC master..xp_cmdshell @sql
The example above won't work: it will return the following:
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]
NULL
if I remove the line break it will work: example below works
DECLARE @StoredProcedure AS NVARCHAR(4000) ,
@sql AS NVARCHAR(4000)
SET @StoredProcedure = N'"SELECT * from clarity.[dbo].[ACCESS_DEP]"'
SET @sql = 'bcp ' + @StoredProcedure
+ ' queryout C:\EmployeeData.dat -T -c'
PRINT @sql
EXEC master..xp_cmdshell @sql
Of course that is not the solution as I have a very long query that requires line breaks, please can you help me on this? What would be the workaround?
Thanks

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 1

The BCP command, including the embedded query, must be on a single line. The work around would be to use the "S" and "d" parameters and the query would be to EXEC a stored procedure. You could also use a text file with a script in it as an input but I hate that method. Way too easy to lose a file compared to keeping a stored procedure, which gets backed up with the rest of the database.

If stored procedures are a real turn off (they shouldn't be), then you can use "sloshing" to breakup the command for display purposes and still have it come out as a single command line. There IS a limit as to how long such things can be and stored procedures are still your absolute best bet here.

"Sloshing" is just adding the "continuation character" (a backslash) at the end of every line. For example, run the following....

PRINT 'This is all on one line except for display purposes.'

Sloshing can be used in a SELECT, as well.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)