05-09-2016 09:35 AM
Even in proc sql I'm not using noexex then again why getting error.
same sql query in executed on hive and it give correct result no syntax error.
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements. 58 disconnect from odbc; NOTE: Statement not executed due to NOEXEC option. proc sql ; connect to odbc (dsn=inventory user=ashu password=''); create table libname.test_table as select c0 format=11., c1 format=$30. length=30, c2 format=11., c3 format=11.2 from connection to odbc (SELECT c0,c1,c2,c3 FROM test_table limit 10;); disconnect from odbc; quit ;
05-09-2016 10:11 AM
Most likely there was an ERROR before that. Look at the whole log.
If necessary, close the workspace server and open a new connection.
05-09-2016 02:50 PM - edited 05-09-2016 02:52 PM
@KurtBremser before query it also show the below error then proc sql query
ERROR: CLI prepare error: [DSI] The error message HardyTCLIServiceClientStatusError could not be found in the en-US locale. Check
that /en-US/SimbaHiveODBCMessages.xml exists.
But issue is, this error comes in only new programs on same pattern previous created programs are running with any error.
05-09-2016 03:23 PM
Then as KurtBremser suggested, you will have to show more of the log. The key portions would occur just before the notes about the NOEXEC option being turned on. There will be some clue as to why that option was turned on.
05-09-2016 08:00 PM
As others have said, we need to see more of the preceeding log.
We don't need to see the ENTIRE log, but it would help if we could see the last successfully executed code, and everything after that, up to the "PROC SQL set option NOEXEC" message.
05-10-2016 02:13 AM
Complete log for same now this issue comes now for those report also which was runnig without error. before I never face
1 The SAS System 16:29 Monday, May 9, 2016 1 ;*';*";*/;quit;run; 2 OPTIONS PAGENO=MIN; 3 %LET _CLIENTTASKLABEL='Program'; 4 %LET _CLIENTPROJECTPATH='C:\Users\goibibp-sas-admin\Desktop\In_Goibibo Reports\ingoibibo_inclusion.egp'; 5 %LET _CLIENTPROJECTNAME='ingoibibo_inclusion.egp'; 6 %LET _SASPROGRAMFILE=; 7 8 ODS _ALL_ CLOSE; 9 OPTIONS DEV=ACTIVEX; 10 GOPTIONS XPIXELS=0 YPIXELS=0; 11 FILENAME EGSR TEMP; 12 ODS tagsets.sasreport13(ID=EGSR) FILE=EGSR 13 STYLE=HtmlBlue 14 STYLESHEET=(URL="file:///C:/Program%20Files/SASHome/SASEnterpriseGuide/7.1/Styles/HtmlBlue.css") 15 NOGTITLE 16 NOGFOOTNOTE 17 GPATH=&sasworklocation 18 ENCODING=UTF8 19 options(rolap="on") 20 ; NOTE: Writing TAGSETS.SASREPORT13(EGSR) Body file: EGSR 21 22 GOPTIONS ACCESSIBLE; 23 libname inibibo '/sasdata/sasdemo1/ingoibibo'; NOTE: Libref INIBIBO was successfully assigned as follows: Engine: V9 Physical Name: /sasdata/sasdemo1/ingoibibo 24 25 26 proc sql; 27 connect to odbc (dsn=inv user=ashu password=XX); ERROR: CLI error trying to establish connection: [unixODBC][DSI] The error message HardyHiveError could not be found in the en-US locale. Check that /en-US/SimbaHiveODBCMessages.xml exists. NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements. 28 create table inibibo.ingoibibo_inclusion as select 29 id format=11., 30 inclusionname format=$50. length=50, 31 displayname format=$50. length=50, 32 rateplan_id format=11., 33 inclusion_status format=$20. length=20, 34 rateplan_status format=$20. length=20 35 from connection to odbc 36 (SELECT inc.id, inc.inclusionname, inc.displayname, ri.rateplan_id, 37 (CASE WHEN inc.isactive = true THEN 'Active Inclusion' WHEN inc.isactive = false THEN 'Non Active Inclusion' END ) as 37 ! inclusion_status, 38 (CASE WHEN ri.inclusions_id = inc.id THEN 'With rate Plan' 39 WHEN ri.inclusions_id IS NULL THEN 'Without rate Plan' END) as rateplan_status 40 FROM goibibo_inventory.hotels_inclusions inc 41 LEFT JOIN goibibo_inventory.hotels_rateinclusions ri ON (ri.inclusions_id = inc.id );); NOTE: Statement not executed due to NOEXEC option. 42 disconnect from odbc; NOTE: Statement not executed due to NOEXEC option. 43 quit ; NOTE: The SAS System stopped processing this step because of errors. NOTE: PROCEDURE SQL used (Total process time): real time 0.01 seconds cpu time 0.02 seconds 2 The SAS System 16:29 Monday, May 9, 2016 44 /* creating index on exiting table 45 for more detail http://support.sas.com/documentation/cdl/en/sqlproc/62086/HTML/default/viewer.htm#a001395386.htm*/ 46 /*proc sql; 47 create index createdon on inibibo.ingoibibo_hotel_booking(createdon); 48 quit; 49 */ 50 51 /* remove table from LasR server if exist */ 52 %macro lasrDeletedIfExists(lib,name); 53 %if %sysfunc(exist(&lib..&name.)) %then %do; 54 proc datasets library=&lib. nolist; 55 delete &name.; 56 quit; 57 %end; 58 %mend; 59 60 /* Load data in LasR */ 61 /* inTable : reference of datasource table (lib.table) */ 62 /* lasRTable : reference of target table in lasR server (lib.table) */ 63 /* lasrLabel : label to use for the lasr Table (users description) */ 64 %macro loadDataInLasR(inTable,lasRTable,lasrLabel); 65 proc options option=MEMSIZE;run; 66 data VALIBLA.&lasRTable (label=&lasrLabel);set inibibo.&inTable;run; 67 %mend; 68 69 /* Refresh metadata for the selected lasR table */ 70 %macro updateMetadataForLasrTable(lasrLibref,mdsTargetFolder,lasrTableName); 71 proc metalib; 72 omr (LIBURI="SASLibrary?@libref='&lasrLibref'" ); 73 /*no more used : update_rule=(delete);*/ 74 report(type=summary); 75 folder="&mdsTargetFolder"; 76 select=("&lasrTableName"); 77 run; 78 %put PROC METALIB returned &SYSRC.; 79 %mend; 80 81 82 83 /* Access the LASR library */ 84 LIBNAME VALIBLA SASIOLA TAG=HPS PORT=10011 HOST="nmclbi01.nm.goibibo.com" 84 ! SIGNER="http://nmclbi01.nm.goibibo.com:7980/SASLASRAuthorization" ; NOTE: Libref VALIBLA was successfully assigned as follows: Engine: SASIOLA Physical Name: SAS LASR Analytic Server engine on local host, port 10011 85 86 /* remove table from memory if loaded in lasr server 87 * Param1 : LASR libref 88 * Param2 : LASR table name 89 */ 90 91 %lasrDeletedIfExists(VALIBLA, ingoibibo_inclusion); NOTE: Deleting VALIBLA.INGOIBIBO_INCLUSION (memtype=DATA). NOTE: The table HPS.INGOIBIBO_INCLUSION has been removed from the SAS LASR Analytic Server. NOTE: PROCEDURE DATASETS used (Total process time): 3 The SAS System 16:29 Monday, May 9, 2016 real time 0.70 seconds cpu time 0.03 seconds 92 93 94 /* Load data in LasR 95 * Param1 : local table to read 96 * Param2 : LASR table to push (write) in LASR server 97 * Param3 : Label to use for user description on the table 98 */ 99 %let etls_endtime = %sysfunc(datetime(), datetime20.); 100 101 %loadDataInLasR(ingoibibo_inclusion,ingoibibo_inclusion,Loaded on &etls_endtime.); SAS (r) Proprietary Software Release 9.4 TS1M3 MEMSIZE=96745808640 Specifies the limit on the amount of virtual memory that can be used during a SAS session. NOTE: PROCEDURE OPTIONS used (Total process time): real time 0.00 seconds cpu time 0.00 seconds NOTE: There were 45690 observations read from the data set INIBIBO.INGOIBIBO_INCLUSION. NOTE: The data set VALIBLA.INGOIBIBO_INCLUSION has 45690 observations and 6 variables. NOTE: DATA statement used (Total process time): real time 1.58 seconds cpu time 0.05 seconds 102 103 104 /* Update metadata for the selected lasr table 105 * Param1 : LASR library libref 106 * Param2 : Target metadata server folder 107 * Param3 : LASR table name (only the table name, no libref) 108 */ 109 %updateMetadataForLasrTable(VALIBLA,/Shared Data/SAS Visual Analytics/Public/LASR,ingoibibo_inclusion); NOTE: A total of 1 tables were analyzed for library "Visual Analytics LASR". NOTE: Metadata for 1 tables was updated. NOTE: Metadata for 0 tables was added. NOTE: Metadata for 0 tables matched the data sources. NOTE: 0 tables listed in the SELECT or EXCLUDE statement were not found in either the metadata or the data source. NOTE: 0 other tables were not processed due to error or UPDATE_RULE. NOTE: PROCEDURE METALIB used (Total process time): real time 1.96 seconds cpu time 0.11 seconds PROC METALIB returned 0 110 111 112 GOPTIONS NOACCESSIBLE; 113 %LET _CLIENTTASKLABEL=; 114 %LET _CLIENTPROJECTPATH=; 4 The SAS System 16:29 Monday, May 9, 2016 115 %LET _CLIENTPROJECTNAME=; 116 %LET _SASPROGRAMFILE=; 117 118 ;*';*";*/;quit;run; 119 ODS _ALL_ CLOSE; 120 121 122 QUIT; RUN; 123
05-10-2016 02:30 AM
Thanks for posting the full log.
Ok, your connection to your ODBC data source is failing, causing every subsequent SQL statement to be ignored.
You'll need to find out why the connection is failing:
I've added a simple macro test to verify a successful connection.
proc sql; connect to odbc (dsn=inv user=ashu password=XX); %put &=SQLRC; /* Should be 0 */ disconnect from odbc; quit;
05-10-2016 02:46 AM
05-10-2016 05:34 AM
Curious - so the connect which previously failed, now works..
Next step is to see if you can create the target table..
create table inibibo.ingoibibo_inclusion
Need further help from the community? Please ask a new question.