SQL Server Error Message: What and Why "This Error"

Download as docx, pdf, or txt
Download as docx, pdf, or txt
You are on page 1of 30

#1

Msg 1766, Level 16, State 0, Line 3 Foreign key references to temporary tables are
not supported. Foreign key '%.*ls'.
SQL Server Error Message:

Foreign key references to temporary tables are not supported. Foreign key '%.*ls'.
What and Why "this Error":

This error occurs when you try to create a foreign key constraint referencing a
temporary table. For example: the following statement will raise the above error.

USE tempdb
CREATE TABLE #TEST
(
COL1 INT NOT NULL PRIMARY KEY
)
GO
CREATE TABLE TEST2
(
COL1 INT NOT NULL PRIMARY KEY CONSTRAINT FRNKY FOREIGN KEY REFERENCES
#TEST(COL1)
)
GO
DROP TABLE TEST2
DROP TABLE #TEST
GO

Action Required to resolve this Error:

The solution to this error is you cannot create a foreign key constraint referencing a
temporary table. In the above example we try to create a foreign key constraint
referencing the temporary table #TEST. This raises the error. By removing # from
the table #TEST, the above statement will execute successfully.

USE tempdb
CREATE TABLE TEST
(
COL1 INT NOT NULL PRIMARY KEY
)
GO
CREATE TABLE TEST2
(
COL1 INT NOT NULL PRIMARY KEY CONSTRAINT FRNKY FOREIGN KEY REFERENCES
TEST(COL1)
)
GO
DROP TABLE TEST2
DROP TABLE TEST
GO

#2
Msg 1771, Level 16, State 1, Line 12 Cannot create foreign key '%.*ls' because it
references object '%.*ls' whose clustered index '%.*ls' is disabled.

SQL Server Error Message:

Cannot create foreign key '%.*ls' because it references object '%.*ls' whose
clustered index '%.*ls' is disabled.

What and Why "this Error":

This error occurs when you try to create a foreign key constraint referencing a table
whose clustered index is disabled. For example: the following statement will raise
the above error.

USE tempdb
CREATE TABLE TEST
(
COL1 INT NOT NULL CONSTRAINT CNSTRNTINDX PRIMARY KEY
)
GO
ALTER INDEX CNSTRNTINDX ON TEST DISABLE
GO
CREATE TABLE TEST2
(
COL1 INT NOT NULL PRIMARY KEY CONSTRAINT FRNKY FOREIGN KEY REFERENCES
TEST(COL1)
)
GO
DROP TABLE TEST2
DROP TABLE TEST
GO

Action Required to resolve this Error:

The solution to this error is the clustered index must be enabled before the
constraint can be created. In the above example we try to create a foreign key
constraint referencing TEST. Because the clustered index of TEST is disabled, the
error is raised.
USE tempdb
CREATE TABLE TEST
(
COL1 INT NOT NULL CONSTRAINT CNSTRNTINDX PRIMARY KEY
)
GO
ALTER INDEX CNSTRNTINDX ON TEST DISABLE
GO
ALTER INDEX CNSTRNTINDX ON TEST REBUILD
GO
CREATE TABLE TEST2
(
COL1 INT NOT NULL PRIMARY KEY CONSTRAINT FRNKY FOREIGN KEY REFERENCES
TEST(COL1)
)
GO
DROP TABLE TEST2
DROP TABLE TEST
GO

#3
Msg 1784, Level 16, State 1, Line 9 Cannot create the foreign key '%.*ls' because
the referenced column '%.*ls.%.*ls' is a non-persisted computed column.

SQL Server Error Message:

Cannot create the foreign key '%.*ls' because the referenced column '%.*ls.%.*ls'
is a non-persisted computed column.

What and Why "this Error":

This error occurs when you try to create a foreign key constraint referencing a non-
persisted computed column. For example: the following statement will raise the
above error.

USE tempdb
CREATE TABLE TEST
(
COL1 INT NOT NULL PRIMARY KEY,
COL2 AS COL1 * 5 CONSTRAINT UNIQ UNIQUE
)
GO
CREATE TABLE TEST2
(
COL1 INT NOT NULL PRIMARY KEY CONSTRAINT FRNKY FOREIGN KEY REFERENCES
TEST(COL2)
)
GO
DROP TABLE TEST2
DROP TABLE TEST
GO

Action Required to resolve this Error:

The solution to this error is you cannot create a foreign key constraint referencing a
non-persisted computed column. In the above example we try to create a foreign
key constraint referencing a non-persisted computed column. This raises the error.

#4
Msg 1946, Level 16, State 3, Line 10 Operation failed. The index entry of length %d
bytes for the index '%.*ls' exceeds the maximum length of %d bytes.

SQL Server Error Message:

Operation failed. The index entry of length %d bytes for the index '%.*ls' exceeds
the maximum length of %d bytes.

What and Why "this Error":

This error occurs when you try to insert or update a row and an index entry
exceeds the maximum allowed length of 900 bytes. For example: the following
statement will raise the above error.

USE tempdb
CREATE TABLE TEST
(
COL1 VARCHAR(901) PRIMARY KEY
)
GO
INSERT INTO TEST SELECT REPLICATE ('X',900)
GO
SELECT * FROM TEST
UPDATE TEST SET COL1=COL1+'Y'
GO
DROP TABLE TEST
GO
Action Required to resolve this Error:

The solution to this error is the maximum allowed length for index entries is 900
bytes. In the above example we first create the table TEST. During creation you get
the following warning error message.

The following INSERT statement runs error-free, but the UPDATE statement
exceeds the maximum length of 900 bytes and raises the error. By reducing the
UPDATE statement length, the above statement will execute successfully.

USE tempdb
CREATE TABLE TEST
(
COL1 VARCHAR(900) PRIMARY KEY
)
GO
INSERT INTO TEST SELECT REPLICATE ('X',899)
GO
SELECT * FROM TEST
UPDATE TEST SET COL1=COL1+'Y'
GO
DROP TABLE TEST
GO

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

SQL Server Error Message:

Update or insert of view or function '%.*ls' failed because it contains a derived or


constant field.

What and Why "this Error":

This error occurs when you try to insert or update through a view or a function that
contains a derived or constant field. For example: the following statement will raise
the above error.
USE tempdb
CREATE TABLE TEST
(
COL1 INT
)
GO
CREATE VIEW TESTVIEW
AS
SELECT * FROM TEST
UNION ALL SELECT 1
GO
UPDATE TESTVIEW SET COL1=10
GO
DROP TABLE TEST
GO
DROP VIEW TESTVIEW
GO

Action Required to resolve this Error:

The solution to this error is you cannot insert or update a view or a function that
contains a derived or constant field. In the above example we try to update the
view TESTVIEW. Because this view contains the constant expression 1, the error is
raised. By removing the constant expression statement that is the UNIO ALL
statement, the above statement will execute successfully.

USE tempdb
CREATE TABLE TEST
(
COL1 INT
)
GO
CREATE VIEW TESTVIEW
AS
SELECT * FROM TEST
GO
UPDATE TESTVIEW SET COL1=10
GO
DROP TABLE TEST
GO
DROP VIEW TESTVIEW
GO

#6
Msg 4442, Level 16, State 7, Line 1 UNION ALL view '%.*ls' is not updatable
because base table '%.*ls' is used multiple times.
SQL Server Error Message:

UNION ALL view '%.*ls' is not updatable because base table '%.*ls' is used multiple
times.

What and Why "this Error":

This error occurs when you try to update a UNION ALL view but at least one
underlying base table is referenced more than once in the view. For example: the
following statement will raise the above error.

USE tempdb
CREATE TABLE TEST
(
COL1 INT
)
GO
CREATE VIEW TESTVIEW
AS
SELECT COL1 FROM TEST
UNION ALL SELECT COL1 FROM TEST
GO
UPDATE TESTVIEW SET COL1=10
GO
DROP TABLE TEST
DROP VIEW TESTVIEW
GO

Action Required to resolve this Error:

The solution to this error each base table in a UNION ALL view can be referenced
just once. In the above example we try to update the view TESTVIEW. Because the
underlying base table TEST is referenced twice, the error is raised. By removing the
extra table TEST reference from CREATE VIEW statement, the above statement will
execute successfully.

USE tempdb
CREATE TABLE TEST
(
COL1 INT
)
GO
CREATE VIEW TESTVIEW
AS
SELECT COL1 FROM TEST
GO
UPDATE TESTVIEW SET COL1=10
GO
DROP TABLE TEST
DROP VIEW TESTVIEW
GO

#7
Msg 4436, Level 16, State 12, Line 1 UNION ALL view '%.*ls' is not updatable
because a partitioning column was not found.

SQL Server Error Message:

UNION ALL view '%.*ls' is not updatable because a partitioning column was not
found.

What and Why "this Error":

This error occurs when you try to update a UNION ALL view but no partitioning
column was found. For example: the following statement will raise the above error.

USE tempdb
CREATE TABLE TEST
(
COL1 INT PRIMARY KEY
)
GO
CREATE TABLE TEST2
(
COL1 INT PRIMARY KEY
)
GO
CREATE VIEW TESTVIEW
AS
SELECT COL1 FROM TEST
UNION ALL SELECT COL1 FROM TEST2
GO
UPDATE TESTVIEW SET COL1=1;
GO
DROP TABLE TEST
DROP TABLE TEST2
GO

Action Required to resolve this Error:

The solution to this error is you cannot update a UNION ALL view that does not
contain a partitioning column, so you have to create partitioning columns to
execute the above statement. In the above example we try to update the view
TESTVIEW. Because this UNION ALL view contains no partitioning column, the error
is raised. The following statement will execute successfully because I have created
the partitioning columns.

USE tempdb
CREATE TABLE TEST
(
COL1 INT PRIMARY KEY CHECK(COL1 BETWEEN 1 AND 10)
)
GO
CREATE TABLE TEST2
(
COL1 INT PRIMARY KEY CHECK(COL1 BETWEEN 11 AND 20)
)
GO
CREATE VIEW TESTVIEW
AS
SELECT COL1 FROM TEST
UNION ALL SELECT COL1 FROM TEST2
GO
UPDATE TESTVIEW SET COL1=1;
GO
DROP TABLE TEST
DROP TABLE TEST2

DROP VIEW TESTVIEW


GO

#8
Msg 4440, Level 16, State 9, Line 1 UNION ALL view '%.*ls' is not updatable
because a primary key was not found on table '%.*ls'.

SQL Server Error Message:

UNION ALL view '%.*ls' is not updatable because a primary key was not found on
table '%.*ls'.

What and Why "this Error":

This error occurs when you try to update a UNION ALL view but at least one
underlying base table does not have a PRIMARY KEY constraint. For example: the
following statement will raise the above error.

USE tempdb
CREATE TABLE TEST
(
COL1 INT CHECK(COL1 BETWEEN 1 AND 10)
)
GO
CREATE TABLE TEST2
(
COL1 INT CHECK(COL1 BETWEEN 11 AND 20)
)
GO
CREATE VIEW TESTVIEW
AS
SELECT COL1 FROM TEST
UNION ALL SELECT COL1 FROM TEST2
GO
UPDATE TESTVIEW SET COL1=1;
GO
DROP TABLE TEST
DROP TABLE TEST2

DROP VIEW TESTVIEW


GO

Action Required to resolve this Error:

The solution to this error is the base tables in a UNION ALL view must have
PRIMARY KEY constraints. In the above example we try to update the view
TESTVIEW because the underlying base tables TEST & TEST2 have no PRIMARY KEY
constraints defined, the error is raised. By defining the PRIMARY KEY for both the
columns in both the tables, the above statement will execute successfully.

USE tempdb
CREATE TABLE TEST
(
COL1 INT PRIMARY KEY CHECK(COL1 BETWEEN 1 AND 10)
)
GO
CREATE TABLE TEST2
(
COL1 INT PRIMARY KEY CHECK(COL1 BETWEEN 11 AND 20)
)
GO
CREATE VIEW TESTVIEW
AS
SELECT COL1 FROM TEST
UNION ALL SELECT COL1 FROM TEST2
GO
UPDATE TESTVIEW SET COL1=1;
GO
DROP TABLE TEST
DROP TABLE TEST2

DROP VIEW TESTVIEW


GO
#9
Msg 4444, Level 16, State 10, Line 1 UNION ALL view '%.*ls' is not updatable
because the primary key of table '%.*ls' is not included in the union result.

SQL Server Error Message:

UNION ALL view '%.*ls' is not updatable because the primary key of table '%.*ls' is
not included in the union result.

What and Why "this Error":

This error occurs when you try to update a UNION ALL view but the primary key of
at least one underlying base table is not contained in the union result. For example:
the following statement will raise the above error.

USE tempdb
CREATE TABLE TEST
(
COL1 INT PRIMARY KEY CHECK(COL1 BETWEEN 1 AND 10),
COL2 INT
)
GO
CREATE TABLE TEST2
(
COL1 INT PRIMARY KEY CHECK(COL1 BETWEEN 11 AND 20)
)
GO
CREATE VIEW TESTVIEW
AS
SELECT COL2 AS COL1 FROM TEST
UNION ALL SELECT COL1 FROM TEST2
GO
UPDATE TESTVIEW SET COL1=1;
GO
DROP TABLE TEST
DROP TABLE TEST2

DROP VIEW TESTVIEW


GO

Action Required to resolve this Error:

The solution to this error is the primary key of each underlying base table must be
contained in the union result. In the above example we try to update the view
TESTVIEW. Because the primary key of the underlying base table TEST is not
contained in the union result, the error is raised. The following statement will
execute successfully because I have used the PRIMARY KEY column in the UPDATE
statement of the above statement.

USE tempdb
CREATE TABLE TEST
(
COL1 INT PRIMARY KEY CHECK(COL1 BETWEEN 1 AND 10)
)
GO
CREATE TABLE TEST2
(
COL1 INT PRIMARY KEY CHECK(COL1 BETWEEN 11 AND 20)
)
GO
CREATE VIEW TESTVIEW
AS
SELECT COL1 FROM TEST
UNION ALL SELECT COL1 FROM TEST2
GO
UPDATE TESTVIEW SET COL1=1;
GO
DROP TABLE TEST
DROP TABLE TEST2
DROP VIEW TESTVIEW
GO

#10
Msg 4443, Level 16, State 8, Line 1 UNION ALL view '%.*ls' is not updatable
because column '%.*ls' of base table '%.*ls' is used multiple times.

SQL Server Error Message:

UNION ALL view '%.*ls' is not updatable because column '%.*ls' of base table
'%.*ls' is used multiple times.

What and Why "this Error":

This error occurs when you try to update a UNION ALL view but at least one column
of an underlying base table is referenced more than once. For example: the
following statement will raise the above error.

USE tempdb
CREATE TABLE TEST
(
COL1 INT PRIMARY KEY CHECK(COL1 BETWEEN 1 AND 10)
)
GO
CREATE TABLE TEST2
(
COL1 INT PRIMARY KEY CHECK(COL1 BETWEEN 11 AND 20)
)
GO
CREATE VIEW TESTVIEW
AS
SELECT COL1,COL1 AS TWICE FROM TEST
UNION ALL SELECT COL1,COL1 FROM TEST2
GO
UPDATE TESTVIEW SET COL1=1;
GO
DROP TABLE TEST
DROP TABLE TEST2
DROP VIEW TESTVIEW
GO

Action Required to resolve this Error:

The solution to this error is each column of each underlying base table can be
referenced once. In the above example we try to update the view TESTVIEW.
Because the column COL1 of the underlying base table TEST is referenced twice,
the error is raised. The following statement will execute successfully because I have
removed the repeated columns from the VIEW & UPDATE commands.

USE tempdb
CREATE TABLE TEST
(
COL1 INT PRIMARY KEY CHECK(COL1 BETWEEN 1 AND 10)
)
GO
CREATE TABLE TEST2
(
COL1 INT PRIMARY KEY CHECK(COL1 BETWEEN 11 AND 20)
)
GO
CREATE VIEW TESTVIEW
AS
SELECT COL1 FROM TEST
UNION ALL SELECT COL1 FROM TEST2
GO
UPDATE TESTVIEW SET COL1=1;
GO
DROP TABLE TEST
DROP TABLE TEST2
DROP VIEW TESTVIEW
GO
#11
Msg 4431, Level 16, State 1, Line 2 Partitioned view '%.*ls' is not updatable
because table '%.*ls' has a timestamp column.

SQL Server Error Message:

Partitioned view '%.*ls' is not updatable because table '%.*ls' has a timestamp
column.

What and Why "this Error":

This error occurs when you try to update a UNION ALL view but at least one of the
underlying base tables has a column of the timestamp data type. For example: the
following statement will raise the above error.

USE tempdb
CREATE TABLE TEST
(
COL1 INT PRIMARY KEY CHECK(COL1 BETWEEN 1 AND 10),
COL2 TIMESTAMP
)
GO
CREATE TABLE TEST2
(
COL1 INT PRIMARY KEY CHECK(COL1 BETWEEN 11 AND 20),
COL2 TIMESTAMP
)
GO
CREATE VIEW TESTVIEW
AS
SELECT COL1,COL2 FROM TEST
UNION ALL SELECT COL1,COL2 FROM TEST2
GO
UPDATE TESTVIEW SET COL1=1;
GO
DROP TABLE TEST
DROP TABLE TEST2
DROP VIEW TESTVIEW
GO

Action Required to resolve this Error:


The solution to this error is you cannot update a UNION ALL view with a timestamp
column in an underlying base table. In the above example we try to update the
view TESTVIEW. Because TEST and TEST2 have timestamp columns, the error is
raised. So as you cannot use the TIMESTAMP datatype in the above statement & If
you want to execute it, you have to remove the TIMSTAMP columns from the above
statement as shown below in the following statement.

USE tempdb
CREATE TABLE TEST
(
COL1 INT PRIMARY KEY CHECK(COL1 BETWEEN 1 AND 10)
)
GO
CREATE TABLE TEST2
(
COL1 INT PRIMARY KEY CHECK(COL1 BETWEEN 11 AND 20)
)
GO
CREATE VIEW TESTVIEW
AS
SELECT COL1 FROM TEST
UNION ALL SELECT COL1 FROM TEST2
GO
UPDATE TESTVIEW SET COL1=1;
GO
DROP TABLE TEST
DROP TABLE TEST2
DROP VIEW TESTVIEW
GO

#12
Msg 4450, Level 16, State 1, Line 2 Cannot update partitioned view '%.*ls' because
the definition of the view column '%.*ls' in table '%.*ls' has a IDENTITY constraint.

SQL Server Error Message:

Cannot update partitioned view '%.*ls' because the definition of the view column
'%.*ls' in table '%.*ls' has a IDENTITY constraint.

What and Why "this Error":

This error occurs when you try to update a UNION ALL view but at least one of the
underlying base tables has an IDENTITY constraint defined. For example: the
following statement will raise the above error.

USE tempdb
CREATE TABLE TEST
(
COL1 INT PRIMARY KEY CHECK(COL1 BETWEEN 1 AND 10),
COL2 INT IDENTITY
)
GO
CREATE TABLE TEST2
(
COL1 INT PRIMARY KEY CHECK(COL1 BETWEEN 11 AND 20),
COL2 INT IDENTITY
)
GO
CREATE VIEW TESTVIEW
AS
SELECT COL1,COL2 FROM TEST
UNION ALL SELECT COL1,COL2 FROM TEST2
GO
UPDATE TESTVIEW SET COL1=1;
GO
DROP TABLE TEST
DROP TABLE TEST2
DROP VIEW TESTVIEW
GO

Action Required to resolve this Error:

The solution to this error is you cannot update a UNION ALL view with a IDENTITY
constraint in an underlying base table. In the above example we try to update the
view TESTVIEW. Because TEST has an IDENTITY constraint, the error is raised. The
following statement will execute because I have removed IDENTITY from the
columns in both the tables.

USE tempdb
CREATE TABLE TEST
(
COL1 INT PRIMARY KEY CHECK(COL1 BETWEEN 1 AND 10),
COL2 INT
)
GO
CREATE TABLE TEST2
(
COL1 INT PRIMARY KEY CHECK(COL1 BETWEEN 11 AND 20),
COL2 INT
)
GO
CREATE VIEW TESTVIEW
AS
SELECT COL1,COL2 FROM TEST
UNION ALL SELECT COL1,COL2 FROM TEST2
GO
UPDATE TESTVIEW SET COL1=1;
GO
DROP TABLE TEST
DROP TABLE TEST2
DROP VIEW TESTVIEW
GO

#13
Msg 4435, Level 16, State 3, Line 2 Partitioned view '%.*ls' is not updatable
because a value was not specified for partitioning column '%.*ls'.

SQL Server Error Message:

Partitioned view '%.*ls' is not updatable because a value was not specified for
partitioning column '%.*ls'.

What and Why "this Error":

This error occurs when you try to insert into a partitioned view but do not provide a
value for the partitioning column. For example: the following statement will raise
the above error.

USE tempdb
CREATE TABLE TEST
(
COL1 INT PRIMARY KEY CHECK(COL1 BETWEEN 1 AND 10),
COL2 INT
)
GO
CREATE TABLE TEST2
(
COL1 INT PRIMARY KEY CHECK(COL1 BETWEEN 11 AND 20),
COL2 INT
)
GO
CREATE VIEW TESTVIEW
AS
SELECT COL1,COL2 FROM TEST
UNION ALL SELECT COL1,COL2 FROM TEST2
GO
UPDATE TESTVIEW SET COL1=1;
GO
INSERT INTO TESTVIEW (COL2) VALUES (10);
GO
DROP TABLE TEST
DROP TABLE TEST2
DROP VIEW TESTVIEW
GO
Action Required to resolve this Error:

The solution to this error is you must provide a value for the partitioning column. In
the above example we try to insert into the view TESTVIEW. Because we have not
supplied a value for the partitioning column COL1, the error is raised. The following
statement will execute successfully.

USE tempdb
CREATE TABLE TEST
(
COL1 INT PRIMARY KEY CHECK(COL1 BETWEEN 1 AND 10),
COL2 INT
)
GO
CREATE TABLE TEST2
(
COL1 INT PRIMARY KEY CHECK(COL1 BETWEEN 11 AND 20),
COL2 INT
)
GO
CREATE VIEW TESTVIEW
AS
SELECT COL1,COL2 FROM TEST
UNION ALL SELECT COL1,COL2 FROM TEST2
GO
UPDATE TESTVIEW SET COL1=1;
GO
INSERT INTO TESTVIEW (COL1,COL2) VALUES (5,10);
GO
DROP TABLE TEST
DROP TABLE TEST2
DROP VIEW TESTVIEW
GO

#14
Msg 4448, Level 16, State 17, Line 2 Cannot INSERT into partitioned view '%.*ls'
because values were not supplied for all columns.

SQL Server Error Message:

Cannot INSERT into partitioned view '%.*ls' because values were not supplied for
all columns.

What and Why "this Error":


This error occurs when you try to insert into a partitioned view but do not provide
values for all columns. For example: the following statement will raise the above
error.

USE tempdb
CREATE TABLE TEST
(
COL1 INT PRIMARY KEY CHECK(COL1 BETWEEN 1 AND 10),
COL2 INT
)
GO
CREATE TABLE TEST2
(
COL1 INT PRIMARY KEY CHECK(COL1 BETWEEN 11 AND 20),
COL2 INT
)
GO
CREATE VIEW TESTVIEW
AS
SELECT COL1,COL2 FROM TEST
UNION ALL SELECT COL1,COL2 FROM TEST2
GO
UPDATE TESTVIEW SET COL1=1;
GO
INSERT INTO TESTVIEW (COL1) VALUES (5);
GO
DROP TABLE TEST
DROP TABLE TEST2
DROP VIEW TESTVIEW
GO

Action Required to resolve this Error:

The solution to this error is you must provide values for all columns. In the above
example we try to insert into the view TESTVIEW. Because we have not supplied a
value for the column COL2, the error is raised. The following statement will execute
successfully.

USE tempdb
CREATE TABLE TEST
(
COL1 INT PRIMARY KEY CHECK(COL1 BETWEEN 1 AND 10),
COL2 INT
)
GO
CREATE TABLE TEST2
(
COL1 INT PRIMARY KEY CHECK(COL1 BETWEEN 11 AND 20),
COL2 INT
)
GO
CREATE VIEW TESTVIEW
AS
SELECT COL1,COL2 FROM TEST
UNION ALL SELECT COL1,COL2 FROM TEST2
GO
UPDATE TESTVIEW SET COL1=1;
GO
INSERT INTO TESTVIEW (COL1,COL2) VALUES (5,15);
GO
DROP TABLE TEST
DROP TABLE TEST2
DROP VIEW TESTVIEW
GO

#15
Msg 4438, Level 16, State 17, Line 2 Partitioned view '%.*ls' is not updatable
because it does not deliver all columns from its member tables.

SQL Server Error Message:

Partitioned view '%.*ls' is not updatable because it does not deliver all columns
from its member tables.

What and Why "this Error":

This error occurs when you try to insert into or update a partitioned view that does
not expose all columns from its member tables. For example: the following
statement will raise the above error.

USE tempdb
CREATE TABLE TEST
(
COL1 INT PRIMARY KEY CHECK(COL1 BETWEEN 1 AND 10),
COL2 INT
)
GO
CREATE TABLE TEST2
(
COL1 INT PRIMARY KEY CHECK(COL1 BETWEEN 11 AND 20),
COL2 INT
)
GO
CREATE VIEW TESTVIEW
AS
SELECT COL1 FROM TEST
UNION ALL SELECT COL1 FROM TEST2
GO
UPDATE TESTVIEW SET COL1=1;
GO
INSERT INTO TESTVIEW (COL1) VALUES (5);
GO
DROP TABLE TEST
DROP TABLE TEST2
DROP VIEW TESTVIEW
GO

Action Required to resolve this Error:

The solution to this error is the partitioned view must expose all columns from its
member tables. In the above example we try to insert into the view TESTVIEW.
Because TESTVIEW does not expose the column COL2 from its member tables, the
error is raised. The following statement will execute successfully.

USE tempdb
CREATE TABLE TEST
(
COL1 INT PRIMARY KEY CHECK(COL1 BETWEEN 1 AND 10),
COL2 INT
)
GO
CREATE TABLE TEST2
(
COL1 INT PRIMARY KEY CHECK(COL1 BETWEEN 11 AND 20),
COL2 INT
)
GO
CREATE VIEW TESTVIEW
AS
SELECT COL1,COL2 FROM TEST
UNION ALL SELECT COL1,COL2 FROM TEST2
GO
UPDATE TESTVIEW SET COL1=1;
GO
INSERT INTO TESTVIEW (COL1,COL2) VALUES (5,15);
GO
DROP TABLE TEST
DROP TABLE TEST2
DROP VIEW TESTVIEW
GO

#16
Msg 4433, Level 16, State 4, Line 2 Cannot INSERT into partitioned view '%.*ls'
because table '%.*ls' has an IDENTITY constraint.
SQL Server Error Message:

Cannot INSERT into partitioned view '%.*ls' because table '%.*ls' has an IDENTITY
constraint.

What and Why "this Error":

This error occurs when you try to insert into a partitioned view, but at least one of
its member tables has an IDENTITY constraint. For example: the following
statement will raise the above error & it will also generate the error 4450.

USE tempdb
CREATE TABLE TEST
(
COL1 INT PRIMARY KEY CHECK(COL1 BETWEEN 1 AND 10),
COL2 INT IDENTITY
)
GO
CREATE TABLE TEST2
(
COL1 INT PRIMARY KEY CHECK(COL1 BETWEEN 11 AND 20),
COL2 INT
)
GO
CREATE VIEW TESTVIEW
AS
SELECT COL1,COL2 FROM TEST
UNION ALL SELECT COL1,COL2 FROM TEST2
GO
UPDATE TESTVIEW SET COL1=1;
GO
INSERT INTO TESTVIEW (COL1,COL2) VALUES (5,15);
GO
DROP TABLE TEST
DROP TABLE TEST2
DROP VIEW TESTVIEW
GO

Action Required to resolve this Error:

The solution to this error is the member tables of a partitioned view cannot have
IDENTITY constraints. In the above example we try to insert into the view
TESTVIEW because TEST has an IDENTITY constraint, the error is raised. The
following statement will execute successfully.

USE tempdb
CREATE TABLE TEST
(
COL1 INT PRIMARY KEY CHECK(COL1 BETWEEN 1 AND 10),
COL2 INT
)
GO
CREATE TABLE TEST2
(
COL1 INT PRIMARY KEY CHECK(COL1 BETWEEN 11 AND 20),
COL2 INT
)
GO
CREATE VIEW TESTVIEW
AS
SELECT COL1,COL2 FROM TEST
UNION ALL SELECT COL1,COL2 FROM TEST2
GO
UPDATE TESTVIEW SET COL1=1;
GO
INSERT INTO TESTVIEW (COL1,COL2) VALUES (5,15);
GO
DROP TABLE TEST
DROP TABLE TEST2
DROP VIEW TESTVIEW
GO

#17
Msg 4449, Level 16, State 1, Line 2 Using defaults is not allowed in views that
contain a set operator.

SQL Server Error Message:

Using defaults is not allowed in views that contain a set operator.

What and Why "this Error":

This error occurs when you try to insert into a view that contains a set operator, but
specify the DEFAULT keyword as part of the INSERT or UPDATE statement. For
example: the following statement will raise the above error.

USE tempdb
CREATE TABLE TEST
(
COL1 INT PRIMARY KEY CHECK(COL1 BETWEEN 1 AND 10),
COL2 INT
)
GO
CREATE TABLE TEST2
(
COL1 INT PRIMARY KEY CHECK(COL1 BETWEEN 11 AND 20),
COL2 INT
)
GO
CREATE VIEW TESTVIEW
AS
SELECT COL1,COL2 FROM TEST
UNION ALL SELECT COL1,COL2 FROM TEST2
GO
UPDATE TESTVIEW SET COL1=1;
GO
INSERT INTO TESTVIEW (COL1,COL2) VALUES (DEFAULT,15);
GO
DROP TABLE TEST
DROP TABLE TEST2
DROP VIEW TESTVIEW
GO

Action Required to resolve this Error:

The solution to this error is you cannot use the DEFAULT keyword when inserting or
updating data through a view that contains a set operator. In the above example
we try to insert into the view TESTVIEW. Because TESTVIEW contains a set
operator, the INSERT statement using the DEFAULT keyword raises the error. The
following statement will execute because I have removed the DEFAULT keyword.

USE tempdb
CREATE TABLE TEST
(
COL1 INT PRIMARY KEY CHECK(COL1 BETWEEN 1 AND 10),
COL2 INT
)
GO
CREATE TABLE TEST2
(
COL1 INT PRIMARY KEY CHECK(COL1 BETWEEN 11 AND 20),
COL2 INT
)
GO
CREATE VIEW TESTVIEW
AS
SELECT COL1,COL2 FROM TEST
UNION ALL SELECT COL1,COL2 FROM TEST2
GO
UPDATE TESTVIEW SET COL1=1;
GO
INSERT INTO TESTVIEW (COL1,COL2) VALUES (5,15);
GO
DROP TABLE TEST
DROP TABLE TEST2
DROP VIEW TESTVIEW
GO
#18
Msg 4432, Level 16, State 4, Line 2 Partitioned view '%.*ls' is not updatable
because table '%.*ls' has a DEFAULT constraint.

SQL Server Error Message:

Partitioned view '%.*ls' is not updatable because table '%.*ls' has a DEFAULT
constraint.

What and Why "this Error":

This error occurs when you try to insert into a partitioned view, but at least one of
its member tables has a DEFAULT constraint. For example: the following statement
will raise the above error.

USE tempdb
CREATE TABLE TEST
(
COL1 INT PRIMARY KEY CHECK(COL1 BETWEEN 1 AND 10),
COL2 INT DEFAULT(5)
)
GO
CREATE TABLE TEST2
(
COL1 INT PRIMARY KEY CHECK(COL1 BETWEEN 11 AND 20),
COL2 INT
)
GO
CREATE VIEW TESTVIEW
AS
SELECT COL1,COL2 FROM TEST
UNION ALL SELECT COL1,COL2 FROM TEST2
GO
UPDATE TESTVIEW SET COL1=1;
GO
INSERT INTO TESTVIEW (COL1) VALUES (5);
GO
DROP TABLE TEST
DROP TABLE TEST2
DROP VIEW TESTVIEW
GO

Action Required to resolve this Error:


The solution to this error is the member tables of a partitioned view cannot have
DEFAULT constraints. In the above example we try to insert into the view
TESTVIEW. Because TEST has a DEFAULT constraint, the error is raised. The
following statement will execute successfully.

USE tempdb
CREATE TABLE TEST
(
COL1 INT PRIMARY KEY CHECK(COL1 BETWEEN 1 AND 10),
COL2 INT
)
GO
CREATE TABLE TEST2
(
COL1 INT PRIMARY KEY CHECK(COL1 BETWEEN 11 AND 20),
COL2 INT
)
GO
CREATE VIEW TESTVIEW
AS
SELECT COL1,COL2 FROM TEST
UNION ALL SELECT COL1,COL2 FROM TEST2
GO
UPDATE TESTVIEW SET COL1=1;
GO
INSERT INTO TESTVIEW (COL1,COL2) VALUES (5,15);
GO
DROP TABLE TEST
DROP TABLE TEST2
DROP VIEW TESTVIEW
GO

#19
SQL Server Error Message:

What and Why "this Error":

Action Required to resolve this Error:

You might also like