asked on

Dynamics CRM 4.0 SQL Reports XML Connection String

"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.

Chinmay Patel

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.

sbdt8631

ASKER

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.

Chinmay Patel

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.

sbdt8631

ASKER

>>"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.

ASKER CERTIFIED SOLUTION

Chinmay Patel

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

sbdt8631

ASKER

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.

sbdt8631

ASKER

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.

sbdt8631

ASKER

Thanks

Chinmay Patel

Welcome.