Help using Base SAS procedures

SQL pass through facility

Reply
Occasional Contributor
Posts: 15

SQL pass through facility

I want to know for using pass through facility of SQL in SAS is there need to install some service. This question arised because of the following ERROR in the log

ERROR: The SQLSERVE engine cannot be found.

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

Super User
Posts: 3,260

Re: SQL pass through facility

In general you need the appropriate SAS/ACCESS product installed and licensed to be able to connect to an external database regardless of the method, LIBNAME, PASSTHRU, SQL etc.

You can confirm what SAS products are licensed by running PROC SETINIT.

What database are you trying to connect to? Your errors suggest SQL Server. In which case you will need SAS/ACCESS to SQL Server, or SAS/ACCESS to ODBC. Your choice may depend on what operating system your SAS server runs on. 

Occasional Contributor
Posts: 15

Re: SQL pass through facility

Thanks for the reply

When i submit proc setinit i got the following:-

---SAS/ACCESS Interface to PC Files                                                                
---SAS/ACCESS Interface to ODBC                                                                    

---SAS/ACCESS Interface to MySQL      

So i have question whether i am able to use PROC SQL pass through or not                                                           

Trusted Advisor
Posts: 2,116

Re: SQL pass through facility

With those products, you can use Pass-thru.  With ODBC, you will need the appropriate drivers for your target database installed.  On Windows, you can technically do it yourself, but you may need your IT support to provide the proper parameterization.

Super User
Posts: 3,260

Re: SQL pass through facility

As Doc says you have the right products. Please post the code that gave you the errors you put in your first post. Also which database are you trying to connect to?

My guess is you are trying to use the SQL Server engine when you should be using ODBC: libname test ODBC dsn=xxxx etc.

Occasional Contributor
Posts: 15

Re: SQL pass through facility

Thanks for the reply

I am connecting ot SQL server using pass through facility

Can you provide me the code for this so that it will be error free

Thanks in advance

Occasional Contributor
Posts: 15

Re: SQL pass through facility

I submitted the following code

Proc sql;

Connect to odbc(datasrc=tribal user=** password=****);

Create table work.account as

Select * from connection to odbc

(Select *

   From tribal.uu);

Disconnect from odbc;

Quit;

ERROR: CLI error trying to establish connection: [Microsoft][ODBC Driver Manager] The specified DSN contains an

       architecture mismatch between the Driver and Application

ERROR: CLI error trying to establish connection: [Microsoft][ODBC Driver Manager] Data source name not found and no

       default driver specified

ERROR: Connection to the odbc DBMS does not exist

Super User
Posts: 7,863

Re: SQL pass through facility

You are encountering a bitness problem. Most probably the ODBC driver is still 32bit, while you are accessing it with 64 bit SW from SAS on a 64bit system.

eg

http://stackoverflow.com/questions/8895823/the-specified-dsn-contains-an-architecture-mismatch-betwe...

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Ask a Question
Discussion stats
  • 7 replies
  • 878 views
  • 0 likes
  • 4 in conversation