Identity Value not being returned by SQL Server 2014

Question

We have a PowerBuilder client application (PB 12.5) that has been in production for the last 6 years and has been working fine against SQL MSDE and MS-SQL Server 2012.

Recently, we've upgraded some installations (about 40) to SQL Server 2014. In about half of these installations, we seeing an issue whereby an Identity column is not returned to the DataWindow after the update method is called. Here is the code in question:

If lds_Update.Update( ) < 1 then
   lb_Error = True
   ls_Temp = "The inspection header could not be created."
Else
   ll_InspID = lds_Update.GetItemNumber(1, "Insp_ID")
   If (ll_InspID <= 0) or Isnull(ll_InspID) then 
       lb_Error = True  // This is being hit as ll_InspID is null
       ls_Temp = "Could not retrieve the inspection identity."
   End If
End If

The DataWindow has the correct setting for the Identity column and Primary key.

This is not happening on all installations but only some. All installations are pretty much identical. The PB application is installed on a server and is launched from a thin-client.

The connection string is as follows:

SQLCA.DBParm = "PROVIDER='SQLOLEDB',DATASOURCE='ServerName',PROVIDERSTRING='Database=DBName',Identity='SCOPE_IDENTITY()'"

We have tried against several test installations and SQL Server 2014 Express also but it works fine. It is only on some installations that this fails and we don't know why. We've tried changing to Identity='@@IDENTITY' as well but there is no effect.

Does anyone have any ideas?

Try it out if it helps: [OLE DB] DBParm="PROVIDER='SQLNCLI11',DATASOURCE='SERVER',PROVIDERSTRING='database=DBNAME;APP=APPNAME',OJSyntax='ANSI',PBTrimCharColumns='Yes',DateTimeFormat='\''yyyy-mm-dd hh:mm:ss.fff\''',DateFormat='\''yyyy-mm-dd\''',TimeFormat='\''hh:mm:ss\',Identity='SCOPE_IDENTITY()',DisableBind=0" — Eduardo G.
If you change the datawindow to: "Key Modification = Use Update" does the same thing happen? Can you try — Eduardo G.
This is an happening for an Insert Statement - No update key property does not apply. — navigator

Eduardo G. Eduardo G. · Accepted Answer · 2016-11-15T09:10:15

You can change the query used for retrieving the identity value via the pbodbxxx.ini file (where xxx is the version of PB you are using - 120 in this case for PB12). Despite the naming convention of the file, Sybase has chosen to make an OLE DB connection responsive to some settings within the file. In this particular case, change the following line in the [MS_SQLSERVER_SYNTAX] section

from GetIdentity='Select @@identity'

to GetIdentity='SELECT SCOPE_IDENTITY()'

If you choose this path, you will need to distribute the INI file with your application. Note that this works in PB11 - I assume it still holds true for PB12.

Interesting article: https://blogs.sap.com/2012/10/25/using-autoincrementing-columns-from-a-powerbuilder-datawindow/

Identity Value not being returned by SQL Server 2014

2 Answers