Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
athel
Starting Member
5 Posts |
 Posted - 2010-11-24 : 22:53:42
|
| I got a weird problem.I have table called "Users" stored account information(accountno, firstname, lastname)And I have another table called "Users_Related" stored other information with accountno(accountno, job, wife,sons)the accountno is like this: "B0112458550L&%*=", they are generated from other placenow I tried to lookup account information(Users table) from "User_Related" table using lookup component base on accountno, and some of them can not match(random), but I can get them using sql statement like this: "select * from Users where accountno='B0112458550L&%*='" ( this value is from User_Related table.but the lookup component cannot find it. any idea about this?=================================================Dream High, Work Hard! |
|
|
X002548
Not Just a Number
15586 Posts |
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2010-11-25 : 05:17:53
|
| I'm not sure but I think the % might confuse the lookup. % is a wildcard character used in LIKE for example. But what's the deal with this "&%*="-suffix on the account number? Can you try to remove it for testing purposes and see if that works?- LumbagoMy blog (yes, I have a blog now! just not that much content yet) -> www.thefirstsql.com |
 |
|
|
athel
Starting Member
5 Posts |
Posted - 2010-11-25 : 21:59:39
|
| thanks Lumbago, I already fixed this issue.the accountno have a space at the end of string, so lookup component cannot match them. And when I set the cachemode = "none", it works because the lookup will be executed on the database level. I don't want it work this way so I trimmed the column and now it works fine.thanks all the same.what I want to say is when you use the loopup component, please always try to upper and trim both the source and the lookup table.this "small" issue cost me a whole day, it's really a waste of time.======================Dream High, Work Hard! |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2010-11-26 : 03:14:56
|
| Ok, great that you figured it out! I guess I could have suggested this sooner but creating a Profiler trace and seeing exactly what's going on in the database is very helpful when debugging SSIS/SSRS...- LumbagoMy blog (yes, I have a blog now! just not that much content yet) -> www.thefirstsql.com |
|
|
|
|
|
|
|
|
lookup component didn't work properly