Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Finance | Project Operations, Human Resources, ... / Bank transfers get stu...
Finance | Project Operations, Human Resources, ...
Suggested answer

Bank transfers get stuck in posting after posting crashes with SQL error.

(0) ShareShare
ReportReport
Posted on by Richard Wheeler 75,730

After a hard drive failure all bank transactions get stuck in posting. In the SY00500 batch status goes to 7 and the userid field is blank. Records show up in SY00800 and SY00801 and get stuck. If I clear these tables and post they go through. What can I do to get this working again? This is happening in only one company and only for bank transactions. Payables and receivables post fine.

This is the error message

Internal Query Processor Error: The query processor encountered an unexpected error(HRESUILT=0x80040e19)

Can I use SQL Maintenance to recreate stored procedures? If so, which ones?

Categories:
Microsoft Dynamics GP
  • Richard Wheeler Profile Picture
    Richard Wheeler 75,730 on at
    RE: Bank transfers get stuck in posting after posting crashes with SQL error.

    Cheryl, this had no effect on this issue. I am going to tell them to open a case with MS.

  • Richard Wheeler Profile Picture
    Richard Wheeler 75,730 on at
    RE: Bank transfers get stuck in posting after posting crashes with SQL error.

    Derek, when I first came across this, DBCC Checkdb indicated consistency check problems with Gl10100, GL20000, CM20200 and CM20600. All  these tables were completely rebuild and repopulated. The problem with the zero value for OPENYEAR was with the GL10000 TABLE.  I can certainly try changing all those values to 2020 to see if they will post.

  • Cheryl Waswick Profile Picture
    Cheryl Waswick on at
    RE: Bank transfers get stuck in posting after posting crashes with SQL error.

    Hi Richard, 

    Sounds like you found some issues with having closed year data in the GL20000 table, and you cannot post GL entries either.   Your current year transactions have a year of 0 instead of 2020.

    I would make a backup and try this.  Update the year field in the GL20000 table for '2020' where the year stamp is blank.  Then I would dump the data into a backup table, and drop and recreate the GL20000 table in File|Maintenance|SQL (all 6 options on the right) and then push the data back in.  Key and post a new journal entry to make sure it posts fine and doesn't error.  If it works, then reopen the GL years back to 2016, and then reclose the GL years again.  Verify that only 2020 is in the GL20000 table now. 

    If you need more help, it would be best to open a support case so we can help dig into data if needed.  I won't be able to do this in this post. 

    Thanks

    Cheryl

    Microsoft Support

  • Richard Wheeler Profile Picture
    Richard Wheeler 75,730 on at
    RE: Bank transfers get stuck in posting after posting crashes with SQL error.

    Dropping and creating the stored procedures for the entire finance series did not help.

  • Richard Wheeler Profile Picture
    Richard Wheeler 75,730 on at
    RE: Bank transfers get stuck in posting after posting crashes with SQL error.

    More clues. I entered a ledger entry and it did not post either.  My attention now turns to the GL20000 table. This was one of the tables I had to rebuild so they could close 2019. Right now there are 4,051 records in this table. I immediately see a problem with this table. There are two records with an openyear of 2016 and 54 records with an openyear of 2018. Also, the value for GL10000.OPENYEAR for all transactions is 0. Shouldn't that be 2020?

    I am now out of ideas. Unless you have any ideas I will have them open a case.

  • Richard Wheeler Profile Picture
    Richard Wheeler 75,730 on at
    RE: Bank transfers get stuck in posting after posting crashes with SQL error.

    Cheryl, I have another clue. I went to Tools->Setup->Posting->Posting and set Bank Transfers to only post to GL and not through. When I enter a bank transfer and post, I do not get the error. However, when I go to post the resulting transaction there is the error. I just tried posting other batches(PM) and the error is the same. 8630 is the DBMS error number. So the error is not bank transactions but posting to the GL.

  • Richard Wheeler Profile Picture
    Richard Wheeler 75,730 on at
    RE: Bank transfers get stuck in posting after posting crashes with SQL error.

    DEXSQL.zip Here is the DEXSQL.LOG. This shows that the issue occurs when the resulting bank transfer transactions tries to post to the GL. Other transactions post through the GL just fine.Any suggestions?

  • Richard Wheeler Profile Picture
    Richard Wheeler 75,730 on at
    RE: Bank transfers get stuck in posting after posting crashes with SQL error.

    I will try dropping and recreating the stored procedures. I can sit there and watch this happen. All activity is cleared and a bank transfer is done. The error occurs and there are the records stuck in the SY00800 and SY00801 TABLES. I will use PSTL to accomplish this.

  • Suggested answer
    Cheryl Waswick Profile Picture
    Cheryl Waswick on at
    RE: Bank transfers get stuck in posting after posting crashes with SQL error.

    Hi Richard, 

    Here are some troubleshooting steps you can try and test in-between:  (make a current backup first please.) (steps in no particular order).

    1. Stop and restart the SQL server.  (refresh the temp tables)

    2. Check activity tables.  When all users are logged out of GP, all of these tables should be empty: (maybe something locked in the tempdb dex tables)

    SELECT * FROM DYNAMICS..ACTIVITY

    SELECT * FROM DYNAMICS..SY00800

    SELECT * FROM DYNAMICS..SY00801

    SELECT * FROM TEMPDB..DEX_LOCK

    SELECT * FROM TEMPDB..DEX_SESSION

     

    DELETE DYNAMICS..ACTIVITY

    DELETE DYNAMICS..SY00800

    DELETE DYNAMICS..SY00801

    DELETE TEMPDB..DEX_LOCK

    DELETE TEMPDB..DEX_SESSION

    **Make a current backup before doing the next steps or do in a test company first (if the issue can be recreated):

    3.  In GP, go to Microsoft Dynamics GP/Maintenance/ Check links.  Under Financial, insert over 'CM Setup' and 'CM Transaction' and click OK.   Test again. 

    4. In GP, go to Microsoft Dynamics GP/Maintenance / SQL.  Select the company database.  Find 'CM Transaction'.  click the bottom two options to drop/recreate the auto procedures.   Repeat for the 'CM Transfer' table.   If this doesn't work, you can do all 6 options on the right, but you must have backups of these tables so you can insert the data back in when you are done as the data is lost in these tables, or use the PSTL Tookit to recreate these tables, as this tool has the option to 'recreate data for selected table' so you don't lose the data.  Using the GP/SQL option, you will have to insert the data back in.  You need to have all users out of GP when doing either option.

    If any questions or this doesn't work, it would be best to open a support case with Microsoft to get additional assistance.


    Thanks,

    Cheryl W. 

    Microsoft Support - Applications  

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Congratulations 2024 Spotlight Honorees!

Kudos to all of our 2024 community stars! 🎉

Meet the Top 10 leaders for December!

Congratulations to our December super stars! 🥳

Get Started Blogging in the Community

Hosted or syndicated blogging is available! ✍️

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,602 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,340 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

End-of-life Announcement for Microsoft Dynamics GP

Product updates

Dynamics 365 release plans