Page based on a SQL View crashes after user customizes columns (changes sequence)

rsaritzky

Hi,

I have a table defined from a SQL View (LinkedTable). I have a page that displays the view. The repeater area is non-editable and the page properties have InsertAllowed=No,ModifyAllowed=No and DeleteAllowed=No.

A user tried to customize the page and relocate one of the columns, moving it "up" in the list. After that, this page has been corrupted for all users. Here is the message that is displayed any time any user tries to run the page:

The following SQL error was unexpected:
View or function 'TESTCompany$v_InventoryByLocation View' is not updatable because the modification affects multiple base tables.
Statement(s) could not be prepared.

I checked the Windows event log and it had some information that doesn't make sense:

View or function 'TEST2016.TESTCompany$v_InventoryByLocation View' is not updatable because the modification affects multiple base tables.
Statement(s) could not be prepared.
SQL statement:
INSERT INTO "TEST2016".dbo."TESTCompany$v_InventoryByLocation View" ("Entry No_","weEntryNo","Item No_","Variant Code","Description","Location Code","Lot No_","Serial No_","In Quarantine","Donor Type","Donor No_","Donation Date","Vial Status","Quantity","Release No_","Released Date","Open","Storage Contract No_","Bin Code","Bin Description","Bin Description (Short)","Egg Quantity","Product Code") VALUES (@0,@1,@2,@3,@4,@5,@6,@7,@8,@9,@10,@11,@12,@13,@14,@15,@16,@17,@18,@19,@20,@21,@DBTS

@DBTS field is the Timestamp field that gets added by NAV to all tables. But if that's what's happening, this "temporary" area isn't the view itself.

We have the same page in NAV2009. I get a similar error:


The following SQL Server error or errors occurred when accessing the v_InventoryByLocation View table: 4405,"42000",[Microsoft][ODBC SQL Server Driver][SQL Server]View or function 'Development2.dbo.TESTCompany$v_InventoryByLocation View' is not updatable because the modification affects multiple base tables.
8180,"42000",[Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be prepared.

SQL:
INSERT INTO "Development2"."dbo"."TESTCompany$v_InventoryByLocation View" WITH (REPEATABLEREAD, ROWLOCK) ("Entry No_","weEntryNo","Item No_","Variant Code","Description","Location Code","Lot No_","Serial No_","In Quarantine","Donor Type","Donor No_","Donation Date","Vial Status","Quantity","Release No_","Released Date","Open","Storage Contract No_","Bin Code","Bin Description","Bin Description (Short)") VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)

Does anyone know if this is a known issue - that you cannot customize a page based on a LinkedView table?

Ron

Slawek_Guzek

It looks like a client bug.

Check if you have a filter on any Primary Key field. It should not matter as you already set the InsertAllowed=No, but who knows... Also try setting the whole page to non editable.

Slawek

rsaritzky

Thanks for the suggestion. I can't change the entire page to non-editable because there are a number of fields that the user can enter to set filters on the list (a "user-friendly" way of entering filters). The

Slawek_Guzek

If you don't mind to workaround the problem at the SQL level you can define an InsteadOF trigger on your view. (CREATE TRIGGER dummyname ON yourView INSTEAD OF INSERT,UPDATE,DELETE AS RETURN )

The trigger like this would make the view writable, doing nothing to the underlying data

rsaritzky

Interesting idea - I'll give it a try!

rsaritzky

Turns out you can't do what you suggest. Apparently, NAV fills an empty "temporary" table of some sort, so an insert error occurs when the trigger is defined.

Slawek_Guzek

rsaritzky wrote: »

..an insert error occurs when the trigger is defined.

Can you post the error here?