Procedure questions - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Procedure questions

Author

Topic

snow12
Yak Posting Veteran

74 Posts

Posted - 2008-05-16 : 14:59:01

Hello:

There are Some ntext fields have char > 4000
I need a prodecure to
1. check all table to find all ntext field.
2. check the length of those fields found
3 if the length of ntext is Null, do nothing.
if the length of ntext < 4000, pull the data to Test table.
If the length of ntext > 4000, truncate the string to 4000, pull the data to the Test table

table: TitleOne
id title Description
1 title ntest < 4000

table: TitleTwo
id title DescriptionOne DescriptionTwo DescriptioinThree
1 title ntext = 8000 ntext = 8000 ntext = 3000

tabel: Test
Column Name: Table_Name Col_Name id Segment_nbr Long_Seg
Data type: varchar varchar int int char

======================================================================
TitleOne Description 1 1 <4000
TitleTwo DescriptionOne 1 1 <=4000
TitleTwo DescriptionOne 1 2 <=4000
TitleTwo DescriptionTwo 1 1 <=4000
TitleTwo DescriptionTwo 1 2 <=4000
TitleTwo DescriptioinThree 1 3 3000

Do you have sample code?

Thanks,

SS

cat_jesus
Aged Yak Warrior

547 Posts

Posted - 2008-05-16 : 15:44:16

This will insert and truncate. No need to check for length.


insert into test_table 
select 
convert(nvarchar(4000),CommentText)
from Table where CommentText is not null

This will search tables.

select * from information_schema.columns where data_type = 'text'

That should get you started.

Edit: removed left function because it's redundant. The convert function will truncate the ntext value.

An infinite universe is the ultimate cartesian product.

snow12
Yak Posting Veteran

74 Posts

Posted - 2008-05-19 : 00:31:10

Hello:

Thanks for the reply. Actually problem is ntext hold > 4000 char. I need to split ntext data into the segnments with 4000 char. Then put those segnments to the another table. For example: The ntext data string has 12000 char. The another table has the char field. The column has segnment1(4000 char), segnment2(4000 char), segnment 3(4000 char) which come from the splitting ntext data sting with 12000 char.

I declare the cursor, but I got the error: The text, ntext, and image data types are invalid for local variables.

How to make it work?

Thanks for the help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources