Πριν από τρία χρόνια είχα γράψει ένα post με αυτό το θέμα. Το συγκεκριμένο post είναι από τα δημοφιλή θα έλεγα και έχει αρκετούς, από όσο γνωρίζω, που έχουν χρησιμοποιήσει την συγκεκριμένη πρόταση.

Παρόλα αυτά όμως το παλαιό post χρειάζεται ένα refactoring καθώς από τότε που γράφτηκε μέχρι σήμερα έχουν μεσολαβήσει αρκετές αλλαγές που σε αρκετές περιπτώσεις δεν θα δουλέψει. Για αυτό σε αυτό δίνω μια νέα βελτιωμένη εκδοχή.

Αλλαγές που έχουν προκύψει από την παλαιότερη έκδοση

Πρώτη και σημαντική αλλαγή είναι το γεγονός ότι πλέον δεν υπάρχει ο Microsoft Jet 4.0 OLEDB provider σε x64. Αυτό είναι ένα θέμα καθώς αν είμαστε σε SQL Server x64 δεν θα μπορέσει η λύση να δουλέψει.

Ένα ακόμα θέμα είναι ότι πλέον τα excel files έχουν αλλάξει format από την έκδοση του Office 2007 και είναι σε xlsx format που ως γνωστό είναι xml εσωτερικά.

Αυτοί ήταν και οι βασικοί λόγοι που αποφάσισα να ασχοληθώ ξανά μαζί του.

Ο «αντικαταστάτης» του Jet

Το πρώτο πράγμα το οποίο χρειάζεται να κάνουμε είναι κατεβάσουμε και να εγκαταστήσουμε στον SQL Server ένα νέο provider που θα μπορούσαμε να πούμε ότι είναι ο «αντικαταστάτης» του Jet.

Αυτός είναι ο Microsoft Access Database Engine 2010 provider. Μπορείτε να τον κατεβάσετε από το link αυτό. Θα ήθελα όμως να διαβάσετε τα σχετικά με αυτόν στοιχεία που υπάρχουν σε αυτό, καθώς περιέχει σημαντικές πληροφορίες για το σκοπό που έχει δημιουργηθεί ο συγκεκριμένος provider.
Φυσικά θα πρέπει να κατεβάσετε αυτόν που ταιριάζει στην πλατφόρμα που είστε (x86, x64).

Ρυθμίσεις του ACE provider στον SQL Server.

Για να δουλέψει ο συγκεκριμένος provider με το σενάριο που θέλουμε και δεν είναι άλλο από το export σε excel θα πρέπει να κάνουμε κάποιες ρυθμίσεις σε αυτόν. Οι ρυθμίσεις αυτές αφορούν τα messages με αριθμούς 7399 & 7330 τα οποία θα εμφανιστούν σε όλους που θα προσπαθήσουν να εκτελέσουν την διαδικασία. Οι ρυθμίσεις αυτές αφορούν κάποια properties και μπορούν να γίνουν εκτελώντας το παρακάτω script.

EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1;  

Υποστήριξη των xlsx

Όπως είπα και παραπάνω το format που υπάρχει πλέον στα office files είναι σε xml. Για το λόγο αυτό θα δείτε στο κώδικα της διαδικασίας ότι κατά την δημιουργία του linked server θα πρέπει σαφέστατα να το ορίσω. Αυτό γίνεται αν στο connection string στα settings του προσθέσω την λέξης Xml μετά από την αναφορά του excel. Όπως φαίνεται στο παρακάτω  code snipet.

EXEC sp_addlinkedserver   @server = @linked_server_name
                                , @srvproduct=@server_product_name
                                , @provider=N'Microsoft.ACE.OLEDB.12.0'
                                , @datasrc=@exp_path_filename
                                , @provstr='EXCEL 12.0 Xml;HDR=YES;' ;

Η νέα λύση

Η νέα λύση πατάει στην ουσία στην προηγούμενη απλά έχω κάνει μερικές αλλαγές. Για να την χρησιμοποιήσει κανείς το πρώτο που πρέπει να κάνει είναι να φτιάξει ένα xlsx file σύμφωνα με τις ανάγκες του σε γραμμογράφηση. Αυτό όπως έχω πει και στο παλαιότερο post μπορεί να γίνει αν στην πρώτη γραμμή του excel sheet βάλω κάποιο λεκτικό στην κάθε κολώνα εν ήδη column name. Επίσης θα πρέπει, αν και δεν είναι απαραίτητο, να βάλω ένα δικό μου sheet name αντί για το γνωστό Sheet1. Αυτό το xlsx θα πρέπει να το κάνω copy σε κάποιο folder που το account του SQL Server service έχει δικαιώματα, και ο ρόλος του είναι γίνει το template για την εκτέλεση της διαδικασίας.

Όπως θα δείτε μέσα στην διαδικασία το template αυτό αντιγράφεται με άλλο όνομα και στο αντίγραφο μπαίνουν να δεδομένα.

Για να γίνει η αντιγραφή αυτή χρησιμοποιείται η xp_cmdshell. Αυτή εξ ορισμού δεν μπορεί να εκτελεστεί για λόγους που έχουν να κάνουν με την ασφάλεια. Η συγκεκριμένη procedure δίνει την δυνατότητα μέσα από τον SQL Server να εκτελεστούν commands και programs που υπάρχουν εκτός SQL Server. Γενικά την έχουμε απενεργοποιημένη και την χρησιμοποιούμε κατά βούληση εφόσον την χρειαζόμαστε. Μπορούμε να την ενεργοποιούμε πριν την εκτέλεση της διαδικασία και να την απενεργοποιούμε μετά. Δεν έχω εντάξει αυτό στην διαδικασία μου καθώς μπορεί σε κάποιο σύστημα αυτή να είναι ήδη ενεργοποιημένη και δεν θα ήταν σωστό μέσα από την διαδικασία αυτή να την κλείνω και να μην δουλεύουν άλλα που χρησιμοποιούν αυτή.
Για την ενεργοποίηση αυτής αρκεί να εκτελεστεί το παρακάτω script για την ενεργοποίηση με την τιμή 1 στο τέλος ή 0 για την απενεργοποίηση.

sp_configure 'xp_cmdshell', 1;
GO
RECONFIGURE 
GO

Η stored procedure

Το επόμενο βήμα είναι να δημιουργήσουμε την stored procedure που θα κάνει την όλη διαδικασία. Αυτή μπορούμε να την δημιουργήσουμε είστε στην βάση που εργαζόμαστε είτε ποιο κεντρικά όπως για παράδειγμα στην master database όπως ο κώδικα παρακάτω κάνει.

Η συγκεκριμένη stored procedure έχει τις εξής παραμέτρους:

@linked_server_name : Δίνουμε το όνομα του προσωρινού liked server που θα δημιουργηθεί για να γίνει η διαδικασία του export.
@server_product_name : Δίνουμε ένα string πχ ‘ExcelExportDemo’
@exp_path_filename : To full path name και file name για το αρχείο που περιέχει τα δεδομένα του export
@template : To full path name και file name για του template xlsx
@sheet_name : Το όνομα του sheet όπως είναι στο xlxs.
@columns_list : Tα ονόματα των columns χωρισμένα με comma όπως ακριβώς τα έχουμε ορίσει στο xlsx template πχ ‘id,name,contact’      , @select_query : To query που θα διαβάσει τα δεδομένα και το οποίο θα πρέπει να έχει ακριβώς τον ίδιο αριθμό σε columns όπως αυτός έχει ορισθεί στο xlsx template.

IF ( OBJECT_ID('sp_Export2Excel') > 0 )
BEGIN
    DROP PROC dbo.sp_Export2Excel;
END
GO

CREATE PROC dbo.sp_Export2Excel 
                          @linked_server_name nvarchar(128) -- linked server name
                        , @server_product_name nvarchar(128) -- sever product name
                        , @exp_path_filename nvarchar(1024) -- path and filename for the exported excel file
                        , @template nvarchar(1204) -- path and filename of template file
                        , @sheet_name nvarchar(64) -- the name of excel sheet
                        , @columns_list nvarchar(1024) -- the list of columns name in the excel sheet 1st line
                        , @select_query nvarchar(max) -- the query of data
                        
                        
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @cmdBuilderString nvarchar (1024);
    
    BEGIN TRY
        
        --
        -- copy empty template to exported filename
        --
        SET    @cmdBuilderString = 'copy ' + @template + ' ' + @exp_path_filename;
        EXEC xp_cmdshell @cmdBuilderString , no_output ;
        
        --
        -- if temporary linked server exists delete it
        --
        IF  EXISTS (SELECT srv.name FROM sys.servers srv 
                    WHERE srv.server_id != 0 
                    AND srv.name = @linked_server_name)
        BEGIN
            EXEC master.dbo.sp_dropserver @server=@linked_server_name
                                        , @droplogins='droplogins'
        END

        --
        -- create temporary linked server with excel file
        --
        EXEC sp_addlinkedserver   @server = @linked_server_name
                                , @srvproduct=@server_product_name
                                , @provider=N'Microsoft.ACE.OLEDB.12.0'
                                , @datasrc=@exp_path_filename
                                , @provstr='EXCEL 12.0 Xml;HDR=YES;' ;
        
        --
        -- execute import        
        --
        SET    @cmdBuilderString = 'INSERT ' 
                                + @linked_server_name + '...' 
                                + QUOTENAME(@sheet_name+'$') + ' '
                                + '('+@columns_list+')' + ' '
                                + @select_query +';';
        EXEC (@cmdBuilderString);                                

        --
        -- delete temporary linked server
        --
        EXEC master.dbo.sp_dropserver @server=@linked_server_name
                                , @droplogins='droplogins'
        
    END TRY
    --
    -- error trap
    --
    BEGIN CATCH
        SELECT      ERROR_NUMBER() AS err_number
                , ERROR_MESSAGE() AS err_message; 
    END CATCH

END
GO

Η εκτέλεση της διαδικασίας

Για να εκτελέσουμε την διαδικασία αυτή εφόσον έχουμε υλοποιήσει τα παραπάνω δεν απαιτεί τίποτα περισσότερο από την κλήση της stored procedure όπως στο παράδειγμα που ακολουθεί.

EXEC dbo.sp_Export2Excel 
                          @linked_server_name = N'ExcelData'
                        , @server_product_name = N'xlsexport'
                        , @exp_path_filename = N'C:\temp\customersX.xlsx'
                        , @template = N'C:\temp\customersX_template.xlsx'
                        , @sheet_name ='MyData'
                        , @columns_list = N'id,name,contact'
                        , @select_query = 'select customerid,companyname,contactname from northwind.dbo.customers'
        
GO

/*antonch*/