결과 셋을 Excel로 저장하기.

예전에 찾은 자료인데... 가끔 써먹을 때도 있다.
경험상 결과셋을 굳이 Excel로 저장할 필요는 없다. BCP를 이용하여 CSV파일로 저장하면 되는데...
하여간 찾아놓은 고생이 아까워 기록은 해 두는 것이 좋을 듯...

사용할 수 있는 환경이 MSSQL의 보안 권고 사항과 충돌한다.
1. MSSQL 노출 영역 구성대상 / 기능에 대한 노출 영역 구성 / OLE 자동화 사용 : True
2. MSSQL 노출 영역 구성대상 / 기능에 대한 노출 영역 구성 / xp_cmdshell 사용 : True

/*
Version: SQL Server 7.0/2000
Created by: Alexander Chigrik
http://www.MSSQLCity.com/ - all about MS SQL
(SQL Server Articles, FAQ, Scripts, Tips and Test Exams).
This stored procedure can be used to insert the result set of the
particular select statement into Excel file (c:ImportToExcel.xls,
by default).
You can pass the server name, user name, user password, the select
statement to execute, and the file name to store the results set,
as in the example below:
EXEC ExportToExcel @server = '.',
                   @uname = 'sa',
                   @QueryText = 'SELECT au_fname FROM pubs..authors',
                   @filename = 'c:ImportToExcel.xls'
*/
IF OBJECT_ID('ExportToExcel') IS NOT NULL DROP PROC ExportToExcel
GO
CREATE PROCEDURE ExportToExcel (
  @server sysname = null,
  @uname sysname = null,
  @pwd sysname = null,
  @QueryText varchar(200) = null,
  @filename varchar(200) = 'c:ImportToExcel.xls'
)
AS
DECLARE @SQLServer int,
        @QueryResults int,
        @CurrentResultSet int,
        @object int,
        @WorkBooks int,
        @WorkBook int,
        @Range int,
        @hr int,
        @Columns int,
        @Rows int,
        @indColumn int,
        @indRow int,
        @off_Column int,
        @off_Row int,
        @code_str varchar(100),
        @result_str varchar(255)
IF @QueryText IS NULL
  BEGIN
    PRINT 'Set the query string'
    RETURN
  END
-- Sets the server to the local server
IF @server IS NULL SELECT @server = @@servername
-- Sets the username to the current user name
IF @uname IS NULL SELECT @uname = SYSTEM_USER
SET NOCOUNT ON
EXEC @hr = sp_OACreate 'SQLDMO.SQLServer', @SQLServer OUT
IF @hr <> 0
BEGIN
    PRINT 'error create SQLDMO.SQLServer'
    RETURN
END
--  Connect to the SQL Server
IF @pwd IS NULL
  BEGIN
    EXEC @hr = sp_OAMethod @SQLServer, 'Connect', null, @server, @uname
    IF @hr <> 0
       BEGIN
         PRINT 'error Connect'
         RETURN
       END
  END
ELSE
  BEGIN
    EXEC @hr = sp_OAMethod @SQLServer, 'Connect', null, @server, @uname, @pwd
    IF @hr <> 0
      BEGIN
        PRINT 'error Connect'
        RETURN
      END
  END
SELECT @result_str = 'ExecuteWithResults("' + @QueryText + '")'
EXEC @hr = sp_OAMethod @SQLServer, @result_str, @QueryResults OUT
IF @hr <> 0
BEGIN
    PRINT 'error with method ExecuteWithResults'
    RETURN
END
EXEC @hr = sp_OAMethod @QueryResults, 'CurrentResultSet', @CurrentResultSet OUT
IF @hr <> 0
BEGIN
    PRINT 'error get CurrentResultSet'
    RETURN
END
EXEC @hr = sp_OAMethod @QueryResults, 'Columns', @Columns OUT
IF @hr <> 0
BEGIN
    PRINT 'error get Columns'
    RETURN
END
EXEC @hr = sp_OAMethod @QueryResults, 'Rows', @Rows OUT
IF @hr <> 0
BEGIN
    PRINT 'error get Rows'
    RETURN
END
EXEC @hr = sp_OACreate 'Excel.Application', @object OUT
IF @hr <> 0
BEGIN
    PRINT 'error create Excel.Application'
    RETURN
END
EXEC @hr = sp_OAGetProperty @object, 'WorkBooks', @WorkBooks OUT
IF @hr <> 0
BEGIN
    PRINT 'error create WorkBooks'
    RETURN
END
EXEC @hr = sp_OAGetProperty @WorkBooks, 'Add', @WorkBook OUT
IF @hr <> 0
BEGIN
    PRINT 'error with method Add'
    RETURN
END
EXEC @hr = sp_OAGetProperty @object, 'Range("A1")', @Range OUT
IF @hr <> 0
BEGIN
    PRINT 'error create Range'
    RETURN
END
SELECT @indRow = 1
SELECT @off_Row = 0
SELECT @off_Column = 1
WHILE (@indRow <= @Rows)
BEGIN
SELECT @indColumn = 1
WHILE (@indColumn <= @Columns)
BEGIN
EXEC @hr = sp_OAMethod @QueryResults, 'GetColumnString', @result_str OUT, @indRow, @indColumn
IF @hr <> 0
BEGIN
    PRINT 'error get GetColumnString'
    RETURN
END
EXEC @hr = sp_OASetProperty @Range, 'Value', @result_str
IF @hr <> 0
BEGIN
    PRINT 'error set Value'
    RETURN
END
EXEC @hr = sp_OAGetProperty @Range, 'Offset', @Range OUT, @off_Row, @off_Column
IF @hr <> 0
BEGIN
    PRINT 'error get Offset'
    RETURN
END
SELECT @indColumn = @indColumn + 1
END
SELECT @indRow = @indRow + 1
SELECT @code_str = 'Range("A' + LTRIM(str(@indRow)) + '")'
EXEC @hr = sp_OAGetProperty @object, @code_str, @Range OUT
IF @hr <> 0
BEGIN
    PRINT 'error create Range'
    RETURN
END
END
SELECT @result_str = 'exec master..xp_cmdshell ''del ' + @filename + ''', no_output'
EXEC(@result_str)
SELECT @result_str = 'SaveAs("' + @filename + '")'
EXEC @hr = sp_OAMethod @WorkBook, @result_str
IF @hr <> 0
BEGIN
    PRINT 'error with method SaveAs'
    RETURN
END
EXEC @hr = sp_OAMethod @WorkBook, 'Close'
IF @hr <> 0
BEGIN
    PRINT 'error with method Close'
    RETURN
END
EXEC @hr = sp_OADestroy @object
IF @hr <> 0
BEGIN
    PRINT 'error destroy Excel.Application'
    RETURN
END
EXEC @hr = sp_OADestroy @SQLServer
IF @hr <> 0
BEGIN
    PRINT 'error destroy SQLDMO.SQLServer'
    RETURN
END
GO

--Version: SQL Server 7.0/2000
--Created by: Alexander Chigrik
--http://www.MSSQLCity.com/ - all about MS SQL
--(SQL Server Articles, FAQ, Scripts, Tips and Test Exams).
--
--This stored procedure can be used to insert the result set of the particular
--select statement into Excel file (c:ImportToExcel.xls, by default).
--You can pass the server name, user name, user password, the select statement
--to execute, and the file name to store the results set, as in the example below:

EXEC ExportToExcel @server = NULL,
                   @uname = 'xxxxxx',
                   @pwd = 'xxxxxxx',
                   @QueryText = 'SELECT * FROM dbXXX..tbXXX',
                   @filename = 'c:\xxxxx.xls'