Saturday, 27 September 2014

HMRC RTI database - deleting an unwanted submission

If like me you have got an outstanding submission in the HMRC Basic PAYE tools and it shouldn't be there and there is no way to delete it; then you may have to edit the SQLLite database that it uses. Here are the steps I undertook:

1. Backup the data from within the Basic PAYE tools application.

2. Download the SQLLite tool from here: http://sqlitebrowser.org (the source code can be downloaded here). This will allow you to view and edit the underlying database.

3. Open the sqllite.db in %appdata%\Roaming\HMRC\payetools-rti

4. Look in the bptrti_submissiondirtydata table. You'll see one row that looks like the outstanding submission. Find the "id" column and note down the number for the row.

5. Execute
delete from bptrti_submissiondirtydata where id = <id>
and enter the appropriate ID.

15 comments:

  1. Lifesave! Brilliant :) Many thanks

    ReplyDelete
  2. Thankyou, this has helped me out a lot. Much appreciated!

    ReplyDelete
  3. Thanks, been bugging me for over a year!

    ReplyDelete
  4. It was all going well until the last line.....

    Execute delete from bptrti_submissiondirtydata where id = and enter the appropriate ID.

    I don't understand what I need to do with the ID's. Deleting the entries just stops Basic PAYE tools from working. Any help gratefully received.

    ReplyDelete
  5. Sorry for the late reply, I didn't spot the comments in my blog.

    In step 3, using the sqlitebrowser tool, you browse the data records in the "bptrti_submissiondirtydata" table.

    You'll see one of the rows in that table that looks like the outstanding submission. Find the "id" column and note down the number for that ID for that row.

    Then use that noted ID number in the following query which you execute in the SQLLiteBrowser tool:

    delete from bptrti_submissiondirtydata where id = [id]

    Replace [id] with the number that you noted.

    Good luck!

    ReplyDelete
  6. Andrew

    It seems that Sourceforge has now ended as I was redirecteed here:

    https://github.com/sqlitebrowser/sqlitebrowser

    Are you able please to provide updated steps to follow following this change ?

    Thanks Brian

    ReplyDelete
  7. Sorry, I'm not good at spotting comments that have been applied. I've updated the main text. You can download the standalone executable from here (http://sqlitebrowser.org) and use it to edit the table.

    ReplyDelete
  8. I Cant do it :/ please can you help...on a Mac, failed submission is from a previous year

    ReplyDelete
  9. Which bit are you stuck on?
    Unfortunately I don't have any Mac devices, so I wouldn't be able to tell you any folders in which to look for files, but the principles should be the same.

    Can you locate the sqllite.db?
    And can you download a SQL Lite client tool such as sqllitebrowser?

    ReplyDelete
  10. Hi Andrew, thanks for this, but where do you execute the delete command from? Is that from within SQLlite or from a command prompt?
    Thanks.

    ReplyDelete
    Replies
    1. Hi Tim, Once I had identified the id number I just deleted the entry using the button at the top right of the browse data tab. I think Andrew wanted us to use the Execute SQL tab and type or paste the delete from bptrti_submissiondirtydata where id = [id] command with the relevant id number then use the run button (like a video player) to execute. Worked fine just deleting the entry from the browse data tab though :)

      Delete
  11. Superb, 3 years I've been ignoring these unsubmitted warnings. All gone now though. Many Thanks

    ReplyDelete
  12. Great this has been bugging me for 18 months!

    ReplyDelete
  13. Tim and Bertie, thank you so much; this had been bugging me for 5 minutes!

    ReplyDelete