Multiple FileDSN Conflicts

[Glen Vickers]

I have a dB with 13 FileDSN links to tables from diff dBs with diff schema names but the same table name. Trying to make union queries to pull the same info for each of the 13 site locations.



Individually my 13 site dBs work find, but when I try to put in the 13 diff links I get the ORA-00942 message about table or view does not exist. My schema.table naming is as follows

sentinel_bwd.individual
sentinel_cps.individual

When I open the dB and select any of the 13 diff links they work but I get the error after selecting another table. It seems to latch onto the first connection and any subsequent redirection via trying to open a linked table or query another site linked table generates the error.

I do not get the same performance if I do a machine ODBC link for the 13 diff sites and it works properly. The problem with that is if you email it to anyone to use, they have to set up 13 specific ODBC connections which is not realistic for end users. I need to be able to use the FileDSN process where the connection is in the dB so I can email to anyone and they can use without any manual ODBC connection.

There is some diff in performance for multiple connections with the FileDSN and manual machine specific ODBC connection.

I'm sure others have seen this as its kinda normal course of business to try to make consolidate queries for multiple sites.

[Minty]

Is the data all stored in the same backend database with a different schema prefix?
Is so why not create a view or stored procedure to return the unionised data, it's probably much more efficient?

[Glen Vickers]

Is the data all stored in the same backend database with a different schema prefix?
Is so why not create a view or stored procedure to return the unionised data, it's probably much more efficient?

There are 13 diff dBs that put out real-time data for workers, hundreds/shift. I'm the guy trying to create that unionized data with 13 FileDSN connections. I want to create a dB that creates those fleetwide roll-ups of data common to all the sites.

I can unionize all 13 in a single dB if I do 13 manual ODBC connections, but there is no way an end user is going to set up 13 manual ODBC connections.

Someone suggested trying a diff driver. I'm using the oldest driver that is common to all users at 13 sites. If I go to a driver that is too new, then half of the users cant use the dB.

Thank you for trying to help me help others.

[Minty]

Okay - Can you link those remotely, and provide a single 14th source of the merged data.
That would at least simplify the setup.

Another possible answer is to keep a table of those sources and their ODBC connection details and use that to connect to the data, a DSNLess solution?
You can create a connection on the fly using the table data.

[Glen Vickers]

Okay - Can you link those remotely, and provide a single 14th source of the merged data.
That would at least simplify the setup.

Another possible answer is to keep a table of those sources and their ODBC connection details and use that to connect to the data, a DSNLess solution?
You can create a connection on the fly using the table data.

I am trying to create that 14th dB with a small footprint of table links only to create those fleet-wide roll-ups. Access struggles with 13 union queries so my queries have to be efficient or they will time out. I liked the pass-thru query as a source for each site as I could use it to filter the external sources to current yr active workers so my union query is only pinging about 2000 records/site and not 1E5 records/site.

Have not heard of putting connection data in a table, sounds interesting. I'll search for some examples.

[Minty]

I was suggesting connecting the databases in another backend DB (SQL or Oracle) to produce a single source of the merged data.
That would then only entail each individual site DB having one other connection to the "Master Data" union stuff.
This would have the advantage of speeding up the result sets as well.

[Glen Vickers]

I was suggesting connecting the databases in another backend DB (SQL or Oracle) to produce a single source of the merged data.
That would then only entail each individual site DB having one other connection to the "Master Data" union stuff.
This would have the advantage of speeding up the result sets as well.

Thats exacty what I'm trying to do. I have 13 connections to a single dB and trying to unionize those 13 sources so a single query filter by an end user can retrieve the records for a worker at the 13 sites with one query.

The multiple connections in the single dB aren't playing well together. Can do 13 query union with manual machine ODBC connection, but not with 13 FileDSN table links or multiple passthru queries.

thanks,

[Minty]

I think you still misunderstand my intention.

Just so I'm not barking up the wrong tree here - You have a SQL or Oracle backend database with 13 schema's in it?
Each schema is the localised data for your localised front ends?

Assuming the above, what I am suggesting is to create (in the SQL or Oracle BACKEND) a union of those schema's data.

This could then be saved in another 14th Schema lets call it
Sential_Master.view_MasterData

Then you only need to connect each front end to one alternative schema.
Or create another view in each regional schema that uses the master schema in the backend.

Does that make sense?

[Glen Vickers]

I think you still misunderstand my intention.

Just so I'm not barking up the wrong tree here - You have a SQL or Oracle backend database with 13 schema's in it?
Each schema is the localised data for your localised front ends?

Assuming the above, what I am suggesting is to create (in the SQL or Oracle BACKEND) a union of those schema's data.

This could then be saved in another 14th Schema lets call it
Sential_Master.view_MasterData

Then you only need to connect each front end to one alternative schema.
Or create another view in each regional schema that uses the master schema in the backend.

Does that make sense?

dB is Oracle

There is an app people interact with and the supporting dB. I'm just trying to ping off of key tables to look for high value data. I'm creating ODBC table links to the back end data so my queries would be on the front end I suppose. I was hoping to be able to do myself to be able to respond to ever changing business questions rather than have one of the company DBA create that view on one of the server dBs which would give me a single connection. I agree that would work.