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?