How to make an ODBC library available for all SAS users

[lfabbri]

Hello everyone, I've successfully created an ODBC library in SAS Management Console 9.4 to read/write data in a Microsoft Sql Server database.

The library seems correctly configured, I can register tables from the MSSql database.

This is the LIBNAME statement:

LIBNAME bdpapl ODBC  NOPROMPT="dsn=MSSQL_BDPAPL;Trusted_Connection=yes"  SCHEMA=DBO ;

In SAS Data Integration Studio I've successfully created and executed a job that reads and writes data using tables registered with this library.

My problem is that when other SAS users want to execute similar operations, they got a failure with the following errors:

  Line 129: ERROR: CLI error trying to establish connection: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified

  Line 129: ERROR: Error in the LIBNAME statement.

  Line 133: ERROR: CLI error trying to establish connection: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified

  Line 133: ERROR: Error in the LIBNAME statement.

  Line 161: ERROR: Libref BDPAPL is not assigned.

 I verified that all the SAS users can see the library and the associated registered tables in the Inventory menu of Data Integration Studio, but for some reasons the job fail when they try to execute.

What have I to do to make the ODBC library working for all the SAS users?

Thanks for any help

[AngusLooney]

Did you create a USER or SYSTEM DSN in the ODBC manager?

User = only accessible to the relevant uses

System = accessible across all users

I've seen this be the cause of this sort of thing in the past.

[lfabbri]

Hi, thanks for the reply.

Can you elaborate? This is what I've got in the ODBC administrator:

[AngusLooney · Accepted solution]

So, your libname is using the DSN created as User DSN, so it will only work for your account. If it were calling a System DSN it would work for all users.

From the looks of things, using 'sqlserver_native_client' in place of 'MSSQL_BDPAPL' ought to fix it - assuming the sqlserver_native_client points to the right database/schema etc.

I think....

[lfabbri]

The error indeed was the use of a User DSN instead of a System DSN. Thank you!

[AngusLooney]

🙂

[SASKiwi]

@lfabbri - If you would like to try ODBC connections to SQL Server without using DSNs and the ODBC Administrator at all try this:

libname sqlsrvr odbc noprompt = "server=MyServerName;DRIVER=SQL Server Native Client 10.0;Trusted_Connection=yes;" qualifier = "MyDatabase" schema = "MySchema";