We help IT Professionals succeed at work.

Dropping a temp table that is not my own, or killing its owner process (AKA getting the SPID of the temp table's owner process)

jimbobmcgee
jimbobmcgee asked
on
Medium Priority
898 Views
Last Modified: 2012-05-06
We have a SQL Server 2000 legacy app that has a proc that utilises a #table.  That temp table has a named PK constraint which specifies the fill factor.  If the proc is run twice at the same time, an 'object exists'-type error is raised against the PK constraint.

I need a foolproof way to ensure this proc always runs and I figured I could either: specify the fill factor in an unnamed PK; or drop the named PK if it exists.  I don't think I can do the former and the latter is giving me grief.

I can get the OBJECT_ID of the temp table and the PK  If both are not NULL, I know the table is mine and can just drop it.  If the table ID is NULL, I know I have a problem.

I can't change the existing fixed table structure and I can't use normal tables or ##tables because the two concurrent processes can't write into the same table.  I can't bail the 2nd process if the 1st one exists because the cursed business logic suggests that the 2nd process must assume the 1st has bailed.   My only option, I feel, is to try and kill the 1st process.

So I need to get the SPID of the #table owner (from its OWNER_ID) and KILL it.
IF OBJECT_ID('tempdb..tmpPK_account') IS NOT NULL 
	AND OBJECT_ID('tempdb..#deal_account') IS NOT NULL BEGIN
	-- i own the table so drop it
	DROP TABLE #deal_account
END
 
IF OBJECT_ID('tempdb..tmpPK_account') IS NOT NULL 
	AND OBJECT_ID('tempdb..#deal_account') IS NULL BEGIN
	-- i do not own the table so kill the owner
	
	-- this is the temp table object info
	SELECT * FROM tempdb..sysobjects WHERE id = (
		SELECT parent_obj 
		FROM tempdb..sysobjects 
		WHERE id = OBJECT_ID('tempdb..tmpPK_account')
	)
 
	DECLARE @spid INT, @kill VARCHAR(32)
 
	-- ...FILL IN THE BLANKS...
 
	SET @kill = 'KILL ' + CAST(@spid AS VARCHAR)
	EXEC (@kill)
END
 
-- ...later on...
CREATE TABLE [#Deal_Account] 
(
	[PARENT_ROLE] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[ROLE] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[ACCOUNT] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[DEAL] [varchar] (252) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[DEAL_LINE] [varchar] (252) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[id] [int] IDENTITY (0, 1) NOT NULL,
	CONSTRAINT [tmpPK_account] PRIMARY KEY NONCLUSTERED ([id]) 
		WITH FILLFACTOR = 90 
		ON [PRIMARY] 
)

Open in new window

Comment
Watch Question

Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009
Commented:
please try this syntax:
CREATE TABLE [#Deal_Account] 
(
        [PARENT_ROLE] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
        [ROLE] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
        [ACCOUNT] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
        [DEAL] [varchar] (252) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
        [DEAL_LINE] [varchar] (252) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
        [id] [int] IDENTITY (0, 1) NOT NULL,
        PRIMARY KEY NONCLUSTERED ([id]) 
                WITH FILLFACTOR = 90 
                ON [PRIMARY] 
)

Open in new window

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
Commented:
Not sure if you can trace the SPId and kill it, but an ugly alternative would be to find the original pk constraint and rename it as follows -

DECLARE @NewPKName varchar(50)
IF      OBJECT_ID('tempdb..pk_temptable', 'PK') IS NOT NULL
BEGIN
      SELECT @NewPKNAME = 'pk_temptable_' + REPLACE(CONVERT(varchar(20),GETDATE(), 114), ':','')
      EXEC sp_rename 'pk_temptable', @NewPKName
END

Author

Commented:
I had to make a minor change to the reb73's rename method, i.e.

EXEC tempdb..sp_rename 'tmpPK_account', 'OBJECT'
...otherwise I got one of the following errors:
  • No item by the name of 'tmpPK_a' could be found in the current database 'BMS_UK_STAR', given that @itemtype was input as '(null)'No item by the name of 'tmpPK_a' could be found in the current database 'BMS_UK_STAR', given that @itemtype was input as '(null)'
  • Either the parameter @objname is ambiguous or the claimed @objtype (OBJECT) is wrong.
In the end I decided to go with the unnamed constraint, as per angelIII.  Looking back at the BOL, I can clearly see that it is possible to specify a table-level constraint without a name and that I was just reading it wrong...

That said, both approaches appear to work, so thanks to both of you.
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.