SQL Server Performance Forum – Threads Archive

connect dynamically to different datasource

Hello I was testing the SQL Reporting Services for my application to connect dynamically to different datasource on the fly while generating reports. This is imperative as the user has an option to select to which physical database on SQL Server he wants to retreive the content from Please let me know how to go about it as while drafting a report I have to define the database server and catalog for the database connection which I want to pass off as a variable in my ASP.NET application interface Please note – I know we can change the datasource by going to the Home directory of myreports, click the datasourse name to modify, modify the server & save. The report will start pointing to new server. But I want to accomplish it programatically Thanks in advance [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />]

Hello, We had the same problem and we solved it using a custom data extension for Reporting Services.
You will find a demo for how to do this in Reporting Services samples, called FSIDataExtension (C

rogram FilesMicrosoft SQL ServerMSSQLReporting ServicesSamplesExtensionsFsiDataExtension).
Your custom data extension must determine at runtime what database to retrive content from based on some parameters you pass to the report. Good Luck,
Sorin

>Your custom data extension must determine at runtime what database to retrive content >from based on some parameters you pass to the report. Hi Can you send me, a code fragment how to program the dynamic connection in the custom data extension? thanks
giuseppe

In order to write a custom data extension you have to code 6 classes that implements each one of the following interfaces: a connection class: IDbConnectionExtension, a command class: (IDbCommandAnalysis, IDbCommand), a data parameter class: Microsoft.ReportingServices.DataProcessing.IDataParameter, a data parameter connection class: System.Collections.ArrayList, Microsoft.ReportingServices.DataProcessing.IDataParameterCollection, a data reader class: Microsoft.ReportingServices.DataProcessing.IDataReader and a transaction class :Microsoft.ReportingServices.DataProcessing.IDbTransaction (if your data extension does not support transactions, you don’t have to write code for this, you just have to write methods that do nothing). In your command implementation’s IDataReader ExecuteReader(CommandBehavior behavior) method, based on one report parameter you open onother connection to the data source of your choice. Keep in mind that your report must have at least one more parameter that your data query requires, and you will use that parameter to pass information you will use to open the data source. You will have also to implement IDataParameterCollection GetParameters(), which is called by Report Desinger and Report Manager to get query parameters; in this impementation you should return a DataParameterCollection with query parameters + your parameter used for data source connection. The best way is to use shell code from FSIDataExtension sample (see above), and write only code required by your implemetation. Sorin

it works <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ /> thanks

The sample does not use a connection string. How is the parameter passed to the report. Any help or code sample would be greatly appreciated. Thanks. I am using VB.

quote:Originally posted by SorinCh Hello, We had the same problem and we solved it using a custom data extension for Reporting Services.
You will find a demo for how to do this in Reporting Services samples, called FSIDataExtension (C

]]>