Post reply

Violation of PRIMARY KEY constraint when used with temp table

  • September 28, 2006 at 8:04 am

    #117014

    Hello All

    I am having difficulty when using a primary key in a temp table.  Your help would be appreciated.

    Here is the skinny;

    SQL 2005

    This is a sample of a stored proc that I am running.

    Create Proc DoSomeStuff

    As

    Select Top 0 * Into #aView From [aView];

    Alter Table #aView Add Constraint pk_aViewPKName Primary Key Clustered(col1, col2);

    'Do some stuff with #aView'

    Drop Table #aView

    GO

    Usually it runs just fine.  However, if there is an error during the 'Do some stuff with #aView' steps, it seems like the #aView table does not get dropped after leaving the scope of the stored proc because when I rerun the proc after receiving an error I get the following;

    Msg 2627, Level 14, State 1,

    Violation of PRIMARY KEY constraint 'pk_aViewPKName '. Cannot insert duplicate key in object 'dbo.#Q2_HostAccountData'.

    This error continues to happen until I stop and restart SQL Server after which procedure runs normally without any problem.

    Suggestions?

    Problems with my procedure?

    Thanks in advance

    Steve

  • September 28, 2006 at 8:49 am

    #663024

    Include IF EXISTS DROP, CREATE statement when you are creating your temp table inside the script, however you should not really get the error on PK violation as the scope of the Temp table defined inside a sproc is available only till the SP execution is active.

     

    Prasad Bhogadi
    www.inforaise.com

Viewing 2 posts - 1 through 1 (of 1 total)

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