Skip Navigation Links
Home
Articles
SQL Server 2012
SQL Server 2014
SQL Server 2016
FAQ
Forums
Practice Test
Bookstore
Tip of the Day : Example Uses of the RTRIM String Function
FAQ - SQL Server

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

 16.  Column 'Primary Table.Primary Key' is not the same data type as referencing column 'Referencing Table.Referencing Column' in foreign key 'Foreign Key Constraint Name'.

Error Message:

Server: Msg 1778, Level 16, State 1, Line 1
Column 'Primary Table.Primary Key' is not the same data type 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 data type of the referencing column is different from the data type 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]  INT REFERENCES [dbo].[Employees] ( [ID] ),
[EmployeeID] INT REFERENCES [dbo].[Employees] ( [ID] )
)
GO

Executing this script will generate the following error:

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

Solution/Workaround:

To avoid this error, always make sure that the data type of the referencing column is the same as the data type 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

 17.  The select list for the INSERT statement contains fewer items than the insert list.  The number of SELECT values must match the number of INSERT columns.

Error Message:

Server: Msg 120, Level 15, State 1, Line 4
The select list for the INSERT statement contains fewer items than the insert list.
The number of SELECT values must match the number of INSERT columns.

Causes:

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

View Error Message Details and Solution / Workaround

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

INSERT INTO [dbo].[Employees] ( [FirstName], [LastName], [Gender] )
SELECT [FirstName], [LastName]
FROM [dbo].[Applicants]

Solution/Workaround:

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

INSERT INTO [dbo].[Employees] ( [FirstName], [LastName], [Gender] )
SELECT [FirstName], [LastName], [Gender]
FROM [dbo].[Applicants]

 18.  The select list for the INSERT statement contains more items than the insert list.  The number of SELECT values must match the number of INSERT columns.

Error Message:

Server: Msg 121, Level 15, State 1, Line 4
The select list for the INSERT statement contains more items than the insert list.
The number of SELECT values must match the number of INSERT columns.

Causes:

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

View Error Message Details and Solution / Workaround

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

INSERT INTO [dbo].[Employees] ( [FirstName], [LastName] )
SELECT [FirstName], [LastName], [Gender]
FROM [dbo].[Applicants]

Solution/Workaround:

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

INSERT INTO [dbo].[Employees] ( [FirstName], [LastName], [Gender] )
SELECT [FirstName], [LastName], [Gender]
FROM [dbo].[Applicants]

 19.  View or function 'View or Function Name' has more column names specified than columns defined.  Could not use view or function 'View or Function Name' because of binding errors.

Error Message:

Server: Msg 4502, Level 16, State 1, Procedure Object Name, Line 4
View or function 'View or Function Name' has more column names specified than columns defined.
Server: Msg 4413, Level 16, State 1, Line 1
Could not use view or function 'View or Function Name' because of binding errors.

Causes:

This error occurs when a column being referenced by a view has been dropped from the base table being referenced by the view and a SELECT statement is performed on the view.

View Error Message Details and Solution / Workaround

To illustrate, suppose you have the following table and view:

CREATE TABLE [dbo].[Customers] (
[CustomerID]    INT,
[CustomerName]  VARCHAR(100),
[Address]       VARCHAR(100),
[Gender]        CHAR(1)
)
GO

CREATE VIEW [dbo].[CustomersView]
AS
SELECT * FROM [dbo].[Customers]
GO

Doing a SELECT * FROM [dbo].[CustomersView] statement will give you the rows from the table with all the columns of the [dbo].[Customers] table.

The error above will be generated if one of the columns in [dbo].[Customers] is suddenly dropped from the table and a SELECT statement is performed on the view:

ALTER TABLE [dbo].[Customers] DROP COLUMN [Gender]
SELECT * FROM [dbo].[CustomersView]

Solution/Workaround:

To avoid this error, you have to refresh the view by calling the sp_refreshview system stored procedure:

EXECUTE sp_refreshview '[dbo].[CustomersView]'

The sp_refreshview system stored procedure refreshes the metadata for the specified view as persistent metadata for a view can become outdated because of changes to the underlying objects upon which the view depends.


 20.  Invalid operator for data type. Operator equals add, type equals text.

Error Message:

Server: Msg 403, Level 16, State 1, Line 1
Invalid operator for data type. Operator equals add, type equals text.

Causes:

This error occurs when you are trying to concatenate 2 columns of TEXT data type.

View Error Message Details and Solution / Workaround

Error Message:

Server: Msg 403, Level 16, State 1, Line 1
Invalid operator for data type. Operator equals add, type equals text.

To illustrate, assume that you have the following table definition:

CREATE TABLE [dbo].[StudentComments] (
[StudentID]            INT,
[CommentsOnTeacher]    TEXT,
[CommentsOnSubject]    TEXT
)

The following SELECT statement will generate the error:

SELECT [StudentID],
[CommentsOnTeacher] + [CommentsOnSubject] AS [AllComments]
FROM [dbo].[StudentComments]

Server: Msg 403, Level 16, State 1, Line 3
Invalid operator for data type. Operator equals add, type equals text.

Solution/Workaround:

To work around this error you need to CAST the TEXT column into VARCHAR first before concatenating the columns.  The following query will avoid this error:

SELECT [StudentID],
CAST(CAST([CommentsOnTeacher] AS VARCHAR(8000)) +
CAST([CommentsOnSubject] AS VARCHAR(8000)) AS TEXT)
AS [AllComments]
FROM [dbo].[StudentComments]

The only drawback with this work around is that you are limiting both TEXT columns to 8000 characters each.  One other way to work around this is to do the concatenation in the client application instead of having SQL Server do the concatenation.

More Frequently Asked Questions - SQL Server Error Messages Previous Page  Next Page  
Related Articles :