locked
CRM 4.0: Why CRM4.0 doesn't support stored procedure when building custom report? RRS feed

  • Question

  • Hi all,

    I read that crm 4.0 sdk  doesn't support stored procedure. I was wondering why. I currently create custom crm 4.0 report using stored procedure. Everything works fine. Then was told that stored procedure is not supported. But in my report, I need create user defined function, and temp table which seems not working properly using crm default way to create report. Also, I found it's also very inconvenience when maintain it. I was wondering what's the rise to use stored procedure.

    Thanks.

    Sunday, September 23, 2012 8:46 AM

Answers

  • Hi, I find that the hasstle using synonyms out weighs the deployment ease, but either way you are doing a search and replace on your deployment scripts.

    I use:

    CREATE SYNONYM synFilteredAccount FOR MyOrg_MSCRM.dbo.FilteredAccount


    Scott Durow
    Read my blog: www.develop1.net/public
    If this post answers your question, please click "Mark As Answer" on the post and "Mark as Helpful"

    Monday, September 24, 2012 6:00 PM
    Answerer

All replies

  • Hi,

    The reason stored procs are not supported is that CRM needs full control over it's database so that it can maintain entity tables,views & indexes etc. As soon as you start changing the database, there maybe something that it is not expecting and would interfer with the DDL statements that it uses to create and modify tables and views. If you look at it from supports perspective - there would be no easy way of determining if the issue was with the core product or due to some change to the database - hence why it is 'unsupported'. That is not to say you can't do it - but it's not recommended.

    If you want to use a stored procedure in reports using a 'supported' method - you can create another database (ideally on the same database server instance as the Organisation MSCRM database) and add the stored procedure to that. In your SQL, Point your queries back at the MSCRM database.

    E.g.

    Select * FROM MyOrg_MSCRM.dbo.FilteredContact

    To make things easier to deploy, you might like to consider creating Synonyms that point at your MSCRM views - when you deploy you then only need to update the Synonym server names rather than all of your stored procedures.

    Scott Durow
    Read my blog: www.develop1.net/public
    If this post answers your question, please click "Mark As Answer" on the post and "Mark as Helpful"

    Sunday, September 23, 2012 8:01 PM
    Answerer
  • Thanks, Scott. I did exactly the same way you pointed out here. when I created the Synonyms server name, it makes things really complicate. I know it is really helpful for deployment, but inconvenience to maintain. Do you have a good example to create Synonyms server that point to MSCRM views? Thanks.
    Monday, September 24, 2012 2:48 PM
  • Hi, I find that the hasstle using synonyms out weighs the deployment ease, but either way you are doing a search and replace on your deployment scripts.

    I use:

    CREATE SYNONYM synFilteredAccount FOR MyOrg_MSCRM.dbo.FilteredAccount


    Scott Durow
    Read my blog: www.develop1.net/public
    If this post answers your question, please click "Mark As Answer" on the post and "Mark as Helpful"

    Monday, September 24, 2012 6:00 PM
    Answerer
  • This is  a really cool, which save me lots of work. Thanks a lot.
    Monday, September 24, 2012 9:18 PM