Getting started with pass-through queries

[80sMetalForever]

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.

[DBailey]

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]

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]

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]

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]

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]

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

[Anotherdream]

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]

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]

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]

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.

[80sMetalForever]

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]

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]

SYSSCPL = AIX

[TomKari]

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