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.