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 |
|
cipriani1984
Constraint Violating Yak Guru
304 Posts |
 Posted - 2008-09-12 : 06:04:00
|
| Question...... I have a query which links off 2 tables from one database and there is a derivedtbl coming off another database. Obviously when creating a dataset off a report you can only set it to one database. How would I go about setting a dataset to go off 2 databases of writing a query for reporting to go off tables from 2 databases?Thanks |
|
|
dexter.knudson
Constraint Violating Yak Guru
260 Posts |
Posted - 2008-09-13 : 00:41:17
|
| If it's on the same server, you can reference it something like the 2 tables here:SELECT *FROM test_db1.dbo.Table_1 t1INNER JOIN test_db2.dbo.Table_1a t1a ON t1.id = t1a.idIf it's on a different server, you need to create a linked server. Then you can add the server name to the prefix above. |
 |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-09-14 : 00:06:26
|
| You can use multiple datasources for single report as well. |
|
|
|
cipriani1984
Constraint Violating Yak Guru
304 Posts |
Posted - 2008-09-18 : 10:19:25
|
Thanks for the replies, yea found that out the hard way.Then it also gives collate errors, so had to convert the fields I was linking with. So advice to those who do it in future to use database name infront of the table name and Collate the field you were linking with otherwise you will get that error 'Collate Latin_1 etc'quote:
Originally posted by sodeep
You can use multiple datasources for single report as well.
|
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-09-18 : 10:28:44
|
| Not if your databases are in same Collation. |
|
|
|
|
|
|
Report off 2 databases