topic Re: SAS reading MS SQL View Table in SAS Programming

SAS reading MS SQL View Table

Handihusen — Fri, 22 Sep 2023 07:46:28 GMT

hi, i need some help regarding SAS reading MS SQL View Table:

i'm using SAS 9.4 for Windows,

i already set up ODBC Connection

i want to read View Table: v_CRM_DATA_DAILY from MS SQL server

this is the captured form MS SQL Server:

i'm using this sas code but failed to get v_CRM_DATA_DAILY

LIBNAME X ODBC datasrc=CRMDATA user=xxxx pwd=xxxxxx;

proc sql;
connect using x;
create table CRMDAILY as select * from connection to X
( select * from v_CRM_DATA_DAILY );
quit;

sas log:

ERROR: CLI describe error: [Microsoft][ODBC SQL Server Driver][SQL Server]The SELECT permission was denied on the object 'v_ISEE_STAFF_DATA', database 'CXXXX_iSee', schema 'dbo'.
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
real time 0.17 seconds
cpu time 0.00 seconds

Thanks in advance for the response and help.

Re: SAS reading MS SQL View Table

andreas_lds — Fri, 22 Sep 2023 09:42:13 GMT

Contact the mssql admin, afaik granting read access to a view is not sufficient, read access to the used tables is necessary, too.

Re: SAS reading MS SQL View Table

Handihusen — Fri, 22 Sep 2023 11:10:38 GMT

If access has been granted, then the SAS code used is as above that I use?

Re: SAS reading MS SQL View Table

LinusH — Fri, 22 Sep 2023 11:34:34 GMT

You could, but writing explicit pass through is not necessary in this case. Simpler coding:

LIBNAME X ODBC datasrc=CRMDATA user=xxxx pwd=xxxxxx;

proc sql;
create table CRMDAILY as select * 
   from X.v_CRM_DATA_DAILY
;
quit;

Re: SAS reading MS SQL View Table

Handihusen — Tue, 26 Sep 2023 03:37:00 GMT

still cannot get the CRM_DATA_OUTBOX, somehow SAS select another table v_ISEE_STAFF_DATA, as shown in the logs.

Re: SAS reading MS SQL View Table

Handihusen — Tue, 26 Sep 2023 03:39:16 GMT

sorry typo: 'CRM_DATA_OUTBOX', i mean 'v_CRM_DATA_DAILY'

Re: SAS reading MS SQL View Table

SASKiwi — Tue, 26 Sep 2023 03:43:44 GMT

If you are still getting SELECT permission errors, then only your organisation's database administrators can fix that for you.

Re: SAS reading MS SQL View Table

Handihusen — Tue, 26 Sep 2023 07:59:44 GMT

noted & thanks,