Case expressions may only be nested to level %d

Msg 125, Level 15, State 1, Line 1
Case expressions may only be nested to level %d.

I never experienced this in SQL Server 2017 onwards, but this was being experienced in previous version on Linked Server only. So below example is the demonstration of that assuming linked server.

Declare @id as int
set @id=13
SELECT 
    (CASE @Id
        WHEN 1 THEN 1
        WHEN 2 THEN 2
        WHEN 3 THEN 3
        WHEN 4 THEN 4
        WHEN 5 THEN 5
        WHEN 6 THEN 6
        WHEN 7 THEN 7
        WHEN 8 THEN 8
        WHEN 9 THEN 9
        WHEN 10 THEN 10
        WHEN 11 THEN 11
	WHEN 12 THEN 12
        WHEN 13 THEN 13
        WHEN 14 THEN 14
    END) AS Test
	from [SANTANA\MSSQLSERVER17].Rohit.dbo.Emp

You can resolve this by using below technique.

Using multiple coalesce cases.

Using Open query.

Points to remember :

• The above query will work absolutely fine if you run from the local instances even more than 10 conditions.this error message only happens when we are applying a case from data that comes from a Linked Server.
• If the query does not use Linked Server, you do not need to use this technique. You can use as many conditions as you want in the same case.
• If your query uses Linked Server that points to its own instance, you do not need to use this technique either. It works like a normal query without linked server.
• The COALESCE function accepts multiple conditions, so it is not limited to just 2 cases, they can be multiple (although the complexity of the code is increasing.)
• You can only use up to 9 conditions in each CASE in scenarios where the query is done on remote data.
• Using ELSE NULL at the end of each case is optional.
• Subquery and CTE do not resolve this issue.
• OPENQUERY and OPENROWSET do not have this limitation of 10 CASE conditions either.

The select list for the INSERT statement contains more items than the insert list

 Msg 121, Level 15, State 1, Line 1

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.

SQL Server error 121 occurs when you don’t specify enough columns in your INSERT list when using a SELECT list for the values to insert.
It happens when you use a SELECT list in your INSERT statement, but the SELECT list doesn’t return as many columns as you’re specifying with the INSERT.

This is easy to fix. Simply make sure the number of columns match between your INSERT and SELECT list.

Here is the example of the code that cause the error.

INSERT INTO OrdersLatest (
    OrderId,OrderDate
    )
SELECT 
    OrderId,OrderDate,OrderDesc
	FROM OrdersMarch;

Output:

 Msg 121, Level 15, State 1, Line 1
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.

You can fix this by correcting it.

INSERT INTO OrdersLatest (
    OrderId,OrderDate
    )
SELECT 
    OrderId,OrderDate
	FROM OrdersMarch;

You can use less columns this would depend on whether or not we have any NOT NULL constraints on the destination table.

The select list for the INSERT statement contains fewer items than the insert list

 Msg 120, Level 15, State 1, Line 1
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.

SQL Server error 120 occurs when you don’t specify enough columns in your INSERT list when using a SELECT list for the values to insert.
It happens when you use a SELECT list in your INSERT statement, but the SELECT list doesn’t return as many columns as you’re specifying with the INSERT.

This is easy to fix. Simply make sure the number of columns match between your INSERT and SELECT list.

Here is the example of the code that cause the error.

INSERT INTO OrdersLatest (
    OrderId,OrderDate,OrderDesc
    )
SELECT 
    OrderId,OrderDate
	FROM OrdersMarch;

Output:

 Msg 120, Level 15, State 1, Line 1
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.

You can fix this by correcting it.

INSERT INTO OrdersLatest (
    OrderId,OrderDate,OrderDesc
    )
SELECT 
    OrderId,OrderDate,OrderDesc
	FROM OrdersMarch;

You can use less columns this would depend on whether or not we have any NOT NULL constraints on the destination table.

SQL Error 119 - Must pass parameter number 2 and subsequent parameters

 Msg 119, Level 15, State 1, Line 1

Must pass parameter number 2 and subsequent parameters as '@name = value'.  After the form '@name = value' has been used, all subsequent parameters must be passed in the form '@name = value'.

Reason : If we want to use variable names while passing parameters to stored procedure then we must have to use variable in all the parameters.

Suppose we have created a simple stored procedure uspDisplayData in SQL Server

CREATE PROCEDURE uspDisplayData(
     @Param1 AS VARCHAR(100),
     @Param2 AS INT
)
AS
SELECT @Param1, @Param2

Now if we will execute this stored procedure:

EXECUTE dbo.uspDisplayData @Param1 = 'Sql Server',2

We will get error message like:

Must pass parameter number 2 and subsequent parameters as '@name = value'. After the form '@name = value' has been used, all subsequent parameters must be passed in the form '@name = value'.

Solution:

Correct ways to pass the parameters are:

EXECUTE dbo.uspDisplayData 'Sql Server',2

OR

EXECUTE dbo.uspDisplayData @Param1 = 'Sql Server',@Param2 = 2

This will result correct result and execute without error. 

The object name 'Object Name' contains more than the maximum number of prefixes. The maximum is 2

Msg 117, Level 15, State 2, Line 4
The object name 'Object Name' contains more than the maximum number of prefixes.  The maximum is 2.

Lets see the below example. Trying Select the records.

select * from [SANTANA\MSSQLSERVER17].Rohit.dbo.Employee.Employeeid
where EmployeeID=101

Output:

Msg 117, Level 15, State 1, Line 2
The object name 'SANTANA\MSSQLSERVER17.Rohit.dbo.Employee.Employeeid' contains more than the maximum number of prefixes. The maximum is 3.

In the above example Employeeid is an invalid selection results in the error.

This error usually comes up when you are trying to perform a SELECT..INTO across a linked server. The problem is that a 4 part name cannot be used as the “INTO” parameter. This will fail.
The trick is to create the table on the remote server, then perform an INSERT INTO.

 

Only one expression can be specified in the select list when the subquery is not introduced with EXISTS

Msg 116, Level 16, State 1, Line 1
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

This error is usually encountered when using a sub-query together with the IN or NOT IN logical operator. The IN logical operator determines whether a specified value matches any value in a subquery or a list.

Select EmployeeID,FirstName,LastName 
from Employee
where EmployeeID IN (Select EmployeeID,FirstName from Employee)

Output:

Msg 116, Level 16, State 1, Line 1
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

You can't return two (or multiple) columns in your subquery to do the comparison in the EmployeeID IN (subquery) clause - which column is it supposed to compare EmployeeID to? The subquery must only return the one column needed for the comparison to the column on the other side of the IN. So the query needs to be of the form:

Select EmployeeID,FirstName,LastName 
from Employee
where EmployeeID IN (Select EmployeeID from Employee)

You also want to add sorting so you can select just from the top rows, but you don't need to return the COUNT as a column in order to do your sort; sorting in the ORDER clause is independent of the columns returned by the query.

Browse mode is invalid for a statement that assigns values to a variable

 SQL Error 114 : Browse mode is invalid for a statement that assigns values to a variable.

NetBackup VMware backups using SQL Server application protection fails showing unimplemented error code 114(114)

Grant sysadmin permissions to the System account (NT AUTHORITY\SYSTEM) on all SQL Server instances that are installed and online on the server where you are trying to perform the VSS backup or use another  local/domain account/group that has sysadmin permissions on the SQL Server online instances for the "Log on as" account that starts the SQL VSS Writer service.

I will share more info if I have...

SQL Error 113 - Missing end comment mark

 Msg 113, Level 15, State 1, Line 1
Missing end comment mark '*/'.

SQL Server error message 113 occurs when you omit a closing comment mark.

This can occur when you open a comment but forget to close it. It can also occur when you accidentally type an opening comment.

There are 2 ways of specifying comments in a Transact-SQL script, namely with the use of two hyphens (--) for single-line comments, and with the use of /* and */ for multi-line comments. This error message occurs when using the /* and */ for multi-line comments and the closing */ is missing.

The error can be generated below 2 ways.

/*
Select * from Employee
Go

/*
--Select * from Employee /*
Go
*/

Output:

 Msg 113, Level 15, State 1, Line 1
Missing end comment mark '*/'.

To avoid this error make sure there are same number of opening and closing tags

/*
Select * from Employee
Go */

/*
--Select * from Employee /*
Go
*/*/

'CREATE FUNCTION' must be the first statement in a query batch

 Msg 111, Level 15, State 1, Line 3
'CREATE FUNCTION' must be the first statement in a query batch.

When creating the User Defined function, if the CREATE FUNCTION is not the first statement in the query, you will receive this error
There should be no other statements before the CREATION FUNCTION statement that would make it not the first statement in a query batch

Below is the SQL Script that will lead to the error.

Select * from Employee

Create FUNCTION [dbo].[Calculate_Age]
(
@DOB datetime , @calcDate datetime
)
RETURNS int
AS
BEGIN
declare @age int
IF (@calcDate < @DOB )
RETURN -1
-- If a DOB is supplied after the comparison date, then return -1
SELECT @age = YEAR(@calcDate) - YEAR(@DOB) +
CASE WHEN DATEADD(year,YEAR(@calcDate) - YEAR(@DOB)
,@DOB) > @calcDate THEN -1 ELSE 0 END
RETURN @age
END

Output:
Msg 111, Level 15, State 1, Line 3
'CREATE FUNCTION' must be the first statement in a query batch.

To avoid this error, CREATE FUNCTION statement must always be the first statement in the Query batch. To fix this, the GO command needs to be added to seperate the SELECT Command from the CREATE FUNCTION. One of the advantages of this is that the GO command lets the SQL Server know that its the end of the batch of T-SQL statements and anything after the GO is a new batch of query.

Select * from Employee
Go

Create FUNCTION [dbo].[Calculate_Age]
(
@DOB datetime , @calcDate datetime
)
RETURNS int
AS
BEGIN
declare @age int
IF (@calcDate < @DOB )
RETURN -1
-- If a DOB is supplied after the comparison date, then return -1
SELECT @age = YEAR(@calcDate) - YEAR(@DOB) +
CASE WHEN DATEADD(year,YEAR(@calcDate) - YEAR(@DOB)
,@DOB) > @calcDate THEN -1 ELSE 0 END
RETURN @age
END

SQL Error 110 - 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.

 Msg 110, Level 15, State 1, Line 7
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.

The error occurs while trying to do insert operation using INSERT..INTO.There are fewer columns in the INSERT statement than values specified in the VALUES clause.

Example:

Create Table Students 
(
	Id int,
	FirstName Varchar(50),
	LastName Varchar(50)
)
INSERT INTO Students (Id,FirstName)
VALUES (1,'Rohit','Kumar')

Output: 

 Msg 110, Level 15, State 1, Line 7
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.

This can be resolved by the number of values in the VALUES clause must match the number of columns specified in the INSERT statement.

SQL Error 109 - 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

 Msg 109, Level 15, State 1, Line 7
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.

You will see this error message when you are doing an INSERT operation to a table using INSERT TO.. There are more columns in the INSERT statement than values specified in the VALUES clause

Example:

Create Table Students 
(
	Id int,
	FirstName Varchar(50),
	LastName Varchar(50)
)
INSERT INTO Students (Id,FirstName,LastName)
VALUES (1,'Rohit')

Output:

Msg 109, Level 15, State 1, Line 7
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.

The above error msg is also self descrptive.

The ORDER BY position number is out of range of the number of items in the select list

Msg 108, Level 15, State 1, Line 1
The ORDER BY position number is out of range of the number of items in the select list. 

This error occurs when specifying the column index or position number in an ORDER BY clause in your SELECT statement and the column index or position number is either 0 or higher than the number of columns specified in the SELECT clause.

This error usually happens when you are using the index in the ORDER BY clause instead of the column name and you have either used 0 or the position that is higher than the number of columns specified in the SELECT clause.

Use AdventureWorks2017
Go

Select FirstName,MiddleName,LastName
from Person.Person
Order By 4

OutPut:

Msg 108, Level 16, State 1, Line 3
The ORDER BY position number 4 is out of range of the number of items in the select list.

To avoid this, error make sure that the position number or the column index specified in the ORDER by clause is with-in the range of items in the SELECT clause.

The best practice is to use the column name instead of the index as shown below.

Use AdventureWorks2017
Go

Select FirstName,MiddleName,LastName
from Person.Person
Order By FirstName

One of the advantages of specifying the column name instead of the index is that even you add new columns in to the SELECT clause, you will have the desired result with-out any errors. Using index for sorting will result in undesired results when you add new columns in to your SELECT clause.