Hi,
We are in process to test SQL Server 2005 migration; everything seems to work very well,
except one job that transfer data between two databases using BCP tool.
In fact the BCP fail only for one table that have ntext column, If I use the table directly in BCP OUT/IN command it works fine, but if I use a View it fail (I'm using view because the ORDER of columns in target database could be different than source database).
This is the error I receive:
SQLState = S1000, NativeError = 606
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Metadata inconsistency. Filegroup id 0 specified for table '' does not exist. Run DBCC CHECKDB or CHECKCATALOG.
Note : If I change the ntext column to nvarchar(max) it works very well
Is this is a known bug ?
Do I have to change my ntext/text columns to nvarchar(max)/varchar(max) types ?
Any advice is welcome
Thank you.
I have repro-ed the proble. However I have a workaround.
Try BCP IN the data from the BCP file into a table with the same column order as the view, and it will work.
Now create a view on this table. This will resolve your problem.
|||We have the same problem. The problem with the workaround is that it is very inefficient. If you wanted to do this same thing on tables that have millions of rows, it means you now have to create a table, load the new table and then transfer the data from this new table to the one you want, and delete the new table, instead of using a view like you could in SQL 2000, SQL 7.5 and SQL 6.5. Not only is that a lot slower, but it consumes a lot more disk space (assuming your tables with Text or image data contain lots of data)
It seems like an obvious bug to me. The documentation for BCP has always allowed you to specify a view instead of a table. I can't image why anyone would design this to not work for image or text data in SQL 2005.
