The SQL types used for float and double observations with intentionally missing values are too small + enhance error message
-
[varchar](6)
to replace[float](24)
and -
[varchar](10)
to replace[float](53)
are too small to hold the allowed float or double values. MS SQL Server defines the following:
n value | Precision | Storage size |
---|---|---|
24 | 7 digits | 4 bytes |
53 | 15 digits | 8 bytes |
The 'Precision' indicates the number of significant figures. Therefore, the maximum number of characters necessary for the storage of the string can be obtained from the extreme negative numbers:
-
[float](24)
:-1.234567E-38
-->[varchar](13)
-
[float](53)
:-1.23456789012345E-308
-->[varchar](22)
Please correct https://gitlab.com/sis-cc/.stat-suite/dotstatsuite-core-data-access/-/blob/develop/DotStat.Db/DbExtensions.cs#L615 and other places as necessary.
- The user received the following unfriendly error message (see details below): 'Received an invalid column length from the bcp client for colid 12'. This technical error complicates the trouble shooting because the error message is not nicely reformulated for easy user comprehension. Please catch this error as described here or here to reformulate the message to:
The component 'ccc' at observation 'ooo' (for CSV file imports located at row 'rrr') contains a value with a length greater than 'lll' characters
.
- a) at bulk copy of the content of data files into staging table
- b) at bulk copy of the content of ref. metadata files into staging table
- Migration script to update data type of affected and already existing primary measure columns in database
Example files
- stuctures: dataflow-BE2_DF_NICP_1.0__withNaNAnnotations.xml
- data with long numbers: BE2_DF_NICP_1.0+all.csv
Original issue from NBB
I can’t seem to load data for the dataflow NICP.To explain what we did:
- We deleted all the artefacts for all the spaces in the DEV environment: o Therefore we used the delete structure option for the dsd’s in dlm and afterwards the cleanup/dsd option in swagger o Afterwards we deleted all the other existing structures which were still in the spaces o We perform an indexation to be sure nothing else remained hanging in the DE
- I added the annotation for SUPPORT_INTENT_MISS_VALUES in all the dsd’s so also for DS_CP which is used by dataflow DF_NICP
- I loaded all the artefacts which we downloaded from uat and tried to load also the data for all the DSD’s and this for the moment only for staging
- For several load’s there was no problem, but for the load of DF_NICP I encountered following problem:
I tried this first with the datafile BE2,DF_NICP,1.0+all.csv and afterwards with BE2,DF_NICP,1.0+all_TEST2.csv because I change in this file the obs_value on line 13914 to NaN because I thought that it could be a problem that the obs_Value was filled with blanks.[dataflow-BE2_DF_NICP_1.0__withNaNAnnotations.xml](/uploads/fe4e7c99cc42d042b414fefbf11545f3/dataflow-BE2_DF_NICP_1.0__withNaNAnnotations.xml[BE2_DF_NICP_1.0+all.csv](/uploads/25837cbdbe812faede3fc9026c0ac3f5/BE2_DF_NICP_1.0+all.csvBE2_DF_NICP_1.0+all_TEST2.csv