SQLTeam.com | Weblogs | Forums

Sqlcmd returnns space within my export to csv datatypes


i wrote the below code that connect to sql server and export table results to .csv file. when using SSIS to export to a table the datatypes have too many spaces

sqlcmd S DBINSTANCE - E -i c:\test.sql -o c:files\test.csv -s "," -W -d DATABASENAME

the headers are coming outas
[name ] [varchar] (50),
[address ] [varchar] (200)

etc .................

it should be

[name] [varchar] (50),
[address] [varchar] ((200)

why is my sqlcmd file creating all these spaces with my column datatypes PLEASE ADVISE


I can't see a difference between "outas" and "should be" (except for an extra left parenthesis in the latter). What's in test.sql? Can you post that?


Probably because the column names were created with a space at the end.




the snip shows how the files are coming out based on the code
sqlcmd S DBINSTANCE - E -i c:\test.sql -o c:files\test.csv -s "," -W -d DATABASENAME



no thats not it sir thx


test sql is select name, address from dbo.emp


Just to make sure, try running this in SSMS on your table:

use yourdatabase;
select '¤'+[name]+'¤' as column_name
      ,len([name]) as column_name_length
  from sys.columns
 where [object_id]=object_id('dbo.emp')

This shows two columns:

  • the column names surrounded by ¤
  • the length og the column names


that didnt work i decided to use bcp (bulk copy) instead