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
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?
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
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
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?
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!
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?
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
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.
*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.
Also you could try to load those tables with Enterprise Guide: File -> Open -> ODBC...
This bypasses SAS server.
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.
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
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 .
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.