... set @sql = 'SELECT * FROM OpenRowset(''SQLOLEDB'', ''z'';''z'';''zz'', + REPLACE(@Query,'''', '''''') + ''')' --select @sql exec sp_executesql @sqlI got it work on Pubs..Sales table. Here's my test code for reference.
declare @Query nvarchar(400) declare @SQL nvarchar(400) set @Query = 'SELECT * FROM Pubs..Stores where state = ''CA''' set @sql = 'SELECT * FROM OpenRowset(''SQLOLEDB.1'', ''zzz''; ''zz''; ''z'', ''' + REPLACE(@Query,'''', '''''') + ''')' exec sp_executesql @sql>Here is what I got now:
declare @datehb datetime >declare @Query varchar(200) >declare @SQL nvarchar(200) >declare @ParmDefinition nvarchar(200) > >set @datehb = (select max(date) from histobarraCNE3) >set @ParmDefinition = '@QueryParm varchar(200)' >set @Query = 'SELECT * FROM RQuant.dbo.histobarrause3 where date> ''' + cast(datepart(yy,@datehb) as varchar(4)) + '-' + cast(datepart(mm,@datehb) as varchar(2)) + '-' + cast(datepart(dd,@datehb) AS varchar(2)) + '''' >--select @query > >set @sql = 'SELECT * FROM OpenRowset(''SQLOLEDB'', ''z'';''z'';''zz'', @QueryParam) ' >--select @sql > >exec sp_executesql @sql, @ParmDefinition , @QueryParam = @Query>