使用 EXEC 的结果创建新表 [英] Create new table with results from 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.
感谢任何帮助.
推荐答案
根据评论，尤其是来自 @Larnu 的评论
Per the comments, particularly from @Larnu
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
几乎正确，但需要切换到
Was nearly correct, but needed to be switched to
DECLARE @sqlcode VARCHAR(MAX)
SET @sqlcode = 'sqlcode'
truncate table [jerry].[dbo].[purchases]
insert into [jerry].[dbo].[purchases]
exec ( @sqlcode ) at OLAP
因为我无法删除一个表，然后在不重新创建它的情况下对其执行任何操作.
Since I could not drop a table and then perform any operation on it without recreating it.
这篇关于使用 EXEC 的结果创建新表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
