These days I’m working on SQL Server upgrade from 2008 R2 to 2012 for one of our project module.
Today while working on it I got blocked while installing a Build. The build was failing with following error:
Error SQL72014: .Net SqlClient Data Provider: Msg 2714, Level 16, State 6, Line 115 There is already an object named ‘#temp’ in the database.
I checked the code and found the line where it was failing:
IF object_id('tempdb.dbo.#temp') > 0 DROP TABLE #temp
I checked this code with SQL Server 2008 R2 and it was working perfectly.
This is a new change done with SQL 2012 version, but this is not mentioned anywhere in MSDN BOL.
So, to make this legacy code work we have to re-factor all such cases, by:
IF object_id('tempdb.dbo.#temp') IS NOT NULL DROP TABLE #temp
Confirmation form Microsoft SQL team blog [CSS SQL Server Engineers]:
“in SQL Server 2012, we made a conscious change to the algorithm so that objectids for user-defined temporary tables would be a particular range of values. Most of the time we use hex arithmetic to define these ranges and for this new algorithm these hex values spill into a specific set of negative numbers for object_id, which is a signed integer or LONG type. So in SQL Server 2012, you will now always see object_id values < 0 for user-defined temp tables when looking at a catalog view like sys.objects.”