go backarticles

Articles of SQLschool.gr Team

SQL Server resultset as formatted Excel spreadsheet attachment in ANSI format.

Komninos Liakos

Αφού ρυθμίσουμε το 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        -- Νέα παράμετρος

Το αποτέλεσμα πλέον είναι το επιθυμητό!


Komninos Liakos

Komninos Liakos

Komninos is a Database Developer. He has been involved in software development since school years when he was honoured as a state IT contest finalist in 1993. During his career starting in 2001 he has worked as a senior consultant and database developer. Specialized in SQL Server since version 2005 and dealing with Databases, mainly Database Design and Development.

  


Next Events

NO EVENTS THIS TIME



Leave your comment

COMMENT

FULL NAME

EMAIL ADDRESS

We use Gravatar

WEB SITE



captcha


 

Newsletter

If you want to receive updates from us subscribe below with your email.
Follow us in
sql school greece logo
© 2010-2021 All rights reserved

This site uses cookies for operational and analytics purposes only. By continuing to browse this site, you agree to their use.