Weird String mismatches MS Access/SQL Server
-
Folks,
Unfortunately, I have become responsible for some legacy code and databases.
A piece of asp code queries a number of MYSQL databases to find some totals for a number of employee_types. A number of counters are updated in code loops then this data along with the employee name (No primary key shared between databases) is inserted into MS access (Oh No!).
Im trying to pass the "employee_name" string from this access table into SQL Server via a stored procedure and lookup further data in there based on the "employee_name" value. Thing is, For certain name strings like "Johnny Onion" sql server will compare two exactly the same strings and will not find a match.
I have even created an audit table in SQL that records all "employee_names" passed in via the lookup stored procedure. When I queried across with the existing "employee_name" in the SQL database, for some names no match is found. Baffling!! What does access do to the specific strings that makes them unmatched in SQL Server.
Help me out folks!!
-
Have never worked with Access but here're a couple of things you could check (if not done so already)...
1) Is your database case sensitive ?! If so, you could use UPPER() before testing for matches.
2) You could "trim" the string for leading/trailing spaces etc.
3) Could you give some examples of the "some names" that don't match...maybe there's a pattern to these that someone on this site may be able to detect ?!
**ASCII stupid question, get a stupid ANSI !!!** -
Sorry, I should have mentioned..
I have rtrim'ed ltrim' ed upper'd, everything already. Does not help
Names that work:
Alex Labunskij
Gerry Walsh
Seamus Keane
Names that dont:
Donal McEneaney
Marcin Skupski
Piotr Warno
Thanks
M
-
There could be extra space(s) between the first and last name.
In SQL Server, try using the LEN command to find the length of the name. Then compare that to the same name in Access.
Ex. SELECT NameColumn,
LEN(namecolumn)
FROM table
WHERE namecolumn LIKE 'Donal %'
That will ignore trailing blanks, but not leading blanks. If the numbers are different and you have already checked for trailing and leading blanks, then there are too many blanks in between the first and last names.
-SQLBill
-
Cheers Bill, but I compared the len mate. I even used Soundex/Difference and found no difference.
The crazy thing is, that once I store these names in SQL Server, through my audit sp and run a query across, its the same result everytime. Just does not like those names. Mad!
-
hmm - I was going to suggest checking the length as well...wonder what your audit sp looks like...
have you tried manually entering the names that don't find a match ?! is it a varchar column ?!
**ASCII stupid question, get a stupid ANSI !!!** -
Yep, its a varchar. I have tried manually entering the names that do not find a match, and they work. ITs only when the values are pulled from an access, cst'ed into a variable and then send as a Varchar parameter to SQL Server that some do not work. Wierd
CREATE PROCEDURE get_installers_NWU
(
@date1 datetime,
@date2 datetime,
@installer_name varchar(50),
@NWU int OUTPUT
)
AS
'this is just to record the names so I can check later why join not working
insert installer_audit(installer_name,date1,date2) values(@installer_name,@date1,@date2)
SELECT @NWU = coalesce(sum(installer_nwd.nwd),0)
FROM installer_NWD LEFT JOIN installer_weeks ON installer_NWD.week_id
= installer_weeks.week_id
WHERE (installer_weeks.week_start >=@date1 and installer_weeks.week_start <=@date2 and installer_NWD.installer_name = @installer_name)
GO
-
Folks,
Thanks for your help. I botched a fix together by pasting the strings for the names directly into SQL Server for a lookup table. The whole system needs to be re-written, but they will not hear of it at the moment.
I really dont understand how this would happen though, and I would love of someone could explain it to me.
Is it something to do with access unicode compression...
...Ill get me coat
-
Intriguing..
You must've done this already and am curious about the outcome...
1) execute sp in QA with "problem" names.
2) change sp to do a "select * from installer_NWD where installer_name = @installer_name".
3) In the sp - push the variable through an ascii loop to see what prints out...
example from bol:
DECLARE @position int SET @position = 1 WHILE @position <= DATALENGTH(@installer_name) BEGIN SELECT ASCII(SUBSTRING(@installer_name, @position, 1)), CHAR(ASCII(SUBSTRING(@installer_name, @position, 1))) SET @position = @position + 1 END GO
**ASCII stupid question, get a stupid ANSI !!!** -
Thanks Sush,
Ill have to get the "crap" data back again to check the ascii loop out. Should'nt be a problem to find some crap
-
"Johnny Onion" is a reserved keyword in SQL Server and means "I'm not at home".
Have you had a look at the collation of you involved fields? Let Query Analyzer create a [create table]-statement for the table involved and have a look at the collation. Does this match your Access definition?
_/_/_/ paramind _/_/_/ -
maybe changing the datatype to nvarchar and retesting for matches...?!?!
ps:think you've got keywords mixed up...it's "johnny come lately" you must be thinking about!
**ASCII stupid question, get a stupid ANSI !!!** -
Could it be special characters? Like the tilde being in one name but not the other. Or an apostrophe?
O'Donnell and ODonnell
Padre` and Padre
-SQLBill
-
If it is, it should be "thrown up" in the ascii loop....
**ASCII stupid question, get a stupid ANSI !!!** -
'Johnny come lately'? , 'No-one at home'?
Are you folks taking the proverbial?
Ill try the collation thing. There is no ' or anything similar. The records look exact same, have the length etc. I even passed them into sql via sp parameters after converting to string, trimming etc. Still no joy.
Thanks for the help folks.
Cheers
M
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply