Using a Variable in OPENROWSET Query

Question

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?

Seclusive

Answer 1

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

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)

Drysalt

Answer 2

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

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

'query'

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

Sadiron

Thanks Scott, I think the error msg I was getting gave me that idea. – Seclusive

Answer 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)

Angelaangele

Hi, and welcome to StackOverflow. Please do not post only-code answer, but include an explanation. Especially for a question like this, where the question is "why is this not working?" and not "what else would work?" – Hereof

While generally I agree that posts should have some explanation, there wasn't a lot of explaining needed as the question was answered in Dec 2012. I found this response useful in that it shows the arguments values for connecting to an Excel file (the database argument) and sheet$ as an alternative to a SQL Server database and table. – Donte

Answer 4

If you need parameters you can also use 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

Onus

Answer 5

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)

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.

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

Just an option....

Cloe

this answer might be better if it explained how it solves the problem. – Veator

Answer 6

You can use Char(39) instead of '

set @lcSql= 'SELECT production_pbook_uq,variety_uq,color_uq,grade_uq,diff_qty
            FROM OPENROWSET("SQLNCLI",
            "Server=.;Trusted_Connection=yes;",
            "EXEC serversql.dbo.sp_flower_production_varieties_consolidated @lcproduction_pbook_uq='+char(39)+@lcWo_uq+char(39)+', @lcproduct_piso_uq ='+char(39)+@lcpiso_uq+char(39)+'")'
            +'where diff_qty>0'

print @lcSql 

Condorcet