HttpCode

Recently I had to write a SQL Server Reporting Services Report that used a web service for the data source, the web service returned a horrible .NET DataSet object which I had no control over.

To use a web service from Reporting Services, the first step is to create a new Data Source of type: XML:

Then enter the URL to the web service in the connection string section.

The next step is to create a new DataSet for the report.

In the Query Designer enter the following:

<Query>
   <Method Name="<web service method name>" Namespace="<webservice namespace url>”>
   </Method>
   <ElementPath IgnoreNamespaces="True">
<web service method name>Response/<web service method name>Result/diffgram/NewDataSet/Table1   (replace Table1 with the dataset name if used)

    </ElementPath>

</Query>

If you need to pass parameters to the web service, this can be done by adding something like:

<Parameters>
       <Parameter Name="<parameter name>">
           <DefaultValue></DefaultValue>
       </Parameter>
   </Parameters>

But be sure that the case of the parameter name is exactly the same as the parameter in the report that you wish to pass in.

One of the main features of SQL Server 2008 is database backup compression. In SQL 2008 you can now specify the backup as:

BACKUP DATABASE ‘WSSDatabase’

TO DISK = ‘C:\WSSDatabase.bak’
WITH COMPRESSION;

Or if your GUI inclined:

This will make managing your content databases a little easier, especially if your already getting heckled by your SAN Administrator or the poor guy trying to backup those 150GB backups.

The second feature is Transparent Data Encryption (TDE), this allows you to specify a key that is used for data encryption for example:

ALTER DATABASE ‘WSSDatabase’
SET ENCRYPTION ON;

The scenario where I think this feature works nicely is this; imagine your pay-role people want to store pay details, you could firstly ensure that the site collection they use is located in a nominated content database, then you could turn on the encryption and have another secured means of protecting your data.

Further information can be found here.

Those two features are pretty compelling, on all our new deployments we are recommending SQL 2008.