Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Sql 2012 - identitycol is gone ???

Posted on 2014-08-01
16
Medium Priority
?
613 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 ...
0
Comment
Question by:dgrafx
  • 9
  • 6
16 Comments
 
LVL 52

Expert Comment

by:_agx_
ID: 40235596
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();
0
 
LVL 25

Author Comment

by:dgrafx
ID: 40235607
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
0
 
LVL 52

Expert Comment

by:_agx_
ID: 40235610
> 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.
0
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 40236360
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.
0
 
LVL 25

Author Comment

by:dgrafx
ID: 40239506
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
0
 
LVL 25

Author Comment

by:dgrafx
ID: 40239640
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.
0
 
LVL 52

Expert Comment

by:_agx_
ID: 40239974
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?
0
 
LVL 25

Author Comment

by:dgrafx
ID: 40240136
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.
0
 
LVL 52

Expert Comment

by:_agx_
ID: 40240330
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?
0
 
LVL 25

Author Comment

by:dgrafx
ID: 40240464
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
0
 
LVL 25

Author Comment

by:dgrafx
ID: 40242154
sorry for the delay ...

here is the url: jdbc:macromedia:sqlserver://***:1433;databaseName=***;SelectMethod=direct;sendStringParametersAsUnicode=false;MaxPooledStatements=1000
0
 
LVL 52

Accepted Solution

by:
_agx_ earned 2000 total points
ID: 40244068
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.
0
 
LVL 25

Author Comment

by:dgrafx
ID: 40244194
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
0
 
LVL 52

Expert Comment

by:_agx_
ID: 40244514
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.
0
 
LVL 25

Author Comment

by:dgrafx
ID: 40244523
will do ...
0
 
LVL 25

Author Comment

by:dgrafx
ID: 40289244
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …
Screencast - Getting to Know the Pipeline

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question