1

We have a strange scenario.

We have a source database running on SQL Server 2005 where we push data to a target database running on SQL Server 2008 R2.

The source database table has 5 columns. The target database contains an extra column which is not nullable and has a DEFAULT value defined.

We need to push data for the 5 columns from source to target. If we use any tool like red-gate or dbcompare, it will compare source with destination and provide a query to insert the new data on to the target, however running the query fails as it is not inserting anything into the newly introduced column which is not nullable.

Any idea on how to handle this?

Improve this question
3
  • 1
    If the comparison tool does not include the extra column in the generated INSERT statement(s), and there is a default on that column, where is the error message during deployment coming from?
    – Darin Strait
    Apr 17, 2012 at 12:18
  • 1
    Redgate has two separate tools, one for data (DML) compare, and the regular compare tool is for schema (DDL). To "check the schema difference" use the regular compare tool.
    – Eric Higgins
    Apr 17, 2012 at 19:15
  • We modified the generated scripts and we move ahead. Thanks for all of the help.
    – Rampo
    Mar 21, 2013 at 13:13

1 Answer 1

Reset to default
1

Did you

  1. Copied all the data to SQL2008
  2. Updated the schema on SQL2008 according to the upgrade script?

Copy data shouln't be the problem here, schema compare neither ;)

For the scheme compare from sql 2005 to 2008 you may use the free Open DbDiff from http://opendbiff.codeplex.com and apply the resulting script to sql 2008.

Otherwise use the new version of the SQL Server Data Tools http://msdn.microsoft.com/en-us/data/tools.aspx that allows SQL 2005 to 2008/2012/Azure migration and scheme update out of Visual Studio 2010+

Improve this answer
1
  • We modified the generated scripts and we move ahead. Thanks for all of the help.
    – Rampo
    Mar 21, 2013 at 13:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.