BookmarkSubscribeRSS Feed
80sMetalForever
Calcite | Level 5

Newish to SAS and trying to figure out pass-through query setup/syntax in Enterprise Guide, specifically to SQL Server to begin with. I've found numerous examples of how to write the query but it seems no matter what I do I get "ERROR: The SQLSERVR engine cannot be found...." Is there maybe something I need to setup first that I'm missing before I can get this to run?

proc sql exec;

    connect to sqlservr as Order (server=srvtest database=dbtest);

        create table Orders_Tot as select *

from connection to Order(

SELECT * FROM tablename);

disconnect from Order;

quit;

Not sure if it helps but I'm running SQL Server "locally" on my own machine and SAS is installed, I believe, in the AIX environment.

18 REPLIES 18
DBailey
Lapis Lazuli | Level 10

keyword is wrong.  should be sqlsvr presuming you've purchased the sas connector to sql server.  If not, you'll probably need to use the odbc connector if you're running locally.

proc sql noprint;

connect to sqlsvr as CTWorks (datasrc="XXXX" user = "XXX" password = "XXX" insert_sql = yes insertbuff = 10000);

disconnect from CTWorks;

quit;

80sMetalForever
Calcite | Level 5

datasrc="XXXX" would be the server name?


So are you saying at a mininum ODBC should work fine? I changed it to ODBC and received:

ERROR: The ODBC engine cannot be found.

ERROR: A Connection to the odbc DBMS is not currently supported, or is not installed at your site.

Maybe I need to work with our Admin to install?

SASKiwi
PROC Star

What SAS/ACCESS product do you have to work with SQL Server? Possible choices are SAS/ACCESS to SQL Server or SAS/ACCESS to ODBC. If you don't know run PROC SETINIT noalias; run; to confirm what your licence contains.

The setup for each of these products is different.

80sMetalForever
Calcite | Level 5

Results as follows:

Expiration:   31DEC2013.

Grace Period:  45 days (ending 14FEB2014).

Warning Period: 45 days (ending 31MAR2014).

System birthday:   22FEB2013.

Operating System:   AIX 64  .

Product expiration dates:

---Base Product                                                            31DEC2013 

---SAS/STAT                                                                31DEC2013 

---SAS/GRAPH                                                               31DEC2013 

---SAS/QC                                                                  31DEC2013 

---SAS/CONNECT                                                             31DEC2013 

---SAS/GIS                                                                 31DEC2013 

---SAS OLAP Server                                                         31DEC2013 

---SAS Enterprise Miner                                                    31DEC2013 

---MDDB Server common products                                             31DEC2013 

---SAS Integration Technologies                                            31DEC2013 

---Enterprise Miner Server                                                 31DEC2013 

---Enterprise Miner Thin Client                                            31DEC2013 

---SAS Text Miner                                                          31DEC2013 

---Unused OLAP Slot                                                        31DEC2013 

---SAS/ACCESS Interface to DB2                                             31DEC2013 

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

---SAS/ACCESS Interface to Teradata                                        31DEC2013 

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

---SAS Metadata Bridges for General Industry Standards                     31DEC2013 

---SAS Workspace Server for Local Access                                   31DEC2013 

---SAS Workspace Server for Enterprise Access                              31DEC2013 

---SAS Table Server                                                        31DEC2013

DBailey
Lapis Lazuli | Level 10

I think your environment is not configured correctly.  Here are some links to help:

http://support.sas.com/techsup/technote/ts765.pdf

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

80sMetalForever
Calcite | Level 5

Thank you, right off the bat I don't see either .dll installed so I'll check with our Admin before proceeding

Anotherdream
Quartz | Level 8

If you want you can always use the ODBC driver, however you have to SET UP an odbc connection to your database through the Datasources (ODBC) system options.

I can walk you through this if this is the route you would like to take, its actually significantly easier than most would expect.

The reason you got the error "

ERROR: The ODBC engine cannot be found.

ERROR: A Connection to the odbc DBMS is not currently supported, or is not installed at your site.

"

Is because the odbc driver was not set up to this server. YOu have to tell the odbc "hey, here is my server and this is the name I want you to be when i'm callin the connection to my server", very similar to how you call a library name with a libname MYLIB in base sas.

80sMetalForever
Calcite | Level 5

I do have an ODBC connection setup already for this server using the "SQL Server" driver. I actually have it setup under "User DSN" and one under "System DSN" just to cover the bases and the TEST CONNECT worked fine.

SASKiwi
PROC Star

Can you confirm what version of SAS as well?

Here is a reference to the post-installation required for SAS/ACCESS to Microsoft SQL Server (as confirmed from your SETINIT) for SAS 9.2 under AIX:

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

DBailey's technote reference is for Windows so I'd be a bit careful with that.

Anotherdream's instructions also apply to Windows. Under AIX configuration of ODBC sources is done in an ODBC.INI file (see link above)

jakarman
Barite | Level 11

The user DSN and system DSN are Windows approaches. Dll-files are Windows based.
As you are telling to run (see setinit) on AIX something is mixed/confused.

Where did you get that setinit listing? What tool was used? what version of sas?

If you are really running AIX, follow the config manual as saskiwi has given.

---->-- ja karman --<-----
80sMetalForever
Calcite | Level 5

At this point I'm just gonna work with our Admins as this is above my head and I'm sure it's very hard for y'all to help troubleshoot without being about to see the setup. I'll post back if/when a resolution is reached. Thx all for your input.

TomKari
Onyx | Level 15

Try running this program on the same server where you're submitting your pass-through code:

data _null_;

file log;

put "SYSSCPL = &SYSSCPL.";

RUN;

What this should do is write the server's O/S to the log, like in the following:

20         RUN;

SYSSCPL = X64_7PRO
NOTE: DATA statement used (Total process time):

where in my case I'm running Windows X64.

Post your results in this thread, to let us know whether your server is running Windows or a Unix variant. Support for SQL Server is very different between the two.

Tom

80sMetalForever
Calcite | Level 5

SYSSCPL = AIX

TomKari
Onyx | Level 15

In that case, you're going to need help from your system administrator. There's no way to access SQL Server databases from Unix environments without some additional software.

Here's a forum thread that gives some references:

https://communities.sas.com/thread/48809

Tom

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
  • 18 replies
  • 4478 views
  • 0 likes
  • 7 in conversation