Stored Procedure

[marcmc]

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.SqlCo nnection("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.SqlCo mmand
cmd.Connection = cn

cmd.CommandText = ProcToExec
cmd.CommandType = CommandType.Sto redProcedure

cmd.Parameters. Add("@pRESULT", SqlDbType.Int)
cmd.Parameters( 0).Direction = ParameterDirect ion.Output

MessageBox.Show (ProcToExec)

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

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

[marcmc]

RE: Stored Procedure

-- The trace shows the following when run through SQLServer
-- usp_MISRE_Premi um
SELECT @PremYTD = isnull(sum(f_ba sic_premium * F_exch_rate),0)
FROM fat_bse_po_risk _detail a(nolock), trt_lu_trans_su btype b(nolock),
tit_lu_day c(nolock), POt_lu_policy d(nolock)
WHERE a.Tr_sub_type_i d = b.Tr_sub_type_i d
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','HR Q','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))SEL ECT [Logged_In]=[Logged_In] FROM [mis_MISRE_e
(@1 varchar(30),@2 varchar(30))UPD ATE [MIS_RECON_Routi nes_parms]
SELECT pId FROM MIS_RECON_Routi nes_parms WHERE pId = '0' AND pSe
(@1 varchar(30),@2 varchar(30))UPD ATE [MIS_RECON_Routi nes_parms]
UPDATE mis_MISRE_emplo yees SET Logged_In = 'N' WHERE uname = 'ma'
(@1 varchar(30),@2 varchar(30))UPD ATE [mis_MISRE_emplo yees] SET
SELECT TOP 1 id, log_desc FROM MISRE_dss_log WHERE LogStat = 'Y'
(@1 varchar(30),@2 varchar(30))SEL ECT [pId]=[pId] FROM [MIS_RECO
-- usp_MISRE_Premi um
SELECT @PremYTD = isnull(sum(f_ba sic_premium * F_exch_rate),0)
FROM fat_bse_po_risk _detail a(nolock), trt_lu_trans_su btype b(nolock),
tit_lu_day c(nolock), POt_lu_policy d(nolock)
WHERE a.Tr_sub_type_i d = b.Tr_sub_type_i d
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','HR Q','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_Premi um
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_Premi um
SELECT @PremYTD = isnull(sum(f_ba sic_premium * F_exch_rate),0)
FROM fat_bse_po_risk _detail a(nolock), trt_lu_trans_su btype b(nolock),
tit_lu_day c(nolock), POt_lu_policy d(nolock)
WHERE a.Tr_sub_type_i d = b.Tr_sub_type_i d
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','HR Q','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_Premi um @pRESULT = @P1 output
select @P1

RPC Output Parameter NULL .Net SqlClient Data Provider
exec sp_reset_connec tion


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

[marcmc]

RE: Stored Procedure

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

SELECT uname FROM mis_MISRE_emplo yees WHERE uname = 'marc'
(@1 varchar(30),@2 varchar(30))UPD ATE [MIS_RECON_Routi nes_parms]
(@1 varchar(30),@2 varchar(30))SEL ECT [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.

[marcmc]

RE: Stored Procedure

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.94693 509


"marcmc" wrote:
[color=blue]
> and the next time is shows different code from the trace after the
> SELECT @PremYTD = isnull....
>
> SELECT uname FROM mis_MISRE_emplo yees WHERE uname = 'marc'
> (@1 varchar(30),@2 varchar(30))UPD ATE [MIS_RECON_Routi nes_parms]
> (@1 varchar(30),@2 varchar(30))SEL ECT [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.
>
>
>
>[/color]