SQL Server 2008 Database Merge.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SQL Server Administration (2008)

SQL Server 2008 Database Merge.

Author

Topic

Anto318
Starting Member

1 Post

Posted - 2015-03-11 : 05:27:20

Hi All,

I'm hoping you might be able to help. I'm pretty new to the SQL world and I'm a little over my head.

I've merged 2 databases (exact same table schema) which collect data for an Energy UI. The merge basically found the max ID for each of the "source" individual tables and added that ID to the corresponding ID in the "Target" tables.

However when I now log into the Energy UI and try to delete anything I get the following error:

The statement has been terminated.
The DELETE statement conflicted with the REFERENCE constraint "Users_RolesUsers_FK1". The conflict occurred in database "EMMA", table "dbo.RolesUsers", column 'UserId'.
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: System.Data.OleDb.OleDbException: The statement has been terminated.
The DELETE statement conflicted with the REFERENCE constraint "Users_RolesUsers_FK1". The conflict occurred in database "EMMA", table "dbo.RolesUsers", column 'UserId'.

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:


[OleDbException (0x80040e2f): The statement has been terminated.
The DELETE statement conflicted with the REFERENCE constraint "Users_RolesUsers_FK1". The conflict occurred in database "EMMA", table "dbo.RolesUsers", column 'UserId'.]
   System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method) +745
   System.Data.OleDb.OleDbCommand.ExecuteNonQuery() +113
   PEMS.EMMA.DAL.DbUtil.RSExecuteNonQuery(String sqlString) +142
   PEMS.EMMA.DAL.User.Delete(Int32 userId) +92
[CustomException: Unable to delete user with ID 342.]
   PEMS.EMMA.DAL.User.Delete(Int32 userId) +194
   EMMA.UserSetup.deleteButton_Click(Object sender, ImageClickEventArgs e) +17
   System.Web.UI.WebControls.ImageButton.OnClick(ImageClickEventArgs e) +108
   System.Web.UI.WebControls.ImageButton.RaisePostBackEvent(String eventArgument) +118
   System.Web.UI.WebControls.ImageButton.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +10
   System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +13
   System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +36
   System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1565

Any help which you can give would be extremely appreciated.

Anto318

Ifor
Aged Yak Warrior

700 Posts

Posted - 2015-03-11 : 07:52:16

It looks like you are trying to delete a user in the table Users while references to the user occur in the table RoleUsers.

If you want to delete the user, you will need to delete all references to the user in UserRoles first. When that is done you still might not be able to delete the user as references may exist in other tables; you will have to delete those references as well.

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2015-03-11 : 15:50:16

Alternately, you could modify the Foreign Key constraints to use CASCADING DELETES. You would need to know (or find) all of the tables that have a foreign key constraint that references the Users table in order to make the modifications.

Those who will not reason, are bigots, those who cannot, are fools, and those who dare not, are slaves. -Lord Byron, poet (1788-1824)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources