MS SQL - Dynamic SQL for OPENQUERY()

May be it is a bug, SQL Server 2000 does not allow the use of a string variable as an input for SQL statement on OPENQUERY():

Not work:

declare @sql  nvarchar(1000)
set @sql = N'SELECT * FROM hvdn WHERE row_date = ''' + convert(varchar(8),getdate(),1) + ''''
print @sql
select * from OPENQUERY ( CMSSERVER,   @sql )
order by row_date, starttime



We need to convert all the command line into string and use sp_executesql to run it:
Work:

declare @sql  nvarchar(1000)
set @sql = N'SELECT * FROM hsplit WHERE row_date = TODAY'
set @sql1 = 'select * from OPENQUERY ( CMSSERVER  ''' + @sql + ''') order by row_date, starttime'
print @sql1
exec sp_executesql @sql1




Comments