save query results to formatted text file

26 Jun 2006 9:53 PM

sneaky

Greetings,
I'm very new to SQL & I'm attempting to schedule a weekly query that saves
the results to a formatted text file.

I used osql but the format was wrong. (all spread out, lots of whitespace &
tabbing?)

I am now using isqlw & it looks much better, however the colums are too
wide. The utility mentions a -C configuration file switch - is this where I
can format the output? I can find no reference to what this file should
look like...

Please help me with this command or let me know if I should be using a
different utility entirely.

Thanks

27 Jun 2006 6:16 PM

Roger Wolter[MSFT]

Neither ISQL nor OSQL give you a lot of options for formatting output. To
do that you either need to use a reporting tool like Reporting Services or
maybe load the results into Excell and format the columns in Excell. I
wouldn't recommend using ISQL because it will disappear in the next version.
The -O option in OSQL should give you the same formatting. The -w option
will allow you to set the maximum column width but unless your columns are
of uniform width, that probably won't give you everything you want.

--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm

Show quote

"sneaky" <me@home> wrote in message
news:%23fM$wqWmGHA.5076@TK2MSFTNGP02.phx.gbl...
> Greetings,
> I'm very new to SQL & I'm attempting to schedule a weekly query that saves
> the results to a formatted text file.
>
> I used osql but the format was wrong. (all spread out, lots of whitespace
> & tabbing?)
>
> I am now using isqlw & it looks much better, however the colums are too
> wide. The utility mentions a -C configuration file switch - is this where
> I can format the output? I can find no reference to what this file should
> look like...
>
> Please help me with this command or let me know if I should be using a
> different utility entirely.
>
>
> Thanks
>

6 Jul 2006 2:01 AM

DosItHelp

As a workaround you can format the output in SQL, i.e.:
to limit two columns to 16 characters each use the convert function as
follows:

SELECT
convert(varchar(16), ColumnName1) as ColumnName1,
convert(varchar(16), ColumnName2) as ColumnName2
FROM mytable

For more tips on how to embed SQL in a cmd batch script look here:
http://dostips.cmdtips.com/DtCodeInterfacing.php