Red Gate forums :: View topic - Duplicate Definition Error
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Source Control 3
SQL Source Control 3 forum

Duplicate Definition Error

Search in SQL Source Control 3 forum
Post new topic   Reply to topic
Jump to:  
Go to page 1, 2  Next
Author Message
conradakunga



Joined: 26 Nov 2012
Posts: 1
Location: Kenya

PostPosted: Mon Nov 26, 2012 3:19 pm    Post subject: Duplicate Definition Error Reply with quote

Version 3.1.0.4829

Greetings,

I am suddenly getting the following exception when trying to check in.

Code:
RedGate.SQLSourceControl.Engine.SqlCompareException: A duplicate definition was found for the trigger [dbo].[PrivateEquityClients.DeleteTrigger]. Ensure that case sensitivity options are set correctly and all object creation scripts are valid. If the problem persists, contact our support. ---> #8rg.#7rg: A duplicate definition was found for the trigger [dbo].[PrivateEquityClients.DeleteTrigger]. Ensure that case sensitivity options are set correctly and all object creation scripts are valid. If the problem persists, contact our support. ---> #8rg.#7rg: A duplicate trigger name ([dbo].[PrivateEquityClients.DeleteTrigger]) has been found. This may occur if the SQL Server that you are registering is case sensitive but the case sensitive option is not set. ---> System.ArgumentException: An item with the same key has already been added.


I know for a fact that there is a single version of that trigger.

I have even ran this script to check for whitespace issues

Code:
SELECT
*
FROM
sys.sysobjects a
INNER JOIN sys.sysobjects b ON REPLACE(RTRIM(LTRIM(a.name)),' ','') = REPLACE(RTRIM(LTRIM(b.name)), ' ','') AND a.id <> b.id


What could be the problem?
Back to top
View user's profile Send private message
jchesney



Joined: 27 Nov 2012
Posts: 1

PostPosted: Tue Nov 27, 2012 10:38 pm    Post subject: Duplicate Definition Error Reply with quote

Hi!

I encountered the same error today when I modified a Trigger definition to include the Schema declaration on the trigger, and add the [] to name of the objects.

I fixed the issue in the Table definition in TFS (in your case I think it would be in the table definition for PrivateEquityClients). However, this does not fix the key problem.

To fix it, I did the following:
1. I openned the Setup tab for SQL Source Control
2. Holding down the Ctrl and Shift buttons, I right clicked on the Schema directory, and selected Open Working Base Location
3. In the Tables directory, I found the table script that contains the trigger definition, and hand editted the script. Note that you will need to clear the Read-Only attribute on the file.

Once I had done this, the comparison worked. What doesn't make sense to me is why SSC uses its own AppData folder for the schema even though during the setup you need to fill in the directory where the TFS workspace puts the Schema.

I hope this helps you!

j.
Back to top
View user's profile Send private message
adriancearnau



Joined: 06 Dec 2012
Posts: 1

PostPosted: Thu Dec 06, 2012 11:09 am    Post subject: Reply with quote

I think I can replicate this problem easily.

Every time I reorder the columns in a table with triggers and then commit a migration script with the modifications, the table definition SQL file gets modified and includes the same triggers twice.

If there are also trigger modifications caught in the migration script, the table definition file ends up containing both the previous and the current trigger definitions.

This is quite a nasty regression, I haven't had this problem in previous versions of SQL Source Control. Any idea where I could report this bug properly?

By the way, thanks a lot for the Ctrl+Shift tip, didn't know I can access the working base folder like that, always had to rummage through AppData before.
Back to top
View user's profile Send private message
antiRev



Joined: 06 Dec 2012
Posts: 2
Location: United Kingdom

PostPosted: Thu Dec 06, 2012 11:55 am    Post subject: Reply with quote

I've had the same problem too, and I've been trying the SeparateTrigger option to see if that would help "SQL Source Control" realise its the same trigger, but setting the option to true doesn't actually seem to have any affect at all. BUT to be honest I'm not sure what the option is supposed to do?
Has anyone used this option?
Back to top
View user's profile Send private message
pk_davidson



Joined: 06 Nov 2012
Posts: 8

PostPosted: Tue Jan 01, 2013 1:40 am    Post subject: Reply with quote

I am just now having this same problem pop up.
I'm trying the suggestions here but have no idea what I did that caused this to start happening.
I had modified the schema to add (5) new vchar fields and then committed the changes.

I had recently added SQL 2008 R2 to my dev machine and this was the first time I worked in SSMS since then. I was working in SSMS 2005 not the latest installed 2008. I have the 2008 engines all set to start manually only so they won't take up memory. we are still using 2005 but I needed 2008 R2 to look at some other servers in the organization.

Scary that it looks like there have been no RedGate responses to this yet and it's been posted for almost a month now.

EDIT- so I followed J.'s suggestions and was able to eventually clean up my source control version. It was a cascading problem. I would clean up one table definition then committ the changes from the working base (thank you THANK YOU for the right click trick, I would have been clueless without it). Then another audit trigger would should up with a duplicate definition in it in another table !

What was also very strange and very concerning was that for my table with the original problem, the apparent committ that hosed everything also dropped all of my Foreign Key relationships that pointed to the primary key in that table ! Fortunately, my local copy still had these so I just had to continually committ those changes back to the source control until it "stuck" and no more duplicate triggers showed up.

I too would sure like to know how to report this as a bug to redgate because it seems to be something fairly new and has happened to a handful of people now.

What say you redgate sysops ?
Any ideas as to what's causing this ?
mucking around in the working base leaves me feeling a bit uncomfortable.
Back to top
View user's profile Send private message
CraigEddy



Joined: 28 Sep 2012
Posts: 16

PostPosted: Wed Jan 02, 2013 5:38 pm    Post subject: Re: Reply with quote

adriancearnau wrote:
I think I can replicate this problem easily.

Every time I reorder the columns in a table with triggers and then commit a migration script with the modifications, the table definition SQL file gets modified and includes the same triggers twice.

If there are also trigger modifications caught in the migration script, the table definition file ends up containing both the previous and the current trigger definitions.

This is quite a nasty regression, I haven't had this problem in previous versions of SQL Source Control. Any idea where I could report this bug properly?

By the way, thanks a lot for the Ctrl+Shift tip, didn't know I can access the working base folder like that, always had to rummage through AppData before.


Exact same problem here. Table with a trigger, added two fields, modified the trigger, and used a migration script to encapsulate the changes.

The issue isn't a SQL script error, it's an error in the processor that's creating the script. I suspect it's actually a SQL Compare issue...
Back to top
View user's profile Send private message
pk_davidson



Joined: 06 Nov 2012
Posts: 8

PostPosted: Wed Jan 02, 2013 7:10 pm    Post subject: Reply with quote

Just for clarity sakes:
I modified only my schema, only in one table. I did not touch the trigger files but the table does have two trigger files associated with it.
After cleaning up the first problem with the modified table (the code in the base had put in the two trigger creations twice), I then had to clean up another table which also had one trigger file that was in the base code twice.

Also, I didn't use a migration script, I was just committing changes directly from SSMS via SQL Source Control: Commit Changes tab.


Last edited by pk_davidson on Wed Jan 02, 2013 7:37 pm; edited 1 time in total
Back to top
View user's profile Send private message
CraigEddy



Joined: 28 Sep 2012
Posts: 16

PostPosted: Wed Jan 02, 2013 7:20 pm    Post subject: Workaround...Post-update Reply with quote

Here is what I did to get my environment back to a usable state:

1) After committing the change (mine was via a migration script), I went into the WorkingBases folder, found the modified table file (sort by date is easiest), made it not Read-only, edited the file to remove the extra trigger definition. Save the file.

2) Manually check out and perform the same edit on the file in actual TFS Tables folder. Check in this change.

3) In SQL Source Control, un-link my database. Ignore any errors that occur ("folder is not empty"). Close SSMS.

4) Re-open SSMS and link the database again.
Back to top
View user's profile Send private message
CraigEddy



Joined: 28 Sep 2012
Posts: 16

PostPosted: Wed Jan 02, 2013 7:21 pm    Post subject: Workaround..Not Reply with quote

Didn't exactly work for me because the migration script seemed to have gotten "disconnected" from the object: when another user did a get-latest on the table it didn't pull the migration script, and the table's schema change failed. The migration script included SQL INSERT statements to properly populate the temporary table created with the new NOT NULL columns I added.

I guess because of the manual edit I performed on the .SQL file for the table (in TFS).

Bummer.
Back to top
View user's profile Send private message
amumaugh



Joined: 30 Jun 2011
Posts: 13

PostPosted: Wed Jan 09, 2013 8:41 pm    Post subject: Reply with quote

I updated the working base file, updated the SVN file, unlinked, relinked, resolved conflicts. This process added a couple of revisions but got rid of the error on the commit tab. I went to deploy using SQL Compare and got the error again on the checking dependencies step.

I think what's happening in my case is that the the table creation script has the faulty code where the trigger is defined twice and the deployment process is checking that file even though it isn't used in the deployment because it's covered by a migration script.

I don't know of any way to go back and modify files within a previous revision so I told the deployment to skip the migration script, copied the deployment script to SSMS and modified it manually with the neccessary change from the migration script.

NOTE: The migration script was fine but I had to tell SQL Compare to skip it anyway because the underlying object script (unused in deployment) was faulty for that revision
Back to top
View user's profile Send private message
mmoore



Joined: 30 Jul 2008
Posts: 13

PostPosted: Tue Jan 22, 2013 5:49 pm    Post subject: Reply with quote

Ok, well, I have tried most of what you guys have said, and I had to make a change to my trigger, guess what, my error is back. It put Duplicate names back in the SQL file it generates, causing the issue again.

Is there a way to roll back to the previous version, where this DID NOT happen to me!

Thanks
_________________
Mark Moore
Software Engineer
NTS Data Services, LLC
Back to top
View user's profile Send private message
pk_davidson



Joined: 06 Nov 2012
Posts: 8

PostPosted: Tue Jan 22, 2013 7:31 pm    Post subject: Reply with quote

Good question...
I assume you mean roll back your redgate install ?
You can get old versions at:
http://www.red-gate.com/supportcenter/GeneralContent/all_products/articles/old_versions

But I'd suggest you contact support.
They may not be aware of this problem, since no one has chimed in on this thread...
And your products are tied together and then there's the question of what about having committed with the newer version ? Can you simply go back ?
Back to top
View user's profile Send private message
Manfred.Castro



Joined: 23 Apr 2012
Posts: 205

PostPosted: Tue Jan 22, 2013 8:30 pm    Post subject: Reply with quote

I think we've identified the issue at the root of these duplicate trigger issues - it's a bug we've logged in our internal bugtracking system under the reference SOC-4512. Here's how you can fix it - it's a bit of a chore, but it should clear up the issue.

1.) Make a note of the trigger throwing the error.

2.) Browse to the creation script for the relevant object directly in your source control repository - the folder structure that SQL Source Control creates is pretty self-explanatory

3.) Open the creation script and remove any and all duplicated CREATE statements. There should only be one CREATE statement for each trigger. Commit the changes to source control.

4.) Unlink and relink your database in SQL Source Control to rebuild the local Working Base. You should now be able to commit/get latest, unless ...

5.) .... it throws the same error for a different trigger, in which case repeat from step 1.

It seems that it's table rebuilds, like re-ordering columns that cause SQL Source Control to write these duplicate definitions. I'm pushing hard to get this bug fixed as soon as possible, because hand-editing the SQL statements in source control is a pain, and this is a showstopper bug, but this should at least get you up and running for the time being. I'll let you know as soon as there's an update on that bug.
_________________
Manfred Castro
Product Support
Red Gate Software
Back to top
View user's profile Send private message
CraigEddy



Joined: 28 Sep 2012
Posts: 16

PostPosted: Fri Apr 26, 2013 2:35 pm    Post subject: Still an issue even with version 3.2 Reply with quote

So I have encountered this issue again. I am using "Version 3.2.0.27 - March 13th, 2013" whose release notes specifically mention that this issue was fixed.

However, I encountered it again when I edited a table that has a trigger: inserting a column into the middle of the column list.

It seems to have to do with the affected tables also being in a migration script. I ended up having to delete the triggers AND the migration script before I could successfully sync my database to source control.
Back to top
View user's profile Send private message
nhustak



Joined: 09 May 2006
Posts: 34

PostPosted: Fri Jan 10, 2014 9:17 pm    Post subject: This issue is still occurring in the latest version Reply with quote

It's adding two copies of the same trigger into the create code. One with the source table wrapped in [], one without. I am basically stuck and nothing I have read here lets me get by it.

BTW, I am using no migration scripts.
Back to top
View user's profile Send private message
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Go to page 1, 2  Next
Page 1 of 2

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group