0

So I have two tables, Records (Input ID = Primary Key) and TaskNotes (Input Id, TaskNote : No primary key).

There used to be a single stored procedure which would add to the record table, get the primary id that was generated, then add that ID to the TaskNotes table, along with the task notes text.

Recently, there was an issue where the sproc would run seemingly half way, with the record being added, but the task notes entry not being run.

I since split out into an AddRecord stored procedure and an AddTaskNotes stored procedure, which are being called from a C# application.

This works as similarly as before, however, at random the AddTaskNotes still wont be run.

I think the issue is a locking of the TaskNotes table.

Has anyone experienced this before and could let me know how it was resolved?

The current rate is about 1 failed tasknotes for every 400 record entries.

This is the AddRecord statement;

 INSERT  INTO Time.Records
                        ( TeamID ,
                          UserID ,
                          TimeIN ,
                          TimeOUT 
                        )
                VALUES  ( @TeamID ,  @UserID ,  @TimeIN , @TimeOUT );
                

                return SCOPE_IDENTITY();

This is the AddTaskNotes statement;

BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    INSERT INTO Time.TaskNotes ( InputID, TaskNotes )
    VALUES  ( @InputID,  @TaskNotes   );
END
Tiernan
  • 33
  • 5
  • What locking strategy are you using? – nicomp Nov 09 '20 at 13:35
  • @nicomp Don't really have one. It hasn't been an issue until recently (tables are about 4 years old) – Tiernan Nov 09 '20 at 13:37
  • Have you examined the logs kept by whatever calls this? – Caius Jard Nov 09 '20 at 13:38
  • 3
    "I since split out into an AddRecord stored procedure and an AddTaskNotes stored procedure, which are being called from a C# application." Why? If these two inserts should be an atomic operation, it's easier and safer to create and manage transactions in a single stored procedure than in multiple stored procedures... – Zohar Peled Nov 09 '20 at 13:42
  • 2
    It's improbable that it's not being run (barring an exception that's silently being swept under the rug), but how have you verified `@InputID` is correct? What does `AddRecord` do and how does it "get the primary id that was generated", seeing as there are plenty of incorrect ways of both generating and retrieving such values? – Jeroen Mostert Nov 09 '20 at 13:42
  • @CaiusJard Yes, the C# app log doesn't show anything, and the SQL Server logs are inaccessible for security reasons – Tiernan Nov 09 '20 at 13:42
  • @ZoharPeled I split them up to see if it would fix the issue, it hasn't. – Tiernan Nov 09 '20 at 13:43
  • Is InputID a surrogate key? – nicomp Nov 09 '20 at 13:44
  • @JeroenMostert '''return SCOPE_IDENTITY();''' Is used to get the ID of the newly added record and returns it to the application, which then passes that ID to the AddTaskNotes sproc. – Tiernan Nov 09 '20 at 13:44
  • @nicomp Yes, the column is Identity (1,1) – Tiernan Nov 09 '20 at 13:45
  • 1
    If your C# app log "doesn't show anything" then it sounds likie it might be a bit of a deficient log?! At the very least one would hope that some logging somewhere would confirm the sproc was being run.. at the moment it seems like you're saying you ask SQLS to do something and it simply doesn't do it, no reason or error given. What does the C# look like? – Caius Jard Nov 09 '20 at 13:45
  • @CaiusJard There is a SQL error log for errors, which populates if SQL returns an error, which isn't being populated with anything relevant to this issue – Tiernan Nov 09 '20 at 13:48
  • The basic form of would be something like `begin transaction begin try insert select scope identity insert commit end try begin catch rollback throw end catch` – Zohar Peled Nov 09 '20 at 13:48
  • 1
    There are two reasons a row is not in a table: either it was never inserted in the first place, or it was deleted after being inserted. You can cover the first case by double checking that parameters are correct and no errors are generated that are not being logged (add an inner exception handler if you must for extra confirmation). Covering the second case would be harder without a profiler trace. Under no circumstances save a serious bug in the engine will a T-SQL `INSERT` simply fail to insert anything without generating an error; you can almost certainly exclude that possibility. – Jeroen Mostert Nov 09 '20 at 13:51
  • 1
    if it's identity(1,1) why are you referencing it in the INSERT statement? – nicomp Nov 09 '20 at 13:52
  • 1
    Another possible cause of an `INSERT` not having a visible effect would be a badly written trigger on the destination table; exclude that too. – Jeroen Mostert Nov 09 '20 at 13:52
  • @JeroenMostert The row in TaskNotes is never inserted in the first place. If it was an issue with the ID from The Records, table it would sill be populated with a NULL value as there are no constraints on the TaskNotes table, but there aren't, just the missing row sequence (such as 1, 2, 3, 5, 6, 7... (missing row being 4)) – Tiernan Nov 09 '20 at 13:53
  • 1
    You cannot tell, simply from looking at a table, if the row was never inserted or if it was, but deleted afterwards. That's a temporal thing that only a trace of everything that happened could prove. The "ID might be `NULL`" hypothesis was just one option, move on from that if you can disprove it. – Jeroen Mostert Nov 09 '20 at 13:57
  • @nicomp The InputID is in the Records table, which is then returned on addition of a row within it, then the tasknotes is entered based off of that ID – Tiernan Nov 09 '20 at 13:57
  • Possiby what has happened is you've somehow inserted more than one "Record", and you've only got the last id back and used that. The first ID never got captured. Without more detailed code and more detailed analysis we're all guessing. But it is good advice to put both these statements in a transaction inside one store procedure – Nick.Mc Nov 09 '20 at 14:01
  • @JeroenMostert Ive added the AddRecord statement in the question – Tiernan Nov 09 '20 at 14:21

0 Answers0