Error while connecting SAS with sql server thhrough ODBC

Reply
Occasional Contributor
Posts: 5

Error while connecting SAS with sql server thhrough ODBC

Hi All,

I am getting the below error while connecting sas with sql server through ODBC.

1. LIBNAME SQL ODBC DSN=’sqlsrv;

ODBC1.JPG

2.

proc sql;

connect to odbc (dsn=’sqlsrv’);

quit ;

ODBC.JPG

Kindly help me on this to correct it. Do i need to install ODBC driver in my system or there is some other problem...........?

Regards,

Ratan

Super User
Posts: 6,928

Re: Error while connecting SAS with sql server thhrough ODBC

You most probably have not installed/licensed SAS/ACCESS to ODBC, or the installation has failed. If you enter "The ODBC engine cannot be found" in google, you will find several threads dealing with the same error condition, maybe one of those is helpful for you.

The ODBC driver for SAS is a freely installable Windows driver that allows other SW (Office etc) access to any SAS data source (workspace server, SAS/SHARE, ..) via ODBC, so that is not a solution to your problem.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Occasional Contributor
Posts: 5

Re: Error while connecting SAS with sql server thhrough ODBC

Hi Kurt,

Please find below products linstalled in my system. I am not able to see SAS/ACCESS to ODBC here.but SAS/ACCESS Interface to PC files is available. Please let me know if mi need to reinstall it.

Capture.JPG

Regards,

Ratan

Super User
Posts: 6,928

Re: Error while connecting SAS with sql server thhrough ODBC

ACCESS to PC files is support for certain file formats typical on PCs, like dBase, MS Office and so on.

If you want to connect (from SAS) to a data source using ODBC, you need ACCESS to ODBC.

What is it that you want to connect to, anyway?

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Occasional Contributor
Posts: 5

Re: Error while connecting SAS with sql server thhrough ODBC

Hi Kurt,

I have data in sql Server database and i want to fetch that data by using SAS for some analysis. So when i am connecting through ODBC, i am not able to do this.

I have first created an ODBC connection with Sql server suppose SE1 and i am using this SE1 in my SAS code. but its not working.

Could you please provide ne some other alternatives to do it. Could you please provide me the code OLEDB so that i can try with that code also.

Regards,

Ratan

Super User
Posts: 6,928

Re: Error while connecting SAS with sql server thhrough ODBC

You either connect to the sql database directly (using the appropriate ACCESS to ... module) or via ODBC, which requires ACCESS to ODBC. Either way, you need to install and license the respective ACCESS module.

If you want to avoid this, you best set up an export procedure in the database and import the resulting file into SAS (once you have a valid structure, almost anything is possible in a data step).

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Occasional Contributor
Posts: 5

Re: Error while connecting SAS with sql server thhrough ODBC

Hi Kurt,

Please let me help in understanding what is difference between SAS/ACCESS Interface to ODBC and SAS/ACCESS Interface to PC Files

Regards,

Ratan

Super User
Posts: 6,928

Re: Error while connecting SAS with sql server thhrough ODBC

Super User
Super User
Posts: 7,392

Re: Error while connecting SAS with sql server thhrough ODBC

Well, I can barely read those screenshots, but it is telling you that you lack the required driver.  A brief search on Google highlighted several articles on this topic for example: http://support.sas.com/techsup/technote/ts765.pdf

I would check with your database team on exactly what needs to be setup where, what your username/password would be etc.  You would also need SAS/Access I believe.

Occasional Contributor
Posts: 5

Re: Error while connecting SAS with sql server thhrough ODBC

Hi RW,

I am not able to find the path mentioned in the pdf

!SASROOT\Access\Sasexe

Could you please help me on this.

Regards,

Ratan

Super User
Super User
Posts: 7,392

Re: Error while connecting SAS with sql server thhrough ODBC

!SASROOT references where SAS is installed on your machine/server etc.  For example a local install might be in:

c:\sas institute\sas\9.3...

N/A
Posts: 1

Re: Error while connecting SAS with sql server thhrough ODBC

I have share some suggestion about solution of your problem, try any third party SQL recovery tool to repair and recover all over SQL database with all over attributes. Visit here of this tool: http://www.sqlrepair.org

N/A
Posts: 1

Re: Error while connecting SAS with sql server thhrough ODBC

You can run the DBCC CHECKDB utility to repair the damaged and corrupt SQL database files. However, the DBCC CHECKDB is not always able to repair the damaged and corrupt SQL database files.

In case you are not able to repair the damaged and corrupt SQL database files using any of the above mentioned methods, then you can use a professional and proficient third party Sql database repair software

To know more about this tool and to download its free  trail demo version, visit: http://www.filesrecoverytool.com/sql-database-repair.html

New User
Posts: 1

Re: Error while connecting SAS with sql server thhrough ODBC

Try more reliable and advance SQL Database Recovery software. This software easy to recover all SQL data including tables, stored procedure, functions, views, rules, triggers and associated Primary Key, Unique keys, etc. Read more information and free download click here http://utilitysoftwarereview.com/sqldatabaserepair.html

Ask a Question
Discussion stats
  • 13 replies
  • 3785 views
  • 6 likes
  • 6 in conversation