MS SQL Server - Alternating INSERTs fail

Author

Alternating INSERTs fail

greg.holmes

2006-12-12, 7:12 pm

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 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

Dan Guzman

2006-12-13, 12:13 am

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

greg.holmes

2006-12-13, 7:12 pm

"Dan Guzman" wrote:

> You might try adding the sp:statement completed event to the trace. Do you
> 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 go
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.
[color=darkred]
> "greg.holmes" < gregholmes@discussio
ns.microsoft.com> wrote in message
> news:26B9E0DA-EFF4-4371-A14A- E3EF007FDD1F@microso
ft.com...

greg.holmes

2006-12-13, 7:12 pm

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 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 go
> 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

2006-12-13, 7:12 pm

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.

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

Dan Guzman

2006-12-14, 12:12 am

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...[color=darkred]
> 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.
>
>

Sponsored Links