retrieve data from three databases

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.

All Forums

Development Tools

Reporting Services Development

retrieve data from three databases

Author

Topic

Haz
Starting Member

38 Posts

Posted - 2008-12-02 : 21:26:53

Hi there,
My task for this week is to retrieve several data from three database. I mean same field name but from different database.how could i do that?
have anybody ever done like that?
Thank you..
Haz

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-03 : 12:42:39

Are databases in same server? if yes just use

SELECT field FROM db1..table
UNION ALL
SELECT field FROM db2..table
UNION ALL
SELECT field FROM db3..table

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-03 : 12:47:21

If they are in different servers, just use OPENROWSET or add them as linked server and use

SELECT field from server1.db1..table
UNION ALL
SELECT field from server2.db2..table
UNION ALL
SELECT field from server3.db3..table

Haz
Starting Member

38 Posts

Posted - 2008-12-04 : 23:34:11

Thank you visakh16

Haz
Starting Member

38 Posts

Posted - 2008-12-15 : 01:08:34

Can I use UNION like this way
Select A
from table A left join table B /* table A and B in the same db*/
on A.[primarykeys]= B.[primarykeys]
Group by A
UNION ALL
Select B
from table C left join table D/* table C and D in the same db*/
on C.[primarykeys]= D.[primarykeys]
Group by B

I got error:
Incorrect syntax near the keyword 'UNION'.
Anything wrong here?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-15 : 01:17:39

[code]
SELECT A
FROM
(
Select A
from table A
left join table B
on A.[primarykeys]= B.[primarykeys]
Group by A
)t1
UNION ALL
SELECT B
FROM
(
Select B
from table C
left join table D
on C.[primarykeys]= D.[primarykeys]
Group by B
)t2
[/code]

Haz
Starting Member

38 Posts

Posted - 2008-12-15 : 04:58:32

Can i put t1 as a table A or
t1 is another table beside table A and B?(in the same db)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-15 : 07:28:53

quote:
Originally posted by Haz

Can i put t1 as a table A or
t1 is another table beside table A and B?(in the same db)

t1 and t2 are aliases for derived table created. just put it as it is

Haz
Starting Member

38 Posts

Posted - 2008-12-30 : 21:23:48

Hi there,
I need to create a report which retrieve data from 3 db.So i need to get total for each db.
Could anyone tell me how to get that total..i try using report wizard,but i just get total for all db
and total for each data only..
any idea?

Thank you

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-01 : 13:34:24

quote:
Originally posted by Haz

Hi there,
I need to create a report which retrieve data from 3 db.So i need to get total for each db.
Could anyone tell me how to get that total..i try using report wizard,but i just get total for all db
and total for each data only..
any idea?

Thank you

are you applying any kind of grouping based on each db data?

Haz
Starting Member

38 Posts

Posted - 2009-01-01 : 21:14:48

Hi,
yes i have only one group for each db-customer no..i want total for each customer, total for each db and total for all..
i already get both total except total for each db..

Haz
Starting Member

38 Posts

Posted - 2009-01-01 : 22:51:55

i also want to retrieve data using parameter-cust no, so where can i put this syntax: where (Vendor.Cust_No. = @CustNo)..?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-02 : 01:31:16

quote:
Originally posted by Haz

i also want to retrieve data using parameter-cust no, so where can i put this syntax: where (Vendor.Cust_No. = @CustNo)..?

put it in query in dataset or if you're using procedure inside it. make usre you run it again in datatab and refresh so that report takes parameter info automatically.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-02 : 01:32:46

quote:
Originally posted by Haz

Hi,
yes i have only one group for each db-customer no..i want total for each customer, total for each db and total for all..
i already get both total except total for each db..

then use
=SUM(Fields!yourfield.value,"YourDB Group name") in group footer to get totals for the group.

for getting total for customer you might need to group by customer as well and use similar expression in its footer.

Haz
Starting Member

38 Posts

Posted - 2009-01-02 : 03:17:46

quote:
Originally posted by visakh16
put it in query in dataset or if you're using procedure inside it. make usre you run it again in datatab and refresh so that report takes parameter info automatically.

You mean like this:
SELECT A db1
FROM
(
Select A
from table A
left join table B
on A.[primarykeys]= B.[primarykeys]
where (A.Cust_No. = @CustNo)
Group by A
)t1
UNION ALL
SELECT B
FROM
(
Select B db2
from table C
left join table D
on C.[primarykeys]= D.[primarykeys]
where (A.Cust_No. = @CustNo)
Group by B
)t2

I already try this but what i found when @CustNo= CustA,they will display result if db1 and db2 have CustA.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-02 : 03:22:14

i think it should be like below

SELECT A --db1
FROM
(
Select A 
from table A 
left join table B 
on A.[primarykeys]= B.[primarykeys]
and (A.Cust_No. = @CustNo)
Group by A
)t1
UNION ALL
SELECT B 
FROM
(
Select B--db2
from table C 
left join table D
on C.[primarykeys]= D.[primarykeys]
and (A.Cust_No. = @CustNo)
Group by B
)t2

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-02 : 03:23:26

the reason is this

http://weblogs.sqlteam.com/jeffs/archive/2007/05/14/60205.aspx

Haz
Starting Member

38 Posts

Posted - 2009-01-05 : 04:11:16

Thanks its work ..
previously you say =SUM(Fields!yourfield.value,"YourDB Group name")
I confius about this "YourDB Group name" ,
In the grouping and sorting property windows,in general tab
at Name:table1_Group1(default)
You mean "YourDB Group name" = table1_Group1
is that correct..?

my big problem is to get total for each db..

for example:
Customer Name Amount
CustA(regionA) 5.0
Total CustA 5.0

CustB(regionA) 10.0
Total CustB 10.0
Total Region A 15.0 <-- i do not know how to get this

CustA(RegionB) 5.0
Total CustA 5.0
Total Region B 5.0 <-- this also

Total Overall : 20.0

Any idea?

Haz
Starting Member

38 Posts

Posted - 2009-01-07 : 21:19:06

I have an idea to solve my problem but i dont know how to make it..
since i use union to retrieve record from 2 databases,i plan to get db name..so next the result will be like this:
DbName CustNo CustName
db1 cust1 custA
db1 cust2 custB
db2 cust1 custA
db2 cust2 custB

after that i can do grouping by DbName and get the sum total for each dbname..The problem is after i put DB_NAME() As [DB Name],they display only one database name...any idea?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-08 : 03:15:59

i've answered in other thread

Haz
Starting Member

38 Posts

Posted - 2009-01-08 : 19:48:07

Thank you very very much...
you save my life....

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources