Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Scheduled Job: Email Output File within same step Expand / Collapse
Author
Message
Posted Monday, July 14, 2008 9:07 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, December 17, 2014 4:07 AM
Points: 365, Visits: 966
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
Post #533607
Posted Monday, July 21, 2008 9:32 PM
UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Friday, April 5, 2013 4:43 PM
Points: 1,473, Visits: 1,314
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.
Post #538128
Posted Wednesday, July 23, 2008 4:03 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 12:15 PM
Points: 119, Visits: 1,554
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
Post #539751
Posted Thursday, July 24, 2008 2:28 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, December 17, 2014 4:07 AM
Points: 365, Visits: 966
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
Post #539928
Posted Thursday, July 24, 2008 10:06 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 12:15 PM
Points: 119, Visits: 1,554
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
Post #540331
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse