topic Re: SAS EG, AIX, Windows, and SQL Server - oh my! in SAS Enterprise Guide

SAS EG, AIX, Windows, and SQL Server - oh my!

JohnSimmons — Wed, 11 Feb 2015 21:42:02 GMT

Good afternoon all,

I have been tasked with something I cannot figure out and I know it will be exceedingly simple, but it is escaping me.

We have SAS EG 5.1 running SAS 9.3. It is located on an AIX machine and we are running windows.

What I am trying to do is connect to a SQL Server database without doing any kind of major installs, downloads, or changes. I don't have a great deal of access as IT locks our machines down pretty well.

So the first example I find from everyone out there is to use LIBNAME mylib ODBC yadda.

ERROR: The ODBC engine cannot be found.

ERROR: Error in the LIBNAME statement.

So then I ran a PROC SETINIT to see what I have available. ODBC, the easiest one, is unfortunately not on the list.

---Base SAS Software 31DEC2014

---SAS/STAT 31DEC2014

---SAS/GRAPH 31DEC2014

---SAS/ETS 31DEC2014

---SAS/QC 31DEC2014

---SAS/CONNECT 31DEC2014

---SAS OLAP Server 31DEC2014

---SAS Enterprise Miner 31DEC2014

---MDDB Server common products 31DEC2014

---SAS Integration Technologies 31DEC2014

---SAS Enterprise Miner Server 31DEC2014

---SAS Enterprise Miner Client 31DEC2014

---Unused OLAP Slot 31DEC2014

---SAS Enterprise Guide 31DEC2014

---SAS/ACCESS Interface to DB2 31DEC2014

---SAS/ACCESS Interface to Oracle 31DEC2014

---SAS/ACCESS Interface to PC Files 31DEC2014

---SAS/ACCESS Interface to Teradata 31DEC2014

---SAS/ACCESS Interface to Microsoft SQL Server 31DEC2014

---SAS Workspace Server for Local Access 31DEC2014

---SAS Workspace Server for Enterprise Access 31DEC2014

---SAS Table Server 31DEC2014

---DataFlux Trans DB Driver 31DEC2014

---SAS Framework Data Server 31DEC2014

---SAS Add-in for Microsoft Excel 31DEC2014

---SAS Add-in for Microsoft Outlook 31DEC2014

---SAS Add-in for Microsoft PowerPoint 31DEC2014

My obvious jump was to try and use the interface with Microsoft SQL Server but I have been met with nothing but failure. I cannot articulate what I'm trying to do well enough into a search engine to get any results other than LIBNAME ODBC.

Can anyone suggest how I can access tables in a SQL server? I'm capable of logging into it and reading data on those tables from Microsoft SQL Server 2008 R2 Management Studio.

I'm also capable of setting up an ODBC on my machine that successfully connects to the database.

I took on a project for management that is past due and under high pressure, so any help would result in my eternal gratitude.

Thank you for reading and any help you can provide.

John

Re: SAS EG, AIX, Windows, and SQL Server - oh my!

Reeza — Wed, 11 Feb 2015 21:48:06 GMT

You don't have a license for ODBC but you do for SQL server.

Assuming your DSN was called: sqlsvr, as set up in your Windows ODBC connection.

From the docs use SQLSVR for SQL Server.

libname mydblib sqlsvr

noprompt="uid=testuser;

pwd=testpass;

dsn=sqlservr;"

stringdates=yes;

SAS/ACCESS(R) 9.2 for Relational Databases: Reference, Fourth Edition

Re: SAS EG, AIX, Windows, and SQL Server - oh my!

JohnSimmons — Wed, 11 Feb 2015 22:11:28 GMT

Thank you so much for responding, I have tried that one as well (I didn't post that in my original one, though)

I tried it again with your suggestion just to be sure and maybe you can assist with this error:

14 GOPTIONS ACCESSIBLE;

15 libname mydblib sqlsvr

16 noprompt=XXXXXXXXXXXXXXXXXXXXXXXXXXXX

17 XXXXXXXXXXXXXXX

18 XXXXXXXXXXXXXXXXX

19 stringdates=yes;

ERROR: CLI error trying to establish connection: [DataDirect][ODBC lib] Data source name not found and no default driver specified

ERROR: Error in the LIBNAME statement.

I have quadruple checked the password and user ID entered, as well as the spelling (including capitalization) of the DSN.

I have also rechecked to make sure that the ODBC test connection succeeded.

Thank you again, if you have any suggestions on what I might be doing wrong I would be very grateful.

John

Re: SAS EG, AIX, Windows, and SQL Server - oh my!

SASKiwi — Wed, 11 Feb 2015 23:03:50 GMT

Check out the configuration guide instructions for SAS/ACCESS to SQL Server 9.3:

http://support.sas.com/documentation/installcenter/en/ikfdtnunxcg/64205/PDF/default/config.pdf

Have these all been done and configured correctly including defining your data sources in the ODBC.INI file?

Re: SAS EG, AIX, Windows, and SQL Server - oh my!

Reeza — Wed, 11 Feb 2015 23:34:51 GMT

It looks like you're using EG? Can you run it locally or can you only submit code through the Server?

Have you tried it through Base SAS?

Re: SAS EG, AIX, Windows, and SQL Server - oh my!

JohnSimmons — Thu, 12 Feb 2015 00:19:58 GMT

Hi SASKiwi, I'm looking into this but unfortunately I am not one of those that set up SAS EG at our company, nor do I have access to the manager and such.

I'm trying to track down how to get the directory to see what is in the ODBC.INI file and maybe updating it there.

I'll keep updated if this ends up solving it for me. At this point I believe this would be something the admin would have control over and not me.

Thanks!

Re: SAS EG, AIX, Windows, and SQL Server - oh my!

JohnSimmons — Thu, 12 Feb 2015 00:21:10 GMT

Hi Reeza, unfortunately we cannot use base SAS, we only have SAS EG 5.1.

We don't have the licenses to run it locally on our machines. (Unless I'm misunderstanding your question)

Thanks again for your continued assistance!

John

Re: SAS EG, AIX, Windows, and SQL Server - oh my!

Reeza — Thu, 12 Feb 2015 00:41:15 GMT

This means that your SAS server needs to be able to see the SQL server. So regardless of what you do on your desktop your out of luck. Your DBA will have to set it up on the SAS Server.

A 'stupid' workaround if your trying to only access a few tables is connect to them via Access or Excel and then import those into EG.

Re: SAS EG, AIX, Windows, and SQL Server - oh my!

JohnSimmons — Thu, 12 Feb 2015 00:56:22 GMT

*Makes a cross with fingers at the suggestion of access*

No, I'm kidding. I think you're right. I have a request for help out to the SAS DBAs at our company but it is a very large company and I am one little person.

It is a good suggestion at least for an interim solution. I was very much concerned that the ODBC wouldn't work from my machine since it was running off the AIX machine. It has created a number of challenges.

I'll continue to hold out hope someone might find another work around and will close this out when I get it resolved, one way or another.

Thanks again for your help, Reeza.

Re: SAS EG, AIX, Windows, and SQL Server - oh my!

gergely_batho — Thu, 12 Feb 2015 04:54:54 GMT

Also you could try to load those tables with Enterprise Guide: File -> Open -> ODBC...

This bypasses SAS server.

Re: SAS EG, AIX, Windows, and SQL Server - oh my!

Kurt_Bremser — Thu, 12 Feb 2015 07:47:42 GMT

As Reeza has already suggested, you need a working install of the SQL Server client on your AIX box, because SAS/ACCESS uses that.

Be aware that this may be quote a hassle, I tried to do that with DB/2 one time (DB/2 server is located on SLES, SAS runs on AIX) and simply gave up.

The simplest way (IMO) of getting database data into SAS is to unload it into a flat file, transfer that where SAS can see it, and read it with a data step. You never have to fiddle around when the DB gets updated, unload stays unload, and SFTP stays SFTP.

Re: SAS EG, AIX, Windows, and SQL Server - oh my!

JohnSimmons — Thu, 12 Feb 2015 19:02:59 GMT

It is a good suggestion, but as the tables in the SQL Server I'm trying to connect to are enormous, (Hundreds of columns and millions of records). For the one report, it is acceptable since I am pulling out maybe around 4000 rows at a time, however once people realize what is being done, the appetite is going to grow to get increasing data out of this system, I have to plan for the future.

SASKiwi provided a suggestion to review the implimentation documentation. While I don't have access to do anything about it, it gave me the ammunition to ask to have our ODBC.INI file updated for us. While I am still pending the final decision, there was a lot of helpful interim advice so I appreciate it from everyone.

The final answer is that since our EG is housed on an AIX machine, it cannot directly interface with the local ODBC I have set up on my machine. I have to get the SAS DBAs at our company to change the ODBC.INI file to include our connection information for LIBNAME libref sqlsvr to work in our setup.

Some amazingly helpful interim responses were given as well by Reeza, Gergely Batho, and KurtBremser, all involving using another product to put a file where it can be accessed by SAS.

Thank you all for your help. I'm going to mark this question as closed. You all have been an incredible assistance!

I love this site.

John

Re: SAS EG, AIX, Windows, and SQL Server - oh my!

SASKiwi — Thu, 12 Feb 2015 22:59:26 GMT

Glad to be of service. I hate to say this but in the Windows world things are quite a bit easier as you can define all of your ODBC settings in SAS.

Best practice is to define all this stuff for all SAS users in SAS metadata via SAS Management Console. It works a treat at our site anyway .