Search code examples
sql-serverbatch-filecsvsqlcmd

csv output from windows batch + sqlcmd only returns first column

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.

Solution

  • 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.

    enter image description here

    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