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.
Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.
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.
Ready to level-up your skills? Choose your own adventure.