Combining tables from different databases

Hi.

I'm currently working on a project which involves the creation of a
web page which reports selected data to customers from two back-end
systems. I use ASP script language on IIS, the server is MS SQL 2000.
Now I'm struggling with combining two tables from the different
databases. I'm sure it's simple enough, but I'm a little short on the
SQL expertise.

I've got two databases, db1 and db2, and then two tables, db1.t1 and
db2.t2. I need to combine these two tables (both tables have a
matching key field) to make a list of all items from db1.t1, and those
who correspond from db2.t2.

I can list all items from db1.t1, but I can't seem to get the db2.t2
joined in.
Can anybody help me with the syntax for this, please ? Help !

Answers, hints & tips greatly appreciated.
Thanks in advance !
KennethHi

You will need three part naming to do this

Use DB1 -- Connected to DB1!

SELECT t.Fld, s.Fld
FROM t1 t JOIN db2..t2 s ON t.Fld = s.Fld

John

"Kenneth Fosse" <kennethfosse@.hotmail.com> wrote in message
news:a4092994.0310110658.42c8abc7@.posting.google.c om...
> Hi.
> I'm currently working on a project which involves the creation of a
> web page which reports selected data to customers from two back-end
> systems. I use ASP script language on IIS, the server is MS SQL 2000.
> Now I'm struggling with combining two tables from the different
> databases. I'm sure it's simple enough, but I'm a little short on the
> SQL expertise.
> I've got two databases, db1 and db2, and then two tables, db1.t1 and
> db2.t2. I need to combine these two tables (both tables have a
> matching key field) to make a list of all items from db1.t1, and those
> who correspond from db2.t2.
> I can list all items from db1.t1, but I can't seem to get the db2.t2
> joined in.
> Can anybody help me with the syntax for this, please ? Help !
> Answers, hints & tips greatly appreciated.
> Thanks in advance !
> Kenneth|||You need to add it as a linked server then use the fully quaklified name
server.database.ownername.tablename

Look up sp_addlinkedserver in BOL

HTH

Ray Higdon MCSE, MCDBA, CCNA

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Combine result sets

Hi,
In my project, I need to combine multiple result sets into one single result
set for further processing. However, the result sets are not having the
same table structure and therefore I can't use union. Any ideas on how I
should do it?
Thanks.Cherly
Create a temporary table , so some of the columns will contrain NULLs or
create DEFAULT constraint
"Cheryl" <justtosayhi@.excite.com> wrote in message
news:BA3FE0A3-D1C9-41C7-B10B-5DDFB0C16284@.microsoft.com...
> Hi,
> In my project, I need to combine multiple result sets into one single
> result set for further processing. However, the result sets are not
> having the same table structure and therefore I can't use union. Any
> ideas on how I should do it?
> Thanks.|||On Sep 19, 10:33 pm, "Cheryl" <justtosa...@.excite.com> wrote:
> Hi,
> In my project, I need to combine multiple result sets into one single result
> set for further processing. However, the result sets are not having the
> same table structure and therefore I can't use union. Any ideas on how I
> should do it?
> Thanks.
overall it's easier to ensure that result sets have identical
structure.
add something like this to you result sets:
CAST(NULL AS MissingColumnType) AS MissingColumnName|||How different are the recordsets ? Is it just a question of creating a
couple of columns with '' as the value, so you can
get to the point of using UNION
--
Jack Vamvas
___________________________________
Need an IT job? http://www.ITjobfeed.com/SQL
"Cheryl" <justtosayhi@.excite.com> wrote in message
news:BA3FE0A3-D1C9-41C7-B10B-5DDFB0C16284@.microsoft.com...
> Hi,
> In my project, I need to combine multiple result sets into one single
> result set for further processing. However, the result sets are not
> having the same table structure and therefore I can't use union. Any
> ideas on how I should do it?
> Thanks.

Column Splitting

Hi Everyone,

I've been given the painstaking project of splitting a single column into multiple columns and rows. I have a solution set up in which I will be posting further down the post but I want to see if there is a much more efficient solution to this.

sample data:
create table tbl_list
(pk_int_itmid int(5) Primary Key,
vchar_desk vchar(300));

create table tbl_test1
(fk_int_itmid int(5) references tbl_list(pk_int_itmid),
vchar_itm varchar(60));

insert into tbl_list values
(1, 'this item');

insert into tbl_list values
(2, 'that item');

insert into tbl_list values
(3, 'those items');

insert into tbl_test1 values
(1, 'A, B - C, D, E - F, G, H - I');

insert into tbl_test1 values
(2, 'J, K - L, M, N - O');

insert into tbl_test1 values
(3, 'P, Q - R');

into this table:
create table tbl_output
(fk_int_itmid int(5) references tbl_list(pk_int_itmid),
vchar_itmA varchar(60),
vchar_itmB varchar(60),
vchar_itmC varchar(60));

Output in comma delimited form:
'1', 'A', 'B', 'C'
'1', 'D', 'E', 'F'
'1', 'G', 'H', 'I'
'2', 'J', 'K', 'L'
'2', 'M', 'N', 'O'
'3', 'P', 'Q', 'R'

my current solution:
create view vw_itm_a as
select fk_int_itmid,
substring(vchar_itm, 0, charindex('-',vchar_itm)) as vchar_itmA,
substring(vchar_itm, charindex('-',vchar_itm)+1 , charindex(',',vchar_itm)-charindex('-',vchar_itm)) as vchar_itmB,
substring(vchar_itm, charindex(',',vchar_itm)+1) as vchar_itmC
from tbl_test1
where charindex(',',vchar_itm) >1
Go

create view vw_itm_b as
select fk_int_itmid,
substring(vchar_itm, 0, charindex('-',vchar_itm)) as vchar_itmA,
substring(vchar_itm, charindex('-',vchar_itm)+1 , charindex(',',vchar_itm)-charindex('-',vchar_itm)) as vchar_itmB,
substring(vchar_itm, charindex(',',vchar_itm)+1) as vchar_itmC
from vw_itm_a
where charindex(',',vchar_itmC) >1;
Go

create view vw_itm_c as
select fk_int_itmid,
substring(vchar_itmC, 0, charindex('-',vchar_itmC)) as vchar_itmA,
substring(vchar_itmC, charindex('-',vchar_itmC)+1 , charindex(',',vchar_itmC)-charindex('-',vchar_itmC)) as vchar_itmB,
substring(vchar_itmC, charindex(',',vchar_itmC)+1) as vchar_itmC
from vw_itm_b
where charindex(',',vchar_itmC) >1;
Go;

create view vw_itm_d as
select fk_int_itmid, vchar_itmA, vchar_itmB,
substring(substring(vchar_itm, charindex(',',vchar_itm)+1), 0, charindex(',',vchar_itm)) as vchar_itmC
from vw_itm_a ia union vw_itm_b ib on ia.fk_int_itmid = ib.fk_int_itmid
Go;

create view vw_itm_e as
select fk_int_itmid, vchar_itmA, vchar_itmB,
substring(substring(vchar_itm, charindex(',',vchar_itm)+1), 0, charindex(',',vchar_itm)) as vchar_itmC
from vw_itm_c ia union vw_itm_b ib on ia.fk_int_itmid = ib.fk_int_itmid
Go;

create view vw_itm as
select fk_int_itmid, vchar_itmA, vchar_itmC, vchar_itmC
from vw_itm_a
where fk_int_itmid not in (select fk_int_itmid from vw_itm_b)
union
select fk_int_itmid, vchar_itmA, vchar_itmC, vchar_itmC
from vw_itm_d
union
select fk_int_itmid, vchar_itmA, vchar_itmC, vchar_itmC
from vw_itm_b
where fk_int_itmid not in (select fk_int_itmid from vw_itm_c)
union
select fk_int_itmid, vchar_itmA, vchar_itmC, vchar_itmC
from vw_itm_e
union
select fk_int_itmid, vchar_itmA, vchar_itmC, vchar_itmC
from vw_itm_c
Go;

select fk_int_itmid, vchar_itmA, vchar_itmC, vchar_itmC
into tbl_output
from vw_itm

Is there a much more efficient manner of handling this column splitting?

Thanks
DCyou have my sincere condolences

i would do this with application programming, not sql

Column Groups : Help with Matrix Report

I have a table that has 2 colums
Project Probablity
1 15
2 89
3 12
4 22
5 7
6 11
7 43
8 92
9 63
10 87
...n ...n
I have to show report that has to show the no. of projects that fall
in the probability 1-10,11-20,21-30,...,91-100
I want to show it in a matrix, Iam just confused about the column
groups and row groups. Can someone hint me how i can achieve this in
matrix ?
thanks
anand sagarYou can group on an expression like this:
=Ceiling(Fields!Probability.Value/10)
And then show this calculation: Count(Fields!Project.Value)
I'm not sure why you want to show this in a matrix since there's only one
axis. I'm assuming that's because you want it to be displayed horizontally.
For that, you would need the row grouping to be static. See this blog entry
for more details:
http://blogs.msdn.com/chrishays/archive/2004/07/23/193292.aspx
--
This post is provided 'AS IS' with no warranties, and confers no rights. All
rights reserved. Some assembly required. Batteries not included. Your
mileage may vary. Objects in mirror may be closer than they appear. No user
serviceable parts inside. Opening cover voids warranty. Keep out of reach of
children under 3.
"Anand Sagar" <anandsagar@.gmail.com> wrote in message
news:3f76a771.0408300414.50d12d6b@.posting.google.com...
> I have a table that has 2 colums
> Project Probablity
> 1 15
> 2 89
> 3 12
> 4 22
> 5 7
> 6 11
> 7 43
> 8 92
> 9 63
> 10 87
> ...n ...n
>
> I have to show report that has to show the no. of projects that fall
> in the probability 1-10,11-20,21-30,...,91-100
> I want to show it in a matrix, Iam just confused about the column
> groups and row groups. Can someone hint me how i can achieve this in
> matrix ?
>
> thanks
> anand sagar

Colspan in reports?

Is it possible to manipulate table or matrix cells with colspan in reports?
I have several reports that show Client and Project.
Currently, the only way I have to display it is like this:
Clientname
..............Project
What I want to do is like this:
Clientname
....Project
Is it possible to do this? How? Conditionally merge cells?
All help appreicated!
Kaisa M. LindahlYou are correct, merge the cells to get a colspan in a table. Simply
highlight the cells you want to merge (in the report designer) and
right click. You'll see then the option to Merge Cells.|||<cmarinella@.gmail.com> wrote in message
news:1104769372.216591.197530@.c13g2000cwb.googlegroups.com...
> You are correct, merge the cells to get a colspan in a table. Simply
> highlight the cells you want to merge (in the report designer) and
> right click. You'll see then the option to Merge Cells.
>
Ah, nice.
But I can only make it work in table view. Is it possible to do something
like it in matrix too?
Kaisa M. Lindahl

Collecting data from remote DBs

Got to start planning for a project that requires our system to collect data from different types of DB platforms remotely from our customers and store them in our SQL DB. Anybody know of any references I could read or where to start with this? I have a couple of ideas, but need to look at all the aspects of this to ensure that it's done correctly.

Thanks.What kind of database? Locating on what platform? How to remotely to access it? Through HTTP or something else?

In general, web service could be a possible solution.|||A couple of our clients are using SQL server, and others are using proprietary DB systems that have XML export capabilities. All are MS based systems and the data collection preference would be via http requests.

Thanks.