BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
JohnSimmons
Calcite | Level 5

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

1 ACCEPTED SOLUTION

Accepted Solutions
SASKiwi
PROC Star

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?

View solution in original post

12 REPLIES 12
Reeza
Super User

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

JohnSimmons
Calcite | Level 5

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

SASKiwi
PROC Star

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?

JohnSimmons
Calcite | Level 5

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!

Reeza
Super User

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?

JohnSimmons
Calcite | Level 5

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

Reeza
Super User

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.

JohnSimmons
Calcite | Level 5

*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.

gergely_batho
SAS Employee

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

This bypasses SAS server.

Kurt_Bremser
Super User

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.

JohnSimmons
Calcite | Level 5

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

SASKiwi
PROC Star

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 Smiley Happy

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 12 replies
  • 2555 views
  • 9 likes
  • 5 in conversation