Few days back I downloaded a SSRS 2005 file from one of our internal report server and started modifying it. It had lot of datasets and the datasets contains huge T-SQL commands. When I tried to modify one dataset I got an error

Bit surprised because the report was working fine in the report server. Once I clicked the OK button, the BIDS environment looked bit different.

Careful look revealed that it is nothing but a SQL query builder. Now I understood why it had thrown an error. As per my understanding, the SQL query builder expects only SQL statements, especially SELECT statement. It doesn’t allow T-SQL commands as it cannot interpret these commands. Below picture will tell you how SQL query builder interprets the SELECT statement

I wrote a query joining sysobjects and syscolumns to fetch all columns of each table (Highlighted in Green). As soon as I hit the run button, the SQL query builder automatically populated Diagram Pane, Grid Pane and Result Pane as it can interpret the query easily.

Since my report has T-SQL commands, I realized that working with SQL Query Builder will not help. So I decided to switch to Generic Query Builder. I just hit the preceding button (highlighted in Red in below screenshot) and to my horror it completely dismantled the formatting which again resulted in syntax error.

The T-SQL batch was so big that I did not like formatting it manually. I tried Google to figure out how to handle this but of no use. Since this issue was not happening to other datasets (which are opened by default with Generic Query Designer), I was comparing the RDL of this dataset with the one which opened correctly. To my delight I found that the dataset which opened correctly had a property UseGenericDesigner set to true whereas this property was missing from the dataset which is erring out.

I manually added this tag in the RDL for the dataset.

<rd:UseGenericDesigner>true</rd:UseGenericDesigner>

Then opened the RDL in designer mode and tried to edit the dataset. Now it opened with Generic Query Designer and with correct format