Connect to MS SQL 2014 via ODBC

[dirks]

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

[Kurt_Bremser]

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

[dirks]

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

[Kurt_Bremser]

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)

[dirks]

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

[SASKiwi · Accepted solution]

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. 

[Reeza]

Schema and table namesmaybe case sensitive. 

[dirks]

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

It is working now.

Thanks everyone!

[SASKiwi]

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...