Server: Msg 205, Level 16, State 1, Line 2
All queries in an SQL statement containing a UNION operator 
must have an equal number of expressions in their target lists.

To illustrate on how the error can be reproduced, let’s say you have two tables for the company employees, one which contains existing employees and the other containing previous employees of the company.

CREATE TABLE [dbo].[Employees] (
    [EmployeeID]            INT,
    [FirstName]             VARCHAR(50),
    [LastName]              VARCHAR(50),
    [Position]              VARCHAR(50)
)

CREATE TABLE [dbo].[OldEmployees] (
    [EmployeeID]            INT,
    [FirstName]             VARCHAR(50),
    [LastName]              VARCHAR(50),
    [Position]              VARCHAR(50),
    [LastEmploymentDate]    DATETIME
)

To get a list of all employees of the company, previous and past, you would do a UNION ALL of both tables, as follows:

SELECT * FROM [dbo].[Employees]
UNION ALL
SELECT * FROM [dbo].[OldEmployees]

But since the [dbo].[OldEmployees] table has an extra column, [LastEmploymentDate], the following error is encountered:

Server: Msg 205, Level 16, State 1, Line 2
All queries in an SQL statement containing a UNION operator
must have an equal number of expressions in their target lists.

Solution/Workaround:

When performing a UNION or UNION ALL on two tables that do not have the same number of columns, you have to specify each column in the SELECT clause instead of doing a SELECT *.  Aside from this, for the table that has a lesser number of columns, you have to provide a filler for the missing column or columns, such as NULL values or empty strings for VARCHAR columns and 0 for numeric columns.

To avoid the error in the example above, here’s how the SELECT statement will look like:

SELECT [EmployeeID], [FirstName], [LastName],
       [Position], NULL AS [LastEmploymentDate]
FROM [dbo].[Employees]
UNION ALL
SELECT [EmployeeID], [FirstName], [LastName],
       [Position], [LastEmploymentDate]
FROM [dbo].[OldEmployees]

Since the [dbo].[Employees] table contain current employees, it doesn’t have a column for the LastEmploymentDate.  To overcome the error, a NULL value is returned for that column, as shown in the SQL statement.

Server: Msg 166, Level 15, State 1, Line 2
'CREATE VIEW' does not allow specifying the database name as a prefix to the object name.

To illustrate, the following script will generate the error:

CREATE VIEW [Northwind].[dbo].[CustomerCountries]
AS
SELECT DISTINCT [Country]
FROM [dbo].[Customers]
GO

Server: Msg 166, Level 15, State 1, Line 2
'CREATE VIEW' does not allow specifying the database name as a prefix to the object name.

Solution/Workaround:

To avoid this error, remove the database name when creating a view:

CREATE VIEW [dbo].[CustomerCountries]
AS
SELECT DISTINCT [Country]
FROM [dbo].[Customers]
GO

If you need to create a view on another database, change your database first then create the view without the database name:

USE Northwind
GO

CREATE VIEW [dbo].[CustomerCountries]
AS
SELECT DISTINCT [Country]
FROM [dbo].[Customers]
GO

Server: Msg 443, Level 16, State 2, Procedure Function Name, Line 5
Invalid use of 'UPDATE' within a function.

To illustrate, here’s an example of a function that will generate the error:

CREATE FUNCTION [dbo].[ufn_GetTotalAmount]
( @OrderID    INT,
@Amount     MONEY )
RETURNS MONEY
AS
BEGIN

DECLARE @TotalAmount    MONEY

SELECT @TotalAmount = [TotalAmount] + @Amount
FROM [dbo].[Orders]
WHERE [OrderID] = @OrderID

UPDATE [dbo].[Orders]
SET [TotalAmount] = @TotalAmount
WHERE [OrderID] = @OrderID

RETURN @TotalAmount

END

Since the UPDATE statement is directed to a table and not to a table variable local to the function, the following error is encountered:

Server: Msg 443, Level 16, State 1, Procedure ufn_GetTotalAmount, Line 14
Invalid use of 'UPDATE' within a function.

Solution/Workaround:

To avoid this error from happening, make sure that you don’t use an UPDATE statement inside a user-defined function unless it’s updating a local table variable. If you really need to use the UPDATE statement on a table, you have to use a stored procedure instead of a user-defined function for this purpose.

Here’s how the script shown above will look like using a stored procedure:

CREATE PROCEDURE [dbo].[usp_GetTotalAmount]
@OrderID      INT,
@Amount       MONEY,
@TotalAmount  MONEY OUTPUT
AS

SELECT @TotalAmount = [TotalAmount] + @Amount
FROM [dbo].[Orders]
WHERE [OrderID] = @OrderID

UPDATE [dbo].[Orders]
SET [TotalAmount] = @TotalAmount
WHERE [OrderID] = @OrderID

Since a stored procedure can only return an integer value, the total amount needs to be returned as an output parameter because it is of money data type.

Server: Msg 5074, Level 16, State 8, Line 1
The object 'Primary Key Constraint Name' is dependent on column 'Primary Key Column Name'.
ALTER TABLE ALTER COLUMN Column Name failed because one or more objects access this column.

To illustrate, let’s say you have the following table definition:

CREATE TABLE [dbo].[Users] (
    [UserID]      SMALLINT NOT NULL IDENTITY,
    [UserName]    VARCHAR(20),
    [Password]    VARCHAR(20)
)
GO

ALTER TABLE [dbo].[Users]
ADD CONSTRAINT PK_Users PRIMARY KEY ( [UserID] )
GO

It was not anticipated during the initial design that the number of users of your application will not exceed 32,767, the maximum value that a SMALLINT data type can hold.  But due to its popularity, the number of users of your system has exceeded this number and you need to change the data type of the UserID from SMALLINT to INT.

To change the data type of the column from SMALLINT to INT, you issued the following command:

ALTER TABLE [dbo].[Users]
ALTER COLUMN [UserID] INT NOT NULL IDENTITY

But since the UserID column is the PRIMARY KEY, the following error is encountered:

Server: Msg 5074, Level 16, State 8, Line 1
The object 'PK_Users' is dependent on column 'UserID'.
Server: Msg 4922, Level 16, State 1, Line 1
ALTER TABLE ALTER COLUMN UserID failed because one or more objects access this column.

Solution/Workaround:

The easiest way to change the data type of a column that belongs to the PRIMARY KEY constraint is to do the changes in Enterprise Manager.  Enterprise Manager will take care of recreating the table for you and change the data type of the column that is a PRIMARY KEY.

To do it manually using Transact-SQL statements, assuming that the table is not being referenced by any other table in a FOREIGN KEY relationship, you first have to drop the PRIMARY KEY constraint, then change the data type of the column and lastly create the PRIMARY KEY constraint again.

ALTER TABLE [dbo].[Users]
DROP CONSTRAINT PK_Users
GO

ALTER TABLE [dbo].[Users]
ALTER COLUMN [UserID] INT NOT NULL
GO

ALTER TABLE [dbo].[Users]
ADD CONSTRAINT [PK_Users] PRIMARY KEY ( [UserID] )
GO

If the table is being referenced by other tables in a FOREIGN KEY relationship, you have to follow the following steps:

• Drop the FOREIGN KEY constraints from the other tables.
• Drop the PRIMARY KEY constraint from the primary table.
• Change the data type of the column in the primary table as well as in the other tables involved in the FOREIGN KEY relationship.
• Create the PRIMARY KEY constraint in the primary table.
• Lastly, create the FOREIGN KEY relationships previously dropped.

Server: Msg 8115, Level 16, State 5, Line 3
Arithmetic overflow error converting numeric to data type varchar.

The error can easily be reproduced using the following script, which explicitly converts a decimal value into a varchar data type:

DECLARE @DecimalValue   DECIMAL(5,3)
SET @DecimalValue = 99.999
SELECT CAST(@DecimalValue AS VARCHAR(5))

Server: Msg 8115, Level 16, State 5, Line 3
Arithmetic overflow error converting numeric to data type varchar.

Here’s another script on how to reproduce the error using an implicit conversion from a decimal data type to varchar:

DECLARE @VarCharValue VARCHAR(5)
SET @VarCharValue = 99.999

As can be seen from both scripts, the decimal value is being converted to a VARCHAR data type with a length 5, which is not long enough to hold the decimal value and the decimal.  The decimal value 99.999 will need 6 characters and not just 5.

Solution/Workaround:

To avoid this problem, make sure that the length of the VARCHAR variable or column is long enough to hold the decimal or numeric value, counting the decimal point as part of the converted VARCHAR value.  On both scripts, simply by increasing the length of the VARCHAR variable, the error is avoided:

DECLARE @DecimalValue   DECIMAL(5,3)
SET @DecimalValue = 99.999
SELECT CAST(@DecimalValue AS VARCHAR(6))

DECLARE @VarCharValue VARCHAR(6)
SET @VarCharValue = 99.999