doing ODBC without access to ODBC

[deleted_user]

We are running on a client server EG install.

How would I build a query in EG without the access to ODBC databases module?

I've been told that EG 4.2 will do ODBC to oracle without either the access to odbc or odbc to oracle databases. I don't like the approach of doing an open to each table.

I would really like to be able to build queries in sql then use them in EG which is a capability I've used in Base SAS with access to odbc. Is this possible with EG?

finally, we should get EG 4.2 soon any odbc connection in EG is currently broken and it is hard to tell if my attempts to build queries is because of the odbc bug or because I'm doing it wrong. I'd like to have some tests available to run while the SAS tech support people are in the building that will prove full functionality before the say they are done and everything is working

[deleted_user]

So is it true? Can you do ODBC in EG without buying the access to ODBC package?

Silence makes me think that it is not true.

[ChrisHemedinger]

You can do it using File->Open->ODBC. It works great for small data sources, but there are performance considerations when you have large data.

See http://support.sas.com/kb/26/178.html for some more information.

Chris

[deleted_user]

Almost every place I've worked one of the first things that I have to solve is how to best get at the data. Often database servers are totally out of gas and simple queries can take for ever or never finish at all.

Once our update is finished and we have a functional EG with ODBC I'll have to experiment. The tables I'm going after currently are not huge (<100,000 rows).

I am thinking of two different approaches (I would prefer to do an access/odbc that permit direct sql to the datasource but the extra cost for the server is prohibative right now). One approach would be to create a project with an Open=>ODBC and select the tables I need then do a proc sql to the tables. The other approach is to create an access database with links to tables in oracle.

I'll have to experiment once I'm able to get a reliable odbc connection to anything.

[deleted_user]

They finished the upgrade on Friday. However, the ODBC that comes with EG still does not work with Oracle. The interface is slow and very limited.

I have created an access database that links the oracle tables then created an ODBC connection to the access tables for EG. This works better but can someone explain why SAS renames the tables? It appears to append a number to the table name and it seems to have no relation to anything

[ChrisHemedinger]

You might simply need an updated ODBC driver for Oracle. Microsoft ships a generic Oracle ODBC driver, but it's not going to be as good as the one you can get directly from Oracle.

Chris

[Doc_Duke]

Kossack,

One follow-up to Chris's reference. If you are writing SQL code, that code is being executed on your SAS Server, not locally on EGuide. So, if you are using a LIBNAME in your SQL code (other than the ECLIBxxx that EGuide built) or using SQL Passthru, then you are going to need the appropriate SAS/Access product licensed for the SAS server.

And it doesn't take much data for Eguide to bog down...

And, Chris, thanks for the writeup. The M&M video is a great illustration, but it doesn't stand alone very well.

Doc

[deleted_user]

I was wondering about that. One solution I'm thinking about is creating local access db with links to the tables on the oracle server.