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

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 @stmt=@sql

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

Resources

Community

Subscribe