Solved

SQL Server 7 Text Truncation on Export

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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Suggested Solutions

Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

920 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

18 Experts available now in Live!

Get 1:1 Help Now