Full text setup questions

My company has a web site that we bought several years ago, and we have had
to replace the machines that the site runs on. I have inherited the DBA role
for this system recently. The site search engine has not been working for
some time, and after digging through the asp code, I found this query:
sSql="SELECT a.article_id,srch.size,srch.rank "&_
"FROM OPENQUERY(FileSystem,"&_
"'SELECT FileName, Size,rank "&_
"FROM SCOPE('' """ & FormScope & """ '') "&_
"WHERE CONTAINS(Contents,''" & sSearch & "'') order by rank desc,write
desc') srch,articles a"
if NOT isCMS Then
sSql=sSql & ",columns c"
End If
sSql=sSql & " where cast(a.article_id as varchar(15))+'.htm'=srch.filename"
if NOT isCMS Then sSql=sSql & " AND a.status_id in (4,5) AND
c.column_id=a.column_id
So, after some searching, I discovered that this was using the Full-Text
Search function in SQL Server to search through the content news articles
under the wwwroot folder on the web server. What I'm having a hard time
doing now is creating a linked server in SQL Server for the folder on the web
server that actually seems to work. I also haven't found enough
documentation on what needs to be set up on both servers for this to work.
What I have done so far may be incorrect, but I have set up a linked server
like so:
sp_addlinkedserver @.server = 'FileSystem'
, @.srvproduct = 'Indexing Service'
, [ @.provider = 'MSIDXS'
, [ @.datasrc = 'FormScope'
, [ @.location = '\\devserver\wwwroot\content\production\'
, [ @.catalog = 'Web'
I have created a Full-Text catalog on the SQL Server called 'Web' and a
virtual directory on the IIS server called 'Web' that points to the devserver
folder above. When I run the search from the web site, I get this error:
An error occurred while processing your query: [OLE/DB provider returned
message: There is no catalog. ]
So I'm guessing that the catalog has not been populated with the content
from the web server, but I have no idea how to do that, or even if I'm
missing one or more steps. I'm scouring the documentation I can find but no
luck so far. Any ideas?
Karl Wiggins
This is not SQL FTS. This is using Indexing Services. You need to create a
catalog called web using the IS MMC (go to a command prompt) and type
ciadv.msc to administer it. To create the catalog in the IS MMC go to
action, new and catalog.
Then when your catalog is created right click on it and select properties
and tracking. In the WWW Server drop down box select your web server.
It also looks like you are indexing your NNTP server. Is this true?
Hilary Cotter
Looking for a SQL Server replication book?
Now available for purchase at:
http://www.nwsu.com/0974973602.html
"Karl" <kwiggins@.randallpub.com.(nospam)> wrote in message
news:BD830BC0-F393-4FBE-A1DC-EB92E2C0CE83@.microsoft.com...
> My company has a web site that we bought several years ago, and we have
had
> to replace the machines that the site runs on. I have inherited the DBA
role
> for this system recently. The site search engine has not been working for
> some time, and after digging through the asp code, I found this query:
> sSql="SELECT a.article_id,srch.size,srch.rank "&_
> "FROM OPENQUERY(FileSystem,"&_
> "'SELECT FileName, Size,rank "&_
> "FROM SCOPE('' """ & FormScope & """ '') "&_
> "WHERE CONTAINS(Contents,''" & sSearch & "'') order by rank desc,write
> desc') srch,articles a"
> if NOT isCMS Then
> sSql=sSql & ",columns c"
> End If
> sSql=sSql & " where cast(a.article_id as
varchar(15))+'.htm'=srch.filename"
> if NOT isCMS Then sSql=sSql & " AND a.status_id in (4,5) AND
> c.column_id=a.column_id
> So, after some searching, I discovered that this was using the Full-Text
> Search function in SQL Server to search through the content news articles
> under the wwwroot folder on the web server. What I'm having a hard time
> doing now is creating a linked server in SQL Server for the folder on the
web
> server that actually seems to work. I also haven't found enough
> documentation on what needs to be set up on both servers for this to work.
> What I have done so far may be incorrect, but I have set up a linked
server
> like so:
> sp_addlinkedserver @.server = 'FileSystem'
> , @.srvproduct = 'Indexing Service'
> , [ @.provider = 'MSIDXS'
> , [ @.datasrc = 'FormScope'
> , [ @.location = '\\devserver\wwwroot\content\production\'
> , [ @.catalog = 'Web'
> I have created a Full-Text catalog on the SQL Server called 'Web' and a
> virtual directory on the IIS server called 'Web' that points to the
devserver
> folder above. When I run the search from the web site, I get this error:
> An error occurred while processing your query: [OLE/DB provider returned
> message: There is no catalog. ]
> So I'm guessing that the catalog has not been populated with the content
> from the web server, but I have no idea how to do that, or even if I'm
> missing one or more steps. I'm scouring the documentation I can find but
no
> luck so far. Any ideas?
> --
> Karl Wiggins
|||Well FTS still has to be set up on the SQL Server doesn't it?
No, we aren't using NNTP, we are indexing articles in our proprietary
content-management system.
"Hilary Cotter" wrote:

> This is not SQL FTS. This is using Indexing Services. You need to create a
> catalog called web using the IS MMC (go to a command prompt) and type
> ciadv.msc to administer it. To create the catalog in the IS MMC go to
> action, new and catalog.
> Then when your catalog is created right click on it and select properties
> and tracking. In the WWW Server drop down box select your web server.
> It also looks like you are indexing your NNTP server. Is this true?
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> Now available for purchase at:
> http://www.nwsu.com/0974973602.html
>
> "Karl" <kwiggins@.randallpub.com.(nospam)> wrote in message
> news:BD830BC0-F393-4FBE-A1DC-EB92E2C0CE83@.microsoft.com...
> had
> role
> varchar(15))+'.htm'=srch.filename"
> web
> server
> devserver
> no
>
>
|||No you don't need SQL FTS - at least it doesn't appear you need it with the
query you have supplied.
If you don't believe me try creating a catalog called web using Ciadv.msc
and see if you still get the no catalog error.
"Karl" <kwiggins@.randallpub.com.(nospam)> wrote in message
news:F2B03DDE-C4EC-4BFF-A27C-DCC50933BA4C@.microsoft.com...[vbcol=seagreen]
> Well FTS still has to be set up on the SQL Server doesn't it?
> No, we aren't using NNTP, we are indexing articles in our proprietary
> content-management system.
> "Hilary Cotter" wrote:
|||Karl,
To clarify a couple of things... First of all, this is SQL Server T-SQL
code, just not the specific T-SQL CONTAINS implementation. The key
difference can be found in the "FROM SCOPE" spec used with OPENQUERY &
FileSystem. Normal T-SQL CONTAINS is use in a WHERE clause, such as the
below:
select * from pubs_info WHERE CONTAINS(*,'"books"')
The key error "[OLE/DB provider returned message: There is no catalog. ]"
indicates an incorrectly configured or missing Indexing Service Catalog in
the sp_addlinkedserver definition.
Regards,
John
"Karl" <kwiggins@.randallpub.com.(nospam)> wrote in message
news:F2B03DDE-C4EC-4BFF-A27C-DCC50933BA4C@.microsoft.com...[vbcol=seagreen]
> Well FTS still has to be set up on the SQL Server doesn't it?
> No, we aren't using NNTP, we are indexing articles in our proprietary
> content-management system.
> "Hilary Cotter" wrote:
a[vbcol=seagreen]
properties[vbcol=seagreen]
have[vbcol=seagreen]
DBA[vbcol=seagreen]
for[vbcol=seagreen]
Full-Text[vbcol=seagreen]
articles[vbcol=seagreen]
time[vbcol=seagreen]
the[vbcol=seagreen]
work.[vbcol=seagreen]
a[vbcol=seagreen]
error:[vbcol=seagreen]
returned[vbcol=seagreen]
content[vbcol=seagreen]
but[vbcol=seagreen]
|||Okay, I'm getting this error now:
An error occurred while processing your query: [OLE/DB provider returned
message: Multiple-step OLE DB operation generated errors. Check each OLE DB
status value, if available. No work was done.]
I set up a Indexing Services catalog called FormScope on the web server that
points to the content folder. My concern is that I don't have the linked
server on the SQL Server side set up correctly. My T-SQL code to add the
linked server looks like this:
sp_addlinkedserver @.server = 'FileSystem'
, @.srvproduct = 'Indexing Service'
, @.provider = 'MSIDXS'
, @.location = '\\DevApps03\devwwwroot\content\siteproduction\'
, @.catalog = 'FormScope'
Is this a valid location? What am I missing here?
"John Kane" wrote:

> Karl,
> To clarify a couple of things... First of all, this is SQL Server T-SQL
> code, just not the specific T-SQL CONTAINS implementation. The key
> difference can be found in the "FROM SCOPE" spec used with OPENQUERY &
> FileSystem. Normal T-SQL CONTAINS is use in a WHERE clause, such as the
> below:
> select * from pubs_info WHERE CONTAINS(*,'"books"')
> The key error "[OLE/DB provider returned message: There is no catalog. ]"
> indicates an incorrectly configured or missing Indexing Service Catalog in
> the sp_addlinkedserver definition.
> Regards,
> John
>
> "Karl" <kwiggins@.randallpub.com.(nospam)> wrote in message
> news:F2B03DDE-C4EC-4BFF-A27C-DCC50933BA4C@.microsoft.com...
> a
> properties
> have
> DBA
> for
> Full-Text
> articles
> time
> the
> work.
> a
> error:
> returned
> content
> but
>
>
|||Karl,
I think that the location spec in your sp_addlinkedserver is either
incorrect (UNC or machine_name) or not necessary when used with Indexing
Service (MSIDXS) provider. While I don't have a remote server to test this
type of configuration, I've used the following sp_addlinkedserver to define
a Linked Server to my Indexing Service Catalog on my primary server:
EXEC sp_addlinkedserver
@.server = 'lsIndexServer', -- Name
@.srvproduct = 'Index Server', -- product name of the OLE DB data source
@.provider = 'MSIDXS', -- Indexing Servics (IS) OLE DB Provider
@.datasrc = 'IS_DDrive' -- IS Catalog
go
Note, if this a Remote Indexing Service, i.e., an Indexing Service Catalog
that is not on the same server as your SQL Server, you can access it via the
following SCOPE Syntax:
Remote_Serve_rName.Remote_Server_IS_Catalogs_Name. .SCOPE(), for example:
SELECT *
FROM OPENQUERY( lsIndexServer,
'SELECT Path, Filename FROM JTKWin2003.IS_DDrive..SCOPE()
WHERE CONTAINS( ''john'' )
AND CONTAINS( ''kane'' )' ) AS jtk
Note, JTKWin2003 is my primary server name.
Thanks,
John
"Karl" <kwiggins@.randallpub.com.(nospam)> wrote in message
news:F093E09E-D972-4B4E-9503-38B0DC198EE2@.microsoft.com...
> Okay, I'm getting this error now:
> An error occurred while processing your query: [OLE/DB provider returned
> message: Multiple-step OLE DB operation generated errors. Check each OLE
DB
> status value, if available. No work was done.]
> I set up a Indexing Services catalog called FormScope on the web server
that[vbcol=seagreen]
> points to the content folder. My concern is that I don't have the linked
> server on the SQL Server side set up correctly. My T-SQL code to add the
> linked server looks like this:
> sp_addlinkedserver @.server = 'FileSystem'
> , @.srvproduct = 'Indexing Service'
> , @.provider = 'MSIDXS'
> , @.location = '\\DevApps03\devwwwroot\content\siteproduction\'
> , @.catalog = 'FormScope'
> Is this a valid location? What am I missing here?
> "John Kane" wrote:
catalog. ]"[vbcol=seagreen]
in[vbcol=seagreen]
create[vbcol=seagreen]
type[vbcol=seagreen]
to[vbcol=seagreen]
server.[vbcol=seagreen]
the[vbcol=seagreen]
working[vbcol=seagreen]
query:[vbcol=seagreen]
desc,write[vbcol=seagreen]
hard[vbcol=seagreen]
on[vbcol=seagreen]
to[vbcol=seagreen]
linked[vbcol=seagreen]
and[vbcol=seagreen]
the[vbcol=seagreen]
I'm[vbcol=seagreen]
find[vbcol=seagreen]

Full and Diff Backups

If I run a full Bkp when the Log file is 10 Gig, and the backup runs for 1 hour while another 5 Gig is added in transactions to the log file, The Backup should complete leaving only the new 5 Gig of transactions while the previous 10 Gig were applied to t
he backup. Basically backing up everything upto and including the Backup statement.
Does the same action take place on the transaction log during a differential backup? Does a differential backup include every transaction on the log file upto and including the backup statement?
>> Basically backing up everything upto and including the Backup statement.
Not exactly. From Inside SQL Server 2000:
'To maintain consistency for either a full or a differential backup, SQL
Server records the current log sequence number (LSN) at the time the backup
starts and then again at the time the backup ends. This allows the backup to
also capture the relevant parts of the log. The relevant part starts with
the oldest open transaction at the time of the first recorded LSN and ends
with the second recorded LSN.'
Thus, the 5 Gig of 'new' trxs will also be present in the log, but will only
be applied upon restore if they were committed at the time the backup ended,
assuming you restore with the RECOVERY option.
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backups? Try MiniSQLBackup
"Brian" <Brian@.discussions.microsoft.com> wrote in message
news:2A52A0B3-23D5-4771-8170-3BE8CFF8330C@.microsoft.com...
> If I run a full Bkp when the Log file is 10 Gig, and the backup runs for 1
hour while another 5 Gig is added in transactions to the log file, The
Backup should complete leaving only the new 5 Gig of transactions while the
previous 10 Gig were applied to the backup. Basically backing up everything
upto and including the Backup statement.
> Does the same action take place on the transaction log during a
differential backup? Does a differential backup include every transaction on
the log file upto and including the backup statement?
>

Full and Diff Backups

If I run a full Bkp when the Log file is 10 Gig, and the backup runs for 1 h
our while another 5 Gig is added in transactions to the log file, The Backup
should complete leaving only the new 5 Gig of transactions while the previo
us 10 Gig were applied to t
he backup. Basically backing up everything upto and including the Backup sta
tement.
Does the same action take place on the transaction log during a differential
backup? Does a differential backup include every transaction on the log fil
e upto and including the backup statement?>> Basically backing up everything upto and including the Backup statement.
Not exactly. From Inside SQL Server 2000:
'To maintain consistency for either a full or a differential backup, SQL
Server records the current log sequence number (LSN) at the time the backup
starts and then again at the time the backup ends. This allows the backup to
also capture the relevant parts of the log. The relevant part starts with
the oldest open transaction at the time of the first recorded LSN and ends
with the second recorded LSN.'
Thus, the 5 Gig of 'new' trxs will also be present in the log, but will only
be applied upon restore if they were committed at the time the backup ended,
assuming you restore with the RECOVERY option.
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backups? Try MiniSQLBackup
"Brian" <Brian@.discussions.microsoft.com> wrote in message
news:2A52A0B3-23D5-4771-8170-3BE8CFF8330C@.microsoft.com...
> If I run a full Bkp when the Log file is 10 Gig, and the backup runs for 1
hour while another 5 Gig is added in transactions to the log file, The
Backup should complete leaving only the new 5 Gig of transactions while the
previous 10 Gig were applied to the backup. Basically backing up everything
upto and including the Backup statement.
> Does the same action take place on the transaction log during a
differential backup? Does a differential backup include every transaction on
the log file upto and including the backup statement?
>

full and diff backup - sql2005

hello...
I've got problem with backups on sql2005
script make backup at saturday with full backup and at monday script
runs diff backup. On one of few sqlservers2005 diff backups doesn't
run because because error: "a current database backup does not
exist" . But I know full backup was executed corectly on saturday.
In backup file , that is used for diff backup exists record for
saturday's full backup. But server can't recognize full backup for
db.
When I run full backup again, it run sucessfully and diff backup
too.
This error is only on one server (named instance), other servers are
ok.
Any hints for this mystery ?
Thanks for any help.Well, try doing a manual full and see if it resolves.
I'm not sure if the backup is looking at logs in msdb, or if it writes a
flag, but a manual full will deal with both. In short, I wouldn't put too
much energy into it, unless the simple solution doesn't work ...
<oto.pakozdy@.gmail.com> wrote in message
news:2398bfcd-fa45-4a4f-ade4-915159aebbea@.d21g2000prf.googlegroups.com...
> hello...
> I've got problem with backups on sql2005
> script make backup at saturday with full backup and at monday script
> runs diff backup. On one of few sqlservers2005 diff backups doesn't
> run because because error: "a current database backup does not
> exist" . But I know full backup was executed corectly on saturday.
> In backup file , that is used for diff backup exists record for
> saturday's full backup. But server can't recognize full backup for
> db.
> When I run full backup again, it run sucessfully and diff backup
> too.
> This error is only on one server (named instance), other servers are
> ok.
> Any hints for this mystery ?
> Thanks for any help.|||On 27. Nov, 16:57 h., "Jay" <s...@.nospam.org> wrote:
> Well, try doing a manual full and see if it resolves.
> I'm not sure if the backup is looking at logs in msdb, or if it writes a
> flag, but a manual full will deal with both. In short, I wouldn't put too
> much energy into it, unless the simple solution doesn't work ...
> <oto.pako...@.gmail.com> wrote in message
> news:2398bfcd-fa45-4a4f-ade4-915159aebbea@.d21g2000prf.googlegroups.com...
>
> > hello...
> > I've got problem with backups on sql2005
> > script make backup at saturday with full backup and at monday script
> > runs diff backup. On one of few sqlservers2005 diff backups doesn't
> > run because because error: "a current database backup does not
> > exist" . But I know full backup was executed corectly on saturday.
> > In backup file , that is used for diff backup exists record for
> > saturday's full backup. But server can't recognize full backup for
> > db.
> > When I run full backup again, it run sucessfully and diff backup
> > too.
> > This error is only on one server (named instance), other servers are
> > ok.
> > Any hints for this mystery ?
> > Thanks for any help.- Skry=BB citovan=FD text -
> - Zobrazi=BB citovan=FD text -
Now I altered db_backupoperator role for MSDB by user running backup
script. soi I will see if it help.|||Perhaps you do some type of snapshot backup on the machine and this interferes in some way with the
diff backups? Consider disabling the SQL Server VSS writer service in that case. Just a thought...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
<oto.pakozdy@.gmail.com> wrote in message
news:2398bfcd-fa45-4a4f-ade4-915159aebbea@.d21g2000prf.googlegroups.com...
> hello...
> I've got problem with backups on sql2005
> script make backup at saturday with full backup and at monday script
> runs diff backup. On one of few sqlservers2005 diff backups doesn't
> run because because error: "a current database backup does not
> exist" . But I know full backup was executed corectly on saturday.
> In backup file , that is used for diff backup exists record for
> saturday's full backup. But server can't recognize full backup for
> db.
> When I run full backup again, it run sucessfully and diff backup
> too.
> This error is only on one server (named instance), other servers are
> ok.
> Any hints for this mystery ?
> Thanks for any help.|||I usually run this to get the history of backups for a specific database.
Try it and see what information it returns. Chage database name and pay
atention to the type column.
select backup_set_id, bs.media_set_id, database_creation_date,
backup_start_date, backup_finish_date,
type, database_name, physical_device_name
from backupset bs join backupmediafamily bmf
on bs.media_set_id = bmf.media_set_id
where database_name = 'your_db'
-- and type <> 'L'
order by backup_start_date desc
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"oto.pakozdy@.gmail.com" wrote:
> hello...
> I've got problem with backups on sql2005
> script make backup at saturday with full backup and at monday script
> runs diff backup. On one of few sqlservers2005 diff backups doesn't
> run because because error: "a current database backup does not
> exist" . But I know full backup was executed corectly on saturday.
> In backup file , that is used for diff backup exists record for
> saturday's full backup. But server can't recognize full backup for
> db.
> When I run full backup again, it run sucessfully and diff backup
> too.
> This error is only on one server (named instance), other servers are
> ok.
> Any hints for this mystery ?
> Thanks for any help.
>|||I found solution on google
Problem is MSSQL SP2 changed startup state SQL Server VSS Writer from
manual to started . So when NTbackup run backup it breaks backup
chain because vss make own db backups.
So when I turn off SQL VSS Writer everyting works fine.
ntbackup backup files without backuping db and sql backup chain
full,diff is not break.
Thank for your hints.|||Hello, I think i meet the same problem. but you said you have find the
resolution on google. Could you please give me the link that describe the
problem?
Thanks,
Gil
"oto.pakozdy@.gmail.com" wrote:
> I found solution on google
> Problem is MSSQL SP2 changed startup state SQL Server VSS Writer from
> manual to started . So when NTbackup run backup it breaks backup
> chain because vss make own db backups.
> So when I turn off SQL VSS Writer everyting works fine.
> ntbackup backup files without backuping db and sql backup chain
> full,diff is not break.
>
> Thank for your hints.
>