Renaming, adding cols

joeybagdnts · October 6, 2011 8:00AM

2 questions

1.
OrigTable:

CustomerID
Modified Table:

NewField1 CustomerID NewField2
The resulting table would end up with:

NewField1 NewField2
Is there any way to tell SQLCompare that first field in Orig table is now the second field in the modified table? I end up losing fields b/c I am adding them before the existing fields.

2. If I have a customer table, and a stored proc that is uses that table, and I rename the customer table, should SQLCompare tell me that the stored proc is one of the dependencies of my renamed table?

Eddie D · October 10, 2011 6:50AM

Thank you for your post into the forum.

With regards to question 1, I suspect you need to enable the 'Force column order' option in the project options. Edit Project ->Options tab ->Locate the 'Force column order' option in the Behavior section.

With regards to question 2, I need further information from you. If the table exists in both data sources, by default SQL Compare will show that as existing only in their own data sources, as each table name differs.

Did you map the tables together?

The only place SQL Compare will inform if you have dependent objects is in the Synchroization wizard. If you select the table only for synchronization, are you warned regarding dependent objects?

Many Thanks
Eddie

joeybagdnts · October 10, 2011 11:16AM

eddie davis wrote:

The only place SQL Compare will inform if you have dependent objects is in the Synchroization wizard. If you select the table only for synchronization, are you warned regarding dependent objects?

Many Thanks
Eddie

Thank you for your help Eddie...

If I select the table that was renamed for sync, I would have hoped that SQL Compare would have warned me about the dependent object (the stored proc that referenced the table that was renamed), but SQL Compare does not tell me that anything is dependent on that table.

I suspect it is b/c as far as SQL Compare knows, the table that was renamed is a new table and the stored proc is still referring to the table before it was renamed, but maybe there is an option (just like in #1) that I don't know about where Compare is able to keep track of this.

You asked if I mapped the tables together... Where do I do that? Maybe that would solve the problem.

peter.peart · October 11, 2011 5:14AM

I believe the 9.5 EAP version deals with renames quite well. The current version though does treat renamed tables as new objects though, which is more than likely why you are experiencing what you currently are.

More on the EAP can be found from the below:

http://www.red-gate.com/messageboard/viewtopic.php?t=14113

HTH!

Pete

joeybagdnts · October 11, 2011 8:38AM

peter.peart wrote:

I believe the 9.5 EAP version deals with renames quite well. The current version though does treat renamed tables as new objects though, which is more than likely why you are experiencing what you currently are.

More on the EAP can be found from the below:

http://www.red-gate.com/messageboard/viewtopic.php?t=14113

HTH!

Pete

Thanks for all your help so far.

I'm not sure if this is working as intended, but here is what is happening in the 9.5 EAP.

In my current DB, I have a table -> Customers
I have a stored proc (GetCustomers) that select * from Customers

I rename my customers table to -> NewCustomers

In 9.5 Compare EAP, I map the Customers table -> NewCustomers

If I compare DBs, I would hope that the dependencies page would tell me that the stored proc GetCustomers needs to be updated as it is still pulling data from the Customers table (which has been renamed). However, it does not tell me this.

Additionally, if another table is dependent on the Customers table, that table DOES show up as a dependency when I compare 2 DBs, but if I compare the DB to a script folder, I am not informed that that table is a dependency.

Hope this helps. If you need additionally info, I'll be happy to provide it.

Eddie D · October 12, 2011 9:02AM

Hi

I have a private build of the SQL Compare V9.5 EAP for you to try out. Can you please send an e-mail to support@red-gate.com containing your contact details with the following Call Reference number in the subject field - F0053077.

I will then reply with a link for you to download this private build of the SQL Compare V9.5 EAP.

Many Thanks
Eddie

joeybagdnts · October 13, 2011 9:09AM

Thank you so much!

The version that you sent 9.5, correctly finds the stored proc to the renamed tables, and informs you on the dependencies page for both the DB and the Source Control comparison table.

You guys rock!

Eddie D · October 13, 2011 9:23AM

Thank you for your reply and excellent news that this build is now working for you.

What actions did you take to overcame the error you reported previously by e-mail using this new build, where the SQL Compare reported a duplicate definition for a function "An item with the same key has already been added" during the regsitering data sources phase of the compare?

Many Thanks
Eddie

joeybagdnts · October 13, 2011 11:40AM

eddie davis wrote:

Thank you for your reply and excellent news that this build is now working for you.

What actions did you take to overcame the error you reported previously by e-mail using this new build, where the SQL Compare reported a duplicate definition for a function "An item with the same key has already been added" during the regsitering data sources phase of the compare?

Many Thanks
Eddie

It was a dumb error on myy part... I had scripted the functions folder out to a temporary location (under functions) and forgot about it. A simple google search pointed me to a forum post where the issue was explained. Again, thanks so much for your help. You guys are really great!

Renaming, adding cols

Comments

Product Learning

Community Forums

Events & Friends

Simple Talk