I have the following Recursive CTE which is used in an OpenQuery function in SQL I use OpenQuery to query a SQL Linked Server. The request does not work because the values from rows Line not transfer . How to transfer values from column Line in OpenQuery ?
DECLARE @OpenSelect varchar(MAX)
DECLARE @resTable TABLE(ID VARCHAR(50), OLDVAL VARCHAR(MAX), NEWVAL VARCHAR(MAX), WHONAME VARCHAR(100), TIME DATETIME, FIELD VARCHAR(100))
SET @OpenSelect = ('SELECT
;with cte as (select Line from [dbo].[#LineID])
FROM openquery (DB, ''select ID, OLDVAL, NEWVAL, WHONAME, TIME, FIELD
from TASKYM1
where ID IN ( Line )
'')
')
INSERT INTO @resTable (ID, OLDVAL, NEWVAL, WHONAME, TIME, FIELD) EXECUTE(@OpenSelect) table [dbo]. [# LineID] contains prepared data separated by commas, for example:
Line
1 'ID1111','ID1112','ID1113','ID1114','ID1115','ID1116','ID1117','ID1118'
2 'ID1119','ID1120','ID1121','ID1122','ID1123','ID1124','ID1125','ID1126'
3 'ID1127','ID1128','ID1129','ID1130','ID1131','ID1132','ID1133','ID1134'
4 ....
5 ....