2.2.1.23 Duplicate PK definition - SSC Inoperable on this DB

• 
  Thanks for your post, and apologies for the delay in replying - the "send error report" option actually updates our bugtracker that the developers use, but doesn't raise a ticket with support.
  
  Anyway, to your error. What's happening is that SQL Source Control is picking up two definitions for "pk_t_meter_clean". Looking at your post you mentioned that a table was moved to a different database. Because each database should have its own folder structure, this shouldn't cause any trouble; but it looks like maybe something has been left behind.
  
  The first thing to do is search through your source control repository (using the location the database is linked to) for files that reference the offending object. Hopefully, you'll find two, one of which shouldn't be there. You can then remove the offending file from your repo, and unlink + relink the database, and you should be back up and running.
  
  Not sure which source control system you're using, but to help you find the relevant files quickly, you might want to check out a copy of the repo folder structure to a temporary folder somewhere, then run the findstr command that another user found useful, as in this post

  James B August 08, 2011 13:25

  - 0 +
• 
  Tried:

  findstr /S /C:"pk_t_meter_clean" *.*
  

  from a command prompt at the root of the SSC Tortoise SVN folder structure.
  
  ZERO HITS
  
  Suggestions as to what to search for or additional /<option> settings?
  
  I also just expanded the database folder in the repository and it does NOT reflect what I applied to it on Monday of last week - it looks just like the OLD DB schema. Appears there's a more fundamental corruption caused when I (apparently successfully) applied the schema updates.
  
  Do you suggest unlink-relink? Or what?
  
  NOT a SVN officiando, BTW, merely a User.
  
  Thanks.

  PDinCA August 08, 2011 13:53

  - 0 +
• 
  Hmm, that's odd. So it's not there at all. It may just be stuck in your local working files.
  Can you try unlinking + relinking - that will create a new set of files.

  James B August 08, 2011 13:55

  - 0 +
• 
  Told you I wasn't a SVN person...
  
  Had to do an "Update" in explorer to get the latest versions of everything... :oops:
  
  Rerunning the findstr...

  PDinCA August 08, 2011 14:00

  - 0 +
• 
  Ah, no problem - hopefully it'll crop up now...

  James B August 08, 2011 14:01

  - 0 +
• 
  Cropped up, yes. What to do with the info - do tell...

  U:\EPS_Deployment\xChangePoint\src\Database\SSC\xcp_trans>findstr /S /C:"pk_t_meter_clean" *.*
  Tables\.svn\text-base\dbo.t_meter_clean.sql.svn-base:ALTER TABLE [dbo].[t_meter_clean] ADD CONSTRAINT [pk_t_meter_clean] PRIMARY KEY CLUSTERED  ([location_variable_id], [t_stamp], [input_id], [t_stamp_utc_delta]) ON [PRIMARY]
  Tables\dbo.t_meter_clean.sql:ALTER TABLE [dbo].[t_meter_clean] ADD CONSTRAINT [pk_t_meter_clean] PRIMARY KEY CLUSTERED  ([location_variable_id], [t_stamp], [input_id], [t_stamp_utc_delta]) ON [PRIMARY]
  Views\.svn\text-base\dbo.t_meter_clean.sql.svn-base:CONSTRAINT [pk_t_meter_clean] PRIMARY KEY CLUSTERED  ([location_variable_id], [t_stamp], [input_id], [t_stamp_utc_delta]) ON [PRIMARY]
  Views\dbo.t_meter_clean.sql:CONSTRAINT [pk_t_meter_clean] PRIMARY KEY CLUSTERED ([location_variable_id], [t_stamp], [input_id], [t_stamp_utc_delta]) ON [PRIMARY]
  

  The VIEW no longer exists - I nuked it in the re-architecture. The fact that it's still there is a reflection of the history of the database and is correct. However, it's existence in the annals of history prevents me from reflecting the current DB schema correctly, as reflected in the Tables\ rows.
  
  How does one clean this up?

  PDinCA August 08, 2011 14:07

  - 0 +
• 
  I looked down the views\ folder and at the .svn content. The trigger on the TABLE has been assigned to the defunct VIEW, which never had a trigger as it was a materialized view.
  
  Wires appear crossed during the update where the View deletion was not completed and the object removed from candidature for dependant objects...

  PDinCA August 08, 2011 14:14

  - 0 +
• 
  OK, looking at that, I think the problem is that it's still listed in the views folder. If you deleted the view and committed the change, it should have removed that file. SVN will still handle the history ok, but the actual file should have gone.
  If you want to zip up the whole folder structure and send it across to us I can verify that's definitely it; but otherwise I'd suggest removing the file from the view folder and the unlinking relinking.
  
  *edit*
  Just seen your more recent message. That sounds a bit odd if it's assigned the trigger to the view. It sounds like something went a bit strange, presumably because the objects were named the same, but it's not something I've come across before.

  James B August 08, 2011 14:15

  - 0 +
• 
  DB is actually small in schema but relatively large in data, so I'll gladly zip the SVN folder. Where should I send it?

  PDinCA August 08, 2011 14:19

  - 0 +
• 
  Eamiled to the support @ address with this thread URL as the subject.

  PDinCA August 08, 2011 14:25

  - 0 +
• 
  Thanks, I'll take a look.

  James B August 08, 2011 14:26

  - 0 +
• 
  James, your directives worked fine:

  So, I can get it working simply by deleting the file in the "Views" folder using Tortoise's repo browser then unlinking and relinking. Assuming that the view is no longer required (which sounds like the case based on your email) it should be the quickest way to get things running.

  DB unlinked and relinked and I can now Commit again.
  
  Perhaps it would be valuable to examine how the trigger update was assigned to the wrong object on Commit...? Maybe the object type qualification was missed in the logic...?

  PDinCA August 08, 2011 17:11

  - 0 +
• 
  If you can let me know the steps you took and supply details of the objects involved I can try to replicate the problem here. Once we can reproduce it a fix can be investigated to stop something similar happening in future changes.

  James B August 09, 2011 08:28

  - 0 +
• 
  The process, in simple terms, all under the dbo schema, was:

  DROP Indexed View t_meter_clean, which had a PK of pk_t_meter_clean. DROP the table on which the View was built (only 1 table in the view's SELECT). CREATE table t_meter_clean. ADD PK constraint pk_t_meter_clean. Add TRIGGER to table t_meter_clean. COMMIT to SSC.

  The result:

  View t_meter_clean persisted in SVN. The table was added to SVN. The TRIGGER was added to the VIEW. Any attempt to Commit AFTER the COMMIT above failed due to the presence of the pk_t_meter_clean PK on the VIEW.

  Hope this helps.

  PDinCA August 09, 2011 14:24

  - 0 +
• 
  Just a quick update on this - i've now managed to run through the repro steps supplied in the post above, and I get the same behaviour with it breaking source control.
  I've logged a bug in our system so hopefully it can get looked at in a future release.

  James B August 16, 2011 11:16

  - 0 +

Add comment

Please sign in to leave a comment.