SQLServer の OPENDATASOURCE 関数による Excel の参照( Microsoft.Jet.OLEDB.4.0 と Microsoft.ACE.OLEDB.12.0 )

OPENDATASOURCE 関数を使用可能にするには、まず show advanced options を 1 にして、詳細オプションを可能に設定して反映するために、RECONFIGURE を実行します。その後、Ad Hoc Distributed Queries を 1 に設定してから RECONFIGURE を実行する必要があります
さらに、Excel 2007 以降の(拡張子 .xlsx)で、 Microsoft.ACE.OLEDB.12.0 プロバイダを利用する場合は、以下のコマンドを実行する必要があります

EXEC sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1
GO
EXEC sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1
GO


Microsoft ドキュメント
※ sp_configure

Microsoft のサンプルのように実行するとエラーになります。設定単位で GO しないとエラーになります。

一度設定すると設定は保持されます。SQL の実行前に毎回行う必要はありません

基本設定

sp_configure 'show advanced options', 1
RECONFIGURE
GO
sp_configure 'Ad Hoc Distributed Queries', 1
RECONFIGURE
GO

Microsoft.ACE.OLEDB.12.0用

EXEC sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1
GO
EXEC sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1
GO

それぞれのプロバイダでの実行

select * from
OPENDATASOURCE(
	'Microsoft.Jet.OLEDB.4.0',
	'Data Source="C:\user\data\商品.xls";Extended properties=Excel 8.0'
)...[Sheet1$]

select * from
OPENDATASOURCE(
	'Microsoft.ACE.OLEDB.12.0',
	'Data Source="C:\user\data\商品.xlsx";Extended properties=Excel 12.0'
)...[Sheet1$]

▼ ローカルの SQLServer(SQLExpress)へコマンドプロンプトから接続する一例
sqlcmd -U sa -P password -S .\SQLEXPRESS

sqlcmd を使った資料
SQLExpress 2005( および 2008 ) の接続設定

参考記事

Excel Import to SQL Server using Distributed Queries
( Microsoft.ACE.OLEDB.12.0 を使えるようにする為の処理が記述されていました )

関連する記事

VBScript : ADO で Excel2007(Excel 2010、Excel 2013) にアクセスする( シートをテーブルとして読み込む )
( VBScript から直接 Excel を DB として読む為の接続文字列と処理です )

SQLExpress

SQLServer の BCP で INT IDENTITY 列に csv からリストアする

VBScript : ADO : ODBC接続 : SQLExpress(SQLServer) 接続と通常処理( 更新は SQL で行う )

Microsoft SQLExpress : sa に空のパスワードを設定する方法

SQLExpress バッチ DB バックアップ

C# : VB.net : SQLExpress(SQLServer) : SQL-DMO と同等の SMO によるバックアップ

各RDBMS の正規表現の実装状況 : MySQL, SQLServer, PostgreSQL, Oracle

SQLServer(SQLExpress) : SQL学習用販売管理データとインポート用スクリプト

SQLExpress 2005( および 2008 ) の接続設定

SQLExpress 内に存在する表の CREATE 文を取得する

ファイルから直接データベースを別のPCでリストアする / SQLExpress

目的のDBが使用している物理ファイルのパスの確認 / SQLExpress

ピンポイントですが、SQLServer 2008 は SQLServer 7.0 のデータをロード出来ないのです

SQLExpress2005 で副問い合わせを含む VIEW が異常終了しました

VBS : SQLServerよりExcel へエクスポート(Excel製品は必要ありません)

SQLServer のドキュメント

VBS : SQLExpress(SQLServer) テーブルの正しい CREATE 文を取得する

PHP : SQLExpress(SQLserver) : COM による接続処理

PHP : PDO : SQLExpress(SQLServer)の読み込みと同時更新

ODBC 用 JDBC : 動的接続 / SQLExpress(SQLServer) / 読み込みながらの行更新

VBScript : ADO : 純正接続 : SQLExpress(SQLServer) 接続と通常処理