January 22, 2005 at 10:53 pm
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
January 24, 2005 at 11:54 am
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