2006/09/05

Identity columns in a view treated as an ordinary int column

I was experiencing the behavior shown in KB920974: FIX: SQL Server 2005 treats an identity column in a view as an ordinary int column when the compatibility level of the database is set to 80 and asked Microsoft support for the hotfix opening a new support incident. After some hours, technical staff sent me the links for the files and installed them on a virtualised environment (testing is always a good thing).

After having checked that the problem was solved if the database compatibility level was 80, I changed it back to 90 and the problem appeared again. I did not accept the ms incident as being resolved and still complained about it. They told me that the shown behaviour was BY DESIGN. In other words, if you are using a database with compatibility level 80, your identity fields will still behave as identity when viewed through a view (check the following example in how to reproduce the problem), but if you want to use compatibility level 90, the identity behavior is lost by design if the table is viewed through a view. I could not believe it! Why did they do that?

As stated in SQL Server 2005 BOL regarding Modifying Data Through a View:

You can modify the data of an underlying base table through a view, in the same manner as you modify data in a table by using UPDATE, INSERT and DELETE statements or by using the bcp utility and BULK INSERT statement. However, the following restrictions apply to updating views, but do not apply to tables:
  • Any modifications, including UPDATE, INSERT, and DELETE statements, must reference columns from only one base table.
  • The columns that are being modified in the view must reference the underlying data in the table columns directly. They cannot be derived in any other way, such as through:
    • An aggregate function (AVG, COUNT, SUM, MIN, MAX, GROUPING, STDEV, STDEVP, VAR and VARP).
    • A computation; the column cannot be computed from an expression using other columns. Columns formed using set operators (UNION, UNION ALL, CROSSJOIN, EXCEPT, and INTERSECT) amount to a computation and are also not updatable.
  • The columns that are being modified cannot be affected by GROUP BY, HAVING, or DISTINCT clauses.
  • TOP cannot be used anywhere in the select_statement of the view when WITH CHECK OPTION is also specified.
[...] The following additional guidelines apply:[...]
  • INSERT statements must specify values for any columns in the underlying table that do not allow null values and have no DEFAULT definitions.

Steps to reproduce the problem: Create a new [test] database in compatibility mode 90 (2005). Define the following two tables: TMaterials and TMeasureUnits with the script:

CREATE TABLE [dbo].[TMeasureUnits](
 [MeasureUnitId] [int] NOT NULL,
 [Description] [varchar](50) COLLATE Modern_Spanish_CI_AS NOT NULL,
 CONSTRAINT [PK_TMeasureUnits] PRIMARY KEY CLUSTERED 
(
 [MeasureUnitId] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[TMaterials](
 [MaterialId] [int] IDENTITY(1,1) NOT NULL,
 [Description] [varchar](50) COLLATE Modern_Spanish_CI_AS NULL,
 [MeasureUnitId] [int] NOT NULL,
 CONSTRAINT [PK_TMaterials] PRIMARY KEY CLUSTERED 
(
 [MaterialId] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[TMaterials] WITH CHECK
  ADD CONSTRAINT [FK_TMaterials_TMeasureUnits] FOREIGN KEY([MeasureUnitId])
  REFERENCES [dbo].[TMeasureUnits] ([MeasureUnitId])
GO
ALTER TABLE [dbo].[TMaterials] CHECK CONSTRAINT [FK_TMaterials_TMeasureUnits]
GO
INSERT INTO [dbo].[TMeasureUnits] (MeasureUnitId, Description) VALUES (1, 'Units')
INSERT INTO [dbo].[TMeasureUnits] (MeasureUnitId, Description) VALUES (2, 'Boxes')
INSERT INTO [dbo].[TMeasureUnits] (MeasureUnitId, Description) VALUES (3, 'm.')
INSERT INTO [dbo].[TMeasureUnits] (MeasureUnitId, Description) VALUES (4, 'Kg.')

Now we will create the offending view:

CREATE VIEW [dbo].[VMaterials]
AS
SELECT dbo.TMaterials.MaterialId,
       dbo.TMaterials.Description,
       dbo.TMaterials.MeasureUnitId,
       dbo.TMeasureUnits.Description AS MeasureUnit
FROM dbo.TMaterials INNER JOIN
     dbo.TMeasureUnits ON dbo.TMaterials.MeasureUnitId = dbo.TMeasureUnits.MeasureUnitId
GO

Now execute:

SELECT name, is_identity
FROM sys.columns
WHERE object_id = object_id('VMaterials')

And you will see that NONE of the fields have is_identity = 1. This behavior is told to be BY DESIGN if database compatibility level 90. The fix is only intended to restore original behavior when database is in level 80 (2000).

The real problem: Despite this different behavior, the view is still insertable using T-SQL. Our real problem then, is due to MS Access.

We are using MS Access 2003 as front-end for our SQL Server 2005 corporate database. With such a new behavior, MS Access 2003 is not able to do INSERTs in such views. When MS Access asks SQL Server for view's metadata (to know field types, lengths, primary keys, nullable fields, and identities), SQL incorrectly returns that the identity field is not an identity field anymore:

  1. Create a new MS Access 2003 database project and connect to your server and database.
  2. Change to see Queries objects and open VMaterials (note that the expected first field should be marked as (Autonumeric) and it is not).
  3. Try to insert a new record:
    • If you try to leave MaterialId blank, and enter Description='Sand', MeasureId=2, you will get the error: Invalid input parameter values. Check the status values for details.Invalid input parameter values. Check the status values for details.
    • If you try to give any value to MaterialId and Description='Sand', MeasureId=2 as before, you get the following error message: Cannot insert explicit value for identity column in table 'TMaterials' when IDENTITY_INSERT is set to OFFCannot insert explicit value for identity column in table 'TMaterials' when IDENTITY_INSERT is set to OFF

Up to now, our incident is still opened and no solution nor workaround has been given yet (apart from the obvious fall back to compatibility level 80).

9 comments:

Anonymous said...

We have the compatibility level of our database set to 80 and with Access 2003 we have the same issue.

Phil Ritchie said...

I have found that if someone has owner rights to the view/table it works as expected even if the compatibility level is 80.

J.A. García Barceló said...

To Phil:
Exactly that is the point:
* When database is in level 80, there is no new behaviour. You just need to have been granted VIEW DEFINITION permissions to know about identity columns.
* The problems arise when database is in level 90: If you try to do inserts through a view, there is no way (for MS Access) to know which columns are identities (whatever rights you have).

Anonymous said...

I think I'm having this problem.. Does Access 2007 resolve this issue?

J.A. García Barceló said...

Sorry but this behaviour is shown by SQL Server 2005. It does not matter whether you connect using Access 2003 or 2007. The identity columns in views are not there anymore if database compatibility level is 90.

Anonymous said...

Using a stored procedure instead of a view or UDF seems to work around this problem.

Anonymous said...

Any updates to this issue? Ever since I installed SP3 for Office 2003 I've had additional issues with "Invalid input parameter values. Check the status values for detail."

It so crazy, if I import the forms from an older version of our Access application it works great, but as soon as you open the form in design mode make one singly insignificant change you will get "Invalid input parameter values. Check the status values for detail" when you try to insert a record.

Anonymous said...

Deos anyone know if SQL 2008 resolves this problem of the identity field?

Gary Herbstman
http://bytesolutions.com

Mackan said...

Old post, but a current issue for me. I'm migrating SQL from 2000 to 2012, and the Access frontend from 2003 to 2010. This issue is present and I can't find a workaround (and compatibility level 80 is not available in sql 2012). Is there a way to force SQL server to see a column as identity? Any other ideas on how to resolve?