"Uri Dimant" wrote:

> Larry
> If I understand properly,you are missing OUTPUT command to get out a value
> of SCOPE IDENTITY
>
> You can more examples in the BOL
>
>
>
>
> "Larry Bird" <LarryBird@discussions.microsoft.com> wrote in message
> news:EC9ED280-5A3C-489C-BF76-78609430A784@microsoft.com...
> > I tired your suggestion using "Set NoCOUNT On". This did not change the
> results at all. Why might the record set be empty? I'm doing a "Select
> SCOPE_Identity()" Should there me more code in the stored procedure to
> return the identity?
> >
> >
> > "Roji. P. Thomas" wrote:
> >
> > > Try adding SET NOCOUNT ON
> > >
> > > --
> > > Roji. P. Thomas
> > > SQL Server Programmer
> > > "Larry Bird" <LarryBird@discussions.microsoft.com> wrote in message
> > > news:E489BB96-9CDF-4071-8E21-783BEEE7FE38@microsoft.com...
> > > > I am trying to retrieve the identity of a previous inserted record.
> I'm
> > > using the Scope_Identity in my stored procecedure as follows:
> > > >
> > > > CREATE PROCEDURE dbo.InsertEventCriteria @VehicleMinNum char(10),
> > > @AlertCategory char, @ReportingFrequency int, @ConfirmRFStartDate
> char(10),
> > > @ConfirmRFStartTime char(8), @ConfirmRFEndDate char(10),
> > > >
> > > @ConfirmRFEndTime char(8), @SageQuestDateTime datetime,
> > > @ConfirmTimeFactorBeginTime char(8), @ConfirmTimeFactorEndTime char(8),
> > > @DBMonday char, @DBTuesday char, @DBWednesday char,
> > > >
> > > @DBThursday char, @DBFriday char, @DBSaturday char, @DBSunday char,
> > > @ConfirmEmailAddresses char(200), @CriteriaDisposition char
> > > > AS Insert into EventCriteria(Min_Num, EventType,ReportingFrequency,
> > > TimeToLiveStartDate, TimeToLiveStartTime, TimeToLiveStopDate,
> > > TimeToLiveStopTime, DateTimeStamp, BeginTime, EndTime, Monday, Tuesday,
> > > Wednesday, Thursday, Friday,
> > > > Saturday, Sunday, EmailAddresses,
> > > DispositionDisplay )
> > > > Values (@VehicleMinNum, @AlertCategory, @ReportingFrequency,
> > > @ConfirmRFStartDate, @ConfirmRFStartTime, @ConfirmRFEndDate,
> > > @ConfirmRFEndTime, @SageQuestDateTime, @ConfirmTimeFactorBeginTime,
> > > @ConfirmTimeFactorEndTime,
> > > > @DBMonday, @DBTuesday, @DBWednesday, @DBThursday,
> @DBFriday,
> > > @DBSaturday, @DBSunday, @ConfirmEmailAddresses, @CriteriaDisposition)
> > > >
> > > > SELECT SCOPE_IDENTITY() AS 'CriteriaID'
> > > > GO
> > > >
> > > > When I execute the stored procedure above I use the record set ordinal
> > > number to retrieve the return record as follows:
> > > >
> > > > Set Alertrs = AlertCMD.Execute
> > > >
> > > > TempCriteriaID = Alertrs(0)
> > > >
> > > > The stored procedure runs well because data is being added to the
> table.
> > > I'm just not getting the identity of the inserted record.
> > > >
> > > > I get the following error message:
> > > >
> > > > Runtime error: 3265
> > > >
> > > > The item cannot be found in the collection corresponding the requested
> > > name or ordinal number.
> > > >
> > > > What I'm I doing wrong here?
> > > >
> > >
> > >
> > >
>
>