We help IT Professionals succeed at work.

Sql 2012 - identitycol is gone ???

832 Views
Last Modified: 2014-08-27
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

CERTIFIED EXPERT
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();
CERTIFIED EXPERT

Author

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
CERTIFIED EXPERT
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.
CERTIFIED EXPERT
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.
CERTIFIED EXPERT

Author

Commented:
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
CERTIFIED EXPERT

Author

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.
CERTIFIED EXPERT
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?
CERTIFIED EXPERT

Author

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.
CERTIFIED EXPERT
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?
CERTIFIED EXPERT

Author

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
CERTIFIED EXPERT

Author

Commented:
sorry for the delay ...

here is the url: jdbc:macromedia:sqlserver://***:1433;databaseName=***;SelectMethod=direct;sendStringParametersAsUnicode=false;MaxPooledStatements=1000
CERTIFIED EXPERT
Most Valuable Expert 2015
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION
CERTIFIED EXPERT

Author

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

thanks
CERTIFIED EXPERT
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.
CERTIFIED EXPERT

Author

Commented:
will do ...
CERTIFIED EXPERT

Author

Commented:

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions