topic Re: I am not able to connect to SQL server? in Microsoft Integration with SAS

I am not able to connect to SQL server?

Inp — Tue, 23 Feb 2010 20:42:26 GMT

Hi Dear,
I am trying to connect to Sql server , the coding and log are below. According to the below log, should I have to installed ODBC driver for SQL server. It seems, I am not able to connect to the server. Password and Userid works when I connect from sql server to sqlserver. For securty reason, I have replace them.If I have to install, can any one please let me know where I can get the odbc driver and how to do it. Thanks so much in advance.

1 proc sql noprint;
2 connect to oledb as idr
3 (init="Provider=SQLOLEDB.1;
4 Password=password;
5 Persist Security Info=True;
6 User ID=userid;
7 Initial Catalog=Anguilla;
8 Data Source=xxxxxx"
9 );
ERROR: The OLEDB engine cannot be found.
ERROR: A Connection to the oledb DBMS is not currently supported, or is not installed at your site.
10
11 create table work.sample as
12 select
13 *
14 from connection to idr
15 (
16 select *
17 from mytabel
18 where period = '2009-10-31'
19 )
20 ;
ERROR: The IDR engine cannot be found.
ERROR: A Connection to the idr DBMS is not currently supported, or is not installed at your site.
21 %PUT &SQLXMSG;
ERROR: A Connection to the idr DBMS is not currently supported, or is not installed at your site.
22
23 disconnect from idr;
ERROR: Connection to the idr DBMS does not exist.
24 quit;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
real time 0.25 seconds
cpu time 0.01 seconds

25 run;

Re: I am not able to connect to SQL server?

Cynthia_sas — Wed, 24 Feb 2010 01:27:00 GMT

Hi:
If you have done the required setup, as explained in this paper (on page 1):
http://support.sas.com/techsup/technote/ts700.pdf

and if you are using the correct syntax, as shown in Example 3 on page 3, then, if connection is still not working, you might want to open a track with Tech Support for more help.

cynthia

Re: I am not able to connect to SQL server?

Inp — Wed, 24 Feb 2010 21:20:48 GMT

Hi Cynthia
First of all thanks for your reply.
My SAS have the below SAS components. Is the component -SAS/ACCESS Interface to ODBC is good enough to communicate with OLE DB or should I have install -SAS/ACCESS Interface to OLE DB with my SAS . If I want to install , How do I get it. Is it free or Do I have to pay for it. Again thanks so much for your replay.

---STATPLUS 31OCT2010
---ENTERPRISE 31OCT2010
---SAS/ETS 31OCT2010
---SAS/OR 31OCT2010
---MDDB Server common products 31OCT2010
---OR OPT 31OCT2010
---OR PRS 31OCT2010
---OR IVS 31OCT2010
---OR LSO 31OCT2010
---SAS/ACCESS Interface to ODBC 31OCT2010

Re: I am not able to connect to SQL server?

Cynthia_sas — Wed, 24 Feb 2010 23:04:39 GMT

Hi:
As it says on page 1 of the document, you need SAS/ACCESS to OLEDB (not ODBC). Quote directly from page 1:

"Before you begin using SAS/ACCESS Interface to OLE DB there are system requirements that will need to be met prior to its use.
They include the following:
Products Required:
• Base SAS software
• SAS/ACCESS Interface to OLE DB

DBMS Products Required:
• An OLE DB provider needs to be installed on the same machine that SAS will be running on. The OLE DB
provider is obtained from a third party (other than SAS) vendor.
• Any additional DBMS client software may need to be installed and configured on the same machine as the OLE DB provider. Many OLE DB providers communicate with the DBMS server using the DBMS client. Once the client software is installed and configured to communicate with the server in question, the OLE DB provider should be able to also communicate with the DBMS server.
• Microsoft Data Access Components (MDAC), Version 2.7 or higher
Note: The SAS System will automatically install Microsoft Data Access Components (MDAC) when you install SAS/ACCESS Interface to OLE DB software.

Once the above software has been installed and any additional client software has been installed and configured, SAS/ACCESS Interface to OLE DB is ready to be used."

In order to get SAS/ACCESS to OLE DB, you should contact your internal SAS software representative or SAS Sales Rep. Pricing depends on the site license that your company has. It's possible that your company already has a SAS/ACCESS to OLE DB license and you have to ask for it to be installed on your system. What you show may only be what's physically installed, not what has actually been licensed.

If your SQL server is ODBC-compliant and you wish to access it via ODBC, then you will need to follow other directions for ODBC access to your database.
http://support.sas.com/documentation/cdl/en/acreldb/63023/HTML/default/a001353892.htm
http://support.sas.com/documentation/cdl/en/acreldb/63023/HTML/default/a001355231.htm

ODBC and OLE-DB are two different methods of connecting to various data sources and each method has a separate SAS/ACCESS product (just as Oracle and DB2 are two different data bases, each with their own SAS/ACCESS product):
Definition of ODBC:
http://en.wikipedia.org/wiki/Open_Database_Connectivity

Definition of OLE-DB:
http://en.wikipedia.org/wiki/OLE-DB

cynthia

Re: I am not able to connect to SQL server?

ksachar — Thu, 07 Jan 2016 18:22:33 GMT

This document is not available. Do you have other information you can share?

Re: I am not able to connect to SQL server?

LinusH — Tue, 02 Feb 2016 18:23:24 GMT

If you already have the license for ODBC, why can't you use that instead?