Using 1 query to combine info from multiple databases

[cdck]

I have data which is stored/managed in 3 different databases - 2 of which are SQL server and 1 is Access. I need to Query all three databases to combine the information needed into one recordset, then display the results on an ASP page.

For example, Vendor information is stored thus:

Database1 (QC db, Access): Vendor certification status
Database2 (ERP, SQL server): Vendor info and on-time delivery
Database3 (Production, SQL server): Vendor rejection rates

For each vendor, I need to pull their info, their cert status, their on-time delivery rate, and their rejection rate. This data will then be output, one vendor/row at a time, on an intranet page to show complete eligibility for vendor approval.

As a first step, I attempted to link one of the SQL Servers to the Access information as follows:

1. In the Access db (Documents), I added linked tables from the SQL server database (M2M) using a system DSN available on my machine.

2. In Documents, I created a query (qryVendInfo) that pulled some data from Documents, and some data through the linked tables in M2M.

3. On the intranet page, I use a DSN to connect to Documents.

4. The intranet runs a query that pulls information from qryVendInfo, then manipulates it and outputs it.

When the page is loaded, it gets to the point where it tries to query qryVendInfo, and I get the error
[tt]Microsoft OLE DB Provider for ODBC Drivers error '80004005'

[Microsoft][ODBC Microsoft Access Driver] ODBC--connection to 'M2M' failed. [/tt]

What do I need to do to make this work?

Cheryl dc Kern

 

[Sheco]

Is it even possible to query a linked table in Acess via ODBC?

I'd make a quick VBS that runs a simple query like:[tt]
SELECT Count(*) FROM MyTable[/tt]
to test if this approach is even possible.

If that works then try the same query from ASP...

How about move the Access data into the SQL server so you don't have to mess with Access at all?

 

[cdck]

I can move the access db into SQL server, no problem, but I can't combine the data into either of the existing databases. Forgetting the idea of using access at all, how do I combine data from the 3 different databases in one query?

Cheryl dc Kern

 

[Sheco]

Look up sp_addlinkedserver in the SQL Server "Books Online" help file... it even says you can make an OLEDB link from SQL Server into Access so maybe you can use it to do your original approach, only backwards.

 

[gmmastros]

Run the query from one of your SQL Servers.

So some research on sp_addlinkedserver. You can create multiple linked servers within one of your servers. Then you can query from the linked servers 'almost' as if they were in the same database.

Ex:

Select *
From   Table1
       Inner Join ErpLinkedServer.DatabaseName.dbo.TableName As ErpTable
         On Table1.Column = ErpTable.Column
       Inner Join QCLinkedServer.DatabaseName.dbo.TableName As QCTable
         On Table1.Column = QCTable.Column

The important part here is that you will need to use the 4 part naming convention.

ServerName.DatabaseName.ObjectOwner.TableName

ObjectOwner is almost always dbo.



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[cdck]

Thank you, George and Sheco. I'll read up on and experiment with your suggestions, and get back to you with my results!

Cheryl dc Kern