FullText Search SQLserver2005 Using VS2005 Server Explorer
Aug 16, 2007
Hello,
I am using SQLServer Express and Visual Studio 2005 to create the website. I would like to implement FullText search, but have never done it before. I have looked at the msdn documentation on FullText search in SQLServer 2005 here http://msdn2.microsoft.com/en-us/library/ms142519.aspx.
I cannot seem to figure out how to use a FT search using Visual Studio. Can someone please help me configure my database and then explain how I can run queries based on user input to return data?
I couldn't debug SQL Server by "Step into Stored Procedure" on Server Explorer of VS2008 (or VS2005) to SQL 2005 Developer on remote Windows Server 2003 machine, it allway issue exception "Unable to start T-SQL Debugging. Could not attach to SQL Server process on 'Server'. Click Help for more information"
1) The environment: The Client: Windows XP SP2 (WORKGROUP) Visual Studio 2008 (or VS2005)
The SQL Server Machine: Windows 2003 Server Sp1 (DOMAIN) SQL Server 2005 Developer
2) User account and Permission login: I create the same user account for both Client and Domain Server with the same password, i also add that user to "Administrators" group in both machine.
At the SQL Server on Server machine, i added that account to ServerSecurityLogin with 'sysadmin' role already
3) Connection and authentication: I used "Windows Authentication" for my connection to SQL server, and i checked sure my account of the connection by SQL command
SELECT SYSTEM_USER, IS_SRVROLEMEMBER ('sysadmin')
4) Firewall: I checked firewall like MSDN helping (i also tried to test by turn off firewall in both machine)
5) Visual Studio Remote Debugger: I read "How to: Enable SQL Server 2005 Debugging" on MSDN with comment "The SQL Server can run on the same machine as the application or on a remote machine. If you are debugging T-SQL code only, then no remote setup is required." so i didn't config Visual Studio Remote Debugger any thing.
Note: If i "Step Into Store Procedure" at Server locally, it works okey, so on at my PC client locally. But if i move debugging from my client to my Server, it occur error "Unable to start T-SQL Debugging. Could not attach to SQL Server process on 'Server'. Click Help for more information"???
If i execute store procedure on Server Explore, it works okey!
I have a table that contains words that will be used to search another table where FullText index has been created on searchable columns. I'm basically trying to run something like this:
SELECT t1.col1, t2.col3 FROM tbl1 t1, tbl2 t2 WHERE CONTAINS (t1.col1, t2.col1)
I know this won't work but is there a way to join these two tables so the words (t2.col1) can be passed as search conditions? There is no common key on both tables so normal join won't work. I'm trying to find a way to pass the search words from one table to another.
My site uses a text box to allow visitors to search products. I'm trying to design the SQL Statement to allow search's on full words, part words, and words/phrases regardless of the order the words are in.
E.g. megger meg mft megger (proper order in the database is megger mft) mft1710 (using 1710 should find the product)
This is my select statement (classic ASP)
Code: <% Dim RSResults__param5 RSResults__param5 = "xxx" If (Request("searchme") <> "") Then RSResults__param5 = Request("searchme") End If
I have create a table called tblcatalog with colums id(identity,primary key) and contents(varchar(100))
I have then created a full text catalog on that table and populated it.
Then i wrote the following query "select contents from tblcatalog where contains(contents,'sample data')" It is fetching 0 records even though u have 5 records with entries "sample data"
Apologies if this is not the correct place to put this.I wrote this query to perform a fulltext search on tblPages.SELECT * FROM tblPages,FREETEXTTABLE(tblPages, *,@searchTerm) searchTableWHERE [KEY] = tblPages.PageID ORDER BY RANK DESCWhere PageID is the primary key.The synax is correct but why does it never return any results?thanksmarc
Hi, I created the following table in which i created full text indexing for DocDatafld and TypeColumn was used as Type Column for DocDataFld field. I store Document name in the DocumentTitlefld and want to get the Document name and Document and want to display it in the front end application and give url for the document. Documentidfld bigint Documenttitlefld varchar MimeTypefld varchar DocDatafld varbinary(MAX) TypeColumn varchar and i am using the following query to get the contents, but not able to get the column values. select documenttitlefld,docdatafld from Documentdatatbl Freetext(DocDatafld,'C') Please help
I'm trying to do a fulltext search which returns the adjacent words also in the result, like u do a google search and it returns the paragraph containing the searched phrase.
I am Using a Full Text Search Engine to Search Members of My Site so getting a Problem in Paging Concept I Basically want FullText Search Should Return me the Row he has Found I Dont want to write a new Query to get total resulset Please can anyone Help Me
I had problem when change database from sqlserver 2000 to sql express 2005.The fulltext index does not create automatically.Example: i use sqldatasource to insert new name to my table.and then i find it by query like this : select count(*) from mytable where contains(mycol,'newname')the result is 0but if i run query to start full index : exec sp_fulltext_catalog 'myfulltext','start_full' and run query select count(*) from mytable where contains(mycol,'newname')again. The result is 1. So i alway run exec sp_fulltext_catalog 'myfulltext','start_full' after insert or update, delete to create fulltext index. When i use sql server 2000 , i didn't need do that, it automatic. Pls help me !!! how to make fulltext index create auto in sql express 2005 .
SQL Server 2005: Anyone know how best to rewrite this SQL string to perform a weighted search on my table? here's my code: SQL="SELECT RecipeName FROM recipeList WHERE FREETEXT(*,'ISABOUT " & ing01 & " WEIGHT (.1) or " & ing02 & " WEIGHT (.2) or " & ing03 & " WEIGHT (.3) or " & ing04 & " WEIGHT (.4) or " & ing05 & " WEIGHT (.5)')" the idea is to get a list of recipes most pertinant to the ingredients entered. table 'recipeList' contains the ingredients in several columns. so column 2 might say '5oz of flour', column 2 - '2oz butter' etc.
at the moment it returns the correct recipes but not in the right order. If I use 'CONTAINS' it returns nothing. hope that's not too vague!
Hello, I try to get the full-text search on SQL Server Express with advanced Services SP2 running, but I got some problems. First I tell you what I did. I created a new database called TestDB.
Then I created a TestTable inside the db with a Primary Key:
sp_fulltext_database enable create fulltext catalog testcatalog as default create fulltext index on TestTable(text) key index PK_TestTable
Now the problem: When I insert some rows in the Table the catalog get not populated. (change Tracking Setting is set to auto) Instead these error messages are written into the crawl log:
Error '0x80004005' occurred during full-text index population for table or indexed view '[TestDB].[dbo].[TestTable]' (table or indexed view ID '2073058421', database ID '5'), full-text key value 0x00000002. Attempt will be made to reindex it.
The component 'sqlfth90.dll' reported error while indexing. Component path 'c:ProgrammeMicrosoft SQL Server90COMsqlfth90.dll'.
I just implemented full text search on a table and it doesn't work like I expected. I am using a stored procedure to search a company name column.
CREATE PROCEDURE swsp_GetCompanyRecord @companyName varchar(120) as begin select recID, companyName from TABLE where contains (companyName, @companyName) end
I use the stored procedure as follows:
swsp_GetCompanyRecord '"Reynolds Aluminum"' (Quotation marks inside single quote)
If I search for a company (e.g., Reynolds Aluminum), I get the following results:
Search Returns Reynolds Reynolds Aluminum Reynolds A Reynolds Aluminum Reynolds Al nothing Reynold nothing Aluminum Reynolds Aluminum
I guess I expected this to work similar to 'LIKE' in that it would return the full company name if I gave it any portion of the name. Is there something wrong with the way I've implemented this?
I am using SSIS to replace set of tables daily. One of the table has primary, unique, foreign keys and full-text index. Before truncating, I am dropping the foreign key constraints (to truncate the parent table), truncating the tables and recreating the foreign keys.
I have few questions:
1) Do I need to drop and recreate the unique key as well? (I am not dropping the primary key) - Unique key is identity column created just for the full-text indexing since it was mentioned that key on integer is better than key on varchar and my pk is a varchar.
2) Do I need to drop and recreate the full-text index or just rebuild/repopulate it every time the table is loaded.
This is the first time i am using full text index and I was able to learn a lot about it from the sites. I would like to understand the correct approach while loading the tables.
We have a table that is Full Text Search index enabled on one column.This table has 200 lakhs of rows(20000000) . ContainsTable() function is searching data with in these 200 lakhs of rows(20000000), if any new rows are inserted then the ContainsTable is not going to search in these recent inserted rows.
We observed when we try for a data to search. it is returning the rows till the rows that are inserted date is less than 30th of march 2012. but not searching in the records that are created after April month , if even the data we are searching is available .
TableFulltextItemCount is around 2.2 crores.
Then we done rebuilt the FT catalog Index. then the TableFulltextItemCount became 0.Again we run the containstable query ,but still it is not getting results.
As the no of rows are very more . so i am not able to show the actual rows from which the data is not coming.
the below query gives 2 results that are from actual base table
HTML Code: select * from g_case_action_log where cas_details like '%235355%' and product_id = 38810
To search for the same above word using FTS,I have used the query as below
HTML Code: SELECT Distinct top 50 cal.case_id,cal.cas_details From g_case_action_log cal (READUNCOMMITTED)inner join containstable(es.g_case_action_log, cas_details, ' ("235355" OR "<br>235355" OR "235355<br> ") ') as key_tbl on cal.log_id = key_tbl.[key] Where cal.product_id = 38810 ORDER By cal.case_id DESC
I have attached one sql script file for your ref that contains create logic and index schema properties
I just realized recently that a database "XYZ" in the Object Explorer of my SQL Server Management Studio Express (SSMSE) is put in the Database Explorer of my VB 2005 Express for processing a Stored Procedure in executing the SELECT statements (not by using Input and/or Output Parameters) during the ADO.NET 2.0-VB 2005 Express programming, then the content of the database "XYZ" is not in the SSMSE. How can I return the database "XYZ" from the DataBase Explorer of VB 2005 Express back to the Object Explorer of SQL Server Management Studio Express (SSMSE) safely? Please help and advise.
in this computer sqlserver express edition is installed, i want to remove this express becoz in configuration manager it show two SQLSERVER'S are running. when i browse from COMPUTER - 2 for network servers it show server name as HASH/SQLEXPRESS, but not the main SQLSERVER.
COMPUTER -2
IP::::129.100.100.142
COMPUTER NAME::: FEROZ
MEMBER OF WORKGROUP
can anyone help me how to connect these two computers and remove this express edition
hi, i must convert my SqlServer2000 to SqlServer2005. I just do it on my Computer and i run a component "SQL Server upgrade wizard" it convert my SQLServer. But now i must do it on my Server Web and if i run that component it give me an error : "must run SQL 6.5" and i don't installed it. Can i download it with all right migration? thank you
I'm looking for help in getting the Transfer Sql Server Objects task to work. The goal is to transfer the tables, views, sprocs,udfs from a SqlServer 2000 database to a Sql Server 2005 database.When I set the task up and run it, it fails complaining about an invalid foreign key relation. The relation in question is valid,and used in the current production environment under SS2000, so I think the real problem is something else. I've seen reports thatthis kind of error occurs when the Transfer task encounters a foreign key involving a table that hasn't been transferred yet.As an aside, this kind of transfer worked flawlessly under Enterprise Manager when I used the DTS Wizard to move the objects betweentwo instances of SqlServer2000. Unfortunately, Enterprise Manager doesn't appear to work with SqlServer2005.Any help and/or pointers would be greatly appreciated (including other strategies on how to affect the transfer -- I tried using theTransfer Database task, too, but it failed when it supposedly couldn't find the path on the target server -- I say supposedlybecause it was able to find the path in order to delete the pre-existing files on the target server)!- Mark
I have a problem restoring a Read-Only filegroup in a database. I've restored the Read/Write filegroups first without any problem.
Then, I am able to recover Read-Only filegroups which do not contain fulltext indexes with the command : RESTORE DATABASE GSM FILEGROUP = 'FG_RO_20080214' WITH RECOVERY
However, for filegroups containing FullText indexes (Fulltext indexes are in the same filegroup as data used for this index), I always get the following error :
Server: Msg 3149, Level 16, State 6, Line 1 The file or filegroup "sysft_CAT_SM_20080127" is not in a valid state for the "Recover Data Only" option to be used. Only secondary files in the OFFLINE or RECOVERY_PENDING state can be processed. Server: Msg 3013, Level 16, State 1, Line 1 RESTORE DATABASE is terminating abnormally.
If I'm looking at sys.database_files catalog view, I can see the following name type_desc state_desc ------------------------ ----------- ----------------- sysft_CAT_MS_20080127 FULLTEXT RECOVERY_PENDING
Hello.. When I used Microsoft SQL Server 2005 Management Studio Express to Create FULL TEXT INDEX by this code:
CREATE FULLTEXT INDEX ON txtfilestbl(txtfile) KEY INDEX PK_txtfilestbl ON ForumsArchiveLibCtlg WITH CHANGE_TRACKING AUTO
It returns this ERR MSG:
Informational: No full-text supported languages found. Informational: No full-text supported languages found. Msg 7680, Level 16, State 1, Line 1 Default full-text index language is not a language supported by full-text search.
I Use same this code to create FULL TEXT INDEX by using Microsoft SQL Server 2005 Management Studio, and it was working properly. What I have to do?
I have a scenario of where the standard Full-Text search identifies keywords but Semantic Search does not recognize them as keywords. I'm hoping to understand why Semantic Search might not recognize them. The context this is being used in medical terminology and the specific key words I noticed missing right off the bat were medications.
For instance, if I put the following string into a FT indexed table
'J9355 - Trastuzumab (Herceptin)' AND 'J9355 - Trastuzumab emtansine'
The Semantic Search recognized 'Herceptin' and 'Emtansine' but not 'Trastuzumab'
Nor in
'J8999 - Everolimus (Afinitor)'
It did not recognize 'Afinitor' as a keyword.
In all cases the Base of Full-Text did find those keywords and were identifiable using the dmvsys.dm_fts_index_keywords_by_document.It does show the index as having completed.
why certain words might not be picked up while others would be? Could it be a language/dictionary issue? I am using English and accent insensitive settings?
I just installed 2005 Evaluation version (high spec laptop but running Windows XP Home edition).
Have opened SQL Server Management Studio then set up a dummy database with a table and a query then tried opening the Server Explorer.
I understand that Server Explorer should be on the 'View' tab with, say, Object Explorer but I can't see it.
Does the fact I am running this on XP Home edition have anything to do with it? Or do I need to run another SETUP.EXE or configure some options or is it something else?
When I installed SQL Server 2005 I got one error message saying:
Microsoft Internet Information Services (IIS) is either not installed or is disabled. IIS is required by some SQL Server features. Without IIS, some SQL Server features will not be available for installation. To install all SQL Server features, install IIS from Add or Remove Programs in Control Panel or enable the IIS service through the Control Panel if it is already installed, and then run SQL Server Setup again. For a list of features that depend on IIS, see Features Supported by Editions of SQL Server in Books Online.
Does Server Explorer need IIS to be running in order to work?
would you use sql server "full text search" feature as your site index? from some reason i can't make index server my site search catalog, and i wonder if the full text is the solution. i think that i wll have to you create new table called some thing like "site text" and i will need to write every text twice- one the the table (let's say "articles table") and one to the text. other wise- there is problems finding the right urlof the text, searching different tables with different columns name and so on... so i thought create site search table, with the columns: id, text, url and to write every thing to this table. but some how ot look the wrong way, that every forum post, every article, album picture or joke will insert twice to the sqr server... what do you think?
I am trying to establish a connection to a SQL server using server explorer. I have tried both the DNS name and the IP address and get this message: "Could not reconnect to "sqlserver". Make sure the machine name and path are valid.When I go to Enterprise Manager and enter the IP address, I can successfully establish a connection.What gives? Is this a VS problem?
Dear All, Can I add more than one server in ServerExplorer bar.I wll use Client Server utility to add create a alias of a same server and add more than one server in SQL 2000. How can do this in SQL 2005
Hi, I have a couple of MS SQL 2005 Mobile edition databases that I was using previously. I am now not able to open them, I have not used them for about 3 weeks and do not recall doing anything 3 weeks ago to cause them to fail (Except maybe SQL Server 2005 install, not CE/Mobile).
There is no option for adding a connection to SQL Mobile 5 DB (or CE2.0 for that matter, which is also installed) when adding new data connection in VS.NET Server Explorer.
The databases seem to work fine when on the device, my probelm is accessing the database file .sdf from VS.NET. I have tried uninstalling and reinstalling SQL Server Mobile 2005 several times with no luck. I am developing on XP Prof with VS.net 2005 for Pocket PC Phone 2003 and 2005
If I click on the already existing connection I get message "The Provider was not found"
If I click on the file directly from VS.net get a message "There is no editor available for 'C:lahb;ah.sdf' Make sure the application for the file type (.sdf) is installed"
I have been using these databases for some time and desperately need them back. ANY assistance or comment would be appreciated. Rod
I am a Visual Studio .net developer and understand Solutions and Projects in VS.net. Nevertheless, it appears that the paradigm of using Solutions Explorer in SQL Server is not exactly the same. That is, in Visual Studio you can't get anything done without creating a solution first. In SQL Server, it is entirely possible to get a lot done without a solution and the ability to create and make use of a solution seems almost an after thought or an add-on. I am particularly interested in using a solution in SQL Server to allow me to make use of SourceSafe. In the past, we jumped through all kinds of hoops to keep a running trail of changes we had made to (for example) stored procedures. It appears now that this can be managed more efficiently with the use of a Solution and SourceSafe and saving scripts or .sql files. I guess it is not possible to save a pristine copy of a stored procedure directly in SourceSafe, but you can save the code that makes up the stored procedure as a .SQL file which I suppose is about as good. Anyway, what I would like to know is whether there is a good white paper (or chapter in a book) on the philosophy behind Solutions and projects in SQL Server and how MS intends us to use them; perhaps something other than the cold facts from the documentation. I would appreciate something more that gives me the big picture on just how MS sees them being used in SQL Server so I can get the most out of them. I have the feeling that if I "get the vision", they are meant to be an integral part of SQL Server now and in the future. Woody
I've had VS2005 installed for quite some time but I just purchansed SQL Server 2005 Developer and installed it. How do I accomplish integeration with SQL Server 2005 to VS2005? I had C# express at one point and SQL Express with the full tool install. That provided me with a designer for SQL Server (which was simply VS2005) and a seperate development environment under C# express. How do I integerate them so all the functionality resides within VS2005?
Any help you can provide will be much appreciated.
