Writing results to a command prompt

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Writing results to a command prompt

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-07-17 : 10:02:36

Joe writes "I am relatively new to SQL and Stored Procedures and would apprecitate some assistance. I wrote a Stored Procedure that queries a database table and checks the status of a field for Success or Failure results. The scripts to this table are kicked of by Maestro and the jobs in the table are updated daily. What I am looking to do is write out a failure code to the C: drive, lets say the number 1 if the status field is anything but Successful. Below is the procedure and I do get the RAISERROR message returned to me in the Query Analyzer tool. How can I replace the Failed Brio Jobs message with the number 1 and send it out to the filesystem?

CREATE PROCEDURE sp_BrioStatus
AS
BEGIN

If (Select count(*)
From brioadmin.briojobs
Where Completion_Status <> 'Successful' and Enabled ='Y' and
convert(varchar, Last_Date,1)= convert(varchar, (dateadd
dd, 0 ,getDate() )),1) ) >=0

BEGIN
RAISERROR ('Failed Brio Jobs', 16 ,1 )
RETURN
End
End"

M.E.
Aged Yak Warrior

539 Posts

Posted - 2002-07-17 : 11:50:46

Hmm, over 10 reads and no ideas. Stumped a few I guess.

2 things to look into...
a. Export the results of this query to a text file then print the text file.
b. maybe xp_cmdshell. just send a simple print command back maybe?

Just some ideas

-----------------------
Take my advice, I dare ya

jasper_smith
SQL Server MVP & SQLTeam MVY

846 Posts

Posted - 2002-07-17 : 16:36:34

In this example you get a file c:\brioreports.log with the error code and the date it was run. If run repeatedly it appends lines to the file.


CREATE PROCEDURE sp_BrioStatus 
AS 
BEGIN 

If (Select count(*) 
From brioadmin.briojobs 
Where Completion_Status <> 'Successful' and Enabled ='Y' and 
convert(varchar, Last_Date,1)= convert(varchar, (dateadd(dd, 0 ,getDate() )),1) ) >=0 

BEGIN 
RAISERROR ('Failed Brio Jobs', 16 ,1 ) 
EXEC master..xp_cmdshell 'echo 1 %DATE% >>c:\brioreports.log',no_output
RETURN 
End 
End

HTH
Jasper Smith

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources