July 14, 2008 at 9:07 am
I've got a job that kicks off a couple of stored procedures, and sends a couple of emails during execution.
One of the things it does, is to send the output file for the step to a group of users. The only problem is that SQL hasn't fully written to it before it sends the email.
I could put this into a separate step, but then I'd have to do some extra condition checking and also duplicate my users email addresses - which would be a pain in maintenance. Admittedly I could also create a table to hold the email addresses and pull out as necessary.
Is there any way of getting SQL Server to pump out whatever's currently in the print buffer, so that I get the full output file before attaching and sending the email?
I hope I am making sense...
Cheers
Kev
July 21, 2008 at 9:32 pm
Your thought does make sense. But as far as I know, all email profiles can only attach a file written on hard disk.
Surely, you can assign data in buffer to a variable and then include it in your email message.
July 23, 2008 at 4:03 pm
Kevin,
Not sure that I can help, but I'll try.
What kind of file are you sending out, and are you using sp_send_dbmail to do it, or something else?
If using dbmail, are you using the @attach_query_result_as_file option?
Just trying to get a sense of what method you're currently using.
Thanks,
James
July 24, 2008 at 2:28 am
Hi guys,
The file I am trying to send out is the output file from the step that's been run.... think it's chicken and egg syndrome...
I've created the step that executes code. In the advanced menu for the step I've used the T-SQL command options area to output to a txt file. The reason I've done it this way is that it captures all print output.. there's a fair few stored procedures being called, most of them generic, so I can't alter them.
So the step is executed and as it goes along it writes to the output file, but this appears to be in a buffered approach - which is fine up to a point.
During the step there are a couple of points an email is sent where it attaches the output file (D:\temp\myoutput.txt) and sends it - so a couple of people can see how far it's got. All no problem, but the buffer hasn't fully written to the file up to that point in time.
The whole step takes about 10 hours to run, it copies databases from one server to another, unzips, restores and builds another database from the restored, detaches, and zips the built mdf.
It uses Postie to send an smtp mail internally. That bit works fine too.
I could break the main sp out over several steps, but I wanted a single point of entry to the process. If something goes wrong along the way, I can pass into the sp a variable to jump to the right portion of the script and carry on, once whatever has been rectified. I don't have to worry about where my fixed variables are stored as there at the top of the sp, rather than farming them out to a table.
The only other option, if the buffer can't be written out, is when the email is sent out to not attach the output file, but instead just provide a link to it...
SQL ORACLE, you say about storing the buffer data in a variable... would the be possible with print statements, and could you point me in the right direction?
Cheers
Kev
July 24, 2008 at 10:06 am
Kevin,
Very thorough explanation, thanks. Well, I have done SQL procs where I periodically have sent out emails to inform of progress. We use sp_send_dbmail here though. It usually involved a custom proc executed from the main one like:
exec usp_send_progress_email 'whomever@mycompany.com'), 'Status of My Job - Step 2 finished'
...and if you needed to include data, then in your main proc maybe you could load some table, then do a query
when you send the email by adding a 3rd param that you pass such as: 'select field1 'Hdr1', field2 'Hdr2' from mydatabase.dbo.tblMyJobStatus where convert(char(10),dtJobRun,101) = convert(char(10),getdate(),101)'
the executed proc:
Alter procedure usp_send_progress_email @emRecipient varchar(50), @emSubject varchar(100), @emQuery = varchar(500) = null
AS
exec sp_send_dbmail @emRecipient, @emSubject, @emQuery
Would require dbmail set up if you don't have it already.
For more: http://msdn2.microsoft.com/en-us/library/ms190307.aspx
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply