01 June 2015
![]()
Aug 21, 2011 SQL Server Database Engine https. Execute msdb.spsenddbmail @fileattachments = 'XXX' sends out the specified file. My requirement is to send ou the dynamic file as the timestamp gets appended to the generated file. Is there any way to send ou the dynamic files. I believe the problem is SQL Server doesn't manage external files. I mean, whatever file you want to send, must be in the same machine in which the SQL Server is. So the solution is to previosly copy your file test.txt to a folder in your server, and then you can send it without problems (that's what I've done).
A few weeks ago I received one challenge: Send audit reports (based on SQL Server Audit) through e-mail.
![]()
The easiest format to use is .CSV, no doubt about this. But how the user would read the .CSV file ?
Of course you already thinked: Excel !
That’s not so easy as we would expect. If you try to create a .CSV using sp_send_dbmail and read in excel you will be disapointed: Excel will not understand the columns, the data will be a mess.
There is an interesting solution for this: We need to send, together the data, a few instructions to excel about our csv file.
To send this instruction we need to change our query: The instruction needs to be on top of the file, so we need to create an alias for the first field in our query result, concatenating the instruction with the field name.
The instruction we need do add is “sep=,”, to ensure that excel will understand comma field separator.
Here a small example with a simple query:
Let’s notice the following details:
![]()
Now Excel can read this file and you can send many query results through e-mail.
![]() Comments are closed.
|
AuthorWrite something about yourself. No need to be fancy, just an overview. Archives
March 2023
Categories |