SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

Connect to MS SQL 2014 via ODBC

Accepted Solution Solved
Reply
Contributor
Posts: 42
Accepted Solution

Connect to MS SQL 2014 via ODBC

[ Edited ]

Hi,

 

I want to connect to a MS SQL 2014 server.

I am using this statement:

 

LIBNAME onbaset ODBC DSN=ONBASE_NISQL2 USER=sa PASSWORD="{SAS002}XXXXXXXXXXXXXXXXXXXX" SCHEMA=ONBASE;

 

It seems to connect and I get this:


18 LIBNAME onbaset ODBC DSN=ONBASE_NISQL2 USER=sa
18 ! PASSWORD=XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX SCHEMA=ONBASE;

NOTE: Libref ONBASET was successfully assigned as follows:
Engine: ODBC
Physical Name: ONBASE_NISQL2

 

But I dont see any tables or views. 

Am I missing an option in the libname statement?

 

Thanks and regards

Dirk


Accepted Solutions
Solution
‎07-17-2017 10:06 AM
Super User
Posts: 3,252

Re: Connect to MS SQL 2014 via ODB

To see the schema tables in SAS, your ODBC LIBNAME to SQL Server must specify the server name, database and schema. It isn't clear from your example whether you are doing that or not. Does your DATASRC include both a server name and a database? If it doesn't you can add QUALIFIER = MyDatabaseName to your LIBNAME. 

View solution in original post


All Replies
Super User
Posts: 7,764

Re: Connect to MS SQL 2014 via ODB

Test your credentials with the native DB client; consult with the DB admins.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Contributor
Posts: 42

Re: Connect to MS SQL 2014 via ODB

Posted in reply to KurtBremser

I did that. I can login in with that account and I can see everything.

Super User
Posts: 7,764

Re: Connect to MS SQL 2014 via ODB

Then look at the names of tables, views and columns. They must adhere to SAS standards (max 32 characters, consist only of standard letters, digits and underlines, must start with a letter or underline)

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Contributor
Posts: 42

Re: Connect to MS SQL 2014 via ODB

[ Edited ]
Posted in reply to KurtBremser

I just created a new database (Test) and a new table (Table) with a colum (a).

If I try to register tables for that database, I get this error:

 

"No tables were retrieved from your query. Your connection information may be incorrect. Do you want to view the SAS Log?"

 

This is the log:

 

1 Das SAS System 16:39 Saturday, July 15, 2017

NOTE: Copyright (c) 2002-2012 by SAS Institute Inc., Cary, NC, USA.
NOTE: SAS (r) Proprietary Software 9.4 (TS1M2)
Licensed to ACME Corp EDITION M DATENMGMT VA, Site 0815.
NOTE: This session is executing on the X64_SRV12 platform.

 

NOTE: Additional host information:

X64_SRV12 WIN 6.2.9200 Server

NOTE: SAS-Initialisierung used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds

NOTE: The autoexec file, D:\SAS\Config\Lev1\SASApp\WorkspaceServer\autoexec.sas, was executed at server initialization.
1 LIBNAME onbaset ODBC DATAsrc=ONBASE_NISQL2 SCHEMA=Test USER=sa
1 ! PASSWORD=XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX ;
NOTE: Libref ONBASET was successfully assigned as follows:
Engine: ODBC
Physical Name: ONBASE_NISQL2
2

 

 

Solution
‎07-17-2017 10:06 AM
Super User
Posts: 3,252

Re: Connect to MS SQL 2014 via ODB

To see the schema tables in SAS, your ODBC LIBNAME to SQL Server must specify the server name, database and schema. It isn't clear from your example whether you are doing that or not. Does your DATASRC include both a server name and a database? If it doesn't you can add QUALIFIER = MyDatabaseName to your LIBNAME. 

Super User
Posts: 19,772

Re: Connect to MS SQL 2014 via ODB

Schema and table namesmaybe case sensitive. 

Contributor
Posts: 42

Re: Connect to MS SQL 2014 via ODB

I just learned that the Schema name is not the same as the database name.

It is working now.

 

Thanks everyone!

Super User
Posts: 3,252

Re: Connect to MS SQL 2014 via ODB

Good to hear. You may also want to check out using connection strings to completely define your database connections instead of defining ODBC sources. Here is a link that describes how to do it:

 

https://communities.sas.com/t5/SAS-Data-Management/Example-of-DSN-less-SQL-Server-connection-from-Li...

 

 

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 8 replies
  • 336 views
  • 2 likes
  • 4 in conversation