Αφού ρυθμίσουμε το Database Mail στο SQL Server έχουμε τη δυνατότητα χρησιμοποιόντας την εντολή sp_send_dbmail να στείλουμε ηλεκτρονικά μηνύματα με τη σύνταξη που ορίζει η Microsoft. Συμπεριλαμβάνονται φυσικά και τα συννημένα αρχεία (attachments) αλλά σε ότι αφορά τα υπολογιστικά φύλλα του Excel συναντώνται 2 προβλήματα.
Πρώτον, αν η παράμετρος κλήσης @query_attachment_filename είναι απευθείας ένα .xls αρχείο τότε το resultset – συμπεριλαμβανομένων και των τίτλων – καταλήγει στο σύνολό του σε μία κολώνα (unformatted). Ο μόνος τρόπος για να πάρουμε ένα formatted αποτέλεσμα είναι να στείλουμε το αρχείο σαν .csv, comma (or tab, space κτλ) separated (το δηλώνουμε στην παράμετρο @query_result_separator), το οποίο ο παραλήπτης μπορεί να ανοίξει με το Excel ακολουθώντας τα βήματα του wizard και τη διαδικασία που προβλέπεται για αυτού του είδους τα αρχεία για να πάρει σωστά το αποτέλεσμα.
Για να μπορέσουμε να στείλουμε το συννημένο κατευθείαν σαν formmated excel file χρειάζεται να γράψουμε λίγο διαφορετικά τον κώδικα δηλώνοντας τον separator μέσα στο string του resultset και μόνο για το πρώτο πεδίο όπως στο παράδειγμα που ακολουθεί:
SQL Script
DECLARE @SQL NVARCHAR(3000), @column1name NVARCHAR (50)
SET @Column1Name = '[sep=,' + CHAR(13) + CHAR(10) + 'column1]'
SET @SQL =
N'set nocount on; SELECT col1 AS ' + @Column1Name + ', col2 AS column2 FROM mytable'
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'MyProfile',
@recipients = 'mymail@gmail.com',
@subject = 'MySubject',
@body = 'MyBody',
@query = @SQL,
@attach_query_result_as_file = 1,
@query_attachment_filename = 'myfilename.xls',
@query_result_header = 1,
@query_result_separator = ',',
@query_result_no_padding = 1,
@query_result_width = 32767
Το αποτέλεσμα είναι ένα multi column Excel spreadsheet.
Δεύτερον, δεν υπάρχει εντολή για να αλλάξουμε ανάμεσα σε UTF/ANSI οπότε οι ελληνικοί χαρακτήρες στην περίπτωσή μας δεν εμφανίζονται.
Για να το πετύχουμε αυτό πρέπει να τροποποιήσουμε τη system stored procedure sp_send_dbmail η οποία βρίσκεται στην msdb κάτω από τις system databases.
- Προσθέτουμε μία επιπλέον παράμετρο κλήσης στη storde procedure (@ANSI_Attachment BIT = 0)
- Αντικαθιστούμε τον κώδικα IF(@AttachmentsExist = 1) BEGIN ....... END μέσα σ’αυτήν όπως περιγράφεται παρακάτω:
Αντί
SQL Script
IF(@AttachmentsExist = 1)
BEGIN
--Copy temp attachments to sysmail_attachments
INSERT INTO sysmail_attachments(mailitem_id, filename, filesize, attachment)
SELECT @mailitem_id, filename, filesize, attachment
FROM sysmail_attachments_transfer
WHERE uid = @temp_table_uid
END
Βάζουμε
SQL Script
IF(@AttachmentsExist = 1)
BEGIN
IF (@ANSI_Attachment = 1)
BEGIN
--Copy temp attachments to sysmail_attachments
INSERT INTO sysmail_attachments(mailitem_id, filename, filesize, Attachment)
SELECT @mailitem_id, filename, filesize, convert(varbinary(max), substring(
-- remove BOM mark from unicode
convert(varchar(max), CONVERT (nvarchar(max), attachment)),
2, DATALENGTH(attachment)/2 ))
FROM sysmail_attachments_transfer
WHERE uid = @temp_table_uid
END ELSE
BEGIN
--Copy temp attachments to sysmail_attachments
INSERT INTO sysmail_attachments(mailitem_id, filename, filesize, attachment)
SELECT @mailitem_id, filename, filesize, attachment
FROM sysmail_attachments_transfer
WHERE uid = @temp_table_uid
END
END
Ο τρόπος κλήσης πλέον είναι:
SQL Script
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'MyProfile',
@recipients = 'mymail@gmail.com',
@subject = 'MySubject',
@body = 'MyBody',
@query = @SQL,
@attach_query_result_as_file = 1,
@query_attachment_filename = 'myfilename.xls',
@query_result_header = 1,
@query_result_separator = ',',
@query_result_no_padding = 1,
@query_result_width = 32767,
@ANSI_Attachment = 1 -- Νέα παράμετρος
Το αποτέλεσμα πλέον είναι το επιθυμητό!