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.
| Author |
Topic |
|
jasscat
Starting Member
9 Posts |
 Posted - 2008-08-05 : 14:54:50
|
| I'm having a problem using Report Builder. I need to use multiple data sources and I'm running into difficulty when I do so. I have two data sources under "Data Sources." Both reside on my local computer. I can do a "test connection" and everything is fine. I'm having no problem creating one data source view using objects from both data sources. I can add tables to the data source view from either source, or create named queries. When I explore data in the data source view designer, up pops the information I need. So far, so good.When I go to add a new report model, I keep getting errors on the objects from the second data source I added. The error is of the "an error occurred while executing a command. Message: Invalid object name 'dbo.whatever'. Command SELECT COUNT(*)FROM[dbo.][whatever]t" variety. I'm probably not seeing the complete sql statement and I'm not sure how to check what it's doing. What's going on here? I know the object exists. Like I said, I can see the data when I explore the object. Is there a restriction to using multiple data sources when using Report Builder? You can certainly use multiple data sources when creating a regular report, but maybe Report Builder is doing something behind the scenes that restricts it to one data source. I just don't know how to go forward on this. Help. |
|
|
jasscat
Starting Member
9 Posts |
Posted - 2008-08-06 : 09:21:54
|
| I think I found the answer to my own question...unfortunately. Seems like you cannot use multiple data sources to create a report model in Report Builder. See:http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=593115&SiteID=1[url][/url]I got around the problem by creating a view referencing the second data source database in the first data source database, and using that view in my data source view. The lack of support for multiple data sources in Report Builder seems like a problem to me, and it's not very well documented either. There are some other workarounds other people have mentioned, including using named queries, but I couldn't figure out how to use that. The named query is build in the data source view, and it still references the second data source, so I'm not sure what the advantage of using that is. If anyone can shed some light on this, I'd appreciate it. |
 |
|
|
|
|
|
|
|
Invalid Object Name using Report Builder