Red Gate forums: SQL Data Compare 10

RE: Updating Data using SQL Data Compare

chriskelly — Fri, 17 May 2013 09:21:32 GMT

Yes, this is possible. Once the comparison is complete, ensure that you have unchecked the whole column titled "target only". This means that when you deploy the changes then any rows that are only in the target will not be changed or deleted. But any other rows will be added or updated.

Updating Data using SQL Data Compare

srob35 — Tue, 14 May 2013 21:13:17 GMT

So I am comparing a source and target table that has customer information in it - is there a way to update records that have changed and also insert new customer records?

Does SQL Data Compare have that functionality?

Our current customer tables have around 8 million plus records in it - so if there is an easier way to update and insert new records that would be the way to go.

Anyone have any experience with this?

RE: Exclude columns from multiple tables using XML config?

eddie davis — Wed, 08 May 2013 14:01:47 GMT

Quick update in case other SQL Data Compare users wish to use the EXCLUDECOLUMNS switch in an XML argument file.

To exclude a column using the xml file, where you have two or more tables whose name includes the same string, the table name needs to be enclosed with the [ ] characters and escaped with the \ character. Using the example in this forum topic, where you need to exclude the column called "Domain" from a table called "Site" but have other tables whose table name includes the word or characters Site.

Using this syntax - <excludecolumns>\[Site\]:Domain</excludecolumns>

Many Thanks
Eddie

RE: Task cannot be completed - Mapping

Anu Deshpande — Tue, 07 May 2013 15:54:34 GMT

Glad its all working now!

Is there anything else you need my help with?

RE: Sync only new rows in a table

sbacheld — Fri, 03 May 2013 04:14:44 GMT

Thanks, Brian. We'll investigate a solution along those lines!

DateTimeOffset support not working

dbouchard@tricomputer.com — Tue, 30 Apr 2013 13:31:22 GMT

I have 2 databases with several tables that contain datetimeoffset fields. I have emptied one of the databases so that sdc10 will basically be doing a move from one db to the other and the script created by sdc10 fails on a datetime conversion error. I find it hard to believe that I'm the first person to run into this!

Here is a sample import row below. The fields CreatedDateTime and ModifiedDateTime are both datetimeoffset(7) fields.

INSERT INTO [dbo].[BuildLabels] ([RowID], [ParentRowID], [BuildLabel], [Comments], [SystemLabel], [OwnerID], [CreatedDateTime], [CreatedBy], [ModifiedDateTime], [ModifiedBy], [Active], [rowguid], [LabelState]) VALUES (1, NULL, N'root', N'main root label', 1, N'administrator', '2012-11-12 16:59:01.1745892 +05:30', N'donaldb', '2012-11-12 16:59:01.1745892 +05:30', N'donaldb', 1, '5f2cc758-d832-e211-ad6c-d067e5e5d59c', NULL)

The exact error in a messagebox titled "SQL Data Compare" is "Error occurred execution synchronization. Conversion failed when converting date and/or time from character string."

Thanks,
Don

RE: Spins infinitely Retrieving schema and Registering Databases

scotteb@mindspring.com — Mon, 29 Apr 2013 12:14:31 GMT

@rvaiyapuri,

I also experienced this and was able to resolve this issue by creating a new project. My project had other symptoms of corruption, maybe your project has only a minor corruption causing this. Try creating a new project and see if it loads more quickly.

Best,
Scott

RE: Task cannot be completed - Mapping

scotteb@mindspring.com — Mon, 29 Apr 2013 12:10:08 GMT

The project was created and last used on 13 April 2013 on the local computer. I did create a new project and all is well.

In fact, it solved another issue which you should be aware of as I saw reports of it on the forum. When I would load the project, the 'retrieving schema' message would display 100s of times, for 4-5 minutes. The new project loads immediately.

Best,
Scott

RE: Task cannot be completed - Mapping

Anu Deshpande — Mon, 29 Apr 2013 09:46:57 GMT

Many thanks for your post and apologies for inconvenience caused.

The 'field mapping not valid for matching mapping' error is normally a result of a corrupt project file workaround for that is to create a new project file which you already done.

Was this project file quite old or was it copied over the network or something?

Task cannot be completed - Mapping

scotteb@mindspring.com — Sun, 28 Apr 2013 22:48:20 GMT

Using SQL Data Compare 10.2.3.5 on Win8, I opened an existing projected, that synchronized without error on 13 April 2013. A table change today was synchronized with SQL Compare. There are no differences in tables (but some in source db only).

When I attempt to compare, I am presented with a "Task cannot be completed" dialog with a X next to mapping. At the bottom of the dialog is the text "FieldMapping not valid for MatchingMapings.".

Of the twenty tables, all but one show a comparison key is set. The one table is "Not Set" (never has been as I do not wish to compare it). I have looked over the tables on the Mapping tab and all are mapped correctly.

I selected only one table, with one column, and receive same error. BTW, after error dialog closes, all tables are selected for compare - that is bad form; no need to do that if user has made selections. Additionally, any custom mappings I have made revert to use keys. But that is not my complaint.

I'd like to synch these databases, but not sure where to look (wish error message mentioned the able!). I created another project against two different databases on these servers and was able to compare.

Best,
Scott

RE: Unsupported column type - Empty

Manfred.Castro — Thu, 25 Apr 2013 18:32:23 GMT

The /ComparisonKeys switch(/ComparisonKeys:<table or view name as regular expression>:<index name>) allows you to specify a unique index to be used to identify rows for comparison. With /ComparisonKeys you can only specify an index as the comparison key, no other columns can be specified.

To specify a comparison key that is is not an index, use the GUI to set up and save a project with the settings you require. You can then use that project from the command line with the /Project switch.

https://documentation.red-gate.com/display/SDC100/Switches+used+in+the+command+line

Unsupported column type - Empty

Meriguan — Thu, 25 Apr 2013 14:30:44 GMT

Hi All,

SQL Data Copmare 8.1.0.4

I try to compare scripts folders and get error "Unsupported column type- Empty". It means table contains PK with User-Defined Data Type column. This datatype was commited in folder "Types", but SQL Data Compare doesn't use it for comparing as I understood.
I changed "Comparison key" to "Custom" and set the same columns like has PK and comparing works fine.

The question is Can I set Custom Comparison key in the command line for each tables? What command I should use?

RE: Sync only new rows in a table

Brian Donahue — Thu, 25 Apr 2013 08:59:20 GMT

Hi Sean,

Unfortunately I can't answer correctly without having the whole schema. SQL Data Compare matches rows based on the primary key or column(s) you choose to use as a row identifier. And it's not state-aware so it can't tell you if there are "new" rows. What you could possibly try is to use a WHERE clause on the tables and views tab and put a selection criteria there -- for instance if your table has a datetime column in it you could sync only records that are newer than 30 days by putting a WHERE clause in to compare only date > GETDATE()-30.

Sync only new rows in a table

sbacheld — Wed, 24 Apr 2013 19:11:10 GMT

Hi,

We are in the process of automating database updates from our dev -> staging -> production process. One of the tables that we want to use sql data compare for has different values for each of the different environments. So it would look something like this:

dev: name=Row1, timeout=1
staging: name=Row1, timeout=10
production: name=Row1, timeout=15

Now, if we add a new row, we want that new row to get synced to all the databases, but would like any existing rows to remain unaffected. Is this possible with sql data compare? We are running this from the command-line if that makes a difference

Thanks,
Sean

RE: Checkbox for ALL

Random Lengths — Fri, 19 Apr 2013 15:38:24 GMT

That does what a want, Thanks.

Just a feature note, the context menu is less intuitive then the ubiquitous checkbox at the top like in SQL Compare uses. Rarely is it a good idea to implement the same feature differently in your own products.