I am trying to import data from a flat file into a decimal(9,2) column. Here is the decimal data in each record of the flat file:
123456789
There is no decimal point in the field but last two positions represent the right of the decimal point, some fields have leading zeros)
I have discovered the following format file (extract) appears to do the import and place the decimal properly in the table column (1234567.89). Note the table column number and name are the same:
snip
11 SQLCHAR 0 7 "" 11 FieldName ""
12 SQLCHAR 0 2 "" 11 FieldName ""
snip
But bcp throws this error after a few seconds:
snip
C:\>bcp DatabeseName..Tablename in DataFileName.dat -fFormatFile.fmt -S.\SQLExpress -T
Starting copy...
SQLState = HY000, NativeError = 0
Error = [Microsoft][SQL Native Client]Unspecified error occurred
Connection may have been terminated by the server.
SQLState = 08S01, NativeError = 233
Error = [Microsoft][SQL Native Client]Shared Memory Provider: No
he other end of the pipe.
SQLState = 08S01, NativeError = 233
Error = [Microsoft][SQL Native Client]Communication link failure
SQLState = 08S01, NativeError = 0
Error = [Microsoft][SQL Native Client]Communication link failure
... (last two line repeated until abort)
BCP copy in failed
snip
For some reason the error does not occur with format files with different field formats. However other formats do not import properly.
Can someone identify this error or perhaps tell me how the above decimal format can be imported from a flat file as described? Any help is really appreciated as I have wasted several days already...TIA.
Sturdy
sturdy wrote:
I am trying to import data from a flat file into a decimal(9,2) column. Here is the decimal data in each record of the flat file: 123456789
There is no decimal point in the field but last two positions represent the right of the decimal point, some fields have leading zeros)
I have discovered the following format file (extract) appears to do the import and place the decimal properly in the table column (1234567.89). Note the table column number and name are the same:
snip
11 SQLCHAR 0 7 "" 11 FieldName ""
12 SQLCHAR 0 2 "" 11 FieldName ""
snip...
In the format file the destination column ordinal are supposed to be unique, except when the ordinal is 0. Hence the format file like this is wrong and BCP should have complained about it. Apparently, there must be a bug in the BCP format file parser and the parser misses the defect in the format file instead of failing the statement. Since the shape of TDS stream that comes to the SQL Engine from BCP is not what is expected, unltimately the statement still fails, but much later - when, possibly, much more serious damage is done, including silent corruptions of the data in the database. Thanks for discovering this.
Back to your original question - the bulk load format file is unable to provide you with the functionality you need. Essentially what you require is to specify an additional scaling factor for your integer input data. One suggestion that comes to mind - use SSIS (aka DTS). This is exactly the kind of a problem the SSIS/DTS was originally intended for.
|||Thanks Tengez. At least that explains why I was having so much trouble ;-) The bcp/format file documentation is horrendous.
It is strange though, that the column numbers as I described does place the decimal correctly.
Sturdy
