SET NOCOUNT ON

GO 

--declare @reqno int
--set @reqno = 231675
declare @num_of_times int
declare @count int
declare @frequency varchar(10)
declare @num_of_times1 int

 
DECLARE @oldrequestid varchar(50),@newrequestid varchar(50)

DECLARE db_cursor CURSOR FOR 
SELECT Requestid from report_request where requestid in (149142)

OPEN db_cursor   
FETCH NEXT FROM db_cursor INTO @oldrequestid


WHILE @@FETCH_STATUS = 0   
BEGIN  
     
     --do work here    
     
SET @num_of_times = NULL
select @num_of_times=datediff(month, receiveddate,expirationdate) from report_request where requestid in (@oldrequestid) 
SET @num_of_times1 = @num_of_times+1




set @count=0
WHILE @count < @num_of_times1 
BEGIN
     
update table_keys
set key_id = key_id + 1 
where table_name = 'adhoc'

Select @newrequestid = key_id from table_keys where table_name = 'adhoc'

     
INSERT INTO [UnderWriting].[dbo].[renoffcyc]
           ([roc_id]
           ,[roc_ind]
           ,[fundtype]
           ,[prodmth]
           ,[opa_id]
           ,[hlthplnid]
           ,[targetdt]
           ,[actualdt]
           ,[freqcd]
           ,[Comments]
           ,[entereddt]
           ,[pltfrmind]
           ,[groupcnt]
           ,[rptdesc]
            ,[rptrsrcetxt]
           ,[tat]        
           ,[mktsgnm]
           ,[date_available]
            ,[requestorid]
           ,[groupname]
           ,[groupnbr]
           ,[tool]
           ,[datarecpdet]
           ,[HRS]
           ,[mins]
           ,[busneed]
           ,[origrequestid]
           )
  
           
SELECT @newrequestid
      ,'O'
       ,RR.fundingtype
           ,convert(varchar(30),DATEADD (month , @count+1 ,RR.Receiveddate))
           ,SR.Assignto
           ,RR.hlthplnid
           ,convert(varchar(30),DATEADD (month , @count+1 ,RR.Receiveddate))

           ,convert(varchar(30),DATEADD (month , @count+1 ,RR.Receiveddate))
           ,RR.defineschedule
           ,RR.rptcomments
           ,convert(varchar(30),DATEADD (month , @count+1 ,RR.Receiveddate))
			,SR.pltfmcd
			,SR.groupcnt
			,SR.rptdesc
			,SR.resource
           ,SR.tat
           ,mktsg
           ,convert(varchar(30),DATEADD (month , @count+1 ,RR.Receiveddate))
           ,RR.requestor
           ,RR.CTNAME
           ,RR.MSGN
           ,SR.tool
           ,SR.datarecpdet
           ,SR.hrs
           ,SR.mins
           ,RR.busneed
           ,@oldrequestid
   

             
FROM [UnderWriting].[dbo].[REPORT_REQUEST] RR FULL OUTER JOIN SELECTED_REPORT SR
ON RR.requestid = SR.requestid
where RR.requestid = @oldrequestid



set @count=@count+1 
END

FETCH NEXT FROM db_cursor INTO @oldrequestid
END  -- Cursor loop
CLOSE db_cursor
DEALLOCATE db_cursor

quote:

---

Originally posted by Bustaz Kool

It sounds to me that another process is trying to insert records into the table. There is a window of (bad) opportunity when both processes are accessing the table_keys table and they end up retreiving the same value. The transaction would act to serialize the two processes so that the first would update and select before the second could gain access to the table/row.

=================================================
We are far more concerned about the desecration of the flag than we are about the desecration of our land. -Wendell Berry

---

quote:

---

Originally posted by Lamprey

What is the definition of the PK (PK__RENOFFCYC__464936E5)?

---

quote:

---

Originally posted by Lamprey

Well you are doing a FULL OUTER JOIN which, apparently, is producing more than one row. I don't know how you would determine which row your are interested in. But, if you don't care which one, then you could just add a TOP 1 clause to the SELECT. Otherwise, you'll have to figure out how rank the rows and only use one or change the logic so you can insert multiple rows.

---