Solved

SQL Server 7 Text Truncation on Export

Posted on 2014-10-31
3
229 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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
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.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how the fundamental information of how to create a table.

820 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