Solved

One query works, another doesnt'

Posted on 2006-07-20
7
269 Views
Last Modified: 2008-02-26
Hi,

This is going to be a hard question to answer as you dont have access the the server, but I have some very strange behaviour going on. I really need some trouble shooting tips to help me diagnois the problem:

The Problem:

This works:

SELECT     *
FROM         dbo.SmartPart
WHERE     CONTAINS(Description, 'A2101010') OR
                      CONTAINS(CPC_Code, 'A2101010') OR
                      CONTAINS(pkg_type, 'A2101010')


But this doesn’t

SELECT     *
FROM         dbo.SmartPart
WHERE     CONTAINS(Description, 'A2104011') OR
                      CONTAINS(CPC_Code, 'A2104011') OR
                      CONTAINS(pkg_type, 'A2104011')


Both the records are there and both have data in them, one returns a result, one doesn't and I have no idea where to start

The data fields coppied directly from enterprise manager:

      A2101010      R 1K58 1% 0603 1      1.58      KO      2KO      1%      1%      0.1      W      RES0603      1.55 mm      .85 mm      .45 mm      res      RES0603      0603      http://smartpart.europe.bkhm.net/components/moreinfo.asp?CPC_Code=A2101010      Yes      R_1K58_1%_0603_1      Yes      \\zpgty0x0.europe.bkhm.net\users\Smartpart\SmartPartData\CPC_Codes\A21\A2101\A2101010\PDF\A2101010.pdf      DX Designer      No      Chip_Resistor      Vishay.                              CRCW_0603_1581_F_100                              http://www.vishay.com                              Pending      Pending      Pending      Pending      Pending       


      A2104011      R 1K58 1% 0603 1      1.58      KO      2KO      1%      1%      0.1      W      RES0603      1.55 mm      .85 mm      .45 mm      res      RES0603      0603      http://smartpart.europe.bkhm.net/components/moreinfo.asp?CPC_Code=A2101010      Yes      R_1K58_1%_0603_1      Yes      \\zpgty0x0.europe.bkhm.net\users\Smartpart\SmartPartData\CPC_Codes\A21\A2101\A2101010\PDF\A2101010.pdf      DX Designer      No      Chip_Resistor      Vishay.                              CRCW_0603_1581_F_100                              http://www.vishay.com                              Pending      Pending      Pending      Pending      Pending       
0
Comment
Question by:bhermer
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
7 Comments
 
LVL 4

Author Comment

by:bhermer
ID: 17144852
Table Design, should it help

3      CPC_Code      varchar      16      0
0      Description      varchar      255      1
0      Electrical_Value      float      8      1
0      Electrical_Unit      varchar      50      1
0      [Value]      varchar      50      1
0      Tol_Plus      varchar      50      1
0      Tol_Minus      varchar      50      1
0      Rating      float      8      1
0      Rating_Unit      varchar      50      1
0      Package_Style      varchar      50      1
0      Body_Length      varchar      50      1
0      Body_Width      varchar      50      1
0      Body_Height      varchar      50      1
0      Symbol      varchar      250      1
0      pkg_type      varchar      70      1
0      Device      varchar      50      1
0      Web_Page      varchar      250      1
0      InPads      varchar      50      1
0      PadsName      varchar      50      1
0      Fitted      varchar      50      1
0      Datasheet_Path      varchar      250      1
0      Tool      varchar      50      1
0      RC      varchar      50      1
0      ComponentType      varchar      50      1
0      Mnf_1      varchar      50      1
0      Mnf_2      varchar      50      1
0      Mnf_3      varchar      50      1
0      Mnf_4      varchar      50      1
0      Mnf_5      varchar      50      1
0      Mnf_1_PartNum      varchar      100      1
0      Mnf_2_PartNum      varchar      100      1
0      Mnf_3_PartNum      varchar      100      1
0      Mnf_4_PartNum      varchar      100      1
0      Mnf_5_PartNum      varchar      100      1
0      Mnf_1_Web      varchar      100      1
0      Mnf_2_Web      varchar      100      1
0      Mnf_3_Web      varchar      100      1
0      Mnf_4_Web      varchar      100      1
0      Mnf_5_Web      varchar      100      1
0      Mnf_1_App      varchar      50      1
0      Mnf_2_App      varchar      50      1
0      Mnf_3_App      varchar      50      1
0      Mnf_4_App      varchar      50      1
0      Mnf_5_App      varchar      50      1
0      Preferred      varchar      50      1
0
 
LVL 12

Expert Comment

by:Einstine98
ID: 17144859
why don't you use like '%A2104011%' and see if this makes any difference? or are you actually using text search?
0
 
LVL 4

Author Comment

by:bhermer
ID: 17144909
We have created the algorithums for searching and they are pretty bug free and complex, we cannot start changing them (My manager would freak out!), The CONTAINS has been working for over2 years, the only difference now is the way we input the data into the SQL box, we were using a vb recordset, ie. rs.addnew, rs.update. We have now bought a third party library tool which is creating them with,I presume INSERT statements.

When I run this in enterprise manager I get both records returned:

SELECT     *
FROM         dbo.SmartPart
WHERE     (CPC_Code = 'A2101010') OR
                      (CPC_Code = 'A2104011')
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 17144939
I assume that the full-text index has not been updated since the insert of the record that does not get returned.
check the schedule of the full-text catalog update
0
 
LVL 12

Expert Comment

by:Einstine98
ID: 17144941
if you get it right from query analyzer or enterprise manager then your problem should be in your code or the library that you are talking about... not much help I can provide there!
0
 
LVL 4

Author Comment

by:bhermer
ID: 17144963
My Guess is that AngelIII is right, dont I feel like an idiot!, trouble is we are installing this new software for the first time today and have desperatly been trying to test it, not realizing the indexing wont happen until midnight tonight! I will see if I can get my Server admin team to initiate an index, if not I will test again tomorrow and (Hopefully) award points to AnglelIII

Thanks
0
 
LVL 4

Author Comment

by:bhermer
ID: 17145328
Thanks alot, yes it was the indexing, and yes I am an idiot for not realizing.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
MS SQL Server connect issues 4 39
Freeze portion of datamart 2 22
SQL- GROUP BY 4 23
ODBC settings not showng in FileMaker External Data Sources 6 23
Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question