Hi,

I have a main SP that is scheduled using SQL Agent. This SP calls other SPs
in a transaction block. The other SPs just inserts data into NBIFeed table.
Code is

Create Procedure SP_Main
As
Declare @Ret1, @Ret2, @Ret3 as integer

Begin Transaction T1

exec @Ret1 = SP_NBIFeed1
exec @Ret2 = SP_NBIFeed2
exec @Ret3 = SP_NBIFeed3

If @Ret1 = 0 and @Ret2 = 0 and @Ret3 = 0
Commit Transaction T1
else
Rollback Transaction T1

The SP_NBIFeed SPs just inserts data into NBIFeed table from other source
tables (simple insert query) and returns @@error to main SP.

The question is, why all SPs are not inserting data if it is scheduled? If
SP_Main is executed in query analyser, all SPs are inserting data. This is
happening in SQL 2000.

Hi
Have run SQL Server Profiler to see what is going on?


"Venkatesh" <Venkatesh@discussions.microsoft.com> wrote in message
news:21798B67-B5A0-4AEC-B1D9-B6055F2BB197@microsoft.com...
> Hi,
>
> I have a main SP that is scheduled using SQL Agent. This SP calls other
> SPs
> in a transaction block. The other SPs just inserts data into NBIFeed
> table.
> Code is
>
> Create Procedure SP_Main
> As
> Declare @Ret1, @Ret2, @Ret3 as integer
>
> Begin Transaction T1
>
> exec @Ret1 = SP_NBIFeed1
> exec @Ret2 = SP_NBIFeed2
> exec @Ret3 = SP_NBIFeed3
>
> If @Ret1 = 0 and @Ret2 = 0 and @Ret3 = 0
> Commit Transaction T1
> else
> Rollback Transaction T1
>
> The SP_NBIFeed SPs just inserts data into NBIFeed table from other source
> tables (simple insert query) and returns @@error to main SP.
>
> The question is, why all SPs are not inserting data if it is scheduled? If
> SP_Main is executed in query analyser, all SPs are inserting data. This is
> happening in SQL 2000.

I have limited access and can't use SQL profiler on prod server. I wrote the
code to log and all these SPs are getting called and returning 0. I guess the
code was working earlier and I just added one more sp say SP_NBIFeed3, which
is not inserting additional records.


"Uri Dimant" wrote:

> Hi
> Have run SQL Server Profiler to see what is going on?
>
>
> "Venkatesh" <Venkatesh@discussions.microsoft.com> wrote in message
> news:21798B67-B5A0-4AEC-B1D9-B6055F2BB197@microsoft.com...
> > Hi,
> >
> > I have a main SP that is scheduled using SQL Agent. This SP calls other
> > SPs
> > in a transaction block. The other SPs just inserts data into NBIFeed
> > table.
> > Code is
> >
> > Create Procedure SP_Main
> > As
> > Declare @Ret1, @Ret2, @Ret3 as integer
> >
> > Begin Transaction T1
> >
> > exec @Ret1 = SP_NBIFeed1
> > exec @Ret2 = SP_NBIFeed2
> > exec @Ret3 = SP_NBIFeed3
> >
> > If @Ret1 = 0 and @Ret2 = 0 and @Ret3 = 0
> > Commit Transaction T1
> > else
> > Rollback Transaction T1
> >
> > The SP_NBIFeed SPs just inserts data into NBIFeed table from other source
> > tables (simple insert query) and returns @@error to main SP.
> >
> > The question is, why all SPs are not inserting data if it is scheduled? If
> > SP_Main is executed in query analyser, all SPs are inserting data. This is
> > happening in SQL 2000.
>
>
>