January 30, 2013 at 8:30 am
Hello all,
I've created my first filestream store on SQL 2k8 Express adv.
Created my Full Text Catalog, and index using the wizard, and loaded up 300 PDF files into it.
When I search for files using this sql, it finds the correct files perfectly
select top 200 * from items where freetext(ItemFile, 'SearchSTRING')
However, when I add a space into the string, it returns all the files, even when either of the words aren't present.
select top 200 * from items where freetext(ItemFile, 'Search STRING')
Any idea's what could be causing it?
Thanks
David.
January 31, 2013 at 3:05 am
Hello All,
I think I'm about to give up with this one, I can't get it to work properly
I'll cover every step i've performed..
I've Installed SQL 2k8r2 sp2 adv with FILESTREAM enabled on a W7 PC..
Then, using this code to create tables, FTC and FTI.
--Create your database with FILESTREAM file group.
CREATE DATABASE IntranetFiles ON PRIMARY
( NAME = IntranetFilesDB_data,
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\DATA\IntranetFiles_data.mdf',
SIZE = 10MB,
MAXSIZE = 50MB,
FILEGROWTH = 10%),
FILEGROUP RowGroup1
( NAME = IntranetFilesDB_group1,
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\DATA\IntranetFiles_group1.ndf',
SIZE = 10MB,
MAXSIZE = 50MB,
FILEGROWTH = 5MB),
FILEGROUP IntranetFilesGroup1 CONTAINS FILESTREAM
( NAME = IntranetFilesDBResumes,
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\DATA\VendorCatalog')
LOG ON
( NAME = 'IntranetFiles_log',
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\DATA\IntranetFiles_log.ldf',
SIZE = 5MB,
MAXSIZE = 25MB,
FILEGROWTH = 5MB);
-- create tables
--CREATE TABLE [dbo].[Items](
--[ItemID] UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL UNIQUE,
--[ItemNumber] VARCHAR(20),
--[ItemDescription] VARCHAR(50),
--[ItemFile] VARBINARY(MAX) FILESTREAM NULL
--)
CREATE TABLE [dbo].[Documents](
[DocID] [uniqueidentifier] ROWGUIDCOL NOT NULL,
[DocName] [varchar](128) NULL,
[DocType] [varchar](10) NULL,
[DocBody] [varbinary](max) FILESTREAM NULL,
CONSTRAINT [PK_Documents] PRIMARY KEY CLUSTERED
([DocID] ASC)
) ON [PRIMARY] FILESTREAM_ON [IntranetFilesGroup1]92
-- create FT cat
CREATE FULLTEXT CATALOG DocumentCatalog AS DEFAULT;
-- create TF index
CREATE FULLTEXT INDEX ON
dbo.Documents(
DocName,
DocBody TYPE COLUMN DocType)
KEY INDEX PK_Documents
ON DocumentCatalog
WITH CHANGE_TRACKING AUTO;
I use this SQL in a VBS loop to import 300 files into the table filestream.
DECLARE @FN AS VARBINARY(MAX)
SELECT @FN = CAST(bulkcolumn AS VARBINARY(MAX))
FROM OPENROWSET(
BULK
'C:\temp\somefile.pdf',
SINGLE_BLOB ) AS x
INSERT INTO Items (DocID, DocName, DocType, DocBody)
SELECT NEWID(), '" & fn & "','" & fne & "', @FN
wait ten miuntes while my CPU settles after indexing it all, then
-- Search for results..
select * from documents where contains(DocBody, 'Search string')
Works fine for a single word, errors on multiple.
Msg 7630, Level 15, State 3, Line 2
Syntax error near 'text' in the full-text search condition 'Some Text'.
When I use freetext, works fine for single word, but returns too many results for multiple words.
select * from dbo.Documents where freetext(DocBody, 'Some Text');
Am I missing something?
Should I be able to enter multiple words?
Could it be picking up a space in every file, and so returning every file?
Should I split the words individually, and parse them into the SQL 1 by 1?
Thanks in advance
Dave
January 31, 2013 at 3:33 am
OK, I think I've fixed it..
You have to speech mark the included text like shown.
select * from dbo.Documents where contains(DocBody, '"Some Text"');
You can also use NEAR to say the text is near something else.
select * from dbo.Documents where CONTAINSTABLE (DocBody,
'("Some Text" NEAR Other) '
Hopefully this will help someone else if they stumble across this feed.
Dave
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply