在openrowset中使用动态SQL会产生错误 [英] Using dynamic sql in openrowset produces error

问题描述

我需要创建一个存储过程，该存储过程将路径作为参数并通过OPENROWSET命令从文件插入表中.

I need to create a stored procedure that gets a path as a parameter and inserts from file into table via OPENROWSET command.

经过大量搜索和尝试，我了解到OPENROWSET并没有 支持参数，因此需要使用动态SQL进行调用.

After lots of searching and trying, I learned that OPENROWSET does not support parameters and thus needs to be called with dynamic SQL.

那是行不通的部分，它向我显示了一个奇怪的错误.

That is the part that doesn't work, it shows me a strange error.

这可能是由于OPENROWSET不接受字符串参数引起的 但是-我看到了许多类似构建的代码段，并且用户说它们可以正常工作.

It could be caused by the OPENROWSET not accepting the string parameter but - I saw many code snippets that are built similarly and users say they work.

请帮助我了解我在这里缺少的内容以及如何进行这项工作?

Please help me understand what I'm missing here and how do I make this work?

这是我的代码:

Declare @string varchar(MAX) = 'C:\Users\akoga_000\Desktop\test1.xlsx'
DECLARE @sqlString AS varchar(MAX)=

'insert into gameIt_DBSummer.dbo.tblUser 
select * from openrowset(
''Microsoft.ACE.OLEDB.12.0'',
 ''EXCEL 12.0;DataBase=''
 '+cast(@string as varchar(max))+'
 '';Extended Properties="EXCEL 12.0 Xml;HDR=YES'',
 ''SELECT * FROM [Sheet1$]''
)';

EXEC (@sqlString)

///我还尝试了EXEC sp_executesql和nvarchar变量以及其他选项

//I tried also with EXEC sp_executesql and a nvarchar variable among other options

这是错误:

第15层状态1线5消息102
'C:'附近的语法不正确.

Msg 102, Level 15, State 1, Line 5
Incorrect syntax near 'C:'.

推荐答案

我认为您会遇到此错误，因为您需要在路径(@string变量)周围加倍多余的''字符.试试这个:

I think you are getting that error because you need double extra '' character surrounding the path (@string variable). Try this:

Declare @string varchar(MAX) = 'C:\Users\akoga_000\Desktop\test1.xlsx'
DECLARE @sqlString AS varchar(MAX)=
'insert into gameIt_DBSummer.dbo.tblUser 
select * from openrowset(
''Microsoft.ACE.OLEDB.12.0'',
 ''EXCEL 12.0;DataBase=''''
 '+@string+'
 '''';Extended Properties="EXCEL 12.0 Xml;HDR=YES'',
 ''SELECT * FROM [Sheet1$]''
)';
select @sqlString

这篇关于在openrowset中使用动态SQL会产生错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！