SQLAzure Upgrade to 5.2 SQL exception on ALTER TABLE [sf_scr - Sitefinity - On Premise – Configurations & Setup

All Replies

Posted by Community Admin on 11-Dec-2012 00:00

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

Posted by Community Admin on 11-Dec-2012 00:00

001./*

002.

003. Open DBDiff 0.8.9.61

004. http://opendbiff.codeplex.com/

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

Posted by Community Admin on 11-Dec-2012 00:00

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.

This thread is closed