Skip to main content

Notifications

Microsoft Dynamics GP (Archived)

Sorting docs in Integration Manager Part 2

Posted on by 740

After upgrading one of our clients to GP 10, they are getting a similar error as discussed in the post listed below. The integration is reading the source data from an SQL table rather than a CSV file. We run other integrations that require sorting so the UseOptimizedFiltering is set to FALSE. When we set the UseOptimizedFiltering=FALSE, we receive the following error:

DOC 1 ERROR: ERROR [42000] [Microsoft][SQL Native Client][SQL Server]'UCase' is not a recognized function name.

DOC 1 ERROR: Unable to enforce relationship to Query '': TTGCBADO_UNABLETOENFORCERELATION

I checked for special characters (i.e. #, etc, in the data source columns) and found none. Any thoughts would be appreciated!

Thanks, Don

--------------------------------------------------------------------------------------------------------------------------------------------------------------------

Topic: Sorting docs in Integration Manager
Posted: 25 June 2008 at 3:41pm By lexi0088

I was having issues with the way IM was sorting our documents after we upgraded until I found the work around where you set UseOptimizedFiltering=FALSE in the Microsoft.Dynamics.GP.IntegrationManager.ini file.
 
HOWEVER, now when we run our integration for inventory items that are lot tracked (don't know if this is the reason) we get two errors on each document we are trying to integrate:

DOC 1 ERROR: Syntax error in date in query expression 'UCase(cust#) = UCase('211409')'.

DOC 1 ERROR: Unable to enforce relationship to Query '': TTGCBADO_UNABLETOENFORCERELATION

It is very important that our non-lot tracked item integrations are sorted correctly... which they do when the UseOptimizedFiltering=FALSE.

SOMEONE PLEASE HELP!!!

*This post is locked for comments

  • Pam Robertson Profile Picture
    Pam Robertson 199 on at
    RE: Sorting docs in Integration Manager Part 2

    I'm having the same problem.  We have to set UseOptimizedFiltering to true for some integrations and false for others.  Did you ever find a solution?  I verified that we don't use Ucase anywhere in queries or scripts.

  • HSalim Profile Picture
    HSalim 175 on at
    RE: Sorting docs in Integration Manager Part 2

    can i look at your integration?  email me hsalim at msn dot com

  • Richard Wheeler Profile Picture
    Richard Wheeler 75,730 on at
    RE: Sorting docs in Integration Manager Part 2

    I have no control of this. The function that causes the error is something that IM is putting in there. I have three sources each with their own SQL statement. In none of the SQL statements will you find ucase.

  • HSalim Profile Picture
    HSalim 175 on at
    RE: Sorting docs in Integration Manager Part 2

    Use upper in place of ucase

  • Richard Wheeler Profile Picture
    Richard Wheeler 75,730 on at
    RE: Sorting docs in Integration Manager Part 2

    Did you ever find a resolution to this? I just ran into this with an SOP integration. I could not understand why I kept getting invalid SQL function UCASE until I ran SQL Profiler and there it was. I changed UseOptimizedFiltering=TRUE and now I no longer get the error message but now it wont bring in all the distrubutions. This is IM for GP 2013 so it looks like this issue has never been corrected.

  • Loan Nguyen Profile Picture
    Loan Nguyen 305 on at
    Re: Re: Re: Sorting docs in Integration Manager Part 2

    Hi Don,

    I'm having an issue with sorting with IM also and was wondering if you ever got your issue resolved.

    Thanks!

    Loan Nguyen

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    Re: Re: Re: Sorting docs in Integration Manager Part 2

    Hi Don, have you applied the latest IM service pack? I just read the release notes and there is some stuff in there related to resolving filtering problems. Also something else you might look at is making sure the source definitions are exactly correct..Optimised Filtering is (I think) is less forgiving and the data has to be exact.

    Ian.

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    Re: Re: Re: Sorting docs in Integration Manager Part 2

    Yeah, just saw that from your original post... Interesting...

    Could you use two installs of IM? one set to false for all your other integrations, and one set to true for the inventory one?

    Ian.

  • Don L Profile Picture
    Don L 740 on at
    Re: Re: Sorting docs in Integration Manager Part 2

    Hi Ian,

     Thanks for your interest and suggestions, which I will try. An interesting part of this problem is that if I set the UseOptimizedFiltering=TRUE the integration runs ok. The down side is, now I do not have the sorting function I need for other integrations.

     Don

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    Re: Sorting docs in Integration Manager Part 2

    Hi Don, I haven't seen this issue (yet!!)..but if you're getting it on two sites it benefits us all to find a resolution...so this post is more of a 'lets investigate together' rather than a resolution.

    With any unusual IM errors, I always do a bit of house keeping as a first step...purge the log files and then compact the database. There should be a techknowledge on compacting the im.mdb. You could also do a repair (again check for a techknowledge.)

    The unable to force relation...could this have something to do with the related fields being different data types?

    Are you using anything from the IM additional scripts library?

     

    Ian Stewart

     

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!

Community AMA December 12th

Join us as we continue to demystify the Dynamics 365 Contact Center

Leaderboard

#1
André Arnaud de Calavon Profile Picture

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

#2
Martin Dráb Profile Picture

Martin Dráb 230,149 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans