使用 EXEC 的结果创建新表_数据库问题-得得之家

本文介绍了使用 EXEC 的结果创建新表的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我正在尝试获取 EXEC 查询的结果并将它们放入我的 SQL Server 2017 数据库中的表中.

I am attempting to take the results of an EXEC query and put them into a table in my SQL Server 2017 database.

我知道之前有人问过类似的问题，比如这里、此处和此处，但我还没有找到有效的解决方案.

I'm aware that similar questions have been asked before like here, here, and here, but I have not found a solution that works.

EXEC 查询本身运行良好，结果正是我正在寻找的数据:

The EXEC query runs great on its own, and results in exactly the data I am looking for:

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

由于 SQL 超过 8000 个字符的限制，我无法使用 OPENQUERY.

I cannot use OPENQUERY due to the SQL exceeding the 8000 character limit.

尝试 1:

DECLARE @sqlcode VARCHAR(MAX)
SET @sqlcode = 'sqlcode'

DROP TABLE IF EXISTS [jerry].[dbo].[purchases]
truncate table [jerry].[dbo].[purchases]
insert into [jerry].[dbo].[purchases]
exec ( @sqlcode ) at OLAP

但是得到:

无法找到购买"的对象，因为它不存在或者您存在没有权限.

Cannot find the object "purchases" because it does not exist or you do not have permissions.

尝试 2:

SELECT * INTO [jerry].[dbo].[purchases] FROM OPENROWSET('EXEC (@sqlcode) AT OLAP')

但是得到一个错误

')'` 附近的语法不正确.

Incorrect syntax near ')'`.

尝试 3

CREATE TABLE [jerry].[dbo].[purchases] ([Transaction_Date] DATE,    [Requirement_Date] DATE, [Element] NVARCHAR(256), [Trx_Quantity]    NVARCHAR(256), [Part_Number] NVARCHAR(256), [NHA_Part_Number]    NVARCHAR(256), [Group] NVARCHAR(256), [Details] NVARCHAR(256));    INSERT INTO [jerry].[dbo].[purchases]    EXEC (@sqlcode) AT OLAP

但得到一个错误:

返回链接服务器OLAP"的 OLE DB 提供程序OraOLEDB.Oracle"消息无法登记交易.".消息 7391，级别 16，状态 2，第 208 行无法执行操作，因为 OLE DB链接服务器OLAP"的提供程序OraOLEDB.Oracle"无法开始一个分布式事务.

OLE DB provider "OraOLEDB.Oracle" for linked server "OLAP" returned message "Unable to enlist in the transaction.". Msg 7391, Level 16, State 2, Line 208 The operation could not be performed because OLE DB provider "OraOLEDB.Oracle" for linked server "OLAP" was unable to begin a distributed transaction.

尝试 4

DECLARE @sqlcode VARCHAR(MAX)
SET @sqlcode = 'sql'

DROP TABLE IF EXISTS [jerry].[dbo].[report]
CREATE TABLE [jerry].[dbo].[report] ([Transaction_Date] DATE, [Requirement_Date] DATE, [Element] NVARCHAR(256), [Trx_Quantity] NVARCHAR(256), [Part_Number] NVARCHAR(256), [NHA_Part_Number] NVARCHAR(256), [Group] NVARCHAR(256), [Details] NVARCHAR(256));
insert into [jerry].[dbo].[report]
exec ( @sqlcode ) at OLAP

但我得到一个错误:

消息 7391，级别 16，状态 2，第 209 行操作无法进行由于链接服务器的 OLE DB 提供程序OraOLEDB.Oracle"而执行OLAP"无法开始分布式事务.而且，当我尝试根据这篇文章更改启用分布式事务的推广设置为 False"时, 我收到另一个权限错误.

Msg 7391, Level 16, State 2, Line 209 The operation could not be performed because OLE DB provider "OraOLEDB.Oracle" for linked server "OLAP" was unable to begin a distributed transaction. And, when I try to change the "Enable Promotion of Distributed Transactions being set to False" per this post, I get another permission's error.

简而言之，我只需要输入到 SQL Server 数据库中的第一个 EXEC 查询的结果.EXEC 查询确实命中了我只有读取权限且无法更改任何安全设置的外部 Oracle 数据库.

In short, I just need the results of the first EXEC query entered in to a SQL Server database. The EXEC query does hit an external Oracle database to which I have ONLY read permissions and cannot change any security settings.

感谢任何帮助.

使用 EXEC 的结果创建新表

问题描述

推荐答案

相关文档推荐