How can we help you today? How can we help you today?

Computed Columns Using Same UDF Cause Syntax Error

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
bazza47
0

Comments

4 comments

Sort by
  • Michelle T
    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.
    Michelle T
    0
  • bazza47
    Backups sent.

    Cheers

    Colin
    bazza47
    0
  • Michelle T
    Thanks a lot for that - it's now in our bug tracking system and hopefully we can get it fixed for the next version.
    Michelle T
    0
  • Eddie D
    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 D
    0

Add comment

Please sign in to leave a comment.