cached datasets?

Hello all,

I have a report with a table and a chart. It uses dataset1 as the data source.

All works fine.

I create a new dataset called dataset2.

The queries are exactly the same. The only differences between the 2 datasets is the database server and the fact that one of the columns is a smallint (in dataset2) and an int(in Dataset1)

I change the datasetName property of both the table and the chart to use dataset2.

When I run the report I get a conversion error stating that there was an overflow of int2 while using dataset1. I have verified the report is not using dataset1 anywhere. If I delete dataset1 and run the report the error goes away. If I add it back, I get the error again. Why is the report looking at dataset1 if it is not referenced at all in the report? Does SQL RS cache the datasets and verify each when it compiles?

regards,

Bill

Sometimes I have noticed that Business Intelligence Studio is not so intelligent. In the XML, it may not be changing your dataset from dataset1 to dataset2.

When this happens to me, I switch to the layout tab. Next, click view code. Search this XML document for any occurance of dataset. You should find something like this:

<DataSetName>dataset1</DataSetName>

If you find any in the XML that say dataset1, then change them to dataset2. Save your project and rebuild and see whether that takes care of it for you.

|||I see instances of the dataset1 in the XML. I have many reports that I need to have multiple datasets created, but not used. I have 3 locations(servers) that will use the same reports. I need a different dataset created for each. I would really love not to have to recreate the dataset for each location whenever I make a change to the report.

|||

Woyler wrote:

I see instances of the dataset1 in the XML. I have many reports that I need to have multiple datasets created, but not used. I have 3 locations(servers) that will use the same reports. I need a different dataset created for each. I would really love not to have to recreate the dataset for each location whenever I make a change to the report.

Dataset1 will still be stored on the layout of the report if you change dataset1 to dataset2 in the XML.

You will not have to recreate the dataset. You are simply manually telling the report which dataset to use, since Business Intelligence Studio was not intelligent enough to change the XML for you.

|||Thanks for your replies. I searched the XML for the <datasetname> tag. All instances of the tag are correct. There is no mention of dataset1. Any other places to look?|||

No, rebuild and deploy the report and see if you get the same error.

|||If I rebuild I get the same error. Like I said if I delete the dataset1 , it works fine. If I add the dataset1 back in , I get the error. It seems that the simple existence of the dataset causes the report to validate it.

|||Have you tried commenting the query in dataset1 out?

|||

That works. Thanks for the help.

I hope someone(hello Microsoft) will chime in here and provide a logical explanation for this.

Best regards,

Bill