0

Is there a way to send a osql query from the command line that returns results that do not include the big long line of dash characters that represent the column width?

Here is the sample code I am working with:

echo.
"%PROGRAMFILES%\Microsoft SQL Server\%SQLVER%\Tools\BINN\osql" -S . -E -Q "SELECT name + ', ' FROM sysdatabases order by name for XML PATH('')"

And the results look something like this:

 -------------------------------------------------------------------------------------------------------------------------------------------

        ------------------------------------------------------------------------------------------------------------------------------------

        ------------------------------------------------------------------------------------------------------------------------------------

        ------------------------------------------------------------------------------------------------------------------------------------

        ------------------------------------------------------------------------------------------------------------------------------------
 master, model, msdb, openfire, tempdb,
Sam
  • 7,543
  • 7
  • 48
  • 62
djangofan
  • 28,471
  • 61
  • 196
  • 289

1 Answers1

3

Use -h-1 switch to suppress headers: the dashes are the header/data separator "line"

The same switch can be used for sqlcmd too

gbn
  • 422,506
  • 82
  • 585
  • 676
  • That is the answer but it still leaves a huge blank footer of whitespace after the result. Any way to trim that? – djangofan Jul 26 '10 at 22:20
  • @djangofan: yes, you need to CAST to varchar because you actually have xml datatype output. Sorry, I can't test right now, you'll have to play around a bit, perhap use a derived table or CTE – gbn Jul 26 '10 at 22:23
  • ok, thanks. i got it: SELECT CAST(name AS VARCHAR(30)) FROM sysdatabases – djangofan Jul 26 '10 at 22:43
  • Turns out, I also need to precede the select statement with: SET NOCOUNT ON; as well as the -h switch. – djangofan Oct 31 '11 at 19:26