SQL Server @@IDENTITY strangeness

Report · Nov 25, 2008

OK something changed in CF8. Either in the JDBC or the SQL Server drivers.

I have a lump of code that looks like this in a custom tag

<cfquery name="make_new_ref_id_qry" datasource="#ATTRIBUTES.dsn#">
INSERT INTO referral_tbl
(referral_type,
an_number,
account_number,
referral_status_code,
rejection_code)
VALUES
('INIT',
'#ATTRIBUTES.AN_number#',
'#ATTRIBUTES.account_number#',
'INIT',
0)
SELECT new_id = @@IDENTITY
</cfquery>
<CFSET rms_referral_id = make_new_ref_id_qry.new_id>

To get the new ID out. Now I'm changing this to be a stored proc that gives me the ID back cos I know this is really sloppy practise.

But this code has been running fine on our CFMX7 boxes, we've upgraded our dev boxes to CF8 and sometimes (not always) this code dies with the fllowing error being reported

Element NEW_ID is undefined in MAKE_NEW_REF_ID_QRY.

Now I want to know is there something I can change on the CF8 boxes that will make it easy for me to just upgrade the porduction boxes or do I have to trawl all my apps and code for this stuff and turn them into stored procs?

What changed?

Also does anyone have any idea, why it works sometimes?

I'd point out that the dev system and live system are in fact talking to the same database. So there's no difference on the SQL Server side of things.

many thanks

Steve

Report · Nov 25, 2008

Steve Powell wrote:
> OK something changed in CF8. Either in the JDBC or the SQL Server drivers.

not anything that would show the behavior that you're describing. cf8 added a
"result variable" that specifies the ID of a row. your code's not using the
results option so i wouldn't think it would have any effect.

> <cfquery name="make_new_ref_id_qry" datasource="#ATTRIBUTES.dsn#">
> INSERT INTO referral_tbl
> (referral_type,
> an_number,
> account_number,
> referral_status_code,
> rejection_code)
> VALUES
> ('INIT',
> '#ATTRIBUTES.AN_number#',
> '#ATTRIBUTES.account_number#',
> 'INIT',
> 0)
> SELECT new_id = @@IDENTITY
> </cfquery>
> <CFSET rms_referral_id = make_new_ref_id_qry.new_id>

kind of surprised that worked. usually if we use multiple SQL statements like
that we wrap the whole mess in SET NOCOUNT ON/OFF block to tell sql server *not*
to return results prematurely to cf.

is either box under a load when the identity goes AWOL? are you 100% sure this
never happened under cf7?