Retrieve data from different database

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Development Tools

Reporting Services Development

Retrieve data from different database

Author

Topic

chriskhan2000
Aged Yak Warrior

544 Posts

Posted - 2004-10-12 : 17:14:21

Is it possible to get data from multiple database? I want to create a picklist of 3 database or if possible actually get the 3 database name into a dynamic picklist. The user will be able to select a certain database from the list and click on "Preview" to have the report from that database. All fields and table will be the same throughout the 3 database, just different data in it. Let me know if anyone has successfully done this in Reporting Services.

Thanks in advance.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-10-19 : 15:27:54

I wouldn't do this inside Reporing Services. Create a stored procedure that brings together your data from multiple databases, then use the stored procedure in the report.

Tara

chriskhan2000
Aged Yak Warrior

544 Posts

Posted - 2004-10-21 : 14:31:42

Tara,

The thing is that we have 3 different databases with same fields, tables, and schema. We have 3 different department that needs access to the same report, but use their respective database. I don't want to go about creating 3 different report, one for each database. If I do this, it will just be extra overhead. I know that it's probably possible using database extension....but I don't know how to do that exactly.

Let me know if you or anyone knows anything about this.

Thanks.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-10-21 : 14:36:25

You missed my point. You don't need 3 different reports. To get data from multiple databases, you can use a stored procedure. You would call the stored procedure in Reporting Services. Are you familiar with stored procedures? You could also do this in a view and wrap that in a stored procedure. Just have an input parameter to the stored procedure for the database name. So then in the report the user would be able to select from 3 values in a drop down list from your report (do this in a static pick list for this parameter), then depending on that value, the proper part of the stored procedure would be run.

Tara

chriskhan2000
Aged Yak Warrior

544 Posts

Posted - 2004-10-22 : 13:52:58

Let me sum up what you posted. So I can create a stored procedure in SQL Server where the databases resides. Then use Reporting services to call up those stored procedures by using the databases as parameters. RIght?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-10-22 : 14:00:04

It would be one stored procedure stored in one of the databases. In the stored procedure would be the calls to the other databases depending on the input parameters. Then your RS data source would point to the database that contains this stored procedure.

Sample proc:



CREATE PROC SomeProc
(@DBName sysname)
AS

SET NOCOUNT ON

IF @DBName = 'DBa'
  SELECT * FROM DBa.dbo.Table1
IF @DBName = 'DBb'
  SELECT * FROM DBb.dbo.Table1
IF @DBName = 'DBc'
  SELECT * FROM DBc.dbo.Table1

RETURN

GO

This is just a possible solution to your problem. There may be a way to solve the problem directly in Reporting Services using multiple data sources; I just don't know how to do it nor have I see it or heard about it being done.

Tara

chriskhan2000
Aged Yak Warrior

544 Posts

Posted - 2004-10-22 : 14:35:19

I've found this in one of the postings for a newsgroup:

1) Execute a query in the DataSet Designer that will pull back the fields
that you want. - this should populate the Fields List.
2) Then replace the query string in the DataSet Designer with a Visual
Basic.NET expression (include the =) - for example:-

=iif(Parameters!Source.Value ="NorthWind", "SELECT * FROM
xpvs2003.NorthWind.dbo.sysobjects", "SELECT * FROM
xpvs2003.pubs.dbo.sysobjects")

3) Create for yourself the Report Parameter - in the above example this is
called "Source".

This is not very thorough and detail so I can't seem to get it to work yet.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources