locked
MS_SSMA_SOURCE Extended property - Is it required? RRS feed

  • Question

  • Hi,

    The SSMA from Oracle to SQL Server adds the "MS_SSMA_SOURCE" extended property to all tables and columns it converts.
    Why is this required? Could there be problems if I remove it? 

    If removing it is OK, is there a way to set a preference in the SSMA GUI so that these statements will not be generated?

    Thanks

    For example:

    BEGIN TRY
        EXEC sp_addextendedproperty
            N'MS_SSMA_SOURCE', N'CTI199_UR.CUST_CALL_DATA.SERVICEPHONE',
            N'SCHEMA', N'dbo',
            N'TABLE', N'CUST_CALL_DATA',
            N'COLUMN', N'SERVICEPHONE'
    END TRY
    BEGIN CATCH
        IF (@@TRANCOUNT > 0) ROLLBACK
        PRINT ERROR_MESSAGE()
    END CATCH

    Sunday, March 23, 2014 1:40 PM

Answers

  • Hi Eran K,

    When we migrate database form Oracle to SQL Server, there may be some comments which are contained by the source database schema describe the schema. This can be as a comment within programming code (such as Oracle PL/SQL) in procedure, function, package, view or as a comment that is part of the table/ column schema. SQL Server Migration Assistant (SSMA) migrates the full programming code, including any comments inside procedure, function, package, view, etc. However, it does not automatically migrate comment as part of table schema. We can use sys.sp_addextendedproperty to recreate the comments in SQL Server.

    According to your description, if you can delete the "MS_SSMA_SOURCE" in extended property, it is ok.  If you want to migrate comment as part of table schema, you need to recreate every comment by hand. For more information, you can review the following article.
    http://blogs.msdn.com/b/ssma/archive/2011/02/17/saving-comments-during-migration.aspx?Redirected=true

    Regards,
    Sofiya Li


    Sofiya Li
    TechNet Community Support

    • Marked as answer by Sofiya Li Monday, March 31, 2014 2:20 AM
    Tuesday, March 25, 2014 2:51 AM