BookmarkSubscribeRSS Feed
Handihusen
Obsidian | Level 7

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:

sample2.jpg

 

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.

 

7 REPLIES 7
andreas_lds
Jade | Level 19

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

Handihusen
Obsidian | Level 7

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

LinusH
Tourmaline | Level 20

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; 
Data never sleeps
Handihusen
Obsidian | Level 7
still cannot get the CRM_DATA_OUTBOX, somehow SAS select another table v_ISEE_STAFF_DATA, as shown in the logs.
Handihusen
Obsidian | Level 7
sorry typo: 'CRM_DATA_OUTBOX', i mean 'v_CRM_DATA_DAILY'
SASKiwi
PROC Star

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

Handihusen
Obsidian | Level 7
noted & thanks,

sas-innovate-wordmark-2025-midnight.png

Register Today!

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.


Register now!

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 7 replies
  • 1043 views
  • 1 like
  • 4 in conversation