使用Microsoft.ACE.OLEDB.12.0搭配变量导入Excel触发语法错误

将Excel文件数据导入目标数据表时出现异常：脚本不使用变量可正常运行，提前声明变量传参执行就会触发错误。

测试脚本：

Declare @DateUsed NVARCHAR(30), @StringDatabase NVARCHAR(30);
SET @DateUsed = '2022-06-20.xlsx';
SET @StringDatabase = 'Database=C:\PATH -'

INSERT INTO Viator.dbo.Test SELECT *  
from  OPENROWSET(
'Microsoft.ACE.OLEDB.12.0',
'Excel 12.0;'
 @StringDatabase + @DateUsed,
'SELECT * FROM [Table1$]'
);

把脚本里的@StringDatabase和@DateUsed直接替换为固定值时，查询可以成功执行。

报错信息 #

Msg 102, Level 15, State 1, Line 10
Incorrect syntax near '@StringDatabase'.

报错原因 #

OPENROWSET的连接字符串入参位置不支持直接传入变量或变量拼接表达式，要求该位置必须传入字符串字面量，直接写变量拼接会触发语法解析错误。

修复方案 #

通过动态SQL拼接完整的合法查询语句，再调用系统存储过程执行，参考代码：

Declare @DateUsed NVARCHAR(30), @StringDatabase NVARCHAR(30), @ExecSql NVARCHAR(MAX);
SET @DateUsed = '2022-06-20.xlsx';
SET @StringDatabase = 'Database=C:\PATH -'

-- 拼接完整SQL，字符串内部的单引号需要用两个单引号转义
SET @ExecSql = '
INSERT INTO Viator.dbo.Test SELECT *  
from  OPENROWSET(
''Microsoft.ACE.OLEDB.12.0'',
''Excel 12.0;' + @StringDatabase + @DateUsed + ''',
''SELECT * FROM [Table1$]''
);'

-- 调试时可以先打开下面的注释，打印拼接后的语句和硬编码的正确语句对比
-- PRINT @ExecSql
EXEC sp_executesql @ExecSql;

内容的提问来源于stack exchange，提问作者Wojciech