1

I have a stored procedure that queries a linked DB2 server in this fashion:

SET @sql='SELECT * FROM openquery(DB2,''SELECT column1, column2 FROM table'')'
exec sp_executesql @sql

I am trying to store that result into a new table via another stored procedure

    INSERT INTO [schema].[Table]
               ([column1]
               ,[column2])
    EXEC('schema.StoredProc')

Where StoredProc contains the aforementioned openquery

However, when I execute the second Stored Procedure (INSERT INTO), the output window prints out the text of @sql from my first stored procedure, and gives me the message:

SELECT * FROM openquery(DB2,'SELECT column1, column2 FROM table')
Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command.  The results, if any, should be discarded.

Any help is appreciated

1

I will give you an example that has been working for me, it will give you an idea:

DECLARE @Radhe  TABLE(
    [n] [bigint] NOT NULL,
    [Login_Name] [nvarchar](128) NOT NULL,
    [Login_From] [nvarchar](128) NULL,
    [Account_Type] [nvarchar](128) NULL,
    THE_ORDER INT NOT NULL
    PRIMARY KEY CLUSTERED (Login_Name,N)
)



        SELECT @SQL = 'SELECT 
                        [n],
                        [Login_Name],
                        [Login_From],
                        [Account_Type],
                        the_order       
                      FROM OPENQUERY(' + quotename(@SERVER ) + ',' + '''' + 'SET FMTONLY OFF; exec sp_GetServerLogins ' + '''' + ')'

        INSERT INTO @Radhe
        exec ( @sql)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.