CODEWITHSUNDEEP
sqlsql-serverdebuggingsql-server-2005bcp
serhio
serhio

Reputation: 28586

bcp utility outputs an empty file

I will use the bcp utility to debug a stored procedure, so I'd like export some data to a file.

I ran a test into my SQL-Server 2005 

DECLARE @SQL VARCHAR(8000)
SELECT @SQL = 'BCP "SELECT 1" QUERYOUT "D:\test.txt" -C -T'
EXEC MASTER..XP_CMDSHELL @SQL

I observed that the file "D:\test.txt" is created, but is empty.
What I did wrong?

Upvotes: 0

Views: 3269

Answers (1)

JAQFrost
JAQFrost

Reputation: 1431

I ran the equivalent command:

BCP "SELECT 1" QUERYOUT "c:\junk\test.txt" -C -T

The output appeared to be an empty file, until I looked at it with a hex editor. In the hex editor I saw this: 01 00 00 00

So it's saving the numeric value 1 in integer format. I suspect that you want line 2 to read:

SELECT @SQL = 'BCP "SELECT '1'" QUERYOUT "D:\test.txt" -C -T'

In which case you'll see the 1 in notepad. In the hex editor you'd get: 01 00 31

Because '1' is a character string length 1 with the hexadecimal value 31, the ASCII code for the character 1.

Edit:

You could also use the -w option to tell bcp to use unicode characters for all output. Probably a better option since you say you'll be debugging.

Upvotes: 2

Related Questions