SQL Papaya

Joining Disparate Datasets in SQL Server Reporting Services using OPENROWSET

First post, so I thought we'd ease into things with something simple.

I recently happened across someone who had a problem with creating a report in SQL Server Reporting Services. The report utilised datasets from several data sources. So far no issue. However,  tables that existed in different database instances that needed to be combined into a single dataset.

That is, each database has a table with a list of contacts to be retrieved as follows:

select FirstName,
       LastName
from SalesDB.Customer.Contact 

The original report looked a bit like this:

As you can probably see already, this isn't exactly useful. The report should show a single contact list. For the record, if you're not certain, SSRS can't do this for you. Each data source is treated as a distinct entity and you can't make a single set of data out of them. So, we need to turn to SQL Server to provide a singe datasource that contains the dataset.

Lucky enough we had an instance of SQL Server to play with and eventually use for production purposes. So, my first instinct was to create a linked server for each of the databases and then a view which combines them. Unfortunately, we ran into permissions issues and that's when I started to scratch my head. What next?

Well, there's an ugly-but-functional way of taking care of situations like this - OPENROWSET. The idea is to create a simple view to query each of the servers and combine the results. The beauty of this approach is that the permissions needed are only for creating a view.

OPENROWSET requires coding the credentials for the datasources inside the query. You'll recall that we were already connecting from SSRS, so we can just use the same information inside the view. In this case we were able to use a trusted connection. This is how the view looks:

create view GetTheData as

select a.*
from openrowset('SQLNCLI',  'Server=ServerA;
                Trusted_Connection=yes;', 
                'select FirstName,
       LastName
from SalesDB.Customer.Contact'
                ) as tabA

union all  -- gives us the full datasets

select tabB.*
from openrowset('SQLNCLI', 
                'Server=ServerB;
                Trusted_Connection=yes;',
                'select FirstName,
       LastName
from SalesDB.Customer.Contact '
                ) as tabB

union all

select tabC.*
select openrowset('SQLNCLI', 
                'Server=Server;
                Trusted_Connection=yes;',
                'select FirstName,
       LastName
from SalesDB.Customer.Contact '
                ) as tabC

From here we can ditch the 3 datasources in SSRS, set up a single datasource and reference the view GetTheData. Note that this a very simple example, connecting via SQL Native Client OLE DB provider. OPENROWSET is capable of far more than connecting to SQL Server, it's great for many other OLE DB data sources.

Now, the important part for me here wasn't that I had anything technically difficult to do. My issue was that I was temporarily blind and automatically thought that linked servers would be the answer. When that didn't work I was stumped for a moment because I'd momentarily forgotten about OPENROWSET (!!) If you're like me, it's easy to have a favourite way of doing everything, to the detriment of keeping an open mind.

You can find more information on OPENROWSET in BOL: http://msdn.microsoft.com/en-us/library/ms190312.aspx