Sql 2012 - identitycol is gone ???

Have a client who just upgraded to Sql Server 2012 and are using ColdFusion 8.
They have some code that grabs the identity of the newly inserted row that has worked for years until the upgrade.
The code is basically this: After your insert query you ask CF what the identitycol value is. result.identitycol

Now when dumping the query results the output is the same except it is missing the identitycol column.

From what I've Googled 2012 has deprecated identitycol to be replaced with $identity but nothing said about eliminating it.

Looking for some ideas on what happened ...
LVL 25
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

CF's implementation of that feature is a bit buggy IMO.  I haven't used 2012 but remember a bunch of weird issues with CF8+2005.  The problems differed depending on which jdbc driver was used - might apply to different sql server versions too - and the exact cfquery.  

In order to get the new id, CF appends "select scope_identity()" to the sql before sending it off to the db. Sounds great, but turns out different things could prevent it from working right: comments "---", multiple statements, SET NOCOUNT, etc..

Not about your issue specifically, but this old entry has some good tips on troubleshooting weirdness with CF8's "result.identitycol".  Try using SQL Profiler. Run the INSERT. What SQL is sent to the db?  

Also, so as not ignore the simple/obvious stuff:

* Do the cfquery's contain any other sql besides the INSERT statement?
* Which driver is their DSN using (built-in driver or "Other" - jTDS, MS JDBC driver, ...)?
* Any special DSN settings?
* If you run the insert within the db, I assume it returns the expected identity value? ie

          insert into table (columns)
          values (.....);
          select scope_identity();
dgrafxAuthor Commented:
thanks for replying

forgot to mention the oddity - it all works swell on dev but the error described happens on prod
so they have been trying to figure out what is different between the environments and it all appears the same including cf version, sql drivers etc
PLUS it is somewhat intermittent - it will fail 1 out of 3 times

sql statement is one simple insert and yes the identity will return if selecting scope_identity()

nobody is in that office until Monday so will post back then
> somewhat intermittent - it will fail 1 out of 3 times

Intermittent stuff is always trickier to track down.

Is it always the same query(ies) every time? Most of the weirdness I encountered was across the board ie happened every time.
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

Anthony PerkinsCommented:
Unrelated to your question, but you should know that starting with SQL Server 2012, the identity value will jump by 1000 every time you restart the server, so if you are relying on this number to not have any gaps you may want to look into using the Sequence object instead.
dgrafxAuthor Commented:
Thanks for the info Anthony ...

To answer about the original query
no comments
there is only one statement (an insert statement) - no other sql
there is no SET NOCOUNT
same JDBC driver as on other environments - remember it only fails on prod
the actual identity column value appears to not be set when failure occurs - we are testing that now as well as preparing to run a trace - we need to take a prod server out of the pool first
dgrafxAuthor Commented:
update: randomly CF does not send "Select Scope_Identity()" to the DB with the insert sql ...
And this is only on the PROD DB server.
Once can connect to the PROD DB from DEV CF and it will work!
We cannot figure out what is different.
So wow!
And yes we had a fix in place but wanted to know WHY.
Sorry I've been swamped today.  

      > Once can connect to the PROD DB from DEV CF and it will work!

So PROD and DEV are both using 2012 - same patches and everything?

      > update: randomly CF does not send "Select Scope_Identity()" to the DB with the insert sql ...

You mean if you run the same statement "statement A" 4 times, CF only appends scope_identity say - 3 out 4 times? Really weird ... obviously if it doesn't send the command, you won't get the ID, but I can't explain why it only sends the command 3 times out of 4.

Any unusual commands in the trace, right before that happens? SET NOCOUNT, etc... ?

Might also enable logging in the DSN settings, just to confirm that what CF sent ... is what's received by SQL Server on the other end. I don't expect it'd be any different, but more info couldn't hurt.

> same JDBC driver as on other environments

So the built in driver? I assume maintain connections is checked on both?  All other DSN settings are the same?
dgrafxAuthor Commented:
yes to all your queries ...
i just found out that under driver they selected ms sql (or whatever it says) and on prod they selected other and entered everything manually (which is he same driver as the ms sql uses), but the db guys say that after analyzing everything the specs are identical.
Hm... without seeing the actual dsn settings, it doesn't sound like they're using the same driver. Otherwise, why use different dsn settings on Prod and Dev?

Selecting driver type "MS SQL ...." on the DSN Screen, means the dsn will use the SQL Server driver from DataDirect. Normally you only select "Other" if you want to use a different driver from say another vendor, for example the MS JDBC driver.

Which one is the "Other" dsn using? What's the jdbc url?
dgrafxAuthor Commented:
the reason for selecting other was so that the driver could be changed down the road with a simple update in the settings.
so i am told ...

I'll post back with the url
dgrafxAuthor Commented:
sorry for the delay ...

here is the url: jdbc:macromedia:sqlserver://***:1433;databaseName=***;SelectMethod=direct;sendStringParametersAsUnicode=false;MaxPooledStatements=1000
sorry another crazy few days...

> jdbc:macromedia:sqlserver

Ok, then they are both using the built in driver.  

Curiouser and curiouser.  Honestly, seems like something is different but if you're positive it's the exact same CF version w/patches, SQL Server w/patches, and driver ... then I'm not sure what to think. When things slow down I'll mull over the list of what you've said is/isn't happening on prod vs dev.. see if anything else jumps out at me.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
dgrafxAuthor Commented:
according to the db guys - yes - everything is exactly the same!
but obviously there is something ...

I'll close this question but would appreciate that if you think of anything to post it

Will do.  Keep me posted on anything else you discover about the behavior (like it *sometimes* fails when pointed to DEV, the code is using exotic SQL commands like SET NOCOUNT, etc... )  too.
dgrafxAuthor Commented:
will do ...
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.