Answered by:
Updation of stored procedures in CRM report

Question
-
Hi
In CRM reports when i try to upload a store procedure
It displays an error "The stored procedure 'pbo_ApplicationAppointments' doesn't exist."
What does this mean? I have saved the store procedure in the reports folder but not getting updated on SQL report server
Any help much appreciated
Thanks
Pradnya
Pradnya07Wednesday, January 11, 2012 4:52 PM
Answers
-
Hi Simran08,
If you want to use a stored procedure in your Custom CRM2011 reports - you must 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.
hth,
Scott
www.develop1.net
If this post answers your question, please click "Mark As Answer" on the post and "Mark as Helpful"- Proposed as answer by Scott Durow (MVP)MVP, Editor Wednesday, January 11, 2012 7:10 PM
- Marked as answer by Curt Spanburgh MVP ModeratorMVP, Moderator Thursday, January 12, 2012 4:13 AM
Wednesday, January 11, 2012 7:09 PMAnswerer -
Hi Scott,
My mistake i was using alter procedure ... instead of create procedure first and then alter procedure
Thanks
Pradnya
Pradnya07- Marked as answer by Simran08 Thursday, January 12, 2012 11:51 AM
Thursday, January 12, 2012 11:50 AM
All replies
-
Hi Simran08,
If you want to use a stored procedure in your Custom CRM2011 reports - you must 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.
hth,
Scott
www.develop1.net
If this post answers your question, please click "Mark As Answer" on the post and "Mark as Helpful"- Proposed as answer by Scott Durow (MVP)MVP, Editor Wednesday, January 11, 2012 7:10 PM
- Marked as answer by Curt Spanburgh MVP ModeratorMVP, Moderator Thursday, January 12, 2012 4:13 AM
Wednesday, January 11, 2012 7:09 PMAnswerer -
Hi Scott
Thanks for your reply
I did created another database , created views for my table, but when i go to Programmability - Store Procedure - right click create a new store procedure
The newly created stored procedure doesn't get save under Programmability - Store Procedure
I am not sure where I am going wrong (Please find the attached sheet )
Regards
Pradnya
Pradnya07- Edited by Simran08 Thursday, January 12, 2012 11:53 AM
Thursday, January 12, 2012 11:23 AM -
Hi Scott,
My mistake i was using alter procedure ... instead of create procedure first and then alter procedure
Thanks
Pradnya
Pradnya07- Marked as answer by Simran08 Thursday, January 12, 2012 11:51 AM
Thursday, January 12, 2012 11:50 AM