db_lib error when doing select *?

[david spaisman]

Hello:

This problem is the continuation of a problem we have been having moving a database from one server to another using transfer manager and the transfer manager dying on a specific table. I believe there is a problem with the table on the source server. The prior posting text is below. This problem is occurring under MSSQL 6.5 sp 4.

In trying to get the data from the problem table out, we get a dr watson error when we try to bcp the data out.

When we try to use insert select it works fine as the data gets moved into the new table correctly with the problem table in one database to a precreted table in another database on the same server.

When a select count is done, the count of the data is returned.

But when s select * from is used, the following error occurs:

DB-Library: Attempt to convert data stopped by syntax error in source field.
DB-Library: Attempt to convert data stopped by syntax error in source field.
DB-Library: Possible network error: Bad token from SQL Server: Datastream processing out of sync.
Net-Library error 0:

DB-Library Process Dead - Connection Broken

Using select * from x table into ...., produces the same error when select * is done on the new table.

We tried checktable against the table and did a checkdb/newalloc as well against all of the databaase tables and no errors appear.

We also dropped, and dropped and recreated index, and either way, we still got the same error message (see above) when we did a select * ....

Does nay one have any suggestions as to why this is occurring? Any suggestions to get the data from the problem table in the source database
to the destination database? BCP doesn't work as when we tryto get a bcp out, we get the dr watson error on the source database(see above).

Any suggestions will be really appreciated. Thanks.

David Spaisman






---------------------------------------------------------------------
I am running on mssql 6.5, sp4. We have been trying to use EM transfer
manager to move one test database on one server to another database on
another server.
We are dealing with 135 tables on this database. The transfer works up
until about the 80th atble and then just dies but the scheduled task says
it failed and check error log. The transfer creates the tables on the
destination database but only loads the data until this one table.

We use all of the options in EM Transfer manager which are st as defaults.

THere is no one on the source or destination databases locking this table.

Other smaller databases were successfully transferred from one database on
one server to the other database on the other server without any problems
today and yesterday.

Has any one run across something like this?

THanks.

[Donnie]

The only thing I can find is:

http://msdn.microsoft.com/library/sdkdoc/sql/2_err002_3.htm (Essentially the 'Bad token' error indicates a failure of DB-Library to interpret the Tabular Data Stream sent by SQL Server. Which sounds really bad. The two causes given are corrupted data and network errors that DB-Library doesn't recognize.)


You say you get the errors when you do 'select * from...'. An off-the-cuff suggestion is to try to select one column at a time (i.e., 'select col1 from...', 'select col2 from...', etc.) to see if one particular column seems to be a problem. Or a few columns.

If that points to a bad column, see if you can select SOME rows from that column (use SET ROWCOUNT or filter out records with the WHERE clause). All of this is assuming that the problem is a few corrupted records. You are looking for a way to isolate any bad records so you can perhaps devise a plan to copy out data around them.

It's a desparate and potentially time-consuming approach. For instance, it could amount to isolating, say, 200 (or 2000, or...) records (identifiable perhaps by the primary key) and finding a way to select all of the other records. The data may or may not be worth it.

Good luck,
Donnie

[Donnie]

On the other hand, check out Craig's response to Steve Jimmo's "10008 Error" post (it's just a few messages before yours).

If it really is a network problem, have you tried running bcp directly on the server while saving the output file on the same server?


------------
david spaisman at 9/15/99 2:58:41 PM

Hello:

This problem is the continuation of a problem we have been having moving a database from one server to another using transfer manager and the transfer manager dying on a specific table. I believe there is a problem with the table on the source server. The prior posting text is below. This problem is occurring under MSSQL 6.5 sp 4.

In trying to get the data from the problem table out, we get a dr watson error when we try to bcp the data out.

When we try to use insert select it works fine as the data gets moved into the new table correctly with the problem table in one database to a precreted table in another database on the same server.

When a select count is done, the count of the data is returned.

But when s select * from is used, the following error occurs:

DB-Library: Attempt to convert data stopped by syntax error in source field.
DB-Library: Attempt to convert data stopped by syntax error in source field.
DB-Library: Possible network error: Bad token from SQL Server: Datastream processing out of sync.
Net-Library error 0:

DB-Library Process Dead - Connection Broken

Using select * from x table into ...., produces the same error when select * is done on the new table.

We tried checktable against the table and did a checkdb/newalloc as well against all of the databaase tables and no errors appear.

We also dropped, and dropped and recreated index, and either way, we still got the same error message (see above) when we did a select * ....

Does nay one have any suggestions as to why this is occurring? Any suggestions to get the data from the problem table in the source database
to the destination database? BCP doesn't work as when we tryto get a bcp out, we get the dr watson error on the source database(see above).

Any suggestions will be really appreciated. Thanks.

David Spaisman






---------------------------------------------------------------------
I am running on mssql 6.5, sp4. We have been trying to use EM transfer
manager to move one test database on one server to another database on
another server.
We are dealing with 135 tables on this database. The transfer works up
until about the 80th atble and then just dies but the scheduled task says
it failed and check error log. The transfer creates the tables on the
destination database but only loads the data until this one table.

We use all of the options in EM Transfer manager which are st as defaults.

THere is no one on the source or destination databases locking this table.

Other smaller databases were successfully transferred from one database on
one server to the other database on the other server without any problems
today and yesterday.

Has any one run across something like this?

THanks.