Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Finance | Project Operations, Human Resources, ... / Stored Procedures Miss...
Finance | Project Operations, Human Resources, ...
Answered

Stored Procedures Missing and Inconsistent Errors on Post

(1) ShareShare
ReportReport
Posted on by RJarrell 277

One of our customers has inconsistently been receiving errors when posting to PM. We have been tracking this for months now. It can happen a few times and then will go away, just to appear a few weeks later. Something else odd is that they will receive errors, but then, if they wait a certain amount of time, then the errors go away and they are able to post. They are using a terminal server on a hyper v station. The sql server is also on the hyper v so I don't think there would a connection issue. I would normally think that something is running in the background on the servers to cause issues, but we have not found anything consistent. 

Now, for the "fun" part. We have been combing through about a months worth of the dex.ini file that we have collected (we consistently copy and remove the file to keep space low - and also only implemented this after months of not being able to resolve the issue and not knowing when it will happen). There are a couple error messages that consistently pop up. I'm not sure if they are applicable to the issue or not. The newest discovery I had found was the "Could not find stored procedures" error message. I looked and compared to other clients and it seems like a large portion of stored procedures are missing. I definitely think this would be causing the issue except sometimes they can post and sometimes they cannot. In all honesty, this has really baffled everyone here. Any thoughts are appreciated.

I am thinking of trying to re-install the stored procedures. If anyone has any ideas on the best way to deal with that, please let me know. I think database maintenance was the normal way to correct that issue but we are on 18.2 and it was not in the upgrade. 

Stored Procedure Errors (not the only stored procedure errors but a big portion of these ones)

SQLSTATE:(37000) Native Err:(2812) stmt(139737160):*/
[Microsoft][SQL Server Native Client 11.0][SQL Server]Could not find stored procedure 'GRI.dbo.zDP_GL10000SS_1'

SQLSTATE:(37000) Native Err:(2812) stmt(139812064):*/
[Microsoft][SQL Server Native Client 11.0][SQL Server]Could not find stored procedure 'GRI.dbo.zDP_WF100002SS_3'.

SQLSTATE:(37000) Native Err:(2812) stmt(139737160):*/
[Microsoft][SQL Server Native Client 11.0][SQL Server]Could not find stored procedure 'GRI.dbo.zDP_GL00100SS_1'

SQLSTATE:(37000) Native Err:(2812) stmt(140404704):*/
[Microsoft][SQL Server Native Client 11.0][SQL Server]Could not find stored procedure 'GRI.dbo.zDP_GL40000F_1'

SQLSTATE:(37000) Native Err:(2812) stmt(139731544):*/
[Microsoft][SQL Server Native Client 11.0][SQL Server]Could not find stored procedure 'GRI.dbo.zDP_SY01300SS_1'

SQLSTATE:(37000) Native Err:(2812) stmt(139816744):*/
[Microsoft][SQL Server Native Client 11.0][SQL Server]Could not find stored procedure 'GRI.dbo.zDP_SY00700SS_1'.

SQLSTATE:(37000) Native Err:(2812) stmt(139732480):*/
[Microsoft][SQL Server Native Client 11.0][SQL Server]Could not find stored procedure 'GRI.dbo.zDP_PM10000F_1'

SQLSTATE:(37000) Native Err:(2812) stmt(139926312):*/
[Microsoft][SQL Server Native Client 11.0][SQL Server]Could not find stored procedure 'GRI.dbo.zDP_PM10300F_8'

SQLSTATE:(37000) Native Err:(2812) stmt(139924440):*/
[Microsoft][SQL Server Native Client 11.0][SQL Server]Could not find stored procedure 'GRI.dbo.zDP_PM80100SI'

SQLSTATE:(37000) Native Err:(2812) stmt(139930056):*/
[Microsoft][SQL Server Native Client 11.0][SQL Server]Could not find stored procedure 'GRI.dbo.zDP_PM40102SS_1

SQLSTATE:(37000) Native Err:(2812) stmt(139929120):*/
[Microsoft][SQL Server Native Client 11.0][SQL Server]Could not find stored procedure 'GRI.dbo.zDP_PM00200SS_1'

SQLSTATE:(37000) Native Err:(2812) stmt(139929120):*/
[Microsoft][SQL Server Native Client 11.0][SQL Server]Could not find stored procedure 'GRI.dbo.zDP_PM00200SS_1'

Duplicate Key Errors:

SQLSTATE:(23000) Native Err:(2627) stmt(139814872):*/
[Microsoft][SQL Server Native Client 11.0][SQL Server]Violation of PRIMARY KEY constraint 'PKPOA40001'. Cannot insert duplicate key in object 'dbo.POA40001'. The duplicate key value is (1).

SQLSTATE:(23000) Native Err:(2627) stmt(140490080):*/
[Microsoft][SQL Server Native Client 11.0][SQL Server]Violation of PRIMARY KEY constraint 'PKPM30300'. Cannot insert duplicate key in object 'dbo.PM30300'. The duplicate key value is (0007208 , 1, 0006921 , 6)

SQLSTATE:(23000) Native Err:(2627) stmt(431907296):*/
[Microsoft][SQL Server Native Client 11.0][SQL Server]Violation of PRIMARY KEY constraint 'PKPM00204'. Cannot insert duplicate key in object 'dbo.PM00204'. The duplicate key value is (VENDORID , 3, 2020, 9, 13).*/

Categories:
Microsoft Dynamics GP
  • Verified answer
    RJarrell Profile Picture
    RJarrell 277 on at
    RE: Stored Procedures Missing and Inconsistent Errors on Post

    Hey Beat,

    So I got MS involved because we couldn't get it figured out. They recommended the same procedure initially but same issue. They got a tech involved and we finally found it. From my understanding, GP maintenance only recreates SQL stored procedures. The stored procedures above are dexterity based. They had to be recreated through the Microsoft Dynamics dropdown -> Maintenance -> SQL. We were able to recreate through there and resolve the issue. We just got it fixed yesterday so wanted to give you an update.

    Thanks!

    Rebecka

  • Suggested answer
    Beat Bucher GP Geek GPUG All Star Profile Picture
    Beat Bucher GP Gee... 28,021 Super User 2024 Season 1 on at
    RE: Stored Procedures Missing and Inconsistent Errors on Post

    You need to identify the one that is locking the Company Database or the DYNAMICS DB with the 'sa' user that you used to login at the DMU tool.. There is only 1 Session that needs to be killed to get the DMU work thru the process.

    Using sp_who2 in SQL should help..

    PS: did you follow the link from my blog post : https://community.dynamics.com/gp/f/microsoft-dynamics-gp-forum/305027/database-maintenance-utility

  • RJarrell Profile Picture
    RJarrell 277 on at
    RE: Stored Procedures Missing and Inconsistent Errors on Post

    Hey Beat,

    Thank you for the input! I tried going through the steps and got stuck at the SPIDS section. There are a lot of sessions open on the server. With the company databases and the system database. I tested deleting a few of the dynamics ones and then running the process but all of the connections popped back up. I then noted the connections, tried running the maintenance process but no new SPIDS for dynamics appears.

    Hopefully that makes sense what happened. I am not sure what to do. Should I try removing all of the SPIDS that are connected to the company and system databases?

  • Suggested answer
    Beat Bucher GP Geek GPUG All Star Profile Picture
    Beat Bucher GP Gee... 28,021 Super User 2024 Season 1 on at
    RE: Stored Procedures Missing and Inconsistent Errors on Post

    Hi RJarrel,

    My first thought on this one would be to check if the SQL objects are indeed all there.. The quickest way to recreate them is actually to use the GP Maintenance Utility, but since version 2015 or 2016 it has a major bug that prevents it to run smoothly.. and complete any actual action.

    Please check this blog post about how to work around the issue:

    dyngpgeek.wordpress.com/.../

    Also, I'd run the GRANT.sql script to make sure every SQL object is properly assigned to the DYNGRP role, juts to make sure nothing is missing.

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

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

Top 10 leaders for November!

Congratulations to our November super stars!

Tips for Writing Effective Suggested Answers

Best practices for providing successful forum answers ✍️

Leaderboard

#1
André Arnaud de Calavon Profile Picture

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

#2
Martin Dráb Profile Picture

Martin Dráb 230,235 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