CODEWITHSUNDEEP
sqlsql-server
siwydym67
siwydym67

Reputation: 139

Save column value to file SQL Server

How can I save first column from first row from query to file without additional character? When I save data like this:

EXEC xp_cmdshell 'BCP "SELECT ''xxx'' " queryout D:\file.txt -w  -T -S OMD-MG\SQL2008R2'

I've got: additional \r\n at the end of file

When I save data like that:

EXEC xp_cmdshell 'BCP "SELECT ''xxx'' " queryout D:\file.txt -N  -T -S OMD-MG\SQL2008R2'

I've got: additional characters at front of file I think this is length

I try many parameter without satisfied result

Is there other option to save data to file from query without designer or management studio with correct data?

Upvotes: 1

Views: 4192

Answers (1)

gordy
gordy

Reputation: 9786

-N is a native binary format where any nullable or variable length fields are preceeded by their length. If you use -N with a non nullable, fixed-width field it will not be preceeded by its length.

If you want text data without the newlines you could try -r '' to specify the row terminator which is \n by default, e.g.:

bcp "select 'xxx'" queryout test.txt -c -t '' -r ''

..at least in SQL Server 2016 CTP I'm seeing that BCP tries to add padding to varchar columns. If you convert to text it seems to work alright:

bcp "select convert(text, col) from table" queryout file -c -t '' -r ''

Upvotes: 2

Related Questions