Desktop productivity for business analysts and programmers

Reading data from a MS SQL database; which connection type to use?

Reply
Frequent Contributor
Posts: 79

Reading data from a MS SQL database; which connection type to use?

Hi, I use EG61 and also for some purposes SAS JMP 11. I have found, that from SAS JMP, I can access a SQL database sitting on a server. It looks as if JMP uses a ODBC type access to the database. I would perfer to do it from EG61, but the connection mechanism seems to be different. I am not able to set up the ODBC data source, but should I use SAS/Access instead. Is it correct, that there are such differences?

Regards

Poul Ravn Sørensen

Esteemed Advisor
Posts: 6,646

Re: Reading data from a MS SQL database; which connection type to use?

What is your SAS setup? Do you work with local or remote SAS, and in case of remote, which operating system does the server use?

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Frequent Contributor
Posts: 79

Re: Reading data from a MS SQL database; which connection type to use?

Sorry, I should have mentioned: The SAS installation is on a remote server. It is 9.3.

Regards

Poul

Esteemed Advisor
Posts: 6,646

Re: Reading data from a MS SQL database; which connection type to use?

And is the remote server also Windows, or another platform, and where do you run JMP?

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Frequent Contributor
Posts: 79

Re: Reading data from a MS SQL database; which connection type to use?

Yes, both are on Windows. My local JMP is on win7. I am not 100% sure about the remote machine though. What could the choices be? What should I look for?

Esteemed Advisor
Posts: 6,646

Re: Reading data from a MS SQL database; which connection type to use?

Like the modules for accessing RDBMS's, SAS/ACCESS to ODBC needs to be licensed.

So you either need the license for ACCESS to a) ODBC or b) your RDBMS.

WIth a), you need the ODBC source configured on the SAS server, with b), you need the DB client installed and working.

A workaround is to open the ODBC source in Enterprise Guide and import the data via EG, but this is considerably less performant (concurrent in/out transfer on your desktop machine)

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Frequent Contributor
Posts: 79

Re: Reading data from a MS SQL database; which connection type to use?

Hi, implicitly I think my question is answered like this: Yes, there is a difference between the way SAS JMP and EG61 connects to a SQL database. Whereas JMP connects via an ODBC mechanism which must come with the complete JMP installation, EG needs extra modules that must be licensed.

Regards Poul

Valued Guide
Posts: 3,206

Re: Reading data from a MS SQL database; which connection type to use?

run a proc setinit;run; with Eguide and you will the SAS server and modules that are licensed. Licensing with Eguide is server based with the SAS installation on that server.

Eg has no additional modules, it is just a very nice terminal approach.  The SAS DI/BI Server installation can possible need additional licenses Still there is is the option to use (limited read-only) ODBC resources defined on your desktop that will upload data for further processing.  http://www2.sas.com/proceedings/forum2007/058-2007.pdf

Open in the processflow open menu's (alt mouse button). Below choice open there is a list.  Data - program --- Exchange , OleDB, ODBC Other

As it doing an upload it warns you can better user SAS/ACess when having above 10.000 rows locally

---->-- ja karman --<-----
Community Manager
Posts: 2,693

Re: Reading data from a MS SQL database; which connection type to use?

You can use File->Open->OLE DB from SAS Enterprise Guide and connect to SQL Server -- but that's not the most efficient method.  I've outlined some of the approach in this thread (about Oracle data):

Chris

Frequent Contributor
Posts: 79

Re: Reading data from a MS SQL database; which connection type to use?

HI again, I agree that I can connect to the database as such. In JMP I can write some quering statements that are performed on the table. However in EG I just import the whole of the file. Seems a little overdoint it when what I want do do is exactly to query the database using some sql statements.

Regards

Poul

Community Manager
Posts: 2,693

Re: Reading data from a MS SQL database; which connection type to use?

I agree -- it's often overkill to import the entire table, which is how File->Open->OLE DB works.  SAS/ACCESS to OLE DB or ODBC is a better option if you have the modules available.  There is a short primer on this concept here:

26178 - Efficient Data Access using SAS Enterprise Guide

Chris

Ask a Question
Discussion stats
  • 10 replies
  • 676 views
  • 0 likes
  • 4 in conversation