在OPENROWSET查询中使用一个变量

I'm having trouble with this query:

我在这个问题上遇到了麻烦:

SELECT * 
FROM OPENROWSET(
    'SQLNCLI',
    'DRIVER={SQL Server};',
    'EXEC dbo.sProc1 @ID = ' + @id 
 )

Gives an error:

给出了一个错误:

Incorrect syntax near '+'.

不正确的语法“+”附近。

Anyone know why I'm getting this error?

有人知道我为什么会犯这个错误吗?

5 个解决方案

#1

As suggested by Scott , you cannot use expressions in OPENROWSET.Try creating a dynamic sql to pass the parameters

正如Scott所建议的，您不能在OPENROWSET中使用表达式。尝试创建一个动态sql来传递参数

Declare @ID int
Declare @sql nvarchar(max)
Set @ID=1
Set @sql='SELECT * 
FROM OPENROWSET(
               ''SQLNCLI'',
               ''DRIVER={SQL Server};'',
               ''EXEC dbo.usp_SO @ID =' + convert(varchar(10),@ID) + ''')'

-- Print @sql
 Exec(@sql)

#2

OPENROWSET requires string literals, not expressions. It's complaining about the plus sign, becaue it doesn't expect anything more than a string literal and you follewed the string literal with an operator.

OPENROWSET需要字符串文字，而不是表达式。它抱怨的是加号，因为它不期望任何东西超过字符串字面量，你可以用一个运算符把字符串字面意思表示出来。

See http://msdn.microsoft.com/en-us/library/ms190312.aspx which states:

见http://msdn.microsoft.com/en-us/library/ms190312.aspx状态:

'query'

“查询”

Is a string constant sent to and executed by the provider...

是由提供者发送和执行的字符串常量……

#3

Declare @Route VARCHAR(200)
Declare @sql nvarchar(max)
Set @Route='C:\OCRevisiones.xlsx;'
Set @sql='SELECT * INTO FFFF
FROM OPENROWSET(
               ''Microsoft.ACE.OLEDB.12.0'',
               ''Excel 12.0;HDR=YES;Database=' + @Route + ''',
               ''SELECT * FROM [Sheet1$]'')'

 Print @sql
 --Exec(@sql)

#4

For what it is worth.. The reason we use openrowset rather than a straight linked server query is that the processing for a linked server query happens on the local server. (Slow and often brings most of the table back)

为了它的价值。我们使用openrowset而不是直接链接服务器查询的原因是，对链接服务器查询的处理发生在本地服务器上。(慢慢地，通常会把桌子的大部分都拿回来)

Yes we can do the string concatination as above.

是的，我们可以像上面那样进行字符串合并。

A different option where you have ease of syntax and the power of parameters.

这是一个不同的选项，您可以轻松地使用语法和参数。

Create a stored proc on the remote box, that proc has all the parameters you need. Call the stored proc from with a standard linked server query (same perf or better than the above soultion and significantly easier to code with.

在远程框上创建一个存储的proc，该proc具有所需的所有参数。使用标准的链接服务器查询调用存储的proc(相同的perf或更好的，并且更容易编码)。

e.g. linkedservername.database.dbo.myproc 123,'abc','someparam',getdate()

例如linkedservername.database.dbo。myproc 123,“abc”,“someparam”,获取当前日期()

Just an option....

只是一个选择....

#5

If you need parameters you can also use sp_executesql:

如果需要参数，也可以使用sp_executesql:

BEGIN

DECLARE
@p_path varchar(200)='D:\Sample\test.xml',  
@v_xmlfile xml,
@v_sql nvarchar(1000)

SET @v_sql=N'select @v_xmlfile= CONVERT(XML, BulkColumn) FROM 
OPENROWSET(BULK '''+@p_path+''', SINGLE_BLOB) AS x;'

EXEC sp_executesql @v_sql,N'@v_xmlfile xml output',@v_xmlfile output;

SELECT @v_xmlfile

END

#1