Violation of PRIMARY KEY constraint when used with temp table

• Steve

  SSCommitted

  Points: 1844

  More actions

  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

• Prasad Bhogadi

  SSCrazy Eights

  Points: 9235

  More actions

  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