|
|
You're in the
sql server full text search
group:searching inside a image column
sql server full text search:
I have stored my word doucument inside a column of type image in sql 2000. I have created a full text index on this column and also have cretaed a column that specifies the type of file as ".doc". I am not able to search inside the stored document. Please guide me . Thanks
vishal,
Could you provide the output the following SQL script? SELECT @@version SELECT @@language go use <you_database_name> go exec sp_help_fulltext_catalogs exec sp_help_fulltext_tables exec sp_help_fulltext_columns go sp_help <your_table_name> go Save the above output as an .rpt or .txt file and post it here. Also, review your server Application Event log for any related "Microsoft Search" or MssCi source event related to errors that reference ".doc". Also what is the language of the text contained within the MS Word documents? Regards, John [quoted text, click to view] "vishal" <vmahajan@noida.hcltech.com> wrote in message news:055a01c384d4$b5ef2df0$a001280a@phx.gbl... > Hi > I have stored my word doucument inside a column of type > image in sql 2000. I have created a full text index on > this column and also have cretaed a column that specifies > the type of file as ".doc". I am not able to search inside > the stored document. > Please guide me . > Thanks >
Hi John
The language of the text inside the documents is English. I have executed the script send by you and the results are : ------------------------------------------------------------------------ ------------------------------------------------------------------------ ------------------------------------------------------------------------ ---------------------------------------- Microsoft SQL Server 2000 - 8.00.194 (Intel X86) Aug 6 2000 00:57:48 Copyright (c) 1988-2000 Microsoft Corporation Developer Edition on Windows NT 5.0 (Build 2195: Service Pack 4) (1 row(s) affected) ------------------------------------------------------------------------ -------------------------------------------------------- us_english (1 row(s) affected) ftcatid NAME PATH STATUS NUMBER_FULLTEXT_TABLES ------- ------------------------------------------------------------------------ -------------------------------------------------------- ------------------------------------------------------------------------ ------------------------------------------------------------------------ ------------------------------------------------------------------------ ---------------------------------------- ----------- ---------------------- 5 catalog D:\test 1 1 (1 row(s) affected) TABLE_OWNER TABLE_NAME FULLTEXT_KEY_INDEX_NAME FULLTEXT_KEY_COLID FULLTEXT_INDEX_ACTIVE FULLTEXT_CATALOG_NAME ------------------------------------------------------------------------ -------------------------------------------------------- ------------------------------------------------------------------------ -------------------------------------------------------- ------------------------------------------------------------------------ -------------------------------------------------------- ------------------ --------------------- ------------------------------------------------------------------------ -------------------------------------------------------- dbo Horn PK_longhorn 1 1 catalog (1 row(s) affected) TABLE_OWNER TABLE_ID TABLE_NAME FULLTEXT_COLUMN_NAME FULLTEXT_COLID FULLTEXT_BLOBTP_COLNAME FULLTEXT_BLOBTP_COLID FULLTEXT_LANGUAGE ------------------------------------------------------------------------ -------------------------------------------------------- ----------- ------------------------------------------------------------------------ -------------------------------------------------------- ------------------------------------------------------------------------ -------------------------------------------------------- -------------- ------------------------------------------------------------------------ -------------------------------------------------------- --------------------- ----------------- dbo 1333579789 Horn doc_stored 2 Extn 3 1033 (1 row(s) affected) Name Owner Type Created_datetime ------------------------------------------------------------------------ -------------------------------------------------------- ------------------------------------------------------------------------ -------------------------------------------------------- ------------------------------- ------------------------------------------------------ Horn dbo user table 2003-09-27 15:01:37.677 Column_name Type Computed Length Prec Scale Nullable TrimTrailingBlanks FixedLenNullInSource Collation ------------------------------------------------------------------------ -------------------------------------------------------- ------------------------------------------------------------------------ -------------------------------------------------------- ----------------------------------- ----------- ----- ----- ----------------------------------- ----------------------------------- ----------------------------------- ------------------------------------------------------------------------ -------------------------------------------------------- DocId int no 4 10 0 no (n/a) (n/a) NULL doc_stored image no 16 no (n/a) (n/a) NULL Extn char no 5 yes no yes SQL_Latin1_General_CP1_CI_AS Identity Seed Increment Not For Replication ------------------------------------------------------------------------ -------------------------------------------------------- ---------------------------------------- ---------------------------------------- ------------------- DocId 1 1 0 RowGuidCol ------------------------------------------------------------------------ -------------------------------------------------------- No rowguidcol column defined. Data_located_on_filegroup ------------------------------------------------------------------------ -------------------------------------------------------- PRIMARY index_name index_description index_keys ------------------------------------------------------------------------ -------------------------------------------------------- ------------------------------------------------------------------------ ------------------------------------------------------------------------ ------------------------------------------------------------------ ------------------------------------------------------------------------ ------------------------------------------------------------------------ ------------------------------------------------------------------------ ---------------------------------------- PK_longhorn clustered, unique, primary key located on PRIMARY DocId constraint_type constraint_name delete_action update_action status_enabled status_for_replication constraint_keys ------------------------------------------------------------------------ ------------------------------------------------------------------------ -- ------------------------------------------------------------------------ -------------------------------------------------------- ------------- ------------- -------------- ---------------------- ------------------------------------------------------------------------ ------------------------------------------------------------------------ ------------------------------------------------------------------------ ---------------------------------------- PRIMARY KEY (clustered) PK_longhorn (n/a) (n/a) (n/a) (n/a) DocId No foreign keys reference this table.
Vishal,
Could you alter your table and change the column Extn to a char(3) or char(4) if you need to store "html" or are storing the doc extensions as ".doc"? The FT Indexing of documents only works with the file extension column defined as sysname or as char(3) or char(4). Once you've made the change, run a Full Population. I'd also recommend that you apply the latest Service Pack (SP3 or SP3a) to your SQL Server 2000 RTM version 194, when you can, primarily for security reasons. Regards, John [quoted text, click to view] "vishal mahajan" <vmahajan@noida.hcltech.com> wrote in message
news:u9alDRkhDHA.2056@TK2MSFTNGP09.phx.gbl... > Hi John > > The language of the text inside the documents is English. > I have executed the script send by you and the results are : > > > ------------------------------------------------------------------------ > ------------------------------------------------------------------------ > ------------------------------------------------------------------------ > ---------------------------------------- > Microsoft SQL Server 2000 - 8.00.194 (Intel X86) > Aug 6 2000 00:57:48 > Copyright (c) 1988-2000 Microsoft Corporation > Developer Edition on Windows NT 5.0 (Build 2195: Service Pack 4) > > > (1 row(s) affected) > > > ------------------------------------------------------------------------ > -------------------------------------------------------- > us_english > > (1 row(s) affected) > > ftcatid NAME > PATH > STATUS NUMBER_FULLTEXT_TABLES > ------- > ------------------------------------------------------------------------ > -------------------------------------------------------- > ------------------------------------------------------------------------ > ------------------------------------------------------------------------ > ------------------------------------------------------------------------ > ---------------------------------------- ----------- > ---------------------- > 5 catalog > D:\test > 1 1 > > (1 row(s) affected) > > TABLE_OWNER > TABLE_NAME > FULLTEXT_KEY_INDEX_NAME > FULLTEXT_KEY_COLID FULLTEXT_INDEX_ACTIVE FULLTEXT_CATALOG_NAME > ------------------------------------------------------------------------ > -------------------------------------------------------- > ------------------------------------------------------------------------ > -------------------------------------------------------- > ------------------------------------------------------------------------ > -------------------------------------------------------- > ------------------ --------------------- > ------------------------------------------------------------------------ > -------------------------------------------------------- > dbo > Horn > PK_longhorn > 1 1 catalog > > (1 row(s) affected) > > TABLE_OWNER > TABLE_ID TABLE_NAME > FULLTEXT_COLUMN_NAME > FULLTEXT_COLID FULLTEXT_BLOBTP_COLNAME > FULLTEXT_BLOBTP_COLID FULLTEXT_LANGUAGE > ------------------------------------------------------------------------ > -------------------------------------------------------- ----------- > ------------------------------------------------------------------------ > -------------------------------------------------------- > ------------------------------------------------------------------------ > -------------------------------------------------------- -------------- > ------------------------------------------------------------------------ > -------------------------------------------------------- > --------------------- ----------------- > dbo > 1333579789 Horn > doc_stored > 2 Extn > 3 1033 > > (1 row(s) affected) > > Name > Owner > Type Created_datetime > ------------------------------------------------------------------------ > -------------------------------------------------------- > ------------------------------------------------------------------------ > -------------------------------------------------------- > ------------------------------- > ------------------------------------------------------ > Horn > dbo > user table 2003-09-27 15:01:37.677 > > > > Column_name > Type > Computed Length Prec Scale Nullable > TrimTrailingBlanks FixedLenNullInSource > Collation > ------------------------------------------------------------------------ > -------------------------------------------------------- > ------------------------------------------------------------------------ > -------------------------------------------------------- > ----------------------------------- ----------- ----- ----- > ----------------------------------- ----------------------------------- > ----------------------------------- > ------------------------------------------------------------------------ > -------------------------------------------------------- > DocId > int > no 4 10 0 no > (n/a) (n/a) > NULL > doc_stored > image > no 16 no > (n/a) (n/a) > NULL > Extn > char > no 5 yes > no yes > SQL_Latin1_General_CP1_CI_AS > > > Identity > Seed Increment > Not For Replication > ------------------------------------------------------------------------ > -------------------------------------------------------- > ---------------------------------------- > ---------------------------------------- ------------------- > DocId > 1 1 > 0 > > > RowGuidCol > ------------------------------------------------------------------------ > -------------------------------------------------------- > No rowguidcol column defined. > > > Data_located_on_filegroup > ------------------------------------------------------------------------ > -------------------------------------------------------- > PRIMARY > > > index_name > index_description > index_keys > ------------------------------------------------------------------------ > -------------------------------------------------------- > ------------------------------------------------------------------------ > ------------------------------------------------------------------------ > ------------------------------------------------------------------ > ------------------------------------------------------------------------ > ------------------------------------------------------------------------ > ------------------------------------------------------------------------ > ---------------------------------------- > PK_longhorn
Hi John I found the solution for the problem. I had tried by making the Extn column length to Char(4), but it was not working. Then I tried by changing the datatype of the column to VarChar(4) and here it goes :) i got the results. Thanks a lot for your responses, and I will install the service pack as mentioned by you. Regards Vishal *** Sent via Developersdex http://www.developersdex.com ***
Hi Vishal,
Good news! Using a varchar was going to be next recommendation as NULLability &/or if you're storing ".doc" (note, the . dot within the quotes) in a char(4) file extension datatype as the column's NULLability and what you store also affect whether or not the MSSearch engine finds up the correct file type to be FT Indexed correctly. Note, sp_help output is difficult to read within an email (word wrapping, etc.), and that is why I asked for you to "Save the above output as an .rpt or .txt file...". None the less, the outcome is more important! Regards, John [quoted text, click to view] "vishal mahajan" <vmahajan@noida.hcltech.com> wrote in message news:edFwstxhDHA.620@TK2MSFTNGP11.phx.gbl... > > Hi John > > I found the solution for the problem. I had tried by making the Extn > column length to Char(4), but it was not working. > Then I tried by changing the datatype of the column to VarChar(4) and > here it goes :) i got the results. > > Thanks a lot for your responses, and I will install the service pack as > mentioned by you. > > Regards > Vishal > > > *** Sent via Developersdex http://www.developersdex.com *** > Don't just participate in USENET...get rewarded for it!
For all those reading along on the newsgroups, Vishal makes a great point --
this comes up from time to time. Your best bet is to use varchar(n) as your type column data type. For char/nchar(n), if the value you insert is shorter than n, it will be padded with spaces (this is part of the ANSI SQL standard for char/nchar) -- this padding will mean that the filter for that file type will not be loaded because the search process does not trim trailing spaces (by design). So the best practice to observe here is to virtually always use varchar(n) for your FT type column data type unless you know that every extension you will be inserting is going to be the same length. Thanks, --andrew Andrew Cencini Program Manger Microsoft Corp. - SQL Server Engine -- This posting is provided "AS IS" with no warranties, and confers no rights. [quoted text, click to view] "vishal mahajan" <vmahajan@noida.hcltech.com> wrote in message news:edFwstxhDHA.620@TK2MSFTNGP11.phx.gbl... > > Hi John > > I found the solution for the problem. I had tried by making the Extn > column length to Char(4), but it was not working. > Then I tried by changing the datatype of the column to VarChar(4) and > here it goes :) i got the results. > > Thanks a lot for your responses, and I will install the service pack as > mentioned by you. > > Regards > Vishal > > > *** Sent via Developersdex http://www.developersdex.com *** > Don't just participate in USENET...get rewarded for it!
Andrew,
I said basically the same thing in my previous post, after Vishal reported a successful FT Indexing of his documents in SQL Server,, for the benefit of others who read this newsgroup. Note, my below post was from today, Tuesday, September 30, 2003 at 8:07 AM.... Hi Vishal, Good news! Using a varchar was going to be next recommendation as NULLability &/or if you're storing ".doc" (note, the . dot within the quotes) in a char(4) file extension datatype as the column's NULLability and what you store also affect whether or not the MSSearch engine finds up the correct file type to be FT Indexed correctly. Note, sp_help output is difficult to read within an email (word wrapping, etc.), and that is why I asked for you to "Save the above output as an .rpt or .txt file...". None the less, the outcome is more important! Regards, John [quoted text, click to view] "Andrew Cencini [MSFT]" <acencini@online.microsoft.com> wrote in message news:#gE5Rc3hDHA.2164@TK2MSFTNGP09.phx.gbl... > For all those reading along on the newsgroups, Vishal makes a great point -- > this comes up from time to time. > > Your best bet is to use varchar(n) as your type column data type. For > char/nchar(n), if the value you insert is shorter than n, it will be padded > with spaces (this is part of the ANSI SQL standard for char/nchar) -- this > padding will mean that the filter for that file type will not be loaded > because the search process does not trim trailing spaces (by design). > > So the best practice to observe here is to virtually always use varchar(n) > for your FT type column data type unless you know that every extension you > will be inserting is going to be the same length. > > Thanks, > --andrew > > Andrew Cencini > Program Manger > Microsoft Corp. - SQL Server Engine > > -- > This posting is provided "AS IS" with no warranties, and confers no rights. > > > "vishal mahajan" <vmahajan@noida.hcltech.com> wrote in message > news:edFwstxhDHA.620@TK2MSFTNGP11.phx.gbl... > > > > Hi John > > > > I found the solution for the problem. I had tried by making the Extn > > column length to Char(4), but it was not working. > > Then I tried by changing the datatype of the column to VarChar(4) and > > here it goes :) i got the results. > > > > Thanks a lot for your responses, and I will install the service pack as > > mentioned by you. > > > > Regards > > Vishal > > > > > > *** Sent via Developersdex http://www.developersdex.com *** > > Don't just participate in USENET...get rewarded for it! > >
Don't see what you're looking for? Try a search.
|
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
| home · blog · groups | Questions? Comments? Contact the dn |