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
Operation not supported on version 11.0 SqlAzureDatabase. (Microsoft.SqlServer.SqlEnum)
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
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.
- Edited by Artyom Karpov Wednesday, October 10, 2012 10:11 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
Sethu Srinivasan [MSFT]
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.
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]