locked
SSISDB collation default: how to change? RRS feed

  • Question

  • I have SSISDB listed under databases in SSMS denali ctp3, but its collation is different from master or all the other databases. 
    SSISDB uses: SQL_Latin1_General_CP1_CI_AS
    But all the other databases are using: Latin1_General_100_CI_AS_KS_WS

    How do I change this to  Latin1_General_100_CI_AS_KS_WS?  I tried going under "properties, options, collation" for SSISDB to change it, but I got this error:

    Alter failed for Database 'SSISDB'.  (Microsoft.SqlServer.Smo)

    ------------------------------
    ADDITIONAL INFORMATION:

    An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

    ------------------------------

    The object 'dm_execution_performance_counters' is dependent on database collation. The database collation cannot be changed if a schema-bound object depends on it. Remove the dependencies on the database collation and then retry the operation.
    The object 'get_database_principals' is dependent on database collation. The database collation cannot be changed if a schema-bound object depends on it. Remove the dependencies on the database collation and then retry the operation.
    The object 'CK_Folder_PermissionType' is dependent on database collation. The database collation cannot be changed if a schema-bound object depends on it. Remove the dependencies on the database collation and then retry the operation.
    The object 'CK_Project_PermissionType' is dependent on database collation. The database collation cannot be changed if a schema-bound object depends on it. Remove the dependencies on the database collation and then retry the operation.
    The object 'CK_Environment_PermissionType' is dependent on database collation. The database collation cannot be changed if a schema-bound object depends on it. Remove the dependencies on the database collation and then retry the operation.
    The object 'CK_Operation_PermissionType' is dependent on database collation. The database collation cannot be changed if a schema-bound object depends on it. Remove the dependencies on the database collation and then retry the operation.
    ALTER DATABASE failed. The default collation of database 'SSISDB' cannot be set to Latin1_General_100_CI_AS_KS_WS. (Microsoft SQL Server, Error: 5075)

    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=11.00.1440&EvtSrc=MSSQLServer&EvtID=5075&LinkId=20476

     

    Tuesday, July 19, 2011 8:37 PM

Answers

  • The observation is correct.
    We do not support the changing of collation for SSISDB.


    • Marked as answer by TechVsLife2 Friday, July 22, 2011 8:36 AM
    Friday, July 22, 2011 8:32 AM

All replies

  • so far, i've worked around this in a way almost certainly not recommended:

    After 1. finding each of the objects in SSISDB database in the error message above (the first two are table functions, and the rest are constraints underneath similar looking object names), and 2.  saving a create script for all of them, I then 3. deleted all those objects from SSISDB. 

    4. Then I stopped and restarted sql server engine (right-click on server in SSMS, restart). 

    5. Then I changed the collation of SSISDB, and it allowed it.

    6.  Then I ran all the create scripts to re-create the deleted objects from SSISDB (you have to select refresh to see them reappear in SSMS object explorer under SSISDB).  I had to disconnect/re-connect in each query window because the query windows didn't realize I had restarted sql server.

    However, they may want SSISDB to have that particular collation--in that case, it should be in the docs, or the option to change collation for SSISDB should be greyed out.  As a rule, I like having only one collation to keep things simple.

     

     

     

     



    Tuesday, July 19, 2011 9:17 PM
  • The observation is correct.
    We do not support the changing of collation for SSISDB.


    • Marked as answer by TechVsLife2 Friday, July 22, 2011 8:36 AM
    Friday, July 22, 2011 8:32 AM
  • Thanks, weehyong.  (I assume this means that no issues should arise from any difference between the SSISDB default collation and the collations used by other databases, and that the user should never change the collation of SSISDB from the default.)
    Friday, July 22, 2011 8:40 AM
  • Thanks, weehyong.  (I assume this means that no issues should arise from any difference between the SSISDB default collation and the collations used by other databases, and that the user should never change the collation of SSISDB from the default.)

    The SSISDB is a self-contained database, so yes, no issues should arise even if the collation is different from the server collation.  If you experience an issue, please submit a bug.

     

     


    Phil Brammer | http://www.ssistalk.com | Twitter: http://twitter.com/PhilBrammer
    Friday, July 22, 2011 9:27 PM