locked
Delete a record in CRM 2011 from SQL? RRS feed

  • Question

  • Ok, so I know this is totally not supported, but I have a single record on a custom entity that when the end users try to delete it from the CRM, it throws a generic SQL error.  I've tried it through the API, and also get a SQL error.  As such, it appears that if I want to delete this one record, I'm going to have to do it directly in the database.

    So my question is, what is the SAFEST SQL script I can use to delete just that one record?  There's no workflow or process on that record, and deleting it won't orphan any other records.  We've cleaned it up as much as CRM will allow us without throwing the aforementioned SQL error.


    The postings on this site are solely my own and do not represent or constitute Hitachi Solutions' positions, views, strategies or opinions.

    Monday, July 16, 2012 12:09 PM

Answers

All replies

  • I don't think you are going to get any takers on this one.  :)  There are just too many things that need to happen at a platform level to keep your implementation solid.  

    If you are getting a generic SQL error you need to find out the root of the problem and find out what the error is.  The best way to do this is to turn on SQL profiler and re-create the issue, I would also use this in conjunction with the CRM Diagnostic tool.  Between the two you should be able to figure it out.

    http://crmdiagtool2011.codeplex.com/


    Jamie Miley
    Check out my about.me profile!
    http://mileyja.blogspot.com
    Linked-In Profile
    Follow Me on Twitter!

    Monday, July 16, 2012 1:56 PM
    Moderator
  • Well, part of the problem is that the record did not complete fully.  There was a hiccup between SQL and CRM servers and this is the one thing I haven't been able to fix from that.  So it appears that the issue is that the record wasn't fully-baked to begin with, and now CRM is complaining that it's missing information.

    Perhaps an alternate would be:  "what is the safest way in SQL to copy another record's data into this record so I can then try deleting it form the UI?"


    The postings on this site are solely my own and do not represent or constitute Hitachi Solutions' positions, views, strategies or opinions.

    Monday, July 16, 2012 2:05 PM
  • This sort of corruption shouldn't happen unless someone is playing with SQL there or due to a weird circumstance of customization changes.

    Do you have only one record for this entity? If yes, then use these statements in the same sequence: 

    delete from <Custom entity schemaname>ExtensionBase

    delete from <Custom entity schemaname>Base

    If not, then you need to find out the GUID of that record and use it in these statements:

    delete from <Custom entity schemaname>ExtensionBase where <Custom entity schemaname>Id = '<Record GUID>'
    delete from <Custom entity schemaname>Base where <Custom entity schemaname>Id = '<Record GUID>'

    Do take a backup of the database in case there is a mistake in SQL execution and validate that any other records for this entity look good through CRM UI.

    Regards,

    Mayank

    • Proposed as answer by MayankAgrawal Monday, July 16, 2012 2:18 PM
    Monday, July 16, 2012 2:16 PM
  • Thanks Mayank, I will test the latter ones. 

    The postings on this site are solely my own and do not represent or constitute Hitachi Solutions' positions, views, strategies or opinions.

    Monday, July 16, 2012 2:19 PM
  • The kind of diagnostics I mentioned earlier would be helpful still because you want to find out exactly what fields are causing your problem I would think.

    Jamie Miley
    Check out my about.me profile!
    http://mileyja.blogspot.com
    Linked-In Profile
    Follow Me on Twitter!

    Monday, July 16, 2012 2:20 PM
    Moderator
  • No doubt.  However, it is a high-volume production system and a unique issue.  Right now, the client would prefer to backup the db, delete the record, and test that the problem is solved than spend money on isolating what has been a one-time-only issue. 

    The postings on this site are solely my own and do not represent or constitute Hitachi Solutions' positions, views, strategies or opinions.

    Monday, July 16, 2012 2:28 PM
  • I am telling you that you really want to either diagnose it and fix it the right way or get MSFT involved.  Otherwise when you go to upgrade or install a rollup and everything dies it will be on your hands.

    Jamie Miley
    Check out my about.me profile!
    http://mileyja.blogspot.com
    Linked-In Profile
    Follow Me on Twitter!

    Monday, July 16, 2012 2:38 PM
    Moderator
  • So after testing, I have to agree.  It's too high-risk to delete this record from CRM via SQL, I'm getting unexpected behavior.

    The postings on this site are solely my own and do not represent or constitute Hitachi Solutions' positions, views, strategies or opinions.

    Thursday, July 19, 2012 2:30 PM
  • Did by ANY chance you imported the organisation from CRM4? This happened to me recently! Somehow CRM4 import to CRM2011 isn't 100% and some things were missing in the database...

    What record are you trying to delete? Have you tried running the trace tool?

    Thursday, July 19, 2012 2:49 PM
  • No, it isn't an import of CRM 4.0.  It was a new instance of CRM 2011 that started on UR5.

    The postings on this site are solely my own and do not represent or constitute Hitachi Solutions' positions, views, strategies or opinions.

    Thursday, July 19, 2012 3:30 PM
  • Do you use queues for this custom entity?
    Thursday, July 19, 2012 3:35 PM
  • They are queue-capable, but almost never used in that circumstance.

    The postings on this site are solely my own and do not represent or constitute Hitachi Solutions' positions, views, strategies or opinions.

    Thursday, July 19, 2012 3:37 PM
  • Have you checked is the problematic record is in a queue?

    Have you ran the CRM diagnostics tool yet? Here are the instructions on how to run it:

    Download the tool diagnostics tool to the CRM server. You can download the same from http://mscrmtools.blogspot.com/2011/04/new-tool-crmdiagtool-2011.html 

    To get the platform trace do the following 

    • Run the tool as administrator.
    • On the platform Tracing tab – Click on Enable Tracing.
    • Recreate the issue by trying to delete the record and note down the local time on the server
    • Click on open Trace Directory
    • Open the log file and look for the error around the date and time it occurred
    • Copy and paste the error message here so we can have a look into it
    Thursday, July 19, 2012 3:45 PM