MS-SQL/ColdFusion: Mysterious database error: Object has been closed

I'm using ColdFusion with Microsoft SQL Server 2005 and on occasion I get this error:

"[Macromedia][SQLServer JDBC Driver]Object has been closed."

The traceback invariably leads to an SQL query as one would expect, even simple SELECT commands with no JOINs or input parameters or anything fa开发者_JAVA技巧ncy at all.

The only solution I've heard about so far is to restart "the service", which I can only imagine refers to the SQL Server service. I've tried restarting both the SQL Server and the ColdFusion Service, but it still keeps happening at random times.

(Also a restart isn't a solution. One cannot have arbitrary pages giving arbitrary error messages at arbitrary times in a production environment.)

When it occurs, it occurs persistently for a few minutes, then goes away and might come back in a few seconds or minutes. It started happening when I was developing a JavaScript portion of my application which should have nothing to do with either the ColdFusion code or the SQL commands.

My searches online have been fruitless so far.

Any help appreciated.

This is old, but I just ran into this and found a way to do it without restarting CF.

In the ColdFusion Administrator go to your Data Sources and edit the one that's causing your problem. Make a change in it, like checking an unimportant box, and Submit.

This will reconfigure the connection and allow it to work. Go back in and un-tick the box (if you don't really want it ticked).

For example. I turned off "Enable High ASCII characters..." hit submit and the datasource worked. I then went back in and checked it back off and submit the changes again.

No restart! Ideal if this is a production box.

We ran into this problem with CF9 and restarting the service didn't help. We deleted and recreated the offending DSN in the CF configuration, and that has resolved the issue. So if the other solutions don't work, give this a try.

My gut instinct is that you're going to need to look outside of your code and work with a DBA to investigate the stability of the database server itself. It sounds like the SQL Server service is hanging or becoming unresponsive.

Like most all software that misbehaves, the first place I look is drivers. You may want to be sure that you're using the latest Microsoft JDBC drivers. I know they've improved the performance of their latest drivers, and this may also resolve the issue you're seeing.

If you're using the drivers that ship with ColdFusion, it's absolutely worth upgrading.

http://www.microsoft.com/downloads/en/details.aspx?FamilyID=a737000d-68d0-4531-b65d-da0f2a735707&displaylang=en

Be aware that if you do upgrade, if you have INSERT statements that use SCOPE_IDENTITY() to return the ID of the newly inserted row, you'll need to at SET NOCOUNT ON; at the top of each INSERT statement to prevent SQL Server from returning the number of rows inserted instead of the ID of the inserted row.

Dan

There was a bug in sQL 2K and I think 2K5, such that the system would think it was in shutdown mode, despite never being asked to close the service. IIRC, it threw an error similar to yours. There was a patch for this issued my MS, though I seem to recall it was a separate download instead of part of MS Update.

Sorry to be so vague, but it's been a while since I dealt with this problem. Hope this helps get you started, though.

Turns out this simply happens when the server is overloaded. There seems to be no remedy other than restarting and/or removing load from the server.

Thanks all who answered, anyway!

I'll share an observation that I had this problem when it turned out simply that for some reason, SQL Server had stopped. Restarting it fixed it.

You might think that "certainly anyone would notice that", but it's possible that with a web app that mostly does the R in CRUD, it could be that only some and not ALL requests would fail--only those really trying to talk to the db. That could be because of use of query caching within CF, or web server or proxy caching, or browser caching, etc. I was indeed surprised to find the DB down.