Re: Cursor and Procedure

On May 18, 5:07 pm, Erland Sommarskog <esq...@xxxxxxxxxxxxx> wrote:
Snow (cs180y...@xxxxxxxxx) writes:
I want to find that the ntext column data string have more than 2000
characters. I need to truncate those strings to the segments with 200
character, then put those segments along with their table_name and
column_name to another table. Maybe need to use cursor? If so, how to
use it?

Your help is highly appreciated.

It's difficult to work with ntext columns more than one a time, so it
sounds like you need to use a cursor. But your description is far too
terse for me to want to give an example. Could you post:

1)  CREATE TABLE statements for your table(s).
2)  INSERT statements with sample data.
3)  The desired result given the sample.

Since it's unpractical to post strings with 2000 characters, you
could pretent that the limit is 50 characters or whatever when you
compose the example.

--
Erland Sommarskog, SQL Server MVP, esq...@xxxxxxxxxxxxx

Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx



Hello:

Thanks for the reply. Actually problem is ntext hold > 4000 char. I
need to split ntext data into the segnments with 4000 char, and then
put those segnments to the another table. For example: The ntext data
string has 12000 char. It will be split to 3 segments. The another
table has the char field: long_seg. The column has segnment1(4000
char), segnment2(4000 char), segnment 3(4000 char) which comes from
the splitting ntext data sting with 12000 char. Another field:
segment_nbr holds segment number : 1, 2, and 3.



Here is the table to get the splitting segments.

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].
[tbl_longdata]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tbl_longdata]
GO

CREATE TABLE [dbo].[tbl_longdata] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[table_name] [char] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[col_name] [char] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[tableId] [int] NULL ,
[segment_nbr] [int] NULL ,
[long_seg] [nvarchar] (4000) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL
) ON [PRIMARY]
GO

Here is the simple cursor test one table: tbl_deptTri.


DECLARE @id_cur int
DECLARE @deptTriDes ntext
Declare @segment_nbr_cur int
DECLARE @segment_nbr_new INT
DECLARE @deptTriDes_cur ntext
DECLARE @stringpos INT

DECLARE TableCursor CURSOR FOR
SELECT [id], [deptTriDes]
FROM tbl_deptTri

OPEN TableCursor

FETCH NEXT FROM TableCursor INTO @id_cur, @deptTriDes_cur

WHILE @@FETCH_STATUS = 0
BEGIN
SET @segment_nbr_new = 0
SET @segment_nbr_cur = CEILING(DATALENGTH(@deptTriDes_cur)/8000)
WHILE @segment_nbr_new <= @segment_nbr_cur
BEGIN
SET @segment_nbr_new = @segment_nbr_new + 1
SET @stringpos = (@segment_nbr_new - 1)*4000 + 1
INSERT INTO tbl_LongData ([table_name],[col_name],[tableID],
[segment_nbr],[long_seg])
VALUES (
'tbl_deptTri',
'deptTriDes',
@id_cur,
@segment_nbr_new,
SUBSTRING(@deptTriDes_cur,@stringpos,4000)
)


END
FETCH NEXT FROM TableCursor INTO @id_cur, @deptTriDes_cur
END
CLOSE TableCursor
DEALLOCATE TableCursor

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

what am I missing here?


Thanks
.

Relevant Pages

  • newbie, @@error does not seem to work
    ... -- Declare variables to store values originating from the Event Table (And ... Declare @ca3k_Medew_ltv_nr char ... Insert into Event_Log (EDate, Number, Status, Badge, Class, ...
    (microsoft.public.sqlserver.programming)
  • Re: C string array problems (again)
    ... on the char ** var approach. ... scope and being re-initialized with each loop. ... So why did they declare it ... Would have to take a look a the original code to see what was meant. ...
    (microsoft.public.vb.general.discussion)
  • Re: Comments on my code?
    ... value in a char *. ... Since you didn't declare the argument type, ... void *malloc; ... There's simply no good reason not to ...
    (comp.lang.c)
  • Re: Question about unpacking a binary file: endian troubles
    ... > This may be a dumb question; I'm just getting into C language here. ... `char' is always zero or positive, ... include to declare it. ... > int fdi, n; ...
    (comp.lang.c)
  • Re: Urgent Converting varchar to ntext in SQL
    ... The DataType is ntext for the Column "Notes" in Database. ... Do u know how to declare the local variable ... I tried declaring the "LocalNotes" as ntext, ... Sileesh ...
    (microsoft.public.dotnet.framework.aspnet)