2

We've created a view in 2008 R2. Provided below are the definitions for both the base table and the view.

Base table:

CREATE TABLE dbo.test(
    fcrecid char(25) NOT NULL,
    fctype char(1) NOT NULL,
    flbomcntrl bit NOT NULL,
    flmstcntrl bit NOT NULL,
    flrtgcntrl bit NOT NULL,
    fcjobno varchar(20) NOT NULL,
    fcsono varchar(10) NOT NULL,
    identity_column int IDENTITY(1,1) NOT NULL,
    timestamp_column timestamp NULL,
) 

View Definition:

CREATE VIEW dbo.testview
AS
  SELECT fcrecid,
         fctype,
         flbomcntrl,
         flmstcntrl,
         flrtgcntrl,
         fcjobno,
         fcsono,
         'Parent' AS parentid
  FROM   dbo.test 

We've migrated the database from 2008 R2 to 2016 and attempted to perform an insertion into the view using the following insert query and we can able to insert without any problem.

Insert Query

INSERT INTO dbo.testview (fcrecid,fctype,flbomcntrl,flmstcntrl,flrtgcntrl,fcjobno,fcsono,parentid) values ('','A',0,0,0,'','000066','Parent')

We dropped the Testview, created a view with same view definition and attempted the same insert query, resulting in the below error.

Msg 4406, Level 16, State 1, Line 1 Update or insert of view or function 'testview' failed because it contains a derived or constant field.

If we opt to alter the view instead of dropping and recreating it, we can avoid encountering the error mentioned earlier

I understand that we're attempting to insert values into the 'parentid' column, which is a constant field and not supported in SQL Server 2016.

How does Insert is working without drop of view (just migrate from 2008 R2 to 2016) or simply altering a view without drop and create a view?

Can the behavior of the insert query in 2016 be replicated to mimic that of 2008 R2 by dropping and creating a view?

I'm aware that many suggestions advise using an 'Instead of' trigger to update the constant field within it or just remove constant field in query. However, we don't want to take that route.

Thanks.

10
  • Does it help running in 2008 compatibility mode? However, we don't want to take that route Why not? Feb 13 at 8:47
  • 2
    The solution is remove parentid from your INSERT. It doesn't make any sense to be there anyway.
    – Thom A
    Feb 13 at 9:34
  • @siggemannen Even though we have 2016 it's compatibility mode set to 2008 (100)
    – Yashas
    Feb 13 at 11:23
  • 1
    You should be able to script a bunch of INSTEAD OF triggers based on sys.views and sys.dm_exec_describe_first_result_set to sniff out the constants and remove them from list of inserted values Feb 13 at 11:34
  • 4
    That is, in truth, part of what you should be doing during your testing during your upgrade process, @Yashas . So get the INSERT statements updated in your Dev/UAT environments now, in readiness for when you go live in your production environment. 2008R2 has been EoL for over 4 years now, so you've had plenty of time to test all this and get ready, if I am honest.
    – Thom A
    Feb 13 at 11:35

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.