Bypassing character limit on OPENQUERY failing use EXECUTE

Question

I am currently using SQL Server Management Studio 17 to connect to an Oracle database instance and then extract some data and insert it into a SQL Server Table I have.

I have tried doing the following:

DROP TABLE IF EXISTS [jerry].[dbo].[purchases]
SELECT * INTO [jerry].[dbo].[purchases] FROM OPENQUERY(OLAP, '

    proprietary sql code

');

However the SQL code is about 9500 characters and thus OPENQUERY fails, which is supported by MSDN articles

I referenced these sites:

• - MSDN One - MSDN Two

and learned that I can use EXEC to accomplish my goal. I have tried to implement the following:

EXEC master.dbo.sp_serveroption @server=N'OLAP', @optname=N'rpc out', @optvalue=N'true'
DECLARE @sqlcode VARCHAR(MAX)
SET @sqlcode = 'sql code'


DROP TABLE IF EXISTS [jerry].[dbo].[purchases]
EXEC @sqlcode AT OLAP

However, I am still getting an Invalid Syntax near OLAP error. I have confirmed that OLAP is the correct name from our DBA and other OPENQUERY functions work just fine (with much shorter SQL statements).

• I cannot edit the SQL query
• I cannot edit the external OLAP's databases permissions (I am not the DBA nor am I in the security group)

Any assistance is greatly appreciated.

Answer 1

EXEC without parentheses runs a stored procedure.

So try:

truncate table [jerry].[dbo].[purchases]

insert into [jerry].[dbo].[purchases]
exec ( @sqlcode ) at olap

See execute