Per instance temporary tables?

Question

Per instance temporary tables?

kmarshall

Ten Centuries

Points: 1001
More actions
November 23, 2005 at 2:39 pm

#89969

I think there must be something about temporary tables that I don't understand.
I have a stored procedure which creates and uses a temporary table. The sp is owned by the dbo user.
If I run the web page that calls the sp, everything is fine. However if I run the web page on two seperate pc's at the same time one of the pages throws an error telling me that the temp table exists already and can't be created.
How do I overcome this? I need to keep the data returned seperate for each users request. Are temp tables a 'per instance' thing?
Thanks for your time.
Windows 2008 Server | SQL Server 2008

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Carl Federl One Orange Chip Points: 25384 More actions · Answer 1

Are the temporary table names prefixed with a two pound signs such as ##mytemptable ? If so, this is a global temporary table which is accessable by multiple connections and a global temporary table's name must be unique for the instance.

It appears that you expect a local temporary table ,which is prefixed with a single pound sign.

SQL = Scarcely Qualifies as a Language

David.Poole SSC Guru Points: 75994 More actions · Answer 2

If you CREATE TABLE #TableName it should be for the current connection only.

If you CREATE TABLE ##TableName then it will persists until the creating connection disconnects.

LinkedIn Profile

kmarshall Ten Centuries Points: 1001 More actions · Answer 3

Yes, I thought that was the case. I am using a single # though:

CREATE TABLE [#ICNReport] (

[ID] [int] IDENTITY (1, 1) NOT NULL ,

[CompanyID] [int] NOT NULL ,

[Town] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[Town_Part] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[PostCode] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[Time_Stamp] [datetime] NULL ,

[Vehicle] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[FrontDoor] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[BackDoor] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[Ignition] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[Latitude] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[Longitude] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[Status_Code_ID] [int] NULL ,

[Value] [int] NULL ,

CONSTRAINT [PK_RP_Temp] PRIMARY KEY CLUSTERED

(

[ID]

&nbsp ON [PRIMARY]

) ON [PRIMARY]

The more I think about it the more I think I have mis-described my problem.

The error I receive is about the creation of the primary key. It says that it already exists. Is there a way to overcome that problem?

Thanks.

Windows 2008 Server | SQL Server 2008

RyanRandall SSChampion Points: 13623 More actions · Answer 4

Take out the "CONSTRAINT [PK_RP_Temp]" bit of your query, and I think it should all work fine.

Ryan Randall

Solutions are easy. Understanding the problem, now, that's the hard part.

kmarshall Ten Centuries Points: 1001 More actions · Answer 5

Thanks for your help. It makes sense when you think about it because primary key constraints are unique within the database I believe.

Windows 2008 Server | SQL Server 2008