July 9, 2009 at 2:45 pm
Hoping someone can help.
My situation is I've inherited an application which allows users the ability to use SQL Server bcp to export table data from one database and import the data back into another database of the same structure, but with a different name. Bat files are the underlying means used to perform the bcp's, and all bcp's are done via views.
Recently we finally moved several databases from 2000 to 2005, using backup and restore. We are now having problems using the application to export the data and import, as we have in the past. The import consistently fails on two tables. The error message for both tables is:
SQL State = 37000, NativeError = 4816
Error = [Microsoft][ODBC SQL Server Driver][SQL Server] Invalid column type from bcp client for colid 11
SQL State = 37000, NativeError = 4816
Error = [Microsoft][ODBC SQL Server Driver][SQL Server] Invalid column type from bcp client for colid 12
In each instance, the colid identified is a bit NOT NULL field.
If I use Management Studio and change the bit field property on the underlying tables from NOT NULL to NULL, then save it, and then change it back to NOT NULL and save again, I can successfully import the data back into the database without any problems.
If I use scripts we have to build a database from scratch, and then try the data import, it also works fine--no problems.
I thought it might be a compatibility level issue since the "restored from 2000" dbs are level 80 and the db created in 2005 from scratch is 90. I changed the compatibility level of the restored dbs to 90 and tried the import again, and it still failed.
Finally, if I bypass the views, and import directly into the underlying table, there's no problem.
Here are the bcp commands that fail
bcp dbname.dbo.block_types_v out block_types_2005.dat -e block_types_2005.err -N -S slbmdb02 -T -E
bcp dbname.dbo.block_types_v in block_types_2005.dat -e block_types_2005.err -N -S slbmdb02 -T -E
bcp dbname.dbo.input_subblock_v out input_subblock_2005.dat -e input_subblock_2005.err -N -S slbmdb02 -T -E
bcp dbname.dbo.input_subblock_v in input_subblock_2005.dat -e input_subblock_2005.err -N -S slbmdb02 -T -E
Sorry for the long post, but this has really got me stumped. Again, I'm hoping someone out there may have run into a similar problem and solution.
November 18, 2009 at 6:04 pm
Instead of using -n (native) datatype, try using -c (character) datatypes.
Amol
Amol Naik
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply