topic Re: File DSN ODBC connection not working in SAS Enterprise Guide in SAS Enterprise Guide

File DSN ODBC connection not working in SAS Enterprise Guide

andrewbrock — Fri, 22 Sep 2023 01:58:32 GMT

We have an ODBC connection on the SAS server that uses a File DSN (for a SQL Server database connection). When we run SAS directly on the server we can assign the library like so:

libname database odbc noprompt="filedsn=d:\DSNs\ Reporting.dsn";

…and it works fine.

(the file path above refers to a place directly on the server)

When we use SAS EG to run the same command we get this error:

1                                                          The SAS System                           11:04 Monday, September 18, 2023



1          ;*';*";*/;quit;run;
2          OPTIONS PAGENO=MIN;
3          %LET _CLIENTTASKLABEL='Program';
4          %LET _CLIENTPROJECTPATH='';
5          %LET _CLIENTPROJECTNAME='';
6          %LET _SASPROGRAMFILE=;
7          
8          ODS _ALL_ CLOSE;
9          OPTIONS DEV=ACTIVEX;
10         GOPTIONS XPIXELS=0 YPIXELS=0;
11         FILENAME EGSR TEMP;
12         ODS tagsets.sasreport13(ID=EGSR) FILE=EGSR
13             STYLE=HtmlBlue
14             STYLESHEET=(URL=file:///C:/Program%20Files/SASHome/SASEnterpriseGuide/7.1/Styles/HtmlBlue.css)
15             NOGTITLE
16             NOGFOOTNOTE
17             GPATH=&sasworklocation
18             ENCODING=UTF8
19             options(rolap="on")
20         ;
NOTE: Writing TAGSETS.SASREPORT13(EGSR) Body file: EGSR
21         
22         GOPTIONS ACCESSIBLE;
23         
24         libname database odbc noprompt=XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX;
ERROR: CLI error trying to establish connection: [Microsoft][ODBC Driver Manager] General error: Unable to create file buffer
ERROR: Error in the LIBNAME statement.
25         
26         GOPTIONS NOACCESSIBLE;
27         %LET _CLIENTTASKLABEL=;
28         %LET _CLIENTPROJECTPATH=;
29         %LET _CLIENTPROJECTNAME=;
30         %LET _SASPROGRAMFILE=;
31         
32         ;*';*";*/;quit;run;
33         ODS _ALL_ CLOSE;
34         
35         
36         QUIT; RUN;
37

I would have thought that it should be telling SAS to find the File DSN on the server? Are File DSN-based ODBC connections supported in SAS EG? Does the File DSN need to be locally on the client that is running SAS EG instead of on the server?

Re: File DSN ODBC connection not working in SAS Enterprise Guide

SASKiwi — Fri, 22 Sep 2023 02:25:17 GMT

I suspect that the driver requires some file buffers be written to disk, and no folder has been defined as to where these should be stored. When you run SAS in batch, you have write access to the folder this SAS session is being run from. However when you run in EG, you don't have write access to the server folder this SAS session is being run from. This folder is likely to be similar to this: <SAS Home Folder>\SAS\Config\Lev1\SASApp\WorkspaceServer.

Re: File DSN ODBC connection not working in SAS Enterprise Guide

andrewbrock — Fri, 22 Sep 2023 02:28:08 GMT

Thanks - so are you perhaps suggesting that the users in question need write access to this directory from a Windows file permission perspective? Or that it's just impossible?

Re: File DSN ODBC connection not working in SAS Enterprise Guide

andrewbrock — Mon, 25 Sep 2023 22:13:33 GMT

The solution was to place the DSN on a SMB share and then change the path to the file DSN to be on that share. It would appear that it needs to be accessible to the client machine that's running EG, not the server.

Re: File DSN ODBC connection not working in SAS Enterprise Guide

SASKiwi — Mon, 25 Sep 2023 22:23:36 GMT

It depends where your EG SAS sessions run. On the remote server or locally on your PC.