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) / Trigger on RM00101
Microsoft Dynamics GP (Archived)

Trigger on RM00101

(0) ShareShare
ReportReport
Posted on by Community Member Microsoft Employee

We feed a sister system with customer data that's entered on GP 9.0. I have a central database (MSTR) that houses all the customers then there is a trigger on the RM00101, 102 and 103 that blows the data out to the other 43 companies that we have on GP. That works fine-it's all on the same server.

The trigger that doesn't work sends it to a linked server that's also SqlServer 2005. When I enter the data (in GP/Cards/Sales/Customer) and hit save I get:

[Microsoft][ODBC SQL Server Driver][SQL Server]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.
EXCEPTION_CLASS_DB
DB_ERR_SQL_OBJ_NOT_FOUND

I've modified my ODBC setting that I log into GP with and that doesn't help. Are ANSI_NULLS/ANSI_WARNINGS set by the application?!?! If it is, I'm screwed. From what I've read online, you can't change these settings from a trigger, it HAS to be the application and/or Enterprise manager that does this. Surely someone has had to do a trigger off new customer additions.

*This post is locked for comments

  • John Kang Profile Picture
    John Kang 40 on at
    Re: Re: Re: Re: Re: Re: Trigger on RM00101

    Thank you Pete & David!  I've got it to work now.  I'll just add one more info in case someone runs into same situation.

    Our stored proc had to insert a parent record in a linked server database that returns the identity key which is in turn used to insert a child record also in the same linked server database.  But of course, inserting a record to a remote linked server will not return the identity.  Thus, we had to move the stored proc to the linked server and call it from our local trigger.

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    Re: Re: Re: Re: Re: Trigger on RM00101

    Well, this has been a REAL eye opener for me. Once I got the "this-is-how-you-can-do-it" mechanics down, I find that getting the process to run on the servers is another challenge.

    After I got the trigger and the stored proc written when it ran I got:


    Server: Msg 7391, Level 16, State 1, Line 1
    The operation could not be performed because OLE DB provider "%ls" for linked server "%ls" was unable to begin a distributed transaction.
     
    Through an exhaustive process I found that the Great Plains server wasn't setup for distributed transactions. There's postings on it, but basically you run dcomcnfg.exe,Component Services/Computer and right click My Computer/Properties. Go to MSDTC/Security Config and switch everything ON.
     
    Apparently, even though I wasn't doing a transaction, it treats it as such "under-the-covers" regarding linked servers.
  • winthropdc Profile Picture
    winthropdc on at
    Re: Re: Re: Re: Trigger on RM00101

    Hi John

    This is a confirmed solution that I have had to use for a customisation project.  It is documented in the following Knowledge Base (KB) article:

    Error: Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options to be set for the connection (KB 875615)

    https://mbs.microsoft.com/knowledgebase/KBDisplay.aspx?scid=kb;en-us;875615

    David

    David Musgrave [MSFT]
    Escalation Engineer - Microsoft Dynamics GP
    Microsoft Dynamics Support - Asia Pacific

    Microsoft Dynamics (formerly Microsoft Business Solutions)
    http://www.microsoft.com/Dynamics

    mailto:David.Musgrave@online.microsoft.com
    http://blogs.msdn.com/DevelopingForDynamicsGP

    Any views contained within are my personal views and not necessarily Microsoft policy.
    This posting is provided "AS IS" with no warranties, and confers no rights. 

     

  • John Kang Profile Picture
    John Kang 40 on at
    Re: Re: Re: Trigger on RM00101

    Our company is having this exact same issue after we added a new server recently and this solution doesn't seem to work.  Is this a fully tested and documented solution or "should work" thing?

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    Re: Trigger on RM00101

    Mr. Musgrave:

    Thanks soooo much for your reply. I have to admit I haven't done that much SqlServer trigger coding. Do you have an example of what it should look like?

    Is it as simple as:

    Create trigger --- as Set ANSI_NULLS On GoExec  InsertNewCust(---)

     

  • winthropdc Profile Picture
    winthropdc on at
    Re: Re: Re: Re: Re: Re: Re: Trigger on RM00101

    Your trigger code will need to change the ANSI settings and then call a second stored procedure and then change them back.  Changing the ANSI settings does not take effect in the current script and needs you to call a second script which now executes in the new context.

    I had to do the same thing when working across two servers.  There is a KB article about this somewhere.

    David

    David Musgrave [MSFT]
    Escalation Engineer - Microsoft Dynamics GP
    Microsoft Dynamics Support - Asia Pacific

    Microsoft Dynamics (formerly Microsoft Business Solutions)
    http://www.microsoft.com/Dynamics

    mailto:David.Musgrave@online.microsoft.com
    http://blogs.msdn.com/DevelopingForDynamicsGP

    Any views contained within are my personal views and not necessarily Microsoft policy.
    This posting is provided "AS IS" with no warranties, and confers no rights. 

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    Re: Re: Re: Re: Re: Trigger on RM00101

    Then what would you suggest?  Maybe a stored procedure that runs at a certain time? That's how I currently have it but it's an application, not a trigger/sproc.

     I just thought since they were both Sql Server that I could check the inserted table and act accordingly.

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,516 Super User 2025 Season 1

#2
Martin Dráb Profile Picture

Martin Dráb 231,317 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans