?
Solved

Dynamics CRM 4.0 SQL Reports XML Connection String

Posted on 2011-04-27
9
Medium Priority
?
808 Views
Last Modified: 2012-05-11
"An error has occurred during report processing.
Cannot create a connection to data source 'LASER'."

      <ConnectionProperties>
        <DataProvider>SQL</DataProvider>
        <ConnectString>Data Source=MySQLServer;User ID=SA; Password=Password;Initial Catalog=LASER</ConnectString>
      </ConnectionProperties>

I created a report in SQL Reports using .net 2005 and imported it into CRM.  The data source is on a SQL Server other than the CRM SQL Server.  I got this error so I tried editing the XML Connection String by adding User ID and Password, but I don't know if this is correct as I do not know XML. I still get the same error.  Is there a correct way to code the connection string or is there another way using CRM to connect to the SQL Data source?  I have previously created and imported a report that uses the CRM data only.
0
Comment
Question by:sbdt8631
  • 5
  • 4
9 Comments
 
LVL 27

Expert Comment

by:Chinmay Patel
ID: 35477717
Hi sbdt8631,

I think you will have to create a datasource on the SSRS itself where you imported the report and then specify in your report to use that connection.

Regards,
Chinmay.
0
 
LVL 1

Author Comment

by:sbdt8631
ID: 35477866
I created an ODBC data source on the sql reports server named the same as I have on my local pc for that database. I created a new dataset in my report using the odbc data source and copied the sql code from the old dataset to the new.  I ran the report on the sql reports server and it worked fine.  I saved it and copied it to my pc where I imported it into CRM, overwriting the old file.  I get the same error.
0
 
LVL 27

Expert Comment

by:Chinmay Patel
ID: 35480718
That is 'cause if you upload an SSRS report to CRM, it will replace the datasource with its own datasource so that it can work properly. I suggest you create a shared datasource on SSRS server itself and try to point it to your existing database and then after you upload the report, change it to use a your custom datasource.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 1

Author Comment

by:sbdt8631
ID: 35483804
>>"and then after you upload the report, change it to use a your custom datasource. "

I created a shared datasource and edited my SSRS report to successfully run using this shared datasource.  I saved it to my local pc and imported it into CRM, but I don't understand what you mean by "Change it to use your custom datasource"  I don't see anywhere in the CRM report editor to change a datasource.
As is I get "Reporting Error the report cannot be displayed"  when I run it.
0
 
LVL 27

Accepted Solution

by:
Chinmay Patel earned 2000 total points
ID: 35484936
You will do that using SSRS Web interface.

Check : http://msdn.microsoft.com/en-us/library/ms155882.aspx
0
 
LVL 1

Author Comment

by:sbdt8631
ID: 35485378
I already did everything in that paper.  I had three datasources in the report, SQL, ODBC and Shared so I tried removing the ODBc data source and the shared and re-imported it to CRM and I was back to the starting point where I could not connect to Laser.  So I removed the Laser(SQL) datasource and re-inserted the Shared Datasource.  The SSRS report runs fine, but when I try to import it into CRM I get a window An Error Hase Occurred with no explanation other than I can consult community support, etc.
0
 
LVL 1

Author Comment

by:sbdt8631
ID: 35485428
I made a new report in SSRS using the shared datasource, two tables and four fields and tried to import it into CRM with the same an error has occurred.
0
 
LVL 1

Author Closing Comment

by:sbdt8631
ID: 35698689
Thanks
0
 
LVL 27

Expert Comment

by:Chinmay Patel
ID: 35699494
Welcome.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Suggested Courses

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question