Tag: errors

ORDER BY items must appear in the select list if the statement contains a UNION, INTERSECT or EXCEPT operator (SQL Server)

Posted on by Ian

If you’re running a query in SQL Server, and you get the following error…

Msg 104, Level 16, State 1, Line 8
ORDER BY items must appear in the select list if the statement contains a UNION, INTERSECT or EXCEPT operator.

…you should check your SQL statement – you’ve probably omitted a column from your SELECT list.

As the error message implies, you’ll probably only see this error if you’re running a query that contains a UNION, INTERSECT or EXCEPT operator.

Simply adding the column to your SELECT list should fix the problem.

Continue reading →

How to Fix “The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION” in SQL Server

Posted on by Ian

If you’re receiving error Msg 3902, Level 16, which reads “The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION”, it’s probably because you’ve got a stray COMMIT statement.

You could be getting this due to implementing error handling, and forgetting that you’ve already committed or rolled back the transaction elsewhere in your code.

Continue reading →

SQL Server Error 206: Operand type clash

Posted on by Ian

SQL Server error Msg 206, Level 16 is a common error to get when inserting data into a table.

It happens when you’re trying to insert data into a column that is incompatible with the data type you’re trying to insert.

This could happen if you accidentally try to insert data into the wrong column (or even the wrong table). But it could also happen if you incorrectly assume that SQL Server will convert the data for you.

To fix this issue, make sure you’re inserting the correct data type.

Continue reading →

How to Fix “Conversion failed when converting the value to data type” in SQL Server

Posted on by Ian

SQL Server error Msg 245, Level 16 tells us that there was a problem when trying to convert a value to a specific data type.

You’ll get this error if you try to insert the wrong data type into a column.

To fix this issue, make sure the data type of the value you’re trying to insert, matches the column’s type.

Continue reading →

How to Fix “The select list for the INSERT statement contains fewer items than the insert list”

Posted on by Ian

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.

To be more specific, 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.

Continue reading →

How to Fix “ALTER TABLE SWITCH statement failed”

Posted on by Ian

If you’re getting error 4939 while trying to switch a partition in SQL Server, it’s probably because you’re trying to switch to a partition that uses a different filegroup.

One of the requirements of switching partitions is that both the source table or partition, and the target table or partition, must be located in the same filegroup.

Continue reading →