Link to home
Start Free TrialLog in
Avatar of bfuchs
bfuchsFlag for United States of America

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
Avatar of Pavel Celba
Pavel Celba
Flag of Czechia image

First of all check the PK on SQL Server, then make sure Access can insert rows into another table (create one for this purpose), then remove the linked table from Access, link it again and test again.

BTW, what timeout is set for the connection to SQL Server?
Avatar of bfuchs

ASKER

@pcelba,
The attached error is from trying to enter it using SSMS, nothing to do with Access.
First of all check the PK on SQL Server
The 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.
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
Avatar of Pavel Celba
Pavel Celba
Flag of Czechia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of bfuchs

ASKER

@pcelba,
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?
Avatar of bfuchs

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
The name of the table has underscores correct?  I don't see any in the above command
Avatar of bfuchs

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_Notes". Check the system catalog.
also tried dbo.Skilled_Nursing_Visit_Notes.
Thanks,
Ben
Avatar of bfuchs

ASKER

Executing this takes forever...
insert into Skilled_Nursing_Visit_Note (snv_id) values ('001122')
Thanks,
Ben
Avatar of bfuchs

ASKER

Doing this now, lets hope this solves the problem...
DBCC DBREINDEX (Skilled_Nursing_Visit_Note)
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...
Avatar of bfuchs

ASKER

@pcelba,
The DBCC DBREINDEX (Skilled_Nursing_Visit_Note) is taking very long.
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.
Avatar of bfuchs

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
Avatar of bfuchs

ASKER

under sp_who2 I see many things and two SUSPENDED            
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.
Avatar of bfuchs

ASKER

whats about re-index command, should leave that going?

Thanks,
Ben
No, cancel it.
How many rows is in the table?
Avatar of bfuchs

ASKER

One Suspended ID is appearing at the other suspended BlkBy, is that OK?

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.
Avatar of bfuchs

ASKER

@Ron,
The problem is happening in SQL as well.
There were no recent changes to that table design.
Thanks,
Ben
Avatar of bfuchs

ASKER

@pcelba,

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.
Avatar of bfuchs

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
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.
Avatar of bfuchs

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
Avatar of bfuchs

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
Avatar of bfuchs

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 googled it and here's an example:
DBCC CHECKTABLE ('HumanResources.Employee');

Open in new window


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.
Avatar of bfuchs

ASKER

@Mark,
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.
Avatar of bfuchs

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 is a table.
Try the insert in Access and during the time it waits issue sp_who2 in SSMS.
Will try that now.
Thanks,
Ben
Avatar of bfuchs

ASKER

See attached what I meant regarding wrong PK field.
Thanks,
Ben
Untitled.png
Avatar of bfuchs

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
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.
Avatar of bfuchs

ASKER

Yes, the Access linked table design seemed wrongly...
What is the cause of it, and how it can be fixed?

Thanks,
Ben
Avatar of bfuchs

ASKER

My Access append/update queries are still not working.

Thanks,
Ben
Avatar of bfuchs

ASKER

While Access queries are freezing I see two suspended tasks under  sp_who2, however BlkBy is null.

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.
Avatar of bfuchs

ASKER

Maybe the table structure changed on SQL Server
Yes 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

Open in new window

Then you may analyze/post the TEXT and query_plan
Avatar of bfuchs

ASKER

In Activity monitor I see them executing the following

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 ) ) 

Open in new window

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)

Open in new window


Thanks,
Ben
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of bfuchs

ASKER

See attached execution plans.

Thanks,
Ben
Untitled.png
Untitled1.png
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.
Avatar of bfuchs

ASKER

@Pat,
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
Avatar of bfuchs

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
Avatar of bfuchs

ASKER

The below keeps switching from suspended to running, while the select task stays at suspended all the time.
(@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)

Open in new window

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?
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?
Avatar of bfuchs

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));

Open in new window


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
Avatar of bfuchs

ASKER

I Changed for the following and its working.

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));

Open in new window


Thanks,
Ben
Avatar of bfuchs

ASKER

Thank you experts!

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.SNVID 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.