This type of 'Insert Into' has been working fine for me.
But, I have run into a situation where @sql is larger than 8000 characters.
I thought that if I declare @sql as varchar(nomax) I should get over 2 gb of space.
It seems to use 8000 not the larger 2 gb for MAX.
DECLARE @sql VARCHAR(max)
SET @sql = N'INSERT INTO [#IssuesDetails]
SELECT * from openquery
+ Replace(@sql, '''', '''''') + ''')'
I attached the error.
I can do a work around but wondering why this does not work.
It seems to ignore the NOMAX.
When I run the SSRS report with less parameter choices everything works fine.
This 'Insert Into' is reading records from Oracle tables and inserting them into a SQL table called #IssuesDetails NomaxError.PNG