CLI Connectivity Error

David_Billa — Tue, 31 May 2022 14:23:06 GMT

I'd like to know why in the error message it shows 'KSS_EXTERNAL' as it is not mentioned any where in the code? If UVW_ISM_TRANSACTIONS_RV_PIVOT is a view, we may get error message as shown below?

proc sql noprint;
  connect to sqlsvr(dsn=KGG_SC  
                    user="SAS_PU_UK" 
                    pwd="test@123" 
                    readbuff=32000);
 
  create table TRANSACTIONS_RV_PIVOT as
  select * from connection to sqlsvr 
    (                                                      
      select *
        
      from KGG_SC.dbo.UVW_ISM_TRANSACTIONS_RV_PIVOT);
 
  disconnect from sqlsvr;
quit;

Log:

26         proc sql noprint;
27           connect to sqlsvr(dsn=KGG_SC
28                             user="SAS_PU_UK"
29                             pwd=XXXXXXXXXXXXX
30                             readbuff=32000);
31         
32           create table TRANSACTIONS_RV_PIVOT as
33           select * from connection to sqlsvr
34             (
35               select *
36         
37               from KGG_SC.dbo.UVW_ISM_TRANSACTIONS_RV_PIVOT);
ERROR: CLI describe error: [SAS/ACCESS][ODBC SQL Server Legacy Driver][SQL Server]The server principal "SAS_PU_UK" is not 
       able to access the database "KSS_EXTERNAL" under the current security context.
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
38         
39           disconnect from sqlsvr;
NOTE: Statement not executed due to NOEXEC option.
40         quit;

Re: CLI Connectivity Error

ChrisNZ — Sat, 04 Jun 2022 06:59:27 GMT

You're showing a SQL Server error message mentioning SQL Server architecture components, and this question is for your SQL Server DBAs.

topic CLI Connectivity Error in SAS Programming

CLI Connectivity Error

Re: CLI Connectivity Error