Report with 2 Databases?

Hello NG,
Is it possible to build a Report or rather a Connectionstring with two
Databases?
Thanks
ThomasHello,
I think it's not possible.
You can combine data from two databases only by using two datasets with two
separate db connections.
best regards,
Radoslaw Lebkowski
U¿ytkownik "Thomas Burger" <thomas.burger@.nospam.de> napisa³ w wiadomo¶ci
news:eYkdrjZNIHA.4948@.TK2MSFTNGP02.phx.gbl...
> Hello NG,
> Is it possible to build a Report or rather a Connectionstring with two
> Databases?
> Thanks
> Thomas
>|||try something like this
select *
from table1 t join
database2.dbo.table2 t2 on t.id=t2.id
That way they're in the same dataset and you're selecting from two
databases. Of course the connection string for table 1 would be the
first database.|||On Dec 3, 5:41 am, "Thomas Burger" <thomas.bur...@.nospam.de> wrote:
> Hello NG,
> Is it possible to build a Report or rather a Connectionstring with two
> Databases?
> Thanks
> Thomas
It is possible to define a single DataSet with an Expression
ConnectionString, so that the connection string is swapped based on
the report parameter. This can only be done with RS 2005 and up.
It would be easier to build a Linked Server and query against that in
my opinion.
-- Scott|||Ok thank you it works.
"SQL Guy" <aymantg@.gmail.com> schrieb im Newsbeitrag
news:81b6bd8f-ddee-4a73-a369-c570d3b3aad1@.l16g2000hsf.googlegroups.com...
> try something like this
> select *
> from table1 t join
> database2.dbo.table2 t2 on t.id=t2.id
>
> That way they're in the same dataset and you're selecting from two
> databases. Of course the connection string for table 1 would be the
> first database.|||Orne, could you show us an example. It's nice to have multiple
solutions to a problem. Also, I don't think a linked server would be
needed in this case since the databases are on the same server.|||On Dec 3, 10:11 am, SQL Guy <ayma...@.gmail.com> wrote:
> Orne, could you show us an example. It's nice to have multiple
> solutions to a problem. Also, I don't think a linked server would be
> needed in this case since the databases are on the same server.
Normally, I used a Shared Data Source for my Reports, but you don't
have to.
In your Data tab, click the Dataset Properties button [...] . You
should see an combo box that says Data Source, with a button next to
it [...]. Clicking this gets you a Data Source Properties window. If
you uncheck the "Use a shared data source reference" box, you can now
enter an explicit connection string for the report.
In the Connection String box, you can see a Expression button [fx].
Clicking this enters the expression builder. To access a SQL Server,
you can use a "Microsoft SQL Server" in the Type combobox, Integrated
Security for credentials, and use the following in the connection
string window:
="Data Source=YOURSERVER;Initial Catalog=YOURCATALOG"
Because it is an expression string, you can swap servers (instances)
with a parameter:
="Data Source=" & Parameters!ServerName.Value & ";Initial
Catalog=YOURCATALOG"
You lose the ability to preview your data with the [!] button, but if
you have an issue like we do here where we have a Production server
(with Now to -7 days of data) and an Archive server (with Yesterday to
-7 years of data) with the same schema names, etc etc then it could be
a solution for you. Same Fields returned so you use the same Report.
-- Scott|||Since it's a string, you can technically use a parameter to define the
database through Catalog=YOURCATALOG
Now that is really nice!