1

In ColdFusion 2018, we have a <cfquery> that performs an INSERT INTO a table. We return the result attribute and check for the GENERATEDKEY. From one environment, the GENERATEDKEY is returned in the result struct. From another environment (on a different server), pointing to the same SQL Server DB with the same datasource connection settings, the record is inserted but GENERATEDKEY is not returned in the result struct.

Given that both environments point to the same server, DB, and table and use the same datasource settings and have the latest ColdFusion update installed, I'm guessing (from lots of Googling) that maybe the faulty environment may have an out-of-date JDBC driver for SQL Server.

Is there a way to check the version of the JDBC driver for SQL Server, so I can compare them in the 2 environments? And how do I update that, if necessary?

Nick Petrie
  • 5,364
  • 11
  • 41
  • 50
  • In the CF Administrator, you'll have to check the Datasource connection settings. I feel like the one that doesn't return `GENERATEDKEY` is using a JDBC-style connection string (`jdbc:...`) instead of the standard driver connection. You may have to talk with your DevOps team. – Adrian J. Moreno Sep 28 '22 at 14:55
  • Hmm... The JDBC driver question is based on what others have talked about in other threads... though generally for older versions of CF (8 or below). Maybe that's not the answer. When setting up the datasource in CF Admin, we just picked the "Microsoft SQL Server" from the Driver drop-down. – Nick Petrie Sep 28 '22 at 16:33
  • You can get the total server settings out of CF Admin and compare between servers if everything matches. There's a page that lists everything at once. The JDBC URL would be the only way that the `GENERATEDKEY` value would not be available. Otherwise, it would have to be that one server does not have the attribute in the `cfquery` that returns the result struct. Are you 100% sure the code is the same on both servers? – Adrian J. Moreno Sep 28 '22 at 18:35
  • The field in the CF administrator that controls this is under the advanced settings in the datasource and is labeled: "Disable Autogenerated Keys" and when checked it will disable retrieval of autogenerated keys... you should make sure that is unchecked as Adrian says – Scott Bennett Sep 28 '22 at 19:35
  • Thanks. I hadn't seen the "Disable Autogenerated Keys" option. Unfortunately, it's currently unchecked on the faulty server. Still, I'd like to check and uncheck it to see if that shakes it loose. But I'll have to wait for an off-hours time, maybe over the weekend. – Nick Petrie Sep 28 '22 at 23:15
  • Also, yeah, absolutely 100% sure the code is identical. In this case, it's also a pretty simple query. I had others take a look too, just in case I was overlooking something. – Nick Petrie Sep 28 '22 at 23:21

0 Answers0