Alternating INSERTs fail

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 ClassObject IDDatabase IDTextApplication NameNT User NameSQL
User NameCPUReadsWritesDurationConnection IDSPIDStart Time
+RPC:Completed9InsertAccountLogin 377864, N'Dec12-Test7', N'Dec12-Test'
.Net SqlClient Data Providersa0400225912014:57:14.380
Event ClassObject IDDatabase IDTextApplication NameNT User NameSQL
User NameCPUReadsWritesDurationConnection IDSPIDStart Time
+RPC:Completed9InsertAccountLogin 377865, N'Dec12-Test7', N'Dec12-Test'
.Net SqlClient Data Providersa03017225912014: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@.discussions.microsoft.com> wrote in message
news:26B9E0DA-EFF4-4371-A14A-E3EF007FDD1F@.microsoft.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 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.
[vbcol=seagreen]
> "greg.holmes" <gregholmes@.discussions.microsoft.com> wrote in message
> news:26B9E0DA-EFF4-4371-A14A-E3EF007FDD1F@.microsoft.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 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.
>
|||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.
[vbcol=seagreen]
> "greg.holmes" <gregholmes@.discussions.microsoft.com> wrote in message
> news:26B9E0DA-EFF4-4371-A14A-E3EF007FDD1F@.microsoft.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@.discussions.microsoft.com> wrote in message
news:1D4547FA-94AE-466C-ABE5-741FA72D2970@.microsoft.com...[vbcol=seagreen]
> 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.
>
|||I added a new column. I confess I didn't try making InternetID an identity.
That might have worked.
"Dan Guzman" wrote:

> 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@.discussions.microsoft.com> wrote in message
> news:1D4547FA-94AE-466C-ABE5-741FA72D2970@.microsoft.com...
>
|||>I added a new column. I confess I didn't try making InternetID an
>identity.
> That might have worked.
I was curious about the data in the InternetID and the new IDENTITY column.
Does the InternetID still increment by 2? What about the IDENTITY col?
Hope this helps.
Dan Guzman
SQL Server MVP
"greg.holmes" <gregholmes@.discussions.microsoft.com> wrote in message
news:B59141B9-485E-4BDC-969A-3CC1B23950D0@.microsoft.com...[vbcol=seagreen]
>I added a new column. I confess I didn't try making InternetID an
>identity.
> That might have worked.
> "Dan Guzman" wrote:
|||No, now that I'm using a text query in ASP.NET instead of a stored procedure,
and now that I have the identity column in the table, the INSERTs work as
expected. The identity column increments by 1, as does InternetID (if
sequential customers both create an account, which is what this table is for).
So by changing those two things, I must have somehow worked around an issue
that I don't understand, or perhaps an obscure bug.
"Dan Guzman" wrote:

> I was curious about the data in the InternetID and the new IDENTITY column.
> Does the InternetID still increment by 2? What about the IDENTITY col?
>