none
Generate Scripts fail for different Collation

    Question

  • Hello,

    I'm having trouble with migrating databases from SQL Azure when they're using a different collation, in this instance - Hebrew-CI-AS

    "Generate Scripts" fails when it's not the default collation, same as deleting the database from the management studio. Same error when trying to use the Migration Wizard from codeplex.

    When I use the default Latin collation, everything works fine.

    The error I get is this:

    TITLE: Microsoft SQL Server Management Studio
    ------------------------------

    Failed to retrieve data for this request. (Microsoft.SqlServer.Management.Sdk.Sfc)

    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&LinkId=20476

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

    Operation not supported on version 11.0 SqlAzureDatabase. (Microsoft.SqlServer.SqlEnum)

    Any ideas?

    Monday, June 11, 2012 12:00 PM

All replies

  • Hi Michael Y. David,

    >> When I use the default Latin collation, everything works fine.

    Regarding to your description, everything works after you use Latin collation. That because the default collation for character data in SQL Azure databases is SQL_Latin1_General_CP1_CI_AS. This collation is also used across the SQL Azure infrastructure to sort and compare metadata that defines database objects. The server and database level collations are not configurable in SQL Azure.

    For more information, please refer to Working With Collations In SQL Azure http://blogs.msdn.com/b/sqlazure/archive/2010/06/11/10023650.aspx

    Regards, Amber zhang

    Tuesday, June 12, 2012 7:40 AM
    Moderator
  • Ok, but why does that prevent me from using the migrate wizard or "Generate Scripts" in the management studio? This is major block, since I updated the collation, I can't migrate to the local sql anymore. Is there really no way around this?
    Tuesday, June 12, 2012 3:01 PM
  • I have come into the same situation, I want to migrate data from sql azure to local using Migration Wizard but it failed.

    Looking for a walk around

    Tuesday, June 26, 2012 1:41 AM
  • I encountered the same situation. Please see http://stackoverflow.com/questions/12712531/failed-to-generate-scripts-for-sql-azure-database-getting-the-list-of-objects.

    I have Cyrillic_General_CI_AS collation (it is shown on Dashboard of Azure DB). 

    Also looking forward to a workaround.


    Wednesday, October 10, 2012 10:10 AM
  • I'm handling same error. This trouble is so annoying.
    Wednesday, October 24, 2012 11:15 AM
  • Could you please post additional information and steps performed to get this specific error in SQL Server management Studio?.  You could report this issue  to https://connect.microsoft.com/SQLServer/Feedback 

    Please include the following

    1) SQL Server Management Studio version

    2) Is it connected to SQL Azure server  or a SQl Server instance running on your machine locally?

    3) SQL Server collation  & Database collation 

    4) Sample script ( table schema  and related database objects)

    5) Did you generate scripts using generate Script Wizard or  you scripted the table from Object Explorer


    Thanks

    Sethu Srinivasan [MSFT]

    SQL Server

    Wednesday, October 31, 2012 6:31 PM
  • This is known issue reported to us already. If you use non-default collation for your database, scripting individual objects will result in the error.

    We are working to address it in SQL Server 2012 SSMS service pack 1.  In the meantime, here are some workarounds:

     
    Scripting out objects
    =====================
    There are two potential workarounds
    Workaround 1:  use SQL Server 2012 management studio
     
    In SQL Server 2012 management studio, you can actually script all schemas if you use “Extract Data-tier application” (not “Export Data-tier application”).  “Extract data-tier application” only extract schema.  After you extract all the schema, you can get your table or procedure to use.  This will require SQL Server 2012 management studio.
     
    Workaround 2:  use SQL Server Data tools (SSDT)
    We SSDT is the tool that integrate with Visual Studio 2010 and 2012 (http://msdn.microsoft.com/en-us/data/tools.aspx).  If you have Visual studio 2010 or 2012, you can download the tool free.
    Once you install the tool, you will have option under “SQL Server Object Explorer” under “View” menu.
    You can right click on the table or procedure, choose “view code”. You can script out individual objects.
     
     
    Backup data
    =============
    Workaround 1: SqlPackage.exe: 
     
    you can download SQL Server data tools (http://msdn.microsoft.com/en-us/data/tools.aspx) which has sqlpackage.exe (http://msdn.microsoft.com/en-us/library/hh550080(v=vs.103).aspx). This tool allows you to backup or extract schema using command line parameters.  Here is an example, the following command line will export GreetTest database to a file called c:\temp\GreekTest.bacpac.   
     
    “C:\Program Files (x86)\Microsoft SQL Server\110\DAC\bin\sqlpackage.exe”  /Action:Export /sdn:GreekTest /ssn:<servername>.database.windows.net /su:<username> /sp:password /tf:c:\temp\Test.bacpac 
     
    Workaround 2:   using Azure Storage with import/export to backup data
    You mentioned that there is charges involved.  From a few offerings (http://www.windowsazure.com/en-us/offers/ms-azr-0020p  and http://www.windowsazure.com/en-us/offers/ms-azr-0003p) we have, Azure Storage is included.  Can you elaborate a bit more on which offering that would incur extra charges?

     

    Jack Li [MSFT]

    Monday, November 05, 2012 2:49 PM