Computed Columns Using Same UDF Cause Syntax Error

bazza47bazza47 Posts: 7
SQL Compare version 6.2.1/SQL Server 2005

I have two tables, each of which has a computed column that uses the same user defined function.

I create a new database and use SQL Packager to sync the schema and data between the original database and the new database.

In the original database I then change the body of the udf (by dropping the computed columns, altering the function and then re-adding the computed columns).

When I use SQL Compare 6 to sync the schema changes between the original database and the new database, the sql generated by the Syncronization Wizard, generates a SQL Error:
The following error message was returned from the SQL Server:

[3729] Cannot ALTER 'dbo.fnBuster' because it is being referenced by object 'Table1'.

Examining the T-SQL it appears that the computed column is only being dropped from one of the tables.

Any workaround?

Cheers

Colin

Comments

  • 1) The easiest workaround is to launch the script in Management Studio instead of using SQL Compare to run it, and add a drop column and recreate column around the function change.

    2) I can't reproduce this problem with a simple case (a database with just two tables and one function, with the tables having computed columns using the function, synchronizes fine) - would you be willing to send a snapshot or backup of your databases to michelle.taylor@red-gate.com so I can take a look at it and hopefully get it fixed? We fixed a similar problem for 6.0 but it's possible our fix doesn't work in all cases, so we'd like to have a look at your case to fix that one too.
    Software Developer
    Redgate Software
  • Backups sent.

    Cheers

    Colin
  • Thanks a lot for that - it's now in our bug tracking system and hopefully we can get it fixed for the next version.
    Software Developer
    Redgate Software
  • Eddie DEddie D Posts: 1,780 Rose Gold 5
    I am pleased to inform you that we have fixed this problem in the recently released SQL Compare V.7.

    If you have a valid Support & upgrades option, you can download SQL Compare V.7 using the 'Check for updates' mechanism (SQL Compare GUI ->Help ->Check for updates)

    or download using this link: HERE.

    SQL Compare V.7 will install along side any previous versions of the software.

    Many Thanks
    Eddie Davis
    Eddie Davis
    Senior Product Support Engineer
    Redgate Software Ltd
    Email: support@red-gate.com
Sign In or Register to comment.