Avoid getting messages while executing Stored Procedure

  • Hi,

    When i execute an sp am getting the below message.

    "Database name 'tempdb' ignored, referencing object in tempdb."

    In this sp am creating and dropping some temp tables. I understood, because of dropping the temp tables am getting this message. But since it has the loop involved, am getting the message for n number of times.

    Can any one advice me on this how to stop getting this warning kind of message which is no longer useful for me.

    Regards,

    Karthik

    Regards,
    Karthik.
    SQL Developer.

  • Post the code.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • SELECT 1 AS a INTO #tmp;

    GO

    DROP TABLE tempdb..#tmp;

    SELECT 1 AS a INTO #tmp

    GO

    DROP TABLE tempdb.dbo.#tmp

    SELECT 1 AS a INTO #tmp

    GO

    SELECT * FROM tempdb..#tmp

    -- look in messages tab

    Modify your code so it does not fully-qualify references to temp tables. Post your code if you need help with identifying where or how to do that.

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • First, I would just refer to a temp table by its name and not fully qualify it.

    Second, it you're in a loop, you may not need to create and drop the table over and over again. It may be better to create it at the start of the procedure, clear it out at the end of each iteration, then drop it at the end of the procedure. Use "set statistics time on" to examine performance implications.

    I know these are generalities, but I would need to see the code to offer any specific suggestions.

Viewing 4 posts - 1 through 3 (of 3 total)

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