Wrox Programmer Forums
| Search | Today's Posts | Mark Forums Read
SQL Server 2000 General discussion of Microsoft SQL Server -- for topics that don't fit in one of the more specific SQL Server forums. version 2000 only. There's a new forum for SQL Server 2005.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2000 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
  #1 (permalink)  
Old September 26th, 2005, 02:16 AM
Friend of Wrox
Points: 6,664, Level: 34
Points: 6,664, Level: 34 Points: 6,664, Level: 34 Points: 6,664, Level: 34
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
Join Date: Jan 2004
Location: Sydney, NSW, Australia.
Posts: 1,870
Thanks: 12
Thanked 20 Times in 20 Posts
Send a message via AIM to mat41
Default Problem Exporting Data...


A client is trying to export the table into another database but receives the following error:
"The value is not consistent with the data type or length of the
column." So far I can't replicate the issue on any of my test
environments. (this is not the problem tho... =))

However, when you do try to copy the row and paste it at the end of the SAME table. you the the same error:
"The value you enetered is not consistent with the data type or
length of the column."

And I find this bizarre because 1) it IS already in the database with no problems and 2) the field is of type Text - which should accommodate very long text values.

I also found this same problem occuring in fields of type VarChar.

Also, in trying to solve the problem, I found that the problem is caused by a CHR(13) [vbCr] and a CHR(10) [vbLf] at the end of the text, and deleting them allows you to paste the rebellious row (but doesn't make the export problem go away). However, I also found a few rows that end with those characters but does NOT generate the error when copied and pasted.

MS SQL Server database bug? What can I do to solve it?

A sample of the table is available as a database backup from http://users.tpg.com.au/adslnl7k/gw/test.zip

Thanks !!

Wind is your friend
Wind is your friend
  #2 (permalink)  
Old September 29th, 2005, 08:35 PM
Friend of Wrox
Join Date: Aug 2004
Location: Orange County, CA, USA.
Posts: 385
Thanks: 0
Thanked 0 Times in 0 Posts

There are situations where you can load data in differen methods, for example bcp vs insert. The data will fail if you use an insert but the bcp has looser acceptance logic and thus will accept the data. When I have come across this it was always because of a strange character. Display the line you know that fails on it's acsii value for every character in the line. Or try to insert the line into itself but field at a time to identify what field is the problem. The error your getting is not the correct error. Its not a length issue its likely a strange character issue. The data was likely brought in with a etl process, bcp, or some non insert type process that circumvented the logic in place the way your currently adding the same data in. Find the strange character, change it to a blank or delete it and you should be able to do what you want to do. After you identify what character that is write some code to look for the same value in other places.

Hope this helps

Similar Threads
Thread Thread Starter Forum Replies Last Post
Problem in exporting data in a gridview to MSExcel Rashmi_Dubey ASP.NET 2.0 Basics 12 July 15th, 2009 04:57 PM
Problem in exporting data into pdf santhoshsanjeevi J2EE 0 April 3rd, 2008 06:15 AM
Problem in exporting data from datagrid to Excel deb_kareng ASP.NET 2.0 Professional 4 August 4th, 2007 09:21 AM
Exporting data to excel from datagrid, Problem. swadhinm ASP.NET 1.x and 2.0 Application Design 0 May 31st, 2005 01:44 AM
Problem in exporting data to PDF vasansrini Crystal Reports 1 December 10th, 2004 11:48 AM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.