Query execution failed: Table 'tmpStudyParticipant' already has a primary key defined…

swshurts 2013-04-16 14:06:59

While trying to get the execution plan for a stored procedure, I get the above error. I drop these keys prior to truncating the tables, then reload the tables and add the keys back. This should not generate an error in the execution plan, should it? If so, why?

arvindravish 2013-04-16 14:20:30
Can you share the drop script used prior to truncating the tables?
Aaron Bertrand 2013-04-16 14:22:06
Are you saying when you run this stored procedure manually it works fine, but when you try to generate a plan in Plan Explorer you get this error? Are you trying to generate an estimated plan or an actual plan? What happens when you try to generate the same plan in Management Studio? I have scanned through the plan (it's rather large) and see your truncates, but I don't see any ALTER TABLE commands. Can you explain further or point out exactly how the keys are dropped and re-created as part of this process?
swshurts 2013-04-16 14:27:40
I was saying that, but based on the analysis of the SQL Sentry Plan Explorer, I got carried away when it suggested that the indices were a bottleneck when I was loading the temp tables. So, I decided to drop the primary keys, along with the indices. Bad idea…

I have converted the primary keys on the temporary tables to indices and it works much better…

Thanks for your responses.
,Never mind… I should be using indexes for the table.