×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Failure to establish ODBC connection between SAS and SQL Server

Failure to establish ODBC connection between SAS and SQL Server

Failure to establish ODBC connection between SAS and SQL Server

(OP)
Hi experts!
I am trying to establish ODBC connection between SAS 9.2  and SQL Server 2005 through Control Panel->Administration Tools->Data Sources (ODBC).
I was able to do it once in the past and it worked!!!!
30   libname mylibref ODBC
31   NOPROMPT=XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX;
NOTE: Libref MYLIBREF was successfully assigned as follows:
      Engine:        ODBC
      Physical Name: IR_SAS

******************************************************************Since that time I had lost my ODBC connection and assumed (although was not absolutely sure)that it happened after my PC had been replaced; Anyway I started the process over to re-establish it. However, tried as I might, it did not work anymore. I cannot find what is my mistake as system message in the process of re-establishing looked all right:

"Microsoft SQL Native Client Version 09.00.5000

Data Source Name: IR_SAS.dsn
Data Source Description: IR_SAS_SQL_SERVER
Server: REPORTP01
Use Integrated Security: Yes
Database: DW
Language: (Default)
Data Encryption: No
Trust Server Certificate: No
Multiple Active Result Sets(MARS): No
Mirror Server:
Translate Character Data: Yes
Log Long Running Queries: No
Log Driver Statistics: No
Use Regional Settings: No
Use ANSI Quoted Identifiers: Yes
Use ANSI Null, Paddings and Warnings: Yes"


Besides, I submitted an offered system test in a process of establishing and it looked successfully:

"Microsoft SQL Native Client Version 09.00.5000
Running connectivity tests...

Attempting connection
Connection established
Verifying option settings
Disconnecting from server

TESTS COMPLETED SUCCESSFULLY!"
******************************************************************
However, after I had ran my newly established ODBC connection through SAS, log complained for the error:

libname mylibref ODBC
NOPROMPT="UID=;PSW=;DSN=IR_SAS;SERVER=REPORTP01;DATABASE=DW;";

22   libname mylibref ODBC
23   NOPROMPT=XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX;
ERROR: CLI error trying to establish connection: [Microsoft][ODBC Driver Manager] Data source
       name not found and no default driver specified
ERROR: Error in the LIBNAME statement.

So far:
-I wonder where my mistake is: is it in the process of establishing connection (while the submitted test show Data source name and driver were set properly)?
-Or it is about libname statement?
-Or probably it is somewhat related to the SAS update to the license renewal process after I had establish my ODBC Connection? (I found the following statements in the SAS revision / renewal script just in case)
*PRODNUM209 = ODBC SAS/ACCESS ODBC;
..
Comp16=odbc
..
SAS/ACCESS Interface to ODBC    01FEB2011
Or both?
What is wrong and how to resolve it? I am totally lost..

Thank you in advance!

Katrin
 

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login


Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close