I was trying to execute the following SQL Query in SQL Azure and got this error “

Msg 40054, Level 16, State 1, Line 2

Tables without a clustered index are not supported in this version of SQL Server. Please create a clustered index and try again”

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}

.csharpcode pre { margin: 0em; }

.csharpcode .rem { color: #008000; }

.csharpcode .kwrd { color: #0000ff; }

.csharpcode .str { color: #a31515; }

.csharpcode .op { color: #0000c0; }

.csharpcode .preproc { color: #cc6633; }

.csharpcode .asp { background-color: #ffff00; }

.csharpcode .html { color: #800000; }

.csharpcode .attr { color: #ff0000; }

.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}

.csharpcode .lnum { color: #606060; }

/****** Object:  Table [dbo].[Department]    Script Date: 2/18/2013 9:42:19 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Department](
    [DepartmentID] [int] IDENTITY(1,1) NOT NULL,
    [DepartmentName] [varchar](50) NOT NULL,
    [DepartmentCode] [char](5) NOT NULL,

 CONSTRAINT [PK_Department] PRIMARY KEY 
(
    [DepartmentId] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF)

)


GO
/****** Object:  Table [dbo].[Employee]    Script Date: 2/18/2013 9:42:19 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Employee](
    [EmpID] [int] IDENTITY(1,1) NOT NULL,
    [EmpName] [varchar](50) NOT NULL,
    [EmpAge] [int] NOT NULL,
    [EmpExperience] [decimal](5, 2) NULL,
 CONSTRAINT [PK_Employee] PRIMARY KEY
(
    [EmpID] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF)
)

I’ve learn that the SQL Azure does not support tables that are without clustered Index. The fix for this is to tweak the above SQL Query to included clustered index.

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}

.csharpcode pre { margin: 0em; }

.csharpcode .rem { color: #008000; }

.csharpcode .kwrd { color: #0000ff; }

.csharpcode .str { color: #a31515; }

.csharpcode .op { color: #0000c0; }

.csharpcode .preproc { color: #cc6633; }

.csharpcode .asp { background-color: #ffff00; }

.csharpcode .html { color: #800000; }

.csharpcode .attr { color: #ff0000; }

.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}

.csharpcode .lnum { color: #606060; }

/****** Object:  Table [dbo].[Department]    Script Date: 2/18/2013 9:42:19 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Department](
    [DepartmentID] [int] IDENTITY(1,1) NOT NULL,
    [DepartmentName] [varchar](50) NOT NULL,
    [DepartmentCode] [char](5) NOT NULL,

 CONSTRAINT [PK_Department] PRIMARY KEY CLUSTERED 
(
    [DepartmentId] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF)

)


GO
/****** Object:  Table [dbo].[Employee]    Script Date: 2/18/2013 9:42:19 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Employee](
    [EmpID] [int] IDENTITY(1,1) NOT NULL,
    [EmpName] [varchar](50) NOT NULL,
    [EmpAge] [int] NOT NULL,
    [EmpExperience] [decimal](5, 2) NULL,
 CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED 
(
    [EmpID] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF)
)

The above changes highlighted in yellow has  solved my issue of “

Msg 40054, Level 16, State 1, Line 2

Tables without a clustered index are not supported in this version of SQL Server. Please create a clustered index and try again”"

 Subscribe to my blog

These days I’m doing lot of proof-of-concepts around the SharePoint 2013 App Model and how an ASP.NET MVC 3 application can be consumed/integrated in the SharePoint 2013 with the help of the App Model. As a first-step, I was trying to migrate a SQL 2008 database of an ASP.NET MVC3 application to Windows Azure. When it comes to migrating the database to Azure, there are multiple options available :-

a)Generate Insert SQL Scripts (compatible for SQL Azure) using ‘Tasks —> Generate Scripts’ in SQL Management Studio

b)Directly deploy the on-premise database to SQL Azure ‘Tasks’ —> Deploy Database to SQL Azure

c)Generate .dacpac export of SQL data + Schema and import it in SQL Azure

Since my database schema is not complex and data is also very less, I’m leveraging the 1’st approach.

Right-click on SQL database — > Tasks –> Generate Scripts

Select the list of tables to be migrated

Advanced Settings –>

Select ‘Sql Azure Database’ for Database Engine Type and ‘Schema and data’ for Types of data to script.

Select the location to save generated ‘.sql’file

You’ll see successful creation of .sql files

Log in to Windows Azure Management Portal and create new instance of SQL

Specify SQL Access Account

Select ‘Manage’ option for SQL Server and open a new Query window

Click ‘Run’. Now you are all set.