Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > SQL Server > SQL Server 2000 > SQL Server 2000
Password Reminder
| FAQ | Members List | 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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Thread Tools Search this Thread Display Modes
  #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
Reply With Quote
  #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

Reply With Quote

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

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

All times are GMT -4. The time now is 10:56 AM.

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