August 14, 2013 at 10:33 pm
I'm following along in Itzhik Ben Gan's book for Exam 70-461 Querying Microsoft SQL Server 2012, in chapter 6 on Creating Full-Text Catalogs and Indexes. I'm on exercise 1 reproducing steps to Create a Table and Full-Text Components.
I am using SQL Server 2012.
I have checked that Full-Text Search is installed by using the following query.
SELECT SERVERPROPERTY('IsFullTextInstalled');
GO
I have created a table using the following code.
CREATE TABLE dbo.Documents
(
id INT IDENTITY(1,1) NOT NULL,
title NVARCHAR(100) NOT NULL,
doctype NCHAR(4) NOT NULL,
docexcerpt NVARCHAR(1000) NOT NULL,
doccontent VARBINARY(MAX) NOT NULL,
CONSTRAINT PK_Documents
PRIMARY KEY CLUSTERED(id)
);
GO
I've imported four documents included in exercise folder using following statements:
INSERT INTO dbo.Documents
(title, doctype, docexcerpt, doccontent)
SELECT N'Columnstore Indices and Batch Processing',
N'docx',
N'You should use a columnstore index on your fact tables,
putting all columns of a fact table in a columnstore index.
In addition to fact tables, very large dimensions could benefit
from columnstore indices as well.
Do not use columnstore indices for small dimensions. ',
bulkcolumn
FROM OPENROWSET(BULK 'C:\Users\Helen\Documents\70-461\Chapter 06\ColumnstoreIndicesAndBatchProcessing.docx',
SINGLE_BLOB) AS doc;
INSERT INTO dbo.Documents
(title, doctype, docexcerpt, doccontent)
SELECT N'Introduction to Data Mining',
N'docx',
N'Using Data Mining is becoming more a necessity for every company
and not an advantage of some rare companies anymore. ',
bulkcolumn
FROM OPENROWSET(BULK 'C:\Users\Helen\Documents\70-461\Chapter 06\IntroductionToDataMining.docx',
SINGLE_BLOB) AS doc;
INSERT INTO dbo.Documents
(title, doctype, docexcerpt, doccontent)
SELECT N'Why Is Bleeding Edge a Different Conference',
N'docx',
N'During high level presentations attendees encounter many questions.
For the third year, we are continuing with the breakfast Q&A session.
It is very popular, and for two years now,
we could not accommodate enough time for all questions and discussions! ',
bulkcolumn
FROM OPENROWSET(BULK 'C:\Users\Helen\Documents\70-461\Chapter 06\WhyIsBleedingEdgeADifferentConference.docx',
SINGLE_BLOB) AS doc;
INSERT INTO dbo.Documents
(title, doctype, docexcerpt, doccontent)
SELECT N'Additivity of Measures',
N'docx',
N'Additivity of measures is not exactly a data warehouse design problem.
However, you have to realize which aggregate functions you will use
in reports for which measure, and which aggregate functions
you will use when aggregating over which dimension.',
bulkcolumn
FROM OPENROWSET(BULK 'C:\Users\Helen\Documents\70-461\Chapter 06\AdditivityOfMeasures.docx',
SINGLE_BLOB) AS doc;
GO
All good so far, but then I try to CREATE SEARCH PROPERTY LIST as follows and get error.
CREATE SEARCH PROPERTY LIST WordSearchPropertyList;
GO
ALTER SEARCH PROPERTY LIST WordSearchPropertyList
ADD 'Authors'
WITH (PROPERTY_SET_GUID = 'F29F85E0-4FF9-1068-AB91-08002B27B3D9',
PROPERTY_INT_ID = 4,
PROPERTY_DESCRIPTION = 'System.Authors - authors of a given item.');
GO
Error is:
Msg 343, Level 15, State 1, Line 1
Unknown object type 'SEARCH' used in a CREATE, DROP, or ALTER statement.
Msg 343, Level 15, State 1, Line 1
Unknown object type 'SEARCH' used in a CREATE, DROP, or ALTER statement.
Can someone help me get past error so I can run statement? Googled by no documentation about this error found.
--Quote me
August 15, 2013 at 6:24 am
Here's the code I use in demos:
CREATE SEARCH PROPERTY LIST TitleProperties;
GO
ALTER SEARCH PROPERTY LIST TitleProperties
ADD 'Title'
WITH ( PROPERTY_SET_GUID = 'F29F85E0-4FF9-1068-AB91-08002B27B3D9', PROPERTY_INT_ID = 2,
PROPERTY_DESCRIPTION = 'System.Title - Title of the item.' );
GO
This should work. Are you sure you've created a full text index and it works? You will assign the search property list to an FTS index. I'd make sure that works first.
I'm not sure why you get the error or "search". Are you positive you're connected to a 2012 instance and not an earlier version?
August 15, 2013 at 4:29 pm
Strange, I've got both installed (SSMS 2008 & SSMS 2012) and I'm definitely connecting to the one listed in my START menu as 2012, and even the Help->About dialogue show I'm in 2012, but when I run SELECT @@version, I get:
Microsoft SQL Server 2008 (SP3) - 10.0.5512.0 (X64)
Aug 22 2012 19:25:47
Copyright (c) 1988-2008 Microsoft Corporation
Enterprise Evaluation Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)
Any idea what's going on? Have you seen this before? Do I have to uninstall 2008?
Helen
--Quote me
August 16, 2013 at 12:21 am
polkadot (8/15/2013)
Strange, I've got both installed (SSMS 2008 & SSMS 2012) and I'm definitely connecting to the one listed in my START menu as 2012, and even the Help->About dialogue show I'm in 2012, but when I run SELECT @@version, I get:Microsoft SQL Server 2008 (SP3) - 10.0.5512.0 (X64)
Aug 22 2012 19:25:47
Copyright (c) 1988-2008 Microsoft Corporation
Enterprise Evaluation Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)
Any idea what's going on? Have you seen this before? Do I have to uninstall 2008?
Helen
Your Start menu will have a link to SSMS 2012, but that does not necessarily mean that you are connecting to a 2012 version of the database engine. SSMS is just the client tool that you use to access SQL Server instances.
When you installed 2012, you will have had to provide an 'instance name' to distinguish it from that which is currently installed. That is what you need to connect to.
One way of reminding yourself of the instance name is to have a look at the services which are running on the server. Look for one called 'SQL Server'. In your case, there will probably be two - one for 2008 and one for 2012. The one for 2012 will be called 'SQL Server ([instance name])'.
Now you know that, go back to SSMS and connect - the server name property should be set to '[workstationname]\[instancename]' (eg, 'PHIL\SQL2012').
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
August 16, 2013 at 3:30 pm
Phil, thanks. I realize the reason SQLSERVER2012 isn't listed in the Connect dialogue box is that the SQL Server service isn't running. But I can't get it to start.
So, I know you guys have solved the problem or why SEARCH object isn't recognized. Should I start another thread to get help with starting the service. Regardless of which account (even admin) I use to try to start the service I keep getting "The request failed or the service did not respond in a timely fashion".
--Quote me
August 16, 2013 at 4:29 pm
Yes, you would be better off starting a new thread in the SQL 2012 General forum.
In the mean time, check the SQL Server errorlog for errors that may explain why the service is not starting.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
August 25, 2013 at 4:08 pm
thanks folks for help here. Got a good install of SQL Server 2012 and above statements (eg. SEARCH) now working.
--Quote me
August 25, 2013 at 4:28 pm
Glad to hear that there was a happy ending!
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
April 26, 2014 at 8:47 pm
Hi,
I am experiencing the same CREATE SEARCH issue now (8 months after the previous post). Can you please post the other thread link or what exactly was the solution you implemented to solve the issue?
Thanks in advance.
April 26, 2014 at 9:22 pm
Please ignore my post.
I was able to perform the CREATE SEARCH now after reading through the other posts and finding out that the "[local]" Server Name, which I was connected to, is an old version.
In my setting, I had to click "Browse for more" and found the "[local machine name]\MSSQLSERVER2012" to be the correct database.
Thanks.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply