April 9, 2009 at 3:52 pm
Hi,
I have a situation where i have to connect to two databases and generate the Report, Because there are few entities present in both the databases, say User ID's from 1 to 10 in Database A and 11 to 20 in Database B, I need to retrieve information from both the databases.
Both the databases have the same structure.
Please suggest me how to use or connect to two databases in a single Report.
Thanks in Advance.
April 10, 2009 at 1:19 am
Why dont you create a Stored Procedure/Sql that accesses these two databases with the fully qualified name) and then use this Stored Procedure in your datasource?
April 11, 2009 at 1:43 am
Are you trying to pull data together from both databases and have it appear as if it was just one data set?
If so, have you trying a UNION query?
Example:
SELECT t1.field1, t1.field2, t1.field3
FROM dbName1..tblName1 AS T1
UNION
SELECT t2.field1, t2.field2, t2.field3
FROM dbName2..tblName1 AS T2
If using parameters for user id then two options:
1)
SELECT t1.field1, t1.field2, t1.field3
FROM dbName1..tblName1 AS T1
WHERE t1.userid = @GivenUserID
UNION
SELECT t2.field1, t2.field2, t2.field3
FROM dbName2..tblName1 AS T2
WHERE t2.userid = @GivenUserID
==== OR ====
2)
SELECT src1.field1, src1.field2, src1.field3
FROM
(
SELECT t1.field1, t1.field2, t1.field3, t1.userid
FROM dbName1..tblName1 AS T1
UNION
SELECT t2.field1, t2.field2, t2.field3, t1.userid
FROM dbName2..tblName1 AS T2
) as src1
WHERE src1.userid = @GivenUserID
April 11, 2009 at 10:49 am
Thank you for the Reply.
The actual issue i am facing is...
i have 30+ Reports which i have integrated in .Net and Passing the parameters from the .aspx page and generating the report in Report Viewer on click of a button.
I am calling Stored procedures and queries in the Data sets Using a Data Source and Designed the Report UI accordingly.
So far it is working fine.
Now, A sudden requirement has come, saying that the reports should also be able fetch the records from another database which has the same structure but different data.
As mentioned in the above post the reports should be able fetch 1-10 records from Database A and 11-20 Records from Database B(there are 100+ Tables and Sp's in Both).
As per My knowledge we can have only 1 Data source while creating the Data set.
I would like your valuable inputs on the same.
Thank you.
April 16, 2009 at 4:01 am
I Resolved this issue by validating the Database Connection string in the SP.
Something like: If @Db = 'A'
Begin
--Do Something
End
Else If @Db = 'B'
Begin
--Do Something
End
and so on....
Thank you All. 🙂
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply