By: Andy Novick | Comments (3) | Related: > TSQL
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%'
goobject_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
About the author
Andy Novick is a SQL Server Developer in the Boston area with 25 years of database and application development experience.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
