OSQL -o

Question

OSQL -o

Ronald San Juan

SSCrazy

Points: 2937
More actions
January 23, 2007 at 12:14 pm

#117077

Hi Guys,
I'm running the script below using the OSQL utility. Is there a switch in OSQL that outputs to a file only when there is an error on the script. the -o switch outputs to the text failed all results. My goal is to be able to look at the folder location of the output file and see a file only when there was an error in processing the scrip.
Also, what does the -r switch do?
--------------------------
Create Table #Drives
(drive char(1), FreeMB int)
Insert #Drives(drive, FreeMB)
exec master..xp_fixeddrives
Declare @msg varchar(100), @subj varchar (100)
set @msg = @@SERVERNAME + ' disk low (below 5000MB)'
set @subj = 'Server Drives Monitoring == ' + @@SERVERNAME
IF Exists(Select 1 from #Drives where FreeMB < 5000)
begin
  exec master..xp_sendmail @recipients = 'RonaldS@pcmall.com',
   @copy_recipients = 'KTS@PCMall.com; AbramK@PCMall.com',
   @subject = @subj, @message = @msg, @Query = 'exec master..xp_fixeddrives'
end
Else
Print @@SERVERNAME + ' OK ' + convert(varchar(10), Getdate(), 101)
Drop table #Drives
---------------------------
Note: I have sysadmin rights on the servers.

Ronald San Juan | SQL DBA
ID 710124 ~ Code "Northwind"

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

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

James Moore (Red Gate) SSCrazy Points: 2644 More actions · Answer 1

Hi Ronald,

I dont think there is a switch which does this however you can create a batch script which does it - osql handily lets you modify the return values it gives.. a batch file with the following in should work:

@ECHO OFF

OSQL -S nonExistantServer -E

IF %ERRORLEVEL% NEQ 0 Goto FINISH

REM DELETE OUTPUT FILE HERE

:FINISH

Of course you need to change the call to OSQL - you will also need to have a look at OSQL's EXIT functionality which is discussed here - http://www.databasejournal.com/features/mssql/article.php/3403331

-r switches any error messages to stderr rather than stdout which you can make redirect to a separate file (http://www.microsoft.com/resources/documentation/windows/xp/all/proddocs/en-us/redirection.mspx?mfr=true)

- James

--
James Moore
Red Gate Software Ltd

Ronald San Juan SSCrazy Points: 2937 More actions · Answer 2

Thanks James, however, the cmd sees the OSQL command as not having an error.

Here's what I did.

--Batch File--

@ECHO OFF

OSQL -E -SHQSearch3 -i"C:\SQL DBA\ServerDrivesMonitoring\ServerDrivesMonitoring.sql" -o"C:\SQL DBA\ServerDrivesMonitoring\HQSearch3Result.txt"

IF NOT ERRORLEVEL 0 Goto FINISH

DEL "C:\SQL DBA\ServerDrivesMonitoring\HQSearch3Result.txt"

:FINISH

-- Inside ServerDrivesMonitoring.sql, the top line has "RAISERROR ('Error test', 10, 1)"

The file gets deleted. So I changed it to "IF ERRORLEVEL 0 Goto FINISH" and this time the file stays, only showing that ERRORLEVEL is indeed 0.

--Output file --

1> 2> 3> 4> 5> Error test

1> 2> 3> 4> 5> 6> 7> 8> 9> 10> 11> 12> 13> 14> 15> 16> 17> 18> 19> 20> 21> 22> 23> 24> 25> 26> 27> 28> 29> 30> 31> 32> 33> (4 rows affected)

Msg 18025, Level 16, State 1:

xp_sendmail: failed with mail error 0x80040111

Ronald San Juan | SQL DBA
ID 710124 ~ Code "Northwind"

James Moore (Red Gate) SSCrazy Points: 2644 More actions · Answer 3

Try using the OSQL EXIT keyword

C:\>osql -E -n -Q "EXIT (SELECT 10)"

-----------

10

(1 row affected)

C:\>echo %ERRORLEVEL%

10

(if you wrap the script in EXIT then osql returns the last value selected in the script).

- James

--
James Moore
Red Gate Software Ltd

Ronald San Juan SSCrazy Points: 2937 More actions · Answer 4

That would be quite difficult as the script is in an input file.

However, I fount in one of the links you gave, a way to append all the output in one file. I used the >> redirection command.

OSQL -E -SCSEDEV2 -i"C:\SQL DBA\ServerDrivesMonitoring\ServerDrivesMonitoring.sql" >>"C:\SQL DBA\ServerDrivesMonitoring\ServerDrivesMonitoringResults.txt"

OSQL -E -SHQSQLDEV -i"C:\SQL DBA\ServerDrivesMonitoring\ServerDrivesMonitoring.sql" >>"C:\SQL DBA\ServerDrivesMonitoring\ServerDrivesMonitoringResults.txt"

OSQL -E -SHQSQLDEV2 -i"C:\SQL DBA\ServerDrivesMonitoring\ServerDrivesMonitoring.sql" >>"C:\SQL DBA\ServerDrivesMonitoring\ServerDrivesMonitoringResults.txt"

OSQL -E -SHQSEarch3 -i"C:\SQL DBA\ServerDrivesMonitoring\ServerDrivesMonitoring.sql" >>"C:\SQL DBA\ServerDrivesMonitoring\ServerDrivesMonitoringResults.txt"

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

This way, all the results append to one file only. Thanks!

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

1> 2> 3> 4> 5> 6> 7> 8> 9> 10> 11> 12> 13> 14> 15> 16> 17> 18> 19> 20> 21> 22> 23> 24> 25> 26> 27> 28> 29> 30> (3 rows affected)

ALL local drives are OK.

HQREPLICATOR4 - 01/24/2007