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
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.
Test your credentials with the native DB client; consult with the DB admins.
I did that. I can login in with that account and I can see everything.
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)
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
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.
Schema and table namesmaybe case sensitive.
I just learned that the Schema name is not the same as the database name.
It is working now.
Thanks everyone!
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:
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.
Find more tutorials on the SAS Users YouTube channel.