topic Re: Adding MS SQL database as a data sourcfe to EG in SAS Enterprise Guide

Adding MS SQL database as a data sourcfe to EG

JakubDeka — Sun, 11 Jan 2009 16:27:10 GMT

I am a new user of SAS and EG.

I have a question. What do I have to do to be able to extract data from my company's MS SQL server database? How would I do this?

I presume that I need to add a remote server on which the database is hosted and then add a library (connecting through ODBC) which is going to reference my database.

Am I correct?

Thanks for help. Message was edited by: Jakub Deka

Re: Adding MS SQL database as a data sourcfe to EG

ChrisHemedinger — Mon, 12 Jan 2009 15:50:29 GMT

Jakub,

First choice: use SAS/ACCESS to OLE DB to connect to the SQL Server data as a SAS library.

Second choice: you can use File->Open->OLE DB to connect EG to your SQL Server, but it's inefficient and you'll notice it's slow for large data. That's because the process moves your SQL Server data into SAS via the EG client (two hops instead of one).

More here:
http://support.sas.com/kb/26/178.html

Chris

Re: Adding MS SQL database as a data sourcfe to EG

JakubDeka — Mon, 12 Jan 2009 17:16:22 GMT

Thanks Chris. I will look at this document now.

Could you tell me what do I have to do to set this up?

I presume that I have to add a server on which my database is hosted and then add the library which will match my database. I am very confused and I am not sure what I have to do. Could you help?

What are the requirements for this to work? Do I need SAS running on that server? I have licence for SAS/ACCESS to ODBC. Is this the right product? Message was edited by: Jakub Deka

Re: Adding MS SQL database as a data sourcfe to EG

LinusH — Mon, 12 Jan 2009 22:25:29 GMT

To give you some advice we need to know your setup in more detail. Does your license for ACCES to ODBC apply to your SAS server license? Is your SAS server a metadata/workspace server implementation?

I don't think there any big difference in performance between OLE DB and ODBC. And the best solution is probably to have your connection to SQL Server on your SAS server if any processing spills over to SAS, or you want to store some large intermediate results.

/Linus

Re: Adding MS SQL database as a data sourcfe to EG

JakubDeka — Tue, 13 Jan 2009 09:09:15 GMT

Hi Linus,

OK. At the moment I have SAS including SAS/ACCESS to ODBC installed on my laptop. Nothing was installed on the server so far.

Server hosting the database is running on Windows.

What I am hoping to achieve is to be able to pass queries directly to the database using pass through.

Thanks for your help. If you need any more details I am more than happy to provide them to you.

Re: Adding MS SQL database as a data sourcfe to EG

LinusH — Tue, 13 Jan 2009 11:49:39 GMT

You probably already have your local SAS defined as an EG meta server.
You need the SQL Server ODBC driver locally (most probably installed by default). In the ODBC manager you have to define your data source using this driver.
In EG, I think you just have to define a Library using a Wizard under Tools. For details se EG documentation.

Linus

Re: Adding MS SQL database as a data sourcfe to EG

JakubDeka — Tue, 13 Jan 2009 13:03:42 GMT

I have defined the server (which works) in the Windows. Should I define it as User DNS or System DNS?

I tried adding the library to the EG using tools -> Assign Library.

Once I do this I am getting following error. Any idea what I am doing wrong?

ERROR: CLI error trying to establish connection: [Microsoft][ODBC Driver Manager] Data source
name not found and no default driver specified
ERROR: Error in the LIBNAME statement.

Re: Adding MS SQL database as a data sourcfe to EG

ChrisHemedinger — Sat, 17 Jan 2009 20:41:10 GMT

I suggest sending a note to SAS tech support with the details; they are pretty well versed in the database connectivity issues.

Chris