full text searching BLOBS

Hi all,
I have an issue with full text indexing of some BLOB data (image data
type). We recently moved a SQL 2000 database to a new server with SQL
2005 installed. Everything thing went very smooth. We have full text
indexing enabled on several columns over several tables. We have a
table for attachments that are stored as BLOBs that is also indexed.
Since the migration, all of the full text searches work as before with
the exception of the binary data. We used to be able to search inside
Word, Excel, and other office documents. Now we cannot.
I believe I have located the source of the problem, but I am not sure
how to resolve. Anyone who has used this feature knows that when you
index a BLOB (image data type) you have to select a second column that
tells SQL what filetype is being stored. My column for this is called
ATTACHMENT_CONTENTTYPE. SQL compares this with a list of iFilters it
has to determine which filter it should use when searching the binary
data for text.
Here is an example of what is contained in my ATTACHMENT_CONTENTTYPE
column:
image/tiff
application/x-zip-compressed
text/richtext
image/bmp
audio/wav
application/msword
video/mpeg
video/avi
image/x-bmp
image/vnd.ms-modi
image/gif
video/mpg
application/octet-stream
application/vnd.ms-excel
All of the sql documentation I am reading is telling me that my column
should contain file extensions like:
..doc
..xls
..ppt
Here is my dilemma. This all worked perfectly with SQL 2000. It is a
big deal for me to change all of the data in this column as well as
the application that populates it. Am I not understanding how this
works, or is there some way I can get SQL 2005 to recognize the way my
column is describing the data type?
Thank you!
On Mar 12, 11:25Xam, markman...@.gmail.com wrote:
> Hi all,
> I have an issue with full text indexing of someBLOBdata (image data
> type). We recently moved aSQL2000 database to a new server withSQL2005installed. Everything thing went very smooth. We have full text
> indexing enabled on several columns over several tables. We have a
> table for attachments that are stored as BLOBs that is also indexed.
> Since the migration, all of the full text searches work as before with
> the exception of the binary data. We used to be able to search inside
> Word, Excel, and other office documents. Now we cannot.
> I believe I have located the source of the problem, but I am not sure
> how to resolve. Anyone who has used this feature knows that when you
> index aBLOB(image data type) you have to select a second column that
> tellsSQLwhat filetype is being stored. My column for this is called
> ATTACHMENT_CONTENTTYPE.SQLcompares this with a list of iFilters it
> has to determine which filter it should use when searching the binary
> data for text.
> Here is an example of what is contained in my ATTACHMENT_CONTENTTYPE
> column:
> image/tiff
> application/x-zip-compressed
> text/richtext
> image/bmp
> audio/wav
> application/msword
> video/mpeg
> video/avi
> image/x-bmp
> image/vnd.ms-modi
> image/gif
> video/mpg
> application/octet-stream
> application/vnd.ms-excel
> All of thesqldocumentation I am reading is telling me that my column
> should contain file extensions like:
> .doc
> .xls
> .ppt
> Here is my dilemma. This all worked perfectly withSQL2000. It is a
> big deal for me to change all of the data in this column as well as
> the application that populates it. Am I not understanding how this
> works, or is there some way I can getSQL2005to recognize the way my
> column is describing the data type?
> Thank you!
Also, I have no errors in the log files that I can see. Thanks all.

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.
>

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...
>
>
>
>
>
>
> - 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 interfer
es in some way with the
diff backups? Consider disabling the SQL Server VSS writer service in that c
ase. 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.
>

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...
>
>
>
>
> - Zobrazi citovan text -
Now I altered db_backupoperator role for MSDB by user running backup
script. soi I will see if it 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.
>

FTS in SQL2005 and JOINS

Hi, I'm having a slight problem with some of my FTS queries, namely it
seems that in SQL 2005 the execution plans are constructed by first
doing the normal query and then joining with the FTS results.
For instance:
SELECT P.id,P.name
FROM CONTAINSTABLE(prd_Names,phrase,'"hp*"') F
INNER JOIN prd_Names P ON F.[key]=P.id
ORDER BY P.name,F.rank;
Causes a massive index scan on prd_Names table first and only then
joins with the smaller set returned by CONTAINSTABLE. IIRC in 2000 I
didn't have this problem (I think it was the other way around).
Is there any way to make the query behave properly?
The DTD is very simple prd_Names is a table with 3 columns
id (pkey) int
name varchar(100)
phrase varchar(500)
a clustered index on id
and an index on the name column.
BTW the table contains ~400k rows.
Thanks.
I've no idea whether this would work, but have you tried to re-order the
query so that the inner join is the other way around?
Griff
|||Griff wrote:
> I've no idea whether this would work, but have you tried to re-order the
> query so that the inner join is the other way around?
Yep, tried that with no effect. I also rewrote it without using the
JOIN statement: WHERE CONTAINS(..) etc. and also WHERE P.id IN (SELECT
... FROM CONTAINSTABLE()) but neither helped.
Thanks.
|||I think I've missed something - why are you doing an inner join in the first
place?
|||Griff wrote:
> I think I've missed something - why are you doing an inner join in the first
> place?
Because CONTAINSTABLE returns a table with [key],[rank] columns which I
need (afaik it's the normal way to use CONTAINSTABLE)