Combine two data sources into one data source inside SSRS report using a stored procedure

Question

I am trying to build SSRS report that will fetch data from two different servers.

I have already created a stored procedure that works fine while running in Management Studio, but the same stored procedure does not work while I call it from my SSRS report builder.

This is the SQL query inside my stored procedure:

Select 
    serv1.PatientName, serv1.DoctorName,
    serv2.HospitalName, serv2.DoctorDegree 
from 
    server1.database1.tblPatient serv1
inner join 
    server2.database2.tbldoctor serv2 on serv2.PatientID = serv1.PatientID
where 
    serv2.PatientID = 2

Please consider above query is just the example. As the stored procedure is working perfectly fine. and I have also created two datasources having the connection perfectly fine.

The main issue is how do I connect both datasources to a single datasource in my SSRS report?

Answer 1

This really needs to be managed outside of SSRS. None of the techniques to union multiple datasources into a single table in SSRS are pretty or recommended. So if you can directly query from one server to the other, then that is the best option. To get the data into a single table in SSRS you need to UNION or JOIN the data from the different servers into a single recordset, that way you only need to connect one server from SSRS.

I have already created a stored procedure that works fine while running in Management Studio, but the same stored procedure does not work while I call it from my SSRS report builder.

There are no additional restrictions that SSRS introduces here, it is the database itself that executes the query, so if you have already encapsulated this query in a stored procedure, and proven that it works when executed via SSMS, then the only other variable is the security context. So check that the user account that SSRS is connecting with has been granted the same permissions as the user account you tested with, or login to SSMS with the SSRS account to verify that the stored procedure can be executed.

The other thing to consider is that when you tested this in SMS, are you testing on the same server that the SSRS report is connecting to? If the security context is the same in the two environments there is no reason this shouldn't work, just make sure you are comparing apples with apples.

RE: stored procedure does not work

Does not work is very vague, can you provide the error message or explain the result? SQL Server usually provides very succinct error messages that we can use to identify the specific cause of any failure.

Alternate Solutions

The key is to enable one of the databases to be able to query the others, that way our SSRS DataSource can be to a single query on one of the databases and union the data in the query or SP. So how can we do this natively in SQL Server?

Linked Servers: sp_addlinkedserver allows you to query other data providers, if the linked server is an instance os SQL Server, then remote stored procedures can be executed.

• This is not supported in Azure SQL DB
• Depending on your security configuration, you may need to define the logins, read more about Security for Linked Servers

External Tables SQL Server 2016 introduced this concept to facilitate calling out to other data sources (not just SQL SERVER) as if they were tables in your main database. You can of course use this to connect other SQL databases. It's an abstraction layer that might not improve your current issue, but if you remain stuck on this it might be worth a look ;)

• This solution works in Azure SQL DB but is also supported in Azure SQL Managed Instance and MS SQL Server