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
 Transact-SQL (2000)
 Passing SQL output...

Author  Topic 

OJ
Starting Member

3 Posts

Posted - 2006-05-09 : 18:03:03
Hello all, I'm fairly new to SQL and I am trying to pass the output from a SQL query to the input of a stored procedure.
I have a query that checks for running SQL jobs in MSDB (msdb.dbo.sp_get_composite_job_info NULL, NULL, NULL, NULL, NULL, NULL, 1, NULL, NULL). I want to pass the output of this query to a stored procedure that uses SMTP to E-mail(sp_send_cdosysmail). I have SMTP running on my windows server and the SP works like a charm. How do I get the output of the above query passed to the SP? Is it even possible?

Thanks

Topic

nosepicker
Constraint Violating Yak Guru

366 Posts

Posted - 2006-05-09 : 19:54:54
You can create a table that will store the output of sp_get_composite_job_info. Then populate like this:

INSERT INTO YourTable
EXEC msdb.dbo.sp_get_composite_job_info

Then you can SELECT anything you want out of that table and pass it into sp_send_cdosysmail.
Go to Top of Page

OJ
Starting Member

3 Posts

Posted - 2006-05-17 : 14:08:24
How do I check for the existence or validate the ouptut of a select statement in a variable? What I'm trying to do is get an E-mail if I have SQL jobs still running. If there are any rows returned from sp_get_composite_job_info I would like to receive an E-mail via sp_send_cdosysmail. If not, don't care.

If the output of a select statement is true(or equal to a variable) then e-mail else quit

I'm just not sure how to pass the output from a select statement to a variable to be able to "if then else" the output
IF select output = blah (> than blah)
THEN "do this"
or pass the output if it meets the criteria

it's a little hard to explain, if this post is convoluted I apoligize in advance

Thanks
Go to Top of Page
   

- Advertisement -