bfuchs
asked on
Urgent! Cannot insert new records in SQL table.
Hi Experts,
Suddenly I can not insert new records in a SQL table.
See error attached.
PS. We restarted the server recently, could be that caused the problem?
In addition I realized something strange with this table, when linking it into Access, its showing the wrong field as PK.
As per users, since this problem started (today morning) at one point they were able to add, and now again same issue.
What could these possible be?!
Thanks
Untitled1.png
Suddenly I can not insert new records in a SQL table.
See error attached.
PS. We restarted the server recently, could be that caused the problem?
In addition I realized something strange with this table, when linking it into Access, its showing the wrong field as PK.
As per users, since this problem started (today morning) at one point they were able to add, and now again same issue.
What could these possible be?!
Thanks
Untitled1.png
ASKER
@pcelba,
The attached error is from trying to enter it using SSMS, nothing to do with Access.
Thanks,
Ben
The attached error is from trying to enter it using SSMS, nothing to do with Access.
First of all check the PK on SQL ServerThe PK in SQL looks fine in design view.
BTW, what timeout is set for the connection to SQL Server?Where do I check that?
Thanks,
Ben
Sorry, I did not realize it...
So in SSMS click on Tools - Options, and Select Query Execution from tree on left side and check the "Execute Timeout" value.
Also when you are editing data in SSMS then you have to make sure the table has PK defined and the PK is entered in SSMS.
So in SSMS click on Tools - Options, and Select Query Execution from tree on left side and check the "Execute Timeout" value.
Also when you are editing data in SSMS then you have to make sure the table has PK defined and the PK is entered in SSMS.
How are you trying to update the record? Make sure the data is correct and if there is a unique key, it's not a duplicate in the data.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
@pcelba,
Execution timeout = 0.
Thanks,
Ben
Untitled.png
Execution timeout = 0.
Also when you are editing data in SSMS then you have to make sure the table has PK defined and the PK is entered in SSMS.See attached design of table.
Thanks,
Ben
Untitled.png
Dose the INSERT command work when entering it in SSMS?
ASKER
trying DBCC DBREINDEX (SkilledNursingVisitNotes)
getting
Msg 2501, Level 16, State 45, Line 1
Cannot find a table or object with the name "SkilledNursingVisitNotes" . Check the system catalog.
@Joan,
As you can see from attached, table has a PK and data is unique.
Thanks,
Ben
getting
Msg 2501, Level 16, State 45, Line 1
Cannot find a table or object with the name "SkilledNursingVisitNotes"
@Joan,
As you can see from attached, table has a PK and data is unique.
Thanks,
Ben
The name of the table has underscores correct? I don't see any in the above command
ASKER
@Joan,
You right, however same error.
Msg 2501, Level 16, State 45, Line 1
Cannot find a table or object with the name "Skilled_Nursing_Visit_Not es". Check the system catalog.
also tried dbo.Skilled_Nursing_Visit_ Notes.
Thanks,
Ben
You right, however same error.
Msg 2501, Level 16, State 45, Line 1
Cannot find a table or object with the name "Skilled_Nursing_Visit_Not
also tried dbo.Skilled_Nursing_Visit_
Thanks,
Ben
ASKER
Executing this takes forever...
insert into Skilled_Nursing_Visit_Note (snv_id) values ('001122')
Thanks,
Ben
insert into Skilled_Nursing_Visit_Note
Thanks,
Ben
ASKER
Doing this now, lets hope this solves the problem...
DBCC DBREINDEX (Skilled_Nursing_Visit_Not e)
Thanks,
Ben
DBCC DBREINDEX (Skilled_Nursing_Visit_Not
Thanks,
Ben
If the insert still takes forever then you have to check all triggers and constraints.
I am under the impression that you have some kind of corruption.. hopefully not...so probably you should start checking on the hardware as well ..check if SSMS can perform CRUD operations and if results are not the expected ones probably you should start expanding your search...
ASKER
@pcelba,
The DBCC DBREINDEX (Skilled_Nursing_Visit_Not e) is taking very long.
How long should I wait?
Thanks<
Ben
The DBCC DBREINDEX (Skilled_Nursing_Visit_Not
How long should I wait?
Thanks<
Ben
And one more thing - the table can be locked in some uncommitted transaction. So issue the INSERT again and in a separate query window issue sp_who2. This command should show what blocks your INSERT command.
If you are still waiting for DBCC issue sp_who2 now. sp_who2 requires admin permissions to show other connected sessions.
ASKER
If the insert still takes forever then you have to check all triggers and constraints.None of them exists for this table.
Thanks,
Ben
ASKER
under sp_who2 I see many things and two SUSPENDED
are these the cause?
Thanks,
Ben
are these the cause?
Thanks,
Ben
SUSPENDED is OK. So I would recommend to cancel all your SSMS queries and close query windows. Then issue the INSERT again and try sp_who2. BlkBy column is the most important one now.
Update: SUSPENDED is OK if its SPID does not appear in BlkBy column for other process.
ASKER
whats about re-index command, should leave that going?
Thanks,
Ben
Thanks,
Ben
No, cancel it.
How many rows is in the table?
ASKER
One Suspended ID is appearing at the other suspended BlkBy, is that OK?
Thanks,
Ben
Thanks,
Ben
If the table design was changed in SSMS you should remove the linked table and relink it in MsAccess .. along with any other tables with linked relationships.
BlkBy is OK for a short time only. And you have to also check whether the SPID isn't yours... Look at other info in suspended processes.
ASKER
@Ron,
The problem is happening in SQL as well.
There were no recent changes to that table design.
Thanks,
Ben
The problem is happening in SQL as well.
There were no recent changes to that table design.
Thanks,
Ben
ASKER
@pcelba,
I should kill the offending process (its not myself)?
Thanks,
Ben
I should kill the offending process (its not myself)?
Thanks,
Ben
You may kill it when you are sure you don't damage someone other's work... It could be good to investigate what it does and who executed it.
ASKER
at the moment nobody can work anyways...
also this is still lasting forever
insert into Skilled_Nursing_Visit_Note (snv_id) values ('001122')
What should I do next?
How about stop everything and restart the server?
Thanks,
Ben
also this is still lasting forever
insert into Skilled_Nursing_Visit_Note
What should I do next?
How about stop everything and restart the server?
Thanks,
Ben
SUSPENDED process means the process is waiting for some resource. Then other process may be blocked by this suspended process. If the BlkBy value is unchanged for a long time then you may kill such process (with SPID = BlkBy value).
You don't need to restart everything. Just kill processes which are blocking other processes.
ASKER
The reindex finally finished.
however insert into Skilled_Nursing_Visit_Note (snv_id) values ('001122') still not working.
Will kill now the processes and see...
Thanks,
Ben
however insert into Skilled_Nursing_Visit_Note
Will kill now the processes and see...
Thanks,
Ben
ASKER
I killed that one process and the insert works!!
So that means this was causing the whole turmoil?
Will test now my Access app...
Thanks,
Ben
So that means this was causing the whole turmoil?
Will test now my Access app...
Thanks,
Ben
ASKER
Oops,
I got excited too early.
Access is still getting timeout expired when trying to insert records, same goes for SSMS by entering manually.
However the following in SSMS works.
insert into Skilled_Nursing_Visit_Note (snv_id) values ('010112222') .
What should I do next?
Thanks,
Ben
I got excited too early.
Access is still getting timeout expired when trying to insert records, same goes for SSMS by entering manually.
However the following in SSMS works.
insert into Skilled_Nursing_Visit_Note
What should I do next?
Thanks,
Ben
I googled it and here's an example:
Did you put single-quotes around you table name? And you shouldn't have to guess at what the table name is - look it up - get it right.
DBCC CHECKTABLE ('HumanResources.Employee');
Did you put single-quotes around you table name? And you shouldn't have to guess at what the table name is - look it up - get it right.
ASKER
@Mark,
I got pass that already, but this didn't show any error.
Thanks,
Ben
I got pass that already, but this didn't show any error.
Thanks,
Ben
You have to check what is blocking Access and/or editing in SSMS. And this you may do by sp_who2 again.
Try the insert in Access and during the time it waits issue sp_who2 in SSMS.
Before it you should link the table in Access again.
DBCC CHECKTABLE works both with and without apostrophes.
Try the insert in Access and during the time it waits issue sp_who2 in SSMS.
Before it you should link the table in Access again.
DBCC CHECKTABLE works both with and without apostrophes.
ASKER
Before it you should link the table in Access again.If this is also happening in SSMS, why bother changing Access, no?
Skilled_Nursing_Visit_Note
Try the insert in Access and during the time it waits issue sp_who2 in SSMS.Will try that now.
Thanks,
Ben
ASKER
ASKER
I re-linked and now Access works.
Will test now running an append query, which is what we really need to use.
Thanks,
Ben
Will test now running an append query, which is what we really need to use.
Thanks,
Ben
The goal of the blocking investigation should be to answer "Who or what applications is blocking the table?"
Yes, the Access linked table design seemed wrongly... and it could also be the reason for long lasting inserts because the generated SQL was not optimized. These long lasting queries then blocked other commands on the table.
ASKER
Yes, the Access linked table design seemed wrongly...What is the cause of it, and how it can be fixed?
Thanks,
Ben
ASKER
My Access append/update queries are still not working.
Thanks,
Ben
Thanks,
Ben
ASKER
While Access queries are freezing I see two suspended tasks under sp_who2, however BlkBy is null.
Thanks,
Ben
Thanks,
Ben
I cannot say what was wrong with the linked table definition. Maybe the table structure changed on SQL Server, maybe access database is corrupted etc.
To investigate append/update queries you should look what commands are posted to SQL Server in SQL Server Profiler. Or create a new append query and test it.
To investigate append/update queries you should look what commands are posted to SQL Server in SQL Server Profiler. Or create a new append query and test it.
ASKER
Maybe the table structure changed on SQL ServerYes this was the case a while back, and I just had to re-link the table in Access and e/t worked fine.
Something must of happened today, the question is what?
Thanks,
Ben
The suspended tasks must wait for something, e.g. disk. You should look what commands are executed by issuing this query:
SELECT dm_ws.wait_duration_ms,
dm_ws.wait_type,
dm_es.status,
dm_t.TEXT,
dm_qp.query_plan,
dm_ws.session_ID,
dm_es.cpu_time,
dm_es.memory_usage,
dm_es.logical_reads,
dm_es.total_elapsed_time,
dm_es.program_name,
DB_NAME(dm_r.database_id) DatabaseName,
-- Optional columns
dm_ws.blocking_session_id,
dm_r.wait_resource,
dm_es.login_name,
dm_r.command,
dm_r.last_wait_type
FROM sys.dm_os_waiting_tasks dm_ws
INNER JOIN sys.dm_exec_requests dm_r ON dm_ws.session_id = dm_r.session_id
INNER JOIN sys.dm_exec_sessions dm_es ON dm_es.session_id = dm_r.session_id
CROSS APPLY sys.dm_exec_sql_text (dm_r.sql_handle) dm_t
CROSS APPLY sys.dm_exec_query_plan (dm_r.plan_handle) dm_qp
WHERE dm_es.is_user_process = 1
Then you may analyze/post the TEXT and query_plan
ASKER
In Activity monitor I see them executing the following
1.
Thanks,
Ben
1.
SELECT "Patient_Last_Name" ,"Date" ,"Nurse_User_ID_num_Pn" ,"Patient_First_Name" ,"Shift_From_Hour" ,"Visit_Date" ,"Status" ,"SNVID" ,"dbo"."Patient_Progress_Notes"."ID" FROM "dbo"."Patient_Progress_Notes" WHERE (("SNVID_MappedYN" = 0 ) OR ("SNVID_MappedYN" IS NULL ) )
2.(@P1 nvarchar(50))SELECT "SNV_ID" ,"Client_Last_Name" ,"Visit_Date" ,"Shift_From_Hour" ,"Nurse_User_ID_num_SNV" ,"Client_First_Name" ,"Status" ,"dbo"."Skilled_Nursing_Visit_Note"."SNV_ID" FROM "dbo"."Skilled_Nursing_Visit_Note" WHERE ("Status" = @P1)
Thanks,
Ben
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
The two above commands do not look as the troublemakers... Of course, I don't know how often are they executed but you should preferably look at blocked commands and apply the recommendations from PatHartman.
ASKER
@Pat,
However I'm still having trouble executing updates/inserts query.
Thanks,
Ben
1. Rename the unique indexes so that the one you want Access to pick is alphabetically first. Then drop and relink the table.Great, that fixed the wrongly PK issue.
However I'm still having trouble executing updates/inserts query.
Thanks,
Ben
ASKER
@pcelba,
There is nothing showing up as blocked, just two suspended tasks and wait type is "ASYNC_NETWORK_IO", does this tells something?
Thanks,
Ben
There is nothing showing up as blocked, just two suspended tasks and wait type is "ASYNC_NETWORK_IO", does this tells something?
Thanks,
Ben
ASKER
The below keeps switching from suspended to running, while the select task stays at suspended all the time.
Thanks,
Ben
(@P1 nvarchar(50))SELECT "SNV_ID" ,"Client_Last_Name" ,"Visit_Date" ,"Shift_From_Hour" ,"Nurse_User_ID_num_SNV" ,"Client_First_Name" ,"Status" ,"dbo"."Skilled_Nursing_Visit_Note"."ID" FROM "dbo"."Skilled_Nursing_Visit_Note" WHERE ("Status" = @P1)
Any clues?Thanks,
Ben
You may read about the ASYNC_NETWORK_IO here: http://www.sqlskills.com/help/waits/async_network_io/
It could e.g. mean the Access is connected via slow network.
"However I'm still having trouble executing updates/inserts query." What troubles? What error message appears?
It could e.g. mean the Access is connected via slow network.
"However I'm still having trouble executing updates/inserts query." What troubles? What error message appears?
You're welcome Ben,
since you have multiple unique indexes, did you validate that the insert is including proper values for ALL fields that participate in the unique indexes?
since you have multiple unique indexes, did you validate that the insert is including proper values for ALL fields that participate in the unique indexes?
ASKER
What troubles? What error message appears?When executing the following update query Access simply freezes.
UPDATE Patient_Progress_Notes INNER JOIN Skilled_Nursing_Visit_Note ON (Patient_Progress_Notes.Nurse_User_ID_num_Pn = Skilled_Nursing_Visit_Note.Nurse_User_ID_num_SNV) AND (Patient_Progress_Notes.Shift_From_Hour = Skilled_Nursing_Visit_Note.Shift_From_Hour) AND (Patient_Progress_Notes.Status = Skilled_Nursing_Visit_Note.Status) SET Patient_Progress_Notes.SNVID = Skilled_Nursing_Visit_Note.snv_id
WHERE (((Patient_Progress_Notes.SNVID) Is Null Or (Patient_Progress_Notes.SNVID)<>[Skilled_Nursing_Visit_Note].[snv_id]) AND ((IIf(IsNull([Patient_Progress_Notes].[visit_date]),Int([date]),Int([Patient_Progress_Notes].[visit_date])))=Int([Skilled_Nursing_Visit_Note].[Visit_Date])) AND ((Mid([Patient_Progress_Notes].[Patient_Last_Name],1,1))=Mid([Skilled_Nursing_Visit_Note].[Client_Last_Name],1,1)) AND ((Mid([Patient_Progress_Notes].[Patient_First_Name],1,1))=Mid([Skilled_Nursing_Visit_Note].[Client_First_Name],1,1)) AND ((Patient_Progress_Notes.SNVID_MappedYN)=0 Or (Patient_Progress_Notes.SNVID_MappedYN) Is Null));
And this is something we constantly use.
did you validate that the insert is including proper values for ALL fields that participate in the unique indexes?This is updating a field that is not part of a unique index at all.
Thanks,
Ben
ASKER
I Changed for the following and its working.
Thanks,
Ben
UPDATE Patient_Progress_Notes INNER JOIN Skilled_Nursing_Visit_Note ON (Patient_Progress_Notes.Status = Skilled_Nursing_Visit_Note.Status) AND (Patient_Progress_Notes.Shift_From_Hour = Skilled_Nursing_Visit_Note.Shift_From_Hour) AND (Patient_Progress_Notes.Nurse_User_ID_num_Pn = Skilled_Nursing_Visit_Note.Nurse_User_ID_num_SNV) AND (Patient_Progress_Notes.Visit_Date = Skilled_Nursing_Visit_Note.Visit_Date) SET Patient_Progress_Notes.SNVID = Skilled_Nursing_Visit_Note.snv_id
WHERE (((Patient_Progress_Notes.SNVID) Is Null Or (Patient_Progress_Notes.SNVID)<>[Skilled_Nursing_Visit_Note].[snv_id]) AND ((Mid([Patient_Progress_Notes].[Patient_Last_Name],1,1))=Mid([Skilled_Nursing_Visit_Note].[Client_Last_Name],1,1)) AND ((Mid([Patient_Progress_Notes].[Patient_First_Name],1,1))=Mid([Skilled_Nursing_Visit_Note].[Client_First_Name],1,1)) AND ((Patient_Progress_Notes.SNVID_MappedYN)=0 Or (Patient_Progress_Notes.SNVID_MappedYN) Is Null));
Thanks,
Ben
ASKER
Thank you experts!
Special thanks for pcelba for the great efforts to get this resolved!
Special thanks for pcelba for the great efforts to get this resolved!
You are welcome!
Even when I don't know all relations in your data model I would have a few notes to your update query:
The speed is better because the new join condition reduces the number of related rows to one day but such complex join condition is still the speed killer. Hopefully the updated column Patient_Progress_Notes.SNV ID will allow better joins in the future.
The Mid() function used in WHERE can still result is ambiguity... but the update query seems to be an attempt to clean the data up. The main goal should be to create correct data directly when Patient_Progress_Notes are created.
If the update query is running for a long time then it blocks other queries reading Patient_Progress_Notes data from SQL Server.
Even when I don't know all relations in your data model I would have a few notes to your update query:
The speed is better because the new join condition reduces the number of related rows to one day but such complex join condition is still the speed killer. Hopefully the updated column Patient_Progress_Notes.SNV
The Mid() function used in WHERE can still result is ambiguity... but the update query seems to be an attempt to clean the data up. The main goal should be to create correct data directly when Patient_Progress_Notes are created.
If the update query is running for a long time then it blocks other queries reading Patient_Progress_Notes data from SQL Server.
ASKER
Hi Experts,
When you have a chance please...
https://www.experts-exchange.com/questions/29151420/Urgent-Cannot-insert-new-records-in-SQL-table-2.html#questionAdd
Thanks,
Ben
When you have a chance please...
https://www.experts-exchange.com/questions/29151420/Urgent-Cannot-insert-new-records-in-SQL-table-2.html#questionAdd
Thanks,
Ben
BTW, what timeout is set for the connection to SQL Server?