Plateforme Level Extreme
Abonnement
Profil corporatif
Produits & Services
Support
Légal
English
Temp Tables in SP's
Message
 
À
30/06/2003 19:40:58
Information générale
Forum:
Microsoft SQL Server
Catégorie:
Stored procedures, Triggers, UDFs
Divers
Thread ID:
00805519
Message ID:
00805527
Vues:
28
I never used a scenario like yours but it seems that both SPs are not running into the same session. Can you use global temp tables?
CREATE TABLE ##temp1 (xyz char(1))
>We're having a problem with Stored Procedures that access Temp tables ... the problem occurs only on one of our customer's servers (using the exact same database, on a different one of their servers, there are no problems).
>
>We've traced the problem to something to do with the visibility or scope of Temp tables.
>
>Here's an example of what I mean:
>
>SP1 has this code:
>
>    CREATE TABLE #temp1 (xyz char(1))
>
>    EXEC SP2
>
>    SELECT * FROM #temp1
>
>Now, SP2 contains code that uses #temp1 if it exists:
>
>    IF OBJECT_ID('TempDb..#temp1) IS  NOT NULL
>        INSERT INTO #temp1 (xyz) VALUE ('x')
>    ELSE
>        SELECT xyz FROM someothertable
>
>What ends up happening is that SP2 does not recognize that #temp1 has already been created and what we're trying to figure out is why. Something to do possibly with *where* Temp tables get created? Isn't it *always* in TempDb? If not, then where is the setting that dictates that?
>
>TIA,
>~~Bonnie
Éric Moreau, MCPD, Visual Developer - Visual Basic MVP
Conseiller Principal / Senior Consultant
Moer inc.
http://www.emoreau.com
Précédent
Suivant
Répondre
Fil
Voir

Click here to load this message in the networking platform