Solved

SQL Server 7 Text Truncation on Export

Posted on 2014-10-31
3
224 Views
Last Modified: 2014-11-03
Hi,

I'm migrating a legacy sql 7/VB app... Several Text fields defined length 16, hold plain and rtf tagged text variously.

It appears that the rich text field in the VB app successfully display the text,  plain or rtf,  at its full length (>1000 chars) as entered, and can be copied manually via the Windows clipboard.

However when I export using Dts,  or access the SQL db using Access 2003 the stored text is consistently truncated to <255 characters - I've seen articles referring to a 255 character limit requiring specific VBA  syntax to workaround when storing text in SQL server older versions,  and if as it appears the same issue applies to DTS export, wonder if anyone knows of a workaround on the backend ? or conversion tool compatible with SQL Server 7 ? (plenty for newer versions but cant find one that connects to SS7).

I've seen another article which refers to special characters causing text processing issues which could be relevant with the rtf tags being present. But seems less likely.

Any guidance or suggestions gratefully received...
0
Comment
Question by:Ringah
  • 2
3 Comments
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 500 total points
ID: 40416011
DTS creates a package.  I think by default the length was set to 255, but you can override that.  Sorry, I don't remember the details of anything in SQL 7.0 and I don't have any instance to try it on.
0
 

Author Comment

by:Ringah
ID: 40416759
Thanks Scott,  will dig out info on that from technet,  makes sense that there's a way around as this ver of SQL was heavily text based...
0
 

Author Comment

by:Ringah
ID: 40419278
After some testing was able to Dts transform in the data export wizard to CSV... The Dts package was defaulting to 255...thanks for pointing this out...
0

Featured Post

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Whether you've completed a degree in computer sciences or you're a self-taught programmer, writing your first lines of code in the real world is always a challenge. Here are some of the most common pitfalls for new programmers.
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question