Skip to main content
A
Anonymous
April 18, 2011
Question

CF DSN 'loses track' of tables and views

  • April 18, 2011
  • 1 reply
  • 778 views

... but only in one database and only at a certain time (around 8am). We have a manual solution, Open the DSN and switch the server name to Network alias or vice versa and save - but why does this work? Ae we refreshing the connection pool? I've increased the connection pool from 100 - 200, decreased the timeout to 10 minutess and checking to 3 minutes and, t.urned off autogenerate keys        We've completed SQL traces, CF DSN logs, and tested different DNS entries, nothing notable yet.


We checked with networking folks, they are not backing up at this time... anyone have something we haven't tried or should have tried a different way or can suggest a way to test to pinpoint the problem?

Our infrastructure is WinServ 2008 w/ SQL2008r2 and a Winserv2008 and CF9 (it doesn't look like we have the right version to use cold fusion monitor)

Thanks in advance,

Chris

    This topic has been closed for replies.

    1 reply

    ilssac
    ilssac
    Inspiring
    April 18, 2011

    Any chance you have SELECT * code in your application?

    Any chance the structure of the database somehow changes (new tables and|or columns added and|or removed)?

    There is a very hard to track problem with SELECT * code where the database driver will cache table and column structure.  Then if the structure is changed in the database, the cahced version IS NOT updated.  Then the next time the SELECT * code runs it is using an outdated layout of the database and can throw errors similar to what you described.

    A
    Anonymous
    April 18, 2011

    I am currently reviewing code for any Select * calls, but your point about the underlying table structure is notable - the main tables are updated via .Net processes each night. The structures are not changed that I am aware of, but the data is refreshed (in whole or part) each night. It seems to be similar to the type of situation you've described.

    But, what is the effect of changing the DSN server/alias? Does that refresh the connection pool? Is there some function that I can replicate the manual DSN switch?

    Thanks again for responding ilssac. Beyond the manual work around, I think I'll try changing the table specifc calls to views.

    ilssac
    ilssac
    Inspiring
    April 18, 2011

    Yes, I beleive you are refreshing the Cached Connections, causing everthing to be read fresh.

    IF that is the problem.  AND IF you can live with the preformance trade offs you can set the DSN to NOT cache the connection.  Thus every time the <cfquery...> is run new connections are made.  For all but the highest traffic, large data consuming sites this may not be to bad.

    You could also do this programmatically with a schedulded task that does the DSN change using the ColdFusion Administrator API to refresh the connection every morning.

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