SQL server IDENTITY fields

Gianco70 · Post by **Gianco70** » Tue 06 Jan 2009 07:59

Hello

I would know if is possible to obtain the value of an IDENTITY field assigned to new records created with an "INSERT INTO" statement (given with a TMSQuery/Execute command), immediately after the statement are executed.

I would not use the SELECT MAX(field) query to obtain the new identifier because this kind of records are created very often in a network environment so it's not the most sure solution.

Thanks a lot for anyone can help me

Gianco

Dimon · Post by **Dimon** » Tue 06 Jan 2009 12:10

Do use you SDAC SQL Generator to generate the INSERT statement or you have provided your own statement?
If you use SDAC SQL Generator, check that the value of the TMSStoredProc.Options.QueryIdentity property is True and you have INDENTITY columns in the result set. In this case an identity field value will be set automatically.
If you use your own SQL query then add this line to the query:

Code: Select all

SET :Identity_field = SCOPE_IDENTITY()

In this case you will get the field value as an output parameter.

Gianco70 · Post by **Gianco70** » Tue 06 Jan 2009 12:56

Thank you very much!

Regards
Gianco