Sql 2012 - identitycol is gone ???

dgrafx
dgrafx used Ask the Experts™
on
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 ...
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2015

Commented:
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();
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
Most Valuable Expert 2015

Commented:
> 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.
Exploring ASP.NET Core: Fundamentals

Learn to build web apps and services, IoT apps, and mobile backends by covering the fundamentals of ASP.NET Core and  exploring the core foundations for app libraries.

Top Expert 2012

Commented:
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.
Thanks for the info Anthony ...

AGX
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
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.
Most Valuable Expert 2015

Commented:
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?
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.
Most Valuable Expert 2015

Commented:
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?
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
sorry for the delay ...

here is the url: jdbc:macromedia:sqlserver://***:1433;databaseName=***;SelectMethod=direct;sendStringParametersAsUnicode=false;MaxPooledStatements=1000
Most Valuable Expert 2015
Commented:
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.
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

thanks
Most Valuable Expert 2015

Commented:
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.
will do ...

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial