Database Synchronization Error with SQL Compare
mint3kool
Posts: 5
I have been experimenting with continuous integration with TeamCity and Redgate, and I have been getting an odd error. I have found that if I remove a NOT NULL column from comparison using SQL Data Compare 11 (not a key column) and then commit the data changes to source control, the Redgate DLM Automation build step fails since it cannot sync my database and the temporary database. SQL compare ends up failing and I get this error:
[15:14:35][Validating database state] Error: Synchronization of 'Scripts.state' and 'enuitserv07.EnstepSrc' failed:
[15:14:35][Validating database state] Cannot insert the value NULL into column 'first', table
[15:14:35][Validating database state] 'tempDB.step.SimpleTable'; column does not allow nulls. INSERT fails.
The thing is, I have already specified a default value for the "first" column, so there is no reason why it should have to insert a null value into that column. When I run the data changes from SQL Data compare in MSSQL, the information is added without any issues. I can't tell if this is left over or something, but the error log also seems to include information on tables I have not changed in my commit:
[15:14:35][Validating database state] Add 21 rows to [step].[typea] Add 4 rows to [step].[typeb] Add 27
[15:14:35][Validating database state] rows to [step].[typec] Add 11 rows to [step].[SimpleTable] Error
[15:14:35][Validating database state] executing the following SQL: DECLARE @pv binary(16) INSERT INTO
[15:14:35][Validating database state] [step].[Bases] ([BaseUID], [Base], [Flag])
[15:14:35][Validating database state] VALUES (2, N'15.025 D', N'D') INSERT INTO [step].[Bases]
[15:14:35][Validating database state] ([BaseUID], [...
I only made changes to the "first" table in this particular commit, [step].[typea], [step].[typeb], and [step].[typec] were already in my database and were fine in my previous commit. We have a table in our database where we are not using all of the columns for comparasion, and we can't seem to get by the NULL column issue. Any help would be greatly appreciated!
Thanks!
[15:14:35][Validating database state] Error: Synchronization of 'Scripts.state' and 'enuitserv07.EnstepSrc' failed:
[15:14:35][Validating database state] Cannot insert the value NULL into column 'first', table
[15:14:35][Validating database state] 'tempDB.step.SimpleTable'; column does not allow nulls. INSERT fails.
The thing is, I have already specified a default value for the "first" column, so there is no reason why it should have to insert a null value into that column. When I run the data changes from SQL Data compare in MSSQL, the information is added without any issues. I can't tell if this is left over or something, but the error log also seems to include information on tables I have not changed in my commit:
[15:14:35][Validating database state] Add 21 rows to [step].[typea] Add 4 rows to [step].[typeb] Add 27
[15:14:35][Validating database state] rows to [step].[typec] Add 11 rows to [step].[SimpleTable] Error
[15:14:35][Validating database state] executing the following SQL: DECLARE @pv binary(16) INSERT INTO
[15:14:35][Validating database state] [step].[Bases] ([BaseUID], [Base], [Flag])
[15:14:35][Validating database state] VALUES (2, N'15.025 D', N'D') INSERT INTO [step].[Bases]
[15:14:35][Validating database state] ([BaseUID], [...
I only made changes to the "first" table in this particular commit, [step].[typea], [step].[typeb], and [step].[typec] were already in my database and were fine in my previous commit. We have a table in our database where we are not using all of the columns for comparasion, and we can't seem to get by the NULL column issue. Any help would be greatly appreciated!
Thanks!
