topic Re: Getting started with pass-through queries in SAS Enterprise Guide

Getting started with pass-through queries

80sMetalForever — Mon, 16 Sep 2013 18:35:17 GMT

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.

Re: Getting started with pass-through queries

DBailey — Mon, 16 Sep 2013 19:13:28 GMT

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;

Re: Getting started with pass-through queries

80sMetalForever — Mon, 16 Sep 2013 19:39:29 GMT

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?

Re: Getting started with pass-through queries

SASKiwi — Mon, 16 Sep 2013 19:57:36 GMT

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.

Re: Getting started with pass-through queries

80sMetalForever — Mon, 16 Sep 2013 20:03:45 GMT

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

Re: Getting started with pass-through queries

DBailey — Mon, 16 Sep 2013 20:09:03 GMT

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

Re: Getting started with pass-through queries

80sMetalForever — Mon, 16 Sep 2013 20:24:19 GMT

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

Re: Getting started with pass-through queries

Anotherdream — Mon, 16 Sep 2013 21:08:28 GMT

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.

Re: Getting started with pass-through queries

SASKiwi — Mon, 16 Sep 2013 22:34:53 GMT

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)

Re: Getting started with pass-through queries

80sMetalForever — Tue, 17 Sep 2013 12:52:28 GMT

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.

Re: Getting started with pass-through queries

jakarman — Tue, 17 Sep 2013 13:40:18 GMT

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.

Re: Getting started with pass-through queries

80sMetalForever — Tue, 17 Sep 2013 14:28:30 GMT

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.

Re: Getting started with pass-through queries

OS2Rules — Tue, 17 Sep 2013 16:18:56 GMT

Hi:

I just started with SQL Server as well and I managed to get something running.

First - I set up the ODBC connection called it "Dbase" using AD authentication. Then I use this code:

proc sql;

connect TO ODBC (dsn=Dbase);

execute (execute my_stored_process 'my_parameter' ) by ODBC;

disconnect from ODBC;

quit;

seems to work fine. I presume that the "execute" can be change to any other SQL statements easily enough.

Re: Getting started with pass-through queries

DBailey — Tue, 17 Sep 2013 18:15:44 GMT

You could also do something like this:

create table work.have as select * from connection to ODBC (

select * from.... or really any database specific statement could go here

);

Re: Getting started with pass-through queries

jakarman — Tue, 17 Sep 2013 18:21:08 GMT

Let 80sMetalForever first figure out whether he is using SAS on Unix(AIX) or at Windows.
And having checked/verified it is installed correctly for him.

Re: Getting started with pass-through queries

TomKari — Tue, 17 Sep 2013 18:53:50 GMT

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

Re: Getting started with pass-through queries

80sMetalForever — Tue, 17 Sep 2013 19:53:38 GMT

SYSSCPL = AIX

Re: Getting started with pass-through queries

TomKari — Wed, 18 Sep 2013 01:16:30 GMT

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

Re: Getting started with pass-through queries

80sMetalForever — Wed, 18 Sep 2013 17:48:35 GMT

Thank you, I followed up with thread started in your link.