SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 Passing SQL output...
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

OJ
Starting Member

USA
3 Posts

Posted - 05/09/2006 :  18:03:03  Show Profile  Reply with Quote
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

USA
366 Posts

Posted - 05/09/2006 :  19:54:54  Show Profile  Reply with Quote
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

USA
3 Posts

Posted - 05/17/2006 :  14:08:24  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000