在OPENQUERY无法使用EXECUTE时绕过字符限制 [英] Bypassing character limit on OPENQUERY failing use EXECUTE

问题描述

我当前正在使用SQL Server Management Studio 17连接到Oracle数据库实例，然后提取一些数据并将其插入到我拥有的SQL Server表中.

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.

我尝试执行以下操作:

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

    proprietary sql code

');

但是，SQL代码大约为9500个字符，因此OPENQUERY失败，

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

我引用了这些网站:

• -MSDN一个 并了解到我可以使用EXEC来实现自己的目标. 我试图实现以下目标:

  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
  

  但是，我仍然出现Invalid Syntax near OLAP错误. 我已经确认OLAP是我们DBA的正确名称，其他OPENQUERY函数也可以正常工作(使用更短的SQL语句).

  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).

  • 我无法编辑SQL查询
  • 我无法编辑外部OLAP的数据库权限(我既不是DBA，也不是安全组中的人)

  我们非常感谢您的协助.

  Any assistance is greatly appreciated.

  推荐答案

  不带括号的EXEC运行存储过程.

  EXEC without parentheses runs a stored procedure.

  所以尝试:

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

  请参见执行

  这篇关于在OPENQUERY无法使用EXECUTE时绕过字符限制的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！