asked on

Strange error on linked SQL view

Hi Experts,

I am receiving complaints from users from time to time that they are getting an error on a linked SQL view that could not be updated (see attached).

However the strange thing is that when I try doing that same change on my pc it works, moreover after a while (sometimes next day) the error doesn't occur on theirs either...

Any idea how to troubleshoot this?

Below is the SQL of the view in question.

SELECT     dbo.Skilled_Nursing_Visit_Note.ID, dbo.SNV_Printed_History.VendorsID, dbo.SNV_Printed_History.SNV_ID, dbo.Skilled_Nursing_Visit_Note.Client_Last_Name, 
                      dbo.Skilled_Nursing_Visit_Note.Nurse_Signature_Last_Name, dbo.Skilled_Nursing_Visit_Note.Visit_Date, dbo.Skilled_Nursing_Visit_Note.Shift_From_Hour, 
                      dbo.Skilled_Nursing_Visit_Note.Shift_To_Hour, dbo.Skilled_Nursing_Visit_Note.Date_Signed, dbo.Skilled_Nursing_Visit_Note.Nurse_Name_Stamp_SNV, 
                      dbo.Skilled_Nursing_Visit_Note.Nurse_User_ID_num_SNV, dbo.SNV_Printed_History.ReviewedBy, dbo.SNV_Printed_History.ReviewedDate, dbo.SNV_Printed_History.PrintedDate, 
                      dbo.SNV_Printed_History.PrintedBy, dbo.Skilled_Nursing_Visit_Note.Client_First_Name, dbo.Skilled_Nursing_Visit_Note.Nurse_Signature_First_Name, 
                      dbo.Skilled_Nursing_Visit_Note.Date_Of_Birth, dbo.Skilled_Nursing_Visit_Note.Shift_From_Minute, dbo.Skilled_Nursing_Visit_Note.Shift_To_Minute, 
                      dbo.Skilled_Nursing_Visit_Note.Treatments_Administered, dbo.SNV_Printed_History.NoPrint, dbo.Skilled_Nursing_Visit_Note.Status, dbo.Skilled_Nursing_Visit_Note.Client_Last_Name_Init, 
                      dbo.Skilled_Nursing_Visit_Note.Visit_Date_Init, dbo.Skilled_Nursing_Visit_Note.Client_First_Name_Init, dbo.Skilled_Nursing_Visit_Note.Shift_From_Init, 
                      dbo.Skilled_Nursing_Visit_Note.Shift_To_Init, dbo.Skilled_Nursing_Visit_Note.SNVNUM, Q.CountOfNotes, Q.CountOfPN, CASE WHEN Shift_From_Hour IS NULL OR
                      Shift_To_Hour IS NULL THEN 0 WHEN Shift_From_Hour < 0 OR
                      Shift_From_Hour > 24 OR
                      Shift_From_Minute < 0 OR
                      Shift_From_Minute > 59 THEN 0 WHEN Shift_To_Hour < 0 OR
                      Shift_To_Hour > 24 OR
                      Shift_To_Minute < 0 OR
                      Shift_To_Minute > 59 THEN 0 WHEN Shift_From_Hour > Shift_To_Hour THEN ((24 - Shift_From_Hour) + Shift_To_Hour) * 60 + (Shift_To_Minute - Shift_From_Minute) 
                      ELSE (Shift_To_Hour - Shift_From_Hour) * 60 + (Shift_To_Minute - Shift_From_Minute) END AS Duration, CASE WHEN Shift_From_Hour IS NULL OR
                      Shift_To_Hour IS NULL OR
                      Shift_From_Hour < 0 OR
                      Shift_From_Hour > 24 OR
                      Shift_From_Minute < 0 OR
                      Shift_From_Minute > 59 OR
                      Shift_To_Hour < 0 OR
                      Shift_To_Hour > 24 OR
                      Shift_To_Minute < 0 OR
                      Shift_To_Minute > 59 THEN 24 WHEN (Shift_From_Hour = Shift_To_Hour) AND (Shift_From_Minute = Shift_To_Minute) THEN 24 WHEN Shift_From_Hour > Shift_To_Hour THEN (((24 - Shift_From_Hour)
                       + Shift_To_Hour) * 60 + (Shift_To_Minute - Shift_From_Minute)) / 60 ELSE ((Shift_To_Hour - Shift_From_Hour) * 60 + (Shift_To_Minute - Shift_From_Minute)) / 60 END AS Duration2, 
                      CASE WHEN Shift_From_Hour IS NULL OR
                      Shift_To_Hour IS NULL THEN 0 WHEN Shift_From_Hour < 0 OR
                      Shift_From_Hour > 24 OR
                      Shift_From_Minute < 0 OR
                      Shift_From_Minute > 59 THEN 0 WHEN Shift_To_Hour < 0 OR
                      Shift_To_Hour > 24 OR
                      Shift_To_Minute < 0 OR
                      Shift_To_Minute > 59 THEN 0 WHEN Shift_From_Hour > Shift_To_Hour THEN ((24 - Shift_From_Hour) + Shift_To_Hour) * 60 + (Shift_To_Minute - Shift_From_Minute) 
                      ELSE (Shift_To_Hour - Shift_From_Hour) * 60 + (Shift_To_Minute - Shift_From_Minute) END / 120 - Q.CountOfNotes AS MissingNotes, dbo.SNV_Printed_History.ts
FROM         dbo.SNV_Printed_History INNER JOIN
                      dbo.Skilled_Nursing_Visit_Note ON dbo.SNV_Printed_History.SNV_ID = dbo.Skilled_Nursing_Visit_Note.SNV_ID LEFT OUTER JOIN
                      dbo.vw_SNVNotesQry AS Q ON Q.SNVID = dbo.Skilled_Nursing_Visit_Note.SNV_ID

Open in new window

Thanks
Capture.PNG
Capture.PNG

Qlemo

You get two different errors: a timeout and a desired change to more than one table.
Since you use an outer join, there might be a corresponding row missing in the right table, which 8s added somehow later.
The timeout may happen if the remote table(s) have locks applied, the server is busy or similar.

ste5an

1) The problem here is how views are update by SQL Server. This the error in the first message. You cannot update values in two different tables at the same time via view.
Thus, when you really need this update scenario, then you need an INSTEAD OF UDPATE trigger on the view, which does the updates in the tables separately.

2) The timeout is imho caused by the history table. How many rows are involved in each table? Here you may run into the problem, that Access uses per default server-side cursors. Thus, when multiple users are working on the same tables / views, this can lead to locks, cause there are cursors still open.
Here you have two approaches:
a) Check your indices. Make sure that they support this view and you don't have table scans. Appropriate indices include a good clustered index. Here it maybe good to have the visit date as first column in the clustered indices. Depending on the kind of data usage and number of rows involved, maybe a dimensional modelling approach is also useful. Using a date coded into the visit id as first part.
b) Rethink your architecture. In larger applications edit forms should use views which return exactly that row, which needs to be edited. This avoids lock escalation. Basically like in old main frame applications, where you have read-only lists to select the record to edit from.

p.s. you should really use table alias names and qualify all used columns..

bfuchs

ASKER

Hi Experts,

Since you use an outer join, there might be a corresponding row missing in the right table, which 8s added somehow later.

Just tested and had no issues updating in such a scenario.

The timeout may happen if the remote table(s) have locks applied, the server is busy or similar.

So why would that work at the same time in my PC?

Thus, when you really need this update scenario, then you need an INSTEAD OF UDPATE trigger on the view...

Well this is working 99% of the time, so I guess its not something wrong with the design in general.

Thus, when multiple users are working on the same tables / views, this can lead to locks...This avoids lock escalation...

this is an application used by no more than two users at a time, hardly believe its a locking issue.

In larger applications edit forms should use views which return exactly that row, which needs to be edited

This is how I have my application setup.

Check your indices. Make sure that they support this view and you don't have table scans.

How do I check for that?

Thanks,
Ben

Mark Edwards

Are we talking about updating a SQL Server view from the server (passthru query or ADODB Command?), or updating a linked SQL Server view linked to Access (shows in Access table Nav Pane, but is not a SQL Server table) and updated thru Access? There's a difference in how that can be done.

bfuchs

ASKER

Hi,

updating a linked SQL Server view linked to Access (shows in Access table Nav Pane, but is not a SQL Server table) and updated thru Access

This is the case.

Thanks,
Ben

Mark Edwards

It's been my experience that in order for a linked view to be updatable, it has to have a unique index. On every SS view I link, I run a DAO CREATE UNIQUE INDEX, for example:

Dim strSQL as String
strSQL="CREATE UNIQUE INDEX vw_ViewNameHere" _
& " ON vw_ViewNameHere ( Field1 ASC[DESC], Field2 ASC[DESC], etc.....)"
Currentdb.Execute strSQL

Open in new window

You might try it on a view that's not updatable and see if it works. There is something somewhere that is handling the unique index on linked views differently somehow.

Mark Edwards

p.s. Although linked SQL Server views show in the Access Nav Pane in the Tables section, they are treated differently from linked SS tables.

bfuchs

ASKER

in order for a linked view to be updatable, it has to have a unique index

Of course it has already an unique index, otherwise this would never be updatable.
As mentioned, this view is updatable by ALL users most of the time, we are just experiencing this occasionally, and for some users only.

Thanks,
Ben

John Tsioumpris

If you experiencing errors that might be of time out nature you could split the view to handle the criteria..output the data to a temp table and finaly perform the update...all these in SQL

bfuchs

ASKER

Hi,

If you experiencing errors that might be of time out nature...

But that should not be happening here since as mentioned, we dont have a lot of users connected to this database, I would prefer looking for the source of the problem and try to fix it there.
Thanks,
Ben

This question needs an answer!

Become an EE member today

7 DAY FREE TRIAL

Members can start a 7-Day Free trial then enjoy unlimited access to the platform.

View membership options

Learn why we charge membership fees

We get it - no one likes a content blocker. Take one extra minute and find out why we block content.