May 1, 2009 at 11:54 am
Hello,
I am creating a report in SSRS and the issue that I am facing is that I need to use two separate databases for that. How can i do that as in a report you can have only one connection string.
For Example. I have the userId in one database and the name of the person in another database.
1) Product Database --> has a table Product---> has two columns 1) Product 2) UserID
2) Employee Database--> has a table Employee--> has two columns 1) userID 2) name.
Now I my query I want to show the Product and the Name of the person. How Can I do that as they are two different databases and I don't see an option in the reporting Tool dataset to have two connections.
Kindly Advice. Thanks!
May 1, 2009 at 11:59 am
How about creating a synonym in one of the databases for the objects in the other database? That should allow what you need.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 4, 2009 at 1:39 am
Hi,
What about creating one view, inwhich you can fetch the id and name , then within SP you can select the records from this view.
Kindly let me know if any issue with this approach
Regards,
M.C
Thanks & Regards,
MC
May 4, 2009 at 9:11 am
Are they on the same server? If not are they linked servers? If so,
Select p.UserID, e.Name, p.Prodoct
From ProductDatabase.dbo.tableProduct As p
Join EmployeeDatabase.dbo.tableEmployee As e
On p.UserID = e.UserID
hth
May 4, 2009 at 4:23 pm
Hi
The best way to solve this issue is to create a view or stored proc in database one by referencing (joining) to the table in the second database. This also helps in reducing code maintenance on the SSRS side.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply