Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

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)

Posted on 2009-02-09
3
Medium Priority
?
852 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

0
Comment
Question by:jimbobmcgee
3 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 1200 total points
ID: 23594404
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

0
 
LVL 25

Assisted Solution

by:reb73
reb73 earned 800 total points
ID: 23594527
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
0
 
LVL 16

Author Comment

by:jimbobmcgee
ID: 23599073
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.
0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Suggested Courses

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question