Dynamic Database Selection in SQL/SSRS

Question

Dynamic Database Selection in SQL/SSRS

Phill Devey 0

I work in an organization that has 50+ databases with identical schemas but data for different divisions and legal entities of the business. Some of the SSRS reports pull data from multiple databases into a single report however, the list of databases needed in a report can change several times a year requiring the Datasets in the reports to be rebuilt to target different databases,

This scenario creates a requirement to dynamically specify the databases needed for the query however, this is not something that SQL or SSRS provides direct support for and requires the use of Dynamic SQL to enable. As SQL is a Database engine, it's somewhat ironic that it does not have the native capability to dynamically specify the database a query should be run against and, that over the many versions of SQL, this capability has never been introduced into SQL or SSRS.

The functionality can be achieved by the use of Dynamic SQL but, Dynamic SQL has many complications associated with it and is not a great option when writing queries inside SSRS.

I have been posting this question on other forums but does anyone know of native ways this functionality can be achieved? Every solution I have found requires some kind of workaround or multi-step process to implement.

Thanks

Coreysan 1,811 Reputation points

2023-06-15T16:09:52.7933333+00:00

Phil, like you I program multiple DBs with the same schema, and am restricted to Dynamic SQL. I have asked this very question myself, and usually get the same answer: Dynamic SQL.

So, I decided to see if I could master Dynamic SQL. I have found some limitations of course, but overall, I've been successful with it. Annoying - yes. I'll be watching this thread to see if there's a better solution, but in the mean time, so far in 7 years of coding dynamically, I haven't met a roadblock I couldn't punch through. And, you make a great point about SQL being an industry standard DB engine. I assumed there would be a more robust toolset than what you and I have!
Phill Devey 0 Reputation points

2023-06-15T16:57:09.89+00:00

Coreysan

I do have a method to dynamically set the database without requiring full dynamic SQL but, it's still what I would consider a workaround. Unfortunately, in my role, I do not develop the majority of the SSRS reports. I am primarily a .NET developer and only work on the reports when they tie into an application I am building or, when I have been specifically requested to work on a report. The reports are mostly handled by a Business Analyst who does not have a programming background, they most use the query designer or write straight forward SQL select queries.

The solution I discovered was to use a combination of a Stored Procedure and, a SQL Cursor inside the SSRS query that loops a table where the databases are listed. The stored procedure contains the main select query which allows the BA to build his query in query designer and then paste it into the SP as a straight SQL query (avoiding the programming knowledge requirements). Inside the SSRS query, you create a table variable and then execute the stored procedure in the loop to insert into the temp table. Then, you can SELECT * on the temp table and apply sorting etc.

Like I said, it's a total workaround but, allows the dynamic database without requiring the main select query be written as dynamic SQL.

2 answers

Your answer

Coreysan 1,811 Reputation points

2023-06-15T16:09:52.7933333+00:00

Phil, like you I program multiple DBs with the same schema, and am restricted to Dynamic SQL. I have asked this very question myself, and usually get the same answer: Dynamic SQL.

So, I decided to see if I could master Dynamic SQL. I have found some limitations of course, but overall, I've been successful with it. Annoying - yes. I'll be watching this thread to see if there's a better solution, but in the mean time, so far in 7 years of coding dynamically, I haven't met a roadblock I couldn't punch through. And, you make a great point about SQL being an industry standard DB engine. I assumed there would be a more robust toolset than what you and I have!
Phill Devey 0 Reputation points

2023-06-15T16:57:09.89+00:00

Coreysan

I do have a method to dynamically set the database without requiring full dynamic SQL but, it's still what I would consider a workaround. Unfortunately, in my role, I do not develop the majority of the SSRS reports. I am primarily a .NET developer and only work on the reports when they tie into an application I am building or, when I have been specifically requested to work on a report. The reports are mostly handled by a Business Analyst who does not have a programming background, they most use the query designer or write straight forward SQL select queries.

The solution I discovered was to use a combination of a Stored Procedure and, a SQL Cursor inside the SSRS query that loops a table where the databases are listed. The stored procedure contains the main select query which allows the BA to build his query in query designer and then paste it into the SP as a straight SQL query (avoiding the programming knowledge requirements). Inside the SSRS query, you create a table variable and then execute the stored procedure in the loop to insert into the temp table. Then, you can SELECT * on the temp table and apply sorting etc.

Like I said, it's a total workaround but, allows the dynamic database without requiring the main select query be written as dynamic SQL.

Answer 1

Hi @Phill Devey

Every solution I have found requires some kind of workaround or multi-step process to implement.

Not an expert in SSRS, but from dynamic SQL side, I didn't see simple method to implement.

Best regards,

Cosmog Hong

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Thank you for ever ones input. I have worked with SQL for almost 20 years so I knew that natively, SQL has poor support for dynamically query data from multiple databases. I was however hopeful that someone would know a clean & efficient way to handle this in SSRS (it is after all; a reporting tool).

The solution I detailed above (calling an SP from within the SSRS Query) seems to be the best solution I can find to fit the needs of my scenario, where the main query is NOT written as dynamic SQL. With that said, selecting data from multiple databases is a legitimate need for SQL, it may not be required in all scenarios but, it IS a legitimate requirement. Perhaps this is something MS need to consider as part of SQL's future design or, if not SQL, then it's own reporting tool. Hardcoding Database names into SQL queries is not a sustainable practice as the more you do it, the more resources are required to maintain the reports. In a larger organization, those kinds of limitations and requirements result in your products being replaces by other technologies that are more efficient to work with.

Share via

Dynamic Database Selection in SQL/SSRS

2 answers

Your answer