Sunday, September 23, 2007

Insert data into a Text File

As you propably know that MS introduced in SQL Server 2005 a new command utility called SQLCMD. In this article I would like to show the difference between SQLCMD and "old fashioned" OSQL in terms of formating the text file's output.

If you run these two statements and look at the files , you'll see that SQL Server inserts three dotted lines in myoutput2.txt, not so good for reading. Opposite, in myoutput1.txt you see very good reading format.

EXEC master..xp_cmdshell 'SQLCMD -S URID\SQLSERVERDEV2005 -E -Q "SELECT TOP 10 * FROM pubs.dbo.authors" -b -o c:\myoutput1.txt', no_output
EXEC master..xp_cmdshell 'OSQL -S URID\SQLSERVERDEV2005 -E -Q "SELECT TOP 10 * FROM pubs.dbo.authors" -b -o c:\myoutput2.txt', no_output

Despite that SQL Server 2005 supports OSQL utility, I strongly recommend you using SQLCMD.

3 comments:

Razvan Socol said...

OSQL can format the output in the same way if you append the -w65536 parameter.

Uri Dimant said...

Hi Razvan. Thanks for reminding me about width parameter ,I forgot it completely. However, in this post I mentioned only this light difference,SQLCMD has more "power" over OSQL utility

Unknown said...

Uri, thanks for posting this command technique -- in my view, MS really screwed up when they took away the ease and simplicity of dts, but anyway ...

Question: what machine does the -o parameter point to? I can't seem to find the output file, and when I change the path to c:\data (exists only on my local machine), I get an error that the path does not exist. Makes me think it is pointing to the server, not the local machine.