By: Siddharth Mehta | Comments (3) | Related: > Azure
Problem
We know that SQL Azure is the database offering on the Windows Azure cloud computing platform, and it goes without saying that all the technologies that plug-in to databases need to start exercising and adapting to this flavor of databases along with the regular approach of database access. In this tip we learn how to use SQL Server Reporting Services (SSRS 2008 R2 hereafter) 2008 R2 Nov CTP to connect to SQL Azure.
Solution
Two major providers can be used in SSRS 2008 R2 to connect to SQL Azure: "Microsoft SQL Server" and "OLE DB". Using these providers, a report developer can continue to develop the report in much the same way as any locally or network installed database. The only thing that one should take care of is that, when SQL Azure goes RTM (official release), users will be charged for accessing SQL Azure. So initial development and prototyping can be done on a locally installed database, and when the report is developed to a considerable extent, testing and validation can be started against SQL Azure.
Again this is not the only option, but it really depends upon the pricing policy opted. It also may fall to a scenario that part of the data is hosted on SQL Azure and part is hosted locally. In this tip we will focus on how to connect to SQL Azure using SSRS 2008 R2, and this tip assumes that the reader has some basic working knowledge of SSRS.
Please follow the below exercise to create and test the subject of this tip.
Step 1
Firstly in order to create some data for our report (which we would develop in the following steps), create a database called "AdventureWorks" and within this database create a table called "Contact Details" and populate it with some test data. Those who are new to SQL Azure, can use this tip to bring themselves up to speed on how to create an account on SQL Azure and connect to the same using SSMS.
Step 2
Open Business Intelligence Development Studio (BIDS), and create a new report project and name it "MyFirstReportProject".
Right click the Reports folder in the solution explorer pane, and select "Add" -> "New Item" and then select "Report".
A blank report should get added to your solution, name this report "MyFirstR2Report".
Step 3
After your report is created, on the right-hand side you should be able to see "Report Data" pane.
Our aim in this exercise is to test connectivity to SQL Azure, so we would not create any Shared Dataset or Shared Datasource. Right-Click DataSources in this "Report Data" pane and select "Add Data Source".
In the Data Source dialog box, there will be an option to select the provider type. Here you can use two options: Microsoft SQL Server and OLE DB. For the sake of this exercise we would use "Microsoft SQL Server" as the type, but those who are curious to use the other one, the connection configurations for both are shown in the figure below.
In the connection properties dialog box, make sure that server name is mentioned
in the format of
After you have configured the connection string using "Microsoft SQL Server" as the type, your dialog box should look as shown in the figure below.
Step 4
Now we need to create a dataset, do the same by right-clicking on the Dataset directory and select "Add Dataset". Name this dataset as "MyDataSet".
Select the datasource which we created in the above set as the datasource for this dataset.
Key-in the query to retrieve records from the "ContactDetails" table which is created from our script.
Click "Refresh Fields", and if everything went fine, you should be able to find all the fields in this table in the "Fields" tab of this dialog box.
Step 5
Place a "Table" control on the report, so that we can populate all the records from our dataset into this control.
Drag some of the fields from this dataset on this table control.
Click on the "Preview" tab which would execute your report and you should be able to see the output of your report.
Your report should look like this, after these steps are completed (apart from my image which I have kept to show that this is my report !).
Step 6
Now it's time to deploy this report on the report server.
Right-click your solution explorer and select "Properties". Keep the rest of the properties as the default, but key-in your report server URL in the "TargetServerURL" property. If you have installed ReportServer using the default values and location, your report server URL should be something like "http://[machinename]/ReportServer".
After this is done, right-click your solution and select "Deploy" which should deploy this report successfully under normal conditions.
Step 7
Now its finally time to see this report getting executed from Report Manager.
In a default configuration, the Report Manager URL should look something like "http://[machinename]/Reports".
Open this URL in Internet Explorer, and then you should be able to see the directory MyFirstR2ReportProject and under it there should be a report named "MyFirstReport".
Click on this report to execute it, and it should work as smoothly with SQL Azure as it works with any locally installed database. Make sure that you do not test on any transaction or data that takes longer than 5 minutes, else there is a chance that SQL Azure would terminate your connection as per the specs of SQL Azure and your report might encounter an error.
Next Steps
- Download and install SQL Server 2008 R2 Nov CTP along with AdventureWorks 2008 R2 database.
- Create your SQL Azure account if not created already.
- Try creating a report that connects to both of these database from a single report using embedded and shared connections.
- Read these other SQL Azure tips
About the author
Siddharth Mehta is an Associate Manager with Accenture in the Avanade Division focusing on Business Intelligence.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
