We are extending an existing process to access a new Snowflake database. Thus is Release 9.04.01M7P080520   In the existing processes that all work for over a year now, the WAREHOUSE does not need to be defined in the libname -- there is a default warehouse associated with the role.   In the new database there is no default WAREHOUSE, and there is a new ROLE. The signon is the same Snowflake account that we have used all the time.   So to start with we issue this libname:   libname FCH_data SNOW server ="bnz_dap_prod_aws.ap-southeast-2.privatelink.snowflakecomputing.com" user =SRV_FCH_SAS_UAT02_SASCONNECT pw ='XXXXXXXXXXXXXXXXX' database =DB_LENDING_UAT02 role =RL_SF_DP_MDS_CONSUMER_UAT02  ;   This results in    60 create table mytable as 61 (select * from connection to fch_data 62 ( 63 select * from DB_LENDING_UAT02.MDS_CCD.ccd_accounts_sas 64 limit 10 65 66 ) 67 ) 68 ; ERROR: CLI open cursor error: No active warehouse selected in the current session. Select an active warehouse with the 'use warehouse' command.   So thats fairly simple, I need to define a warehouse. So I got the warehouse from the Snowflake team and submitted this libname (the Snowflake admin confirmed that all necessary grants are in place and I sent them the grant stream from the original database to mimic)   libname FCH_data SNOW server ="bnz_dap_prod_aws.ap-southeast-2.privatelink.snowflakecomputing.com" user =SRV_FCH_SAS_UAT02_SASCONNECT database =DB_LENDING_UAT02 role =RL_SF_DP_MDS_CONSUMER_UAT02  warehouse =wh_lending_m    It is clear though that this did not set  the warehouse. We got the same No ACTIVE WAREHOUSE message. So I added a simple check of running an SQL to query the warehouse and sure enough there is no warehouse defined.   create table mytable as      (select * from connection to fch_data            (    SELECT CURRENT_WAREHOUSE();          )     )  ;   So I commented out the ROLE in the libname and tried again, but the warehouse doesnt get set.   After reading up a bit on Snowflake I realised the libname isnt the only way to set the WAREHOUSE. I can just execute the USE WAREHOUSE XXXXX command   So I did this   proc sql ; connect using fch_data ; execute (use warehouse wh_lending_m) by fch_data ;   That also fails, with this Snowflake message   ERROR: CLI execute error: SQL compilation error: Object does not exist, or operation cannot be performed.   At this point one of my teammates started to experiment and removed the ROLE from the libname. That failed, but then he changed the WAREHOUSE to the original one (which we dont want to use as the COMPUTE cost shouldnt be against that database). That worked and set the WAREHOUSE. The query didnt work as there is no ROLE. So I placed the ROLE in an EXECUTE instead of the libname   execute (use role RL_SF_DP_MDS_CONSUMER_UAT02;) by fch_data ;   select current_role() shows that worked fine.   So I then added the WAREHOUSE   execute (use role RL_SF_DP_MDS_CONSUMER_UAT02;) by fch_data ; execute (use warehouse wh_lending_m;) by fch_data ;   The role was set, the WAREHOUSE is not ACTIVE. So that tends to verify that the problem is unrelated to the libname.   I've tried flipping the WAREHOUSE to run before the USE ROLE but no WAREHOUSE is defined   So we have this situation, whether its libname or EXECUTE *  if the warehouse is defined as the new warehouse in the new database, it does not get set whether from the libname or execute. No query runs as there is no warehouse. select current_warehouse() verifies no warehouse exists *  if the warehouse is the old warehouse which we have a directive to not use, then it is set provided the ROLE is not set in libname or EXECUTE.  *  The old role cant run queries in the new database (thats intentional there is no grant for that role, and if it could the COMPUTE costs would be against wrong database) . We will get that grant done to see if it works.   But -- has anyone struck this issue of WAREHOUSE and ROLE being in conflict from SAS??? OI did think maybe the Snowflake grants are not right, but Snowflake admin is sure they are ...                   ... View more

Not a question rather an outcome of some weird errors with Snowflake.   Created a Snowflake table using passthrough and CREATE TEMP TABLE. table name was myTable   then PROC APPEND BASE=<<libname>>.mytable data=....   We were getting misformed Snowflake SQL in the statements generated by SAS in PROC APPEND. The object was to use that proc to populate the temp Snowflake table.   Spoke to DBA, he pointed out that case matters to Snowflake, and SAS passes through what you give it.   Snowflake had not found mytable in the SQL that SAS passes through as the create used myTable.   Changed every instance of the libname, database name and anything else Snowflake to UPPER CASE and it all worked fine.   I had already opened a track with SAS and they confirmed this.    BACK TO COBOL CODING 🙂 ! ... View more

Trying to setup a libname for Snowflake as follows:   libname fch_data snow dbmstemp=YES server="<<serverName>>" database=<<dbname>> user=<<username>> role=<<rolename>> pw=<<pwname>> schema=<<schemaname>> connection=global conopts="<<proxyname";     29 libname fch_data snow dbmstemp=YES ________ 22 ERROR 22-7: Invalid option name DBMSTEMP.     If I remove DBMSTEMP the libname works fine. I only need it to allow a libname to create temporary tables in Snowflake so if there is another way of doing that from data step I can try that, but cant see any obvious way apart from libname and executing CREATE TEMP in SQL (thats not my preference, the temp table source is always a SAS DB and can be very large, also I cant see any way using execute to create a Snowflake table from a SAS table -- not sure I'd want to).   This is in   Current version: 9.04.01M7P080520 Operating System: WX64_SV .   DBMSTEMP is documented as valid in SAS/ACCESS to SnowFlake at https://documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/acreldb/p0he4t6yjfmkhpn16qrf0cdhllu6.htm   DBMSTEMP= LIBNAME Statement Option Specifies whether SAS creates temporary or permanent tables. Valid in:Category:Default:Requirement:Interaction:Data source: SAS/ACCESS LIBNAME statement Data Set Control NO To specify this option, you must first specify CONNECTION=GLOBAL—except for Microsoft SQL Server, which defaults to UNIQUE. To access temporary tables, set DBMSTEMP= to YES and set the CONNECTION= LIBNAME option to GLOBAL. Amazon Redshift, Aster, DB2 under UNIX and PC Hosts, DB2 under z/OS, Greenplum, Hadoop, HAWQ, JDBC, Microsoft SQL Server, MySQL, Netezza, ODBC, OLE DB, Oracle, PostgreSQL, SAP HANA, SAP IQ, Snowflake, Spark, Teradata, Vertica, Yellowbrick ... View more