We are trying to load data from SAS to postgres database by running jobs in SAS Data Integration Studio (4.9).
We are getting the following error at the table loader.
ERROR: CLI execute error: ERROR: current transaction is aborted, commands ignored until end of transaction block; Error while preparing parameters
Tables are being created in the Database but no data is being inserted into the tables.
Can you please guide us on this.
Appreciate your help on this.
What libname options have you used?
If you set
options msglevel=i sastrace=',,,d' sastraceloc=saslog nostsuffix;
you'll may get more informaiton in the log, perhqaps at least what SQl SAS is pushing down.
A quick Google geave me hint on that this might be work aroundable bgy using autocommit.
Hi,
We have tried shared option but we are not able to trace out the exact error.
I am sharing my both environment (UAT & Production) logs, if you able to explain or identify issue then pls help us.
for production error:
POSTGRES_4: Prepared: on connection 1
SELECT * FROM "datamart".DM_GSTR9_TBL_TAX_PAID WHERE 0=1
POSTGRES: AUTOCOMMIT is NO for connection 2
NOTE: SAS variable labels, formats, and lengths are not written to DBMS tables.
POSTGRES_5: Executed: on connection 2
CREATE TABLE "datamart".DM_GSTR9_TBL_TAX_PAID ("called_dt" VARCHAR(10),"fil_dt" VARCHAR(10),"gstin" VARCHAR(15),"fp"
VARCHAR(6),"isnil" VARCHAR(1),"txpaid_igst" DOUBLE PRECISION,"txpyble_igst" DOUBLE PRECISION,"txpaid_cgst" DOUBLE
PRECISION,"txpyble_cgst" DOUBLE PRECISION,"txpaid_sgst" DOUBLE PRECISION,"txpyble_sgst" DOUBLE PRECISION,"txpaid_cess" DOUBLE
PRECISION,"txpyble_cess" DOUBLE PRECISION,"txpaid_intr" DOUBLE PRECISION,"txpyble_intr" DOUBLE PRECISION)
POSTGRES: 0 row(s) affected by INSERT/UPDATE/DELETE or other statement.
POSTGRES: COMMIT performed on connection 2.
POSTGRES: COMMIT performed on connection 2.
NOTE: The data set DWH.DM_GSTR9_TBL_TAX_PAID has 0 observations and 15 variables.
POSTGRES: COMMIT performed on connection 2.
POSTGRES: COMMIT performed on connection 2.
NOTE: PROCEDURE| _DISARM| STOP| _DISARM| 2020-12-19T13:52:20,547+05:1800| _DISARM| WorkspaceServer| _DISARM| SAS| _DISARM|
| _DISARM| 132886528| _DISARM| 20090880| _DISARM| 11| _DISARM| 24| _DISARM| 0| _DISARM| 809888| _DISARM| 0.010000| _DISARM|
0.055709| _DISARM| 1923985340.491702| _DISARM| 1923985340.547411| _DISARM| 0.000000| _DISARM| | _ENDDISARM
NOTE: DATA statement used (Total process time):
real time 0.05 seconds
cpu time 0.02 seconds
MPRINT(ETLS_LOADER): ;
NOTE: Appending data ...
POSTGRES_6: Prepared: on connection 1
90 The SAS System Saturday 19 December 2020 01:16:00 PM
SELECT * FROM "datamart".DM_GSTR9_TBL_TAX_PAID WHERE 0=1
POSTGRES: AUTOCOMMIT is NO for connection 2
POSTGRES: COMMIT performed on connection 2.
POSTGRES_7: Prepared: on connection 2
SELECT * FROM "datamart".DM_GSTR9_TBL_TAX_PAID
MPRINT(ETLS_LOADER): proc append base = DWH.dm_gstr9_tbl_tax_paid data = WORK.W7K8BK8S () force ;
MPRINT(ETLS_LOADER): run;
NOTE: Appending WORK.W7K8BK8S to DWH.DM_GSTR9_TBL_TAX_PAID.
NOTE: SAS threaded sort was used.
POSTGRES_8: Prepared: on connection 2
INSERT INTO "datamart".DM_GSTR9_TBL_TAX_PAID
("called_dt","fil_dt","gstin","fp","isnil","txpaid_igst","txpyble_igst","txpaid_cgst","txpyble_cgst","txpaid_sgst","txpyble_sgst","t
xpaid_cess","txpyble_cess","txpaid_intr","txpyble_intr") VALUES ( ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? )
POSTGRES_9: Executed: on connection 2
Prepared statement POSTGRES_8
POSTGRES: ROLLBACK performed on connection 2.
NOTE: There were 20000 observations read from the data set WORK.W7K8BK8S.
NOTE: View WORK.W7JXE9GV.VIEW used (Total process time):
real time 1.62 seconds
cpu time 2.52 seconds
NOTE: There were 185332 observations read from the data set S_GSTR9.STG_GSTR9_TBL_TABLE14_CAMT.
NOTE: There were 185332 observations read from the data set WORK.W3Z7RQCW.
NOTE: There were 184909 observations read from the data set S_GSTR9.STG_GSTR9_TBL_TABLE14_CSAMT.
NOTE: There were 184909 observations read from the data set WORK.W3Z7RQCZ.
NOTE: There were 185331 observations read from the data set S_GSTR9.STG_GSTR9_TBL_TABLE14_SAMT.
NOTE: There were 185331 observations read from the data set WORK.W3Z7RQD1.
NOTE: There were 185164 observations read from the data set S_GSTR9.STG_GSTR9_TBL_TABLE14_IAMT.
NOTE: There were 185164 observations read from the data set WORK.W3Z7RQD2.
NOTE: There were 183464 observations read from the data set S_GSTR9.STG_GSTR9_TBL_TABLE14_INTR.
NOTE: There were 183464 observations read from the data set WORK.W3Z7RQD4.
NOTE: 19999 observations added.
NOTE: The data set DWH.DM_GSTR9_TBL_TAX_PAID has . observations and 15 variables.
POSTGRES: COMMIT performed on connection 2.
POSTGRES: COMMIT performed on connection 2.
ERROR: CLI execute error: ERROR: current transaction is aborted, commands ignored until end of transaction block;
preparing parameters
NOTE: Statements not processed because of errors noted above.
NOTE: PROCEDURE APPEND used (Total process time):
real time 1.73 seconds
cpu time 2.55 seconds
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE| _DISARM| STOP| _DISARM| 2020-12-19T13:52:22,288+05:1800| _DISARM| WorkspaceServer| _DISARM| SAS| _DISARM|
| _DISARM| 132886528| _DISARM| 20877312| _DISARM| 11| _DISARM| 24| _DISARM| 268816| _DISARM| 1078704| _DISARM| 2.550000|
_DISARM| 1.739518| _DISARM| 1923985340.549101| _DISARM| 1923985342.288619| _DISARM| 2.070000| _DISARM| | _ENDDISARM
MPRINT(ETLS_LOADER): ;
The SAS Users Group for Administrators (SUGA) is open to all SAS administrators and architects who install, update, manage or maintain a SAS deployment.
Learn how to install the SAS Viya CLI and a few commands you may find useful in this video by SAS’ Darrell Barton.
Find more tutorials on the SAS Users YouTube channel.