No clustered index on table CompletedScope

Vote:
 

Today I was deploying an existing database to SQL Azure with the help of SQL Management Studio and it first failed because there were no clustered index on the table CompletedScope. I created one and after that it was all good, but you should create a clustered index on that table (it did not have any primary key, so that needs to be added also).

#88054
Jun 30, 2014 20:24
Vote:
 

No it did not go ok, there were a problem with hints also, here are the error message:

TITLE: Microsoft SQL Server Management Studio
------------------------------

Could not import package.
Warning SQL0: A project which specifies SQL Server 2012 as the target platform may experience compatibility issues with SQL Azure.
Error SQL72014: .Net SqlClient Data Provider: Msg 40512, Level 16, State 1, Procedure InsertCompletedScope, Line 8 Deprecated feature 'Multiple table hints without comma' is not supported in this version of SQL Server.
Error SQL72045: Script execution error. The executed script:
CREATE PROCEDURE [dbo].[InsertCompletedScope]
@instanceID UNIQUEIDENTIFIER, @completedScopeID UNIQUEIDENTIFIER, @state IMAGE
AS
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET NOCOUNT ON;
UPDATE [dbo].[CompletedScope] WITH (ROWLOCK, UPDLOCK)
SET state = @state,
modified = GETUTCDATE()
WHERE completedScopeID = @completedScopeID;
IF (@@ROWCOUNT = 0)
BEGIN
INSERT INTO [dbo].[CompletedScope] WITH (ROWLOCK)
VALUES (@instanceID, @completedScopeID, @state, GETUTCDATE());
END
RETURN;
RETURN;


(Microsoft.SqlServer.Dac)

#88055
Jun 30, 2014 20:26
Vote:
 

I changed to this

ALTER PROCEDURE [dbo].[InsertCompletedScope]
@instanceID uniqueidentifier,
@completedScopeID uniqueidentifier,
@state image
As
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
SET NOCOUNT ON
		UPDATE [dbo].[CompletedScope] WITH(ROWLOCK, UPDLOCK) 
		    SET state = @state,
		    modified = GETUTCDATE()
		    WHERE completedScopeID=@completedScopeID 
		IF ( @@ROWCOUNT = 0 )
		BEGIN
			--Insert Operation
			INSERT INTO [dbo].[CompletedScope] WITH(ROWLOCK)
			VALUES(@instanceID, @completedScopeID, @state, GETUTCDATE()) 
		END
		RETURN
RETURN

And it all worked fine!

#88100
Jul 01, 2014 21:13
Vote:
 

These tables (CompletedScope etc) is created by Microsoft Workflow Foundation 3.5 and is not supported on Azure. In later versions of EPiServer CMS we no longer install Microsoft Workflows, to make it easier to deploy to Azure.

So either patch the tables as above, or remove the tables altogether. But also disable Workflows as described in the SDK to avoid running an unsupported version.

See workflows section in SDK about deploying to Azure:

http://world.episerver.com/documentation/Items/Developers-Guide/EPiServer-CMS/8/Deployment/Deployment-scenarios/Deploying-to-Azure-webapps/

#120042
Apr 09, 2015 10:18
Vote:
 

Thanks Per, I noticed that and with the latest update I did not get any error at all deploying to Azure.

Thaks for the great work!!

#120046
Apr 09, 2015 10:30
This topic was created over six months ago and has been resolved. If you have a similar question, please create a new topic and refer to this one.
* You are NOT allowed to include any hyperlinks in the post because your account hasn't associated to your company. User profile should be updated.