Are dynamic Data Sources possible? 2

[kempis555]

Hi all,

I'm new to RS, and my project is to get RS running on the web using the URL method.

My problem is that I have more than one SQL server running, all with identical databases (with different data, o'course).

I've made different Data Sources on the Report server which point to the different SQL servers, but it seems like you can't tell a report to pick a Data Source at runtime. Is that true?

Please tell me it's not true.



Otherwise, what are the alternatives? I can see only two: each different database needs it's own Report file containing a tailored Data Source, or some SQL Server trick needs to happen based on report parameters.

Bleah on both of them.


-k

 

[xlbo]

Not tried this before but I believe it is possible to se the data source itself to be dynamic in terms of the server / database it points at. Not sure if you can do this in formula or whether you need to write a small piece of code to do it but I would start by looking at the data Source itself rather than the report connection to the data source

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[kempis555]

That's an interesting idea, but I don't think it would work. It sounds like you would update the datasource before running the report. I would be concerned about the situation where two users from different databases tried to access the report at the same time... there might be a horrifying collision with body parts and data rows flying all of the place. Yuck.

I think the only way to do this is to use linked SQL servers and fully qualified sproc calls. I'll have one database server which is the target of all of the reports' connection string, and one of the parameters passed by the report will be which server and database to run the sproc on.


The one database server's sproc will do something like this:

DECLARE @FullQualSproc AS VARCHAR(255)
SET @FullQualSproc = @server + '.' @database + '.dbo.' + 'reportSproc'

SELECT * FROM @FullQualSproc @param1 @param2 @param3 @param4



If anyone has already tried this, please let me know if there's any pitfalls I'll need to avoid. AFAIK, this should be ok.

-k

 

[xlbo]

Had a quick look into thsi and whilst MS says that it is technically possible, there are issues at the mo with RS 2000. It is apparently fixed in 2005 where you can use an expression to determine the data source - this can either be formulaic or from report code.

Some good info in this google group:

http://groups.google.com/group/microsoft.public.sqlserver.reportingsvcs

has seom RS MVPs on it and some of the MS design team regularly answer questions

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[SQLBI]

Have you thought about using linked servers?

I may be way off here or it may be an admin nightmare but here's how i'd try to go about it.

Firstly pick one of your SQL Servers to act as a 'main' the one where all RS queries will be directed.

Then, use sp_addlinkedserver to add each of your other SQL Servers as linked servers, which will enable you to query them from the 'main' SQL Server. (Look up Linked Servers in BOL)

Then, on the 'main' SQL Server, create a Stored Procedure that takes a the Linked Server as a parameter and directs the query to the relevant server.

Something like...

CREATE PROCEDURE pr_LinkedServerQuery (@LinkedServer varchar(255))

DECLARE @SQL varchar(8000)

SET @SQL = 'SELECT * FROM ' + @LinkedServer + '.dbo.Table'

EXEC (@SQL)

Then in your datasource for your report, add

EXEC pr_LinkedServerQuery @LinkedServer = 'YourLinkedServer'

This is just an abstract illustration of whats required, but a good way to acomplish what you need to do.

Be sure to read up on Linked Servers and Dynamic SQL, both in BOL and here and you'll be fine.

Hope this helps.

Cheers,
Leigh

The problem with common sense is that it isn't that common!

 

[kempis555]

Thanks y'all.

A linked server system similar to what LeighMoore suggested is what I'm using.

The only drawback I can see is a single point of failure risk. If the one SQL server which runs the master SPROCs goes down, all reports for all of the clients will go down too.

This isn't much of a problem for my situation, but others might be concerned about that.

-k