Combining two different datasources in one report

[hambo12]

Hi all,

I need to make an urgent report that combines data from two datasources in the one report.

I have heard this is not as easy as it sounds. Basically I have the following:

one datasource connected via ODBC to an access database:
Fields:
Date
BodyNo
Weight

another datasource being a progress database connected via ODBC:
Fields:
Date
BodyNo
Weight

The two datasources can be linked by date and bodyno.

I want to have a very simple table showing each field in both datasources:

------------------------------------------------------------------------------
DB1.Date . DB2.Date . DB1.BodyNo . DB2.BodyNo . DB1.Weight . DB2.Weight
------------------------------------------------------------------------------
And so on.

How do I go about doing this? Can I use a subreport and still have the link between the data sources?

 

[bburnell]

Why not make a view and use that with the report?
~Brett

--------------------------
Web/.net Programmer & DBA
Central PA

 

[hambo12]

hmmm, a view...

So where do I create this view?
I try to create it in SQL reporting services, and it says it doesnt support views?

So I try to create a view as follows in SQL Server 2005:

create view PIC as

select *
from openrowset('stockaidnew', 'Dsn=stockaidnew; trusted_connection=Yes;',
'select KillDateTime, KillBodyNo, HotScoreWeight
from tblbeastcarcass') as TStockaid
union all

select *
from openrowset('ccc', 'Dsn=ccc; uid=sysprogress; persist security info=True;',
'select DateKill, TxBody, WeightHDW') as TCCC


I am trying to connect 2 datasources that are connected via ODBC. Is the query above correct? Because I get the following error message:

Msg 7403, Level 16, State 1, Procedure PIC, Line 3
The OLE DB provider "stockaidnew" has not been registered.

Any ideas??

 

[hambo12]

Ok to answer my own question, I have fixed part of the openrowset query, but now the following query does not work:

select *
from openrowset('Microsoft.Jet.OLEDB.4.0', 'Z:\Stockaid_Data.mdb';'administrator'; '',
'select KillDateTime, KillBodyNo, HotScoreWeight from tblbeastcarcass') as TStockaid

It is an ODBC connection to a remote MDB, mapped to Z:\ drive.

This is the error:

OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" returned message "Unspecified error".
Msg 7303, Level 16, State 1, Line 3
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".

Any ideas??

 

[bburnell]

Yes, the view has to be written in SQL Server and then used by the report.

Try this for your error:

http://support.microsoft.com/kb/814398

~Brett

--------------------------
Web/.net Programmer & DBA
Central PA