Acces SQL Insert Problem
Hello All,
We have a Access FE and SQL BE (2008 sql express windows server 2012) and the database was working flawlessly more then two years.
Now we have a problem with one table (not a large table 17 fileds and 125,000 records)
I m adding records in a loop i.e eight records, the system will add 3 or 4 recs and will stop with msg "ODBC -- Insert on linked table TableName failed"
I already had a professional SQL guy looked at the problem with many sql commends, the profiler doesn't show anything because the sql "stops and locks in a stand still with no indication and any hint why it stopped.
He said it looks like a locking issue but he cant see why and who is locking the table.
Thanks in advance it's very important.
We have a Access FE and SQL BE (2008 sql express windows server 2012) and the database was working flawlessly more then two years.
Now we have a problem with one table (not a large table 17 fileds and 125,000 records)
I m adding records in a loop i.e eight records, the system will add 3 or 4 recs and will stop with msg "ODBC -- Insert on linked table TableName failed"
I already had a professional SQL guy looked at the problem with many sql commends, the profiler doesn't show anything because the sql "stops and locks in a stand still with no indication and any hint why it stopped.
He said it looks like a locking issue but he cant see why and who is locking the table.
Thanks in advance it's very important.
Vitor Montalvão
How big is your database now? MSSQL 2008 only allows 4GB for database size. If you upgrade it to MSSQL 2008R2 or even newer version then it will allow up to 10GB database sizes.
ASKER
I looked in SQL for the Database size.
Size: 4866.94 MB
Space available: 0.98 MB
Does it look full?
How come I only have the insert problem only with one table?
Size: 4866.94 MB
Space available: 0.98 MB
Does it look full?
How come I only have the insert problem only with one table?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Does it look full?Yes it does. You already reached the 4GB database size limit.
How come I only have the insert problem only with one table?Because must be the only table that has all datapages full and need to create a new datapage to insert the new record. Other tables must still have space in their datapages so they still not have this problem.
OOps ...i missed the size....
ASKER
John,
I followed your list.
Then i took off the page lock from that table in sql and so far we didn't have any problems
Thanks again
I followed your list.
Then i took off the page lock from that table in sql and so far we didn't have any problems
Thanks again