SSMS 2008 R2 11/2009 CTP Has Scripting Problems with SQL Azure Selected as the Target Data Base Engine Type
Update 1/18/2010: Microsoft’s Li-Lun Luo was able to reproduce the problem according to the last item in the Has Anyone Else Seen These Bugs in SQL Server 2008 R2's Script Generation for SQL Azure? thread of 1/13/2010 in the SQL Azure - Getting Started forum.
Also, Vinod Kumar Jagannathan, who I believe is a Program Manager at Microsoft India, reports in a comment to this post that the problem is an issue with the 11/2009 CTP and is fixed for later (future) SSMS 2008 R2 CTPs.
MSDN blogger sateesp published Seven benefits for using the SQL Server Management Studio(SSMS) for managing the SQL Azure Database on 1/1/2009. His fully illustrated post included details or and screen captures for the following benefits:
- Seamless navigation
- Query Editor
- Object scripting
- Database Migration
- Object Explorer Details
- Basic dialogs support
- Templates
Item 4 describes the use of the Generate Scripts Wizard with, according to the Database Migration topic’s last, the Northwind Sample database. sateesp did not report any problems with performing the migration to SQL Azure.
I use the AdventureWorksLT2008AZ database from the SQL Azure (December CTP) release on CodePlex, rather than Northwind, with SQL Server 2008 R2 November 2009 CTP as the source for scripting test samples because AdventureWorks Lite has more challenging table structures and data types.
The test environment is Windows Server 2008 R2 with Hyper-V as the host OS and Windows Server 2008 R2 without Hyper-V as the VM’s Guest OS with 4,096 MB RAM assigned. Visual Studio 2008 SP1 with current versions of Windows Azure Tools for Visual Studio, Windows Azure SDK, Windows Azure Platform Training Kit, and other client software installed.
Script Generation Failure due to ‘Phone’ User Defined Data Type Incompatibility
The most logical candidate for migrating on-premises SQL Server databases to SQL Azure is George Huey’s SQL Server Migration Wizard v3.1.1. However, some users may want or need to use SSMS 2008 R2’s wizard-like Script Generation feature.
1. I used SSMS2008R2’s Script Generation feature with the AdventureWorksLT2008AZ database in an attempt to generate a script acceptable to SQL Azure v1 (release to Web version). I selected all objects in the Wizard’s second dialog because I expected to be able to prevent scripting user-defined types in a later step:
2. I specified saving the resulting script to a file in the default folder:
3. I specified SQL Azure Database as the target Database Engine Type which I assumed set options appropriate to SQL Azure’s DDL limitations and specified scripting Schema and Data:
4. Following is a capture of the wizard’s Summary Dialog displaying General Options:
5. Clicking Next opened the Save or Publish Scripts dialog which immediately displayed an error:
6. I clicked Save Report to determine the source of the problem; the report displayed this message:
Following is the text of the message:
Microsoft.SqlServer.Management.SqlScriptPublish.SqlScriptPublishException: Object 'Phone' of type 'UserDefinedDataType' is not valid for the selected database engine type. at Microsoft.SqlServer.Management.SqlScriptPublish.GeneratePublishPage.worker_DoWork(Object sender, DoWorkEventArgs e) at System.ComponentModel.BackgroundWorker.OnDoWork(DoWorkEventArgs e) at System.ComponentModel.BackgroundWorker.WorkerThreadStart(Object argument)
I was surprised to see an error related to the Phone UDDT because the Convert UDDTs to Base Types option was set to True (see Step 4). This appears to me to be a bug, because SQL Azure’s How to: Migrate a Database by Using the Generate Scripts Wizard online help topic says, in part:
In Choose Script Options, set the following options:
- Convert UDDTs to Base Types = True
- Script Extended Properties = False
- Script Logins = False
- Script USE DATABASE = False
- Script Data = True
SQL Azure does not support user-defined data types, extended properties, Windows authentication, or the USE statement.
Step 4’s screen capture indicates that these options were set in accordance with the help topic.
Script Execution Failure due to Attempts to Set Extended Property Values
1. Omitting the Phone UDDT only from the script doesn’t solve the problem; you must remove all UDDTs from the selected objects to script to prevent errors in step 5. You also must remove XML Schema Collections which throw an error message similar to that for UDDTs:
2. The Save or Publish Scripts dialog now appears as follows, indicating successful scripting of the database:
3. Launching SSMS 2008 R2 with the empty SQL Azure database specified, opening and executing the 5,222-line AW2008LTR2AZ.sql script creates the SalesLT schema’s tables, but doesn’t insert the data because of hundreds of errors due to failure to SET IDENTITY INSERT ON for tables having a primary key based on an identity column (all of them):
In addition, it would be necessary to manually add a
CONSTRAINT [PK_BuildVersion_SystemInformationID] PRIMARY KEY CLUSTERED
command because SQL Azure requires all tables to have a clustered primary key index.
These failures indicates three additional bugs in the scripting process for SQL Azure databases. You can download a compressed copy of AW2008LTR2AZ.sql from SkyDrive.
Atom 1.0 site feed
