G
Guest
Have you ever heard of a bug in .Net that uses the below to execute a sProc
and only executes half the sProc before ducking out(without error) with a
return value of NULL
Here's the clincher, I can run my sProc in SQLServer no problem
This particular sProc is the only one I have trouble with. All the rest run
fine!!
It stops at the population of @PremYTD (the first large piece of sql) and
does nothing after that. @PremYTD is numeric(30,8) and has a value
143848559.94693509
Try
cn = New SqlClient.SqlConnection("user id=" & UserName.Text & ";password="
& Password.Text & ";database=" & Database.Text & ";server=" & Server.Text)
cn.Open()
Catch ex As Exception
sqlCnError = ("Error: Could not establish database connection")
End Try
cmd = New SqlClient.SqlCommand
cmd.Connection = cn
cmd.CommandText = ProcToExec
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.Add("@pRESULT", SqlDbType.Int)
cmd.Parameters(0).Direction = ParameterDirection.Output
Try
cmd.ExecuteNonQuery()
Catch ex As SqlClient.SqlException
sqlCnError = ("Error: Could not execute " + ProcToExec + " stored procedure")
End Try
cmd.Dispose()
cn.Close()
cn.Dispose()
SPROC
USE db
if exists (select * from sysobjects where id =
object_id(N'[dbo].[usp_MISRE_Premium]') and OBJECTPROPERTY(id,
N'IsProcedure') = 1)
drop procedure [dbo].[usp_MISRE_Premium]
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE usp_MISRE_Premium (@pRESULT varchar(200) output)
AS
/*
* Program: usp_MISRE_Premium
* Author: Marc McGuckian, JohnDoe.corp. MIS RECON Engine Project.
* Date: 24/Jun/2005
* Description: Runs specific sunday check on Premium reconciliation data.
* Purpose: Returns a parameter to MIS RECON Engine application to say
whether checkpoint succeeded/failed.
* Tables: MISRE_Prem_YTD_LW, tit_ta_six_week_roll, fat_bse_po_risk_detail,
trt_lu_trans_subtype, tit_lu_day
*
* Assumptions:
*
* Parameters : None
*
* Returns: @pRESULT
* Notes: ********************* check part iii) before running
********************
*
*
* History: 24/Jun/2005. Created
*
* Example: declare @result varchar(200)
exec usp_MISRE_Premium @pRESULT = @result
* sp_helptext usp_MISRE_Premium
* drop table MISRE_Prem_YTD_LW
* truncate table MISRE_Prem_YTD_LW
* select * from MISRE_Prem_YTD_LW
* insert into MISRE_Prem_YTD_LW values(200551, 10000,1000,10000,
'admin_mis')
*
* Time: <2 mins
*
* Modification History:
* Name Date Description
*
*/
BEGIN
SET NOCOUNT ON
-- 1. PREMIUM
IF NOT EXISTS (SELECT * FROM SYSOBJECTS WHERE name = 'MISRE_Prem_YTD_LW')
begin
CREATE TABLE MISRE_Prem_YTD_LW(
Id int identity,
Week_Id int,
PremYTD numeric(30,8),
PremTW numeric(30,8),
PremYTDLW numeric(30,8),
Entered_by varchar(15))
END
DECLARE @ID int, @Week_Id int, @Year_id int, @PremYTD numeric(30,8), @PremTW
numeric(30,8),
@PremYTDLW numeric(30,8), @Entered_by varchar(15), @PremYTDLS
numeric(30,8), @result int, @a numeric(30,8), @b numeric(30,8), @text
varchar(70), @ret int
SELECT @Week_Id = max(week_id) FROM tit_ta_six_week_roll(nolock) WHERE
latest_week_ind = 'Y'
SELECT @Year_id = substring(convert(varchar,week_id),1,4) FROM
tit_ta_six_week_roll(nolock) WHERE latest_week_ind = 'Y' and week_id IN
(SELECT max(week_id) FROM tit_ta_six_week_roll WHERE latest_week_ind = 'Y')
SELECT @Entered_by = 'admin_mis' -- will use logged in user
-- ii) Premium YTD
SELECT @PremYTD = isnull(sum(f_basic_premium * F_exch_rate),0)
FROM fat_bse_po_risk_detail a(nolock), trt_lu_trans_subtype b(nolock),
tit_lu_day c(nolock), POt_lu_policy d(nolock)
WHERE a.Tr_sub_type_id = b.Tr_sub_type_id
AND a.cur_trn_dt = c.cur_trn_dt
AND a.Policy_id = d.Policy_id
AND Tr_type_id NOT IN ('HDQ', 'HFQ','HMQ','HRQ','IEQ','ILP','IQT','IPR')
AND Po_corp_unit_id in ('GEI', 'GNI', 'GED')
AND Year_id = 2001 -- @Year_id
AND f_basic_premium is not null
if @@ERROR <> 0
begin
select @text = 'Error -10: Could not retrieve MISRE Premium Data for this
year'
select @result = -10
GOTO ERROR_POINT
end
-- iii) Premium This Week
SELECT @PremTW = isnull(sum(f_basic_premium * F_exch_rate),0)
FROM fat_bse_po_risk_detail a(nolock), trt_lu_trans_subtype b(nolock),
tit_lu_day c(nolock), POt_lu_policy d(nolock)
WHERE a.Tr_sub_type_id = b.Tr_sub_type_id
AND a.cur_trn_dt = c.cur_trn_dt
AND a.Policy_id = d.Policy_id
AND Tr_type_id NOT IN
('HNC','HRN','INB','IRN','HNB','HLP','HPR','HCN','ICN','HRS','IRS','HMA','HMC','HPY','HSP','IEN','HNA')
AND Po_corp_unit_id in ('GEI', 'GNI', 'GED')
AND Week_id = @Week_Id
AND f_basic_premium is not null
if @@ERROR <> 0
begin
select @text = 'Error -20: Could not retrieve MISRE Premium Data for this
week'
select @result = -20
GOTO ERROR_POINT
end
-- iv)
-- DO ONE ORIGINAL INSERT OF FIRST ROW, ALL SUCCESSIVE INSERTS WILL USE THE
PREVIUOS WEEK VALUES
INSERT INTO MISRE_Prem_YTD_LW(Week_Id, PremYTD, PremTW, Entered_by) VALUES
(@Week_Id, @PremYTD, @PremTW, @Entered_by)
SELECT @ID = Id FROM MISRE_Prem_YTD_LW WHERE Week_Id = @Week_Id
UPDATE a
SET PremYTDLW = b.PremYTD
FROM MISRE_Prem_YTD_LW a, MISRE_Prem_YTD_LW b
WHERE a.PremYTDLW IS NULL
AND b.Id = @ID - 1
If @@ERROR <> 0 or @@ROWCOUNT <= 0
begin
select @text = 'Error -30: Could not update MISRE_Prem_YTD_LW data'
select @result = -30
GOTO ERROR_POINT
end
-- v)
-- gets the sum of the premium that has cur_trn_dt of last saturday but load
date of not last saturday...
-- ...if it is equal to X then all fine
SELECT @PremYTDLS = isnull(sum(f_basic_premium * F_exch_rate),0)
FROM fat_bse_po_risk_detail a(nolock), trt_lu_trans_subtype b(nolock),
tit_lu_day c(nolock), POt_lu_policy d(nolock)
WHERE a.Tr_sub_type_id = b.Tr_sub_type_id
AND a.cur_trn_dt = c.cur_trn_dt
AND a.Policy_id = d.Policy_id
AND Tr_type_id NOT IN ('HDQ', 'HFQ','HMQ','HRQ','IEQ','ILP','IQT','IPR')
AND Po_corp_unit_id in ('GEI', 'GNI', 'GED')
AND a.cur_trn_dt = convert(char(12), dateAdd(dd, -8, getdate()),112)
AND Load_Date_id <> convert(char(12), dateAdd(dd, -8, getdate()),112)
AND f_basic_premium is not null
if @@ERROR <> 0
begin
select @text = 'Error -40: Could not retrieve MISRE Premium Data for this
year up until last Sunday'
select @result = -40
GOTO ERROR_POINT
end
SELECT @a = @PremYTD - @PremTW -- calculate @a for use in if cond.
IF @a <> @PremYTDLS BEGIN -- @PremYTDLW for that wk
SELECT @a = @b -- express a in terms of b to use in else if
SELECT @text = 'Premium Checkpoint Fail.'
SELECT @result = -1
SELECT @ret = -1
INSERT INTO MISRE_dss_log VALUES (@text, 'Premium', getdate(), 'Y')
End
Else If @PremYTDLS = @b Begin
SELECT @text = 'Premium Checkpoint Success.'
SELECT @result = 0
SELECT @ret = 0
INSERT INTO MISRE_dss_log VALUES (@text, 'Premium', getdate(), 'Y')
END
END
GOTO SUCCESS_POINT
BEGIN
ERROR_POINT:
PRINT 'ERROR_POINT'
GOTO FINISH
SUCCESS_POINT:
GOTO FINISH
FINISH:
select @pRESULT = @result
select @text = 'Premium returned ' + convert(varchar, @result)
print 'Return ' + convert(varchar, @result)
print @ret
-- raiserror below is used to pass message back to vb.net App via a
variable that works in vb syntax
-- raiserror(@text,1,1)
INSERT INTO MISRE_dss_log VALUES (@text, 'Premium', getdate(), 'Y')
INSERT INTO MISRE_dss_log VALUES ('', 'Premium', getdate(), 'Y')
UPDATE MIS_RECON_Routines_parms SET pSet = 'N' WHERE pID = 1
RETURN (@ret)
set nocount off
END
GO
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET NOCOUNT OFF
GO
and only executes half the sProc before ducking out(without error) with a
return value of NULL
Here's the clincher, I can run my sProc in SQLServer no problem
This particular sProc is the only one I have trouble with. All the rest run
fine!!
It stops at the population of @PremYTD (the first large piece of sql) and
does nothing after that. @PremYTD is numeric(30,8) and has a value
143848559.94693509
Try
cn = New SqlClient.SqlConnection("user id=" & UserName.Text & ";password="
& Password.Text & ";database=" & Database.Text & ";server=" & Server.Text)
cn.Open()
Catch ex As Exception
sqlCnError = ("Error: Could not establish database connection")
End Try
cmd = New SqlClient.SqlCommand
cmd.Connection = cn
cmd.CommandText = ProcToExec
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.Add("@pRESULT", SqlDbType.Int)
cmd.Parameters(0).Direction = ParameterDirection.Output
Try
cmd.ExecuteNonQuery()
Catch ex As SqlClient.SqlException
sqlCnError = ("Error: Could not execute " + ProcToExec + " stored procedure")
End Try
cmd.Dispose()
cn.Close()
cn.Dispose()
SPROC
USE db
if exists (select * from sysobjects where id =
object_id(N'[dbo].[usp_MISRE_Premium]') and OBJECTPROPERTY(id,
N'IsProcedure') = 1)
drop procedure [dbo].[usp_MISRE_Premium]
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE usp_MISRE_Premium (@pRESULT varchar(200) output)
AS
/*
* Program: usp_MISRE_Premium
* Author: Marc McGuckian, JohnDoe.corp. MIS RECON Engine Project.
* Date: 24/Jun/2005
* Description: Runs specific sunday check on Premium reconciliation data.
* Purpose: Returns a parameter to MIS RECON Engine application to say
whether checkpoint succeeded/failed.
* Tables: MISRE_Prem_YTD_LW, tit_ta_six_week_roll, fat_bse_po_risk_detail,
trt_lu_trans_subtype, tit_lu_day
*
* Assumptions:
*
* Parameters : None
*
* Returns: @pRESULT
* Notes: ********************* check part iii) before running
********************
*
*
* History: 24/Jun/2005. Created
*
* Example: declare @result varchar(200)
exec usp_MISRE_Premium @pRESULT = @result
* sp_helptext usp_MISRE_Premium
* drop table MISRE_Prem_YTD_LW
* truncate table MISRE_Prem_YTD_LW
* select * from MISRE_Prem_YTD_LW
* insert into MISRE_Prem_YTD_LW values(200551, 10000,1000,10000,
'admin_mis')
*
* Time: <2 mins
*
* Modification History:
* Name Date Description
*
*/
BEGIN
SET NOCOUNT ON
-- 1. PREMIUM
IF NOT EXISTS (SELECT * FROM SYSOBJECTS WHERE name = 'MISRE_Prem_YTD_LW')
begin
CREATE TABLE MISRE_Prem_YTD_LW(
Id int identity,
Week_Id int,
PremYTD numeric(30,8),
PremTW numeric(30,8),
PremYTDLW numeric(30,8),
Entered_by varchar(15))
END
DECLARE @ID int, @Week_Id int, @Year_id int, @PremYTD numeric(30,8), @PremTW
numeric(30,8),
@PremYTDLW numeric(30,8), @Entered_by varchar(15), @PremYTDLS
numeric(30,8), @result int, @a numeric(30,8), @b numeric(30,8), @text
varchar(70), @ret int
SELECT @Week_Id = max(week_id) FROM tit_ta_six_week_roll(nolock) WHERE
latest_week_ind = 'Y'
SELECT @Year_id = substring(convert(varchar,week_id),1,4) FROM
tit_ta_six_week_roll(nolock) WHERE latest_week_ind = 'Y' and week_id IN
(SELECT max(week_id) FROM tit_ta_six_week_roll WHERE latest_week_ind = 'Y')
SELECT @Entered_by = 'admin_mis' -- will use logged in user
-- ii) Premium YTD
SELECT @PremYTD = isnull(sum(f_basic_premium * F_exch_rate),0)
FROM fat_bse_po_risk_detail a(nolock), trt_lu_trans_subtype b(nolock),
tit_lu_day c(nolock), POt_lu_policy d(nolock)
WHERE a.Tr_sub_type_id = b.Tr_sub_type_id
AND a.cur_trn_dt = c.cur_trn_dt
AND a.Policy_id = d.Policy_id
AND Tr_type_id NOT IN ('HDQ', 'HFQ','HMQ','HRQ','IEQ','ILP','IQT','IPR')
AND Po_corp_unit_id in ('GEI', 'GNI', 'GED')
AND Year_id = 2001 -- @Year_id
AND f_basic_premium is not null
if @@ERROR <> 0
begin
select @text = 'Error -10: Could not retrieve MISRE Premium Data for this
year'
select @result = -10
GOTO ERROR_POINT
end
-- iii) Premium This Week
SELECT @PremTW = isnull(sum(f_basic_premium * F_exch_rate),0)
FROM fat_bse_po_risk_detail a(nolock), trt_lu_trans_subtype b(nolock),
tit_lu_day c(nolock), POt_lu_policy d(nolock)
WHERE a.Tr_sub_type_id = b.Tr_sub_type_id
AND a.cur_trn_dt = c.cur_trn_dt
AND a.Policy_id = d.Policy_id
AND Tr_type_id NOT IN
('HNC','HRN','INB','IRN','HNB','HLP','HPR','HCN','ICN','HRS','IRS','HMA','HMC','HPY','HSP','IEN','HNA')
AND Po_corp_unit_id in ('GEI', 'GNI', 'GED')
AND Week_id = @Week_Id
AND f_basic_premium is not null
if @@ERROR <> 0
begin
select @text = 'Error -20: Could not retrieve MISRE Premium Data for this
week'
select @result = -20
GOTO ERROR_POINT
end
-- iv)
-- DO ONE ORIGINAL INSERT OF FIRST ROW, ALL SUCCESSIVE INSERTS WILL USE THE
PREVIUOS WEEK VALUES
INSERT INTO MISRE_Prem_YTD_LW(Week_Id, PremYTD, PremTW, Entered_by) VALUES
(@Week_Id, @PremYTD, @PremTW, @Entered_by)
SELECT @ID = Id FROM MISRE_Prem_YTD_LW WHERE Week_Id = @Week_Id
UPDATE a
SET PremYTDLW = b.PremYTD
FROM MISRE_Prem_YTD_LW a, MISRE_Prem_YTD_LW b
WHERE a.PremYTDLW IS NULL
AND b.Id = @ID - 1
If @@ERROR <> 0 or @@ROWCOUNT <= 0
begin
select @text = 'Error -30: Could not update MISRE_Prem_YTD_LW data'
select @result = -30
GOTO ERROR_POINT
end
-- v)
-- gets the sum of the premium that has cur_trn_dt of last saturday but load
date of not last saturday...
-- ...if it is equal to X then all fine
SELECT @PremYTDLS = isnull(sum(f_basic_premium * F_exch_rate),0)
FROM fat_bse_po_risk_detail a(nolock), trt_lu_trans_subtype b(nolock),
tit_lu_day c(nolock), POt_lu_policy d(nolock)
WHERE a.Tr_sub_type_id = b.Tr_sub_type_id
AND a.cur_trn_dt = c.cur_trn_dt
AND a.Policy_id = d.Policy_id
AND Tr_type_id NOT IN ('HDQ', 'HFQ','HMQ','HRQ','IEQ','ILP','IQT','IPR')
AND Po_corp_unit_id in ('GEI', 'GNI', 'GED')
AND a.cur_trn_dt = convert(char(12), dateAdd(dd, -8, getdate()),112)
AND Load_Date_id <> convert(char(12), dateAdd(dd, -8, getdate()),112)
AND f_basic_premium is not null
if @@ERROR <> 0
begin
select @text = 'Error -40: Could not retrieve MISRE Premium Data for this
year up until last Sunday'
select @result = -40
GOTO ERROR_POINT
end
SELECT @a = @PremYTD - @PremTW -- calculate @a for use in if cond.
IF @a <> @PremYTDLS BEGIN -- @PremYTDLW for that wk
SELECT @a = @b -- express a in terms of b to use in else if
SELECT @text = 'Premium Checkpoint Fail.'
SELECT @result = -1
SELECT @ret = -1
INSERT INTO MISRE_dss_log VALUES (@text, 'Premium', getdate(), 'Y')
End
Else If @PremYTDLS = @b Begin
SELECT @text = 'Premium Checkpoint Success.'
SELECT @result = 0
SELECT @ret = 0
INSERT INTO MISRE_dss_log VALUES (@text, 'Premium', getdate(), 'Y')
END
END
GOTO SUCCESS_POINT
BEGIN
ERROR_POINT:
PRINT 'ERROR_POINT'
GOTO FINISH
SUCCESS_POINT:
GOTO FINISH
FINISH:
select @pRESULT = @result
select @text = 'Premium returned ' + convert(varchar, @result)
print 'Return ' + convert(varchar, @result)
print @ret
-- raiserror below is used to pass message back to vb.net App via a
variable that works in vb syntax
-- raiserror(@text,1,1)
INSERT INTO MISRE_dss_log VALUES (@text, 'Premium', getdate(), 'Y')
INSERT INTO MISRE_dss_log VALUES ('', 'Premium', getdate(), 'Y')
UPDATE MIS_RECON_Routines_parms SET pSet = 'N' WHERE pID = 1
RETURN (@ret)
set nocount off
END
GO
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET NOCOUNT OFF
GO