John, As you've probably guessed the N in the N'<whatever>' is normal for a unicode server - it's telling the DB to consider the string a unicode string.
That really is a very strange problem. You could still try the step 2 I described to try to directly issue just "SELECT 'S363'" instead of the SOUNDEX to make sure a "normal" string is making it into the field to which would then seem to imply it's somehow something about the particular about the string as returned from SOUNDEX. Probably a stupid question, but have you run a filter log merged with your sql log to make sure that some filter isn't nulling the field before it gets written to the DB? -charlie On Mon, Aug 4, 2014 at 8:59 AM, Reiser, John J <[email protected]> wrote: > ** > > Charlie, > > > > I did as you suggested. The workflow showed up as select soundex(N’STEHRT’) > > I never saw the “N” before. I see it in other logs now too. > > So I ran that statement and it returns S363 but in the log file it sets > the Lname_soundex field to “” > > The update statement later in the log shows C672201002=N'' > > > > So I put Select ‘S363’ into my filter and ran a modify that would fire the > filter. > > It placed S363 into the Lname_soundex field just as it should. > > > > > > I’ll give the Update statement a try. > > > > > > > > Thank you, > > --- > John J. Reiser > Remedy Developer/Administrator > > Senior Software Development Analyst > Lockheed Martin - MS2 > The star that burns twice as bright burns half as long. > Pay close attention and be illuminated by its brilliance. - paraphrased by > me > > > > *From:* Action Request System discussion list(ARSList) [mailto: > [email protected]] *On Behalf Of *Charlie Lotridge > *Sent:* Thursday, July 31, 2014 11:37 AM > *To:* [email protected] > *Subject:* EXTERNAL: Re: using SQL soundex function to find similar names > > > > ** > > John, > > > > This is a pretty strange problem you're having, and I can suggest the > following steps to *try* to figure out what's going on: > > > > 1) Run a SQL log and capture the actual SQL being issued to generate the > soundex value, then run it in a query window in the MS SQL Management > Studio. Make sure it's doing what you expect. > > > > 2) Take the actual string returned by the SQL above and put it directly > into the Set Fields' "SELECT" statement. E.g. "SELECT 'G123'". Then make > sure that the value "G123" is being returned to the Lname_Soundex field. > > > > But if all this fails to yield anything useful, you can probably just work > around the problem by updating the Lname_Soundex field directly using a > Direct SQL action in a filter: > > > > UPDATE <The Form's View Name> > > SET Lname_Soundex = SOUNDEX(User_Last_Name) > > > > Of course, this is problematic during a CREATE operation, so don't bother > trying during a Submit triggered filter. But a work-around to THIS is to > have a Submit triggered filter do a Push Fields to the same record to > trigger the filter that runs that Direct SQL (which probably just should > run with the qualification 'User Last Name' != $NULL$ AND 'Lname_Soundex' = > $NULL$). Of course be sure the filter doing this Push Fields does not have > the tick-bang (`!) suffix - you don't want the Push Fields happening before > the record is actually created in the DB. > > > > If the problem is isolated to just this Lname_Soundex situation then this > should be a sufficient workaround: you don't ever *really* need to > visualize the soundex data at the app level anyway (do you?). And you've > seemed to indicate that this is true (it's isolated). If it's not > isolated, then, obviously, you really need to figure out what's going on as > this is probably causing data corruption. > > > > If any of this is not clear let me know. > > > > -charlie > > > > On Thu, Jul 31, 2014 at 8:02 AM, Reiser, John J <[email protected]> > wrote: > > Fred, > Wouldn't that cause problems with all set fields actions where I'm getting > data from other forms or is it because the function is modifying and > returning a value? > I'll look around to see what I can find. > > > Thank you, > --- > John J. Reiser > Remedy Developer/Administrator > Senior Software Development Analyst > Lockheed Martin - MS2 > The star that burns twice as bright burns half as long. > Pay close attention and be illuminated by its brilliance. - paraphrased by > me > > -----Original Message----- > From: Action Request System discussion list(ARSList) [mailto: > [email protected]] On Behalf Of Grooms, Frederick W > Sent: Thursday, July 31, 2014 10:12 AM > To: [email protected] > Subject: EXTERNAL: Re: using SQL soundex function to find similar names > > Remember . The thick client (Windows User Tool) is not truly Unicode > compliant > > It sounds like you may have some sort of mismatch in the character sets > (between SQL, the server, and the client) > > Fred > > -----Original Message----- > From: Action Request System discussion list(ARSList) [mailto: > [email protected]] On Behalf Of Reiser, John J > Sent: Thursday, July 31, 2014 8:50 AM > To: [email protected] > Subject: using SQL soundex function to find similar names > > ** > Hello Listers, > ARS 8.1.00 > MS SQL 2008 > MS OS 2008 R2 > > While I'm working on my DSO issues and the report publisher I also came > across a problem in some workflow. > > I've been calling the soundex() function for years in my Customer Form to > make it easier to look for names that may be spelled incorrectly. > You know Smith instead of Smyth and such. > > Since we've moved to the new system the set fields action is returning > non-displayable characters in the thick client and what looks to me like > oriental characters in Mid Tier. > Is this a Unicode setting or does ARS 8.1 not handle the returned varchar > properly? > > I just run a Set Fields filter using SQL as the data source with a query > of SELECT SOUNDEX('$User Last Name$') Then I set my field Lname_Soundex > with $1$ > > Can this be corrected or is there a better way to search and manage > similar names? > > Thank you, > --- > John J. Reiser > Remedy Developer/Administrator > Senior Software Development Analyst > Lockheed Martin - MS2 > The star that burns twice as bright burns half as long. > Pay close attention and be illuminated by its brilliance. - paraphrased by > me > > > > > > _______________________________________________________________________________ > UNSUBSCRIBE or access ARSlist Archives at www.arslist.org "Where the > Answers Are, and have been for 20 years" > > > _______________________________________________________________________________ > UNSUBSCRIBE or access ARSlist Archives at www.arslist.org > "Where the Answers Are, and have been for 20 years" > > > > _ARSlist: "Where the Answers Are" and have been for 20 years_ > _ARSlist: "Where the Answers Are" and have been for 20 years_ _______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org "Where the Answers Are, and have been for 20 years"
