Hi folks!

A long simmering problem,eh? You insert a new record via remote view or SPT into a SQL Server table. The primary key is an Identity datatype, a sequential number maintained by SQL Server. You requery the record to get the new key and it comes across as zero. You query "@@INDENTITY" and you still get zero. Major PITA.

Well, thanks to some ideas from a Powerbuilder/Sybase buddy of mine, I have a resolution to the problem:

Open TWO connections to the datasource or DBC connection. Make the first manual transactions via SQLSETPROP. Use SQLEXEC to insert the new record via the first connection. Then do an SQLCOMMIT on the 1st connection. You can immediately do a query via a candidate key for the value of the new primary through the SECOND connection and you will get back the actual new primary key (identity).

This works MUCH faster than any other solution I have found. I will be writing this up for a UT "how-to" article soon but for some of you, just mentioning this method probably gives you enough info to duplicate my findings.