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 ManagerPosted: 25 June 2008 at 3:41pm By lexi0088
DOC 1 ERROR: Syntax error in date in query expression 'UCase(cust#) = UCase('211409')'.
It is very important that our non-lot tracked item integrations are sorted correctly... which they do when the UseOptimizedFiltering=FALSE.
*This post is locked for comments
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
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
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 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.
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
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.
Use upper in place of ucase
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.
can i look at your integration? email me hsalim at msn dot com
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.
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.
As AI tools become more common, we’re introducing a Responsible AI Use…
We are honored to recognize Pallavi Phade as our Community Spotlight honoree for…
These are the community rock stars!
Stay up to date on forum activity by subscribing.
Community Member 2
