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:
We need to convert all the command line into string and use sp_executesql to run it:
Work:
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