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
|