Using a cursor with UPDATETEXT to consolidate many notes records to one

  • I have a table (c2txt) that holds customer notes; there are many notes per customer. I am migrating the notes to a notes table (SV000805) in a new system. The notes must be consolidated by customer. I have created a cursor that will insert the initial note record into the table however after it cycles through the initial notes and attempts to execute the update section, I get a primary key error. Rather than adding to the existing records, it appears to be attempting to insert a new record.

    Any help would be appreciated. Here is the query:

    /* Declare variables to hold the current record as system loops through the source */

    declare

     @NoteIndx varchar,

     @Line_Indx int,

     @1stLineIndexForNote int,  --will store the lowest line index number for this note indx

     @ptrval binary(16),

     @datalen int,

     @texttoadd varchar(8000)

    declare NoteCursor CURSOR for

     select (cast(c2txt.jobsite as varchar) + '-C') NOTEINDX, rtrim(c2txt.sequence) Line_Indx

     from Service..c2txt c2txt

     where c2txt.file_code = 'ar' and cast(c2txt.jobsite as varchar) in (select adrscode from AESB.. SV00200)

     order by 1, 2

    OPEN  NoteCursor

    --Get the first record from source

    FETCH NEXT from NoteCursor into @NoteIndx,@Line_Indx

    --loop starts here

    WHILE @@FETCH_STATUS = 0 BEGIN

    --select @Noteindx

     IF NOT EXISTS (select (cast(c2txt.jobsite as varchar) + '-C')  from c2txt where (cast(c2txt.jobsite as varchar) + '-C') = @NoteIndx)

      BEGIN

       INSERT INTO AESB..SV000805

         (CUSTNMBR,ADRSCODE,Reference_ID,Service_Call_ID,WS_Note_Type,Note_Service_Index,                                                                                                                                                                               

    Wennsoft_Affiliate,Wennsoft_Region,Wennsoft_Branch,USERID,Technician_ID,Technician_Team,                                                                                                                                                                      

    Note_Author,Reminder,Reminder_Date,Reminder_Finish,Printable_Group,Urgent_Checkbox,                                                                                                                                                                           

    DATE1,TIME1,CUSTNAME,NOTEINDX,Service_User_Define_1,Service_User_Define_2,                                                                                                                                                                                    

    Service_User_Define_3,Service_User_Define_4,Service_User_Define_5,Service_User_Define_6,Service_User_Define_7,Service_User_Define_8,                                                                                                                          

    Service_User_Define_9,Service_User_Define_10,Service_User_Define_11,Service_User_Define_12,Service_User_Define_18,Service_User_Define_19,                                                                                                                     

    Service_User_Define_20,Service_User_Define_21,Service_User_Define_22,Service_User_Define_23,Service_User_Define_24,Service_User_Define_25,                                                                                                                    

    USERNAME,MODIFDT,Modified_Time,MDFUSRID,Time_Zone,SV_Language_ID,                                                                                                                                                                                             

    Base_Currency_ID,Base_Curr_Conv_Factor,Billing_Currency_ID,Billing_Curr_Conv_Factor,Local_Currency_ID,WSReserved_CB1,                                                                                                                                         

    WSReserved_CB2,WSReserved_CB3,WSReserved_CB4,WSReserved_CB5,WSReserved_STR1,WSReserved_STR2,                                                                                                                                                                  

    Record_Notes)

       select

        AESB..SV00200.CUSTNMBR CUSTNMBR,

        'x' ADRSCODE,

        'x' Reference_ID,

        'x' Service_Call_ID,

        'C' WS_Note_Type,

        (cast(c2txt.jobsite as varchar) + '-C') Note_Service_Index,

        ' ' Wennsoft_Affiliate,

        ' ' Wennsoft_Region,

        ' ' Wennsoft_Branch,

        'MIGRATE' USERID,

        ' ' Technician_ID,

        ' ' Technician_Team,

        'AR-MIGRATE' Note_Author,

        0 Reminder,

        '1/1/1900' Reminder_Date,

        '1/1/1900' Reminder_Finish,

        0 Printable_Group, --1 means it can be printed.

        0 Urgent_Checkbox,

        '2005-01-07 00:00:00.000' DATE1,

        '1900-01-01 00:00:00.000' TIME1,

        AESB..SV00200.CUSTNAME CUSTNAME,

        0.0 NOTEINDX,

        ' ' Service_User_Define_1,

        ' ' Service_User_Define_2,

        ' ' Service_User_Define_3,

        ' ' Service_User_Define_4,

        0 Service_User_Define_5,

        0 Service_User_Define_6,

        0 Service_User_Define_7,

        0 Service_User_Define_8,

        '1/1/1900' Service_User_Define_9,

        '1/1/1900' Service_User_Define_10,

        '1/1/1900' Service_User_Define_11,

        '1/1/1900' Service_User_Define_12,

        0 Service_User_Define_18,

        0 Service_User_Define_19,

        0 Service_User_Define_20,

        0 Service_User_Define_21,

        0.0 Service_User_Define_22,

        0.0 Service_User_Define_23,

        0.0 Service_User_Define_24,

        0.0 Service_User_Define_25,

        'MIGRATE' USERNAME,

        '2005-01-07' MODIFDT,

        '1900-01-01 14:30:00.000' Modified_Time,

        ' ' MDFUSRID,

        ' ' Time_Zone,

        0 SV_Language_ID,

        ' ' Base_Currency_ID,

        0.0 Base_Curr_Conv_Factor,

        ' ' Billing_Currency_ID,

        0.0 Billing_Curr_Conv_Factor,

        ' ' Local_Currency_ID,

        0 WSReserved_CB1,

        0 WSReserved_CB2,

        0 WSReserved_CB3,

        0 WSReserved_CB4,

        0 WSReserved_CB5,

        ' ' WSReserved_STR1,

        ' ' WSReserved_STR2,

        rtrim(cast(c2txt.sequence as varchar))+ ': ' + rtrim(notes) as Record_Notes

       FROM  c2txt

       join AESB..SV00200 on

       cast(c2txt.jobsite as varchar) = AESB..SV00200.ADRSCODE

       WHERE rtrim(c2txt.sequence) = @Line_Indx AND c2txt.file_code = 'ar'

        

      END

     ELSE

      BEGIN

       select @1stLineIndexForNote = rtrim(min(c2txt.sequence))--+ 1

       from c2txt

       left join AESB..SV00200 on

       cast(c2txt.jobsite as varchar) = AESB..SV00200.ADRSCODE

       where (cast(c2txt.jobsite as varchar) + '-C') = @NoteIndx--get the lowest line index for this note index

    /* Get the 16 byte binary pointer that points to the actual text, the current length of the text and the length of text to add */

       SELECT @ptrval = TEXTPTR(Record_Notes) FROM AESB..SV000805 where Note_Service_Index = @NoteIndx and ADRSCODE = 'x' and WS_Note_Type = 'C'

       SELECT @datalen = DATALENGTH(Record_Notes) FROM AESB..SV000805 where Note_Service_Index = @NoteIndx and ADRSCODE = 'x' and WS_Note_Type = 'C'

       select @texttoadd = char(13) + rtrim(cast(c2txt.sequence as varchar))+ ': ' + rtrim(notes)--  rtrim((cast(c2txt.notes as varchar(8000))))

        FROM  c2txt

       left join AESB..SV00200 on

       cast(c2txt.jobsite as varchar) = AESB..SV00200.ADRSCODE

       where (cast(c2txt.jobsite as varchar) + '-C') = @NoteIndx and rtrim(c2txt.sequence) = @Line_Indx and c2txt.file_code = 'ar'

    /* Truncate any data that would cause in excess of 32000 characters*/

       if @datalen + len(@texttoadd) > 32000

        begin

         select @texttoadd = left(@texttoadd,32000-@datalen)

        end

       UPDATE AESB..SV000805 set  DATE1= CONVERT(varchar,getdate(),101),

              TIME1= CONVERT(varchar,getdate(),108)

       WHERE AESB..SV000805.Note_Service_Index = @NoteIndx

       

       UPDATETEXT AESB..SV000805.Record_Notes

        @ptrval  --This is the pointer to the actual text, the AESB..SV000805 simply stores a pointer

        NULL-- -- what character at which to start the insert--only necessary if we are inserting data between other data

        NULL--how far forward to delete (value of NULL means append on the end)

        @texttoadd --The actual text to add

      END

    FETCH NEXT from NoteCursor into @NoteIndx, @Line_Indx

    END

    Close  NoteCursor

    Deallocate  NoteCursor

     



    "Some like to understand what they believe in. Others like to believe in what they understand."
    --Stanislaus J. Lec

  • What I would do in this case is use the Debugger tool that is part of Query Analyzer in your development
    environment.  Maybe you have a subtle logic error that you don't see that will show up if you step
    through the code.  Obviously, an update statement doesn't insert records, but it could modify the primary
    key field. 

    Using the object browser in QA, right click on the stored proc and select Debug. 

    Aunt Kathi Data Platform MVP
    Author of Expert T-SQL Window Functions
    Simple-Talk Editor

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply