Contents

Generate and Publish Scripts Wizard

The Generate and Publish Scripts Wizard is used to create T-SQL scripts for SQL Server databases and/or related objects within the selected database. You have probably used this wizard, so this article doesn’t walk through it step by step; instead, the article briefly highlights a few steps in the wizard and points out the options necessary to effectively work with SQL Azure.


SQL Server 2008 R2 comes with the ability to script an on-premises database for the SQL Azure environment. Because many haven’t moved to SQL Server 2008 R2, the examples in this article use the version prior to R2, which is the original release of SQL Server 2008.


One of the differences between SQL Server 2008 R2 and SQL Server 2008 (pertaining to object scripting) is a setting in the Advanced Scripting Options dialog as you go through the wizard. This dialog includes two properties you can set regarding the version of SQL Server for which you’re scripting database objects: Script for Server Version and “Script for the database engine type.” The Script for Server Version option lists the version of SQL Server that the Generate and Publish Scripts wizard supports, which ranges from SQL Server 2000 to SQL Server 2008 R2.


The “Script for the database engine type” property has two options you can choose from: “Stand-alone instance” and “SQL Azure database.” The “SQL Azure database” option only works with the SQL Server 2008 R2 Server version. For example, if you set the Script for Server version to SQL Server 2008 (non R2) and then set the “Script for the database engine type” property to “SQL Azure database,” the Script for Server version property value automatically changes to SQL Server 2008 R2.


The Generate and Publish Scripts Wizard does a really nice job of appropriately scripting objects for SQL Azure. The wizard checks for unsupported syntax and data types, and checks for primary keys on each table. Thus, the following example sets SQL for Server Version to SQL Server 2008 (non R2) for several reasons. First, many people aren’t using SQL Server 2008 R2 and therefore don’t have the option to script for SQL Azure. Second, this exercise shows you what steps are needed to get a script ready to run in SQL Azure.

Starting the Wizard

To start the Generate and Publish Scripts Wizard in SQL Server Management Studio (SSMS), open Object Explorer and expand the Databases node. Select a database, right-click it, and then select Generate Scripts from the context menu.


On the wizard’s Introduction page for SQL Server 2008 R2, you’re informed that you must follow four steps to complete this wizard:


1. Select database objects.

2. Specify scripting or publishing objects.

3. Review selections.

4. Generate scripts.


The following work through these steps.

Choosing Target Objects

To select your target database objects, follow these steps:


1. On the Introduction page of the Generate and Publish Scripts Wizard, click Next.


2. On the Choose Objects page (see Figure 1), select the “Select specific database objects” option, because for the purposes of this example, you simply want to select a few objects to migrate.


Figure 1 - Choosing objects to migrate into script form


3. In the list of objects in Figure 1, expand the Tables and Stored Procedures nodes, and select the following objects:

  • Tables: Docs, UserDoc, and Users
  • Stored procedures: proc_CreateProfile, proc_GetDocument, and proc_UpdateDocFile


4. Click Next on the Choose Objects page.


5. On the Set Scripting Objects page, select the “Save to new query window” option shown in Figure 2, and then click the Advanced button.


Figure 2 - Scripting options

Setting Advanced Options

Clicking the Advanced button brings up the Advanced Scripting Options dialog shown in Figure 3. Follow these steps:


1. In the Advanced Scripting Options dialog, set the following options:

  • Convert UDDTs to Base Types = True
  • Script Extended Properties = False
  • Script Logins = False
  • Script USE DATABASE = False
  • Types of data to script = Schema and data


Figure 3 - Advanced Scripting Options dialog


You can also set the Script DROP and CREATE option to Script DROP and CREATE, as shown in Figure 3, but that option isn’t required for SQL Azure.


2. Click OK in the Advanced Scripting Options dialog, and then click Next in the Generate Scripts wizard.

Saving and Publishing

Complete the wizard with these steps:


1. On the wizard’s Summary page, review your selections, and then click Next. The T-SQL script is generated, and you’re taken to the Save or Publish Scripts page (see Figure 4).


Figure 4 - Completed Scripted Process


2. Click Finish. At this point your script is finished and is displayed in a query window in SSMS.

Reviewing the Generated Script

Open the file you created, and let’s take a quick look at the generated T-SQL. The following snippet from what you see shows the creation of three tables and three stored procedures, and a few INSERT statements that add rows of data to some of the tables. Except for the things you told the script-generation wizard to ignore, the following T-SQL looks like all other object creation T-SQL you typically deal with on a daily basis:

/****** Object:  Table [dbo].[Users]    Script Date: 03/31/2010 23:39:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Users](
	[ID] [int] IDENTITY(1,1) NOT NULL,
     [Name] [nvarchar](50) NULL,
     [NTUserName] [nvarchar](128) NULL,
     [Domain] [nvarchar](50) NOT NULL,
     [Intro] [nvarchar](100) NULL,
     [Title] [nvarchar](50) NOT NULL,
     [State] [nvarchar](10) NOT NULL,
     [Country] [nvarchar](100) NULL,
     [PWD] [varbinary](100) NULL,
     [rowguid] [uniqueidentifier] DEFAULT NEWSEQUENTIALID()
PRIMARY KEY CLUSTERED 
(
     [ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, 
IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, 
ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO

SET IDENTITY_INSERT [dbo].[Users] ON
INSERT [dbo].[Users] ([ID], [Name], [NTUserName], [Domain], [Intro], [Title], [State], [Country], [PWD]) VALUES (1, N'Herve Roggero', N'hroggero', N'PYNLOGIC', N'Enterprise and Azure Architect; Speaker. Expert knowledge in C#. Prev. mgmt exp.', N'Azure Architect', N'FL', N'US', 0xE8F97FBA9104D1EA5047948E6DFB67FACD9F5B73)

INSERT [dbo].[Users] ([ID], [Name], [NTUserName], [Domain], [Intro], [Title], [State], [Country], [PWD]) VALUES (2, N'Jim Mullis', N'jmullis', N'PYNLOGIC', N'Expert in software development. C++; Oracle; SQL Server DBA', N'', N'FL', N'US', 0xE8F97FBA9104D1EA5047948E6DFB67FACD9F5B73)

INSERT [dbo].[Users] ([ID], [Name], [NTUserName], [Domain], [Intro], [Title], [State], [Country], [PWD]) VALUES (3, N'Scott Klein', N'sklein', N'', N'Expert in software development. MVP SQL Server. Author. Speaker.', N'Architect', N'FL', N'US', 0xE8F97FBA9104D1EA5047948E6DFB67FACD9F5B73)
SET IDENTITY_INSERT [dbo].[Users] OFF

/****** Object:  StoredProcedure [dbo].[proc_CreateProfile]    Script Date: 03/31/2010 23:39:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[proc_CreateProfile]
    @uid [nvarchar](50),
    @pwd [nvarchar](50),
    @name [nvarchar](50),
    @title [nvarchar](50),
    @country [nvarchar](50),
    @state [nvarchar](20),
    @rowguid uniqueidentifier
WITH RECOMPILE, ENCRYPTION
AS
DECLARE @password varbinary(100)
SET @password = HASHBYTES('sha1', @pwd)

-- Make sure the UID is not already taken...
IF (Exists(SELECT TOP 1 * FROM Users WHERE NTUserName = @uid))
BEGIN
    RAISERROR(N'0x001 - User ID already in use', 16, 1)
END
ELSE
BEGIN
    INSERT INTO Users 
         (Name, NTUserName, Domain, Intro, Title, State, Country, PWD, rowguid)
    VALUES
         (@name, @uid, '', '', @title, @state, @country, @password, @rowguid)
 END
GO


Notice that the script enables several options, such as ANSI_NULL and ANSI_PADDING. Then, the script creates the Users table. This table has an IDENTITY column as well as a rowguid column that uses the uniqueidentifier database. The rowguid column also has a default on it, which uses the NEWSEQUENTIALID() function to automatically generate new GUIDs. This table is created on the PRIMARY file group, followed by the setting of several table options via the WITH clause.


Further down in the script, several stored procedures are created, one of which is shown in the preceding code snippet. proc_CreateProfile is a standard stored procedure that accepts several input parameters and uses the WITH option to specify procedure options: in this case, RECOMPILE (to indicate that the database engine doesn’t need to cache a plan for this procedure and to compile the procedure at runtime) and ENCRYPTION (indicating that SQL Server converts the text of this stored procedure to an obfuscated format).

Fixing the Script

Because you selected to script for SQL Server 2008, the script includes some syntax and statements that aren’t supported in SQL Azure. Figure 5 shows some of the errors you see if you try to run the script as generated.


Figure 5- SQL Azure execution errors


Another problem is that SQL Azure doesn’t support heap tables. A heap table is one without a clustered index. SQL Azure currently supports only clustered tables.


You need to make some changes for your script to run under SQL Azure. Here’s what to do:


1. Delete all instances of SET ANSI_NULLS ON.


2. Delete all instances of ON [PRIMARY].


3. Delete all instance of PAD_INDEX = OFF as well as ALLOW_ROW_LOCKS = ON and ALLOW_PAGE_LOCKS = ON.


4. In the Users table, modify the rowguid column, changing DEFAULT NEWSEQUENTIALID() to NULL.


5. In the stored procedure, remove the ENCRYPTION clause.


6. Add a clustered index to any heap tables.


Appendix B discusses the need for these changes in detail. For now, here’s a quick explanation:

  • ON [PRIMARY ] isn’t needed because, as you learned in Chapters 1 and 2, SQL Azure hides all hardware-specific access and information. There is no concept of PRIMARY or file groups because disk space is handled by Microsoft, so this option isn’t required.
  • According to SQL Server Books Online (BOL) you can remove the entire WITH clause that contains the table options. However, the only table options you really need to remove are those listed in step 3 (PAD_INDEX, ALLOW_ROW_LOCKS, and ALLOW_PAGE_LOCKS).
  • The NEWSEQUENTIALID() function isn’t supported in SQL Azure because there is no CLR support in SQL Azure, and thus all CLR-based types aren’t supported. The NEWSEQUENTIALID() return value is one of those types. Also, the ENCRYPTION option isn’t supported because SQL Azure as a whole doesn’t yet support encryption.
  • SQL Azure doesn’t support heap tables. Thus, you need to change any heap table into a clustered table by adding a clustered index. (Interestingly, if you execute one statement at a time, you can, in fact, create a heap table. However, any inserts into that table fail.)

A word about the final item in the list. The syntax for defining a clustered index looks like this:

CREATE TABLE [dbo].[UserDocs]
(
    [UserID] [int] NOT NULL,
    [DocID] [int] NOT NULL
PRIMARY KEY CLUSTERED
(
    [UserID], [DocID] ASC
)
)

One of the things the SQL Azure documentation suggests, and which is listed earlier, is to set the Convert UDDTs to Base Types property to True. This is because user-defined types aren’t supported in SQL Azure.


After you make the changes just described to your SQL script, it should look like the following:

/****** Object:  Table [dbo].[Users]    Script Date: 03/31/2010 23:39:20 ******/
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Users](
     [ID] [int] IDENTITY(1,1) NOT NULL,
     [Name] [nvarchar](50) NULL,
     [NTUserName] [nvarchar](128) NULL,
     [Domain] [nvarchar](50) NOT NULL,
     [Intro] [nvarchar](100) NULL,
     [Title] [nvarchar](50) NOT NULL,
     [State] [nvarchar](10) NOT NULL,
     [Country] [nvarchar](100) NULL,
     [PWD] [varbinary](100) NULL,
     [rowguid] [uniqueidentifier] NULL
PRIMARY KEY CLUSTERED 
(
     [ID] ASC
)WITH (STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF)
) 
GO
SET ANSI_PADDING OFF
GO

SET IDENTITY_INSERT [dbo].[Users] ON
INSERT [dbo].[Users] ([ID], [Name], [NTUserName], [Domain], [Intro], [Title], [State], [Country], [PWD]) VALUES (1, N'Herve Roggero', N'hroggero', N'PYNLOGIC', N'Enterprise and Azure Architect; Speaker. Expert knowledge in C#. Prev. mgmt exp.', N'Azure Architect', N'FL', N'US', 0xE8F97FBA9104D1EA5047948E6DFB67FACD9F5B73)

INSERT [dbo].[Users] ([ID], [Name], [NTUserName], [Domain], [Intro], [Title], [State], [Country], [PWD]) VALUES (2, N'Jim Mullis', N'jmullis', N'PYNLOGIC', N'Expert in software development. C++; Oracle; SQL Server DBA', N'', N'FL', N'US', 0xE8F97FBA9104D1EA5047948E6DFB67FACD9F5B73)

INSERT [dbo].[Users] ([ID], [Name], [NTUserName], [Domain], [Intro], [Title], [State], [Country], [PWD]) VALUES (3, N'Scott Klein', N'sklein', N'', N'Expert in software development. MVP SQL Server. Author. Speaker.', N'Architect', N'FL', N'US', 0xE8F97FBA9104D1EA5047948E6DFB67FACD9F5B73)
SET IDENTITY_INSERT [dbo].[Users] OFF

/****** Object:  StoredProcedure [dbo].[proc_CreateProfile]    Script Date: 03/31/2010 23:39:21 ******/
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[proc_CreateProfile]
    @uid [nvarchar](50),
    @pwd [nvarchar](50),
    @name [nvarchar](50),
    @title [nvarchar](50),
    @country [nvarchar](50),
    @state [nvarchar](20),
    @rowguid uniqueidentifier
WITH RECOMPILE
AS
DECLARE @password varbinary(100)
SET @password = HASHBYTES('sha1', @pwd)

-- Make sure the UID is not already taken...
IF (Exists(SELECT TOP 1 * FROM Users WHERE NTUserName = @uid))
BEGIN
    RAISERROR(N'0x001 - User ID already in use', 16, 1)
END
ELSE
BEGIN
    INSERT INTO Users 
         (Name, NTUserName, Domain, Intro, Title, State, Country, PWD, rowguid)
    VALUES
         (@name, @uid, '', '', @title, @state, @country, @password, @rowguid)
 END
GO


Now that you’ve made the necessary corrections, you’re ready to create your objects in a SQL Azure database.

Executing the Script against an Azure Database

You don’t have a SQL Azure database to run the script against, so let’s create one now:


1. Connect to your SQL Azure instance (refer to Chapter 2 for reference if needed), making sure you’re connecting to the master database.


2. Open a new query window, and use the syntax discussed in Chapter 2 to create your SQL Azure database. Name it TechBio, because this is the name the examples use throughout this chapter.


3. Click over to the generated script. This query window is currently connected to your local SQL instance, so you need to change it to your SQL Azure instance and the database you just created. Right-click anywhere in the script, and select Connection ~TRA Change Connection from the context menu.


4. In the Connect to Database Engine dialog, enter the information for your SQL Azure instance, and enter the name of the database you just created on the Connection Properties tab.


5. Click Connect.


You now have your script, a database, and a connection to that database. Click the Execute button. Your script should run and create the tables, procedures, and data in your SQL Azure Database.


The SQL Server Generate and Publish Script wizard is a great way to start understanding the required changes that need to be made when migrating to SQL Azure. With this foundation, let's discuss one of the other options, SQL Server Integration Services.

Author

Scott Klein

Scott Klein is co-founder of Blue Syntax, a consulting a training company specialized on the Azure platform. Scott has been working with SQL Server for nearly 20 years, working with companies large and small in fields such as medical, finance, and retail. Scott is a veteran author, having written a number of books including the soon to be released Pro SQL Azure. Scott is a SQL Server MVP and President of the South Florida SQL Server Users Group and the newly formed Azure PASS Virtual Chapter. Scott also organizes and runs the South Florida SQL Saturday and speaks frequently at SQL Saturday and .NET Code Camp events as well as user groups. He was lucky enough to speak at the 2008 European PASS conference and recently returned from Germany providing a 3-day deep dive training at the PASS Camp.

His online presences include: