Skip to main content
G
ghanna1
Inspiring
September 15, 2020
Answered

Phantom cfquery column

  • September 15, 2020
  • 2 replies
  • 771 views

Here's a unique one.  My only SQL statement on this page is:

<cfquery datasource="#session.mix#" >
Update Status SET
AuthToken = '#Results.Auth_Token#',
AuthTokenExpire = #AuthExpire#
Where FID = '#key.FID#'
</cfquery>

When I execute it I get:

[Macromedia][SQLServer JDBC Driver][SQLServer]Invalid column name 'NorthStatus'. 

The variable NorthStatus doesn't appear anywhere in the code for this page. I've RDP to the server and checked the code there too.  It doesn't appear in the SQL table. It's a column that was long ago deleted from the table. If I update the SQL table and add a column NorthStatus the page runs fine and nothing ever changes in the column NorthStatus.  Yes, it's a workaround but it makes zero sense. I've rebooted both the SQL server and CF server just for kicks.

 

Anybody got a clue?

 

Gary

    This topic has been closed for replies.
    Correct answer ghanna1

    That's great information but it didn't solve the problem. As a test, I moved the page to our disaster recovery server and it had the same issue. It's a different SQL server also BUT the tables are synced. As a last resort I deleted the tables in both the primary and DR SQL servers and recreated it. Poof...Like magic the code works. So something was corrupt in the SQL table and CF reported it back as a column missing.

    2 replies

    Charlie Arehart
    Community Expert
    Charlie ArehartCommunity Expert
    Community Expert
    September 16, 2020

    I realize you've solved this with a delete of the table, but in case somehow someone else ever has this and finds this, it would have been interesting if things would have "worked" merely by modifying the update statement in any way, like switching the order of the cols listed to be updated.

     

    If that worked, it would confirm that there was indeed a cached statement somewhere, either in the dB or perhaps in the dB driver. (Changing the sql statement causes any cached variant to be ignored and a new one to be created. And such a cache would not be cleared by any of the buttons in the CF Admin, as it seems whatever it was did survive restarts of cf and the dB server. But if doing this solved things, maybe it wouldn't matter to dig further.) 

     

    Again, a moot point now, but just sharing it for future consideration. 

    /Charlie (troubleshooter, carehart. org)
    G
    ghanna1Author
    Inspiring
    September 16, 2020

    Charlie, The original page had 3 potential update statements. 1 of the 3 would trigger based on conditions. When I moved it to DR I stripped it down to just one SQL Update as a test. The original code also had a column named auth token which I changed to AuthToken (removed the space). So yes it's possible that changing the column name was hung up somewhere. I am also using symmetricDS to sync the two SQL servers. It creates a bunch of triggers behind the scenes so that's a possibility also.

    The table also only had 1 row. It was storing tokens for f5 cloud load balancing so deleting it was no big deal. It would have been great to keep troubleshooting to find the real cause but banging my head against the desk for 3 hours was enough 🙂 

     

    Gary

      Charlie Arehart
      Community Expert
      Charlie ArehartCommunity Expert
      Community Expert
      September 16, 2020

      Totally understandable, and glad you were able to prevent further head-banging. 🙂 Again, I just shared the info in case it may help others (or perhaps you), if somehow something like this ever happens again.

      /Charlie (troubleshooter, carehart. org)
      BKBK
      Community Expert
      BKBKCommunity Expert
      Community Expert
      September 15, 2020

      You say, "It's a column that was long ago deleted from the table.". So, perhaps, something might have been cached somewhere.

      For further kicks, revert to the original situation. Go to the caching page of the ColdFusion Administrator, press the button Clear Template Cache Now and then the button Clear Query Cache Now. Next, delete all files from wwwroot\WEB-INF\cfclasses.

      G
      ghanna1AuthorCorrect answer
      Inspiring
      September 15, 2020

      That's great information but it didn't solve the problem. As a test, I moved the page to our disaster recovery server and it had the same issue. It's a different SQL server also BUT the tables are synced. As a last resort I deleted the tables in both the primary and DR SQL servers and recreated it. Poof...Like magic the code works. So something was corrupt in the SQL table and CF reported it back as a column missing.

      EddieLotter
      EddieLotter
      Inspiring
      September 15, 2020

      It might have been a trigger causing the error. Dropping the table will have dropped all triggers as well. Just a thought.

        Terms & ConditionsAccessibility statement
        Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices