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

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?

Plan1.pesession (261.7 kB)
avatar image By swshurts 21 asked Apr 16, 2013 at 02:06 PM
more ▼
(comments are locked)
avatar image arvindravish Apr 16, 2013 at 02:20 PM

Can you share the drop script used prior to truncating the tables?

avatar image Aaron Bertrand ♦ Apr 16, 2013 at 02:22 PM

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?

10|10000 characters needed characters left

1 answer: sort voted first

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.

avatar image By swshurts 21 answered Apr 16, 2013 at 02:27 PM
more ▼
(comments are locked)
10|10000 characters needed characters left
Your answer
toggle preview:

Up to 50 attachments (including images) can be used with a maximum of 209.7 MB each and 209.7 MB total.

Follow this question



asked: Apr 16, 2013 at 02:06 PM

Seen: 592 times

Last Updated: Apr 16, 2013 at 02:29 PM