4.1 upgrade sql exception ALTER TABLE sf_subscriber_sf_lst
After upgrade when I access any page I see. What to do?
Server Error in '/' Application.
The ALTER TABLE statement conflicted with the
FOREIGN KEY constraint "ref_sf_sbscriber_sf_lst_sf_lst". The conflict
occurred in database "egmission_dev", table "dbo.sf_lst", column 'id'.
Description: An unhandled exception occurred during
the execution of the current web request. Please review the stack trace
for more information about the error and where it originated in the
code.
Exception Details: Telerik.OpenAccess.RT.sql.SQLException:
The ALTER TABLE statement conflicted with the FOREIGN KEY constraint
"ref_sf_sbscriber_sf_lst_sf_lst". The conflict occurred in database
"egmission_dev", table "dbo.sf_lst", column 'id'.
Source Error:
An unhandled exception was generated during the execution of the current
web request. Information regarding the origin and location of the
exception can be identified using the exception stack trace below.
Stack Trace:
[SQLException: The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "ref_sf_sbscriber_sf_lst_sf_lst". The conflict occurred in database "egmission_dev", table "dbo.sf_lst", column 'id'.]
Telerik.OpenAccess.RT.Adonet2Generic.Impl.StatementImp.executeUpdate(String sql) +420
Telerik.OpenAccess.RT.Adonet2Generic.Impl.StatementImp.execute(String sql) +9
OpenAccessRuntime.DataObjects.tools.ant.DataObjectsTaskBase.ExecuteSqlScript(String script, Connection con, SqlDriver sqlDriver, Boolean noShredder) +459
[OpenAccessException: SQL exception on 'ALTER TABLE [sf_subscriber_sf_lst] ADD CONSTRAINT [ref_sf_sbscriber_sf_lst_sf_lst] FOREIGN KEY ([id]) REFERENCES [sf_lst]([id])' : The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "ref_sf_sbscriber_sf_lst_sf_lst". The conflict occurred in database "egmission_dev", table "dbo.sf_lst", column 'id'.]
Telerik.OpenAccess.RT.ExceptionWrapper.Throw() +13
OpenAccessRuntime.DataObjects.tools.ant.DataObjectsTaskBase.throwBuildException(String str, Exception e) +38
OpenAccessRuntime.DataObjects.tools.ant.DataObjectsTaskBase.ExecuteSqlScript(String script, Connection con, SqlDriver sqlDriver, Boolean noShredder) +639
OpenAccessRuntime.DataObjects.tools.ant.DataObjectsTaskBase.runScript(String script) +142
OpenAccessRuntime.DataObjects.tools.ant.ExecuteScriptTask.execute() +359
[OpenAccessException: Telerik.OpenAccess.OpenAccessException: SQL exception on 'ALTER TABLE [sf_subscriber_sf_lst] ADD CONSTRAINT [ref_sf_sbscriber_sf_lst_sf_lst] FOREIGN KEY ([id]) REFERENCES [sf_lst]([id])' : The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "ref_sf_sbscriber_sf_lst_sf_lst". The conflict occurred in database "egmission_dev", table "dbo.sf_lst", column 'id'.]
Telerik.OpenAccess.RT.ExceptionWrapper.Throw() +13
OpenAccessRuntime.DataObjects.tools.ant.DataObjectsTaskBase.throwBuildException(String str, Exception e) +38
OpenAccessRuntime.DataObjects.tools.ant.ExecuteScriptTask.execute() +718
Telerik.OpenAccess.RT.OpenAccessSchemaAdapter.ExecuteDDLScript(String ddl) +546
Telerik.OpenAccess.SchemaHandlerImpl.Telerik.OpenAccess.ISchemaHandler.ExecuteDDLScript(String ddl) +67
Telerik.Sitefinity.Data.OA.OpenAccessConnection.UpdateMetadata(MetadataSource metadataSource, String moduleName, List`1 dynamicTypesToRegister, IOpenAccessUpgradableProvider upgradable) +2105
[Exception: Unable to upgrade database schema metadataSource provided by 'Telerik.Sitefinity.Security.Data.OpenAccessRoleProvider': Telerik.OpenAccess.OpenAccessException: SQL exception on 'ALTER TABLE [sf_subscriber_sf_lst] ADD CONSTRAINT [ref_sf_sbscriber_sf_lst_sf_lst] FOREIGN KEY ([id]) REFERENCES [sf_lst]([id])' : The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "ref_sf_sbscriber_sf_lst_sf_lst". The conflict occurred in database "egmission_dev", table "dbo.sf_lst", column 'id'.]
DynamicModule.ns.Wrapped_OpenAccessRoleProvider_3af6a76a533c495cadb0a8f41fceee9a.Initialize(String providerName, NameValueCollection config, Type managerType) +405
Telerik.Sitefinity.Data.ManagerBase`1.InstantiateProvider(IDataProviderSettings providerSettings, Type providerType, ExceptionPolicyName policy, ManagerBase`1 manager) +2211
Telerik.Sitefinity.Data.ManagerBase`1.InstantiateProvider(IDataProviderSettings providerSettings, ExceptionPolicyName policy, ManagerBase`1 manager) +74
Telerik.Sitefinity.Data.ManagerBase`1.SetProvider(String providerName, String transactionName) +214
[TargetInvocationException: Exception has been thrown by the target of an invocation.]
System.RuntimeTypeHandle.CreateInstance(RuntimeType type, Boolean publicOnly, Boolean noCheck, Boolean& canBeCached, RuntimeMethodHandleInternal& ctor, Boolean& bNeedSecurityCheck) +0
System.RuntimeType.CreateInstanceSlow(Boolean publicOnly, Boolean skipCheckThis, Boolean fillCache) +117
System.RuntimeType.CreateInstanceDefaultCtor(Boolean publicOnly, Boolean skipVisibilityChecks, Boolean skipCheckThis, Boolean fillCache) +247
System.Activator.CreateInstance() +88
Telerik.Sitefinity.Data.ManagerBase`1.GetManager(String providerName, String transactionName) +115
Telerik.Sitefinity.Security.SitefinityPrincipal.Initialize() +854
Telerik.Sitefinity.Security.SitefinityPrincipal.get_Roles() +16
Telerik.Sitefinity.Configuration.RolePolicyHandler.GetPolicyNames(ConfigProvider configProvider, ConfigSection section) +62
Telerik.Sitefinity.Configuration.ConfigElement.GetValueFromPolicy(ConfigPolicyHandler handler, ConfigProperty prop, Object& value) +159
Telerik.Sitefinity.Configuration.ConfigElement.GetPolicyValue(ConfigProperty prop, Object& value) +102
Telerik.Sitefinity.Configuration.ConfigElement.get_Item(ConfigProperty prop, ConfigPolicyHandler policyHandler, String policyName) +42
Telerik.Sitefinity.Configuration.ConfigElement.get_Item(String propertyName) +56
Telerik.Sitefinity.Security.Configuration.SecurityConfig.get_ApplicationRoles() +28
Telerik.Sitefinity.Security.SitefinityPrincipal.get_IsBackendUser() +86
Telerik.Sitefinity.Security.SecurityManager.AuthenticateRequest(HttpContextBase context) +1062
Telerik.Sitefinity.Web.SitefinityHttpModule.Context_AuthenticateRequest(Object sender, EventArgs e) +104
System.Web.SyncEventExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute() +80
System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously) +266
Version Information: Microsoft .NET Framework Version:4.0.30319; ASP.NET Version:4.0.30319.225
Hi Alex,
We've identified the issue with the upgrade of the Newsletters module data. The fix requires deleting all your Newsletters related data (lists, subscribers, campaigns).
If you have just given a try of the module and the data can be safely deleted, run the following SQL script against your database (after stopping the project manager and IIS):
IF OBJECT_ID('sf_message_body', 'U') ISNOTNULLDELETEFROMsf_message_body
IF OBJECT_ID('sf_campaing_link', 'U') ISNOTNULLDELETEFROMsf_campaign_link
IF OBJECT_ID('sf_ab_campaign', 'U') ISNOTNULLDELETEFROMsf_ab_campaign
IF OBJECT_ID('sf_campaign', 'U') ISNOTNULLDELETEFROMsf_campaign
IF OBJECT_ID('sf_subscription_info', 'U') ISNOTNULLDELETEFROMsf_subscription_info
IF OBJECT_ID('sf_unsubscription_info', 'U') ISNOTNULLDELETEFROMsf_unsubscription_info
IF OBJECT_ID('sf_subscriber', 'U') ISNOTNULLDELETEFROMsf_subscriber
IF OBJECT_ID('sf_subscriber_sf_lst', 'U') ISNOTNULLDELETEFROMsf_subscriber_sf_lst
IF OBJECT_ID('sf_subscriber_sf_lst2', 'U') ISNOTNULLDELETEFROMsf_subscriber_sf_lst2
IF OBJECT_ID('sf_lst', 'U') ISNOTNULLDELETEFROMsf_lst
IF OBJECT_ID('sf_link_click_stat', 'U') ISNOTNULLDELETEFROMsf_link_click_stat
IF OBJECT_ID('sf_dynamic_list', 'U') ISNOTNULLDELETEFROMsf_dynamic_list
IF OBJECT_ID('sf_delivery_entry', 'U') ISNOTNULLDELETEFROMsf_delivery_entry
IF OBJECT_ID('sf_open_stat', 'U') ISNOTNULLDELETEFROMsf_open_stat
Thank you very much, Ivan!
Formatted version of SQL code:
IF OBJECT_ID('sf_message_body', 'U') IS NOT NULL DELETE FROM sf_message_body IF OBJECT_ID('sf_campaing_link', 'U') IS NOT NULL DELETE FROM sf_campaign_link IF OBJECT_ID('sf_ab_campaign', 'U') IS NOT NULL DELETE FROM sf_ab_campaign IF OBJECT_ID('sf_campaign', 'U') IS NOT NULL DELETE FROM sf_campaign IF OBJECT_ID('sf_subscription_info', 'U') IS NOT NULL DELETE FROM sf_subscription_info IF OBJECT_ID('sf_unsubscription_info', 'U') IS NOT NULL DELETE FROM sf_unsubscription_info IF OBJECT_ID('sf_sbscriber_subscription_info', 'U') IS NOT NULL DELETE FROM sf_sbscriber_subscription_info IF OBJECT_ID('sf_subscriber', 'U') IS NOT NULL DELETE FROM sf_subscriber IF OBJECT_ID('sf_subscriber_sf_lst', 'U') IS NOT NULL DELETE FROM sf_subscriber_sf_lst IF OBJECT_ID('sf_subscriber_sf_lst2', 'U') IS NOT NULL DELETE FROM sf_subscriber_sf_lst2 IF OBJECT_ID('sf_lst', 'U') IS NOT NULL DELETE FROM sf_lst IF OBJECT_ID('sf_link_click_stat', 'U') IS NOT NULL DELETE FROM sf_link_click_stat IF OBJECT_ID('sf_dynamic_list', 'U') IS NOT NULL DELETE FROM sf_dynamic_list IF OBJECT_ID('sf_delivery_entry', 'U') IS NOT NULL DELETE FROM sf_delivery_entry IF OBJECT_ID('sf_open_stat', 'U') IS NOT NULL DELETE FROM sf_open_statHello Ivan,
I am a developer for a medium sized software company, and have upgraded a sitefinity instance to version 5.0.2860.0.
I have the same issue as above. I have wiped the data from the tables using the script, and re-imported the data. Is there a solution to this, or do I have to send a database backup file somewhere to get this dealt with.
Thanks