CODEWITHSUNDEEP
sql-servert-sqlfull-text-search
AspCoder
AspCoder

Reputation: 83

Sql query for full-text on a column

I know, it's best to get to the point when I ask a question here, but if I don't explain the full situation, somebody more than likely would question why I'm in this predicament (that I actually explain starting in the third paragraph beginning with bold text), and in that case I would've had to explain it anyway, so, I decided to get that out of the way from the get-go.

I used to have a better laptop, but my brothers ferrets spilled water on it. So, for the last year I've been using an older laptop. It works and is reasonably fast enough. But, it came with Windows 7, I installed Windows 8, 8.1, and now 10 on it. During all this time that I've had this machine (and yes, before it too), I've been doing computer programming as a hobby, and have gotten pretty decent at it. But, I've installed Visual Studio 2010, 2012, 2013, and 2015 on it (and still have all those additions installed, because of a memory of once when I uninstalled an older Visual Studio upon installing a new Visual Studio - the un-installation of the old Visual Studio somehow did something to the new Visual Studio and made it unusable, which forced me to reinstall Windows and start back from square one, and reinstalling Windows on this machine would be a horrible decision for me because I also produce music as an on-again-off-again hobby, and it has a library of very important plugins for music production which I don't want to loose - among other reasons).

But the issue at the core of my question is this - I've installed several editions of SQL Server on this machine - 2008, 2008 R2, 2012, and 2014. Having done this, it created a strange scenario. In Visual Studio 2015, I can easily create and manipulate SQL databases. But, as probably everybody who knows anything about Microsoft SQL Server knows, there are some database manipulation features, even in Visual Studio 2015 (with it's more advanced manipulation capabilities than previous editions), that just cannot be done, and the only place to do such things is inside of SQL Server Management Studio. Well, since I've installed several versions of SQL Server during the lifetime of this machine across several editions of Windows, something pretty strange has happened. Every single time I installed SQL Server - I installed with Advanced Services (with full-text search enabled). Ok, so, when I go into SQL Server Management Studio 2014 - it just will not log into my database system and gives me an error message that says (among other things...) "A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible." Well the point is, I've made databases on different projects before, and I incorporated Full-Text services on a (or some...) columns in a Table before. Look, I understand T-SQL and can write it, but, I'm not exceptionally well-versed in it. So, when I've setup Full-Text services like I said on previous databases - it was (obviously) done inside of SQL Server Management Studio.

So, the main point is - I have a Table that I'd like to set Full-Text services on a certain column in this database I made. Following this paragraph, I wrote an example create table script, and I highlight the column I'd like to have Full-Text services applied to, please can someone show me how to do this properly.

CREATE TABLE [dbo].[thisTable] (
    [Id]  INT             IDENTITY (1, 1) NOT NULL,
    [aColumn]    DATETIME        NOT NULL, 
    [INEEDFULLTEXTHERE] NVARCHAR (100)  NOT NULL <<< the column I need full-text on
)

Upvotes: 1

Views: 379

Answers (2)

AspCoder
AspCoder

Reputation: 83

I decided to select gofr1's answer as the answer to this question out of respect for the fact that he came here and gave a great shot at the answer, and I think his answer is would certainly work if applied properly. Unfortunately, as I said, even though I do have some knowledge in T-SQL - it's not as well-rounded as I would wish, and I tried applying his answer, but had problems, but I am pretty sure if I knew a little bit more knowledge of T-SQL, I would've been able to tweak it a little to make it work perfectly. But I think it's a good reference point, and offers a valuable perspective.

However, I should've done a more thorough search for answers to such a conundrum before even posting the question, but, I guess, me asking this question revealed gofr1's method - and I guess the more detailed resources on such matters - the better. But the fact of the matter is after having searched more into the matter, I came across the stackoverflow question that gave me the method of doing this that actually worked for me without having to do much tweaking at all, and was a little more straightforward, and that question can be found at - Express with Advanced Services, can't create Full Text Index

Thanks again gofr1, I'm sure your answer will come in handy to some people out there.

Upvotes: 1

gofr1
gofr1

Reputation: 15997

To enable full-text search and create catalog on your table use this:

use MyDB
go

--Enable Full-text search on the DB
if (select DATABASEPROPERTY(DB_NAME(), N'IsFullTextEnabled')) <> 1
exec sp_fulltext_database N'enable'
GO

--Create a full-text catalog
if not exists (select * from dbo.sysfulltextcatalogs where name = N'CatalogName')
exec sp_fulltext_catalog N'CatalogName', N'create'
GO

exec sp_fulltext_table N'[dbo].[thisTable] ', N'create', N'CatalogName', N'IndexName' -- Id column in your examle
GO

--Add a column to catalog
exec sp_fulltext_column N'[dbo].[thisTable] ', N'INEEDFULLTEXTHERE', N'add', 0 /* neutral */
GO

--Activate full-text for table 
exec sp_fulltext_table N'[dbo].[thisTable] ', N'activate'
GO

--Full-text index update
exec sp_fulltext_catalog 'CatalogName', 'start_full'
GO

Upvotes: 0

Related Questions