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