SAS dsnless connection to SQL server not working even if OLEDB is installed

0
votes

I am trying to setup a DSN less connection to SQL server as follows:

libname sqwireno sqlsvr noprompt='Driver=SQLServer;Address=TOCGDMS,1433;Database=DMS1;
       UID=s11111;PWD=Test;' schema=dbo;

ERROR: The SQLSVR engine cannot be found. ERROR: Error in the LIBNAME statement.

I am using SAS 9.4 on windows and have verified that SAS Access to OLEDB is installed.

What did I do wrong

1
sql-serversasodbc
OLEDB uses providers while ODBC uses drivers. You are attempting an ODBC connection. – Parfait

1 Answers

0
votes

In Windows environments, clients like SAS can make OLEDB and ODBC connections (two distinct APIs) to relational databases. SAS/ACCESS using libname supports both forms requiring those specific features installed (check: proc setinit; run;). Below are examples of DSN-less connections but DSNs (Data Source Name) as supported by all three:

ODBC (requires 'SQL Server' or other driver installed)

libname sqldata odbc noprompt="driver=SQL Server; Server=servername; user=username; 
                               pwd=password; database=databasename" schema=myschema;

OLEDB (requires 'SQLOLEDB' or other provider installed)

libname sqldata oledb user=username password=password datasource=servername schema=myschema
                      provider=sqloledb properties=('initial catalog'=databasename);

Do note: Microsoft allows free downloads of latest ODBC driver and OLEDB provider for SQL Server (where 'SQLOLEDB' is now deprecated for 'MSOLEDBSQL').

Additionally, SAS supports an SQL Server engine connection which possibly offers more specific MSSQL features than the above two interfaces which are generalized for any database or data source:

SQLSERV (using SAS ODBC driver)

libname mssql sqlsvr noprompt="driver={SAS ACCESS to SQL Server}; server=servername; 
                               database=databasename; uid=username; pwd=password" schema=myshema;

In fact, your connection may work with appropriate punctuation such as {...} in Driver argument.

libname sqwireno sqlsvr noprompt="Driver={SQL Server};Server=TOCGDMS,1433;Database=DMS1;    
                                  UID=s11111;PWD=Test;" schema=dbo;