Crash with "Failed Assertion"

Hello,
I have a table with 2 indexes.
My C++ program deletes some data from this table and then
does bcp data in. It does it twice in one run.
After I run my program for the first time I get the
correct result. When I run it again, the first bcp is
successful, however there is no data from the second bcp.
If I repeat it again, the result is correct; one more
time - the data from the first bcp only.
If I try to debug it, everything works fine.
If I run it without break points in the program, I
duplicate a problem.
SQL Server log records an error:
Failed Assertion = '(logMode != nonlogged) || (dbt-
>dbt_dbid == TEMPDBID)'
and creates a SQLdump file, which I don't know how to
interprit.
If I remove indexes from the table, everything works fine.
Does anyone understand what's going on?
I would appriciate any help.
Thanks.Are your indexes unique indexes? Chances are thats why it's failing the
second time, if you're using the same data in you're BCP load.
-Morgan
"Tanya Bardakh" <tbardakh@.dysanalytics.com> wrote in message
news:0cf001c39d5d$a3068a20$a401280a@.phx.gbl...
> Hello,
>
> I have a table with 2 indexes.
> My C++ program deletes some data from this table and then
> does bcp data in. It does it twice in one run.
> After I run my program for the first time I get the
> correct result. When I run it again, the first bcp is
> successful, however there is no data from the second bcp.
> If I repeat it again, the result is correct; one more
> time - the data from the first bcp only.
> If I try to debug it, everything works fine.
> If I run it without break points in the program, I
> duplicate a problem.
> SQL Server log records an error:
> Failed Assertion = '(logMode != nonlogged) || (dbt-
> >dbt_dbid == TEMPDBID)'
> and creates a SQLdump file, which I don't know how to
> interprit.
> If I remove indexes from the table, everything works fine.
> Does anyone understand what's going on?
> I would appriciate any help.
> Thanks.
>
>|||Thank you for the reply.
My indexes are not unique, nor cluster.
Tanya.
>--Original Message--
>Are your indexes unique indexes? Chances are thats why
it's failing the
>second time, if you're using the same data in you're BCP
load.
>-Morgan
>"Tanya Bardakh" <tbardakh@.dysanalytics.com> wrote in
message
>news:0cf001c39d5d$a3068a20$a401280a@.phx.gbl...
>> Hello,
>>
>> I have a table with 2 indexes.
>> My C++ program deletes some data from this table and
then
>> does bcp data in. It does it twice in one run.
>> After I run my program for the first time I get the
>> correct result. When I run it again, the first bcp is
>> successful, however there is no data from the second
bcp.
>> If I repeat it again, the result is correct; one more
>> time - the data from the first bcp only.
>> If I try to debug it, everything works fine.
>> If I run it without break points in the program, I
>> duplicate a problem.
>> SQL Server log records an error:
>> Failed Assertion = '(logMode != nonlogged) || (dbt-
>> >dbt_dbid == TEMPDBID)'
>> and creates a SQLdump file, which I don't know how to
>> interprit.
>> If I remove indexes from the table, everything works
fine.
>> Does anyone understand what's going on?
>> I would appriciate any help.
>> Thanks.
>>
>
>.
>|||Any replication on the tables involved?
How about triggers?
If the answer to the above is "no", the only thing I can think of is that
you're running out of space in the logfile and you have configured for
"Simple" recovery model. It would work in "debug" because SQLServer would
have time to remove the previous checkpoint...
Bruce
"Tanya Bardakh" <anonymous@.discussions.microsoft.com> wrote in message
news:0de201c39d64$5d354b10$a401280a@.phx.gbl...
> Thank you for the reply.
> My indexes are not unique, nor cluster.
> Tanya.
>
> >--Original Message--
> >Are your indexes unique indexes? Chances are thats why
> it's failing the
> >second time, if you're using the same data in you're BCP
> load.
> >
> >-Morgan
> >
> >"Tanya Bardakh" <tbardakh@.dysanalytics.com> wrote in
> message
> >news:0cf001c39d5d$a3068a20$a401280a@.phx.gbl...
> >> Hello,
> >>
> >>
> >> I have a table with 2 indexes.
> >> My C++ program deletes some data from this table and
> then
> >> does bcp data in. It does it twice in one run.
> >>
> >> After I run my program for the first time I get the
> >> correct result. When I run it again, the first bcp is
> >> successful, however there is no data from the second
> bcp.
> >> If I repeat it again, the result is correct; one more
> >> time - the data from the first bcp only.
> >>
> >> If I try to debug it, everything works fine.
> >>
> >> If I run it without break points in the program, I
> >> duplicate a problem.
> >>
> >> SQL Server log records an error:
> >> Failed Assertion = '(logMode != nonlogged) || (dbt-
> >> >dbt_dbid == TEMPDBID)'
> >> and creates a SQLdump file, which I don't know how to
> >> interprit.
> >>
> >> If I remove indexes from the table, everything works
> fine.
> >>
> >> Does anyone understand what's going on?
> >>
> >> I would appriciate any help.
> >> Thanks.
> >>
> >>
> >>
> >
> >
> >.
> >|||Thank you, Bruce
I don't have any triggers & replications.
Space should not be an issue, since I have plenty of it
(30 GB). Actually I am reloading same data (about 200
rows) over and over again. The only thing is it's
successful the first time and crashes SQL server on the
subsequent run.
>--Original Message--
>Any replication on the tables involved?
>How about triggers?
>If the answer to the above is "no", the only thing I can
think of is that
>you're running out of space in the logfile and you have
configured for
>"Simple" recovery model. It would work in "debug"
because SQLServer would
>have time to remove the previous checkpoint...
>Bruce
>"Tanya Bardakh" <anonymous@.discussions.microsoft.com>
wrote in message
>news:0de201c39d64$5d354b10$a401280a@.phx.gbl...
>> Thank you for the reply.
>> My indexes are not unique, nor cluster.
>> Tanya.
>>
>> >--Original Message--
>> >Are your indexes unique indexes? Chances are thats why
>> it's failing the
>> >second time, if you're using the same data in you're
BCP
>> load.
>> >
>> >-Morgan
>> >
>> >"Tanya Bardakh" <tbardakh@.dysanalytics.com> wrote in
>> message
>> >news:0cf001c39d5d$a3068a20$a401280a@.phx.gbl...
>> >> Hello,
>> >>
>> >>
>> >> I have a table with 2 indexes.
>> >> My C++ program deletes some data from this table and
>> then
>> >> does bcp data in. It does it twice in one run.
>> >>
>> >> After I run my program for the first time I get the
>> >> correct result. When I run it again, the first bcp is
>> >> successful, however there is no data from the second
>> bcp.
>> >> If I repeat it again, the result is correct; one more
>> >> time - the data from the first bcp only.
>> >>
>> >> If I try to debug it, everything works fine.
>> >>
>> >> If I run it without break points in the program, I
>> >> duplicate a problem.
>> >>
>> >> SQL Server log records an error:
>> >> Failed Assertion = '(logMode != nonlogged) || (dbt-
>> >> >dbt_dbid == TEMPDBID)'
>> >> and creates a SQLdump file, which I don't know how to
>> >> interprit.
>> >>
>> >> If I remove indexes from the table, everything works
>> fine.
>> >>
>> >> Does anyone understand what's going on?
>> >>
>> >> I would appriciate any help.
>> >> Thanks.
>> >>
>> >>
>> >>
>> >
>> >
>> >.
>> >
>
>.
>|||You should contact Product Support (http://support.microsoft.com) who will
be able to help you figure this out.
Regards,
Paul.
--
Paul Randal
DBCC Technical Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"bcarson@.asgoth.com" <anonymous@.discussions.microsoft.com> wrote in message
news:027e01c39d82$9d9c2070$a301280a@.phx.gbl...
> Thank you, Bruce
> I don't have any triggers & replications.
> Space should not be an issue, since I have plenty of it
> (30 GB). Actually I am reloading same data (about 200
> rows) over and over again. The only thing is it's
> successful the first time and crashes SQL server on the
> subsequent run.
>
> >--Original Message--
> >Any replication on the tables involved?
> >How about triggers?
> >
> >If the answer to the above is "no", the only thing I can
> think of is that
> >you're running out of space in the logfile and you have
> configured for
> >"Simple" recovery model. It would work in "debug"
> because SQLServer would
> >have time to remove the previous checkpoint...
> >
> >Bruce
> >
> >"Tanya Bardakh" <anonymous@.discussions.microsoft.com>
> wrote in message
> >news:0de201c39d64$5d354b10$a401280a@.phx.gbl...
> >> Thank you for the reply.
> >>
> >> My indexes are not unique, nor cluster.
> >>
> >> Tanya.
> >>
> >>
> >> >--Original Message--
> >> >Are your indexes unique indexes? Chances are thats why
> >> it's failing the
> >> >second time, if you're using the same data in you're
> BCP
> >> load.
> >> >
> >> >-Morgan
> >> >
> >> >"Tanya Bardakh" <tbardakh@.dysanalytics.com> wrote in
> >> message
> >> >news:0cf001c39d5d$a3068a20$a401280a@.phx.gbl...
> >> >> Hello,
> >> >>
> >> >>
> >> >> I have a table with 2 indexes.
> >> >> My C++ program deletes some data from this table and
> >> then
> >> >> does bcp data in. It does it twice in one run.
> >> >>
> >> >> After I run my program for the first time I get the
> >> >> correct result. When I run it again, the first bcp is
> >> >> successful, however there is no data from the second
> >> bcp.
> >> >> If I repeat it again, the result is correct; one more
> >> >> time - the data from the first bcp only.
> >> >>
> >> >> If I try to debug it, everything works fine.
> >> >>
> >> >> If I run it without break points in the program, I
> >> >> duplicate a problem.
> >> >>
> >> >> SQL Server log records an error:
> >> >> Failed Assertion = '(logMode != nonlogged) || (dbt-
> >> >> >dbt_dbid == TEMPDBID)'
> >> >> and creates a SQLdump file, which I don't know how to
> >> >> interprit.
> >> >>
> >> >> If I remove indexes from the table, everything works
> >> fine.
> >> >>
> >> >> Does anyone understand what's going on?
> >> >>
> >> >> I would appriciate any help.
> >> >> Thanks.
> >> >>
> >> >>
> >> >>
> >> >
> >> >
> >> >.
> >> >
> >
> >
> >.
> >

counting number of deleted rows

Hi

I have a stored procedure which deletes a number of rows from a number of different tables. How to i count/return the number of deleted rows in each table?

Here is my stored procedure if it helps:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROCEDURE [dbo].[usp_delete_entry]
@.new_venue_id int
AS
BEGIN


DECLARE @.new_customer_id int
SET @.new_customer_id = (SELECT customer_id FROM VENUE WHERE venue_id = @.new_venue_id)

DELETE FROM FEATURED WHERE venue_id = @.new_venue_id
DELETE FROM FACILITIES WHERE venue_id = @.new_venue_id
DELETE FROM SIC WHERE venue_id = @.new_venue_id
DELETE FROM SUBSCRIPTION WHERE venue_id = @.new_venue_id
DELETE FROM ADMIN WHERE venue_id = @.new_venue_id
DELETE FROM VENUE WHERE venue_id = @.new_venue_id
DELETE FROM CUSTOMER WHERE customer_id = @.new_customer_id

END

thanks@.@.rowcount global variable holds the number of rows affected for the latest query.
something like this.

ALTER PROCEDURE [dbo].[usp_delete_entry]
@.new_venue_id int
AS
BEGIN
DECLARE @.count1 int, @.count2 int ,@.count3 int, @.count4 int, @.count5 int,
@.count6 int, @.count7 int

DECLARE @.new_customer_id int
SET @.new_customer_id = (SELECT customer_id FROM VENUE WHERE venue_id = @.new_venue_id)

DELETE FROM FEATURED WHERE venue_id = @.new_venue_id
SET @.count1=@.@.rowcount
DELETE FROM FACILITIES WHERE venue_id = @.new_venue_id
SET @.count2=@.@.rowcount
DELETE FROM SIC WHERE venue_id = @.new_venue_id
SET @.count3=@.@.rowcount
DELETE FROM SUBSCRIPTION WHERE venue_id = @.new_venue_id
SET @.count4=@.@.rowcount
DELETE FROM ADMIN WHERE venue_id = @.new_venue_id
SET @.count5=@.@.rowcount
DELETE FROM VENUE WHERE venue_id = @.new_venue_id
SET @.count6=@.@.rowcount
DELETE FROM CUSTOMER WHERE customer_id = @.new_customer_id
SET @.count7=@.@.rowcount

select @.count1, @.count2,@.count3,@.count4,@.count5,@.count6,@.count7

END|||thats spot on, thanks|||Keep in mind (voice of experience speaking) that ANY SELECT operation will change the value of the @.@.ROWCOUNT system variable.
DECLARE @.recCount int
declare @.RowCount int
Select @.recCount = count(*) from osid (nolock)
select @.recCount
Select @.RowCount = @.@.ROWCOUNT
select @.RowCount

Not necessarily an issue - but something to be aware of, since I have had my buttocks spanked (Ohhhhhh...) on a few occasions by test stub code inserted or other such foolishness.|||Hi,

You can use @.@.ROWCOUNT global variable after each delete and assiging to other local variables to return etc.

I think it is helpful.

Cheers
Riaz

Hi

I have a stored procedure which deletes a number of rows from a number of different tables. How to i count/return the number of deleted rows in each table?

Here is my stored procedure if it helps:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROCEDURE [dbo].[usp_delete_entry]
@.new_venue_id int
AS
BEGIN


DECLARE @.new_customer_id int
SET @.new_customer_id = (SELECT customer_id FROM VENUE WHERE venue_id = @.new_venue_id)

DELETE FROM FEATURED WHERE venue_id = @.new_venue_id
DELETE FROM FACILITIES WHERE venue_id = @.new_venue_id
DELETE FROM SIC WHERE venue_id = @.new_venue_id
DELETE FROM SUBSCRIPTION WHERE venue_id = @.new_venue_id
DELETE FROM ADMIN WHERE venue_id = @.new_venue_id
DELETE FROM VENUE WHERE venue_id = @.new_venue_id
DELETE FROM CUSTOMER WHERE customer_id = @.new_customer_id

END

thanks