Removing the leading space in OSQL output


Quite annoying isn't it? You want to output a table or part of a table to a text file using OSQL, and the output has a leading space on it.

Problem: there's no way around it. Even with the -n switch. Shoddy coding.

Solution: don't use OSQL. These days, we can use SQLCMD instead. And they fixed the issue in that.

So, say you've got a table called STATES, and you want to output the contents of it to C:\TEST\STATES.TXT, and your input query is in a file called C:\TEST\QUERY.TXT, which looks like this:



SET NOCOUNT ON
go
SELECT STATE_ABBREVIATION + ',' + STATE_DESC FROM myDB..STATES
go



Here's what the TSQL code would have looked like using OSQL:


declare @cmd varchar(max)

select @cmd = 'osql -n -h-1 -E -S MYSERVER -i C:\TEST\QUERY.TXT
  -o C:\TEST\STATES.TXT -w80'

exec master..xp_cmdshell @cmd



And here's what the code should be:


declare @cmd varchar(max)

select @cmd = 'sqlcmd -h-1 -E -S MYSERVER -i C:\TEST\QUERY.TXT  
  -o C:\TEST\STATES.TXT -w80'

exec master..xp_cmdshell @cmd



And the beautiful thing is: all the command switches are the same, plus some new ones. So all your old OSQL scripts don't have to be rewritten (you don't need the -n switch any more - you'll get a warning for using it)

Don't forget also, that SQLServer has to be configured to allow xp_cmdshell to be used:



sp_configure 'xp_cmdshell',1

--then run this:
reconfigure




del.icio.us this is del.icio.us