La stored procedure CS_SP_DMOExportToExcel rappresenta un esempio di utilizzo degli oggetti SQLDMO.SQLServer per esportare il risultato di una query su foglio Excel specificando il worksheet su cui trasferire i dati. Il worksheet specificato dal parametro @WorksheetIndex verrà "pulito" e ri-assegnato con i dati della query. Il file XLS esistente non verrà sovrascritto.

/* Stored Procedure CS_SP_DMOExportToExcel per esportazione dati su Excel */

IF (OBJECT_ID('CS_SP_DMOExportToExcel', 'P') IS NOT NULL)
 DROP PROCEDURE DBO.[CS_SP_DMOExportToExcel]
GO
 
/* Creazione stored procedure */
CREATE PROCEDURE [dbo].[CS_SP_DMOExportToExcel]
 (@SourceServer AS VARCHAR(30),
   @SourceUID AS VARCHAR(30)=NULL,
   @SourcePWD AS VARCHAR(30)=NULL,
   @QueryText AS VARCHAR(8000),
   @filename AS VARCHAR(256),
   @WorksheetName AS VARCHAR(100)='Worksheet',
   @WorksheetIndex AS INT,
   @RangeName AS VARCHAR(80)='MyRangeName',
   @RowsAffected AS VARCHAR(512) OUTPUT)
 
AS BEGIN
 
 /*
     Descrizione   : Stored procedure per esportazione dati da query T-SQL
                     a file Excel. Se il file Excel specificato non esiste
                     viene creato
 
     Parametri     : @SourceServer   = Nome server
                     @SourceUID      = Nome utente
                     @SourcePWD      = Password
                     @QueryText      = Query T-SQL di estrazione dei dati da esportare
                     @filename       = Path e nome del file XLS
                     @WorksheetName = Nome del foglio in cui esportare i dati
                     @WorksheetIndex = Posizione del foglio (attenzione, il foglio
                                       corrispondente alla posizione specificata verrà sovrascritto)
                     @RangeName      = Nome range
                     @RowsAffected   = Parametro di OUTPUT, restituisce il numero di righe estratte dalla
                                       query @QueryText ed esportate sul foglio XLS
 
     Sviluppata il : 04 Mar 2008
 
     Ultimo agg.to : 23 Mar 2008
 
 
 */
 
 DECLARE @objServer INT,
          @objQueryResults INT,
          @objCurrentResultSet INT,
          @objExcel INT,
          @objWorkBooks INT,
          @objWorkBook INT,
          @objWorkSheet INT,
          @objRange INT,
          @hr INT,
          @Columns INT,
          @Rows INT,
          @Output INT,
          @currentColumn INT,
          @currentRow INT,
          @ResultSetRow INT,
          @off_Column INT,
          @off_Row INT,
          @command VARCHAR(255),
          @ColumnName VARCHAR(255),
          @value VARCHAR(255),
          @strErrorMessage VARCHAR(255),
          @objErrorObject INT,
          @Alphabet VARCHAR(27),
          @T INT,
          @FindFile INT, /* 0=False, -1=True */
          @ColumnType INT
 
 SELECT @Alphabet = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'
 
 SET @RowsAffected = ''
 
 IF (@QueryText IS NULL)
 BEGIN
    RAISERROR ('Errore: Non è stata specificata la query per la stored procedure CS_SP_DMOExportToExcel', 16, 1)
   RETURN 1
 END
 
 IF ((@RangeName = '') OR
      (@RangeName IS NULL))
 BEGIN
    RAISERROR ('Errore: Non è stato specificato il nome del range. Parametro @RangeName per la stored procedure CS_SP_DMOExportToExcel', 16, 1)
    RETURN 2
 END
 
 -- Impostazione del server a local server
 IF (@SourceServer IS NULL)
    SELECT @SourceServer = @@servername
 
 -- Disabilitazione messaggi
 SET NOCOUNT ON
 
 SELECT @strErrorMessage = 'Istanza di DMO',
         @objErrorObject = @objServer
 
 EXEC @hr = sp_OACreate 'SQLDMO.SQLServer', @objServer OUT
 
 IF (@SourcePWD IS NULL) OR
     (@SourceUID IS NULL)
 BEGIN
    -- Trusted connection
    IF (@hr = 0)
      SELECT @strErrorMessage = 'Impostazione login con windows authentication su '
                                +@SourceServer,
                                @objErrorObject = @objServer
    IF (@hr = 0)
      EXEC @hr = sp_OASetProperty @objServer, 'LoginSecure', 1
   
    IF (@hr = 0)
      SELECT @strErrorMessage = 'Login in corso al server richiesto, utilizzando windows authentication su '
                                +@SourceServer
    IF (@SourceUID IS NULL) AND
       (@hr = 0)
      EXEC @hr = sp_OAMethod @objServer,
                             'Connect',
                             NULL,
                             @SourceServer
   
    IF (@SourceUID IS NOT NULL) AND
       (@hr = 0)
      EXEC @hr = sp_OAMethod @objServer,
                             'Connect',
                             NULL,
                             @SourceServer,
                             @SourceUID
 END
 ELSE BEGIN
    IF (@hr = 0)
      SELECT @strErrorMessage = 'Connessione a '''+@SourceServer+
                                ''' con user ID '''+@SourceUID+'''',
             @objErrorObject = @objServer
    IF (@hr = 0)
      EXEC @hr = sp_OAMethod @objServer,
                             'Connect',
                             NULL,
                             @SourceServer,
                             @SourceUID,
                             @SourcePWD
 END
 
 -- Esecuzione della query
 IF (@hr = 0)
    SELECT @strErrorMessage = 'Esecuzione query "'
                              +@querytext+'", su '+@SourceServer,
           @objErrorObject = @objServer,
           @command = 'ExecuteWithResults("' + @QueryText + '")'
 IF (@hr = 0)
    EXEC @hr = sp_OAMethod @objServer, @command, @objQueryResults OUT
 
 IF (@hr = 0)
    SELECT @strErrorMessage = 'Restituzione del primo result set per "'
                              +@querytext+'", su '+@SourceServer,
           @objErrorObject = @objQueryResults
 
 IF (@hr = 0)
    EXEC @hr = sp_OAMethod @objQueryResults, 'CurrentResultSet', @objCurrentResultSet OUT
 IF (@hr = 0)
    SELECT @strErrorMessage = 'Restituzione di righe e colonne "'
                              +@querytext+'", su '+@SourceServer
 IF (@hr = 0)
    EXEC @hr = sp_OAMethod @objQueryResults, 'Columns', @Columns OUT
 
 IF (@hr = 0)
    EXEC @hr = sp_OAMethod @objQueryResults, 'Rows', @Rows OUT
 
 -- Assegnazione parametro di output row(s) affected
 IF (@hr = 0)
    SET @RowsAffected = '(' + LTRIM(RTRIM(STR(@Rows))) + ' row(s) affected)'
 
 -- Il risultato della query è disponibile. Start up della connessione a Excel
 IF (@hr = 0)
    SELECT @strErrorMessage = 'Creazione dell''applicazione Excel su '
                              +@SourceServer,
           @objErrorObject = @objExcel
 IF (@hr = 0)
    EXEC @hr = sp_OACreate 'Excel.Application', @objExcel OUT
 IF (@hr = 0)
    SELECT @strErrorMessage = 'Restituzione dell''oggetto WorkBooks '
 
 
 -- Apertura oggetto WorkBooks foglio Excel per sovrascrivere solo il
 -- WorkSheet
 IF (@hr = 0)
    EXEC @hr = sp_OAMethod @objExcel,
                           'WorkBooks.Open',
                           @objWorkBooks OUT,
                           @filename
 
 IF (@hr = 0)
 BEGIN
    -- Apertura WorkBooks Excel avvenuta correttamente
    Set @FindFile = -1
   
    -- Selezione WorkSheet passato come parametro
    IF (@objWorkSheet <> 0)
      EXEC @hr = sp_OADestroy @objWorkSheet
 
    EXEC @hr = sp_OAMethod @objWorkBooks,
                           'Worksheets.Item',
                           @objWorkSheet OUT,
                           @WorksheetIndex
    IF (@hr = 0)
      PRINT ('Selezione WorkSheet avvenuta correttamente')
    ELSE                                  
      PRINT ('Selezione WorkSheet terminata con errori')
 
    -- Attivazione WorkSheet
    IF (@hr = 0)
      EXEC @hr = sp_OAMethod @objWorkSheet, 'Activate'
 
    IF (@hr = 0)
      PRINT ('Attivazione WorkSheet avvenuta correttamente')
    ELSE                                  
      PRINT ('Attivazione WorkSheet terminata con errori')
 
    -- Eliminazione WorkSheet
    IF (@hr = 0)
      EXEC @hr = sp_OAMethod @objWorkSheet, 'Cells.Clear', @T OUT
      /*EXEC @hr = sp_OAMethod @objWorkSheet, 'Delete', @T OUT*/
 
    IF (@hr = 0)
      PRINT ('Pulizia celle WorkSheet avvenuta correttamente')
    ELSE                                  
      PRINT ('Pulizia celle WorkSheet terminata con errori')
 END
 ELSE BEGIN
    -- Apertura WorkBooks fallita, il file Excel potrebbe non esistere
    Set @FindFile = 0
 
    -- Creazione oggetto WorkBooks
    EXEC @hr = sp_OAGetProperty @objExcel, 'WorkBooks', @objWorkBooks OUT
 
    -- Creazione workbook
    IF (@hr = 0)
      SELECT @strErrorMessage = 'Aggiunta di un workbook ',
             @objErrorObject = @objWorkBooks
 
    IF (@hr = 0)
      EXEC @hr = sp_OAGetProperty @objWorkBooks, 'Add', @objWorkBook OUT
 
    -- Creazione worksheet
    IF (@hr = 0)
      SELECT @strErrorMessage = 'Aggiunta di un worksheet ',
             @objErrorObject = @objWorkBook
 
    IF (@hr = 0)
      EXEC @hr = sp_OAGetProperty @objWorkBook,
                                  'worksheets.Add',
                                  @objWorkSheet OUT
 
 END
 
 IF (@hr = 0)
    SELECT @strErrorMessage = 'Assegnazione nome worksheet con "'+@WorksheetName+'"',
           @objErrorObject = @objWorkBook
 IF (@hr = 0) 
    EXEC @hr = sp_OASetProperty @objWorkSheet, 'name', @WorksheetName
 
 SELECT @currentRow = 1
 
 -- Scrittura column headings
 SELECT @currentColumn = 1
 WHILE (@currentColumn <= @Columns AND @hr = 0)
 BEGIN
    IF (@hr = 0)
      SELECT @strErrorMessage = 'Restituzione column heading ' +LTRIM(STR(@currentcolumn)),
             @objErrorObject = @objQueryResults,
             @Command = 'ColumnName(' +CONVERT(VARCHAR(3),@currentColumn)+')'
 
    IF (@hr = 0)
      EXEC @hr = sp_OAGetProperty @objQueryResults, @command, @ColumnName OUT
     
    IF (@hr = 0)
      SELECT @strErrorMessage = 'Assegnazione column heading '+
                                + LTRIM(STR(@currentColumn))
                                + ' dalla query',
             @objErrorObject = @objExcel,
             @command = 'Cells('+ LTRIM(STR(@currentRow)) +', '
                                + LTRIM(STR(@CurrentColumn)) +').value'
    IF (@hr = 0)
      EXEC @hr = sp_OASetProperty @objExcel, @command, @ColumnName
     
    SELECT @currentColumn = @currentColumn + 1
 END
 
 -- Modifica del formato per la prima riga (headings)
 IF (@hr = 0)
    SELECT @strErrorMessage = 'Formattazione colonne con Bold ',
           @objErrorObject = @objWorkSheet,
           @command = 'Range("A1:'
                      +SUBSTRING(@alphabet,@currentColumn/26,1)
                      +SUBSTRING(@alphabet,@currentColumn % 26,1)
                      +'1'+'").font.bold'
 IF (@hr = 0)
    EXEC @hr = sp_OASetProperty @objWorkSheet, @command, 1
 
 -- Scrittura dei dati
 SELECT @currentRow = 2
 WHILE (@currentRow <= @Rows+1) AND
        (@hr = 0)
 BEGIN
    SELECT @currentColumn = 1
   
    WHILE (@currentColumn <= @Columns) AND
          (@hr = 0)
    BEGIN
 
      IF (@hr = 0)
        SELECT @strErrorMessage = 'Restituzione dei valori dalla query'
                                  + LTRIM(STR(@currentRow)) +','
                                  + LTRIM(STR(@currentRow)) +')',
               @objErrorObject = @objQueryResults,
               @ResultSetRow = @CurrentRow-1
 
      -- Restituzione column type
      IF (@hr = 0)
        SELECT @strErrorMessage = 'Restituzione column type ' +LTRIM(STR(@currentcolumn)),
               @objErrorObject = @objQueryResults,
               @Command = 'ColumnType(' +CONVERT(VARCHAR(3),@currentColumn)+')'
 
      IF (@hr = 0)
        EXEC @hr = sp_OAGetProperty @objQueryResults, @command, @ColumnType OUT
     
      IF (@hr = 0)
      BEGIN
        IF (@ColumnType = 1)
          EXEC @hr = sp_OAMethod @objQueryResults,
                                 'GetColumnString',
                                 @value OUT,
                                 @ResultSetRow,
                                 @currentColumn
        ELSE IF ((@ColumnType = 6) OR
                 (@ColumnType = 7) OR
                 (@ColumnType = 8))
          EXEC @hr = sp_OAMethod @objQueryResults,
                                 'GetColumnFloat',
                                 @value OUT,
                                 @ResultSetRow,
                                 @currentColumn
        ELSE
          EXEC @hr = sp_OAMethod @objQueryResults,
                                 'GetColumnString',
                                 @value OUT,
                                 @ResultSetRow,
                                 @currentColumn
      END
 
      IF (@hr = 0)
        SELECT @strErrorMessage = 'Assegnazione dei valori restituiti dalla query'
                                  + LTRIM(STR(@CurrentRow-1)) +', '
                                  + LTRIM(STR(@currentcolumn))+')' ,
               @objErrorObject = @objExcel,
               @command = 'Cells('+ STR(@currentRow) +', '
                                  + STR(@CurrentColumn) +').value'
      IF (@hr = 0)
        EXEC @hr = sp_OASetProperty @objExcel, @command, @value
 
      SELECT @currentColumn = @currentColumn + 1
    END
    SELECT @currentRow = @currentRow + 1
 END
  
 -- Definizione nome range
 --Cells(1, 1).Resize(10, 5).Name = "TheData"
 IF (@hr = 0)
    SELECT @strErrorMessage = 'Assegnazione del nome al range '
                              + LTRIM(STR(@CurrentRow-1)) +', '
                              + LTRIM(STR(@currentcolumn-1)) +')' ,
           @objErrorObject = @objExcel,
           @command = 'Cells(1, 1).Resize('+ STR(@currentRow-1) +', '
                                           + STR(@CurrentColumn-1) +').Name'
 IF (@hr = 0)
    EXEC @hr = sp_OASetProperty @objExcel, @command, @RangeName
 
 -- Auto-fit delle colonne scritte
 IF (@hr = 0)
    SELECT @strErrorMessage = 'Auto-fit delle colonne ',
           @objErrorObject = @objWorkSheet,
           @command = 'Columns("A:'
                      + SUBSTRING(@alphabet,(@Columns / 26), 1)
                      + SUBSTRING(@alphabet,(@Columns % 26), 1) +
                      '").autofit'
 
 IF (@hr = 0) --insert into @bucket(bucket)
    EXEC @hr = sp_OAMethod @objWorkSheet, @command, @output out
 
 IF (@FindFile = 0)
 BEGIN
    IF (@hr = 0)
      SELECT @command = 'del "' + @filename + '"'
 
    IF (@hr = 0)
      EXECUTE master..xp_cmdshell @Command, no_output
 
    IF (@hr = 0)
      SELECT @strErrorMessage = 'Salvataggio del workbook come "'+@filename+'"',
             @objErrorObject = @objRange,
             @command = 'SaveAs("' + @filename + '")'
 
    IF (@hr = 0)
      EXEC @hr = sp_OAMethod @objWorkBook, @command
 END
 ELSE BEGIN
    IF (@hr = 0)
      EXEC @hr = sp_OAMethod @objExcel, 'ActiveWorkbook.Save'   
 END
 
 IF (@hr = 0)
    SELECT @strErrorMessage = 'Chiusura Excel ',
           @objErrorObject = @objExcel
 
 IF (@FindFile = 0)
    EXEC @hr = sp_OAMethod @objWorkBook, 'Close'
 ELSE
    EXEC @hr = sp_OAMethod @objExcel, 'Workbooks.Close'
 
 EXEC sp_OAMethod @objExcel, 'Close'
 
 IF (@hr <> 0)
 BEGIN
    DECLARE @Source VARCHAR(255),
            @Description VARCHAR(255),
            @Helpfile VARCHAR(255),
            @HelpID INT
   
    EXECUTE sp_OAGetErrorInfo @objErrorObject,
                              @source output,
                              @Description output,
                              @Helpfile output,
                              @HelpID output
 
    SELECT @hr, @source, @Description,@Helpfile,@HelpID output
 
    SELECT @strErrorMessage = 'Error whilst '
                              + COALESCE(@strErrorMessage, 'doing something')
                              + ', ' + COALESCE(@Description, '')
    RAISERROR (@strErrorMessage, 16, 1)
 END
 
 -- Libero la memoria allocata
 EXEC sp_OADestroy @objServer
 EXEC sp_OADestroy @objQueryResults
 EXEC sp_OADestroy @objCurrentResultSet
 EXEC sp_OADestroy @objExcel
 EXEC sp_OADestroy @objWorkSheet
 EXEC sp_OADestroy @objWorkBooks
 EXEC sp_OADestroy @objWorkBook
 EXEC sp_OADestroy @objRange
 EXEC sp_OADestroy @objErrorObject
 
 -- Abilitazione messaggi
 SET NOCOUNT OFF
 
 RETURN @hr
END

/*

-- Utilizzo con autenticazione SQL Server

DECLARE @ROWS VARCHAR(512)
CS_SP_DMOExportToExcel /*@SourceServer = */ '<Server_Name>', 
                       /*@SourceUID = */ '<User_Name>',
                       /*@SourcePWD = */ '<Password>', 
                       /*@QueryText = */ 'USE DBTEST Select Titolo From Articoli', 
                       /*@Filename = */ 'C:\TitoliTest.xls', 
                       /*@WorksheetName = */ 'MyTitoli', 
                       /*@WorksheetIndex = */ 1,
                       /*@RangeName = */ 'MyRangeTitoli',

                       @Rows OUTPUT,


-- Utilizzo con integrated security

DECLARE @ROWS VARCHAR(512)
CS_SP_DMOExportToExcel /*@SourceServer = */ '<Server_Name>',
                       /*@QueryText = */ 'USE DBTEST Select Titolo From Articoli',
                       /*@filename = */ 'C:\TitoliTest.xls', 
                       /*@WorksheetName = */ 'MyTitoli', 
                       /*@WorksheetIndex = */ 1,
                       /*@RangeName = */ 'MyRangeTitoli',

                       @Rows OUTPUT

*/