Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics GP (Archived) / DBMS error 7405 when p...
Microsoft Dynamics GP (Archived)

DBMS error 7405 when performing Security tasks in GP

(0) ShareShare
ReportReport
Posted on by jerryparker@protect... 14

I just recently upgraded our GP system from GP 2010 to GP 2018R2.

I am now experiencing an issue where I cannot assign users to company databases (new or existing accounts), I cannot remove access to a company, I also cannot create new companies in GP without receiving an error stating the following:

Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options to be set for the connection. This ensures consistent query semantics. Enable these options and then reissue your query.

Detailed information shows: The stored procedure smUserCmpnyAccssChckAccssCHG returned the following results:DBMS 7405, Microsoft Dynamics GP:0

I have tried to enable those options on the ODBC connection, no change in results.

I've run this with DEXSQLLog turned on - and that results in the same exact error when it gets to the point of calling that stored procedure.

If I copy that step from the log (just before the error) and run it in SQL, it returns 0, 0.

I'm at a lost as to what is causing this, or where else to look to get this resolved.

Any thoughts or ideas would be greatly appreciated.

Thanks

Jerry

*This post is locked for comments

  • Suggested answer
    jerryparker@protectplusair.com Profile Picture
    jerryparker@protect... 14 on at
    RE: DBMS error 7405 when performing Security tasks in GP

    Sorry for the delay in getting back to everyone.

    So, apparently something is just wrong with the current SQL server. I can't pin it down, but when I had a new server spun up to test on, after the GP databases were restored there, everything worked as it should.

    So, we are going to migrate our GP system from the one server over to a new server and just "unplug" the faulty server.

    Thank you all for your suggestions and help on this issue. Was definitely confusing, and unfortunately will remain unanswered as to the true cause of the issue.

  • jerryparker@protectplusair.com Profile Picture
    jerryparker@protect... 14 on at
    RE: DBMS error 7405 when performing Security tasks in GP

    Yep, that was exactly everything that I found as well (linked servers, etc).

    I've checked the ODBC (trying it with those options off (be default) as well as checked) - nothing changes. I've also looked through the settings on the databases - comparing them to the GP 2010 environments databases line for line (and that is when I came across those lines which are all false).

    I have tried the Database Maintenance tool as well (thinking the SPs had been corrupted), but that did nothing to resolve this either.

    One thing I did do was to go and modify the smUserCmpnyAccssChckAccssCHG stored proc (along with the other 3 that are similar to this one, 2 being for unChecking), adding in SET ANSI_NULLS ON SET ANSI_WARNINGS ON into the procedure just after the 'as'. Since these are called from the company you are logged into, I only did this in one company. This did allow me to move forward with setting a users access when logged into that company, but I am not 100% sure that is actually fully working. Plus I KNOW that is absolutely not the way to solve issues like this.

    The only reason creating companies fails is because at the end of it, it also calls these stored procs to grant initial permissions to the company (or something similar to that - I haven't run a dexlog of an entire company creation process to see the exact proc, but I get the same error at the end of the company creation process).

    I've been traveling with work for the last couple of days, so haven't had a chance to run a SQL profiler yet to see if that gives me any clues.

    Thanks all!!

    Jerry

  • Derek Albaugh Profile Picture
    Derek Albaugh on at
    RE: DBMS error 7405 when performing Security tasks in GP

    I looked for what we've seen for DBMS: 7405 errors with Dynamics GP and this ‘Heterogeneous queries’ error you mentioned here, but the only thing I've found that we’ve seen this was when the customer was attempting to use Linked Servers in some way, such as with SmartList Builder reports, running a stored procedure that runs a query to a linked server, using a SQL View that pulls from a linked server with SLB, etc.  Other than linked servers, we don’t see this error message typically.

    You are correct that this is a SQL error and not a Dynamics GP error, thus the DBMS value.

    The only ANSI settings I can think of are in the ODBC DSN being used to connect Dynamics GP to SQL Server, where we want to make sure all ANSI options are NOT marked, as per KB 870416.

    In the database properties for the GP system and company database(s), in SQL, under the ‘Options’ page, there are about 4 ANSI settings under ‘Miscellaneous’, all of which should be set to FALSE by default, which is required for dexterity procedures to work.

    That's about all I was able to find off hand...….

    You could try running the Database Maintenance tool for Dynamics GP 2018 to drop and re-create the stored procedures, which would include this smUserCmpnyAccssChckAccssCHG procedure, then see if that changes anything.

    Very odd error, especially when you state you cannot create a new company database, give users access or remove access...….

    Let us know if you find anything more...……

    Thanks

  • jerryparker@protectplusair.com Profile Picture
    jerryparker@protect... 14 on at
    RE: DBMS error 7405 when performing Security tasks in GP

    Ok. I will give that a try and get back to you.

    I agree with you that this has got to be a SQL issue, it is just nothing I have ever run across in all my GP years, so has me VERY stumped.

  • Richard Wheeler Profile Picture
    Richard Wheeler 75,788 Moderator on at
    RE: DBMS error 7405 when performing Security tasks in GP

    Have you tried running SQL Profiler while trying any of these tasks? I do not think this is a GP issue but something in SQL. Is DYNSA the owner of your databases? Try to capture a statement and then run it in SSMS. I bet it will fail there as well.

  • jerryparker@protectplusair.com Profile Picture
    jerryparker@protect... 14 on at
    RE: DBMS error 7405 when performing Security tasks in GP

    Mine is set to the same as yours, the only difference is that I'm on SQL Server 2017.

    Dynamics.png

  • Richard Wheeler Profile Picture
    Richard Wheeler 75,788 Moderator on at
    RE: DBMS error 7405 when performing Security tasks in GP

    If you check the properties of the DYNAMICS database what is the setting for ANSI NULLS?

    pastedimage1568800852733v1.png

  • jerryparker@protectplusair.com Profile Picture
    jerryparker@protect... 14 on at
    RE: DBMS error 7405 when performing Security tasks in GP

    Yeah, I double checked that early on. The old and new server and databases are all at the same collation - SQL_Latin1_General_CP1_CI_AS .

  • Richard Wheeler Profile Picture
    Richard Wheeler 75,788 Moderator on at
    RE: DBMS error 7405 when performing Security tasks in GP

    The collation sequence didn't get changed, did it?

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

Announcing Our 2025 Season 1 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Vahid Ghafarpour – Community Spotlight

We are excited to recognize Vahid Ghafarpour as our February 2025 Community…

Tip: Become a User Group leader!

Join the ranks of valued community UG leaders

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 292,494 Super User 2025 Season 1

#2
Martin Dráb Profile Picture

Martin Dráb 231,305 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans