What are the challenges you face when working across database platforms? Take the survey
Options

Error in Synchronization script for SQL 2000

montymonty Posts: 6
edited January 30, 2006 9:09AM in SQL Compare Previous Versions
Hi,
We have a custom type 'boolean' in our databases and after comparing two databases (one on SQL 2005 express and the other on SQL 2000), the custom type has been considered to be different.

However, when I go to update the database in the SQL 2000 server, I get the following error message:

The following error message was returned from the SQL Server:
[170] Line 1: Incorrect syntax near 'TYPE:'.

The following SQL command caused the error:

GRANT REFERENCES ON TYPE:: [dbo].[boolean] TO [public]
IF EXISTS (SELECT * FROM #tmpErrors) ROLLBACK TRANSACTION
It appears that the only difference between the type in one db to the other is that the permissions are different.

Any suggestions?

Using SQLExpress 4.1.0.59

thanks
Mark

Comments

  • Options
    monty wrote:
    Hi,
    We have a custom type 'boolean' in our databases and after comparing two databases (one on SQL 2005 express and the other on SQL 2000), the custom type has been considered to be different.

    However, when I go to update the database in the SQL 2000 server, I get the following error message:

    The following error message was returned from the SQL Server:
    [170] Line 1: Incorrect syntax near 'TYPE:'.

    The following SQL command caused the error:

    GRANT REFERENCES ON TYPE:: [dbo].[boolean] TO [public]
    IF EXISTS (SELECT * FROM #tmpErrors) ROLLBACK TRANSACTION
    It appears that the only difference between the type in one db to the other is that the permissions are different.

    Any suggestions?

    Using SQLExpress 4.1.0.59

    thanks
    Mark

    Hi Mark,

    many thanks for your feedback. It is indeed a bug in SQL Compare which we will fix shortly. Meantime, you could either set the ignore permissions option, or you will need to remove the permission alter lines for user defined types from the script manually.

    Regards,
    Andras
    András Belokosztolszki, PhD
    Red Gate Software Ltd.
  • Options
    Just a short followup. This bug only affects REFERENCES and EXECUTE permissions on user defined types that are migrated from 2005 to 2000.


    Regards,
    Andras
    András Belokosztolszki, PhD
    Red Gate Software Ltd.
This discussion has been closed.