OSQL.EXE Not Rocket Science, But...

Question

Post reply

OSQL.EXE Not Rocket Science, But...

shew

SSChampion

Points: 11387
More actions
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

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

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

shew SSChampion Points: 11387 More actions · Answer 1

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.)

john-132685 SSCertifiable Points: 6084 More actions · Answer 2

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....."

Anoop P. SSCommitted Points: 1632 More actions · Answer 3

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.