BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
JamesPower4
Fluorite | Level 6

Hello:  I have created an ODBC source on my PC to read from SQL Server.  When I test the ODBC connection it is successful; also I can view the SQL Server tables using Microsoft Access.  I'm using NT authentication.

 

However, I cannot create a SAS Libname that will also show or display the tables.  I've tried both the ODBC and OLEDB approaches.  In both cases the Libname is created successfully.  However, no tables are listed when I open that library.  Here's an example log, in which I cut code from a SAS technical paper ("Accessing a Microsoft SQL Server Database from SAS® under Microsoft Windows") and pasted it into SAS:

 

12 libname sqlsrv oledb;
NOTE: Libref SQLSRV was successfully assigned as follows:
Engine: OLEDB
Physical Name:
13 %put %superq(sysdbmsg); /* V9 syntax */
OLEDB: Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;Initial
Catalog=NCCA;Data Source=p2626xorth005

 

The above example displays prompts for the necessary Libname options, and then stores the values in %sysdbmsg.

 

Any ideas about what I'm missing?

 

Using Windows 10, SAS 9.4., and not sure of the SQL Server version

 

Thanks in advance.

 

Jim Power

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User
libname test odbc dsn='dsn Name' schema=dbo;

Try something like above, with the dsn being the name you put in the ODBC connection and the schema being the schema you're trying to access. If it's multiple schema's you'll need a new libname for each of them. 

 


@JamesPower4 wrote:

Hello:  I have created an ODBC source on my PC to read from SQL Server.  When I test the ODBC connection it is successful; also I can view the SQL Server tables using Microsoft Access.  I'm using NT authentication.

 

However, I cannot create a SAS Libname that will also show or display the tables.  I've tried both the ODBC and OLEDB approaches.  In both cases the Libname is created successfully.  However, no tables are listed when I open that library.  Here's an example log, in which I cut code from a SAS technical paper ("Accessing a Microsoft SQL Server Database from SAS® under Microsoft Windows") and pasted it into SAS:

 

12 libname sqlsrv oledb;
NOTE: Libref SQLSRV was successfully assigned as follows:
Engine: OLEDB
Physical Name:
13 %put %superq(sysdbmsg); /* V9 syntax */
OLEDB: Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;Initial
Catalog=NCCA;Data Source=p2626xorth005

 

The above example displays prompts for the necessary Libname options, and then stores the values in %sysdbmsg.

 

Any ideas about what I'm missing?

 

Using Windows 10, SAS 9.4., and not sure of the SQL Server version

 

Thanks in advance.

 

Jim Power


 

View solution in original post

2 REPLIES 2
Reeza
Super User
libname test odbc dsn='dsn Name' schema=dbo;

Try something like above, with the dsn being the name you put in the ODBC connection and the schema being the schema you're trying to access. If it's multiple schema's you'll need a new libname for each of them. 

 


@JamesPower4 wrote:

Hello:  I have created an ODBC source on my PC to read from SQL Server.  When I test the ODBC connection it is successful; also I can view the SQL Server tables using Microsoft Access.  I'm using NT authentication.

 

However, I cannot create a SAS Libname that will also show or display the tables.  I've tried both the ODBC and OLEDB approaches.  In both cases the Libname is created successfully.  However, no tables are listed when I open that library.  Here's an example log, in which I cut code from a SAS technical paper ("Accessing a Microsoft SQL Server Database from SAS® under Microsoft Windows") and pasted it into SAS:

 

12 libname sqlsrv oledb;
NOTE: Libref SQLSRV was successfully assigned as follows:
Engine: OLEDB
Physical Name:
13 %put %superq(sysdbmsg); /* V9 syntax */
OLEDB: Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;Initial
Catalog=NCCA;Data Source=p2626xorth005

 

The above example displays prompts for the necessary Libname options, and then stores the values in %sysdbmsg.

 

Any ideas about what I'm missing?

 

Using Windows 10, SAS 9.4., and not sure of the SQL Server version

 

Thanks in advance.

 

Jim Power


 

JamesPower4
Fluorite | Level 6

Reza:  Many thanks!  That was the problem - I wasn't specifying the schema.

 

Again, many many thanks.

 

Jim Power

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to connect to databases in SAS Viya

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.

Discussion stats
  • 2 replies
  • 2844 views
  • 2 likes
  • 2 in conversation