none
AdventureWorks database 2008 - Full Text Search

    Question

  • I assume there is no eBook for SQL Server Express 2008.
    The ebook for 2005 mentions the AdventureWorks sample database.

    I discovered there is a version of that for 2008.
    http://msftdbprodsamples.codeplex.com/Release/ProjectReleases.aspx?ReleaseId=18407
    File: SQL2008.AdventureWorks_All_Databases.x86.msi

    I downloaded and installed.
    Get error message:
    "The following features are missing: Full Text Search"
    So the installation failed.

    When I connect to my 2008 SQL Server, properties show:
    SQL Server Express with Advanced Services

    In this forums FAQs "What are the features of each SQL Express edition",
    Integrated Full Text Search is shown available in SQL Server Express with Advanced Services.
    http://blogs.msdn.com/sqlexpress/archive/2008/08/07/what-s-up-with-sql-server-2008-express-editions.aspx

    When I show Properties of a database, Files, "Use full text indexing" is checked and dimmed.

    Why do I get the error message?

    It would save a lot of time if the latest info for these items were included in FAQS:
    >eBooks
    >AdventureWorks Sample Database

    George

    ps, I have Windows XP Home edition

    Tuesday, March 17, 2009 1:49 AM

Answers

  • Hi George,

    1. Full Text Search is a feature of SQL Server that allows you to write a specific type of query that search text data based on a different kind of matching. This is the type of search that is being done when you see results that specify the percentage match the result has to your search string amoung other things. A full text index is the kind of index that is created to allow you to perform a full text search query.

    2. It means that the database was created to allow a full text index to be used but that the Full Text Search feature is not installed on the Server so you cannot change the database setting.

    B. What that statement means is that the menu items in SSMS that allow you to manage Full Text Search on a SQL Server are not shown when SSMS is connected to SQL Express. The reason for that limitation is that those particular menu items depend on the functionality of another feature that is not supported in SQL Express so while FTS itself is supported, the SSMS menu items can't work. This has not changed so the only way to manage FTS for SQL Express is by using T-SQL commands. Lukily, there is a set of FTS T-SQL templates available in the Template Explorer in SSMS. You can open the Template Explorer from the View menu and you find a whole list of folders with technology names on them; open the FTS folder and you'll find scripts for controlling FTS.

    C. SQLEXPR32_x86_ENU.exe is the installer package for SQL Express Basic and this SKU does not include FTS. You need to install either SQL Express with Tools (SQLEXPRTOOL_x86_ENU.exe) or SQL Express with Advanced Services (SQLEXPRADV_x86_ENU.exe) in order to get FTS. Also, you cannot just "Add Features" when trying to upgrade from one SKU to another, you have to first do a Version Upgrade, and then add features. I've explained how to do this in the SQL Express Blog, find it at http://blogs.msdn.com/sqlexpress.

    D. The AdventureWorksLT2008 database does not use any of the full text features so it will attach to SQL Express Basic without any issues, as you've discovered. Here is a short primer on the sample databases:
    •     The three MSI files make an attempt to install all the possible sample databases. There is a separate MSI per architecture so you just install the one that matches your architecture. This is not actually the best option for SQL Express Basic as there are numerous databases that aren't supported.
    • The All Databases ZIP file contains script files (.sql) to create the databases as well as other supporting files. This version has a problem for SQL Express Basic in that the AdventureWorksLT database, which can be installed on Express Basic, requires the full version of AdventureWorks in order to populate it's database tables. You cannot install the full version because of the FTS requirement.
    • The AdventureWorksLT.zip file was just recently posted to resolve the problems described above, and it includes the AdventureWroksLT (light) databae files pre-loaded with data and you can just attach them. This is the one I recomend for Express Basic as it actually works.

    Oddly enough I've been discussing this confusion with the Samples team, literally over the last two days. They are working to produce a better set of installers that are more sensitive to feature capabilities and also fixing the wierd dependency in the script files for populating data that I described in the second point. I expect that they'll be posting better sample installers in a couple months.

    Regards,
    Mike


    This posting is provided "AS IS" with no warranties, and confers no rights.
    Thursday, March 19, 2009 6:30 AM

All replies

  • Hi George,

    Are you sure you installed Full Text Search? FTS is a separate component that is not selected by default, if you're not certain you selected it you should run back through setup and select it so that the feature is added. Once you do that you should be able to enable it on a database and install the full adventure works sample.

    Note - You can always install the AdventureWorksLT database which is smaller, simpler and doesn't require FTS.

    Regards,
    Mike
    This posting is provided "AS IS" with no warranties, and confers no rights.
    Tuesday, March 17, 2009 1:54 AM
  • Mike,

    A. I said "When I show Properties of a database, Files, "Use full text indexing" is checked and dimmed."

    The casual observer (me) gets the impression that "full text indexing" is active in this product, and it is a "read only" property.

    1. Is this the same as "full text search"?

    2. What does "Use full text indexing" being checked and dimmed mean to a Microsoft professional?


    B. In another thread (9/23/08), Alok Parmesh (MSFT) said
    "Microsoft® SQL Server® 2008 Express with Advanced Services provides full text search support in engine.
    As of now the SSMS support for Full Text is not present in express edition. We are tracking this issue internally.
    Please watch the updates and patches for SQL Server to see development regarding this."
    http://social.msdn.microsoft.com/forums/en-US/sqlexpress/thread/ec4ee6a9-920a-4b94-a245-0fc894b6539b/
    It sounds like FTS is available in the Server but not in SSMS.
    Is there anything new on this?


    C. I opened installation file SQLEXPR32_x86_ENU.exe and selected "Add features".
    In the page to add features, all options are dimmed.
    None were for Full Text.


    D. Went to http://msftdbprodsamples.codeplex.com/Release/ProjectReleases.aspx?ReleaseId=18407
    Downloaded  SQL2008.AdventureWorksLT2008_Only_Database.zip
    Note: There is no explanation on that page as to what each download is for.
    I attached it to my server.
    Seems to be OK.

    Thanks for your help,

    George



     
    Tuesday, March 17, 2009 8:07 PM
  • Hi George,

    1. Full Text Search is a feature of SQL Server that allows you to write a specific type of query that search text data based on a different kind of matching. This is the type of search that is being done when you see results that specify the percentage match the result has to your search string amoung other things. A full text index is the kind of index that is created to allow you to perform a full text search query.

    2. It means that the database was created to allow a full text index to be used but that the Full Text Search feature is not installed on the Server so you cannot change the database setting.

    B. What that statement means is that the menu items in SSMS that allow you to manage Full Text Search on a SQL Server are not shown when SSMS is connected to SQL Express. The reason for that limitation is that those particular menu items depend on the functionality of another feature that is not supported in SQL Express so while FTS itself is supported, the SSMS menu items can't work. This has not changed so the only way to manage FTS for SQL Express is by using T-SQL commands. Lukily, there is a set of FTS T-SQL templates available in the Template Explorer in SSMS. You can open the Template Explorer from the View menu and you find a whole list of folders with technology names on them; open the FTS folder and you'll find scripts for controlling FTS.

    C. SQLEXPR32_x86_ENU.exe is the installer package for SQL Express Basic and this SKU does not include FTS. You need to install either SQL Express with Tools (SQLEXPRTOOL_x86_ENU.exe) or SQL Express with Advanced Services (SQLEXPRADV_x86_ENU.exe) in order to get FTS. Also, you cannot just "Add Features" when trying to upgrade from one SKU to another, you have to first do a Version Upgrade, and then add features. I've explained how to do this in the SQL Express Blog, find it at http://blogs.msdn.com/sqlexpress.

    D. The AdventureWorksLT2008 database does not use any of the full text features so it will attach to SQL Express Basic without any issues, as you've discovered. Here is a short primer on the sample databases:
    •     The three MSI files make an attempt to install all the possible sample databases. There is a separate MSI per architecture so you just install the one that matches your architecture. This is not actually the best option for SQL Express Basic as there are numerous databases that aren't supported.
    • The All Databases ZIP file contains script files (.sql) to create the databases as well as other supporting files. This version has a problem for SQL Express Basic in that the AdventureWorksLT database, which can be installed on Express Basic, requires the full version of AdventureWorks in order to populate it's database tables. You cannot install the full version because of the FTS requirement.
    • The AdventureWorksLT.zip file was just recently posted to resolve the problems described above, and it includes the AdventureWroksLT (light) databae files pre-loaded with data and you can just attach them. This is the one I recomend for Express Basic as it actually works.

    Oddly enough I've been discussing this confusion with the Samples team, literally over the last two days. They are working to produce a better set of installers that are more sensitive to feature capabilities and also fixing the wierd dependency in the script files for populating data that I described in the second point. I expect that they'll be posting better sample installers in a couple months.

    Regards,
    Mike


    This posting is provided "AS IS" with no warranties, and confers no rights.
    Thursday, March 19, 2009 6:30 AM