Could EXEC and Select Into work together?

Question

Im running following statement to get a result from remote server

EXEC  ( @var)  AT [linkedservername]

Note : @var holds select query to run on linked server. Linkedserver is DB2 server.

I would like to save the result into a temptable.

Is it possible ?

Can we achieve like below?

EXEC  ( @var)  AT [linkedservername]  into #t

-- Updating to provider more info.

I came up to this,

read query from file.

execute it in desired DB2 server using linked server.

now i need to save it in a temptable

DECLARE @FileContents  VARCHAR(MAX)

SELECT @FileContents=BulkColumn
FROM   OPENROWSET(BULK'E:\ADDRESS.txt',SINGLE_BLOB) x;

set @FileContents = Replace(@FileContents,'''''','''')

set @FileContents = @FileContents + ' FETCH FIRST 1 ROWS only'

EXEC    ( @FileContents)  AT [linkedservername]

I Need something like below,

select * from (
    EXEC    ( @FileContents)  AT [linkedservername]   )

I have seeked help for Select into not insert into. Link is for insert into. Im already using it, for that table structure must be already created, but in my case query is dynamic — vignesh, Sep 20 '18 at 11:48
So now I can't close it again as a dupe, but here's one possible answer: https://stackoverflow.com/a/21141083/3094533 — Zohar Peled, Sep 20 '18 at 13:05

GreyOrGray · Accepted Answer · 2018-09-20T14:25:59.593

Updated for new information.

The answer is no for the way you're attempting to do it. If you know the structure already, create the temp table first. You might have to change your RPC and DTC settings to get the call to work.

CREATE TABLE #temp
(<Table definition for your results>)

DECLARE @var VARCHAR(100)= 'your command'

INSERT INTO #temp EXEC (@var) AT TRANSACCOUNT

SELECT * FROM #temp

If the structure is unknown you, you can use OPENROWSET or OPENQUERY to generate the table on the fly.

Openquery:

SELECT  * 
INTO #temp FROM    
OPENQUERY(targetServer, 'your command')

Openrowset:

SELECT  * INTO
  #temp
FROM
  OPENROWSET(
    'SQLNCLI',
    'Server=targetServer;Trusted_Connection=yes;',
    'your command'
)
SELECT * FROM #temp

OPENQUERY and OPENROWSET require string literals, so if you need to dynamically set your base command, you'd need to also build your OPENROWSET call. The context of the call would also change the context of any temporary table, so you could use a temporary permanent table to store your results like this:

DECLARE @var VARCHAR(100)= 'your command'
DECLARE @command VARCHAR(MAX)

SELECT @command = CONCAT(
'SELECT  * INTO  temporaryTable
FROM
  OPENROWSET(
    ''SQLNCLI'',
    ''Server=targetServer;Trusted_Connection=yes;'',
    ''', @var,'''
)')
EXEC (@command)
SELECT * FROM temporaryTable

Table structure is unknown.. After running the query only I could get it — vignesh, Sep 20 '18 at 14:03
I have came across openrowset but ended up with this error Msg 7399, Level 16, State 1, Line 26 The OLE DB provider "SQLNCLI11" for linked server "(null)" reported an error. Authentication failed. Msg 7303, Level 16, State 1, Line 26 Cannot initialize the data source object of OLE DB provider "SQLNCLI11" for linked server "(null)". — vignesh, Sep 20 '18 at 15:08
Note : im running it in DB2 server may be due to that it may fail. thought of change the sql provider to db2 provider — vignesh, Sep 20 '18 at 15:09
Yeah SQLNCLI11 is for native client for SQL Server. I don't have a DB2 environment to verify, but I believe it looks something like this `OPENROWSET('IBMDADB2' ,'DSN=yourDSN;UID=yourUser;pwd=yoruPass;' ,'your command` — GreyOrGray, Sep 20 '18 at 16:15

Could EXEC and Select Into work together?

1 Answers1