Alternating INSERTs fail

In SQL Server 7, I have a stored procedure that does a simple INSERT.  I cal
l
it from ASP.NET.

It only succeeds every other time (i.e. alternating).  No error is returned
when it doesn't work.  A return value of 1 is always returned for both cases
(indicating 1 row affected) but the new data row simply isn't there exactly
every other time, alternating.

I even ran a trace, and I can see both inserts happen , and nothing else
comes along to delete any of the rows.  But the problem persists.

The only difference that I see in the trace is the number of reads, and a
longer duration in the one that succeeds (the second one):

Event Class	Object ID	Database ID	Text	Application Name	NT User Name	SQL
User  Name	CPU	Reads	Write
 s	Duration	Connectio
n ID	SPID	Start Time
 +RPC:Completed		9	In
sertAccountLogin 377864, N'Dec12-Test7', N'Dec12-Test'
.Net SqlClient Data  Provider		sa	0	4	0	0
	22591	20	14:57:14.380


Event Class	Object ID	Database ID	Text	Application Name	NT User Name	SQL
User  Name	CPU	Reads	Write
 s	Duration	Connectio
n ID	SPID	Start Time
 +RPC:Completed		9	In
sertAccountLogin 377865, N'Dec12-Test7', N'Dec12-Test'
.Net SqlClient Data  Provider		sa	0	3	0	1
7	22591	20	14:57:30.223

Can anyone suggest some more troubleshooting steps I can take here?


I've tried it with the stored procedure as:

 ====================
============
CREATE PROCEDURE InsertAccountLogin @InternetID as int, @UserName as
char(40), @Password as char(15) AS

Begin Transaction

Insert into AccountLogin (InternetID, UserName, Password) Values
(@InternetID, @UserName, @Password)

COMMIT Transaction
 ====================
============

and also


 ====================
============
CREATE PROCEDURE InsertAccountLogin @InternetID as int, @UserName as
char(40), @Password as char(15) AS

Insert into AccountLogin (InternetID, UserName, Password) Values
(@InternetID, @UserName, @Password)
 ====================
============


Thanks,

Greg Holmes

You might try adding the sp:statement completed event to the trace.  Do you
have any triggers on the table?

--
Hope this helps.

Dan Guzman
SQL Server MVP

"greg.holmes" < gregholmes@discussio
ns.microsoft.com> wrote in message
news:26B9E0DA-EFF4-4371-A14A- E3EF007FDD1F@microso
ft.com...
> In SQL Server 7, I have a stored procedure that does a simple INSERT.  I
> call
> it from ASP.NET.
>
> It only succeeds every other time (i.e. alternating).  No error is
> returned
> when it doesn't work.  A return value of 1 is always returned for both
> cases
> (indicating 1 row affected) but the new data row simply isn't there
> exactly
> every other time, alternating.
>
> I even ran a trace, and I can see both inserts happen , and nothing else
> comes along to delete any of the rows.  But the problem persists.
>
> The only difference that I see in the trace is the number of reads, and a
> longer duration in the one that succeeds (the second one):
>
> Event Class Object ID Database ID Text Application Name NT User Name SQL
> User Name CPU Reads Writes Duration Connection ID SPID Start Time
> +RPC:Completed 9 InsertAccountLogin 377864, N'Dec12-Test7', N'Dec12-Test'
> .Net SqlClient Data Provider sa 0 4 0 0 22591 20 14:57:14.380
>
>
> Event Class Object ID Database ID Text Application Name NT User Name SQL
> User Name CPU Reads Writes Duration Connection ID SPID Start Time
> +RPC:Completed 9 InsertAccountLogin 377865, N'Dec12-Test7', N'Dec12-Test'
> .Net SqlClient Data Provider sa 0 3 0 17 22591 20 14:57:30.223
>
> Can anyone suggest some more troubleshooting steps I can take here?
>
>
> I've tried it with the stored procedure as:
>
>  ====================
============
> CREATE PROCEDURE InsertAccountLogin @InternetID as int, @UserName as
> char(40), @Password as char(15) AS
>
> Begin Transaction
>
> Insert into AccountLogin (InternetID, UserName, Password) Values
> (@InternetID, @UserName, @Password)
>
> COMMIT Transaction
>  ====================
============
>
> and also
>
>
>  ====================
============
> CREATE PROCEDURE InsertAccountLogin @InternetID as int, @UserName as
> char(40), @Password as char(15) AS
>
> Insert into AccountLogin (InternetID, UserName, Password) Values
> (@InternetID, @UserName, @Password)
>  ====================
============
>
>
> Thanks,
>
> Greg Holmes

"Dan Guzman" wrote:

> You might try adding the sp:statement completed event to the trace.  Do yo
u
> have any triggers on the table?

Thanks Dan.  I added statement completed to the trace, but all that did was
add a
"sp:statement completed" line before each RPC line for the INSERTs.  Those
"sp:statement completed" lines look identical.

This is so weird.  The first field in the insert should be incrementing by
1s (by the ASP.NET application), but you can look at the rows and watch it g
o
up by 2s.  I also added an auto incrementing field to the table and you can
watch the phenomenon there too ("1", "3", "5", etc.).  It's actually
incrementing the auto-incrementing field, but not leaving a row in the
database, every other time.

> "greg.holmes" < gregholmes@discussio
ns.microsoft.com> wrote in message
> news:26B9E0DA-EFF4-4371-A14A- E3EF007FDD1F@microso
ft.com... 

And I forgot to add - there are no triggers on the table.

"greg.holmes" wrote:

> "Dan Guzman" wrote:
> 
>
> Thanks Dan.  I added statement completed to the trace, but all that did wa
s
> add a
> "sp:statement completed" line before each RPC line for the INSERTs.  Those
> "sp:statement completed" lines look identical.
>
> This is so weird.  The first field in the insert should be incrementing by
> 1s (by the ASP.NET application), but you can look at the rows and watch it
 go
> up by 2s.  I also added an auto incrementing field to the table and you ca
n
> watch the phenomenon there too ("1", "3", "5", etc.).  It's actually
> incrementing the auto-incrementing field, but not leaving a row in the
> database, every other time.
> 
>

OK, here's the only thing that worked to remedy this - as you might expect,
my confidence in the robustness of this solution is low!

1. Switch from using Stored Procedure to local text SQL query.

2. Add an auto-incrementing identity field to the database.

Has to do both.  Neither worked by itself.


> "greg.holmes" < gregholmes@discussio
ns.microsoft.com> wrote in message
> news:26B9E0DA-EFF4-4371-A14A- E3EF007FDD1F@microso
ft.com... 

Another thing you might try is adding Exception, OLEDB Errors and Attention
events to the trace.  Out of curiosity, did you change the existing
InternetID column to an IDENTITY or did you add a new column?

--
Hope this helps.

Dan Guzman
SQL Server MVP

"greg.holmes" < gregholmes@discussio
ns.microsoft.com> wrote in message
news:1D4547FA-94AE-466C-ABE5- 741FA72D2970@microso
ft.com...
> OK, here's the only thing that worked to remedy this - as you might
> expect,
> my confidence in the robustness of this solution is low!
>
> 1. Switch from using Stored Procedure to local text SQL query.
>
> 2. Add an auto-incrementing identity field to the database.
>
> Has to do both.  Neither worked by itself.
>
>