CURSOR FETCH STATEMENT IS HANGING

Hi All,
I have a stored proc that uses a cursor to iterate over the join resultset
of 5 tables. After fetching roughly 12,000 records and running for about 30
minutes, the next FETCH statement inside the WHILE loop hangs. This happens
consisently. I tried to do a commit / checkpoint after every 10,000 records,
but the problem persists.
Can any one please provide some thoughts about why this could be happening?
ps: The tempdb size is 955 MB and the disk size is 33 GB. This is our
development database.
Thanks,
RajeshWhat are you trying to accomplish?
Why are you using cursors?
What kind of cursor are you using?
Any research about a set-based solution?
Can we see some code?
AMB
"rajeshlh" wrote:

> Hi All,
> I have a stored proc that uses a cursor to iterate over the join resultse
t
> of 5 tables. After fetching roughly 12,000 records and running for about 3
0
> minutes, the next FETCH statement inside the WHILE loop hangs. This happen
s
> consisently. I tried to do a commit / checkpoint after every 10,000 record
s,
> but the problem persists.
> Can any one please provide some thoughts about why this could be happening
?
> ps: The tempdb size is 955 MB and the disk size is 33 GB. This is our
> development database.
> Thanks,
> Rajesh
>|||I have to process a set of records obtained from the join of 5 tables. For
each record in the resultset, i need to insert/update into 5-6 tables, plus
i
need to write to a log table the key for each inserted row or updated row.
Following is the code.
The FETCH FROM Subscription_Cursor fails inside the While Loop after some
11,000 records. Since iam writing debug statements to another table, i can
say that after processing 11000 records inside the cursor, the FETCH
statement freezes.
I did think about SET-based approach, but the processing logic forces me to
use a cursor. The Job is run nightly and processes about 100,000+ records.
Following is the SP Code.
CREATE PROCEDURE TEST_SP
AS
--Variables to store Name record values
DECLARE @.Account_Number varchar(20),@.Publisher_Code varchar(5),@.Mag_Code
varchar(5),@.Postal_Code varchar(6),@.Common_Name varchar(30),
@.Job_Title varchar(50),@.Company_Name varchar(30),@.Address_Line_1
varchar(30),@.Address_Line_2 varchar(30),@.City varchar(30),
@.State_Prov varchar(2),@.Country_Code varchar(2),@.Telephone
varchar(11),@.Fax_Number varchar(11)
--Variables to store Product record values
DECLARE @.Service_Status varchar(1),@.Start_Issue datetime,@.Expire_Issue
datetime,@.Num_Copies integer,@.Email_User_Name varchar(50),
@.Current_Email_Address varchar(50),@.Email_Password varchar(50)
--Variables to store Order record values
DECLARE @.Order_Number varchar(20),@.Order_Status varchar(1),@.Order_Term
integer,@.Order_Net_Value money,@.Source_Code varchar(2),@.Medium_Code
varchar(2),
@.Document_Key varchar(10),@.Setcode varchar(1),@.Orig_Start_Issue
datetime,@.Order_Entry_Type varchar(5)
--Variables to store Demographic record values
DECLARE @.Version_Number varchar(10),@.Segment_Number integer ,@.Demo_Data
varchar(1024)
--variales to store newly created reg_visitor_id,account_id and order_id -
for subscriptions not existing in Elogic Reg DB
DECLARE @.Reg_Visitor_Id integer ,@.Account_Id integer ,@.Order_Id
integer,@.Address_Id integer
--variables to store values present in staging tables - For subscriptions
already existing in Elogic Reg DB
DECLARE @.eLogic_Reg_Visitor_Id integer ,@.eLogic_Account_Id integer
,@.eLogic_Order_Id integer,@.eLogic_Publication_Id integer
--variables used for logging
DECLARE @.Publication_Id integer,@.Pub_Code varchar(5),@.Target_Type
varchar(100),@.Process_Type varchar(100),@.Summary_Count integer,
@.Log_Summary_Id integer,@.Source_File_Name varchar(100)
--Variables to store Donor's data
DECLARE @.Donor_Company_Name varchar(30),@.Donor_Address_Line_1
varchar(30),@.Donor_Address_Line_2 varchar(30),@.Donor_City varchar(30),
@.Donor_State_Prov varchar(2),@.Donor_Country_Code
varchar(2),@.Donor_Telephone varchar(11),@.Donor_Fax_Number varchar(11),
@.Donor_Account_number varchar(20), @.Donor_Postal_Code varchar(6)
--Variables to store CDS to Elogic Converted values
DECLARE @.eLogic_Account_Status varchar(5),@.eLogic_Pay_Type
varchar(5),@.eLogic_Auto_Renew bit,@.eLogic_Account_Type varchar(20)
--Misc variables
DECLARE @.SP_NAME varchar(50),@.Ret integer
DECLARE @.Reg_Visitor_Product varchar(100)
DECLARE @.Exec_Start_Time datetime
DECLARE @.Promo_Code_Pos2 varchar(1)
DECLARE @.Delivery_Type varchar(1)
DECLARE @.Cnt int
--Cursor to subscriptions stored in the staging tables
DECLARE Subscriptions_Cursor CURSOR FOR
SELECT nr.eLogic_reg_visitor_id,nr.eLogic_publication_id,nr.source_file_name
,nr.account_number,nr.publisher_code,nr.mag_code,nr.postal_code,nr.common_na
me,nr.job_title,nr.company_name,
nr.address_line_1,nr.address_line_2,nr.city,nr.state_prov,nr.country_code,nr
.telephone,nr.fax_number,
pr.eLogic_account_id,pr.service_status,pr.start_issue,pr.expire_issue,pr.num
_copies,pr.email_user_name,pr.email_password,pr.current_email_address,
ord.eLogic_order_id,ord.order_number,ord.order_status,ord.order_term,ord.ord
er_net_value,ord.source_code,ord.medium_code,ord.document_key,
ord.setcode,ord.orig_start_issue,ord.order_entry_type,
dr.version_number,dr.segment_number,dr.demo_data,
delivery_type
FROM cds_name_record nr
JOIN cds_product_record pr
ON nr.account_number=pr.account_number
AND nr.publisher_code=pr.publisher_code
AND nr.mag_code=pr.mag_code
JOIN cds_order_record ord
ON ord.account_number=pr.account_number
AND ord.publisher_code=pr.publisher_code
AND ord.mag_code=pr.mag_code
JOIN cds_demographic_record dr
ON dr.account_number=pr.account_number
AND dr.publisher_code=pr.publisher_code
AND dr.mag_code = pr.mag_code
JOIN publication_subscription ps
ON ps.external_multi_mag_code = pr.publisher_code
AND ps.code = pr.mag_code
WHERE GETDATE() BETWEEN nr.address_start_date AND nr.address_end_date --
Get Only current address
AND (ord.setcode='A' OR ord.setcode='C' OR ord.setcode='E')--Get only
Non-Gift and Donee Orders
AND ord.order_status='B' --Get only Base Orders
AND dr.demo_status = 'B' --Get only Base Demo Records
--AND nr.account_number <>'0010115103'
ORDER BY CAST(nr.account_number AS int)
--cursor to the reg_feed_detail_log table , used for populating the
reg_feed_summary_log table
DECLARE Summary_Cursor CURSOR FOR
SELECT
publication_id,pub_code,target_type,proc
ess_type,source_file_name,count(*) a
s
summary_count
FROM reg_feed_detail_log
GROUP BY publication_id,pub_code,target_type,proc
ess_type,source_file_name
SET @.SP_NAME = OBJECT_NAME(@.@.PROCID)
SET @.Exec_Start_Time = CURRENT_TIMESTAMP
OPEN Subscriptions_Cursor
FETCH NEXT FROM Subscriptions_Cursor
INTO
@.eLogic_Reg_Visitor_Id,@.eLogic_Publicati
on_Id,@.Source_File_Name,@.Account_Num
ber ,@.Publisher_Code ,@.Mag_Code ,@.Postal_Code ,@.Common_Name ,@.Job_Title ,
@.Company_Name ,@.Address_Line_1,@.Address_Line_2 ,@.City ,
@.State_Prov ,@.Country_Code ,@.Telephone ,@.Fax_Number,
@.eLogic_Account_Id,@.Service_Status ,@.Start_Issue ,@.Expire_Issue
,@.Num_Copies ,@.Email_User_Name ,@.Email_Password,
@.Current_Email_Address,
@.eLogic_Order_Id,@.Order_Number, @.Order_Status ,@.Order_Term
,@.Order_Net_Value ,@.Source_Code,@.Medium_Code ,
@.Document_Key ,@.Setcode ,@.Orig_Start_Issue, @.Order_Entry_Type,
@.Version_Number,@.Segment_Number,@.Demo_Da
ta,@.Delivery_Type
SET @.Cnt =1
PRINT 'start'
WHILE @.@.FETCH_STATUS = 0
BEGIN --B1
INSERT INTO Demo_data values ('inside while loop',null,null)
INSERT INTO debug_table
(eLogic_reg_visitor_id,eLogic_publicatio
n_id,account_number,publisher_code,m
ag_code,order_status,set_code,delivery_t
ype,seq)
VALUES (@.eLogic_Reg_Visitor_Id,@.eLogic_p
ublication_id,@.Account_Number
,@.Publisher_Code ,@.Mag_Code,@.Order_Status,@.SetCode,@.Deliv
ery_Type,@.Cnt)
SELECT @.Reg_Visitor_Id = NULL,@.Account_Id = NULL,@.Order_Id =
NULL,@.Address_Id = NULL,@.Reg_Visitor_Product = NULL
SELECT @.Donor_Company_Name = NULL,@.Donor_Address_Line_1 =
NULL,@.Donor_Address_Line_2 = NULL,@.Donor_City = NULL,
@.Donor_State_Prov = NULL,@.Donor_Country_Code = NULL,@.Donor_Telephone =
NULL,@.Donor_Fax_Number = NULL,
@.Donor_Account_number = NULL
SELECT @.eLogic_Account_Status = NULL,@.eLogic_Pay_Type =
NULL,@.eLogic_Auto_Renew = NULL,@.eLogic_Account_Type = NULL
SELECT @.Promo_Code_Pos2 = NULL
IF ( @.SetCode = 'C' OR @.SetCode ='E') --SetCode 'C' and 'E' denote donee
BEGIN --B2
SELECT @.Donor_Account_number = nr.account_number,@.Donor_Postal_Code =
nr.postal_code,@.Donor_Company_Name = nr.company_name,
@.Donor_Address_Line_1 = nr.address_line_1,@.Donor_Address_Line_2 =
nr.address_line_2,@.Donor_City = nr.city,
@.Donor_State_Prov = nr.state_prov,@.Donor_Country_Code =
nr.country_code,@.Donor_Telephone = nr.telephone,@.Donor_Fax_Number =
nr.fax_number
FROM cds_name_record nr
JOIN cds_order_record ord
ON ord.account_number=nr.account_number
AND ord.publisher_code=nr.publisher_code
AND ord.mag_code=nr.mag_code
WHERE GETDATE() BETWEEN nr.address_start_date AND nr.address_end_date --
Get Only current address Name record
AND (ord.setcode='B' OR ord.setcode='D')--Get only Donor Orders
AND (ord.order_status='B' OR ord.order_status='D') --Get only Base Order
or Non-Subscibing Donor Order
AND nr.publisher_code = @.Publisher_Code
AND nr.mag_code = @.Mag_Code
AND ord.order_number = @.Order_Number
-- Note : Donor and Donee will have different account numbers, but same
publisher code, mag code and order number
END --END B2
SET @.eLogic_Account_Status = CASE
WHEN @.Service_Status = 'A' THEN 'A'
WHEN @.Service_Status IN ( 'B','H','I') THEN 'C'
WHEN @.Service_Status = 'C' THEN 'X'
WHEN @.Service_Status IN ('D','E','F','G') THEN 'O'
END
SET @.eLogic_Pay_Type = CASE
WHEN @.Order_Entry_Type IN ('A','B','C','D') THEN 'P'
WHEN @.Order_Entry_Type IN ('L','M','U') THEN 'F'
ELSE ''
END
SET @.eLogic_Auto_Renew= CASE
WHEN ( SUBSTRING(@.Document_Key,1,1)= '#' OR @.Medium_Code = 'E') THEN 1
ELSE 0
END
SET @.Promo_Code_Pos2 = SUBSTRING(@.Document_Key,2,1)
SET @.eLogic_Account_Type=CASE
WHEN @.Source_Code = 'CC' THEN
CASE
WHEN @.Promo_Code_Pos2 = 'T' THEN 'FREETRIAL'
WHEN @.Promo_Code_Pos2 = 'E' THEN 'EMAILONLY'
ELSE 'CONTROLLED'
END
WHEN @.Source_Code = 'CA' THEN 'COMP'
ELSE 'PAID'
END
SELECT top 1 @.Reg_Visitor_Id = A.reg_visitor_id
FROM account A
JOIN publication_subscription PS
ON A.pub_code = PS.code
WHERE A.account_number = @.Account_Number
AND PS.external_multi_mag_code = @.Publisher_Code
--If a reg_visitor is not determined in the Staging tables or by looking up
the Account table , a new reg_visitor
--is created
IF ( @.eLogic_Reg_Visitor_Id IS NULL AND @.Reg_Visitor_Id IS NULL)
BEGIN --B3
--No Reg_Visitor corresponding to the mag subscription - mag subscription
generated at CDS
--Create a new reg_visitor and associated rows in Account,Account_Order
and visitor_demographic
SELECT @.Reg_Visitor_Product = master_brand
FROM publication
WHERE external_multi_mag_code=@.Publisher_Code
--insert into reg_visior table
EXEC @.Ret= dbo.upd_reg_visitor @.p_reg_visitor_id = @.Reg_Visitor_Id OUTPUT,
@.p_common_name = @.Common_Name,
@.p_company_name = @.Company_Name,
@.p_email = @.Current_Email_Address,
@.p_encrypted_password = @.Email_Password,
@.p_given_name = NULL,
@.p_login_id = @.Email_User_Name,
@.p_merged_visitor_id = NULL,
@.p_middle_initial = NULL,
@.p_middle_name = NULL,
@.p_name_suffix = NULL,
@.p_password = @.Email_Password,
@.p_product = @.Reg_Visitor_Product,
@.p_subproduct = NULL,
@.p_professional_title = @.Job_Title,
@.p_record_status = 1,
@.p_registration_level = 1,
@.p_salutation = NULL,
@.p_sur_name = NULL,
@.p_zip = NULL,
@.p_country = 'TESTDTS'
IF ( @.@.error <> 0 OR @.Ret < 0 )
BEGIN
RAISERROR('%s: Error inserting into Reg_Visitor table!', 18, 2, @.SP_NAME)
--ROLLBACK TRAN T1
RETURN -2
END
--Log details to reg_feed_detail_log
EXEC Log_Reg_Feed_Details @.P_Publication_Id = @.eLogic_Publication_Id,
@.P_Pub_Code = @.Mag_Code,
--@.P_Process_Cycle_Id = SELECT DATEPART(dy, GETDATE()) ,
@.P_Source_Key_1 = NULL,
@.P_Source_Key_2 = NULL,
@.P_Source_Key_3 = NULL,
@.P_Target_Type = 'reg_visitor',
@.P_Target_Key_1 = @.Reg_Visitor_Id,
@.P_Target_Key_2 = NULL,
@.P_Target_Key_3 = NULL,
@.P_Process_Type = 'INSERT',
@.P_Source_File_Name = @.Source_File_Name
--insert into address table
EXEC @.Ret= dbo.set_address @.p_address_id = @.Address_Id OUTPUT,
@.p_reg_visitor_id = @.Reg_Visitor_Id,
@.p_company_name = @.Company_Name,
@.p_address_line_1 = @.Address_Line_1,
@.p_address_line_2 = @.Address_Line_2,
@.p_city = @.City,
@.p_postal_code = @.Postal_Code,
@.p_state_prov = @.State_Prov,
@.p_country_code = @.Country_Code,
@.p_phone = @.Telephone,
@.p_fax = @.Fax_Number,
@.p_address_type = 0 --shipping
--Log details to reg_feed_detail_log
EXEC Log_Reg_Feed_Details @.P_Publication_Id = @.eLogic_Publication_Id,
@.P_Pub_Code = @.Mag_Code,
--@.P_Process_Cycle_Id =SELECT DATEPART(dy, GETDATE()) ,
@.P_Source_Key_1 = NULL,
@.P_Source_Key_2 = NULL,
@.P_Source_Key_3 = NULL,
@.P_Target_Type = 'address',
@.P_Target_Key_1 = @.Reg_Visitor_Id,
@.P_Target_Key_2 = @.Address_Id ,
@.P_Target_Key_3 = NULL,
@.P_Process_Type = 'INSERT',
@.P_Source_File_Name = @.Source_File_Name
IF (@.SETCODE = 'C' OR @.SETCODE = 'E') -- Donee subscription
BEGIN
--Insert the corresponding Donor Address
EXEC @.Ret= dbo.set_address @.p_address_id = @.Address_Id OUTPUT,
@.p_reg_visitor_id = @.Reg_Visitor_Id,
@.p_company_name = @.Donor_Company_Name,
@.p_address_line_1 = @.Donor_Address_Line_1,
@.p_address_line_2 = @.Donor_Address_Line_2,
@.p_city = @.Donor_City,
@.p_postal_code = @.Donor_Postal_Code,
@.p_state_prov = @.Donor_State_Prov,
@.p_country_code = @.Donor_Country_Code,
@.p_phone = @.Donor_Telephone,
@.p_fax = @.Donor_Fax_Number,
@.p_address_type = 1 --Billing
--IF @.Account_Number = '0010115103' OR @.Cnt =11214
--INSERT INTO demo_data values ('inserted into address for donor')
--Log details to reg_feed_detail_log
EXEC Log_Reg_Feed_Details @.P_Publication_Id = @.eLogic_Publication_Id,
@.P_Pub_Code = @.Mag_Code,
--@.P_Process_Cycle_Id =SELECT DATEPART(dy, GETDATE()) ,
@.P_Source_Key_1 = NULL,
@.P_Source_Key_2 = NULL,
@.P_Source_Key_3 = NULL,
@.P_Target_Type = 'address',
@.P_Target_Key_1 = @.Reg_Visitor_Id,
@.P_Target_Key_2 = @.Address_Id ,
@.P_Target_Key_3 = NULL,
@.P_Process_Type = 'INSERT',
@.P_Source_File_Name = @.Source_File_Name
--IF @.Account_Number = '0010115103' OR @.Cnt =11214
--INSERT INTO demo_data values ('inserted into detail log for donor
address')
END
IF ( @.@.error <> 0 OR @.Ret < 0 )
BEGIN
RAISERROR('%s: Error inserting into Address table!', 18, 3, @.sp_name)
CLOSE Subscriptions_Cursor
DEALLOCATE Subscriptions_Cursor
RETURN -3
END
--Insert into Account table
EXEC @.Ret = dbo.set_account @.p_reg_visitor_id = @.Reg_Visitor_Id,
@.p_account_id = @.Account_Id OUTPUT,
@.p_account_number = @.Account_Number,
@.p_pub_code = @.Mag_Code,
@.p_account_type = @.eLogic_Account_type ,
@.p_status = @.eLogic_Account_Status,
@.p_supp_account_number = @.Donor_Account_Number -- If its a Non-Gift
Order, NULL will be inserted for Supp_Account_Number
IF ( @.@.error <> 0 OR @.Ret < 0 OR @.Account_Id IS NULL)
BEGIN
RAISERROR('%s: Error inserting into Account table!', 18, 4, @.SP_NAME)
CLOSE Subscriptions_Cursor
DEALLOCATE Subscriptions_Cursor
RETURN -4
END
INSERT INTO Demo_data values ('completed inserting into account
table',@.Reg_Visitor_id,@.Account_Number)
--Log details to reg_feed_detail_log
EXEC Log_Reg_Feed_Details @.P_Publication_Id = @.eLogic_Publication_Id,
@.P_Pub_Code = @.Mag_Code,
--@.P_Process_Cycle_Id =SELECT DATEPART(dy, GETDATE()) ,
@.P_Source_Key_1 = NULL,
@.P_Source_Key_2 = NULL,
@.P_Source_Key_3 = NULL,
@.P_Target_Type = 'account',
@.P_Target_Key_1 = @.Reg_Visitor_Id,
@.P_Target_Key_2 = @.Account_Id ,
@.P_Target_Key_3 = NULL,
@.P_Process_Type = 'INSERT',
@.P_Source_File_Name = @.Source_File_Name
INSERT INTO Demo_data values ('completed inserting into detail log for
account table',@.Reg_Visitor_id,@.Account_Number)
--insert into account_order table
EXEC @.Ret = dbo.set_account_order @.p_reg_visitor_id = @.Reg_Visitor_Id,
@.p_account_id = @.Account_Id,
@.p_order_id = @.Order_Id OUTPUT,
@.p_term = @.Order_Term,
@.p_term_unit = NULL,
@.p_pay_type = @.eLogic_Pay_Type,
@.p_net_amt = @.Order_Net_Value,
@.p_quantity = @.Num_Copies,
@.p_vendor_order_number = @.Order_Number,
@.p_promo_response_key = @.Document_Key,
@.p_number_of_installments=1,
@.p_auto_renew =@.eLogic_Auto_Renew
IF ( @.@.error <> 0 OR @.Ret < 0 OR @.Order_Id IS NULL)
BEGIN
RAISERROR('%s: Error inserting into Account_Order table!', 18, 5, @.SP_NAME)
CLOSE Subscriptions_Cursor
DEALLOCATE Subscriptions_Cursor
RETURN -5
END
INSERT INTO Demo_data values ('completed inserting into account_order
table',@.Reg_Visitor_id,@.Account_Number)
--Log details to reg_feed_detail_log
EXEC Log_Reg_Feed_Details @.P_Publication_Id = @.eLogic_Publication_Id,
@.P_Pub_Code = @.Mag_Code,
--@.P_Process_Cycle_Id =SELECT DATEPART(dy, GETDATE()) ,
@.P_Source_Key_1 = NULL,
@.P_Source_Key_2 = NULL,
@.P_Source_Key_3 = NULL,
@.P_Target_Type = 'account_order',
@.P_Target_Key_1 = @.Reg_Visitor_Id,
@.P_Target_Key_2 = @.Account_Id ,
@.P_Target_Key_3 = @.Order_Id,
@.P_Process_Type = 'INSERT',
@.P_Source_File_Name = @.Source_File_Name
INSERT INTO Demo_data values ('completed logging to detail log table for
account_order',@.Reg_Visitor_id,@.Account_
Number)
--insert into visitor_demographics table only for Online Magazines ;
Delivery Type W - Online, P- Print
IF(@.Delivery_Type = 'W')
BEGIN
INSERT INTO Demo_data values ('calling demographcis
sp',@.Reg_Visitor_id,@.Account_Number)
EXEC @.Ret = Process_CDS_Demographics @.Reg_Visitor_Id = @.Reg_Visitor_Id,
@.Mag_Code = @.Mag_Code,
@.Demo_Data = @.Demo_Data,
@.Segment_Number = @.Segment_Number,
@.Version_Number = @.Version_Number
IF ( @.@.error <> 0 OR @.Ret < 0 )
BEGIN
RAISERROR('%s: Error inserting/updating into Visitor_Demographics
table!', 18, 6, @.SP_NAME)
CLOSE Subscriptions_Cursor
DEALLOCATE Subscriptions_Cursor
RETURN -6
END
END
INSERT INTO Demo_data values ('DEBUG LINE
HIT',@.Reg_Visitor_id,@.Account_Number)
END --END B3
INSERT INTO demo_data (debug_message,reg_visitor_id,account_nu
mber)VALUES
('About to fetch next row- current row details
-->',@.Reg_visitor_id,@.Account_number)
FETCH NEXT FROM Subscriptions_Cursor
INTO
@.eLogic_Reg_Visitor_Id,@.eLogic_Publicati
on_Id,@.Source_File_Name,@.Account_Num
ber ,@.Publisher_Code ,@.Mag_Code ,@.Postal_Code ,@.Common_Name ,@.Job_Title ,
@.Company_Name ,@.Address_Line_1,@.Address_Line_2 ,@.City ,
@.State_Prov ,@.Country_Code ,@.Telephone ,@.Fax_Number,
@.eLogic_Account_Id,@.Service_Status ,@.Start_Issue ,@.Expire_Issue
,@.Num_Copies ,@.Email_User_Name ,@.Email_Password,
@.Current_Email_Address,
@.eLogic_Order_Id,@.Order_Number, @.Order_Status ,@.Order_Term
,@.Order_Net_Value ,@.Source_Code,@.Medium_Code ,
@.Document_Key ,@.Setcode ,@.Orig_Start_Issue, @.Order_Entry_Type,
@.Version_Number,@.Segment_Number,@.Demo_Da
ta,@.Delivery_Type
INSERT INTO demo_data (debug_message,reg_visitor_id,account_nu
mber)VALUES
('fetched next row',@.eLogic_Reg_Visitor_Id,@.Account_num
ber)
SET @.Cnt = @.Cnt + 1
INSERT INTO demo_data (debug_message,reg_visitor_id,account_nu
mber)VALUES
('value of @.@.FETCHSTATUS =',@.@.Fetch_Status,@.Account_number)
END --END B1
CLOSE Subscriptions_Cursor
DEALLOCATE Subscriptions_Cursor
"Alejandro Mesa" wrote:
> What are you trying to accomplish?
> Why are you using cursors?
> What kind of cursor are you using?
> Any research about a set-based solution?
> Can we see some code?
>
> AMB
> "rajeshlh" wrote:
>|||- Declare the cursor LOCAL FAST_FORWARD.
- In the WHERE clause, change:
GETDATE() BETWEEN nr.address_start_date AND nr.address_end_date
by:
(nr.address_start_date <= GETDATE() and nr.address_end_date >= GETDATE())
- Do you need the ORDER BY clause in the select associated?
- Are the rows, of the result, processed in group or one by one?. For
example, Do you need to process multiple rows
per nr.account_number as a group?
- Can you do it by chuncks?
declare @.min int
declare @.max int
select @.min = min(nr.account_number), @.max = max(nr.account_number)
from ...
where ...
while @.min <= @.max
begin
DECLARE Subscriptions_Cursor CURSOR local fast_forward
for
select ...
from ...
where ...
and nr.account_number between @.min and case when (@.min + 1000) > @.max
then @.max else (@.min + 1000) end
open cursor ...
while 1 = 1
begin
fetch ...
if @.@.error != 0 or @.@.fetch_status != 0 break
..
end
close cursor ...
deallocate cursor ...
set @.min = @.min + 1000
end
...
AMB
"rajeshlh" wrote:
> I have to process a set of records obtained from the join of 5 tables. For
> each record in the resultset, i need to insert/update into 5-6 tables, plu
s i
> need to write to a log table the key for each inserted row or updated row
.
> Following is the code.
> The FETCH FROM Subscription_Cursor fails inside the While Loop after some
> 11,000 records. Since iam writing debug statements to another table, i can
> say that after processing 11000 records inside the cursor, the FETCH
> statement freezes.
> I did think about SET-based approach, but the processing logic forces me t
o
> use a cursor. The Job is run nightly and processes about 100,000+ records.
> Following is the SP Code.
> CREATE PROCEDURE TEST_SP
> AS
> --Variables to store Name record values
> DECLARE @.Account_Number varchar(20),@.Publisher_Code varchar(5),@.Mag_Code
> varchar(5),@.Postal_Code varchar(6),@.Common_Name varchar(30),
> @.Job_Title varchar(50),@.Company_Name varchar(30),@.Address_Line_1
> varchar(30),@.Address_Line_2 varchar(30),@.City varchar(30),
> @.State_Prov varchar(2),@.Country_Code varchar(2),@.Telephone
> varchar(11),@.Fax_Number varchar(11)
> --Variables to store Product record values
> DECLARE @.Service_Status varchar(1),@.Start_Issue datetime,@.Expire_Issue
> datetime,@.Num_Copies integer,@.Email_User_Name varchar(50),
> @.Current_Email_Address varchar(50),@.Email_Password varchar(50)
> --Variables to store Order record values
> DECLARE @.Order_Number varchar(20),@.Order_Status varchar(1),@.Order_Term
> integer,@.Order_Net_Value money,@.Source_Code varchar(2),@.Medium_Code
> varchar(2),
> @.Document_Key varchar(10),@.Setcode varchar(1),@.Orig_Start_Issue
> datetime,@.Order_Entry_Type varchar(5)
> --Variables to store Demographic record values
> DECLARE @.Version_Number varchar(10),@.Segment_Number integer ,@.Demo_Data
> varchar(1024)
> --variales to store newly created reg_visitor_id,account_id and order_id -
> for subscriptions not existing in Elogic Reg DB
> DECLARE @.Reg_Visitor_Id integer ,@.Account_Id integer ,@.Order_Id
> integer,@.Address_Id integer
> --variables to store values present in staging tables - For subscriptions
> already existing in Elogic Reg DB
> DECLARE @.eLogic_Reg_Visitor_Id integer ,@.eLogic_Account_Id integer
> ,@.eLogic_Order_Id integer,@.eLogic_Publication_Id integer
> --variables used for logging
> DECLARE @.Publication_Id integer,@.Pub_Code varchar(5),@.Target_Type
> varchar(100),@.Process_Type varchar(100),@.Summary_Count integer,
> @.Log_Summary_Id integer,@.Source_File_Name varchar(100)
> --Variables to store Donor's data
> DECLARE @.Donor_Company_Name varchar(30),@.Donor_Address_Line_1
> varchar(30),@.Donor_Address_Line_2 varchar(30),@.Donor_City varchar(30),
> @.Donor_State_Prov varchar(2),@.Donor_Country_Code
> varchar(2),@.Donor_Telephone varchar(11),@.Donor_Fax_Number varchar(11),
> @.Donor_Account_number varchar(20), @.Donor_Postal_Code varchar(6)
> --Variables to store CDS to Elogic Converted values
> DECLARE @.eLogic_Account_Status varchar(5),@.eLogic_Pay_Type
> varchar(5),@.eLogic_Auto_Renew bit,@.eLogic_Account_Type varchar(20)
> --Misc variables
> DECLARE @.SP_NAME varchar(50),@.Ret integer
> DECLARE @.Reg_Visitor_Product varchar(100)
> DECLARE @.Exec_Start_Time datetime
> DECLARE @.Promo_Code_Pos2 varchar(1)
> DECLARE @.Delivery_Type varchar(1)
> DECLARE @.Cnt int
>
> --Cursor to subscriptions stored in the staging tables
> DECLARE Subscriptions_Cursor CURSOR FOR
> SELECT nr.eLogic_reg_visitor_id,nr.eLogic_publication_id,nr.source_file_na
me,nr.account_number,nr.publisher_code,nr.mag_code,nr.postal_code,nr.common_
name,nr.job_title,nr.company_name,
> nr.address_line_1,nr.address_line_2,nr.city,nr.state_prov,nr.country_code
,nr.telephone,nr.fax_number,
> pr.eLogic_account_id,pr.service_status,pr.start_issue,pr.expire_issue,pr.
num_copies,pr.email_user_name,pr.email_password,pr.current_email_address,
> ord.eLogic_order_id,ord.order_number,ord.order_status,ord.order_term,ord.
order_net_value,ord.source_code,ord.medium_code,ord.document_key,
> ord.setcode,ord.orig_start_issue,ord.order_entry_type,
> dr.version_number,dr.segment_number,dr.demo_data,
> delivery_type
> FROM cds_name_record nr
> JOIN cds_product_record pr
> ON nr.account_number=pr.account_number
> AND nr.publisher_code=pr.publisher_code
> AND nr.mag_code=pr.mag_code
> JOIN cds_order_record ord
> ON ord.account_number=pr.account_number
> AND ord.publisher_code=pr.publisher_code
> AND ord.mag_code=pr.mag_code
> JOIN cds_demographic_record dr
> ON dr.account_number=pr.account_number
> AND dr.publisher_code=pr.publisher_code
> AND dr.mag_code = pr.mag_code
> JOIN publication_subscription ps
> ON ps.external_multi_mag_code = pr.publisher_code
> AND ps.code = pr.mag_code
> WHERE GETDATE() BETWEEN nr.address_start_date AND nr.address_end_date --
> Get Only current address
> AND (ord.setcode='A' OR ord.setcode='C' OR ord.setcode='E')--Get only
> Non-Gift and Donee Orders
> AND ord.order_status='B' --Get only Base Orders
> AND dr.demo_status = 'B' --Get only Base Demo Records
> --AND nr.account_number <>'0010115103'
> ORDER BY CAST(nr.account_number AS int)
>
> --cursor to the reg_feed_detail_log table , used for populating the
> reg_feed_summary_log table
> DECLARE Summary_Cursor CURSOR FOR
> SELECT
> publication_id,pub_code,target_type,proc
ess_type,source_file_name,count(*)
as
> summary_count
> FROM reg_feed_detail_log
> GROUP BY publication_id,pub_code,target_type,proc
ess_type,source_file_name
> SET @.SP_NAME = OBJECT_NAME(@.@.PROCID)
> SET @.Exec_Start_Time = CURRENT_TIMESTAMP
> OPEN Subscriptions_Cursor
> FETCH NEXT FROM Subscriptions_Cursor
> INTO
> @.eLogic_Reg_Visitor_Id,@.eLogic_Publicat
ion_Id,@.Source_File_Name,@.Account_
Number ,@.Publisher_Code ,@.Mag_Code ,@.Postal_Code ,@.Common_Name ,@.Job_Title ,
> @.Company_Name ,@.Address_Line_1,@.Address_Line_2 ,@.City ,
> @.State_Prov ,@.Country_Code ,@.Telephone ,@.Fax_Number,
> @.eLogic_Account_Id,@.Service_Status ,@.Start_Issue ,@.Expire_Issue
> ,@.Num_Copies ,@.Email_User_Name ,@.Email_Password,
> @.Current_Email_Address,
> @.eLogic_Order_Id,@.Order_Number, @.Order_Status ,@.Order_Term
> ,@.Order_Net_Value ,@.Source_Code,@.Medium_Code ,
> @.Document_Key ,@.Setcode ,@.Orig_Start_Issue, @.Order_Entry_Type,
> @.Version_Number,@.Segment_Number,@.Demo_D
ata,@.Delivery_Type
>
> SET @.Cnt =1
> PRINT 'start'
> WHILE @.@.FETCH_STATUS = 0
> BEGIN --B1
> INSERT INTO Demo_data values ('inside while loop',null,null)
> INSERT INTO debug_table
> (eLogic_reg_visitor_id,eLogic_publicatio
n_id,account_number,publisher_code
,mag_code,order_status,set_code,delivery
_type,seq)
> VALUES (@.eLogic_Reg_Visitor_Id,@.eLogic_
publication_id,@.Account_Number
> ,@.Publisher_Code ,@.Mag_Code,@.Order_Status,@.SetCode,@.Deliv
ery_Type,@.Cnt)
> SELECT @.Reg_Visitor_Id = NULL,@.Account_Id = NULL,@.Order_Id =
> NULL,@.Address_Id = NULL,@.Reg_Visitor_Product = NULL
> SELECT @.Donor_Company_Name = NULL,@.Donor_Address_Line_1 =
> NULL,@.Donor_Address_Line_2 = NULL,@.Donor_City = NULL,
> @.Donor_State_Prov = NULL,@.Donor_Country_Code = NULL,@.Donor_Telephone =
> NULL,@.Donor_Fax_Number = NULL,
> @.Donor_Account_number = NULL
> SELECT @.eLogic_Account_Status = NULL,@.eLogic_Pay_Type =
> NULL,@.eLogic_Auto_Renew = NULL,@.eLogic_Account_Type = NULL
> SELECT @.Promo_Code_Pos2 = NULL
> IF ( @.SetCode = 'C' OR @.SetCode ='E') --SetCode 'C' and 'E' denote donee
> BEGIN --B2
> SELECT @.Donor_Account_number = nr.account_number,@.Donor_Postal_Code =
> nr.postal_code,@.Donor_Company_Name = nr.company_name,
> @.Donor_Address_Line_1 = nr.address_line_1,@.Donor_Address_Line_2 =
> nr.address_line_2,@.Donor_City = nr.city,
> @.Donor_State_Prov = nr.state_prov,@.Donor_Country_Code =
> nr.country_code,@.Donor_Telephone = nr.telephone,@.Donor_Fax_Number =
> nr.fax_number
> FROM cds_name_record nr
> JOIN cds_order_record ord
> ON ord.account_number=nr.account_number
> AND ord.publisher_code=nr.publisher_code
> AND ord.mag_code=nr.mag_code
> WHERE GETDATE() BETWEEN nr.address_start_date AND nr.address_end_date -
-
> Get Only current address Name record
> AND (ord.setcode='B' OR ord.setcode='D')--Get only Donor Orders
> AND (ord.order_status='B' OR ord.order_status='D') --Get only Base Orde
r
> or Non-Subscibing Donor Order
> AND nr.publisher_code = @.Publisher_Code
> AND nr.mag_code = @.Mag_Code
> AND ord.order_number = @.Order_Number
> -- Note : Donor and Donee will have different account numbers, but same
> publisher code, mag code and order number
> END --END B2
>
> SET @.eLogic_Account_Status = CASE
> WHEN @.Service_Status = 'A' THEN 'A'
> WHEN @.Service_Status IN ( 'B','H','I') THEN 'C'
> WHEN @.Service_Status = 'C' THEN 'X'
> WHEN @.Service_Status IN ('D','E','F','G') THEN 'O'
> END
> SET @.eLogic_Pay_Type = CASE
> WHEN @.Order_Entry_Type IN ('A','B','C','D') THEN 'P'
> WHEN @.Order_Entry_Type IN ('L','M','U') THEN 'F'
> ELSE ''
> END
> SET @.eLogic_Auto_Renew= CASE
> WHEN ( SUBSTRING(@.Document_Key,1,1)= '#' OR @.Medium_Code = 'E') THEN
1
> ELSE 0
> END
> SET @.Promo_Code_Pos2 = SUBSTRING(@.Document_Key,2,1)
> SET @.eLogic_Account_Type=CASE
> WHEN @.Source_Code = 'CC' THEN
> CASE
> WHEN @.Promo_Code_Pos2 = 'T' THEN 'FREETRIAL'
> WHEN @.Promo_Code_Pos2 = 'E' THEN 'EMAILONLY'
> ELSE 'CONTROLLED'
> END
> WHEN @.Source_Code = 'CA' THEN 'COMP'
> ELSE 'PAID'
> END
>
> SELECT top 1 @.Reg_Visitor_Id = A.reg_visitor_id
> FROM account A
> JOIN publication_subscription PS
> ON A.pub_code = PS.code
> WHERE A.account_number = @.Account_Number
> AND PS.external_multi_mag_code = @.Publisher_Code
> --If a reg_visitor is not determined in the Staging tables or by looking
up
> the Account table , a new reg_visitor
> --is created
> IF ( @.eLogic_Reg_Visitor_Id IS NULL AND @.Reg_Visitor_Id IS NULL)
> BEGIN --B3
> --No Reg_Visitor corresponding to the mag subscription - mag subscriptio
n
> generated at CDS
> --Create a new reg_visitor and associated rows in Account,Account_Order
> and visitor_demographic
> SELECT @.Reg_Visitor_Product = master_brand
> FROM publication
> WHERE external_multi_mag_code=@.Publisher_Code
> --insert into reg_visior table
> EXEC @.Ret= dbo.upd_reg_visitor @.p_reg_visitor_id = @.Reg_Visitor_Id OUTP
UT,
> @.p_common_name = @.Common_Name,
> @.p_company_name = @.Company_Name,
> @.p_email = @.Current_Email_Address,
> @.p_encrypted_password = @.Email_Password,
> @.p_given_name = NULL,
> @.p_login_id = @.Email_User_Name,
> @.p_merged_visitor_id = NULL,
> @.p_middle_initial = NULL,
> @.p_middle_name = NULL,
> @.p_name_suffix = NULL,
> @.p_password = @.Email_Password,
> @.p_product = @.Reg_Visitor_Product,
> @.p_subproduct = NULL,
> @.p_professional_title = @.Job_Title,
> @.p_record_status = 1,
> @.p_registration_level = 1,
> @.p_salutation = NULL,
> @.p_sur_name = NULL,
> @.p_zip = NULL,
> @.p_country = 'TESTDTS'
> IF ( @.@.error <> 0 OR @.Ret < 0 )
> BEGIN
> RAISERROR('%s: Error inserting into Reg_Visitor table!', 18, 2, @.SP_NAM
E)
> --ROLLBACK TRAN T1
> RETURN -2
> END
> --Log details to reg_feed_detail_log
> EXEC Log_Reg_Feed_Details @.P_Publication_Id = @.eLogic_Publication_Id,
> @.P_Pub_Code = @.Mag_Code,
> --@.P_Process_Cycle_Id = SELECT DATEPART(dy, GETDATE()) ,
> @.P_Source_Key_1 = NULL,
> @.P_Source_Key_2 = NULL,
> @.P_Source_Key_3 = NULL,
> @.P_Target_Type = 'reg_visitor',
> @.P_Target_Key_1 = @.Reg_Visitor_Id,
> @.P_Target_Key_2 = NULL,
> @.P_Target_Key_3 = NULL,
> @.P_Process_Type = 'INSERT',
> @.P_Source_File_Name = @.Source_File_Name
> --insert into address table
> EXEC @.Ret= dbo.set_address @.p_address_id = @.Address_Id OUTPUT,
> @.p_reg_visitor_id = @.Reg_Visitor_Id,
> @.p_company_name = @.Company_Name,
> @.p_address_line_1 = @.Address_Line_1,
> @.p_address_line_2 = @.Address_Line_2,
> @.p_city = @.City,
> @.p_postal_code = @.Postal_Code,
> @.p_state_prov = @.State_Prov,
> @.p_country_code = @.Country_Code,
> @.p_phone = @.Telephone,
> @.p_fax = @.Fax_Number,
> @.p_address_type = 0 --shipping
> --Log details to reg_feed_detail_log
> EXEC Log_Reg_Feed_Details @.P_Publication_Id = @.eLogic_Publication_Id,
> @.P_Pub_Code = @.Mag_Code,
> --@.P_Process_Cycle_Id =SELECT DATEPART(dy, GETDATE()) ,
> @.P_Source_Key_1 = NULL,
> @.P_Source_Key_2 = NULL,
> @.P_Source_Key_3 = NULL,
> @.P_Target_Type = 'address',
> @.P_Target_Key_1 = @.Reg_Visitor_Id,
> @.P_Target_Key_2 = @.Address_Id ,
> @.P_Target_Key_3 = NULL,
> @.P_Process_Type = 'INSERT',
> @.P_Source_File_Name = @.Source_File_Name
> IF (@.SETCODE = 'C' OR @.SETCODE = 'E') -- Donee subscription
> BEGIN
> --Insert the corresponding Donor Address
> EXEC @.Ret= dbo.set_address @.p_address_id = @.Address_Id OUTPUT,
> @.p_reg_visitor_id = @.Reg_Visitor_Id,
> @.p_company_name = @.Donor_Company_Name,
> @.p_address_line_1 = @.Donor_Address_Line_1,
> @.p_address_line_2 = @.Donor_Address_Line_2,
> @.p_city = @.Donor_City,
> @.p_postal_code = @.Donor_Postal_Code,
> @.p_state_prov = @.Donor_State_Prov,
> @.p_country_code = @.Donor_Country_Code,
> @.p_phone = @.Donor_Telephone,
> @.p_fax = @.Donor_Fax_Number,
> @.p_address_type = 1 --Billing
> --IF @.Account_Number = '0010115103' OR @.Cnt =11214
> --INSERT INTO demo_data values ('inserted into address for donor')
> --Log details to reg_feed_detail_log
> EXEC Log_Reg_Feed_Details @.P_Publication_Id = @.eLogic_Publication_Id,
> @.P_Pub_Code = @.Mag_Code,
> --@.P_Process_Cycle_Id =SELECT DATEPART(dy, GETDATE()) ,
> @.P_Source_Key_1 = NULL,
> @.P_Source_Key_2 = NULL,
> @.P_Source_Key_3 = NULL,
> @.P_Target_Type = 'address',
> @.P_Target_Key_1 = @.Reg_Visitor_Id,
> @.P_Target_Key_2 = @.Address_Id ,
> @.P_Target_Key_3 = NULL,
> @.P_Process_Type = 'INSERT',
> @.P_Source_File_Name = @.Source_File_Name
> --IF @.Account_Number = '0010115103' OR @.Cnt =11214
> --INSERT INTO demo_data values ('inserted into detail log for donor
> address')
> END
> IF ( @.@.error <> 0 OR @.Ret < 0 )
> BEGIN
> RAISERROR('%s: Error inserting into Address table!', 18, 3, @.sp_name)
> CLOSE Subscriptions_Cursor
> DEALLOCATE Subscriptions_Cursor
> RETURN -3
> END
> --Insert into Account table
> EXEC @.Ret = dbo.set_account @.p_reg_visitor_id = @.Reg_Visitor_Id,
> @.p_account_id = @.Account_Id OUTPUT,
> @.p_account_number = @.Account_Number,
> @.p_pub_code = @.Mag_Code,
> @.p_account_type = @.eLogic_Account_type ,
> @.p_status = @.eLogic_Account_Status,
> @.p_supp_account_number = @.Donor_Account_Number -- If its a Non-Gift
> Order, NULL will be inserted for Supp_Account_Number
>
> IF ( @.@.error <> 0 OR @.Ret < 0 OR @.Account_Id IS NULL)
> BEGIN
> RAISERROR('%s: Error inserting into Account table!', 18, 4, @.SP_NAME)
> CLOSE Subscriptions_Cursor
> DEALLOCATE Subscriptions_Cursor
> RETURN -4
> END
> INSERT INTO Demo_data values ('completed inserting into account
> table',@.Reg_Visitor_id,@.Account_Number)
> --Log details to reg_feed_detail_log
> EXEC Log_Reg_Feed_Details @.P_Publication_Id = @.eLogic_Publication_Id,
> @.P_Pub_Code = @.Mag_Code,
> --@.P_Process_Cycle_Id =SELECT DATEPART(dy, GETDATE()) ,
> @.P_Source_Key_1 = NULL,
> @.P_Source_Key_2 = NULL,
> @.P_Source_Key_3 = NULL,
> @.P_Target_Type = 'account',
> @.P_Target_Key_1 = @.Reg_Visitor_Id,
> @.P_Target_Key_2 = @.Account_Id ,
> @.P_Target_Key_3 = NULL,
> @.P_Process_Type = 'INSERT',
> @.P_Source_File_Name = @.Source_File_Name
> INSERT INTO Demo_data values ('completed inserting into detail log for
> account table',@.Reg_Visitor_id,@.Account_Number)
> --insert into account_order table
> EXEC @.Ret = dbo.set_account_order @.p_reg_visitor_id = @.Reg_Visitor_Id,
> @.p_account_id = @.Account_Id,
> @.p_order_id = @.Order_Id OUTPUT,
> @.p_term = @.Order_Term,
> @.p_term_unit = NULL,
> @.p_pay_type = @.eLogic_Pay_Type,
> @.p_net_amt = @.Order_Net_Value,
> @.p_quantity = @.Num_Copies,
> @.p_vendor_order_number = @.Order_Number,
> @.p_promo_response_key = @.Document_Key,
> @.p_number_of_installments=1,
> @.p_auto_renew =@.eLogic_Auto_Renew
> IF ( @.@.error <> 0 OR @.Ret < 0 OR @.Order_Id IS NULL)
> BEGIN
> RAISERROR('%s: Error inserting into Account_Order table!', 18, 5, @.SP_N
AME)
> CLOSE Subscriptions_Cursor
> DEALLOCATE Subscriptions_Cursor
> RETURN -5
> END
> INSERT INTO Demo_data values ('completed inserting into account_order
> table',@.Reg_Visitor_id,@.Account_Number)
> --Log details to reg_feed_detail_log
> EXEC Log_Reg_Feed_Details @.P_Publication_Id = @.eLogic_Publication_Id,
> @.P_Pub_Code = @.Mag_Code,
> --@.P_Process_Cycle_Id =SELECT DATEPART(dy, GETDATE()) ,
> @.P_Source_Key_1 = NULL,
> @.P_Source_Key_2 = NULL,
> @.P_Source_Key_3 = NULL,
> @.P_Target_Type = 'account_order',
> @.P_Target_Key_1 = @.Reg_Visitor_Id,
> @.P_Target_Key_2 = @.Account_Id ,
> @.P_Target_Key_3 = @.Order_Id,
> @.P_Process_Type = 'INSERT',
> @.P_Source_File_Name = @.Source_File_Name
> INSERT INTO Demo_data values ('completed logging to detail log table for
> account_order',@.Reg_Visitor_id,@.Account_
Number)
> --insert into visitor_demographics table only for Online Magazines ;
> Delivery Type W - Online, P- Print
> IF(@.Delivery_Type = 'W')
> BEGIN
> INSERT INTO Demo_data values ('calling demographcis
> sp',@.Reg_Visitor_id,@.Account_Number)
> EXEC @.Ret = Process_CDS_Demographics @.Reg_Visitor_Id = @.Reg_Visitor_Id
,
> @.Mag_Code = @.Mag_Code,
> @.Demo_Data = @.Demo_Data,
> @.Segment_Number = @.Segment_Number,
> @.Version_Number = @.Version_Number
> IF ( @.@.error <> 0 OR @.Ret < 0 )
> BEGIN
> RAISERROR('%s: Error inserting/updating into Visitor_Demographics
> table!', 18, 6, @.SP_NAME)
> CLOSE Subscriptions_Cursor
> DEALLOCATE Subscriptions_Cursor
> RETURN -6
> END
> END
> INSERT INTO Demo_data values ('DEBUG LINE
> HIT',@.Reg_Visitor_id,@.Account_Number)
>
> END --END B3
> INSERT INTO demo_data (debug_message,reg_visitor_id,account_nu
mber)VALUES
> ('About to fetch next row- current row details
> -->',@.Reg_visitor_id,@.Account_number)
> FETCH NEXT FROM Subscriptions_Cursor
> INTO
> @.eLogic_Reg_Visitor_Id,@.eLogic_Publicat
ion_Id,@.Source_File_Name,@.Account_
Number ,@.Publisher_Code ,@.Mag_Code ,@.Postal_Code ,@.Common_Name ,@.Job_Title ,
> @.Company_Name ,@.Address_Line_1,@.Address_Line_2 ,@.City ,
> @.State_Prov ,@.Country_Code ,@.Telephone ,@.Fax_Number,
> @.eLogic_Account_Id,@.Service_Status ,@.Start_Issue ,@.Expire_Issue
> ,@.Num_Copies ,@.Email_User_Name ,@.Email_Password,
> @.Current_Email_Address,
> @.eLogic_Order_Id,@.Order_Number, @.Order_Status ,@.Order_Term
> ,@.Order_Net_Value ,@.Source_Code,@.Medium_Code ,
> @.Document_Key ,@.Setcode ,@.Orig_Start_Issue, @.Order_Entry_Type,
> @.Version_Number,@.Segment_Number,@.Demo_
Data,@.Delivery_Type
> INSERT INTO demo_data (debug_message,reg_visitor_id,account_nu
mber)VALUES
> ('fetched next row',@.eLogic_Reg_Visitor_Id,@.Account_num
ber)
> SET @.Cnt = @.Cnt + 1
> INSERT INTO demo_data (debug_message,reg_visitor_id,account_nu
mber)VALUES
> ('value of @.@.FETCHSTATUS =',@.@.Fetch_Status,@.Account_number)
> END --END B1
> CLOSE Subscriptions_Cursor
> DEALLOCATE Subscriptions_Cursor
>
> "Alejandro Mesa" wrote:
>