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

Forums / Bugs & Issues / 4.1 upgrade sql exception ALTER TABLE sf_subscriber_sf_lst

4.1 upgrade sql exception ALTER TABLE sf_subscriber_sf_lst

4 posts, 0 answered
  1. Kronos
    Kronos avatar
    17 posts
    Registered:
    08 Apr 2011
    21 Apr 2011
    Link to this post
    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              
  2. Ivan Dimitrov
    Ivan Dimitrov avatar
    16072 posts
    Registered:
    25 Nov 2016
    21 Apr 2011
    Link to this post
    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


    After that your site should run successfully, missing the newsletters data.

    In case your newsletters data is important, please send us your database files and we'll upgrade them.


    All the best,
    Ivan Dimitrov
    the Telerik team

  3. Kronos
    Kronos avatar
    17 posts
    Registered:
    08 Apr 2011
    22 Apr 2011
    Link to this post
    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_stat
  4. Jim
    Jim avatar
    3 posts
    Registered:
    06 Jun 2012
    30 Oct 2012 in reply to Ivan Dimitrov
    Link to this post
    Hello 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
4 posts, 0 answered