+1-888-365-2779
Try Now
More in this section

Forums / Deployment / SQLAzure Upgrade to 5.2 SQL exception on ALTER TABLE [sf_scrty_rts_sf_prmssns_nhrtnc]

SQLAzure Upgrade to 5.2 SQL exception on ALTER TABLE [sf_scrty_rts_sf_prmssns_nhrtnc]

3 posts, 0 answered
  1. Webdev
    Webdev avatar
    6 posts
    Registered:
    20 Sep 2012
    11 Dec 2012
    Link to this post
    We're attempting to upgrade our local sitefinity 5.1 install to 5.2. We're using SQLAzure as the database provider. Everything worked fine through the upgrade process, and we built the project. When loading up the sitefinity site for the first time after the upgrade, we get the following error:

    'ALTER TABLE [sf_scrty_rts_sf_prmssns_nhrtnc] DROP CONSTRAINT [pk_sf_scrty_rts_sf_prmssns_nhr]


    [SQLException: Tables without a clustered index are not supported in this version of SQL Server. Please create a clustered index and try again.
    Could not drop constraint. See previous errors.
    The statement has been terminated.]
    Telerik.OpenAccess.RT.Adonet2Generic.Impl.StatementImp.executeUpdate(String sql) +298
    Telerik.OpenAccess.RT.Adonet2Generic.Impl.StatementImp.execute(String sql) +9
    OpenAccessRuntime.DataObjects.tools.ant.DataObjectsTaskBase.ExecuteSqlStatements(IEnumerable`1 statements, Connection con) +283


    [OpenAccessException: SQL exception on 'ALTER TABLE [sf_scrty_rts_sf_prmssns_nhrtnc] DROP CONSTRAINT [pk_sf_scrty_rts_sf_prmssns_nhr]' : Tables without a clustered index are not supported in this version of SQL Server. Please create a clustered index and try again.
    Could not drop constraint. See previous errors.
    The statement has been terminated.]
    OpenAccessRuntime.ExceptionWrapper.Throw() +13
    OpenAccessRuntime.DataObjects.tools.ant.DataObjectsTaskBase.throwBuildException(String str, Exception e) +72
    OpenAccessRuntime.DataObjects.tools.ant.DataObjectsTaskBase.ExecuteSqlStatements(IEnumerable`1 statements, Connection con) +493
    OpenAccessRuntime.DataObjects.tools.ant.ExecuteScriptTask.runScript(IEnumerable`1 script) +128
    OpenAccessRuntime.DataObjects.tools.ant.ExecuteScriptTask.execute() +451


  2. Webdev
    Webdev avatar
    6 posts
    Registered:
    20 Sep 2012
    11 Dec 2012
    Link to this post


    001./*
    002. 
    003.    Open DBDiff 0.8.9.61
    005.

    011. 
    012.*/
    013. 
    014.ALTER TABLE [dbo].[sf_libraries] DROP CONSTRAINT [pk_sf_libraries]
    015.GO
    016.DROP INDEX [idx_sf_lst_sf_subscriber_id] ON [dbo].[sf_lst_sf_subscriber]
    017.GO
    018.DROP INDEX [idx_sf_profile_link_user_id] ON [dbo].[sf_user_profile_link]
    019.GO
    020.DROP INDEX [idx_sf_ns_sn_list_subscriber] ON [dbo].[sf_notif_subscriptions]
    021.GO
    022.DROP INDEX [idx_click_stat_camp_subscr] ON [dbo].[sf_link_click_stat]
    023.GO
    024.DROP INDEX [idx_sf_ns_slist_resolvekey] ON [dbo].[sf_notif_subscr_list]
    025.GO
    026.DROP INDEX [idx_sf_open_stat_camp_subscr] ON [dbo].[sf_open_stat]
    027.GO
    028.DROP INDEX [idx_sf_url_data_id2] ON [dbo].[sf_url_data]
    029.GO
    030.DROP INDEX [idx_sf_url_type] ON [dbo].[sf_url_data]
    031.GO
    032.DROP INDEX [idx_sf_ns_sn_subscriber] ON [dbo].[sf_notif_subscriptions]
    033.GO
    034.DROP INDEX [idx_sf_bounce_stat_camp_subscr] ON [dbo].[sf_bounce_stat]
    035.GO
    036.DROP INDEX [idx_sf_ab_campaign_campaign_a] ON [dbo].[sf_ab_campaign]
    037.GO
    038.DROP INDEX [idx_sf_ab_campaign_campaign_b] ON [dbo].[sf_ab_campaign]
    039.GO
    040.DROP INDEX [idx_delivery_entry_camp_subscr] ON [dbo].[sf_delivery_entry]
    041.GO
    042.DROP INDEX [idx_sf_draft_pages_page_id] ON [dbo].[sf_draft_pages]
    043.GO
    044.DROP INDEX [idx_sf_presentation_data_id4] ON [dbo].[sf_presentation_data]
    045.GO
    046.DROP INDEX [idx_sf_presentation_data] ON [dbo].[sf_presentation_data]
    047.GO
    048.DROP INDEX [idx_sf_permissions] ON [dbo].[sf_permissions]
    049.GO
    050.DROP INDEX [idx_permission_setname] ON [dbo].[sf_permissions]
    051.GO
    052.DROP INDEX [idx_sf_object_data_page_id] ON [dbo].[sf_object_data]
    053.GO
    054.DROP INDEX [idx_sf_unsubscr_info_camp_sub] ON [dbo].[sf_unsubscription_info]
    055.GO
    056.DROP INDEX [idx_sf_ns_sr_resolvekey] ON [dbo].[sf_notif_subscribers]
    057.GO
    058.DROP TABLE [dbo].[sf_notif_msg_job]
    059.GO
    060.DROP TABLE [dbo].[sf_notif_cmplt_msg_job]
    061.GO
    062.DROP TABLE [dbo].[sf_notif_msg_job_log]
    063.GO
    064.DROP TABLE [dbo].[sf_ntf_msg_jb_cstm_mssg_haders]
    065.GO
    066.DROP TABLE [dbo].[sf_ntf_sbscrbrs_cstm_prperties]
    067.GO
    068.DROP TABLE [dbo].[sf_mb_dynmc_mdl_fld_sf_prmssn2]
    069.GO
    070.DROP TABLE [dbo].[sf_issue_subscriber_report]
    071.GO
    072.DROP TABLE [dbo].[sf_failed_deliveries]
    073.GO
    074.DROP TABLE [dbo].[sf_mb_dynmc_mdl_typ_sf_prmssn2]
    075.GO
    076.DROP TABLE [dbo].[sf_mb_dynmc_mdl_typ_sf_prmssns]
    077.GO
    078.DROP TABLE [dbo].[sf_mb_dynmc_mdl_fld_sf_prmssns]
    079.GO
    080.ALTER TABLE [dbo].[sf_url_data] DROP COLUMN [item_type]
    081.GO
    082.ALTER TABLE [dbo].[sf_unsubscription_info] DROP COLUMN [sf_campaign_id]
    083.GO
    084.ALTER TABLE [dbo].[sf_notif_msg_template] ALTER COLUMN [module_name] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
    085.GO
    086.ALTER TABLE [dbo].[sf_ab_campaign] DROP COLUMN [winner_issue_id],[testing_note],[root_campaign_id],[nme],[date_sent],[conclusion]
    087.GO
    088.ALTER TABLE [dbo].[sf_page_templates] DROP COLUMN [framework]
    089.GO
    090.ALTER TABLE [dbo].[sf_subscriber] DROP COLUMN [is_suspended]
    091.GO
    092.ALTER TABLE [dbo].[sf_permissions] ALTER COLUMN [set_name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
    093.GO
    094.ALTER TABLE [dbo].[sf_page_data] DROP COLUMN [build_stamp]
    095.GO
    096.ALTER TABLE [dbo].[sf_page_node] ALTER COLUMN [extension] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
    097.GO
    098.ALTER TABLE [dbo].[sf_page_node] DROP COLUMN [module_name],[extension_fr],[extension_en]
    099.GO
    100.ALTER TABLE [dbo].[sf_mb_dynamic_module_field] DROP COLUMN [inherits_permissions],[field_namespace],[can_inherit_permissions]
    101.GO
    102.ALTER TABLE [dbo].[sf_mb_dynamic_module_type] DROP COLUMN [parentTypeId],[pageId],[is_slf_referencing],[inherits_permissions],[check_field_permissions],[can_inherit_permissions]
    103.GO
    104.ALTER TABLE [dbo].[sf_notif_msg_template] ALTER COLUMN [subject] [varchar] (80) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
    105.GO
    106.ALTER TABLE [dbo].[sf_mb_dynamic_module_field] ALTER COLUMN [choices] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
    107.GO
    108.ALTER TABLE [dbo].[sf_form_description] DROP COLUMN [subscr_lst_id]
    109.GO
    110.ALTER TABLE [dbo].[sf_dynamic_content] DROP COLUMN [system_parent_type],[system_parent_id]
    111.GO
    112.ALTER TABLE [dbo].[sf_lst] DROP COLUMN [unsubscribe_page_id],[total_unsubscriptions]
    113.GO
    114.ALTER TABLE [dbo].[sf_notif_subscribers] DROP COLUMN [module_name],[disabled],[account_name]
    115.GO
    116.ALTER TABLE [dbo].[sf_notif_subscr_list] DROP COLUMN [title],[resolve_key],[account_name]
    117.GO
    118.ALTER TABLE [dbo].[sf_campaign] DROP COLUMN [rootCampaign_id],[related_notification_job]
    119.GO
    120.ALTER TABLE [dbo].[sf_notif_subscr_list] ALTER COLUMN [module_name] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
    121.GO
    122.ALTER TABLE [dbo].[sf_bounce_stat] DROP COLUMN [message_status]
    123.GO
    124.ALTER TABLE [dbo].[sf_notif_msg_template] DROP COLUMN [account_name]
    125.GO
    126.CREATE TABLE [dbo].[Tempsf_libraries]
    127.(
    128.    [allow_comments] [tinyint] NULL,
    129.    [allow_track_backs] [tinyint] NULL,
    130.    [app_name] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    131.    [approve_comments] [tinyint] NULL,
    132.    [content_id] [uniqueidentifier] NOT NULL,
    133.    [content_state] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    134.    [date_created] [datetime] NULL,
    135.    [default_page_id] [uniqueidentifier] NULL,
    136.    [draft_culture] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    137.    [email_author] [tinyint] NULL,
    138.    [expiration_date] [datetime] NULL,
    139.    [last_modified] [datetime] NULL,
    140.    [last_modified_by] [uniqueidentifier] NULL,
    141.    [original_content_id] [uniqueidentifier] NULL,
    142.    [ownr] [uniqueidentifier] NULL,
    143.    [post_rights] [int] NOT NULL,
    144.    [publication_date] [datetime] NOT NULL,
    145.    [status] [int] NOT NULL,
    146.    [vrsion] [int] NOT NULL,
    147.    [views_count] [int] NOT NULL,
    148.    [visible] [tinyint] NOT NULL,
    149.    [votes_count] [bigint] NOT NULL,
    150.    [votes_sum] [numeric] (20,10) NOT NULL,
    151.    [can_inherit_permissions] [tinyint] NOT NULL,
    152.    [inherits_permissions] [tinyint] NOT NULL,
    153.    [max_item_size] [bigint] NOT NULL,
    154.    [max_size] [bigint] NOT NULL,
    155.    [voa_class] [int] NOT NULL,
    156.    [voa_version] [smallint] NOT NULL,
    157.    [new_size] [int] NULL,
    158.    [resize_on_upload] [tinyint] NULL,
    159.    [title_] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    160.    [url_name_] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    161.    [description_] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    162.    [dummy_] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    163.    [dummy_2] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    164.    [dummy_3] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    165.    [source_key] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    166.    [url_name_fr] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    167.    [title_en] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    168.    [description_en] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    169.    [description_fr] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    170.    [title_fr] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    171.    [url_name_en] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    172.    [dummy_en] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    173.    [dummy_fr] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    174.    [dummy_fr2] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    175.    [dummy_en2] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    176.    [dummy_en3] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    177.    [dummy_fr3] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    178.    [url_name_en_us] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    179.    [title_en_us] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    180.    [description_en_us] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    181.    [dummy_en_us] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    182.    [dummy_en_us2] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    183.    [dummy_en_us3] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    184.    [blob_storage] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    185.    [cache_profile] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    186.    [client_cache_profile] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    187.    [item_default_url_fr] [nvarchar] (510) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    188.    [item_default_url_en] [nvarchar] (510) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    189.    [item_default_url_] [nvarchar] (510) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    190.    [running_task] [uniqueidentifier] NULL,
    191.    [security_provider] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
    192. 
    193.)
    194.GO
    195. 
    196.INSERT INTO [dbo].[Tempsf_libraries] ([allow_comments],[allow_track_backs],[app_name],[approve_comments],[content_id],[content_state],[date_created],[default_page_id],[draft_culture],[email_author],[expiration_date],[last_modified],[last_modified_by],[original_content_id],[ownr],[post_rights],[publication_date],[status],[vrsion],[views_count],[visible],[votes_count],[votes_sum],[can_inherit_permissions],[inherits_permissions],[max_item_size],[max_size],[voa_class],[voa_version],[new_size],[resize_on_upload],[title_],[url_name_],[description_],[dummy_],[dummy_2],[dummy_3],[source_key],[url_name_fr],[title_en],[description_en],[description_fr],[title_fr],[url_name_en],[dummy_en],[dummy_fr],[dummy_fr2],[dummy_en2],[dummy_en3],[dummy_fr3],[url_name_en_us],[title_en_us],[description_en_us],[dummy_en_us],[dummy_en_us2],[dummy_en_us3],[blob_storage],[cache_profile],[client_cache_profile],[item_default_url_fr],[item_default_url_en],[item_default_url_],[running_task],[security_provider]) SELECT [allow_comments],[allow_track_backs],[app_name],[approve_comments],[content_id],[content_state],[date_created],[default_page_id],[draft_culture],[email_author],[expiration_date],[last_modified],[last_modified_by],[original_content_id],[ownr],[post_rights],[publication_date],[status],[vrsion],[views_count],[visible],[votes_count],[votes_sum],[can_inherit_permissions],[inherits_permissions],[max_item_size],[max_size],[voa_class],0,[new_size],[resize_on_upload],[title_],[url_name_],[description_],[dummy_],[dummy_2],[dummy_3],[source_key],[url_name_fr],[title_en],[description_en],[description_fr],[title_fr],[url_name_en],[dummy_en],[dummy_fr],[dummy_fr2],[dummy_en2],[dummy_en3],[dummy_fr3],[url_name_en_us],[title_en_us],[description_en_us],[dummy_en_us],[dummy_en_us2],[dummy_en_us3],[blob_storage],[cache_profile],[client_cache_profile],[item_default_url_fr],[item_default_url_en],[item_default_url_],[running_task],[security_provider] FROM [dbo].[sf_libraries]
    197.DROP TABLE [dbo].[sf_libraries]
    198.GO
    199.EXEC sp_rename N'[dbo].[Tempsf_libraries]',N'sf_libraries', 'OBJECT'
    200.GO
    201. 
    202. 
    203.CREATE UNIQUE NONCLUSTERED INDEX [idx_sf_permissions] ON [dbo].[sf_permissions]
    204.(
    205.    [app_name] ASC,
    206.    [object_id] ASC,
    207.    [principal_id] ASC,
    208.    [set_name] ASC
    209.) WITH (STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY  = OFF)
    210.GO
    211.CREATE NONCLUSTERED INDEX [idx_sf_presentation_data] ON [dbo].[sf_presentation_data]
    212.(
    213.    [embedded_template_name] ASC,
    214.    [resource_assembly_name] ASC,
    215.    [app_name] ASC
    216.) WITH (STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY  = OFF)
    217.GO
    218.CREATE NONCLUSTERED INDEX [idx_permission_setname] ON [dbo].[sf_permissions]
    219.(
    220.    [set_name] ASC
    221.) WITH (STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY  = OFF)
    222.GO
    223.ALTER TABLE [dbo].[sf_libraries] ADD CONSTRAINT [pk_sf_libraries] PRIMARY KEY CLUSTERED
    224.    (
    225.        [content_id] ASC
    226.    ) WITH (STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY  = OFF)
    227.GO
    228.ALTER TABLE [dbo].[sf_scrty_rts_sf_prmssns_nhrtnc] WITH NOCHECK ADD CONSTRAINT [ref_sf_scrty_rts_sf_prmssns_n2] FOREIGN KEY
    229.    (
    230.        [sf_prmssons_inheritance_map_id]
    231.    )
    232.    REFERENCES [dbo].[sf_permissions_inheritance_map]
    233.    (
    234.        [sf_prmssons_inheritance_map_id]
    235.    )
    236.GO
  3. Webdev
    Webdev avatar
    6 posts
    Registered:
    20 Sep 2012
    11 Dec 2012 in reply to Webdev
    Link to this post
    Using opendbiff I compared my backup of the databases to see what's changed. I guess I'll try another copy of the database in case there was something in my process that caused this.
3 posts, 0 answered