Post reply

OSQL.EXE Not Rocket Science, But...

  • February 1, 2005 at 10:02 am

    #66444

    This is not rocket science, but I am having trouble with line wraps in osql.exe, even when I set the -w parameter to 4000. I am using osql.exe to write a command file, but the output is unintentionally wrapping. Here is the command file that I am using to write the second command file:

     @echo on

     osql -SAFXSQL005 -E -n -w400 -dProdDBAWeb -b -ip:\temp\temp2.sql -oOutfile.cmd

    Here is the input file (p:\temp\temp2.sql):

    -- File name = p:\temp\temp2.sql

    declare @Server_NME varchar(20)

    set @Server_NME = 'MyServer'

    print ' rem Intentionally cause an error'

    print ' asdf'

    print ' if not %ERRORLEVEL%==0 osql -SPRODCID01 -E -n -w 4000 -dmaster -b -Q"exec master.dbo.xp_sendmail @recipients=''jpshewb'', @subject=''' + @Server_NME + ' does not appear to be responding to Named Pipes requests'', @message =''%ScriptPath% sent this message via %ComputerName%''"'

    The "if not %ERRORLEVEL%" line wraps prior to %ComputerName%, regardless of the -w parameter for osql.exe.

    Any ideas?

    TIA,

    Jon

  • February 1, 2005 at 2:00 pm

    #540027

    I found this link (http://www.sqlmonster.com/Uwe/Forum.aspx/ms-sql-server/1763/ISQL-and-OSQL-Output-Lines-Wrapped-Around-at-256-Characters) that says that print always wraps at 256 characters, regardless of the -w parameter setting for osql.exe. This appears to be true. What a dumb default...     Does anyone know of a way around it? (SELECT does not have that problem, but it dumps a line of dashes into the output command file.)

     

  • February 1, 2005 at 5:27 pm

    #540063

    No good answer here, but the kluge I have used many times in the past is:

    1. select stuff into a ##temptable (even formatted text or DOS batch scripts made from data in tables--may even be a column of type TEXT).

    2. Use BCP to extract that data into a DOS file (.TXT, .BAT, etc.)

    BCP won't put dashes or anything and only puts the line break between rows.

    Example

    CREATE TABLE ##tempbat (T TEXT)

    SELECT "EXEC sp_help " + name

       INTO ##tempbat

    FROM sysobjects WHERE type = "P"

    xp_cmdshell "BCP ""SELECT T FROM ##tempBAT"" -QUERYOUT....."

     

  • February 2, 2005 at 1:16 am

    #540097

    My suggestion is to print the comand into a file and call that file from the second OSQL.

    ie the following part should reside in a file (with proper values of the variables)

    exec master.dbo.xp_sendmail @recipients=''jpshewb'', @subject=''' + @Server_NME + ' does not appear to be responding to Named Pipes requests'', @message =''%ScriptPath% sent this message via %ComputerName%''"'

    call this file from the second OSQL.

    Tell me whether this satisfies your need.

  • February 2, 2005 at 11:59 am

    #540235

    Being the select an option to overcome this limit, for the dashes you can try in the first call using -h-1

    @echo on

     osql -SAFXSQL005 -E -n -w400 -h-1 -dProdDBAWeb -b -ip:\temp\temp2.sql -oOutfile.cmd

  • February 2, 2005 at 12:34 pm

    #540241

    Good point. I had forgotten about the -h-1 parameter for osql.exe.

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

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