Itgenase094: "The table can not be created" door itgenspr113 op SQL Server

mds · 30 april 2024 om 08:13

Na het toevoegen van een Exact Online Database en een update van de Invantive Query tool (24.0) en Data Hub (24.0.156) krijg ik de onderstaande foutmelding bij het wegschrijven van Metacom-data uit PostgreSQL in SQL Server.

Kunnen jullie mij hiermee helpen?

itgenase094: Statement #2 'begin
  inse...r.sql');
end;' starting on line 25 caused an error.

The table can not be created.

Could not bulk insert 0 rows into the table 'Metacom.dbo.MC_FJOURNAALPOST'.

The row.

0 = 01-01-1753, 1 = 31-12-9999.

Message ID: f9308598-c589-49fa-b935-f2f5e9c5cf5c

Occurred (UTC): 30-4-2024 07:22:28

begin
  insert into SYNCLOGS@sql
  ( id
  , occurrence_date
  , action_type
  , table_name
  , label
  , actions
  --
  , ip_address
  , iuid
  , date_created
  , created_by
  , created_at
  , session_created
  )
  values
  ( newid()
  , sysdateutc
  , 'S'
  , null
  , 'Start'
  , null
  --
  , sys_context('USERENV', 'CLIENT_IP_ADDRESS_EXTERNAL', 'mc')
  , sys_context('USERENV', 'IUID', 'mc')
  , sysdateutc
  , sys_context('USERENV', 'CURRENT_USER', 'mc')
  , sys_context('USERENV', 'APPLICATION_FULL', 'mc')
  , sys_context('USERENV', 'SESSIONID', 'mc')
  );
  dbms_audit.register_event('xxbus001', 'Start job', natural_key => 'kopieer.sql');
  --
  set invantive-sql-correct-invalid-date@mc true;
  --
  create or replace table mc_dpersoneel@sql
  as
  select * except codePersoneel
  ,      cast(codePersoneel as varchar2(30)) codePersoneel
  from   dpersoneel@mc
  ;
  insert into SYNCLOGS@sql
  ( id
  , occurrence_date
  , action_type
  , table_name
  , label
  , actions
  --
  , ip_address
  , iuid
  , date_created
  , created_by
  , created_at
  , session_created
  )
  values
  ( newid()
  , sysdateutc
  , 'C'
  , 'mc_dpersoneel@sql'
  , 'Created'
  , to_char(sqlrowcount) || ' rows'
  --
  , sys_context('USERENV', 'CLIENT_IP_ADDRESS_EXTERNAL', 'mc')
  , sys_context('USERENV', 'IUID', 'mc')
  , sysdateutc
  , sys_context('USERENV', 'CURRENT_USER', 'mc')
  , sys_context('USERENV', 'APPLICATION_FULL', 'mc')
  , sys_context('USERENV', 'SESSIONID', 'mc')
  );
  --
  create or replace table mc_fjournaalpost@sql
  as
  select *
  from   fJournaalpost@mc
  ;
  insert into SYNCLOGS@sql
  ( id
  , occurrence_date
  , action_type
  , table_name
  , label
  , actions
  --
  , ip_address
  , iuid
  , date_created
  , created_by
  , created_at
  , session_created
  )
  values
  ( newid()
  , sysdateutc
  , 'C'
  , 'mc_fjournaalpost@sql'
  , 'Created'
  , to_char(sqlrowcount) || ' rows'
  --
  , sys_context('USERENV', 'CLIENT_IP_ADDRESS_EXTERNAL', 'mc')
  , sys_context('USERENV', 'IUID', 'mc')
  , sysdateutc
  , sys_context('USERENV', 'CURRENT_USER', 'mc')
  , sys_context('USERENV', 'APPLICATION_FULL', 'mc')
  , sys_context('USERENV', 'SESSIONID', 'mc')
  );
  --
  create or replace table mc_dProject@sql
  as
  select *
  from dProject@mc
  ;
  insert into SYNCLOGS@sql
  ( id
  , occurrence_date
  , action_type
  , table_name
  , label
  , actions
  --
  , ip_address
  , iuid
  , date_created
  , created_by
  , created_at
  , session_created
  )
  values
  ( newid()
  , sysdateutc
  , 'C'
  , 'mc_dProject@sql'
  , 'Created'
  , to_char(sqlrowcount) || ' rows'
  --
  , sys_context('USERENV', 'CLIENT_IP_ADDRESS_EXTERNAL', 'mc')
  , sys_context('USERENV', 'IUID', 'mc')
  , sysdateutc
  , sys_context('USERENV', 'CURRENT_USER', 'mc')
  , sys_context('USERENV', 'APPLICATION_FULL', 'mc')
  , sys_context('USERENV', 'SESSIONID', 'mc')
  );
  --
  create or replace table mc_dGrootboekrekening@sql
  as
  select *
  from dGrootboekrekening@mc
  ;
  insert into SYNCLOGS@sql
  ( id
  , occurrence_date
  , action_type
  , table_name
  , label
  , actions
  --
  , ip_address
  , iuid
  , date_created
  , created_by
  , created_at
  , session_created
  )
  values
  ( newid()
  , sysdateutc
  , 'C'
  , 'mc_dGrootboekrekening@sql'
  , 'Created'
  , to_char(sqlrowcount) || ' rows'
  --
  , sys_context('USERENV', 'CLIENT_IP_ADDRESS_EXTERNAL', 'mc')
  , sys_context('USERENV', 'IUID', 'mc')
  , sysdateutc
  , sys_context('USERENV', 'CURRENT_USER', 'mc')
  , sys_context('USERENV', 'APPLICATION_FULL', 'mc')
  , sys_context('USERENV', 'SESSIONID', 'mc')
  );
  --
  create or replace table mc_dBedrijf@sql
  as
  select *
  from dBedrijf@mc
  ;
  insert into SYNCLOGS@sql
  ( id
  , occurrence_date
  , action_type
  , table_name
  , label
  , actions
  --
  , ip_address
  , iuid
  , date_created
  , created_by
  , created_at
  , session_created
  )
  values
  ( newid()
  , sysdateutc
  , 'C'
  , 'mc_dBedrijf@sql'
  , 'Created'
  , to_char(sqlrowcount) || ' rows'
  --
  , sys_context('USERENV', 'CLIENT_IP_ADDRESS_EXTERNAL', 'mc')
  , sys_context('USERENV', 'IUID', 'mc')
  , sysdateutc
  , sys_context('USERENV', 'CURRENT_USER', 'mc')
  , sys_context('USERENV', 'APPLICATION_FULL', 'mc')
  , sys_context('USERENV', 'SESSIONID', 'mc')
  );
  --
  create or replace table mc_dDatum@sql
  as
  select *
  from dDatum@mc
  ;
  insert into SYNCLOGS@sql
  ( id
  , occurrence_date
  , action_type
  , table_name
  , label
  , actions
  --
  , ip_address
  , iuid
  , date_created
  , created_by
  , created_at
  , session_created
  )
  values
  ( newid()
  , sysdateutc
  , 'C'
  , 'mc_dDatum@sql'
  , 'Created'
  , to_char(sqlrowcount) || ' rows'
  --
  , sys_context('USERENV', 'CLIENT_IP_ADDRESS_EXTERNAL', 'mc')
  , sys_context('USERENV', 'IUID', 'mc')
  , sysdateutc
  , sys_context('USERENV', 'CURRENT_USER', 'mc')
  , sys_context('USERENV', 'APPLICATION_FULL', 'mc')
  , sys_context('USERENV', 'SESSIONID', 'mc')
  );
  --
  create or replace table mc_dRelatie@sql
  as
  select *
  from dRelatie@mc
  ;
  insert into SYNCLOGS@sql
  ( id
  , occurrence_date
  , action_type
  , table_name
  , label
  , actions
  --
  , ip_address
  , iuid
  , date_created
  , created_by
  , created_at
  , session_created
  )
  values
  ( newid()
  , sysdateutc
  , 'C'
  , 'mc_dRelatie@sql'
  , 'Created'
  , to_char(sqlrowcount) || ' rows'
  --
  , sys_context('USERENV', 'CLIENT_IP_ADDRESS_EXTERNAL', 'mc')
  , sys_context('USERENV', 'IUID', 'mc')
  , sysdateutc
  , sys_context('USERENV', 'CURRENT_USER', 'mc')
  , sys_context('USERENV', 'APPLICATION_FULL', 'mc')
  , sys_context('USERENV', 'SESSIONID', 'mc')
  );
  --
  create or replace table mc_dKostensoort@sql
  as
  select *
  from dKostensoort@mc
  ;
  insert into SYNCLOGS@sql
  ( id
  , occurrence_date
  , action_type
  , table_name
  , label
  , actions
  --
  , ip_address
  , iuid
  , date_created
  , created_by
  , created_at
  , session_created
  )
  values
  ( newid()
  , sysdateutc
  , 'C'
  , 'mc_dKostensoort@sql'
  , 'Created'
  , to_char(sqlrowcount) || ' rows'
  --
  , sys_context('USERENV', 'CLIENT_IP_ADDRESS_EXTERNAL', 'mc')
  , sys_context('USERENV', 'IUID', 'mc')
  , sysdateutc
  , sys_context('USERENV', 'CURRENT_USER', 'mc')
  , sys_context('USERENV', 'APPLICATION_FULL', 'mc')
  , sys_context('USERENV', 'SESSIONID', 'mc')
  );
  --
  insert into SYNCLOGS@sql
  ( id
  , occurrence_date
  , action_type
  , table_name
  , label
  , actions
  --
  , ip_address
  , iuid
  , date_created
  , created_by
  , created_at
  , session_created
  )
  values
  ( newid()
  , sysdateutc
  , 'E'
  , null
  , 'Finished'
  , null
  --
  , sys_context('USERENV', 'CLIENT_IP_ADDRESS_EXTERNAL', 'mc')
  , sys_context('USERENV', 'IUID', 'mc')
  , sysdateutc
  , sys_context('USERENV', 'CURRENT_USER', 'mc')
  , sys_context('USERENV', 'APPLICATION_FULL', 'mc')
  , sys_context('USERENV', 'SESSIONID', 'mc')
  );
  --
  create or replace table mc_FVerkoopFactuur@sql
  as
  select *
  from FVerkoopFactuur@mc
  ;
  insert into SYNCLOGS@sql
  ( id
  , occurrence_date
  , action_type
  , table_name
  , label
  , actions
  --
  , ip_address
  , iuid
  , date_created
  , created_by
  , created_at
  , session_created
  )
  values
  ( newid()
  , sysdateutc
  , 'C'
  , 'mc_FVerkoopFactuur@sql'
  , 'Created'
  , to_char(sqlrowcount) || ' rows'
  --
  , sys_context('USERENV', 'CLIENT_IP_ADDRESS_EXTERNAL', 'mc')
  , sys_context('USERENV', 'IUID', 'mc')
  , sysdateutc
  , sys_context('USERENV', 'CURRENT_USER', 'mc')
  , sys_context('USERENV', 'APPLICATION_FULL', 'mc')
  , sys_context('USERENV', 'SESSIONID', 'mc')
  );
  --
  insert into SYNCLOGS@sql
  ( id
  , occurrence_date
  , action_type
  , table_name
  , label
  , actions
  --
  , ip_address
  , iuid
  , date_created
  , created_by
  , created_at
  , session_created
  )
  values
  ( newid()
  , sysdateutc
  , 'E'
  , null
  , 'Finished'
  , null
  --
  , sys_context('USERENV', 'CLIENT_IP_ADDRESS_EXTERNAL', 'mc')
  , sys_context('USERENV', 'IUID', 'mc')
  , sysdateutc
  , sys_context('USERENV', 'CURRENT_USER', 'mc')
  , sys_context('USERENV', 'APPLICATION_FULL', 'mc')
  , sys_context('USERENV', 'SESSIONID', 'mc')
  );
  --
  create or replace table mc_Finkoopfactuur@sql
  as
  select *
  from Finkoopfactuur@mc
  ;
  insert into SYNCLOGS@sql
  ( id
  , occurrence_date
  , action_type
  , table_name
  , label
  , actions
  --
  , ip_address
  , iuid
  , date_created
  , created_by
  , created_at
  , session_created
  )
  values
  ( newid()
  , sysdateutc
  , 'C'
  , 'mc_FinkoopFactuur@sql'
  , 'Created'
  , to_char(sqlrowcount) || ' rows'
  --
  , sys_context('USERENV', 'CLIENT_IP_ADDRESS_EXTERNAL', 'mc')
  , sys_context('USERENV', 'IUID', 'mc')
  , sysdateutc
  , sys_context('USERENV', 'CURRENT_USER', 'mc')
  , sys_context('USERENV', 'APPLICATION_FULL', 'mc')
  , sys_context('USERENV', 'SESSIONID', 'mc')
  );
  --
  insert into SYNCLOGS@sql
  ( id
  , occurrence_date
  , action_type
  , table_name
  , label
  , actions
  --
  , ip_address
  , iuid
  , date_created
  , created_by
  , created_at
  , session_created
  )
  values
  ( newid()
  , sysdateutc
  , 'E'
  , null
  , 'Finished'
  , null
  --
  , sys_context('USERENV', 'CLIENT_IP_ADDRESS_EXTERNAL', 'mc')
  , sys_context('USERENV', 'IUID', 'mc')
  , sysdateutc
  , sys_context('USERENV', 'CURRENT_USER', 'mc')
  , sys_context('USERENV', 'APPLICATION_FULL', 'mc')
  , sys_context('USERENV', 'SESSIONID', 'mc')
  );
  create or replace table mc_dMaterieel@sql
  as
  select *
  from dMaterieel@mc
  ;
  insert into SYNCLOGS@sql
  ( id
  , occurrence_date
  , action_type
  , table_name
  , label
  , actions
  --
  , ip_address
  , iuid
  , date_created
  , created_by
  , created_at
  , session_created
  )
  values
  ( newid()
  , sysdateutc
  , 'C'
  , 'mc_dMaterieel@sql'
  , 'Created'
  , to_char(sqlrowcount) || ' rows'
  --
  , sys_context('USERENV', 'CLIENT_IP_ADDRESS_EXTERNAL', 'mc')
  , sys_context('USERENV', 'IUID', 'mc')
  , sysdateutc
  , sys_context('USERENV', 'CURRENT_USER', 'mc')
  , sys_context('USERENV', 'APPLICATION_FULL', 'mc')
  , sys_context('USERENV', 'SESSIONID', 'mc')
  );
  --
  insert into SYNCLOGS@sql
  ( id
  , occurrence_date
  , action_type
  , table_name
  , label
  , actions
  --
  , ip_address
  , iuid
  , date_created
  , created_by
  , created_at
  , session_created
  )
  values
  ( newid()
  , sysdateutc
  , 'E'
  , null
  , 'Finished'
  , null
  --
  , sys_context('USERENV', 'CLIENT_IP_ADDRESS_EXTERNAL', 'mc')
  , sys_context('USERENV', 'IUID', 'mc')
  , sysdateutc
  , sys_context('USERENV', 'CURRENT_USER', 'mc')
  , sys_context('USERENV', 'APPLICATION_FULL', 'mc')
  , sys_context('USERENV', 'SESSIONID', 'mc')
  );
--
  create or replace table mc_dSubadministratie@sql
  as
  select *
  from dSubadministratie@mc
  ;
  insert into SYNCLOGS@sql
  ( id
  , occurrence_date
  , action_type
  , table_name
  , label
  , actions
  --
  , ip_address
  , iuid
  , date_created
  , created_by
  , created_at
  , session_created
  )
  values
  ( newid()
  , sysdateutc
  , 'C'
  , 'mc_dSubadministratie@sql'
  , 'Created'
  , to_char(sqlrowcount) || ' rows'
  --
  , sys_context('USERENV', 'CLIENT_IP_ADDRESS_EXTERNAL', 'mc')
  , sys_context('USERENV', 'IUID', 'mc')
  , sysdateutc
  , sys_context('USERENV', 'CURRENT_USER', 'mc')
  , sys_context('USERENV', 'APPLICATION_FULL', 'mc')
  , sys_context('USERENV', 'SESSIONID', 'mc')
  );
  --
  insert into SYNCLOGS@sql
  ( id
  , occurrence_date
  , action_type
  , table_name
  , label
  , actions
  --
  , ip_address
  , iuid
  , date_created
  , created_by
  , created_at
  , session_created
  )
  values
  ( newid()
  , sysdateutc
  , 'E'
  , null
  , 'Finished'
  , null
  --
  , sys_context('USERENV', 'CLIENT_IP_ADDRESS_EXTERNAL', 'mc')
  , sys_context('USERENV', 'IUID', 'mc')
  , sysdateutc
  , sys_context('USERENV', 'CURRENT_USER', 'mc')
  , sys_context('USERENV', 'APPLICATION_FULL', 'mc')
  , sys_context('USERENV', 'SESSIONID', 'mc')
  );
  --
  create or replace table mc_fBudgetGrootboek@sql
  as
  select *
  from fBudgetGrootboek@mc
  ;
  insert into SYNCLOGS@sql
  ( id
  , occurrence_date
  , action_type
  , table_name
  , label
  , actions
  --
  , ip_address
  , iuid
  , date_created
  , created_by
  , created_at
  , session_created
  )
  values
  ( newid()
  , sysdateutc
  , 'C'
  , 'mc_fBudgetGrootboek'
  , 'Created'
  , to_char(sqlrowcount) || ' rows'
  --
  , sys_context('USERENV', 'CLIENT_IP_ADDRESS_EXTERNAL', 'mc')
  , sys_context('USERENV', 'IUID', 'mc')
  , sysdateutc
  , sys_context('USERENV', 'CURRENT_USER', 'mc')
  , sys_context('USERENV', 'APPLICATION_FULL', 'mc')
  , sys_context('USERENV', 'SESSIONID', 'mc')
  );
  --
  insert into SYNCLOGS@sql
  ( id
  , occurrence_date
  , action_type
  , table_name
  , label
  , actions
  --
  , ip_address
  , iuid
  , date_created
  , created_by
  , created_at
  , session_created
  )
  values
  ( newid()
  , sysdateutc
  , 'E'
  , null
  , 'Finished'
  , null
  --
  , sys_context('USERENV', 'CLIENT_IP_ADDRESS_EXTERNAL', 'mc')
  , sys_context('USERENV', 'IUID', 'mc')
  , sysdateutc
  , sys_context('USERENV', 'CURRENT_USER', 'mc')
  , sys_context('USERENV', 'APPLICATION_FULL', 'mc')
  , sys_context('USERENV', 'SESSIONID', 'mc')
  );
  --
  create or replace table mc_dGebruikerPerBedrijf@sql
  as
  select *
  from dGebruikerPerBedrijf@mc
  ;
  insert into SYNCLOGS@sql
  ( id
  , occurrence_date
  , action_type
  , table_name
  , label
  , actions
  --
  , ip_address
  , iuid
  , date_created
  , created_by
  , created_at
  , session_created
  )
  values
  ( newid()
  , sysdateutc
  , 'C'
  , 'mc_dGebruikerPerBedrijf'
  , 'Created'
  , to_char(sqlrowcount) || ' rows'
  --
  , sys_context('USERENV', 'CLIENT_IP_ADDRESS_EXTERNAL', 'mc')
  , sys_context('USERENV', 'IUID', 'mc')
  , sysdateutc
  , sys_context('USERENV', 'CURRENT_USER', 'mc')
  , sys_context('USERENV', 'APPLICATION_FULL', 'mc')
  , sys_context('USERENV', 'SESSIONID', 'mc')
  );
  --
  insert into SYNCLOGS@sql
  ( id
  , occurrence_date
  , action_type
  , table_name
  , label
  , actions
  --
  , ip_address
  , iuid
  , date_created
  , created_by
  , created_at
  , session_created
  )
  values
  ( newid()
  , sysdateutc
  , 'E'
  , null
  , 'Finished'
  , null
  --
  , sys_context('USERENV', 'CLIENT_IP_ADDRESS_EXTERNAL', 'mc')
  , sys_context('USERENV', 'IUID', 'mc')
  , sysdateutc
  , sys_context('USERENV', 'CURRENT_USER', 'mc')
  , sys_context('USERENV', 'APPLICATION_FULL', 'mc')
  , sys_context('USERENV', 'SESSIONID', 'mc')
  );
  --
  create or replace table mc_dMaterieelExploitatie@sql
  as
  select *
  from dMaterieelExploitatie@mc
  ;
  insert into SYNCLOGS@sql
  ( id
  , occurrence_date
  , action_type
  , table_name
  , label
  , actions
  --
  , ip_address
  , iuid
  , date_created
  , created_by
  , created_at
  , session_created
  )
  values
  ( newid()
  , sysdateutc
  , 'C'
  , 'mc_dMaterieelExploitatie'
  , 'Created'
  , to_char(sqlrowcount) || ' rows'
  --
  , sys_context('USERENV', 'CLIENT_IP_ADDRESS_EXTERNAL', 'mc')
  , sys_context('USERENV', 'IUID', 'mc')
  , sysdateutc
  , sys_context('USERENV', 'CURRENT_USER', 'mc')
  , sys_context('USERENV', 'APPLICATION_FULL', 'mc')
  , sys_context('USERENV', 'SESSIONID', 'mc')
  );
  --
  insert into SYNCLOGS@sql
  ( id
  , occurrence_date
  , action_type
  , table_name
  , label
  , actions
  --
  , ip_address
  , iuid
  , date_created
  , created_by
  , created_at
  , session_created
  )
  values
  ( newid()
  , sysdateutc
  , 'E'
  , null
  , 'Finished'
  , null
  --
  , sys_context('USERENV', 'CLIENT_IP_ADDRESS_EXTERNAL', 'mc')
  , sys_context('USERENV', 'IUID', 'mc')
  , sysdateutc
  , sys_context('USERENV', 'CURRENT_USER', 'mc')
  , sys_context('USERENV', 'APPLICATION_FULL', 'mc')
  , sys_context('USERENV', 'SESSIONID', 'mc')
  );
  --
  create or replace table mc_dActivum@sql
  as
  select *
  from dActivum@mc
  ;
  insert into SYNCLOGS@sql
  ( id
  , occurrence_date
  , action_type
  , table_name
  , label
  , actions
  --
  , ip_address
  , iuid
  , date_created
  , created_by
  , created_at
  , session_created
  )
  values
  ( newid()
  , sysdateutc
  , 'C'
  , 'mc_dActivum@sql'
  , 'Created'
  , to_char(sqlrowcount) || ' rows'
  --
  , sys_context('USERENV', 'CLIENT_IP_ADDRESS_EXTERNAL', 'mc')
  , sys_context('USERENV', 'IUID', 'mc')
  , sysdateutc
  , sys_context('USERENV', 'CURRENT_USER', 'mc')
  , sys_context('USERENV', 'APPLICATION_FULL', 'mc')
  , sys_context('USERENV', 'SESSIONID', 'mc')
  );
  --
  insert into SYNCLOGS@sql
  ( id
  , occurrence_date
  , action_type
  , table_name
  , label
  , actions
  --
  , ip_address
  , iuid
  , date_created
  , created_by
  , created_at
  , session_created
  )
  values
  ( newid()
  , sysdateutc
  , 'E'
  , null
  , 'Finished'
  , null
  --
  , sys_context('USERENV', 'CLIENT_IP_ADDRESS_EXTERNAL', 'mc')
  , sys_context('USERENV', 'IUID', 'mc')
  , sysdateutc
  , sys_context('USERENV', 'CURRENT_USER', 'mc')
  , sys_context('USERENV', 'APPLICATION_FULL', 'mc')
  , sys_context('USERENV', 'SESSIONID', 'mc')
  );
  dbms_audit.register_event('xxbus002', 'End job', natural_key => 'kopieer.sql');
end;

Invantive.Basics.InvantiveSqlException
InvantiveSystemException
ValidationException
ValidationException
   at Invantive.Basics.ValidationException..ctor(GlobalState owner, ExecutionOptions executionOptions, String messageCode, String errorMessage, String kindRequest, String localStackTrace, String nk, Exception innerException, Boolean inheritMessageCodeWhenPresent, Nullable`1 uid, Boolean isRecoverable, String poolIdentityId, String url, Nullable`1 partyUid, String partitionCode, String tableName)
   at Invantive.Basics.InvantiveSqlException..ctor(GlobalState owner, ExecutionOptions executionOptions, String validationCode, String errorMessage, String kindRequest, String stackTrace, String nk, Exception innerException, Boolean inheritMessageCodeWhenPresent, Nullable`1 uid, Boolean isRecoverable, String poolIdentityId, String partitionCode, String tableName)
   at Invantive.Data.Providers.SqlServer.SqlServerProvider.CheckAllowedColumnValue(GlobalState owner, ExecutionOptions executionOptions, Object val, Int64 rowNumber, Int32 columnNumber, DatabaseColumn databaseColumn, String fullQualifiedTableName)
   at Invantive.Data.Providers.SqlServer.SqlServerProvider.InsertBulk(GlobalState owner, ExecutionOptions executionOptions, SqlExecutionStep sqlExecutionStep, EntityFieldCollection entityFields, ResultSet inputResultSet, Nullable`1 maxBatchSizeRows, String partitionCode)
   at Invantive.Data.AnsiSqlProviderBase.CreateTable(GlobalState owner, ExecutionOptions executionOptions, SqlExecutionStep sqlExecutionStep, String applicationCode, String nonStandardizedPartitionCode, String catalogName, String schemaName, String tableName, String code, ResultSet resultSet, Nullable`1 maxBatchSizeRows)
   at Invantive.Sql.V1.DdlStatementTree.<CreateTable>d__103.MoveNext()
   at Invantive.Data.CompressedEnumerable`1.<GetEnumerator>d__10.MoveNext()
   at Invantive.Sql.V1.FirehoseResultSet.JT.MoveNext()
   at Invantive.Sql.V1.QueryPlan.WF.MoveNext()
   at Invantive.Data.ExtensionMethods.IteratorWithFinally`1.IteratorWithFinallyEnumerator.MoveNext()
   at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
   at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
   at Invantive.Sql.V1.PSqlParser.ExecuteQueryTree(GlobalState owner, ExecutionOptions executionOptions, SqlExecutionStep sqlExecutionStep, IQueryStatePerExecution queryStatePerExecution, PSqlScope scope, SqlStatementContext ctx, ISparseArray variablesRow, DatabaseColumnDefinitionCollection oldNewTriggerFields, ISparseArray oldTriggerRow, ISparseArray newTriggerRow, DatabaseColumnDefinitionCollection sourceTriggerFields, ISparseArray sourceTriggerRow, DatabaseColumn[] intoVariables, ParameterList globalParameters, BT queryPlanState)
   at LambaExecutePSqlTree49(Closure , GlobalState , ExecutionOptions , SqlExecutionStep , IQueryStatePerExecution , ISparseArray , ISparseArray , DatabaseColumnDefinitionCollection , ISparseArray , ISparseArray , DatabaseColumnDefinitionCollection , ISparseArray )
   at Invantive.Sql.V1.PSqlStatementTree.<Fetch>d__29.MoveNext()
   at Invantive.Data.CompressedEnumerable`1.<GetEnumerator>d__10.MoveNext()
   at Invantive.Sql.V1.FirehoseResultSet.JT.MoveNext()
   at Invantive.Sql.V1.QueryPlan.WF.MoveNext()
   at Invantive.Data.ExtensionMethods.IteratorWithFinally`1.IteratorWithFinallyEnumerator.MoveNext()
   at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
   at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
   at Invantive.Data.ConnectionManager.ExecuteAndCombineQueryPlansV1(GlobalState owner, ExecutionOptions executionOptions, SqlExecutionStep sqlExecutionStep, QueryPlan[] queryPlans, String sqlStatement, ParameterList parameters)
   at Invantive.Data.ConnectionManager.ExecuteProviderPassthroughSqlActionTable(GlobalState owner, ExecutionOptions executionOptions, String actionSql, ParameterList parameters, SqlExecutionStep sqlExecutionStep, String callSafeNameOverrule)
   at Invantive.Data.ConnectionManager.JR(GlobalState , ExecutionOptions , SqlExecutionStep , String , ParameterList , String )
   at Invantive.Data.ActionProceduresBase.PassthroughSqlActionTable(GlobalState owner, ExecutionOptions executionOptions, String actionSql, ParameterList parameters)
   at Invantive.Producer.Windows.Forms.QueryToolResults.ISY(GlobalState , ExecutionOptions , IProgressNotifier , String , ParameterList , Boolean , Boolean , Boolean , Boolean )
   at Invantive.Producer.Windows.Forms.QueryToolResults.ESY(GlobalState , ExecutionOptions , String , String , Boolean )
   at Invantive.Producer.Windows.Forms.QueryToolResults.ExecuteStatements(GlobalState owner, ExecutionOptions executionOptions)
   at Invantive.Producer.Windows.Forms.QueryToolDocumentViewModel.VP.IP.MoveNext()
   at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
   at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
   at System.Runtime.CompilerServices.AsyncMethodBuilderCore.MoveNextRunner.Run()
   at System.Threading.Tasks.AwaitTaskContinuation.RunOrScheduleAction(Action action, Boolean allowInlining, Task& currentTask)
   at System.Threading.Tasks.Task.FinishContinuations()
   at System.Threading.Tasks.Task`1.TrySetResult(TResult result)
   at System.Runtime.CompilerServices.AsyncTaskMethodBuilder`1.SetResult(TResult result)
   at System.Runtime.CompilerServices.AsyncTaskMethodBuilder`1.SetResult(Task`1 completedTask)
   at Invantive.Producer.Windows.Forms.QueryToolDocumentViewModel.FP.MoveNext()
   at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
   at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
   at System.Runtime.CompilerServices.AsyncMethodBuilderCore.MoveNextRunner.Run()
   at System.Threading.Tasks.AwaitTaskContinuation.RunOrScheduleAction(Action action, Boolean allowInlining, Task& currentTask)
   at System.Threading.Tasks.Task.FinishContinuations()
   at System.Threading.Tasks.Task`1.TrySetResult(TResult result)
   at System.Threading.Tasks.Task.DelayPromise.Complete()
   at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
   at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
   at System.Threading.TimerQueueTimer.CallCallback()
   at System.Threading.TimerQueueTimer.Fire()
   at System.Threading.TimerQueue.FireNextTimers()
   at Invantive.Data.Providers.SqlServer.SqlServerProvider.CheckAllowedColumnValue(GlobalState owner, ExecutionOptions executionOptions, Object val, Int64 rowNumber, Int32 columnNumber, DatabaseColumn databaseColumn, String fullQualifiedTableName)
   at Invantive.Data.Providers.SqlServer.SqlServerProvider.InsertBulk(GlobalState owner, ExecutionOptions executionOptions, SqlExecutionStep sqlExecutionStep, EntityFieldCollection entityFields, ResultSet inputResultSet, Nullable`1 maxBatchSizeRows, String partitionCode)

mds · 30 april 2024 om 12:19

Het probleem is verder geanalyseerd:

--
-- Op pgAdmin levert de volgende query enkele honderden rijen op ver in het verleden
-- (rond jaar 1) of in de toekomst (jaar 9000+).
--
select *
from   "fJournaalpost"
where  "documentDatum" > now()::date + interval '1 years'
       or
	   "documentDatum" < now()::date - interval '200 years'

Blijkbaar bevat metacom veel documentdatums in het verre verleden en verre toekomst. Als vervolgstap is geprobeerd deze data te verwerken en te kijken of een foutmelding optreedt:

--
-- Hiervoor maken we eerst een lege laadtabel met de juiste velden:
--
create or replace table mc_fjournaalpost@sql
as
select *
from   fJournaalpost@mc
where  false

--
-- Vervolgens probeer we rijen te laden.
--
begin
  for r
  in
  ( execute native 'select *'
    || 'from   "fJournaalpost"'
    || 'where  1=1'
    || 'and    "documentDatum" < now()::date - interval ''200 years'''
    datacontainer 'mc'
  )
  loop
    begin
      insert into mc_fjournaalpost@sql
      ( documentDatum
      )
      values
      ( r.documentDatum
      );
    exception
      when others
      then
        dbms_output.put_line('FAIL:' || to_char(r.documentDatum, 'YYYYMMDDHH24MISS'));
    end;
  end loop;
end;

Dit drukt enkele honderden rijen af in dbms_output. Uiteindelijk blijkt de foutmelding op te wekken met bijvoorbeeld:

insert into mc_fjournaalpost@sql
( documentDatum
)
values
( to_date('10220110000000', 'YYYYMMDDHH24MISS')
)

De foutmelding is dan:

itgenspr113: The row.
0 = 01-01-1753, 1 = 31-12-9999.

Ook de volgende statements leiden tot een foutmelding (merk op dat documentDatum een datetime2-kolom is):

set invantive-sql-correct-invalid-date@sql true;

insert into mc_fjournaalpost@sql
( documentDatum
)
values
( to_date('10220110', 'YYYYMMDD')
)

Door het vertalen van documentDatum buiten Gregoriaanse kalender naar 1-1-1753 werkt het zonder fouten:

begin
  for r
  in
  ( execute native 'select *'
    || 'from   "fJournaalpost"'
    || 'where  1=1'
    || 'and    "documentDatum" < now()::date - interval ''200 years'''
    datacontainer 'mc'
  )
  loop
    begin
      insert into mc_fjournaalpost@sql
      ( documentDatum
      )
      values
      ( case when r.documentDatum < to_date('17530101', 'YYYYMMDD') then to_date('17530101', 'YYYYMMDD') else r.documentDatum end
      );
    end;
  end loop;
end;

Oplossing is de kopieerquery te herformuleren als:

create or replace table mc_fjournaalpost@sql
as
select * except documentDatum
,      case
       when documentDatum < to_date('17530101', 'YYYYMMDD')
       then to_date('17530101', 'YYYYMMDD')
       else documentDatum
       end
       documentDatum
from   fJournaalpost@mc

De itgenspr113 is eigenlijk onterecht; datetime2 ondersteunt datums vanaf 1-1-0001.

forums · 30 april 2024 om 12:41

Dank voor de melding.

De controle itgenspr113 op SQL Server voor datetime2-kolommen is verwijderd vanaf release 24.0.162. Deze controle is ergens rond januari 2022 geintroduceerd tijdens de overstap van standaard datetime naar datetime2 om zo problemen bij verdere verwerking te voorkomen. Er zijn echter geen problemen gebleken in twee jaar tijd en de preventieve controle voegt dus geen waarde toe qua bedrijfszekerheid.

De tekst van de foutmelding is gecorrigeerd en zal luiden:

The row #… for column ‘…’ at position … in table ‘…’ contains a date/time value ‘…’ beneath the minimum allowed value on SQL Server of ‘…’.

system · 3 mei 2024 om 12:41

Dit topic is 3 dagen na het laatste antwoord automatisch gesloten. Nieuwe antwoorden zijn niet meer toegestaan.

Topic		Antwoorden	Weergaven
Itgenrst013 melding op datum in PostgreSQL Vragen invantive-sql , postgresql	3	212	11 april 2024
Itgenase094: De tabel kan niet worden gemaakt door syntax error Vragen	5	211	11 april 2024
Itgenase094: The table can not be created Vragen invantive-query-tool , exact-online , microsoft-sql-server	1	124	19 juli 2023
Filter op PostgreSQL geeft foutmelding Vragen invantive-sql , postgresql , metacom	2	239	8 mei 2024
Bijwerken van artikelvelden tussen Exact Online administraties geeft itgengpr040 Vragen exact-online , invantive-sql	3	189	11 april 2024

Itgenase094: "The table can not be created" door itgenspr113 op SQL Server

Gerelateerde topics