SQL Reporting Services DataConnection Update

Question

Is it possible to change the connection string of a published sql reporting services report? I can see the binary field called DataSource in the ReportServer database, but since it's stored as binary I don't think it's easily updatable.

Do I need to republish the report with the correct data source? I'm hoping not since I do not want to have to install VS2003.

EDIT: The client is running SQL Server 2000 Reporting Services with all of the service packs installed.

Answer 1

SQL Reporting Services 2000 has a web service that you can use to change the data source. Given that, the following, allows for changing of a data source to a shared data source. This was adapted from MSDN.

// Create our reporting services class
ReportingService theRS = new ReportingService();
theRS.Credentials = System.Net.CredentialCache.DefaultCredentials;

// We need to setup a data source reference to an existing shared data source
DataSourceReference theDSRef = new DataSourceReference();
theDSRef.Reference = "/Path/To/ExistingSharedDataSource";
DataSource[] theDSArray = new DataSource[1];
DataSource theDS = new DataSource();
theDS.Item = (DataSourceReference)theDSRef;
theDS.Name = "NameOfSharedDataSource";
theDSArray[0] = theDS;

try
{
    // Attempt to change the data source of the report
    theRS.SetReportDataSources("/Path/To/ReportName", theDSArray);
    Console.Out.WriteLine("We have changed the data source");
}
catch (System.Web.Services.Protocols.SoapException e)
{
    Console.Out.WriteLine(e.Message);
    Console.Out.WriteLine(e.Detail.InnerXml.ToString());
}

In this example, the ReportingService class is taken from the Proxy class that I generated to talk to the web service, which is described here.

I hope this helps some. Let me know if you're looking for something different.