locked
Microsoft Dynamics CRM 2011 upgrade issue RRS feed

  • Question

  • Hello All,

    I was getting the error "Invalid column name 'DeletionStateCode'" when I was trying to upgrade from 4.0 to 2011. After a lot of trials and errors I got to know that there were many stored procedures and triggers in the database which were referencing to this column.

    Can any one help me out, it could be a great.

    Thanks,

    Friday, June 17, 2011 7:39 AM

All replies

  • I'm afraid there's no quick solution. The deletionstatecode column no longer exists in CRM 2011, so any SQL objects that reference it will no longer be valid (strictly it wasn't supported to access the deletionstatecode column in CRM 4, but it's a bit late to worry about that now).

    What I suggest you do is:

    1. Identify the SQL objects that reference deletionstatecode (see below for a SQL script that'll get you most of the way)
    2. Use SQL tools to generate the script for these SQL objects
    3. Drop the SQL objects
    4. Edit the script to remove any reference to deletionstatecode (this won't affect the functionality, unless the objects were deliberately looking at deleted records)
    5. Run the script to recreate the SQL objects

    You'll probably want to take SQL backups at appropriate points, and dependencies between the objects may make step 5 a bit fiddly

    A sample SQL script is this - it reads the SQL used to generate the object to look for those that reference deletionstatecode by name, and it also filters out the built in CRM views so that it can be run against a CRM 4 database (this filtering out still leaves some CRM objects that you shouldn't touch, mostly stored procedures and functions)


    select object_name(o.id), o.xtype from syscomments c
    join sysobjects o on c.id = o.id
    where text like '%deletionstatecode%'
    and object_name(o.id) not in
    (select PhysicalName from entity where PhysicalName is not null
     union
     select ReportViewName from entity where ReportViewName is not null )

     


    Microsoft CRM MVP - http://mscrmuk.blogspot.com  http://www.excitation.co.uk
    • Proposed as answer by Ahmed Uddin Friday, July 27, 2012 10:26 AM
    Friday, June 17, 2011 9:31 AM
    Moderator
  • Hello David,

    I was getting the one more error Invalid column name "owning Team" when I was trying to upgrade from 4.0 to 2011. After a lot of trials and errors I got to know that there were many stored procedures and triggers in the database which were referencing to this column.

    Can any one help me out, it could be a great.

    Thanks,

    Friday, June 17, 2011 9:54 AM
  • Hi,

    This is because of the stored procedures and triggers which are created in Database.

    If you try to remove trigger perfectly then you can able to upgrade the database.

    SQL org_mscrm database to check number of custom triggers and custom stored procedures using following queries:-

     select type,* from sys.objects Where type='p'

     

    select type,* from sys.objects Where type = 'TR'

     

    -By using above two commands you can see number of triggers and procedures.

    -The name of the procedures started with ‘p_ ‘ were default CRM procedures and without it were custom procedures.

    -run second command for triggers you will find custom triggers.

    -delete all custom triggers one by one using following command:-

     

    DROP TRIGGER trigger_name

     

    and start import the organization again.

    Regards,


    Khaja Mohiddin
    • Proposed as answer by Ahmed Uddin Friday, July 27, 2012 10:27 AM
    Friday, June 17, 2011 10:29 AM
  • We are a bit behind the upgrade curve here at my company. I don't know if this will help anyone or if its still a widely reported issue but I used Khaja's solution and found over 60 Scribe Triggers in my database. I had to delete them one by one. Once I did that, my upgrade completed. 

    Thanks Khaja!

    Monday, July 23, 2012 9:17 PM