同事问起,想给OPENROWSET带入参数。脑子里第一时间想到的,自然是定义一个变量就可以了。哪知道,一用才知道根本不行。查了帮助,才知道:
OPENROWSET 不接受参数变量。
脑子里马上想到了sp_executesql这个老大,一试验,还真成了。尽管如此,还是郁闷,不知道为什么MS要这么做。
示例1:
DECLARE @DSN nvarchar(1000)
DECLARE @SQL nvarchar(4000)
DECLARE @SQL nvarchar(4000)
SET @DSN=
N'DRIVER=Microsoft Access Driver (*.mdb);
UID=admin;
UserCommitSync=Yes;
Threads=3;
SafeTransactions=0;
PageTimeout=5;
MaxScanRows=8;
MaxBufferSize=2048;
FIL=MS Access;
DriverId=281;
DefaultDir=[YOUR DIRECTORY];
DBQ=[FULL PATH OF MDB]'
N'DRIVER=Microsoft Access Driver (*.mdb);
UID=admin;
UserCommitSync=Yes;
Threads=3;
SafeTransactions=0;
PageTimeout=5;
MaxScanRows=8;
MaxBufferSize=2048;
FIL=MS Access;
DriverId=281;
DefaultDir=[YOUR DIRECTORY];
DBQ=[FULL PATH OF MDB]'
SET @SQL=N'
SELECT
*
FROM OPENROWSET(''msdasql'', '''+@DSN+''', ''SELECT * FROM [AccessTable]'')
AS [SomeAlias]'
SELECT
*
FROM OPENROWSET(''msdasql'', '''+@DSN+''', ''SELECT * FROM [AccessTable]'')
AS [SomeAlias]'
sp_executesql @sql
对于访问Access,可以直接使用OLEDB。
示例2:
SELECT * FROM
OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'd:/northwind.mdb',
Orders) t
OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'd:/northwind.mdb',
Orders) t