Page 1 of 1

BaseStationImporter stops after 1000 rows

Posted: Tue Feb 06, 2018 8:04 pm
by Datamix
I tested the BaseStationImporter utility included in preview version 04-FEB-18.

Unfortunately the importer stops after 1000 rows.

Code: Select all

BaseStation Import
  Source:    [SQLite] Database.sqb
  Target:    [SqlServer] Server=CONNECTION_STRING (apply schema)
  Aircraft:  Import / Update
  Locations: Import / Update
  Sessions:  Import / Update
  Flights:   Import / Update 0001-01-01 to 9999-12-31

 [####################] [1 / 1] [100.0%] 00:00:00  Applying schema
 [####################] [1 / 1] [100.0%] 00:00:00  Importing locations
 [####################] [102 / 102] [100.0%] 00:00:00  Importing sessions
 [#                   ] [1'000 / 18'348] [5.5%] 00:02:30  Importing aircraft
Caught exception: System.Data.SqlClient.SqlException (0x80131904): String or binary data would be truncated.
The data for table-valued parameter "@BulkAircraft" doesn't conform to the table type of the parameter. SQL Server error is: 8152, state: 10
The statement has been terminated.
   bei System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   bei System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   bei System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   bei System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   bei System.Data.SqlClient.SqlDataReader.TryConsumeMetaData()
   bei System.Data.SqlClient.SqlDataReader.get_MetaData()
   bei System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption)
   bei System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest)
   bei System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)
   bei System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
   bei System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
   bei System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
   bei System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
   bei Dapper.SqlMapper.ExecuteReaderWithFlagsFallback(IDbCommand cmd, Boolean wasClosed, CommandBehavior behavior) in C:\projects\dapper\Dapper\SqlMapper.cs:Zeile 1053.
   bei Dapper.SqlMapper.QueryMultipleImpl(IDbConnection cnn, CommandDefinition& command) in C:\projects\dapper\Dapper\SqlMapper.cs:Zeile 1045.
   bei VirtualRadar.Plugin.SqlServer.BaseStationDatabase.<>c__DisplayClass105_1.<UpsertManyAircraft>b__0(ConnectionWrapper wrapper)
   bei VirtualRadar.Plugin.SqlServer.BaseStationDatabase.PerformInConnection(Action`1 action)
   bei VirtualRadar.Plugin.SqlServer.BaseStationDatabase.UpsertManyAircraft(IEnumerable`1 upsertAircraft)
   bei BaseStationImport.BaseStationImporter.LoadOrImportAircraft()
   bei BaseStationImport.BaseStationImporter.ProcessAircraft()
   bei BaseStationImport.BaseStationImporter.Import()
   bei BaseStationImport.CommandRunner_Import.Run()
   bei BaseStationImport.Program.Main(String[] args)
ClientConnectionId:4c9aee04-f803-42ef-9d61-cbdf3621aeb2
Fehlernummer (Error Number):8152,Status (State):10,Klasse (Class):16

Full details have been recorded in the log
From BaseStationImporter.cs

Code: Select all

/// <summary>
/// Gets or sets the number of aircraft records to upsert at a time.
/// </summary>
public int AircraftPageSize { get; set; } = 1000;

/// <summary>
/// Gets or sets the number of flight records to copy at a time.
/// </summary>
public int FlightPageSize { get; set; } = 1000;
Is there something wrong in the procedure or do I overlook something?

Re: BaseStationImporter stops after 1000 rows

Posted: Thu Feb 08, 2018 12:53 am
by agw
One of the differences between SQL Server and SQLite is that you can tell SQLite that a column is (say) a variable-length text column of up to 10 characters and then you can stick a date in it, or a string of 11 characters. It has a pretty loose concept of types.

SQL Server, on the other hand, has strongly typed columns and if you say a column is a varchar(10) and then try to write an 11 character string into it it'll either truncate (if it's a parameter to a stored procedure) or it'll throw the exception you're getting. The text field lengths started off at the values that Kinetic originally had in their BaseStation.sqb declaration and I think I bumped a couple of them up to try to avoid this, but you have a field somewhere in BaseStation.sqb that exceeds the limits and it's causing the crash.

I'll take a look at modifying the importer so that it truncates everything before it reaches the database.

Re: BaseStationImporter stops after 1000 rows

Posted: Thu Feb 08, 2018 11:58 am
by Datamix
Yes thats true I didn't thought about this.

Re: BaseStationImporter stops after 1000 rows

Posted: Wed Apr 18, 2018 8:29 pm
by Datamix
How can I troubleshot this issue exactly? I could not figure out which row is affected. Even the new vesion of the importer stop after 1000 rows.

Code: Select all

 [####################] [1 / 1] [100.0%] 00:00:00  Applying schema
 [####################] [1 / 1] [100.0%] 00:00:00  Importing locations
 [####################] [172 / 172] [100.0%] 00:00:00  Importing sessions
 [                    ] [1'000 / 20'100] [5.0%] 00:00:18  Importing aircraft
Caught exception: String or binary data would be truncated.
The data for table-valued parameter "@BulkAircraft" doesn't conform to the table type of the parameter. SQL Server error is: 8152, state: 10
The statement has been terminated.
Full details have been recorded in the log

Re: BaseStationImporter stops after 1000 rows

Posted: Sat Apr 28, 2018 3:44 pm
by agw
There isn't a new version yet :) I'll be returning to the SQL Server preview in a bit.

Re: BaseStationImporter stops after 1000 rows

Posted: Sun Apr 29, 2018 5:00 pm
by Datamix
agw wrote:
Sat Apr 28, 2018 3:44 pm
There isn't a new version yet :) I'll be returning to the SQL Server preview in a bit.
I thought there already a new version :oops: Sorry

Re: BaseStationImporter stops after 1000 rows

Posted: Wed May 16, 2018 5:04 pm
by Datamix
Works with version 12-MAY-2018 - thank you.