Solved

SQL Server 7 Text Truncation on Export

Posted on 2014-10-31
3
214 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:
ScottPletcher earned 500 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

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.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how the fundamental information of how to create a table.

771 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now