Things to check in ColdFusion:
1. Is there any information in the ColdFusion logs?
2. Can you post your code?
3. Can other queries of the SQL Server database and/or target table be run from your ColdFusion code before 8:30 and after 1:00?
Things to check in SQL Server:
1. Does your table have any triggers that change how inserts are handled?
2. Are there any long running queries, jobs, or maintainence tasks that are locking the table and preventing inserts?
3. Try running the SQL profiler tool. This will show the queries being executed on your SQL server.
4. Is there any information in the SQL logs or Windows event logs?
There is no information in the ColdFusion logs, and the entry form is
1842 lines and the action page is 457 lines long, so I'm attaching just
the insert query and the update query. Last night I was able to
successfully insert a record at 12:30 am, but now (since 7:30 am) I have
been unable to successfully insert any new records (current time 3:26
pm). I can still view and edit previous records successfully at any
time, however, I have not re-written the update query with the
I will check the SQL server as you suggest... thanks for all your help
Oops! here is the insert query:
Your insert query does not show up on the forum.
here is no information in the ColdFusion logs, and the entry form is 1842 lines and the action page is 457 lines long
With pages this large is it possible there is a logic error hiding somewhere in your code that prevents the CFQUERY insert block from being executed? I'd use SQL profiler to see if the insert statement is even being sent to the SQL Server.
As a simple test of whether this is SQL or ColdFusion related, when you are not able to make an update through the code, login as the ColdFusion user to the SQL database and attempt to insert a record. If that works, then try running a stored procedure that wasn't working. If there are issues, it should show you the error code or you can run profiler while running these queries to see what is happening.
If both of those work try the ColdFusion side again. If it still doesn't work, you know that the problem is in ColdFusion, not SQL related. If the problem is ColdFusion related, remove your error handling code to see what the problem might be.
This should start narrowing things down a little for you. Let us know which is causing the problem and any error codes you are receiving.
I have now solved the problem.... I am embarrassed to say that the
insert was in fact working all along. Using the SQL profiler we were
able to see that the insert query was working, but the ID was improperly
formatted and therefore was returning the wrong record. The
submission_id was set to insert the date and time as the id but the mask
was written #DateFormat(Now(),"yyyymmdd")##TimeFormat(Now(),"hhmmss")#
instead of #DateFormat(Now(),"yyyymmdd")##TimeFormat(Now(),"HHmmss")#.
Therefore every day at 1:00pm and 1:00 am record id's were being sorted
out of sequence, returning the last record inserted prior to the change
from 12:59 to 1:00.
All of the suggestions I received were very helpful in narrowing down my
stupid mistake... many thanks!