csv output from windows batch + sqlcmd only returns first column

Question

i have looked all over the internet and cant seem to find a solution to this problem.

i am trying to output query results as a CSV through using a combination of sqlcmd and windows batch. here is what i have so far:

sqlcmd.exe -S %DBSERVER% -U %DBUSER% -P %DBPASS% -d %USERPREFIX%  -Q "SELECT Username, UserDOB, UserGender FROM TABLE" -o %USERDATA%\%USERPREFIX%\FACT_BP.CSV -h-1 -s","

is there something i'm missing here? some setting that only looks at the first column of the query results?

any advice at all would be a huge help - i'm lost.

Answer 1

Here is the reference page from MSDN on SQLCMD.

http://technet.microsoft.com/en-us/library/ms162773.aspx

I placed this command in a batch file in C:\temp as go.bat.

sqlcmd -S(local) -E -dmaster 
  -Q"select cast(name as varchar(16)), str(database_id,1,0), create_date from sys.databases" 
  -oc:\temp\sys.databases.csv -h-1 -s,

Notice I hard coded the file name and removed the "" around the field delimiter.

I get the expected output below.

Either the command does not like the system variables or something else is wrong. Please try my code as a base line test. It works for SQL 2012.

Also, the number of lines is always dumped to file. You must clear this out of the file. That is why I do not use SQLCMD for ETL.

Why not use BCP instead?

I have writing several articles on my website.

http://craftydba.com/?p=1584