topic Re: Connect to MS SQL 2014 via ODB in SAS Data Management

Connect to MS SQL 2014 via ODBC

dirks — Sat, 15 Jul 2017 13:21:32 GMT

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

Re: Connect to MS SQL 2014 via ODB

Kurt_Bremser — Sat, 15 Jul 2017 12:55:32 GMT

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

Re: Connect to MS SQL 2014 via ODB

dirks — Sat, 15 Jul 2017 13:22:50 GMT

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

Re: Connect to MS SQL 2014 via ODB

Kurt_Bremser — Sat, 15 Jul 2017 14:24:03 GMT

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)

Re: Connect to MS SQL 2014 via ODB

dirks — Sat, 15 Jul 2017 14:45:48 GMT

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

Re: Connect to MS SQL 2014 via ODB

SASKiwi — Sat, 15 Jul 2017 22:59:10 GMT

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.

Re: Connect to MS SQL 2014 via ODB

Reeza — Sat, 15 Jul 2017 23:36:10 GMT

Schema and table namesmaybe case sensitive.

Re: Connect to MS SQL 2014 via ODB

dirks — Mon, 17 Jul 2017 14:06:09 GMT

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

It is working now.

Thanks everyone!

Re: Connect to MS SQL 2014 via ODB

SASKiwi — Mon, 17 Jul 2017 20:16:57 GMT

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-Linux/m-p/349265#M10387