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?
Add a comment
|
6 Answers
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)
Comments
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...
1 Comment
Control Freak
Thanks Scott, I think the error msg I was getting gave me that idea.
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)
2 Comments
dwitvliet
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?"
RyanB
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.
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
Comments
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....
1 Comment
FistOfFury
this answer might be better if it explained how it solves the problem.
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
