Stored Procedure

[Guest]

I have code below that will only execute half of a procedure!!!
I can execute it fine in SQLServer.
The parametars are setup the same as are the returning params for other
sProcs that run fine. I have checked everything
Why does it only execute half the proc thru .net???
Very strange.

Private Function RunExecRoutines()

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

MessageBox.Show(ProcToExec)

Try
cmd.ExecuteNonQuery()
Catch ex As Exception
sqlCnError = ("Error: Could not execute procedure")
End Try

cmd.Dispose()
cn.Close()
cn.Dispose()

 

[Guest]

-- The trace shows the following when run through SQLServer
-- usp_MISRE_Premium
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

(@1 varchar(30))SELECT [Logged_In]=[Logged_In] FROM [mis_MISRE_e
(@1 varchar(30),@2 varchar(30))UPDATE [MIS_RECON_Routines_parms]
SELECT pId FROM MIS_RECON_Routines_parms WHERE pId = '0' AND pSe
(@1 varchar(30),@2 varchar(30))UPDATE [MIS_RECON_Routines_parms]
UPDATE mis_MISRE_employees SET Logged_In = 'N' WHERE uname = 'ma'
(@1 varchar(30),@2 varchar(30))UPDATE [mis_MISRE_employees] SET
SELECT TOP 1 id, log_desc FROM MISRE_dss_log WHERE LogStat = 'Y'
(@1 varchar(30),@2 varchar(30))SELECT [pId]=[pId] FROM [MIS_RECO
-- usp_MISRE_Premium
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
-- usp_MISRE_Premium
if @@ERROR <> 0

.....goes onto do other things from procedure and finishes

-- The trace shows the following when run through the vb.net app

-- usp_MISRE_Premium
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

RPC:Completed declare @P1 int
declare @P1 int
set @P1=NULL
exec usp_MISRE_Premium @pRESULT = @P1 output
select @P1

RPC Output Parameter NULL .Net SqlClient Data Provider
exec sp_reset_connection


....doesn't goto next statement, ducks out without fail

 

[Guest]

and the next time is shows different code from the trace after the
SELECT @PremYTD = isnull....

SELECT uname FROM mis_MISRE_employees WHERE uname = 'marc'
(@1 varchar(30),@2 varchar(30))UPDATE [MIS_RECON_Routines_parms]
(@1 varchar(30),@2 varchar(30))SELECT [pId]=[pId] FROM [MIS_RECO
TRUNCATE TABLE MISRE_dss_log

This just doesn't make any sense. The flow of control is the same, i have
other very similar sProcs running through the app and they work fine.
Any ideas greatly appreciated.

 

[Guest]

the trace seems to be picking up other stuff running on the server. I just
closed all my connections and saw that some other dimension stuff was coming
thru on the trace. However my stored procedure only got as far as it usually
does and then ducks out without error (i have plenty of error trapping in my
vb and my sql) and an output parameter of null.

What is happening? My other procedures just don't have this problem. The
query it last executes takes about 3 minutes to run and populates a numeric
(30, 8) variable with the value 143848559.94693509