Problem Statement: One of my colleague send me a database backup and asked me to help on a SQL query that was taking long time to execute. I restored the database on my local system and executed the query but that query was failing and I was getting below error message while executing the query.
Msg 10734, Level 15, State 1, Line 5
Variable assignment is not allowed in a statement containing a top level UNION, INTERSECT or EXCEPT operator.
I called my colleague and told him that your query is returning error message then he told me that he was using SQL server version 2005 while I was using SQL 2012 Version. I am not going to share the exact query here but I will write a similar query so that above error message can be reproduced.
Solution: Actually this problem occurred due to different version of SQL Server. Since Microsoft is upgrading the SQL Server versions on a regular interval of time and in newer version few things are getting obsolete from SQL Server, as an alternative, Microsoft has given a provision to change the compatibility level of SQL Server, so that, those things which got obsoleted from the newer version can be supported by setting the database compatibility to earlier version. We will create two tables “Employee” and “Dept” and insert few rows into both tables using below SQL Statements.
CREATE TABLE Employee(Eid int,Ename varchar(100)) Go CREATE TABLE Dept(DeptId int,DeptName varchar(50),Eid int) Go INSERT [dbo].[Employee] ([Eid], [Ename]) VALUES (101, 'Neeraj') INSERT [dbo].[Employee] ([Eid], [Ename]) VALUES (102, 'Ramesh') INSERT [dbo].[Employee] ([Eid], [Ename]) VALUES (103, 'Vijay') INSERT [dbo].[Employee] ([Eid], [Ename]) VALUES (104, 'Praveen') INSERT [dbo].[Employee] ([Eid], [Ename]) VALUES (105, 'Akshay') Go INSERT [dbo].[Dept] ([DeptId], [DeptName], [Eid]) VALUES (201, 'IT', 101) INSERT [dbo].[Dept] ([DeptId], [DeptName], [Eid]) VALUES (202, 'Admin', 103) INSERT [dbo].[Dept] ([DeptId], [DeptName], [Eid]) VALUES (203, 'HR', 105)
Below is the Query which was throwing the error which is mentioned earlier in this post.
declare @x int select @x=eid from Employee union all select @x=deptid from dept
Lets change the compatibility level using below T-SQL Command that resolved the issue for me.
ALTER DATABASE MyDB SET COMPATIBILITY_LEVEL = 90
Lets run the same query again and see the difference.
Note: We can set only backward compatibility of the database, for example, if we are using SQL server 2012 then we can only set compatibility lower than 2012 version not the higher version.
We can also change the database compatibility using below System stored procedure.
Execute sp_dbcmptlevel 'mydb', 110
I hope this article will also help you in future.
2,006 total views, 5 views today