Computed Columns Using Same UDF Cause Syntax Error

bazza47

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

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.

bazza47

Backups sent.

Cheers

Colin

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.

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