| Author |
Topic |
|
SQLSoaker
Posting Yak Master
169 Posts |
 Posted - 2008-12-16 : 12:43:03
|
| Hello,I have a stored procedure that emails a query. I need to know how to schedule this procedure to run at specific times for t-sql 2005.I have found info on this for sql 2000 but not 05'.Thanks in advance.Regards. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-12-16 : 12:47:39
|
| Use jobs in SQL Server Agent.You need to have permission to create and run it. |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
SQLSoaker
Posting Yak Master
169 Posts |
Posted - 2008-12-16 : 14:36:26
|
| Thankyou tkizer once again. One more question: Now that I have created a job and scheduled it, how do you tell the job to execute the stored procedure? Thank you! (Again, I am new so please excuse) |
|
|
|
SQLSoaker
Posting Yak Master
169 Posts |
Posted - 2008-12-16 : 14:43:11
|
| Steps! Thanks! |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
SQLSoaker
Posting Yak Master
169 Posts |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
SQLSoaker
Posting Yak Master
169 Posts |
Posted - 2008-12-16 : 16:37:19
|
quote:
Originally posted by tkizer
What are you using for an email solution?Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog
Simply emailing from a mail server that runs Outlook... The problem is that I don't want to alter the SP that we currently have. However, I do need to run it with various parameters in each email.I have tested running multiple parameter like : 1001..1010No luck.If dbmail would allow multiple @query param's then that would solve it... Something like@query@query1@query2etc. etc.Either that or something like:@query = 'select * from table1, select * from table2, select * from table3...If all the query results can go to a file or simply in the body of the email, that would be great!...! But how???Thanks again tkizer, you've been a real help! |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
SQLSoaker
Posting Yak Master
169 Posts |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
SQLSoaker
Posting Yak Master
169 Posts |
Posted - 2008-12-16 : 17:23:25
|
quote:
Originally posted by tkizer
Yes. You could choose to use 3 job steps, which could make it easier when looking back at job history for failures. But I'd probably just use one job step, cuz I'm lazy.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog
Okay, I'm kind of confused about what to do here...I have a SP (lets call it sp_myMail)that generates an email thru DBmail that calls another SP (lets call it sp_mySP). Currently I set up a job that calls the stored procedure:exec sp_mySPThis sp calls another (sp_myMail) where I run the parameters. Something like:@query= 'exec sp_myMail @parm1 ='' @parm2 ='''tkizer, what you have suggested is calling the sp multiple times. But I am not calling the sp_myMail from the job, I am calling sp_mySP so I can't use multiple sp calls in one step!Also, I can't run multiple queries in DBMail like this:@query = 'select * from table1, select * from table2 etc.'Am I missing something here? I can set up a job to run sp_mySP but that doesn't help because it does not utilize DBMail (its just a query in the stored procedure!)Ugh, I hope I am not confusing! |
|
|
|
SQLSoaker
Posting Yak Master
169 Posts |
Posted - 2008-12-16 : 17:32:51
|
| want to clarify a little:This is the sp that calls another.set ANSI_NULLS ONset QUOTED_IDENTIFIER ONGOALTER PROCEDURE [dbo].[testEmailattachSP_28] AS BEGIN SET NOCOUNT ON -- do some other actions DECLARE @body1 VARCHAR(1024) SET @body1 = 'This is a test to execute a stored procedure thru a job. During this test you should receieve an email every morning at 7 AM. The attached file is the aging bucket report for account number 100001 for xxx. This message was sent on:'+ CONVERT(VARCHAR, GETDATE()) EXEC msdb.dbo.sp_send_dbmail @recipients='me.me@me.com;, @body= @body1, @execute_query_database = 'xxx_xxx_Production', @query = 'sp_AgingBuckets @dataareaid = ''xxx'', @accountnum = ''100001''', --@query = 'select top(5)accountnum, name from custtable where dataareaid = ''xxx''', @attach_query_result_as_file = 1, --@query_result_width = 250, @query_attachment_filename = 'testing.xls' ENDNow I scheduled a job to run this command (in one step):exec testEmailattachSP_28But I need to run sp_AgingBuckets using multiple parameters and generate one email from all of em'.Ahhh! |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
SQLSoaker
Posting Yak Master
169 Posts |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
SQLSoaker
Posting Yak Master
169 Posts |
Posted - 2008-12-17 : 09:24:18
|
quote:
Originally posted by tkizer
That's not what I meant. Call sp_mySP 3 different times, so that 3 emails are sent out. Otherwise, you are going to need to modify the stored procedure to attach multiple files.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog
Understood, but calling sp_mySP three times will send out three emails. I want to call this once and generate one email with three different queries attached (those queries are another call to an sp).Going in circles on this but let me try to explain what I want.I want to be able to call the sp that sends the email once. But within that sp I want to be able to attach MULTIPLE query results (this is another call to another sp) to either a file or the body of the email. Currently I cannot do this because DBMail only accepts one query.So my job is scheduled to call the sp once, which is what I want, but I want to attach multiple results to a file...I am not sure that this could be done with just one email!!!PS: tkizer, i really appreciate the help on this! |
|
|
|
SQLSoaker
Posting Yak Master
169 Posts |
Posted - 2008-12-17 : 09:41:20
|
| Figured something out. Created yet another stored procedure that executes the sp multiple times. Then in the sp_mail procedure I execute this one. (multiple exec calls to the sp). This gives me multiple results in the file attachment in the email! |
|
|
|
|
Automatic emails
Tara Kizer