Error migrating from SQL Source Control to SQL Server 2000

ciaranarcherciaranarcher Posts: 28
edited June 26, 2012 5:31AM in SQL Source Control Previous Versions
I created a migration, committed it and when trying to run it against a SQL Server 2000 database, I get the following error:
The following error message was returned from the SQL Server:

[208] Invalid object name 'fn_listextendedproperty'.

Specifically it seems to be an issue with the extended props:
The following SQL command caused the error:


INSERT INTO dbo.tmp_ciaran_XMLRequests
        ( LengthBytes ,
          RequestedOn ,
          AnotherCol
        )
VALUES  ( 0 , -- LengthBytes - int
          '2012-06-22 14:58:03' , -- RequestedOn - datetime
          N''  -- AnotherCol - nchar(10)
        )
/*
End of custom script trunk 8 to 10 migrating from version 8 to version 10
*/
/*
Start of RedGate SQL Source Control versioning database-level extended properties.
*/
DECLARE @RG_SC_VERSION BIGINT
SET @RG_SC_VERSION = 10
IF EXISTS (SELECT 1 FROM fn_listextendedproperty(N'SQLSourceControl Database Revision', NULL, NULL, NULL, NULL, NULL, NULL))
  EXEC sp_dropextendedproperty N'SQLSourceControl Database Revision', NULL, NULL, NULL, NULL, NULL, NULL
EXEC sp_addextendedproperty N'SQLSourceControl Database Revision', @RG_SC_VERSION, NULL, NULL, NULL, NULL, NULL, NULL

This can be made work by using ::fn_listextendedproperty instead (note colons).

Is this a bug or something I'm doing wrong?
· Share on Twitter

Comments

  • Andy C SAndy C S Posts: 173
    I'm afraid SQL Source Control doesn't actually support SQL Server 2000 - if you can get it working that's great, but we don't guarantee anything, and I know some features won't be too happy with it. Sorry!
    Andy Campbell Smith

    Red Gate Technical Support Engineer
    · Share on Twitter
  • ciaranarcherciaranarcher Posts: 28
    We use 2008 R2 to develop on (in 2000 compat mode) and have been using this since SQL Source Control V1, but production DBs are still on 2000. I only started using the new Migrations stuff features recently.

    I thought SQL Source Control uses uses SQL Compare Pro to generate the migration script, and doesn't that support SQL Server 2000?

    andy.campbell.smith wrote:
    I'm afraid SQL Source Control doesn't actually support SQL Server 2000 - if you can get it working that's great, but we don't guarantee anything, and I know some features won't be too happy with it. Sorry!
    · Share on Twitter
  • Andy C SAndy C S Posts: 173
    Actually, SQL Compare doesn't support SQL 2000 any more either, although it still just about works. SQL Source Control has never supported SQL 2000, though, and it's because of the way we collect trace information, not because of the Compare engine - the way we collect trace information is fundamentally incompatible with SQL 2000, so it's unlikely it'll ever be supported with SQL Source Control. Sorry!
    Andy Campbell Smith

    Red Gate Technical Support Engineer
    · Share on Twitter
  • ciaranarcherciaranarcher Posts: 28
    Hi Andy

    When you say SQL Compare doesn't support SQL Server 2000 'anymore', can you tell me since when/version?

    The reason I ask is that your website still says it does support SQL Server 2000: http://www.red-gate.com/supportcenter/c ... ng_Started

    Thanks,
    Ciaran
    andy.campbell.smith wrote:
    Actually, SQL Compare doesn't support SQL 2000 any more either, although it still just about works. SQL Source Control has never supported SQL 2000, though, and it's because of the way we collect trace information, not because of the Compare engine - the way we collect trace information is fundamentally incompatible with SQL 2000, so it's unlikely it'll ever be supported with SQL Source Control. Sorry!
    · Share on Twitter
  • Andy C SAndy C S Posts: 173
    The last version of SQL Compare to officially support SQL Server 2000 was version 9.xx - it looks like that page is outdated in that aspect. The requirements page here: http://www.red-gate.com/products/sql-de ... quirements doesn't list SQL 2000, as you can see.

    Really what that means is that we've stopped testing SQL Compare with SQL Server 2000 - SQL Compare 10 still works, as far as I'm aware, with SQL Server 2000, but there may be SQL 2000-specific bugs, and since we don't test against it, those could make it into release.

    If you want to use SQL Compare with SQL 2000 databases, version 10 will probably work, and if it doesn't version 9.5 is available for download here: http://www.red-gate.com/supportcenter/G ... d_versions

    Hope that helps!
    Andy Campbell Smith

    Red Gate Technical Support Engineer
    · Share on Twitter
Sign In or Register to comment.