April 12, 2010 at 2:18 am
Hello,
The below SP has two insert stmnts. the first is to the master and the second to the detail.
the SP is working till the first insert statement.
the second insert must insert 1 lakh records, but it is going to catch block after inserting 252 records.....
please help.
BEGIN
BEGIN TRY
DECLARE @contactid uniqueidentifier
DECLARE @companyname varchar
DECLARE @historytypeid int
DECLARE @endtime datetime
DECLARE @duration int
DECLARE @subject varchar
DECLARE @ttyperid bigint
DECLARE @rid bigint
DECLARE @title varchar(100)
DECLARE @tasktypeid int
DECLARE @flag bigint
SET @flag = 0
DECLARE cur cursor STATIC LOCAL
FOR
SELECT tc.contactid,tc.companyname,
th.historytypeid,
th.endtime,
th.duration,
th.regarding,
tht.name
FROM tp.dbo.tbl_contact tc
JOIN tp.dbo.tbl_contact_history tch ON tc.contactid = tch.contactid
JOIN tp.dbo.tbl_history th ON th.historyid = tch.historyid
JOIN tp.dbo.tbl_historytype tht ON tht.historytypeid = th.historytypeid
WHERE category IS NOT NULL
AND th.historytypeid<>-1
OPEN cur
FETCH cur INTO @contactid,@companyname,@historytypeid,@endtime,@duration,@subject,@title
print @contactid
print @companyname
print @historytypeid
print @endtime
print @duration
print @subject
print @title
print 'cur fetched'
WHILE @@fetch_status = 0
BEGIN
BEGIN
BEGIN TRANSACTION
SET @rid = 0
SELECT @rid = MAX(rid)
FROM hc_clients
WHERE clientname = @companyname
print @rid
SET @tasktypeid = 0
SELECT @tasktypeid = MAX(rid) FROM hcm_task_type WHERE title = @title
IF @tasktypeid IS NULL
BEGIN
INSERT INTO hcm_task_type(title) VALUES(@title)
SELECT @tasktypeid = MAX(rid) FROM hcm_task_type WHERE title = @title AND @title IS NOT NULL
END
SET @flag = @flag + 1
print @flag
INSERT INTO hc_task_manager(tasktype,targetdate,reminder,duration,subject,status,tasktypeid,ttyperid,remindercheck)
VALUES (@tasktypeid,@endtime,0,@duration,@subject,0,1,@rid,0)
END
COMMIT TRANSACTION
FETCH cur INTO @contactid,@companyname,@historytypeid,@endtime,@duration,@subject,@title
END
CLOSE cur
DEALLOCATE cur
-- print @flag
END TRY
BEGIN CATCH
print 'Error'
ROLLBACK TRANSACTION
END CATCH
END
April 12, 2010 at 2:47 am
Replace the code in your catch block with something that returns useful information about the error:
BEGIN CATCH
DECLARE @ErrorMessage NVARCHAR(4000)
DECLARE @ErrorSeverity INT
DECLARE @ErrorState INT
SELECT @ErrorMessage = ERROR_MESSAGE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE()
IF @@TRANCOUNT > 0
ROLLBACK TRAN
RAISERROR ( @ErrorMessage, @ErrorSeverity, @ErrorState )
END CATCH
It won't solve your issue, but, at least, will give you something to investigate.
-- Gianluca Sartori
April 12, 2010 at 2:53 am
Thank You very much!
Infact i was looking for this catch block code.
It really helped me.
One of the inserted values were NULL. So the statement treminated after tht.
Thanks a lot again....
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply