[MSSQL] Tijdelijke tabellen toch niet zo tijdelijk?

[PrinsEdje80]

Als ik een tijdelijke tabel (##-versie) aanmaak met dezelfde naam in twee stp's, maar met een andere tabel-definitie en ik drop de tabel indien deze bestaat voordat ik de tabel opnieuw maak, dan krijg ik een foutmelding dat een kolom van de nieuwe definitie niet bestaat. Zie output aan einde.

Als ik alleen aan stp1 de regel

SQL:

DROP TABLE ##x

toevoeg aan het einde, dan krijg ik geen foutmelding. Kan iemand mij dit uitleggen? Ik snap er niets van, namelijk .

Als ik deze regel aan beide stp's toevoeg, dan krijg ik ook geen foutmelding...

stp1:

SQL:

CREATE PROC [dbo].[stp_33_test_x]
AS
BEGIN
    SET NOCOUNT ON
    BEGIN TRY
        DROP TABLE ##x
    END TRY
    BEGIN CATCH
        IF NULL=NULL PRINT NULL--PRINT 'Iets ging fout bij y'
    END CATCH

    IF OBJECT_ID('tempdb..##x') IS NOT NULL
        DROP TABLE ##x
    ELSE
        IF NULL=NULL PRINT NULL--PRINT 'Iets ging fout bij y'

    CREATE TABLE ##x (x INT NOT NULL)
    INSERT INTO ##x
            (x)
    VALUES
            (0)
END

stp2:

SQL:

CREATE PROC [dbo].[stp_33_test_y]
AS
BEGIN
    SET NOCOUNT ON
    BEGIN TRY
        DROP TABLE ##x
    END TRY
    BEGIN CATCH
        IF NULL=NULL PRINT NULL--PRINT 'Iets ging fout bij y'
    END CATCH

    IF OBJECT_ID('tempdb..##x') IS NOT NULL
        DROP TABLE ##x
    ELSE
        IF NULL=NULL PRINT NULL--PRINT 'Iets ging fout bij y'

    CREATE TABLE ##x (y INT NOT NULL)
    INSERT INTO ##x
            (y)
    VALUES
            (0)
END

Test-situatie:

SQL:

DECLARE @cnt BIGINT = 0
DECLARE @max BIGINT = 1000

BEGIN TRY
    WHILE (@cnt < @max) BEGIN
        EXEC stp_33_test_x
        EXEC stp_33_test_y

        SET @cnt = @cnt + 1
    END
END TRY
BEGIN CATCH
    DECLARE @errornumber VARCHAR(MAX)
    DECLARE @error_severity VARCHAR(MAX)
    DECLARE @error_state VARCHAR(MAX)
    DECLARE @error_procedure VARCHAR(MAX)
    DECLARE @error_line VARCHAR(MAX)
    DECLARE @error_message VARCHAR(MAX)

    SET @errornumber=LTRIM(STR(ISNULL(ERROR_NUMBER(), 0)))
    SET @error_severity=LTRIM(STR(ISNULL(ERROR_SEVERITY(), 0)))
    SET @error_state=LTRIM(STR(ISNULL(ERROR_STATE(), 0)))
    SET @error_procedure=ISNULL(ERROR_PROCEDURE(), '')
    SET @error_line=LTRIM(STR(ISNULL(ERROR_LINE(), 0)))
    SET @error_message=ISNULL(ERROR_MESSAGE(), '')

    -- Melding op scherm zetten
    PRINT '**FOUT**'
    PRINT 'Error number         : ' + @errornumber
    PRINT 'Error severity       : ' + @error_severity
    PRINT 'Error state          : ' + @error_state
    PRINT 'Error procedure      : ' + @error_procedure
    PRINT 'Error line           : ' + @error_line
    PRINT 'Error message        : ' + @error_message

END CATCH

Output:

code:

**FOUT**
Error number         : 207
Error severity       : 16
Error state          : 1
Error procedure      : stp_33_test_y
Error line           : 19
Error message        : Invalid column name 'y'.

[Feanathiel]

PrinsEdje80 schreef op woensdag 19 februari 2014 @ 15:51:
[...]

code:

BEGIN CATCH 
  IF NULL=NULL PRINT NULL
END CATCH

[...]

Wellicht niet relevant voor het opsporen van de fout, maar je suppressed hier een foutmelding. Null is nooit gelijk aan null als je de equality operator gebruikt. Alleen de IS-operator zorgt ervoor dat 'null is null = True'. Wat is verder het resultaat van OBJECT_ID('tempdb..##x')?

[PrinsEdje80]

Deze is blijkbaar NULL, want ik heb het ook met de stukjes tekst "--PRINT 'Iets ging fout bij y' " gedaan en ik kreeg daar in de output de tekst..

[pedorus]

quote: http://technet.microsoft.com/en-us/library/ms174979.aspx

You can create local and global temporary tables. Local temporary tables are visible only in the current session, and global temporary tables are visible to all sessions. Temporary tables cannot be partitioned.

Prefix local temporary table names with single number sign (#table_name), and prefix global temporary table names with a double number sign (##table_name).
[...]
Global temporary tables are automatically dropped when the session that created the table ends and all other tasks have stopped referencing them. The association between a task and a table is maintained only for the life of a single Transact-SQL statement. This means that a global temporary table is dropped at the completion of the last Transact-SQL statement that was actively referencing the table when the creating session ended.

Waarom denk je dat een global temporary table automatisch gedropt zou worden en waarom gebruik je een global?

[PrinsEdje80]

pedorus schreef op woensdag 19 februari 2014 @ 17:44:
Waarom denk je dat een global temporary table automatisch gedropt zou worden en waarom gebruik je een global?

Dat denk ik dus absoluut niet! Als je kijkt naar mijn code, ik voer een DROP uit op de tabel VOORDAT ik weer een CREATE doe. Echter, de DROP statements worden niet uitgevoerd...
De reden dat ik een global gebruik is even niet noodzakelijk. Ik wil gewoon weten waarom dit zo is .

[Feanathiel]

Ziet er naar uit dat definities gecached worden binnen dezelfde batch (GO). Waarschijnlijk performance redenen. Zie: http://blogs.msdn.com/b/s...tables-in-sql-server.aspx

[PrinsEdje80]

Ik snap wat je bedoeld. Ik heb de link bekeken.
Het is namelijk niet één batch, maar twee stp's (in mijn geval). In de situatie waarin ik het probleem als eerste tegenkwam, heb ik alles vervangen door andere tijdelijke tabellen (#, dus enkele hash). Dat werkt dan wel weer feilloos. En dat is logisch, vanwege het feit dat de scope van een #-tabel namelijk een stp is.

Dan ben ik nieuwsgierig tussen de overeenkomst van een batch en een stp.

BTW, als ik achter

SQL:

        EXEC stp_33_test_x
        EXEC stp_33_test_y

de optie

SQL:

WITH RECOMPILE

doe (om caching te voorkomen, althans vermoed ik), dan werkt dit niet...

[ Voor 1% gewijzigd door PrinsEdje80 op 21-02-2014 11:59 . Reden: Kleine verduidelijking ]