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!

Combining Reports

I'm trying to create a report that is actually 4 reports. Each has it's own page headers and Footers.

I can't use sub reports because I need the headers and footers to show for each indivual report.

My output is going to be PDF, and I know how to write code that i could use to combine the PDFs but I really want to do this in the report. I don't want to have to use some outside process to get my wanted output, If I can get away with it. Any help is appreciated!! Thanks.

There is no way to combine the reports into one PDF without an outside process.|||

Brad,

Your response not only lacks details but leaves me with the impression that combination reports will never be supported in Reporting Services. I have been developing reports for more years than I like to admit and I can say with hesitation that building a report that consolidates the output from several reports is by no means an uncommon practice. If Reporting Services wants to be an "enterprise" solution supporting common practices is going to have to become a priority.

Mark73

Combining Reports

I'm trying to create a report that is actually 4 reports. Each has it's own page headers and Footers.

I can't use sub reports because I need the headers and footers to show for each indivual report.

My output is going to be PDF, and I know how to write code that i could use to combine the PDFs but I really want to do this in the report. I don't want to have to use some outside process to get my wanted output, If I can get away with it. Any help is appreciated!! Thanks.

There is no way to combine the reports into one PDF without an outside process.|||

Brad,

Your response not only lacks details but leaves me with the impression that combination reports will never be supported in Reporting Services. I have been developing reports for more years than I like to admit and I can say with hesitation that building a report that consolidates the output from several reports is by no means an uncommon practice. If Reporting Services wants to be an "enterprise" solution supporting common practices is going to have to become a priority.

Mark73

sqlsql

Combining reports

I want to combine several reports into a single report to print out.
I want the correct page numbering on each of the individual reports so
I don't want to use subreports on a main report. Is there a way to
combine reports into a single report to allow the user to print
without having to print out each of the individual reports?On Apr 25, 8:03 pm, jwchoi...@.gmail.com wrote:
> I want to combine several reports into a single report to print out.
> I want the correct page numbering on each of the individual reports so
> I don't want to use subreports on a main report. Is there a way to
> combine reports into a single report to allow the user to print
> without having to print out each of the individual reports?
The only thing I can think of is to create a single report that has
all the controls of each report (i.e., add x number of table controls
to a single report for x number of reports). Sorry that I could not be
of further assistance.
Regards,
Enrique Martinez
Sr. Software Consultant

Combine Chart and Matrix on page

I want to combine my matrix and my chart for the current selection on
a single page.
As it is now, I'm stick with all of the results and the chart below
in. I also have parenting on the left side... however... the only
way for my chart to show the selection from the left, I have to put it
on page break. When I do that, my chart gets pushed to the very
last page of the report.
How can I get the chart to stay on the current parent selection
results and reflect the selected change?On Sep 20, 2:04 pm, Bruce Lawrence <BL32...@.gmail.com> wrote:
> I want to combine my matrix and my chart for the current selection on
> a single page.
> As it is now, I'm stick with all of the results and the chart below
> in. I also have parenting on the left side... however... the only
> way for my chart to show the selection from the left, I have to put it
> on page break. When I do that, my chart gets pushed to the very
> last page of the report.
> How can I get the chart to stay on the current parent selection
> results and reflect the selected change?
I'm not sure if I understand you, but you might try including the
chart and matrix control inside a single rectangle. Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant

Columns on Summary Page of SSMS

Hello all,
The Summary Page In SSMS looking at the tables of a DB in Details view,
there are 3 columns: Name, Schema, & Created.
Is there any way to select other columns to view?
I created a "Notes" property in the Extended Properties of a Table's
Properties with some info on why that table is there and what the data is.
I would like to display this as a column in the Summary Page.
Is this possible?
Thanks for any help anyone can provide,
Conan Kelly
Have a look at this blog. There is a lot of good info related to SSMS and
specifically custom reports in SP2.
http://blogs.msdn.com/sqlrem/default.aspx
Andrew J. Kelly SQL MVP
"Conan Kelly" <CTBarbarinNOSPAM@.msnNOSPAM.comNOSPAM> wrote in message
news:7H7Wh.62184$VU4.46621@.bgtnsc05-news.ops.worldnet.att.net...
> Hello all,
> The Summary Page In SSMS looking at the tables of a DB in Details view,
> there are 3 columns: Name, Schema, & Created.
> Is there any way to select other columns to view?
> I created a "Notes" property in the Extended Properties of a Table's
> Properties with some info on why that table is there and what the data is.
> I would like to display this as a column in the Summary Page.
> Is this possible?
> Thanks for any help anyone can provide,
> Conan Kelly
>

Columns on Summary Page of SSMS

Hello all,
The Summary Page In SSMS looking at the tables of a DB in Details view,
there are 3 columns: Name, Schema, & Created.
Is there any way to select other columns to view?
I created a "Notes" property in the Extended Properties of a Table's
Properties with some info on why that table is there and what the data is.
I would like to display this as a column in the Summary Page.
Is this possible?
Thanks for any help anyone can provide,
Conan KellyHave a look at this blog. There is a lot of good info related to SSMS and
specifically custom reports in SP2.
http://blogs.msdn.com/sqlrem/default.aspx
Andrew J. Kelly SQL MVP
"Conan Kelly" <CTBarbarinNOSPAM@.msnNOSPAM.comNOSPAM> wrote in message
news:7H7Wh.62184$VU4.46621@.bgtnsc05-news.ops.worldnet.att.net...
> Hello all,
> The Summary Page In SSMS looking at the tables of a DB in Details view,
> there are 3 columns: Name, Schema, & Created.
> Is there any way to select other columns to view?
> I created a "Notes" property in the Extended Properties of a Table's
> Properties with some info on why that table is there and what the data is.
> I would like to display this as a column in the Summary Page.
> Is this possible?
> Thanks for any help anyone can provide,
> Conan Kelly
>

Columns on Summary Page of SSMS

Hello all,
The Summary Page In SSMS looking at the tables of a DB in Details view,
there are 3 columns: Name, Schema, & Created.
Is there any way to select other columns to view?
I created a "Notes" property in the Extended Properties of a Table's
Properties with some info on why that table is there and what the data is.
I would like to display this as a column in the Summary Page.
Is this possible?
Thanks for any help anyone can provide,
Conan KellyHave a look at this blog. There is a lot of good info related to SSMS and
specifically custom reports in SP2.
http://blogs.msdn.com/sqlrem/default.aspx
Andrew J. Kelly SQL MVP
"Conan Kelly" <CTBarbarinNOSPAM@.msnNOSPAM.comNOSPAM> wrote in message
news:7H7Wh.62184$VU4.46621@.bgtnsc05-news.ops.worldnet.att.net...
> Hello all,
> The Summary Page In SSMS looking at the tables of a DB in Details view,
> there are 3 columns: Name, Schema, & Created.
> Is there any way to select other columns to view?
> I created a "Notes" property in the Extended Properties of a Table's
> Properties with some info on why that table is there and what the data is.
> I would like to display this as a column in the Summary Page.
> Is this possible?
> Thanks for any help anyone can provide,
> Conan Kelly
>

columns

I have a report I am creating that needs to look similar to a newspaper. I
want it to flow down a column then at the end of the page I want it to jump
to the top and start population the next column until I have three columns
total then start all over on the second page in the first column...Is this
possible?OK, I have figured out how to have multiple columns. But it shows on the
design view that there are multple columns, so why wont it jump to the
second column when the report is ran?
"Ben Watts" <lunuticshouse@.hotmail.com> wrote in message
news:eBgRwRgiIHA.1188@.TK2MSFTNGP04.phx.gbl...
>I have a report I am creating that needs to look similar to a newspaper. I
>want it to flow down a column then at the end of the page I want it to jump
>to the top and start population the next column until I have three columns
>total then start all over on the second page in the first column...Is this
>possible?
>

column heading is missing on some pdf rendered pages

I am new to RS so this might be a stupid question. Since the page header
can't have table, I have placed the table in the body as table group header.
The table contains column heading for my report. The report produces over a
hundred pages of PDF files. As I scroll through the PDF pages I noticed that
on some pages the column heading is missing. Does anyone know why or is there
a better way to do this?
Thanks,
TimThis might not be the right answer, but my impression is that where pages
break in the web report, and in the PDF exported report, are two different
things. I noticed it when I added a footer with a line to a report. THis
line would show up at the end of each page in the web report, and totally
randomly in the PDF report. And there would be a lot more text on each page
in the web report, than in the PDF. At least this goes for reports with 8pt
font and landscape view.
Try to check if what you see on each page in the PDF corresponds with what
you see in your web report.
Kaisa M. Lindahl
"Tim2Be" <Tim2Be@.discussions.microsoft.com> wrote in message
news:5660FC32-2C75-43F2-BA22-212001F7D535@.microsoft.com...
> I am new to RS so this might be a stupid question. Since the page header
> can't have table, I have placed the table in the body as table group
header.
> The table contains column heading for my report. The report produces over
a
> hundred pages of PDF files. As I scroll through the PDF pages I noticed
that
> on some pages the column heading is missing. Does anyone know why or is
there
> a better way to do this?
> Thanks,
> Tim|||My web report looks perfectly find with all the column heading in tack on all
pages. Only some of the PDF pages that is missing the column heading. I don't
know why it does that.
"Kaisa M. Lindahl" wrote:
> This might not be the right answer, but my impression is that where pages
> break in the web report, and in the PDF exported report, are two different
> things. I noticed it when I added a footer with a line to a report. THis
> line would show up at the end of each page in the web report, and totally
> randomly in the PDF report. And there would be a lot more text on each page
> in the web report, than in the PDF. At least this goes for reports with 8pt
> font and landscape view.
> Try to check if what you see on each page in the PDF corresponds with what
> you see in your web report.
> Kaisa M. Lindahl
> "Tim2Be" <Tim2Be@.discussions.microsoft.com> wrote in message
> news:5660FC32-2C75-43F2-BA22-212001F7D535@.microsoft.com...
> > I am new to RS so this might be a stupid question. Since the page header
> > can't have table, I have placed the table in the body as table group
> header.
> > The table contains column heading for my report. The report produces over
> a
> > hundred pages of PDF files. As I scroll through the PDF pages I noticed
> that
> > on some pages the column heading is missing. Does anyone know why or is
> there
> > a better way to do this?
> >
> > Thanks,
> > Tim
>
>

Column Headers at the top of the page....?

I have a report looking something like this:

Field 1 Field 3 Field 5

RandomInfo1 RandomInfo1 RandomInfo1

RandomInfo2 RandomInfo2 RandomInfo2

Field 2 Field 4 Field 6

RandomInfo1 RandomInfo1 RandomInfo1

RandomInfo2 RandomInfo2 RandomInfo2

It extends along a fair number of pages, and I'm currently using the "# of columns = 3" in the Report properties --> Layout section in order to make it span over 3 columns instead of the usual 1. I'm using a table in which the random info fields are the repeating values, and the Field 1,2,3,4,etc are included in the list control, but not part of the physical table itself.

My question stems from a couple of chunks of data which basically extend over the course of 1.5 pages (yep. There's enough to fill about 4.5 columns worth.)

I currently have

Field 1 RandomInfo50 RandomInfo100

RandomInfo1 RandomInfo51 RandomInfo101

Randominfo2 RandomInfo52 RandomInfo102

etc.... etc.... etc....

--NewPage-

RandomInfo150 Field 2

Randominfo151 NewRandomInfo1

RandomInfo152 NewRandomInfo2

etc...

What I'm looking to do is have "Field 1" appear at the top of each column whenever a larger chunk of data forces itself into multiple columns, or even multiple columns on multiple pages. I have this nasty feeling it's something silly and blatantly obvious I'm missing...

Any thoughts?

Is "repeat header rows on each page" checked in the properties for the matrix/table?|||

Wow.

I swear I tried that in earlier attempts... It caused a strange... bleh. No matter!

Cheers Greg!

Column Headers at the top of the page....?

I have a report looking something like this:

Field 1 Field 3 Field 5

RandomInfo1 RandomInfo1 RandomInfo1

RandomInfo2 RandomInfo2 RandomInfo2

Field 2 Field 4 Field 6

RandomInfo1 RandomInfo1 RandomInfo1

RandomInfo2 RandomInfo2 RandomInfo2

It extends along a fair number of pages, and I'm currently using the "# of columns = 3" in the Report properties --> Layout section in order to make it span over 3 columns instead of the usual 1. I'm using a table in which the random info fields are the repeating values, and the Field 1,2,3,4,etc are included in the list control, but not part of the physical table itself.

My question stems from a couple of chunks of data which basically extend over the course of 1.5 pages (yep. There's enough to fill about 4.5 columns worth.)

I currently have

Field 1 RandomInfo50 RandomInfo100

RandomInfo1 RandomInfo51 RandomInfo101

Randominfo2 RandomInfo52 RandomInfo102

etc.... etc.... etc....

--NewPage-

RandomInfo150 Field 2

Randominfo151 NewRandomInfo1

RandomInfo152 NewRandomInfo2

etc...

What I'm looking to do is have "Field 1" appear at the top of each column whenever a larger chunk of data forces itself into multiple columns, or even multiple columns on multiple pages. I have this nasty feeling it's something silly and blatantly obvious I'm missing...

Any thoughts?

Is "repeat header rows on each page" checked in the properties for the matrix/table?|||

Wow.

I swear I tried that in earlier attempts... It caused a strange... bleh. No matter!

Cheers Greg!