Server: Msg 2739, Level 16, State 1, Line 1
The text, ntext, and image data types are invalid for local variables.

Solution/Workaround:

If you are using SQL Server 2005, you can use the varchar(max), nvarchar(max) and varbinary(max) in place of the text, ntext and image data types, respectively.  With varchar(max), nvarchar(max) and varbinary(max), you can create local variables of these data types and manipulate them just like any varchar, nvarchar and varbinary data type.

If you are using SQL Server 2000, a work around is to use VARCHAR(8000) or NVARCHAR(4000) as the data type of the local variable. Then you have to CAST the value of the TEXT into these data types.  The disadvantage of using VARCHAR(8000) or NVARCHAR(4000) is that you are limited in the length, as specified in the length.

Here’s an example on how to use NVARCHAR in place of the NTEXT data type:

DECLARE @CategoryDescription  NVARCHAR(4000)

SELECT @CategoryDescription = CAST([Description] AS NVARCHAR(4000))
FROM [dbo].[Categories]
WHERE [CategoryID] = 1

A second option is to make use of multiple VARCHAR(8000) or NVARCHAR(4000) local variables then concatenate them as needed.  One common task when using local variables with more than 8000 characters is when building and executing long dynamic SQL statements.  This limitation can easily be overcome by making use of multiple VARCHAR(8000) or NVARCHAR(4000) local variables and then concatenating them when they are about to be executed:

DECLARE @SQLStmt1    VARCHAR(8000)
DECLARE @SQLStmt2    VARCHAR(8000)
DECLARE @SQLStmt3    VARCHAR(8000)

-- Build your SQL statements using these variables

EXECUTE (@SQLStmt1 + @SQLStmt2 + @SQLStmt3)

A third option is to use the READTEXT, WRITETEXT and UPDATETEXT Transact-SQL statements together with the TEXTPTR function to manipulate text, ntext and image columns in a table.  You can refer to Books Online regarding these Transact-SQL statements and functions for more information.

Server: Msg 8157, Level 16, State 1, Procedure View Name, Line 3
All the queries in a query expression containing a UNION operator 
must have the same number of expressions in their select lists.

To illustrate, let’s say you have the following table definitions for the company employees, past and present:

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
)

Then you decided to create a view on these two tables that will contain all employees of the company:

CREATE VIEW [dbo].[AllEmployees]
AS
SELECT * FROM [dbo].[Employees]
UNION ALL
SELECT * FROM [dbo].[OldEmployees]

Since the [dbo].[OldEmployees] table has an extra column for the last employment date of the employee, [LastEmploymentDate], the following error is encountered:

Server: Msg 8157, Level 16, State 1, Procedure AllEmployees, Line 3
All the queries in a query expression containing a UNION operator 
must have the same number of expressions in their select lists.

Solution/Workaround:

When creating a view that merges or joins the results of two tables that do not have the same number of columns, always specify the columns in the SELECT clause instead of using the SELECT *.  Also, use fillers for the missing columns from each table.  If the missing column is of VARCHAR data type, you can use NULL or an empty string as filler for the missing column.  If the missing column is a numeric column, you can use either NULL or 0 for the missing column.

To avoid the error generated from the above example, the script for the view will look like the following:

CREATE VIEW [dbo].[AllEmployees]
AS
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 does not have the [LastEmploymentDate] column, a NULL value is returned for that column so that the number of columns in the first table matches the number of columns in the second column.

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

To illustrate, here’s a user-defined function that will generate the error:

CREATE FUNCTION [dbo].[CreateNewRow] 
( @pFirstName VARCHAR(50), @pLastName VARCHAR(50) )
RETURNS INT
AS
BEGIN

    INSERT INTO [dbo].[Student] ( [FirstName], [LastName] )
    VALUES ( @pFirstName, @pLastName )

    RETURN SCOPE_IDENTITY()
END
GO

Server: Msg 443, Level 16, State 2, Procedure CreateNewRow, Line 7
Invalid use of 'INSERT' within a function.

Here’s a user-defined function that uses the INSERT command but the modified table is a table variable local to the function:

CREATE FUNCTION [dbo].[ufn_GetMaxValue]
( @pInt1 INT, @pInt2 INT, @pInt3 INT, @pInt4 INT )
RETURNS INT
AS
BEGIN
    DECLARE @IntTable TABLE ( [IntValue] INT )
    DECLARE @MaxValue INT

    INSERT INTO @IntTable ( [IntValue] ) VALUES ( @pInt1 )
    INSERT INTO @IntTable ( [IntValue] ) VALUES ( @pInt2 )
    INSERT INTO @IntTable ( [IntValue] ) VALUES ( @pInt3 )
    INSERT INTO @IntTable ( [IntValue] ) VALUES ( @pInt4 )

    SELECT @MaxValue = MAX( [IntValue] )
    FROM @IntTable

    RETURN @MaxValue
END
GO

This user-defined function also uses the INSERT command but since it is inserting into a local variable, then the error is not encountered.

Solution/Workaround:

Since the INSERT command is not allowed in a user-defined function, you have to use a stored procedure for this purpose instead.  Here’s the same script as the above function but using a stored procedure:

CREATE PROCEDURE [dbo].[CreateNewRow]
    @pFirstName VARCHAR(50), 
    @pLastName VARCHAR(50)
AS

INSERT INTO [dbo].[Student] ( [FirstName], [LastName] )
VALUES ( @pFirstName, @pLastName )

RETURN SCOPE_IDENTITY()
GO

Server: Msg 8111, Level 16, State 1, Line 1
Cannot define PRIMARY KEY constraint on nullable column in table 'Table Name'.
Server: Msg 1750, Level 16, State 1, Line 1
Could not create constraint. See previous errors.

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

CREATE TABLE [dbo].[Currency] (
    [Code]   CHAR(3),
    [Name]   VARCHAR(50)
)

Since the nullability of the columns is not specified, this is determined by the session settings ANSI_NULL_DFLT_ON and ANSI_NULL_DFLT_OFF or by the database settings ANSI null default.  If the ANSI_NULL_DFLT_ON session setting is ON or the ANSI null default is set to true, then the nullability of the column will be set to NULL.

Assuming that this is the case, wherein the nullability of both the Code and Name columns are set to NULL, creating a PRIMARY KEY constraint on the Code column generates the error:

ALTER TABLE [dbo].[Currency]
ADD CONSTRAINT PK_Currency_Code 
PRIMARY KEY ( [Code] )

Server: Msg 8111, Level 16, State 1, Line 1
Cannot define PRIMARY KEY constraint on nullable column in table 'Currency'.
Server: Msg 1750, Level 16, State 1, Line 1
Could not create constraint. See previous errors.

Solution/Workaround:

To avoid this error from happening, first change the nullability of the columns being designated as the primary key columns to NOT NULL then create the PRIMARY KEY constraint:

ALTER TABLE [dbo].[Currency]
ALTER COLUMN [Code] CHAR(3) NOT NULL
GO

ALTER TABLE [dbo].[Currency]
ADD CONSTRAINT PK_Currency_Code 
PRIMARY KEY ( [Code] )
GO

Server: Msg 256, Level 16, State 1, Line 3
The data type int is invalid for the substring function.
Allowed types are: char/varchar, nchar/nvarchar, and binary/varbinary.

To illustrate, the following script will generate the error:

DECLARE @YYYYMMDD  INT
SET @YYYYMMDD = 20060101
SELECT SUBSTRING(@YYYYMMDD, 1, 4) AS [Year]

Solution/Workaround:

To avoid this error, always make sure that the data type of the first parameter that is passed to the SUBSTRING function is of char, varchar, nchar, nvarchar, binary or varbinary data type.  If the data type is not any of these, you can use the CAST function to convert it to one of these data types.

DECLARE @YYYYMMDD  INT
SET @YYYYMMDD = 20060101
SELECT SUBSTRING(CAST(@YYYYMMDD AS VARCHAR(8)), 1, 4) AS [Year]