June 9, 2014 at 7:50 am
...Clustered indexes are required for inserting data in this version of SQL Server.
I have imported an existing Database which is hosted in Azure SQL Database (Paas) into VS2013, thereby creating a Database Project.
When I build the project I am confronted with this error even though a.) the Project has been imported FROM Azure b.) the table definition scripts in Project (DDL) clearly contain the Primary Key constraints and also the 'key' symbol in the margin indicating a Primary Key.
If anyone can shed any light on this issue before I've found the solution that would be much appreciated. I'll report back soonest...
June 9, 2014 at 7:54 am
Have you confirmed that there is a clustered index on that table? The PK does not have to be clustered, and the clustered index does not have to be on PK columns.
June 9, 2014 at 8:47 am
I would agree, the table definition is indeed:
CONSTRAINT [<...>] PRIMARY KEY NONCLUSTERED
...but this DDL I extracted directly from the database which is currently sitting in Azure SQL Databases. So is this a bug in VS2013???
The restriction is in VS2013, not Azure SQL Databases. That's a bug surely?
June 9, 2014 at 9:09 am
Are you saying you extracted DDL from one SQL DB and have run it in another SQL DB and are trying to insert data into the second SQL DB?
June 9, 2014 at 11:30 am
I have only seen this error when I run a table creation script that doesn't have a primary key definition. You can't have a CREATE TABLE definition and an ALTER TABLE command to add the primary key. The primary key must be part of the CREATE TABLE script.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 9, 2014 at 12:41 pm
What I've done is imported a database which is currently hosted in Azure SQL Database (Paas) into VS2013; I imported it as the Source database into a Database Project.
Now, I am deploying that database project to another, different Azure SQL Database with no Tables, etc. but the project won't even Build because I have selected Azure SQL Database as the Target database platform in the Database Project configuration. These Clustered Index errors are returned when I try to build the Project, before I even get to the Deployment!
That is why I think it's a bug in VS2013; because the non-clustered Primary Keys exist actually exist in the Source Azure SQL Database (created using CREATE script) but they cannot be deployed there using VS2013... ergo: in mind that is a bug in VS2013. The Clustered Index restriction exists in VS2013 but not in Azure SQL Database.
June 9, 2014 at 12:48 pm
As I understand it, you can indeed create a table with no clustered index in SQL DB. But you just can't put data in it. I know, crazy.
Sorry I can't help you, but I used different tools for database management and deployment. Perhaps others can.
June 9, 2014 at 4:12 pm
Well, I have used Visual Studio 2013 to create a MASD database before, so I'd be inclined to think it might be something to do with your configuration possibly. Maybe how you're doing the deployment. Maybe the target of the deployment process. I'm not sure, but it does work.
I just went to Azure, reverse engineered a copy of AdventureWorks2012 from one server to my Visual Studio database project. I did a build and I did a publish, and it worked. When you do the build, is it all databases that error out, or just the one? If just the one, can you do the build without it?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 10, 2014 at 1:29 am
I think I know what the problem is; it's not the use of VS2013 per se but certain restrictions which are built in to VS around Azure and probably lagging behind Azure SQL Database functionality.
VS2013 is preventing the build because the Database project contains DDL for some tables with no Clustered Index, only Primary Keys. However, Azure SQL Database itself has no such restriction, the fact that the source database is already in Azure is testimony to that.
I've tried changing the Target Platform setting to SQL Server 2012 to try to fool VS but it won't let you deploy to Azure with that setting. This is clearly a bug in VS2013 as the DDL scripts execute just fine directly on the Azure SQL Database but not when Deployed through the VS2013 Database project.
June 10, 2014 at 4:40 am
But it's like Karen already said. You can create a table in Azure without a clustered index, but you can't put data into it, so what's the point? If anything, it's not a bug in VS2013. It's a bug in Azure that VS2013 is helping you avoid.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 10, 2014 at 5:26 am
Yep, that sounds about right. I'm also checking with Microsoft regarding this and will post any new info here. Much appreciated all!!!
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply