mssqltips logo

SQL Server work around for Msg 2714 There is already an object named #temp in the database

By:   |   Updated: 2009-06-02   |   Comments (3)   |   Related: More > T-SQL

Problem
I was working on a development project and was getting these intermittent error messages like "Msg 2714... There is already an object named 'pk_#PackageWeight' in the database." which plagued a group of stored procedures that create seemingly simple reports that use temporary tables. After a little digging I found the problem and this tip offers a solution to work around this error message.

Solution
The problem is the requirement to uniquely name all entries in sys.objects, also known as sysobjects in SQL Server 2000.   Temporary tables get unique names because SQL Server modifies the name of the table before adding it to sys.objects.  It doesn't provide any help when you create a primary key on the temporary table and give it a name yourself.  You must force the creation of a unique name for the primary key in order to avoid message 2714.

Lets start by taking a look at the way that SQL Server creates entries in sys.objects for temporary tables.  This script creates a simple temporary table and adds a primary key to the table.  It then queries the related entries in sys.objects:

use adventureworks
go
create table #foo (a int not null, b char(20))
go
alter table #foo add constraint PK_#foo
 primary key clustered (a)
go
create index IX_#foo_b on #foo (b)
go
select object_id, parent_object_id, type, name
   from tempdb.sys.objects 
   where name like '%foo%'
go

These are the results of the final query in that script.  I've shortened the name of the temporary table at the ellipsis so that you can see how SQL Server makes it unique.

object_id parent_object_id type name
--------- ---------------- ---- -----------------------
805577908                0 U    #foo___..._00000000001E
821577965        805577908 PK   PK_#foo

Primary keys are unique among indexes for requiring an entry in sys.objects. Indexes other than the primary key such as IX_#foo_b don't have entries in sys.objects.  However both the primary key and other indexes have entries in sys.indexes as seen here:

select object_id, Index_id, type, type_desc, name 
    from tempdb.sys.indexes 
    where object_id=object_id('tempdb..#foo') 
go
object_id Index_id type type_desc     name
--------- -------- ---- ------------- ----------
805577908        1    1 CLUSTERED     PK_#foo
805577908        2    2 NONCLUSTERED  IX_#foo_b

The following stored procedure is very much like the ones that caused the problem that I ran into.  It creates a temporary table and inserts the contents into the table.  It then creates a row that represents data from the rows that have null CarrierTrackingNumbers, so that those rows can be removed before CarrierTrackingNumber can be made to be non-nullable and the primary key created. 

In this case the primary key can't be created until the rows with null CarrierTrackingNumbers are removed.  Another reason that primary keys are created after the table is for performance.  Here's the original stored procedure:

USE [AdventureWorks]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create proc [dbo].[usp_Product_Shipping_Analysis] as
set nocount on
set xact_abort on
if object_id('tempdb..#PackageWeight') is not null 
               drop table #PackageWeight
create table #PackageWeight (
     CarrierTrackingNumber nvarchar(25)
   , total_items int not null
   , Weight decimal(19,2) null
   )
insert into #PackageWeight
   (CarrierTrackingNumber, total_items, Weight)
select sod.CarrierTrackingNumber
     , sum(sod.OrderQty) as total_items
     , sum(sod.OrderQty * p.Weight) as Weight
   from Sales.SalesOrderDetail sod
   inner join Production.Product p
      on sod.ProductID = p.ProductID
   group by sod.CarrierTrackingNumber
-- consolidate the items without tracking numbers
insert into #PackageWeight
   (CarrierTrackingNumber, total_items, Weight)
 select '___<No Tracking Number>'
      , sum(total_items) as total_items
      , sum(Weight) as Weight
      from #PackageWeight
        where CarrierTrackingNumber is null
-- remove any null entries
delete from #PackageWeight 
   where CarrierTrackingNumber is null
-- make #PackageWeight not null so it can be in the PK 
alter table #PackageWeight 
   alter column CarrierTrackingNumber varchar(25) not null
              
alter table #PackageWeight 
    add constraint pk_#PackageWeight
 primary key clustered (CarrierTrackingNumber)
create index IX_#PackageWeight_weight
 on #PackageWeight (weight desc)
 
-- Do some other slow processing  
waitfor delay '00:01:00'
  
select * from #PackageWeight order by Weight desc

To make it easier to generate message 2714 there is a WAITFOR statement near the end of the procedure that stands in for some other type of long running processing that a stored procedure might perform.  This isn't required, but makes it easier to recreate the problem in a test environment. 

Now open two SSMS queries and execute the SP.  Here's the first query:

exec usp_Product_Shipping_Analysis
go

Now the second query, which immediately runs into a problem:

exec usp_Product_Shipping_Analysis
go
Warning: Null value is eliminated by an aggregate or other SET operation.
Msg 2714, Level 16, State 4, Procedure usp_Product_shipping_analysis, Line 43
There is already an object named 'pk_#PackageWeight' in the database.
Msg 1750, Level 16, State 0, Procedure usp_Product_shipping_analysis, Line 43
Could not create constraint. See previous errors.

If there is only one user executing the procedure, as might be typical during development, there's no problem.  The problem occurs when more than one user executes the procedure simultaneously.  You might not encounter the problem every day, but sooner or later you'll run into it and then find it very difficult to reproduce.

There are two potential solutions to the problem of creating a unique name for the primary key.  The easiest would be to have SQL Server generate a unique name for you by including the primary key in the CREATE TABLE statement such as this one:

CREATE TABLE #PackageWeight (
     CarrierTrackingNumber nvarchar(25)
         primary key clustered
   , total_items int not null
   , Weight decimal(19,2) null
   )
go
Select  object_id, parent_object_id, type, name
   from tempdb.sys.objects
   where name like '%PackageWeight%'
go
 object_id parent_object_id type name
---------- ---------------- ---- ---------------------------------
1029578706                0 U    #PackageWeight__...__000000000027
1045578763       1029578706 PK   PK__#PackageWeight____3E52440B

SQL Server has added a unique string to the end of the primary key for #PackageWeight so that there are no name collisions with other instances of the same code running in other sessions.  Unfortunately, this solution isn't well suited to usp_Product_Shipping_Analysis because it must remove the null rows before creating the primary key. It's possible to get around this but at the cost of development effort. 

An alternative solution is to create a uniquely named index by adding a UniqueIdentifer value or GUID to the end of the primary key name and using dynamic SQL to create the PK.  Here's the alternative stored procedure.

USE [AdventureWorks]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE proc [dbo].[usp_Product_Shipping_Analysis_Unique_PK] as
set nocount on
set xact_abort on
if object_id('tempdb..#PackageWeight') is not null 
               drop table #PackageWeight
create table #PackageWeight (
     CarrierTrackingNumber nvarchar(25)
   , total_items int not null
   , Weight decimal(19,2) null
   )
insert into #PackageWeight
   (CarrierTrackingNumber, total_items, Weight)
select sod.CarrierTrackingNumber
     , sum(sod.OrderQty) as total_items
     , sum(sod.OrderQty * p.Weight) as Weight
   from Sales.SalesOrderDetail sod
   inner join Production.Product p
      on sod.ProductID = p.ProductID
   group by sod.CarrierTrackingNumber
-- consolidate the items without tracking numbers
insert into #PackageWeight
   (CarrierTrackingNumber, total_items, Weight)
 select '___<No Tracking Number>'
      , sum(total_items) as total_items
      , sum(Weight) as Weight
      from #PackageWeight
        where CarrierTrackingNumber is null
-- remove any null entries
delete from #PackageWeight 
   where CarrierTrackingNumber is null
-- make #PackageWeight not null so it can be in the PK 
alter table #PackageWeight 
   alter column CarrierTrackingNumber varchar(25) not null
              
declare @sql nvarchar(max)
set @sql ='alter table #PackageWeight 
    add constraint [PK_#PackageWeight_'
     + convert(varchar(64), newid()) +
     '] primary key clustered  
       (CarrierTrackingNumber) '
exec sp_executesql @[email protected]
create index IX_#PackageWeight_weight
 on #PackageWeight (weight desc)
 
-- Do some other slow processing  
waitfor delay '00:01:00'
  
select * from #PackageWeight order by Weight desc
 

Note that the square brackets around the primary key name are required, because uniqueidentifiers  include dashes when converted to varchar. Execute this procedure in two sessions and you'll find that both return the expected result:

exec usp_Product_Shipping_Analysis_Unique_PK
go

The same script goes into the second session:

exec usp_Product_Shipping_Analysis_Unique_PK
go

 

Next Steps

  • Anytime you have long running code that creates a named primary key be alert for the possibility of a name collision on the primary key.  Either have SQL Server create the primary key name or if that doesn't work well for you, be sure to create the primary key with a unique name.

  • Check out this tip for related information about temporary tables and when to use a table variable instead: Differences between SQL Server temporary tables and table variables



Last Updated: 2009-06-02


get scripts

next tip button



About the author
MSSQLTips author Andy Novick Andy Novick is a SQL Server Developer in the Boston area with 25 years of database and application development experience.

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.





Tuesday, January 03, 2012 - 5:46:09 PM - Ray Back To Top

Nice post Andy.

Just one clarification.  Any Unique Constraint, not just a Primary Key is a "system object" and so must appear in sys.Objects.  I doubt that very many of us will need a temp table that has a Primary Key and one or more other Unique Constraints. ;)

 


Friday, September 09, 2011 - 11:04:41 AM - bryant Back To Top

If SQL Server makes each table name unique, why do you need to include the following code?

if object_id('tempdb..#PackageWeight') is not null
               drop table #PackageWeight


Tuesday, June 02, 2009 - 8:42:38 AM - tranfamily35 Back To Top

How about create temp table using ##Tmp instead of single pound?



download

























get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools