June 7, 2004 at 8:13 am
I can add & retrieve data to & from this table with no problems.
The problem occurs when I try to alter any data in this table.
Any update operation will complete eventually, but can take up to 20 minutes!
Primary Key defined as ID.
There are 4 foreign keys on this table and 1 index using 4 columns.
However I am not updating any of these fields.
The web app times out after 60 seconds or so, so I am running queries through Query Analyzer only at the moment.
It is from here, that they sometimes take 20 minutes as web app would have fallen over a long time before.
I have also copied entire application including database from local workstation to development server and same problem occurs!
It appears to be connected in some way with the image data as I have deleted these columns from the table and everything works fine.
I haven't tested this from the web app, as it will require changing Stored Procedures and Web app code.
Incidentally whilst running update query on table containing image data, there is no noticable change in performance monitor.
Neither CPU or memory useage alter to any great degree.
Can anyone assist me with this please?
June 7, 2004 at 8:35 am
I have had this joyous thing happen on a couple of occassions.
I couldn't see anything wrong so I got the web page to write out the SQL statement that it was trying to execute.
I then copied the SQL Statement into SQL Query Analyser and tried to run it.
BINGO! it told me I had a parameter not surrounded by quotes.
I now make sure that I have full error trapping and logging on all ASP pages so I can see what is going on.
Some CMS's do this as well. They log the error, but don't really trap it so your page goes round and round in circles in a deadly embrace.
June 7, 2004 at 8:57 am
As stated, when I try and run a simple update query from Query Analyzer it times out too.
I am not trying to execute a SQL statement that I build on the fly, the web application calls a Stored Procedure (which works fine).
The problem lies in the SQL database somewhere not the web application.
June 10, 2004 at 3:45 am
After further investigation, the problem seems to occur once the web application has called the relevant functions.
There are two functions, one selects the data to display and the other updates a flag on the table.
The update is called before the select so that the updated data is re-displayed once the web page has been refreshed.
However what appears to be happening is that the process that calls the select is locking the process that runs the update.
I have deduced this by looking at the process/locks within Ent Mgr.
Once this has happened the only way to clear and process transactions is to run
"SET Transaction Isolation Level Read Uncommitted".
Why would this be happening?
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply