SQL Server stored procedure to insert called from Python doesn't always store data, but still increments identity coutner

Question

I've hit a strange inconsistency problem with SQL Server inserts using a stored procedure. I'm calling a stored procedure from Python via pyodbc by running a loop to call it multiple times for inserting multiple rows in a table.

It seems to work normally most of the time, but after a while it will just stop working in the middle of the loop. At that point even if I try to call it just once via the code it doesn't insert anything. I don't get any error messages in the Python console and I actually get back the incremented identities for the table as though the data were actually inserted, but when I go look at the data, it isn't there.

If I call the stored procedure from within SQL Server Management Studio and pass in data, it inserts it and shows the incremented identity number as though the other records had been inserted even though they are not in the database.

It seems I reach a certain limit on the number of times I can call the stored procedure from Python and it just stops working.

I'm making sure to disconnect after I finish looping through the inserts and other stored procedures written in the same way and sent via the same database connection still work as usual.

I've tried restarting the computer with SQL Server and sometimes it will let me call the stored procedure from Python a few more times, but that eventually stops working as well.

I'm wondering if it is something to do with calling the stored procedure in a loop too quickly, but that doesn't explain why after restarting the computer, it doesn't allow any more inserts from the stored procedure.

I've done lots of searching online, but haven't found anything quite like this.

Here is the stored procedure:

USE [Test_Results]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[insertStepData]
@TestCaseDataId int,
@StepNumber nchar(10),
@StepDateTime nvarchar(50)
AS
SET NOCOUNT ON;
BEGIN TRANSACTION
DECLARE @newStepId int


INSERT INTO TestStepData (
    TestCaseDataId,
    StepNumber,
    StepDateTime
     )
    VALUES (
    @TestCaseDataId,
    @StepNumber,
    @StepDateTime

)
SET @newStepId = SCOPE_IDENTITY();


SELECT @newStepId
FROM TestStepData

COMMIT TRANSACTION

Here is the method I use to call a stored procedure and get back the id number ('conn' is an active database connection via pyodbc):

def CallSqlServerStoredProc(self, conn, procName, *args):
    sql = """DECLARE @ret int
             EXEC @ret = %s %s
             SELECT @ret""" % (procName, ','.join(['?'] * len(args)))
    return int(conn.execute(sql, args).fetchone()[0])

Here is where I'm passing in the stored procedure to insert:

    ....
    for testStep in testStepData:  

        testStepId = self.CallSqlServerStoredProc(conn, "insertStepData", testCaseId, testStep["testStepNumber"], testStep["testStepDateTime"])

        conn.commit()
        time.sleep(1)

    ....

Answer 1

SET @newStepId = SCOPE_IDENTITY();

SELECT @newStepId
FROM StepData

looks mighty suspicious to me:

1. SCOPE_IDENTITY() returns numeric(38,0) which is larger than int. A conversion error may occur after some time. Update: now that we know the IDENTITY column is int, this is not an issue (SCOPE_IDENTITY() returns the last value inserted into that column in the current scope).

2. SELECT into variable doesn't guarantee its value if more that one record is returned. Besides, I don't get the idea behind overwriting the identity value we already have. In addition to that, the number of values returned by the last statement is equal to the number of rows in that table which is increasing quickly - this is a likely cause of degradation. In brief, the last statement is not just useless, it's detrimental.

The 2nd statement also makes these statements misbehave:

EXEC @ret = %s %s
SELECT @ret

Since the function doesn't RETURN anything but SELECTs a single time, this chunk actually returns two data sets: 1) a single @newStepId value (from EXEC, yielded by the SELECT @newStepId <...>); 2) a single NULL (from SELECT @ret). fetchone() reads the 1st data set by default so you don't notice this but it doesn't work towards performance or correctness anyway.

Bottom line

Replace the 2nd statement with RETURN @newStepId.

Data not in the database problem

I believe it's caused by RETURN before COMMIT TRANSACTION. Make it the other way round.

In the original form, I believe it was caused by the long-working SELECT and/or possible side-effects from the SELECT not-to-a-variable being inside a transaction.