ITブログ時々なんでもブログ

--SQL Server によって、コンポーネント 'Ad Hoc Distributed Queries' の 
--STATEMENT 'OpenRowset/OpenDatasource' に対するアクセスがブロックされました。
--このサーバーのセキュリティ構成で、このコンポーネントが OFF に設定されているためです。
--システム管理者は sp_configure を使用して、'Ad Hoc Distributed Queries' の使用を有効にできます。
--'Ad Hoc Distributed Queries' を有効にする手順の詳細については、SQL Server オンライン ブックで、
--'Ad Hoc Distributed Queries' を検索してください。
USE master
EXEC sp_configure 'show advanced options', 1;
EXEC sp_configure 'Ad Hoc Distributed Queries', 1; 
EXEC sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'AllowInProcess', 1;
EXEC sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'DynamicParameters', 1;
RECONFIGURE;
GO

USE testDB
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
DROP PROCEDURE IF EXISTS dbo.usp_ImportExcel;
GO
CREATE PROCEDURE dbo.usp_ImportExcel
     @TableName NVARCHAR(255)   --テーブル名:'WK_IMPORT_XLS'
    ,@FilePath  NVARCHAR(255)   --エクセルファイルフルパス:'C:\excelworkbook.xlsx'
    ,@SheetName NVARCHAR(255)   --エクセルシート名:'Sheet1'
    ,@Range     NVARCHAR(5)     --取込範囲:'A2:Z'
    ,@Where     NVARCHAR(1000)  --条件指定:'OrderCode IS NOT NULL OR CustomerName IS NOT NULL '
    ,@RetCode   INT             OUTPUT 
    ,@RetMsg    VARCHAR(MAX)    OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    
    DECLARE @SQL NVARCHAR(1000);
    SET @SQL = '';
    
    BEGIN TRY

        --既存ワークテーブルの削除
        SET @SQL = 'DROP TABLE IF EXISTS ' + @TableName + ';';
        EXECUTE sp_executesql @SQL;

        --OPENROWSET
        --https://docs.microsoft.com/ja-jp/sql/t-sql/functions/openrowset-transact-sql
        
        SET @SQL = ''
        + ' SELECT * '
        + ' INTO  ' + @TableName
        + ' FROM   OPENROWSET ( '
        + '      ''Microsoft.ACE.OLEDB.12.0''    '
        + '     ,''Excel 12.0;Database=' + @FilePath + ''' '
        + '     ,''SELECT * '
        + '        FROM   [' + @SheetName + '$' + @Range + ']'
        ;
            IF ISNULL(@Where, '') <> ''
                BEGIN
                    SET @SQL += ' WHERE ' + @Where ;
                END;
        SET @SQL += ' '' ); ';
                
        --OLE DB プロバイダー "Microsoft.ACE.OLEDB.12.0" が登録されていません。
        --AccessDatabaseEngine.exe 32bit/64bit
        --https://www.microsoft.com/ja-jp/download/details.aspx?id=13255

        EXECUTE sp_executesql @SQL;

    END TRY
    BEGIN CATCH
        SET @RetCode = ERROR_NUMBER();
        SET @RetMsg  = ERROR_MESSAGE();
    END CATCH;
END
GO

USE [testDB]
GO
DECLARE
        @return_value  int
       ,@RetCode       int
       ,@RetMsg        varchar(max)

EXEC    @return_value  = [dbo].[usp_ImportExcel]
        @TableName     = N'WK_IMPORT_XLS',
        @FilePath      = N'D:\エクセル取込サンプルデータ.xls',
        @SheetName     = N'取込シート',
        @Range         = N'A2:G',
        @Where         = N'OrderCode IS NOT NULL OR CustomerCode IS NOT NULL',
        @RetCode       = @RetCode OUTPUT,
        @RetMsg        = @RetMsg  OUTPUT

SELECT
        @RetCode as N'@RetCode',
        @RetMsg  as N'@RetMsg'

SELECT  'Return Value' = @return_value
GO