Returning @@IDENTITY from an OPENQUERY INSERT Statement

Question

Post reply

Returning @@IDENTITY from an OPENQUERY INSERT Statement

RichardB-309825

SSC Enthusiast

Points: 191
More actions
March 17, 2006 at 9:04 am

#171700

Hi, I'd like to know if it's possible to get the equivalent of and output parameter back from an SQL stored procedure which uses OPENQUERY to insert a record to an Oracle linked server.
e.g.
INSERT OPENQUERY (ORACLE,
                             'SELECT
                             ColumnID,
                             Value  RECORDCD,
                             FROM Table WHERE 1=2')
VALUES (1, @Value);
ColumnID is the identity and I'd like to get this value back from the insert if possible (it appears I need to set it to 1 in the insert statement to allow this to work, but it does insert with a new value ok!).
I'm not sure if this is possible though - can anyone help?
Thanks
March 20, 2006 at 8:00 am

This was removed by the editor as SPAM

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

dotnet85 SSC Rookie Points: 49 More actions · Answer 1

any update on the topic? I am facing the same issue.
Thank you

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 2

dotnet85 - Tuesday, May 22, 2018 7:51 PM
any update on the topic? I am facing the same issue.
Thank you

I don't know what ORACLE uses as a "last row marker inserted" but it's not likely that OPENQUERY can provide such a return.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 3

Jeff Moden - Tuesday, May 22, 2018 9:30 PM
dotnet85 - Tuesday, May 22, 2018 7:51 PM
any update on the topic? I am facing the same issue.
Thank you
I don't know what ORACLE uses as a "last row marker inserted" but it's not likely that OPENQUERY can provide such a return.

Besides the fact that the insert happened on the remote server, so the local server's @@identity would not be getting updated as a result.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

dotnet85 SSC Rookie Points: 49 More actions · Answer 4

Thank you for your both replies and clarifications.

But why SQL Server looks like parsing the query provided inside OPENQUERY, though the statement is meant to be executed on the remote server (i.e., Oracle)?

I have tried different ways, but still getting errors. Here they are:

using Sequence CURRVAL

DECLARE @SOMEID BIGINT;DECLARE @QUERY NVARCHAR(MAX) = N'INSERT OPENQUERY(ORACLE_SERVER2, ''SELECT CODE, DESCS, CREATED_BY FROM STORAGE_TYPE'')VALUES(''1234'', ''1234DESC'', ''TESTTEST''); SELECT STORAGE_TYPE_SEQ.CURRVAL INTO @SOMEID FROM DUAL;';EXEC SP_EXECUTESQL @QUERY, N'@SOMEID BIGINT', @SOMEID OUTPUT;SELECT @SOMEID;

Msg 102, Level 15, State 1, Line 90 Incorrect syntax near '@SOMEID'.
(1 row(s) affected)

using RETURNING statement

DECLARE @SOMEID BIGINT;DECLARE @QUERY NVARCHAR(MAX) =  N'INSERT OPENQUERY(ORACLE_SERVER2, ''SELECT CODE, DESCS, CREATED_BY FROM    STORAGE_TYPE'')VALUES(''1234'', ''1234DESC'', ''TESTTEST'') RETURNING ID INTO @SOMEID';EXEC SP_EXECUTESQL @QUERY, N'@SOMEID BIGINT', @SOMEID OUTPUT;SELECT @SOMEID;

Msg 102, Level 15, State 1, Line 90 Incorrect syntax near 'RETURNING'.
(1 row(s) affected)

Thank you