Skip Navigation Links
Home
Articles
SQL Server 2012
SQL Server 2014
SQL Server 2016
FAQ
Practice Test
Tip of the Day : SQL Server Database Design - Internet Movie Database (IMDb)
FAQ - SQL Server

FAQ - SQL Server 2005
Home > Frequently Asked Questions > SQL Server Error Messages
SQL Server Error Messages - Frequently Asked Questions

 11.  The Query Designer does not support the UNION SQL construct.

Error Message:

The Query Designer does not support the UNION SQL construct.

Causes:

This error occurs when creating a view in SQL Server Enterprise Manager wherein the view definition uses a UNION or UNION ALL operator.

Solution/Workaround:

The UNION or UNION ALL operator can be used in a view but SQL Server Enterprise Manager Query Designer will not allow the creation of such a view.  To create a view that contains the UNION or UNION ALL operator, you have to use Query Analyzer.

CREATE VIEW [dbo].[AllEmployees]
AS
SELECT [EmployeeID], [Name]
FROM [dbo].[OldEmployees]
UNION ALL
SELECT [EmployeeID], [Name]
FROM [dbo].[Employees]

 12.  The Query Designer does not support the CASE SQL construct.

Error Message:

The Query Designer does not support the CASE SQL construct.

Causes:

This error occurs when creating a view in SQL Server Enterprise Manager wherein the view definition uses a CASE statement.

Solution/Workaround:

A CASE statement can be used in a view definition but SQL Server Enterprise Manager Query Designer will not allow the creation of such a view. To create a view that contains a CASE statement, you have to use Query Analyzer.

CREATE VIEW [dbo].[EmployeeStatus]
AS

SELECT [ID],
       [Name],
       CASE [MaritalStatus]
           WHEN ‘S’ THEN ‘Single’
           WHEN ‘M’ THEN ‘Married’
           WHEN ‘D’ THEN ‘Divorced’
           ELSE ‘Unknown’
       END AS [MaritalStatus]
FROM [dbo].[Employees]
GO

 13.  There are more columns in the INSERT statement than values specified in the VALUES clause.  The number of values in the VALUES clause must match the number of columns specified in the INSERT statement.

Error Message:

Server: Msg 109, Level 15, State 1, Line 4
There are more columns in the INSERT statement than values specified in the VALUES clause.
The number of values in the VALUES clause must match the number of columns 
specified in the INSERT statement.

Causes:

As the error message describes, this error occurs when doing an INSERT to a table using the INSERT INTO … VALUES format and the number of values specified in the VALUES clause is less than the number of columns specified.

View Error Message Details and Solution / Workaround

To illustrate, the following INSERT statement will generate the error:

INSERT INTO [dbo].[Employees] ( [FirstName], [LastName], [Gender] )
VALUES ( 'Mickey', 'Mouse' )

Solution/Workaround:

To avoid this error, make sure that the number of values specified in the VALUES clause matches the number of columns specified in the INSERT INTO clause:

INSERT INTO [dbo].[Employees] ( [FirstName], [LastName], [Gender] )
VALUES ( 'Mickey', 'Mouse', 'M' )

 14.  There are fewer columns in the INSERT statement than values specified in the VALUES clause.  The number of values in the VALUES clause must match the number of columns specified in the INSERT statement.

Error Message:

Server: Msg 110, Level 15, State 2, Line 4
There are fewer columns in the INSERT statement than values specified in the VALUES clause.
The number of values in the VALUES clause must match the number of columns
specified in the INSERT statement.

Causes:

As the error message describes, this error occurs when doing an INSERT to a table using the INSERT INTO … VALUES format and the number of values specified in the VALUES clause is more than the number of columns specified.

View Error Message Details and Solution / Workaround

To illustrate, the following INSERT statement will generate the error:

INSERT INTO [dbo].[Employees] ( [FirstName], [LastName] )
VALUES ( 'Mickey', 'Mouse', 'M' )

Solution/Workaround:

To avoid this error, make sure that the number of values specified in the VALUES clause matches the number of columns specified in the INSERT INTO clause:

INSERT INTO [dbo].[Employees] ( [FirstName], [LastName], [Gender] )
VALUES ( 'Mickey', 'Mouse', 'M' )

 15.  Column ‘Primary Table.Primary Key’ is not the same length as referencing column 'Referencing Table.Referencing Column' in foreign key ‘Foreign Key Constraint Name’.

Error Message:

Server: Msg 1753, Level 16, State 1, Line 2
Column ‘Primary Table.Primary Key’ is not the same length as referencing column 
'Referencing Table.Referencing Column' in foreign key ‘Foreign Key Constraint Name’.

Causes:

This error occurs when you are trying to create a foreign key relationship between two tables and the length of the referencing column is different from the length of the primary key.

View Error Message Details and Solution / Workaround

To illustrate, here’s a script that will generate the error:

CREATE TABLE [dbo].[Employees] (
[ID]    VARCHAR(10)    PRIMARY KEY,
[Name]  VARCHAR(50),
[Position]    VARCHAR(2)
)
GO

CREATE TABLE [dbo].[EmployeeManager] (
[ManagerID]    VARCHAR(5) REFERENCES [dbo].[Employees] ( [ID] ),
[EmployeeID]   VARCHAR(5) REFERENCES [dbo].[Employees] ( [ID] )
)
GO

Executing this script will generate the following error:

Server: Msg 1753, Level 16, State 1, Line 6
Column 'dbo.Employees.ID' is not the same length as referencing column 
'EmployeeManager.ManagerID' in foreign key 'FK__EmployeeM__Manag__2BFE89A6'.
Server: Msg 1750, Level 16, State 1, Line 6
Could not create constraint. See previous errors.

Solution/Workaround:

To avoid this error, always make sure that the length of the referencing column is the same as the length of the primary key being referenced.  The following script will solve the problem:

CREATE TABLE [dbo].[Employees] (
[ID]        VARCHAR(10)    PRIMARY KEY,
[Name]      VARCHAR(50),
[Position]  VARCHAR(2)
)
GO

CREATE TABLE [dbo].[EmployeeManager] (
[ManagerID]    VARCHAR(10) REFERENCES [dbo].[Employees] ( [ID] ),
[EmployeeID]   VARCHAR(10) REFERENCES [dbo].[Employees] ( [ID] )
)
GO
More Frequently Asked Questions - SQL Server Error Messages Previous Page  Next Page  
Related Articles :