tMSSQL_LastInsertID returning 0 after changing Select @@IDENTITY to SCOOE_IDENTITY

[Anonymous]

Hello,

Im having an issue where tMSSQLLastInsertID object is returning a 0 after changing the code located here:

\Talend\Talend\Studio\plugins\org.talend.designer.components.localprovider_6.5.1.20180116_1512\components\tMSSqlLastInsertId\tMSSqlLastInsertId_begin.javajet

to the following:

<%
log4jCodeGenerateUtil.useExistConnection(node);
log4jCodeGenerateUtil.logInfo(node,"info",cid + " - Query:'SELECT @@IDENTITY'");
%>
java.sql.PreparedStatement pstmt_<%=cid %> = conn_<%=cid %>.prepareStatement("SELECT SCOPE_IDENTITY() as 'last_insert_id'");
int nb_line_<%=cid%> = 0;

I have a trigger on the table and that is why I need to change to Scope_Identity instead of using @@identity.  When I use @@identity it returns the incorect ID.  With the change I made above it returns 0, I have also tried just SCOPE_IDENTITY() without the as and it also returns 0.  I have also tried select SCOPE_IDENTITY() in the line above in blue still returns 0.

The ID field is a bigint that is auto incrementing.

Any help would be greatly appreciated

[Anonymous]

Hello,

Please try to disable "Use Batch Size" in the tMSSqlOutput component that does the insert.

Let us know if it is OK with you.

Best regards

Sabrina

[Anonymous]

I already had that disabled and for future reference or anyone who comes across this post...We opened up the java for the tMSSQLOutput component and found that it closes the connection to the database after the record is inserted and the tMSSQL_LastInsert makes a second call to the database so there is no way it can accurately get the last insert ID for the exact record that was inserted into that table.  

In order to solve this issue we actually make a 2nd call to the database to get the ID of the record inserted using a select statement and in the where clause 2 fields that make the record unique.  

[Anonymous]

Hello,

Thanks for sharing your solution with us. We will appreciate it if you could post your select statement on forum.

Best regards

Sabrina