question

jobprojn avatar image
jobprojn asked

sqlcmd -W switch removing leading white space

We are using sqlcmd to export query data from SQL Server 2008R2 to a csv file with column headers. The csv export itself works fine, the problem is there is a requirement that any null or empty strings within the data extract come over as a SINGLE length white space in the csv. In our query code we explicitly specify the return of a single leading white space for any null or empty value by way of a simple IsNull(Convert(varchar(20),[FIELDNAME]),' '). But when we run the csv export via SQL 2008R2 the sqlcmd's -W switch removes the leading white space. Eliminating the -W switch from the sqlcmd leaves a minimum of 7 white spaces in each field and we cannot have this, we need a single white space. Exports from SQL Server 2012 work fine, as it leaves the specified single leading white space, and removes the trailing white space as expected. Because we cannot upgrade the SQL instance to SQL 2012 we are looking for a workaround to this problem. Below is the sqlcmd code we're using to run the export. Any assistance is appreciated. `Set @command = 'sqlcmd -S SQLSERVER -d mydatabase -E -s, -W -Q "set nocount on EXEC [dbo].[DataToExport]" | findstr /V /C:"-" /B > ' + @PrimaryExportPath exec master..xp_cmdshell @command`
sqlcmd
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

0 Answers

·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.