insert only 1 record when use Proc Append to Azure Synapse SQL Pool

[osmelbrito]

I have a Azure synapse SQL Pool where I have to insert data using SAS

libname bet sqlsvr datasrc="BET" user=datanalitica password="XXXXXX" schema="BET" 
                   USE_ODBC_CL=YES insertbuff=30000
                   insert_sql=yes autocommit=yes stringdates=yes
                   ;

I successfully connect to the database, but when I try to insert data using proc append

proc append base=bet.DIM_RAMO_FINANZAS () data=W_DIM_RAMO_FINANZAS nowarn ;
run;

It only inserts the first record of the dataset even though in log proc append shows that added all records

SQLSRV_4: Prepared: on connection 2
INSERT INTO "BET"."DIM_RAMO_FINANZAS" 
("RAMO_FINANZAS_CD","FILIAL_GRUPO_SK","RAMO_FINANZAS_NM","RAMO_FINANZAS_DESC","TIPO_RAMO_CD","ORDEN_RAMO_ID","PROCESSED_DTTM")  
VALUES ( ? , ? , ? , ? , ? , ? , ? )
 
NOTE: There were 23 observations read from the data set WORK.W_DIM_RAMO_FINANZAS.
NOTE: 23 observations added.
NOTE: The data set BET.DIM_RAMO_FINANZAS has . observations and 7 variables.
 
SQLSRV_5: Executed: on connection 2
Prepared statement SQLSRV_4
 
 
Summary Statistics for SQLSVR are:
Total SQL execution seconds were:                   0.140779
3                                                          The SAS System                          14:22 Thursday, September 1, 2022

Total SQL prepare seconds were:                     0.000061
Total SQL describe seconds were:                    0.049702
Total seconds used by the SQLSVR ACCESS engine were     0.281108

I have SAS 9.4 M6

[osmelbrito]

I just tried using proc sql and insert, and it happens the same, it only insert first record

[SASKiwi]

This looks like a problem best suited to SAS Tech Support. You could try setting DBCOMMIT to 0 to enforce a single commit for the inserts. 

libname bet sqlsvr datasrc="BET" user=datanalitica password="XXXXXX" schema="BET" USE_ODBC_CL=YES insertbuff=30000 dbcommit = 0
                   insert_sql=yes autocommit=yes stringdates=yes
                   ;

[osmelbrito]

I just tried different values of inserbuff and dbcommit and it does not work (including recommendation dbcommit=0)

When I used insertbuff=1 it works. But, it is supposed that this parameter increase speed for insertion? if I use 1, massive insertions could be slow?

[SASKiwi]

I think you are correct - using INSERTBUFF = 1 as a workaround isn't likely to be very efficient. As already mentioned raising this with Tech Support is your best option.

[osmelbrito · Accepted solution]

I just opened a support track and the solution they found was not use USE_ODBC_CL parameter or setting to NO

after this, with this libname

libname bet sqlsvr datasrc="BET" user=datanalitica password=XXXXXXXXXXXXXXXXXXXX schema="BET" USE_ODBC_CL=NO insertbuff=30000

insertion of full records works perfect 

[Tom]

So the explanation of that options is just as clear as mud to me.

https://documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/acreldb/p0nhsktrtipxw9n1p8ypfk9vq6fk.htm

But it does tend to make sense that there should not be any need to use "block cursors", whatever the heck that means, for a simple insert.