Create new table with results from EXEC

Question

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.

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

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

Attempt 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

But get:

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

Attempt 2:

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

but get an error of

Incorrect syntax near ')'`.

Attempt 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

but get an error of:

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.

Attempt 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

But I get an error of:

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.

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.

Any assistance is appreciated.

`INSERT INTO... EXEC....` is the correct syntax ([db<>fiddle](https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=d6d3a870985b4aa900d4c44b4382bc23)), which suggests you don't have access to insert into `[jerry].[dbo].[purchases]`, or it doesn't exist (as the error suggests) — Thom A, May 31 '19 at 15:19
Can you post the EXEC? I'm no genius, but isn't EXEC used for Stored Procedures and not Views? — Johnny Bones, May 31 '19 at 15:20
`DROP TABLE IF EXISTS [jerry].[dbo].[purchases]` followed by `truncate table [jerry].[dbo].[purchases]`. How you you `TRUNCATE` a table you just dropped or `INSERT INTO` it? For a `TRUNCATE` or `INSERT INTO` statement to work the table needs to exist... — Thom A, May 31 '19 at 15:20
I cannot post the `EXEC` @JohnnyBones. Unfortunately it is proprietary company owned SQL. I can confirm the contents of the `EXEC` query execute just fine, and even when I use Oracle's SQL Developer to run just that SQL code against the Oracle instance that too works great. — artemis, May 31 '19 at 15:21
@Larnu I am not sure. I am very new to SQL Server, so this is has been a tidal wave in the last 24 hours. I'm sure I'm making an error, but I don't understand how. Judging from other posts on SO, it seems other users have shared in my sadness. — artemis, May 31 '19 at 15:22
The error is telling you the problem, you can't do anything to a table without it existing. Remove the `DROP TABLE IF EXISTS [jerry].[dbo].[purchases]` and this will work. What you're doing it like deleting a file, and then trying to edit it. You can't edit the file anymore, it's been deleted. — Thom A, May 31 '19 at 15:23
I'm pretty sure the error is on the `TRUNCATE` statement in the one with the correct syntax. HAd the OP looked at the line number the error occurred in (which isn't in their post), I think they would have noticed that, @SeanLange :) — Thom A, May 31 '19 at 15:27
I will try your suggestions and see what I can find. I have tried to research every error I have come across, as evidenced by the multiple tags to other posts and attempts. — artemis, May 31 '19 at 15:29
I'm not mocking you at all, @JerryM. nor am i assuming. I'm very confident the reason for the error is because you `DROP` the table first. We can, however, test this [here](https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=bb9e9e8b2c6e1ebd03194b15c680488f). I've excluded the `INSERT...EXEC` statement, however, note you get the same error you had (proving it isn't the `INSERT` that was the problem). If we, however, omit the `DROP TABLE IF EXISTS`, statement, everything works as planned: [db<>fiddle](https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=2d87eab5a5fb267841205cd3b5fe8cd2) — Thom A, May 31 '19 at 15:33
Thank you for the clarification! I am not sure if you feel comfortable to post as an answer @Larnu. I have tried your suggestion, but I get the same error as attempt 4, regarding an an inability to begin a distributed type transaction — artemis, May 31 '19 at 15:36
We're onto a different error the, in truth. The problem i was pointing out was more of a typographical error; so I'm fine with leaving it in the comments. You might, in truth, be better off focusing on the first (corrected) query and asking about that again and deleting this one; as the initial error has clouded the error. — Thom A, May 31 '19 at 15:39
I think now you are hitting problem described in https://stackoverflow.com/questions/4360467/insert-into-table-exec-at-linked-server-does-not-work. Set option 'remote proc transaction promotion' to 'false'. sysadmin will have to change it. — Piotr Palka, May 31 '19 at 15:40
Hi @Piotr if you look at the bottom remark, I have followed those suggestions but am unable to change any settings as I am low-level permissions user. Thank you for the suggestion. — artemis, May 31 '19 at 15:41
Other option is to enable distributed transactions, which is more complicated and requires configuring MSDTC service. I would advice to ask your admin to change transaction promotion. Other question is if you can use SSIS or other tools to do this transfer? — Piotr Palka, May 31 '19 at 15:44

artemis · Accepted Answer · 2019-06-10T19:39:21.853

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.

Create new table with results from EXEC

1 Answers1

Linked