| Author |
Topic |
|
DBADave
Constraint Violating Yak Guru
366 Posts |
 Posted - 2004-03-17 : 14:24:33
|
| I need to create some reports through a scheduled SQL Server job. The reports are created by stored procedures and the procs contain print statements. The print statements, as expected, generate informational messages when the stored procs are invoked through the SQL scheduler. I can avoid the messages by executing the stored procedures using OSQL, but then how does the job know if the stored procedure failed. I don't want to write FindString logic to search the output files. Any thoughts?Dave |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-03-17 : 16:26:04
|
| Why do the procs contain PRINT statements? PRINT statements should be used for debugging, then removed when in production.Tara |
 |
|
|
DBADave
Constraint Violating Yak Guru
366 Posts |
Posted - 2004-03-17 : 16:52:07
|
| I've never heard of that one. BOL says use PRINT to help in troubleshooting Transact-SQL code, to check the values of data, or to produce reports. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-03-17 : 16:55:59
|
| That's what I'm talking about though. Troubleshooting or debugging, whatever you want to call it. Once you are ready to use it for production, there is no one to look at the PRINT statements, so why have them there?If you just want to display data, then have the stored procedure display a result set:CREATE PROC SomeProcAS...SELECT Column1FROM Table1...RETURNTara |
|
|
|
DBADave
Constraint Violating Yak Guru
366 Posts |
Posted - 2004-03-17 : 17:10:11
|
| Tara,BOL says troubleshooting, debugging OR creating reports. This is for creating reports. Many times it is easier to format a report using print statements then select statement. For example, Select statements always generate the dashes used to separate a header from the results. There are many times in a report where the dashes are not appropriate. Perhaps there is a way to turn the header option off, but I've yet to find it. I know it can be done in Query Analyzer, but if invoking stored procedure from the job scheduler I don't believe it's possible. I could be wrong.Dave |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2004-03-17 : 22:08:45
|
| Why not use a real reporting tool? T-SQL is not a reporting language, regardless of what BOL might say or what the print statement can do. You can hammer a nail with a screwdriver, but it's not designed for it. Use a hammer; use a reporting tool. |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-03-18 : 08:55:11
|
| How are you using the query in a job? Run the query in osql with an output to a file. Attach the file as your report. Will that work?MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
DBADave
Constraint Violating Yak Guru
366 Posts |
Posted - 2004-03-18 : 10:16:24
|
| I agree their are much better reporting tools, not to mention job scheduling tools, but neither exist in our environment. I don't see the company investing money in these areas at this time. Things are still a bit tight financially.I am currently invoking the stored procs with OSQL and writing the results to an output file. That does eliminate the SQLSTATE messages, but the problem I ran into was getting OSQL to report errors back to the SQL Scheduler. I want the scheduler to have job steps that send emails and/or pages when a stored procedure has failed. I did a lot of Google searches yesterday and finally found an archived discussion on DBforums.com explaining about the use of the osql -b switch. This appears to have addressed my needs.Thanks, Dave |
|
|
|
|
Best way to avoid SQLSTATE 01000 in Job?