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.
Contact the mssql admin, afaik granting read access to a view is not sufficient, read access to the used tables is necessary, too.
If access has been granted, then the SAS code used is as above that I use?
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;
If you are still getting SELECT permission errors, then only your organisation's database administrators can fix that for you.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.