August 20, 2007 at 5:44 pm
Having unloaded a table (containing column "Contents long binary") from a Sybase SQLAny db, I am now trying to bulk insert the data into a SS2K5 db table with the same schema ("Contents" data type is now "varbinary(max)").
Data exported from Sybase SQL Any db using the following SQL:
Select * from DBA."DocumentBinaries" ;
OUTPUT TO 'E:\TW\KMCA\Scripts\SQL Server\Data\DocumentBinaries_Export.DAT' FORMAT ASCII DELIMITED BY '\t' QUOTE '' ;
Here's the Import SQL:
BULK
INSERT DocumentBinaries from 'C:\TW\KMCA\Scripts\SQL Server\Data\DocumentBinaries_Export.DAT' WITH ( KEEPIDENTITY , FIELDTERMINATOR = '\t' , ROWTERMINATOR ='\n', MAXERRORS = 100 ) ;
The following errors result:
Msg 4864, Level 16, State 3, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1, column 2 (contents).
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.
Msg 7330, Level 16, State 2, Line 1
Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".
DocumentBinaries is the only table with an Image/varbinary column in a group of over 300 being migrated, and the only tbale I can get the above export/import statement combo to work for.
Takauma
August 21, 2007 at 8:14 am
One very small hair split - using drive mappings rather than fully explicit paths. You output to 'E:...' but import form 'C:...' - i trust there are intermediate steps you are not boring us with.
Have you 'examined' the actual data file with a text editor (e.g., textpad) to verify that the type mismatch or invalid character is in fact a legitimate complaint? Is it possible that you have a terminator embedded in the text, and you didn't check for it first? that could throw everything off, no?
August 21, 2007 at 6:37 pm
Yes looked at all those and other. I ended up with a format file which is documated as being the only way to deal wth varbinary(max) data in bcp and BULK INSERT. Working okay now.
Takauma
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply