We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

Dynamics CRM 4.0 SQL Reports XML Connection String

Medium Priority
832 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.
Comment
Watch Question

Chinmay PatelChief Technology Ninja
CERTIFIED EXPERT
Distinguished Expert 2019

Commented:
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.

Author

Commented:
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 PatelChief Technology Ninja
CERTIFIED EXPERT
Distinguished Expert 2019

Commented:
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.

Author

Commented:
>>"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.
Chief Technology Ninja
CERTIFIED EXPERT
Distinguished Expert 2019
Commented:
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview

Author

Commented:
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.

Author

Commented:
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.

Author

Commented:
Thanks
Chinmay PatelChief Technology Ninja
CERTIFIED EXPERT
Distinguished Expert 2019

Commented:
Welcome.
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a free trial preview!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.