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 |
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
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.
can i look at your integration? email me hsalim at msn dot com
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.
Use upper in place of ucase
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.
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
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.
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.
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
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
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
André Arnaud de Cal... 291,240 Super User 2024 Season 2
Martin Dráb 230,149 Most Valuable Professional
nmaenpaa 101,156