Post reply

OSQL Output Problem -- generates multiple blank lines between data

  • February 27, 2005 at 7:49 pm

    #163792

    Hello everyone.  I'm running into a strange behavior with the OSQL utility and I was hoping someone might have run into this before.  When I run my query I get all the data it should be producing, but in between each line of data OSQL is placing 52 blank lines.  The command line I use is:

    osql -S PSDB -E -d HRPRD -i TQM_PS.SQL -o TQMEMP.TXT -h-1 -n -w300

    When I run the query contained in TQM_PS.SQL inside of Query Analyzer it does not produce any blank lines so the problem must be in my command-line or some setting I don't know about (which is a pretty large search space, actually <g> ).  I don't think it's the command line because I have used essentially the same command for other scripts I've written (with a varying input, output, and line width).

    Does anyone have any suggestions as to what might be causing this?

    Thanks in advance for any help.

    Jose'

  • February 27, 2005 at 10:29 pm

    #544349

    try to change -w value, like -w4000

  • February 28, 2005 at 8:10 am

    #544434

    Thanks for the suggestion! That worked, although I had to bump the -w param up to over 8000 before it removed all the blank lines.  But my first thought regarding this was "what the... ?!"  The data itself is only 150 characters per line, so at 5800 lines returned and this line length I get a forty-four Mb file when I should be receiving a file less than 1 Mb in length.  Why is OSQL requiring me to make the line width this ridiculously large?

    I forgot to mention in my previous post:  I'm running SQL Server 2000 v8.00.  The script consists of a single SELECT statement which joins six tables.

    Anyway, thanks again for the help.  If you have any insight to offer as to what I can do to get this working without having to resort to this line length, I'd appreciate it.

    Jose'

  • February 28, 2005 at 4:10 pm

    #544515

    Use ISQLW with -C option instead of osql.

  • March 1, 2005 at 6:52 am

    #544592

    I have just closed a case with Microsoft on this issue.  What you are experiencing is caused by OSQL padding the results to the defined width of the column.  For example, if the column you are returning is defined as varchar(8000) then OSQL will pad the results with spaces out to 8000 total characters no matter what.  They say that this is due to the fact that OSQL was intended to format data as a report, but that still does not explain why they pad the last column.  The -w option controls how many characters appear on a line which is why the number if blank lines changed as you changed the value of this parameter - the number of spaces did not change, just how many were on each line.  The ISQL and ISQLW tools work the same way.

    To avoid the file bloat caused by the padding I chose to use BCP instead.  You should be able to acomplish what you want using BCP once you figure-out how it works.

  • March 1, 2005 at 6:48 pm

    #544739

    Thanks, Paul and wz700 for your suggestions.  That gives me a couple of things to try (and the excuse to learn some new utils!)

    I appreciate your help,

    Jose'

  • March 1, 2005 at 9:55 pm

    #544757

    isqlw provides much more options than BCP.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply