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.

The column prefix does not match with a table name or alias name used in the query

Msg 107, Level 16, State 3, Line 1

The column prefix does not match with a table name or alias name used in the query.

The actual Error message varies with the Alias being used. The usual message is as shown below.

The column prefix '%.*ls' does not match with a table name or alias name used in the query.

The % in the above can be replaced with the alias that was used and caused the issue.

For example, below are 2 scenarios that will lead to the SQL Error msg 107.

• When the Column Prefix doesnot correspond to the table or alias name that is used in the SQL query. The below query leads to the error. 

Use AdventureWorks2017
Go

SELECT P.*
FROM Person.Person
where FirstName='Adam'

Output:

Msg 107, Level 15, State 1, Line 7

The column prefix 'P' does not match with a table name or alias name used in the query.

In this query, the alias P cannot be identified and will result above error

• The table name is specified as a column prefix where as the alias name is supplied in the FROM clause. 

Use AdventureWorks2017
Go

SELECT Person.*
FROM Person.Person As PR
where FirstName='Adam'

Output:

Msg 107, Level 15, State 1, Line 7

The column prefix 'Person' does not match with a table name or alias name used in the query.

To avoid this error, always make sure that the table name used as a prefix of a column in a query exists in the FROM clause.

Use AdventureWorks2017
Go

SELECT Person.*
FROM Person.Person
where FirstName='Adam'

Also, to avoid the second cause of this error, once you assign an alias to a table in the FROM clause, make sure to use that alias in a column prefix and not the original table name:

Use AdventureWorks2017
Go

SELECT PR.*
FROM Person.Person As PR
where FirstName='Adam'

Too many table names in the query. The maximum allowable is %d

 While using SQL Server 2005, You might have exceeded maximum allowed tables.

This time the error message was Too many table names in the query. The maximum allowable is 256.

Unfortunately, it’s not very feasible to reduce the number of tables being used as that number is dependent on the query.

Higher version of sql server is not having such problem.

SQL Error 105 : Unclosed quotation mark before the character string '%.*ls'

Msg 105, Level 15, State 1, Line n
Unclosed quotation mark before the character string '%.*ls'

The error msg indicate not correctly formatted string. In SQL Server it must be enclosed with single quotation mark (' '). 

SQL Server uses the single quote to delimit strings.  If the data itself contains a single quote you must pass in two single quotes in order for sql server to process the string correctly.

SELECT 'Hello

Output:

Msg 105, Level 15, State 1, Line 1
Unclosed quotation mark after the character string 'Hello
'.
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'Hello
'.

SELECT 'Hello'' World'''

Output:

Hello' World'

SQL Error 104 : ORDER BY items must appear in the select list if the statement contains a UNION, INTERSECT or EXCEPT operator

 Msg 104, Level 16, State 1, Line 11

ORDER BY items must appear in the select list if the statement contains a UNION, INTERSECT or EXCEPT operator.

This error message appears when you try to sort a resultset by an element that is not contained in the SELECT list and the statement contains a UNION-, INTERSECT-, or EXCEPT operator.

For Example, suppose you are fetching FirstName and LastName columns and you want to generate a list of these names where the first name and last name are concatenated together to form the full name and sort the output by the LastName column:

Use AdventureWorks2017
Go

SELECT [FirstName] +' '+ [LastName] AS [FullName]
FROM Person.Person
where FirstName='Adam'
UNION
SELECT [FirstName] +' '+ [LastName] AS [FullName]
FROM Person.Person
where FirstName='Ben'
ORDER BY [LastName]

Output:

Msg 104, Level 16, State 1, Line 11

ORDER BY items must appear in the select list if the statement contains a UNION, INTERSECT or EXCEPT operator.

Since the LastName column is not part of the output, although it’s part of one of the columns in the SELECT list, the above error will be encountered

To avoid this error, simply remove the ORDER BY clause from your SELECT statement. If you really need to sort it by a particular column, just include that column in your SELECT statement.

Use AdventureWorks2017
Go

SELECT [FirstName] + [LastName] AS [FullName],LastName
FROM Person.Person
where FirstName='Adam'
UNION
SELECT [FirstName] + [LastName] AS [FullName],LastName
FROM Person.Person
where FirstName='Ben'
ORDER BY [LastName]

If you don’t want the extra column in your output, you can put the SELECT statement with the UNION in a sub-query, as follows:

Use AdventureWorks2017
Go

SELECT [FullName] 
FROM (
SELECT [FirstName] +' '+ [LastName] AS [FullName],LastName
FROM Person.Person
where FirstName='Adam'
UNION
SELECT [FirstName] +' '+ [LastName] AS [FullName],LastName
FROM Person.Person
where FirstName='Ben'
) A
ORDER BY [LastName]

The %S_MSG that starts with '%.*ls' is too long. Maximum length is %d

Error Message:

Msg 103, Level 15, State 4, Line 1
The %S_MSG that starts with '%.*ls' is too long. Maximum length is %d.

This error message appears when you try to use an identifier name that exceeds the maximum allowed length.

Example:

select
    "Overview of Park Central New York - New York
    This hotel is making improvements.
        The property is undergoing renovations. The following areas are affected:
        Bar/lounge
        Business center
        Select guestrooms

    Every effort will be made to minimize noise and disturbance.
    Occupying a Beaux Arts building dating to 1927, Park Central New York Hotel is within a block of famed concert venue Carnegie Hall and within a 5-minute walk of Manhattan’s world-renowned Broadway theater district. Prefer the great outdoors to the Great White Way? Central Park is just 3 blocks from the hotel. There, you can rent a rowboat at the lake, play a game of tennis, or visit the Central Park Zoo. The international boutiques and flagship department stores of Fifth Avenue start within a 10-minute walk of the hotel. For travel to sights farther afield, there are 7 subway lines located within 3 blocks of the Park Central.
    The hotel has a snack bar for guests' convenience, and coffee and tea in the lobby.
    Retreat to your guestroom and sink into a bed with a pillowtop mattress and down comforter and pillows.
	 Need to check email or finish up some work? You’ll find a desk with an ergonomic chair and wireless high-speed Internet access (surcharge).
	  Unwind with a video game (surcharge) on the flat-panel HDTV."
	as hotel

Output:

Msg 103, Level 15, State 4, Line 3

The identifier that starts with 'Overview of Park Central New York - New York

    This hotel is making improvements.

        The property is undergoing renovat' is too long. Maximum length is 128.

Errors of the Severity Level 15 are generated by the user and can be fixed by the SQL Server user. The statement cannot be executed this way. You must choose a short name with up to 128 characters.

If you don't want to change double quotes to single quotes add following two lines in the begining of the script which will help in exectin the script.

SET QUOTED_IDENTIFIER OFF
select
    "Overview of Park Central New York - New York
    This hotel is making improvements.
        The property is undergoing renovations. The following areas are affected:
        Bar/lounge
        Business center
        Select guestrooms

    Every effort will be made to minimize noise and disturbance.
    Occupying a Beaux Arts building dating to 1927, Park Central New York Hotel is within a block of famed concert venue Carnegie Hall and within a 5-minute walk of Manhattan’s world-renowned Broadway theater district. Prefer the great outdoors to the Great White Way? Central Park is just 3 blocks from the hotel. There, you can rent a rowboat at the lake, play a game of tennis, or visit the Central Park Zoo. The international boutiques and flagship department stores of Fifth Avenue start within a 10-minute walk of the hotel. For travel to sights farther afield, there are 7 subway lines located within 3 blocks of the Park Central.
    The hotel has a snack bar for guests' convenience, and coffee and tea in the lobby.
    Retreat to your guestroom and sink into a bed with a pillowtop mattress and down comforter and pillows.
	 Need to check email or finish up some work? You’ll find a desk with an ergonomic chair and wireless high-speed Internet access (surcharge).
	  Unwind with a video game (surcharge) on the flat-panel HDTV."
	as hotel
	SET QUOTED_IDENTIFIER ON

This will ecxecute witout error.

SQL Error 101 - Query not allowed in Waitfor

If you’re using the WAITFOR statement in SQL Server, and you get the following error, it’s probably because you’re providing a query as its argument.

Msg 101, Level 15, State 1, Line 2

Query not allowed in Waitfor.

The WAITFOR statement doesn’t accept queries for its "wait for" period. It only accepts a specific time or an interval.

Actually, it does accept RECEIVE statements, but this is only applicable to Service Broker messages, so if you’re not using Service Broker messages, the above error is quite self-explanatory.

Example:

When you incorrectly trying to use a query in WAITFOR statement

waitfor(SELECT firstname from [dbo].[Employee])

Result:

Msg 101, Level 15, State 1, Line 2

Query not allowed in Waitfor.

To overcome this issue, you’ll need to provide an actual time or a time delay.

For example, this will work.

WAITFOR DELAY '00:00:10';SELECT firstname from [dbo].[Employee]

The maximum recursion has been exhausted before statement completion

OPTION (MAXRECURSION n);

While executing above statement with CTE (common table expression) or recursive CTE, error came like below

Msg 530, Level 16, State 1, Line 1

The statement terminated. The maximum recursion %d has been exhausted before statement completion.

Let's see the below example where try to control the recursion (think stack overflow in code) with MAXRECURSION as a query option that will limit the number of recursive calls.

WITH yearsAgo
(
myYear
)
AS
(
-- Base Case
SELECT DATEPART(year , GETDATE()) AS myYear
UNION ALL
-- Recursive Section
SELECT yearsAgo.myYear - 1
FROM yearsAgo
WHERE yearsAgo.myYear >= 2002
)
SELECT * FROM yearsAgo
OPTION (MAXRECURSION 10);

Output:

Msg 530, Level 16, State 1, Line 1

The statement terminated. The maximum recursion 10 has been exhausted before statement completion.

A Cursor with the name already exists

 While executing cursor inside Stored procedure it is throwing error:

A cursor with the name 'cursorName' already exists

declare cursorName CURSOR FOR
        select ...;

    open cursorName;

        Body of cursor...
        close cursorName;
        deallocate cursorName;

This is because we are using global cursor that will be defined each time you are calling this procedure and give you the same error.

Define a local cursor. Just put the keyword LOCAL after CURSOR

This will look like

declare cursorName CURSOR LOCAL FOR
...
This will resolve the issue.

error while passing Datetime parameter in dynamic sql command

 I am using a dynamic query wherein I want to use the variable which holds the datetime, whenever I execute the query it says cannot convert datetime from string

Lets see using below example:

Declare @OrderDate Datetime
Declare @sql varchar(max)
set @OrderDate='2010-12-29'
set @sql='Select SalesAmount,taxamt,totalproductcost,orderdate from [dbo].[FactInternetSales]
where orderdate='''+@OrderDate+''''
exec (@sql)

Output:
Msg 241, Level 16, State 1, Line 8
Conversion failed when converting date and/or time from character string.

To resolve this we need to convert datetime variable. It looks like below.

Declare @OrderDate Datetime
Declare @sql varchar(max)
set @OrderDate='2010-12-29'
set @sql='Select SalesAmount,taxamt,totalproductcost,orderdate from [dbo].[FactInternetSales]
where orderdate='''+CONVERT(VARCHAR(10),@OrderDate, 101)+''''
exec (@sql)

SSIS Error : The value violated the integrity constraints for the column

 While Executing SSIS package getting error "The value violated the integrity constraints for the column" .

It means that it tried to insert null value into the non nullable column in the database. What you want to do is check the table definition of the destination table for any integrity constraints like NOT NULL or PRIMARY KEY.

You can distinguish from whether violation due to Not NULL or Primary Key by looking at Error column.

Generally if Error column is 0 (zero) then it is a PK violation otherwise it is a Not NULL key violation.

So need to analyze source data and correction if required.

'CONCAT' is not a recognized built-in function name in SQL Server 2008

 CONCAT function newly introduced from SQL Server 2012.

SQL Server 2008 will throw an error 

'CONCAT' is not a recognized built-in function name

So in SQL Server 2008 we can use "+" operator to concat the 2 or more strings. It is always suggested to use cast your columns before using them. This operator will throw an error if the first operand is a number since it thinks will be adding and not concatenating

cast('data1' as varchar) + cast('data2' as varchar) + cast('data3' as varchar)

There are few differences between "CONCAT" and "+" operator

SELECT 'A' + 'B' + 'C' 
SELECT CONCAT('A', 'B', 'C') 
SELECT 'A' + 'B' + NULL 
SELECT CONCAT('A', 'B', NULL)

output will be ABC,ABC,NULL,AB respectively.

The value of the Color property for the textrun which is not a valid Color in SSRS

While setting up background color of a tablix cell or font, below error is generated.

The value of the Color property for the textrun ‘Textbox1.Paragraphs[0].TextRuns[0]’ is “#00ffffff” which is not a valid Color.

Textbox does not support (partial) transparency color.

Need to select valid RGB color values. (instead of selecting " No Color")

Could not update list of Fields for the query in SSRS

While Connecting to the stored procedure from the SSRS reports getting below error.

"Could not create a list of fields for the query. Verify that you can connect to the data source and that your query syntax is correct."

An item with the same key has already been added.

I have added here sample stored procedure created.

create procedure products
as
begin
    with #ProductCat as(
        select DPC.EnglishProductCategoryName,DPC.FrenchProductCategoryName,dpsc.EnglishProductSubcategoryName,DPSC.FrenchProductSubcategoryName
        from DimProductCategory DPC
        join DimProductSubcategory DPSC on DPC.ProductCategoryKey=DPSC.ProductCategoryKey
    )
select PC.EnglishProductCategoryName as Product_Category,PC.FrenchProductCategoryName as Product_Category
,PC.EnglishProductSubcategoryName as Product_SubCategory
from #ProductCat PC
end

Two column names are having in the output with same name.
unfortunately SSMS will not throw any error for this.
Make proper naming while naming the dataset column names.

The maximum recursion 100 has been exhausted before statement completion

When running the report getting below error while report is trying to execute the SQL script

An error has occurred during report processing. (rsProcessingAborted)

Cannot read the next data row for the dataset .... (rsErrorReadingNextDataRow)

The statement terminated. The maximum recursion 100 has been exhausted before statement completion.

max recursion error is also a SQL error. Add below code at the end of the script which will resolve the issue.

Select 

-----------------

From Employee

where

--------------

option (maxrecursion 0)