none
Report Builder 3 using data sources from multiple servers

    Question

  • I have a report designed in RB3 that uses a data source from a SQL database that is on the report server. I want to add data to the report from an access database an a network drive.

    I can add the second data source and create a data set to add data to the report. The dataset query returns data from the Access database but when I run the report I get the following error.

    An error has occurred during report processing. (rsProcessingAborted)

    Cannot create a connection to data source 'Feasibility'. (rsError OpeningConnection)

    Also when I test the connection to the Access data base I get error

    ERROR (IM002) (Microsoft)(ODBC Driver Manger) Data source name not found and no default driver specified.

    I noticed that Report Builder is connecting to the report server. If I disconnect from the report server I can a can connect to the Access data base but not the SQL database.

    How can I get the report to run against both data sources?

    Thursday, September 05, 2013 3:14 AM

Answers

  • Hi Mordar,
     
    According to the error message, I think the issue should occur because there is not a corresponding ODBC DSN on the report server machine.
     
    When the Report Builder 3.0 is connected to the Report Server, if we test the connection of the ODBC data source, it will fail with the error message as you see if there is not a DSN with the same name on report server machine. However, the Query Builder uses the local DSN created on the client machine, hence, the Query Builder still works properly even though the test connection operation against the data source is failed. When we try to preview a report in Report Builder that is connected to the report server, it actually deploy the report to the report server and use the DSN created on the report server machine instead of the DSN on the client machine to retrieve the source data for the report. That’s why the report runs properly when the Report Builder is disconnected, but fails to run when the Report Builder is connected to the report server.   
     
    Since Report Builder 3.0 is a 32-bit Windows application, it can only use a DSN that is created based on a 32-bit ODBC driver. However, a 64-bit report server natively compiles 64-bit data providers installed. Wrapping a 32-bit data provider in 64-bit interfaces is not supported.  
     
    To resolve the issue, if the report server is a 32-bit machine, we need to start C:\Windows\SysWOW64\odbcad32.exe to create a DSN with the same name and based on the 32-bit ODBC driver on the report server machine. If the report server is a 64-bit machine, please use C:\Windows\System32\odbcad32.exe to create a DSN with the same name based on the 64-bit ODBC driver.
     
    Regards,
    Heidi Duan
     
    Monday, September 09, 2013 3:44 PM