temp table syntax

  • On SQL 2000, I have several SPs which contain, among other stuff, lines like this:

    IF EXISTS(SELECT * FROM TEMPDB..SYSOBJECTS WHERE NAME = '##CHEMPAX_INVENTORY') DROP TABLE [##CHEMPAX_INVENTORY]

    SET @CMD = 'SELECT * INTO [##CHEMPAX_INVENTORY] FROM OPENQUERY(CHEMPAX, ''' + @SQL + ''' )'

    EXEC (@CMD)

    These SPs are used to copy data from a Proggress database to a SQL DB.

    Works fine, but I am trying to move this database from SQL 2000 to SQL 2005 on another server. It's giving me this error when I try to execute the SP:

    Msg 4701, Level 16, State 1, Procedure SP_REFRESH_CHEMPAX_COSTS, Line 30

    Cannot find the object "CHEMPAX_COSTS" because it does not exist or you do not have permissions.

    I don't think it's a permissions error, but I could be wrong. I suspect that the ## is something that doesn't work on 2005, but am googling it and finding nothing. Anyone familiar with changes in temp table syntax that has changed between the two versions?

  • The ## at the beginning of a temp table name designates that the table is a global temporary table.

    Quoting from BOL: Global temporary tables are visible to any user and any connection after they are created, and are deleted when all users that are referencing the table disconnect from the instance of SQL Server. (emphasis mine)

    I suspect the stored procedure which will not execute expects this table to already be in existence, even though it is a temporary table. Also quoting from BOL: If no other user works with this table after you create it, the table is deleted when you disconnect.

    There may be some other procedure which creates it in another session. That session cannot be ended before your procedure starts or the ##table will disappear.

    __________________________________________________

    Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
    Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

  • But that wouldn't explain why this thing works just fine on the old database. It runs successfully every morning.

  • I don't think the problem is your temporary table, look at your error message again:

    Msg 4701, Level 16, State 1, Procedure SP_REFRESH_CHEMPAX_COSTS, Line 30

    Cannot find the object "CHEMPAX_COSTS" because it does not exist or you do not have permissions

    It can't find CHEMPAX_COSTS. Your temporary table is ##CHEMPAX_INVENTORY.

  • I'm sorry for causing confusion. I have several of these jobs. My question was about the # signs, so I copied and pasted the particular one I had highlighted at the time.

    Here's the code for the Costs one:

    USE [RICCA]

    GO

    /****** Object: StoredProcedure [dbo].[SP_REFRESH_CHEMPAX_COSTS] Script Date: 08/24/2009 13:53:26 ******/

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    ALTER PROCEDURE [dbo].[SP_REFRESH_CHEMPAX_COSTS]

    AS

    SET NOCOUNT ON

    DECLARE @SQL VARCHAR(1000)

    DECLARE @CMD VARCHAR(1000)

    SET @SQL =

    '

    SELECT

    "FACILITY",

    "AVERAGE-COST",

    "STANDARD-COST",

    "PROD-PKG-CODE"

    FROM "SQLVIEW"."WHS-PROD-PKG"

    WHERE "System-ID" = ''''DATACOR''''

    AND ACTIVE = 1

    '

    IF EXISTS(SELECT * FROM TEMPDB..SYSOBJECTS WHERE NAME = '##CHEMPAX_COSTS') DROP TABLE [##CHEMPAX_COSTS]

    SET @CMD = 'SELECT * INTO [##CHEMPAX_COSTS] FROM OPENQUERY(CHEMPAX, ''' + @SQL + ''' )'

    EXEC (@CMD)

    IF EXISTS(SELECT TOP 1 * FROM [##CHEMPAX_COSTS])

    BEGIN

    TRUNCATE TABLE CHEMPAX_COSTS

    INSERT INTO CHEMPAX_COSTS SELECT * FROM ##CHEMPAX_COSTS

    END

    IF EXISTS(SELECT * FROM TEMPDB..SYSOBJECTS WHERE NAME = '##CHEMPAX_COSTS') DROP TABLE [##CHEMPAX_COSTS]

    SET NOCOUNT OFF

    GRANT EXECUTE

    ON [DBO].[SP_REFRESH_CHEMPAX_COSTS]

    TO [Public]

    and here's the message when I try to manually execute that SP on the new DB server:

    Msg 4701, Level 16, State 1, Procedure SP_REFRESH_CHEMPAX_COSTS, Line 30

    Cannot find the object "CHEMPAX_COSTS" because it does not exist or you do not have permissions.

  • Again, look at the error message. It is not complaining about the temporary table:

    Msg 4701, Level 16, State 1, Procedure SP_REFRESH_CHEMPAX_COSTS, Line 30

    Cannot find the object "CHEMPAX_COSTS" because it does not exist or you do not have permissions.

  • AAAAAUUUUUUGHHHHHHHHH!

    It's OK if I yell here, right?

    Sorry, I am inheriting other people's code and totally didn't catch that there is a temp table and real table with almost the same name.

    So thanks to you, I just imported that one table over from old to new. I re-ran the SP, and it didn't disply an error. It simply shows in the message or results pane a Return Value of 0.

    So I opened up the new table, and it appears to be full of valid data. So I guess that was my problem.

    thanks!

  • Yes, you are permitted to yell. I'm glad we were able to help you find the problem. Sometimes it just takes another set of eyes. Typical Forest and Trees Syndrome.

  • Lynn Pettis (8/24/2009)


    Yes, you are permitted to yell. I'm glad we were able to help you find the problem. Sometimes it just takes another set of eyes. Typical Forest and Trees Syndrome.

    Or as I prefer to state it: hard to read the letters with your head banging on the keyboard.......:)

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • In a word: Doh! :w00t:

    Man, I am really off my game.

    __________________________________________________

    Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
    Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

  • Bob Hovious (8/24/2009)


    In a word: Doh! :w00t:

    Man, I am really off my game.

    You probably got a concussion from banging your head on your keyboard too many times. 😛

  • Quite likely.... and in the end, she solves her own problem. 😛

    __________________________________________________

    Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
    Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

Viewing 12 posts - 1 through 11 (of 11 total)

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