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.

Not sure but this link has great deal of information about the linked server which you are using, and if you could check it. https://www.sqlshack.com/link-sql-server-oracle-database/ — Avi, May 30 '19 at 21:05

David Browne - Microsoft · Accepted Answer · 2019-05-31T14:28:58.453

3

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

edited May 31 '19 at 14:28

answered May 30 '19 at 21:05

David Browne - Microsoft

80,331
6
39
67

Looks like the user does not have permissions to modify the linked server. Typically a sysadmin would run that command. – David Browne - Microsoft May 30 '19 at 21:14
how can I update my table using `EXEC` here, can you edit your answer to show that so I can accept? – artemis May 30 '19 at 21:55
how can I update my table using `EXEC` here, can you edit your answer to show that so I can accept? Currently, this shows me the correct rows but does not create the `[jerry].[dbo].[purchases]` table – artemis May 30 '19 at 22:01
Instead of SELECT .. INTO you just truncate the table and run INSERT ... EXEC – David Browne - Microsoft May 31 '19 at 14:29
Thanks a lot for your suggestion @DavidBrowne. Oddly, it tells me I cannot find it or I don't have permissions (which is impossible since I am the one who set it up). But I will keep researching. Sigh... Thank you!! – artemis May 31 '19 at 14:32
If you dropped the table, you'll have to recreate it. – David Browne - Microsoft May 31 '19 at 16:11

Bypassing character limit on OPENQUERY failing use EXECUTE

1 Answers1

Linked