Does SAS9 and Viya Support SQL Server's Linked Server In Libname and ODBC?

[Eddie305]

Dear colleagues,

I would like to know if you have any experience using SAS9 and Viya4 to access databases and tables that are hosted in SQL Server's Linked Server.

In other words, can we have a library definition (i.e. libname statement) to access tables in a Linked Server?

Please kindly share your know-how or KB documents that confirm it is not a supported feature.

Thank you very much.

Eddie

[ChrisNZ]

Have you tried using SQL passthru to use the 4-part identifier reference ?

Like:

proc sql;
 connect using SQLLIB;
 select * from connection to SQLLIB (
   SELECT * FROM [.\LNKSVR].master.sys.databases
 );
quit;

[Eddie305]

Thank you for your sharing, but it is not the answer I expected.

My key question is whether we can or how to define a data library (i.e. libname statement) of a linked server which is on a SQL Server? Or is it a mission impossible?

[LinusH]

I don't have a SQL Server to play at this point, but if you can create an ODBC data source to your linked server, chances are that you can connect from SAS as well.

I found this (old) hack on Stack Overflow:

Connection string for Linked Servers Database - Stack Overflow.

But wouldn't it be easier to connect directly to your linked server?