0

I am trying to create the equivalent of the following:

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

    sql code
');

usingEXEC(see this question here)

With that being said, I am unable to use SELECT * INTO according to multiple sources (and this one)

I have found some other resources where I can create a new table using EXEC, however, I do not know the exact structure of the resulting table (number of columns, column types, column names, etc) so it needs to be dynamic.

The following code results in giving me the exact resulting table that I want, but I have not been able to figure out how to create the purchases table with the resulting data from the below query:

-- 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 have tried using the following: SELECT * INTO [jerry].[dbo].[purchases] FROM OPENROWSET('EXEC (@sqlcode) AT OLAP') but get an error of Incorrect syntax near ')'.

I have also tried (just to see):

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

And get an:

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.

error.

Apologies if this is an easy logic question -- I feel like I am exhausting my researching ability trying to find a solution as I am very new to SQL Server. I am working in SSMS 2017, as well, if that helps.

artemis
  • 6,857
  • 11
  • 46
  • 99
  • Not sure if this helps, but `sp_describe_first_result_set` takes a query(probably an exec... too) as a parameter and returns the result set structure. – George Menoutis May 31 '19 at 13:48
  • Thanks @GeorgeMenoutis. I am not sure if that completely solves this problem, but I appreciate the path to follow. – artemis May 31 '19 at 13:57
  • Two solutions for the "distributed transaction" part of your question are found here: https://stackoverflow.com/questions/6999906/distributed-transaction-error – Ed Harper May 31 '19 at 14:09
  • Unfortunately cannot do either -- not a DBA -- fails ith Error 15247, lack of permissions – artemis May 31 '19 at 14:12

1 Answers1

0

The answer can be found at this post: Create new table with results from EXEC

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

truncate table [jerry].[dbo].[purchases]
insert into [jerry].[dbo].[purchases]
exec ( @sqlcode ) at OLAP
artemis
  • 6,857
  • 11
  • 46
  • 99