SQL Server @@IDENTITY strangeness

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