I have a full text indexed xml column in sql server 2005 containing the
following xml snippet:
<plays>
<folder id="681">
<page id="3155">
<submitdate>12-10-02</submitdate>
<pagetext>Horatio says 'tis but our fantasy,</pagetext>
</page>
<page id="9267">
<submitdate>09-04-04</submitdate>
<pagetext>And will not let belief take hold of him</pagetext>
</page>
</folder>
<folder id="902">
<page id="1853">
<submitdate>22-11-05</submitdate>
<pagetext>Touching this dreaded sight, twice seen of us:</pagetext>
</page>
<page id="8423">
<submitdate>31-05-02</submitdate>
<pagetext>Therefore I have entreated him along</pagetext>
</page>
</folder>
</plays>
I'd like to be able to search this document for keywords, such as
"belief" to return the value '@.id=9267' i.e. return the id value of the
containing node ('page') where the string "belief" was found.
Likewise, if the search was for "him" then '@.id=9267' and '@.id=8423'
would be returned.
Could anyone give me pointers as to how to implement this in XQuery /
Full-text?
Many thanks, David
Here is a possible implementation:
begin
declare @.x xml
set @.x='
<plays>
<folder id="681">
<page id="3155">
<submitdate>12-10-02</submitdate>
<pagetext>Horatio says ''tis but our fantasy,</pagetext>
</page>
<page id="9267">
<submitdate>09-04-04</submitdate>
<pagetext>And will not let belief take hold of him</pagetext>
</page>
</folder>
<folder id="902">
<page id="1853">
<submitdate>22-11-05</submitdate>
<pagetext>Touching this dreaded sight, twice seen of us:</pagetext>
</page>
<page id="8423">
<submitdate>31-05-02</submitdate>
<pagetext>Therefore I have entreated him along</pagetext>
</page>
</folder>
</plays>
'
declare @.x2 xml
set @.x2 = (SELECT T.c.query('.') AS risultato FROM
@.x.nodes('/plays/folder/page[@.id]') T(c) where T.c.value('.','varchar(50)')
like '%Hor%')
select @.x2.value('(/page/@.id)[1]','int')
end
OK?
"firechaser@.talk21.com" wrote:
> I have a full text indexed xml column in sql server 2005 containing the
> following xml snippet:
> <plays>
> <folder id="681">
> <page id="3155">
> <submitdate>12-10-02</submitdate>
> <pagetext>Horatio says 'tis but our fantasy,</pagetext>
> </page>
> <page id="9267">
> <submitdate>09-04-04</submitdate>
> <pagetext>And will not let belief take hold of him</pagetext>
> </page>
> </folder>
> <folder id="902">
> <page id="1853">
> <submitdate>22-11-05</submitdate>
> <pagetext>Touching this dreaded sight, twice seen of us:</pagetext>
> </page>
> <page id="8423">
> <submitdate>31-05-02</submitdate>
> <pagetext>Therefore I have entreated him along</pagetext>
> </page>
> </folder>
> </plays>
>
> I'd like to be able to search this document for keywords, such as
> "belief" to return the value '@.id=9267' i.e. return the id value of the
> containing node ('page') where the string "belief" was found.
> Likewise, if the search was for "him" then '@.id=9267' and '@.id=8423'
> would be returned.
> Could anyone give me pointers as to how to implement this in XQuery /
> Full-text?
> Many thanks, David
>
|||Thanks very much for your time Luca.
Unfortunately I was hoping to use the full-text capabilities in SQL
Server 2005 to produce *multiple* search results from any single xml
snippet stored in a single (and known in advance) row.
All the FTS examples I've seen are of the type
select xmlcol from t1
where contains(xmlcol, '"dog cat"')
which return the whole xml record. Is there a way to accomplish this on
a single record as per xml snippet in my original post which exists as
an xml document in a single row in the table?
I will know *in advance* which record in the table needs to be
queried, so I need to be able to query the actual content of the xml
cell and return 0,1 or more matching results based upon that xml
snippet and *not* based upon matches spanning several rows.
Looking at the above snippet, I need to be able to search for the word
"him" which would give me '@.id=9267' and '@.id=8423' returned.
I'm not looking to search across rows - that appears straightforward -
I'm trying to perform searches *within* the xml cell that exits in a
single (and at the moment, unique) record in my table.
I'd be very grateful for any help in trying to explain this, as I'm at
a loss at the moment.
Thanks again, David
|||try this
create database FullTextXML
use FullTextXML
GO
drop table XMLFULLText
drop XML SCHEMA COLLECTION PlaysSchema
CREATE XML SCHEMA COLLECTION PlaysSchema AS '
<xsd:schema targetNamespace="http://www.plays.com/plays"
xmlns ="http://www.plays.com/plays"
elementFormDefault="qualified"
attributeFormDefault="unqualified"
xmlns:xsd="http://www.w3.org/2001/XMLSchema" >
<xsd:element name="plays"><xsd:complexType><xsd:complexContent>
<xsd:restriction base="xsd:anyType"><xsd:choice
maxOccurs="unbounded"><xsd:element name="folder">
<xsd:complexType><xsd:complexContent><xsd:restrict ion
base="xsd:anyType"><xsd:sequence>
<xsd:element name="page" minOccurs="0"
maxOccurs="unbounded"><xsd:complexType>
<xsd:complexContent><xsd:restriction base="xsd:anyType"><xsd:sequence>
<xsd:element name="submitdate" type="xsd:string" minOccurs="0" />
<xsd:element name="pagetext" type="xsd:string" minOccurs="0" />
</xsd:sequence><xsd:attribute name="id" type="xsd:string"
/></xsd:restriction></xsd:complexContent>
</xsd:complexType></xsd:element></xsd:sequence><xsd:attribute name="id"
type="xsd:string" />
</xsd:restriction></xsd:complexContent></xsd:complexType></xsd:element></xsd:choice></xsd:restriction>
</xsd:complexContent></xsd:complexType></xsd:element></xsd:schema>'
GO
Create Table XMLFULLText (pk int not null identity constraint XMLFULLTEXTPK
primary key, XMLDOC XML(PlaysSchema))
GO
declare @.XMLDOC XML
set @.XMLDOC ='<plays xmlns="http://www.plays.com/plays"><folder id="681">
<page id="3155">
<submitdate>12-10-02</submitdate>
<pagetext>Horatio says ''tis but our
fantasy,</pagetext>
</page>
<page id="9267">
<submitdate>09-04-04</submitdate>
<pagetext>And will not let belief take hold of
him</pagetext>
</page>
</folder>
<folder id="902">
<page id="1853">
<submitdate>22-11-05</submitdate>
<pagetext>Touching this dreaded sight, twice seen of
us:</pagetext>
</page>
<page id="8423">
<submitdate>31-05-02</submitdate>
<pagetext>Therefore I have entreated him
along</pagetext>
</page>
</folder></plays>'
insert into XMLFULLText (XMLDOC) values(@.XMLDOC)
GO
sp_fulltext_database 'enable'
GO
create fulltext catalog test as default
GO
create fulltext index on XMLFULLText (XMLDOC) key index XMLFULLTextPK
GO
select * from XMLFULLText where contains(*,'belief')
GO
--1 row returned. OK, lets make this a little more interesting
GO
declare @.XMLDOC XML
set @.XMLDOC ='<plays xmlns="http://www.plays.com/plays"><folder id="681">
<page id="3155">
<submitdate>12-10-02</submitdate>
<pagetext>Horatio says ''tis but our
fantasy,</pagetext>
</page>
<page id="9267">
<submitdate>09-04-04</submitdate>
<pagetext>And will not let beliefs take hold of
him</pagetext>
</page>
</folder>
<folder id="902">
<page id="1853">
<submitdate>22-11-05</submitdate>
<pagetext>Touching this dreaded sight, twice seen of
us:</pagetext>
</page>
<page id="8423">
<submitdate>31-05-02</submitdate>
<pagetext>Therefore I have entreated him
along</pagetext>
</page>
</folder>
</plays>'
insert into XMLFULLText (XMLDOC) values(@.XMLDOC)
GO
select * from XMLFULLText where contains(*,'belief')
GO
--1 row returned.
select * from XMLFULLText where FREETEXT(*,'belief')
GO
--2 row returned. OK, lets restrict this to contents coming from an element
declare @.XMLDOC XML
set @.XMLDOC ='<plays xmlns="http://www.plays.com/plays"><folder id="681">
<page id="3155">
<submitdate>belief</submitdate>
<pagetext>Horatio says ''tis but our
fantasy,</pagetext>
</page>
<page id="9267">
<submitdate>09-04-04</submitdate>
<pagetext>And will not let take hold of
him</pagetext>
</page>
</folder>
<folder id="902">
<page id="1853">
<submitdate>22-11-05</submitdate>
<pagetext>Touching this dreaded sight, twice seen of
us:</pagetext>
</page>
<page id="8423">
<submitdate>31-05-02</submitdate>
<pagetext>Therefore I have entreated him
along</pagetext>
</page>
</folder>
</plays>'
insert into XMLFULLText (XMLDOC) values(@.XMLDOC)
GO
select * from XMLFULLText where contains(*,'belief')
GO
--2 rows returned, one in page text and one in submit date
select * from XMLFULLText where FREETEXT(*,'belief')
GO
--2 rows returned. OK, lets restrict this to contents coming from an element
select * from XMLFulltext where contains(*,'belief')
WITH XMLNAMESPACES ('http://www.plays.com/plays' AS pd)
SELECT pk, XMLDOC
FROM XMLFulltext
where contains(XMLDOC, 'belief')
and
xmldoc.exist('/plays/folder/page/pagetext/text()[contains(.,"belief")]')=1
--notice we don't get a hit from 3 where belief is in the submitdate column
WITH XMLNAMESPACES ('http://www.plays.com/plays' AS pd)
SELECT pk, XMLDOC.query('
-- <pd:plays>
--{/pd:folder/pd:page/pd:pagetext}
-- </pd:plays>
--') AS Result
FROM XMLFulltext
where contains(XMLDOC, 'belief')
and
xmldoc.exist('/pd:plays/pd:folder/pd:page/pd:pagetext[1]/text()[contains(.,"belief")]')=1
--notice we don't get a hit from 3 where belief is in the submitdate column
WITH XMLNAMESPACES ('http://www.plays.com/plays' AS pd)
WITH XMLNAMESPACES
('http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription'
AS pd)
SELECT CatalogDescription.query('
<pd:Product ProductModelID="{
(/pd:ProductDescription/@.ProductModelID)[1] }">
<Picture>
{ /pd:ProductDescription/pd:Picture/pd:Angle }
{ /pd:ProductDescription/pd:Picture/pd:Size }
</Picture>
</pd:Product>
') as Result
FROM Production.ProductModel
WHERE CatalogDescription.exist('/pd:ProductDescription/pd:Picture') = 1
AND
CatalogDescription.value('(/pd:ProductDescription/pd:Picture/pd:Angle)[1]',
'varchar(20)') = 'front'
AND
CatalogDescription.value('(/pd:ProductDescription/pd:Picture/pd:Size)[1]',
'varchar(20)') = 'small'
WITH XMLNAMESPACES ('http://www.plays.com/plays' AS pd)
SELECT pk,*
FROM XMLFulltext
where contains(XMLDOC, 'belief')
and
xmldoc.exist('/pd:plays[1]/pd:folder[1]/pd:page[1]/pd:submitdate[contains(.,"belief")]')=1
WITH XMLNAMESPACES ('http://www.plays.com/plays' AS pd)
SELECT pk,*
FROM XMLFulltext
where contains(XMLDOC, 'belief')
and
xmldoc.exist('/pd:plays[1]/pd:folder[1]/pd:page[2]/pd:pagetext[contains(.,"belief")]')=1
WITH XMLNAMESPACES ('http://www.plays.com/plays' AS pd)
SELECT pk,*
FROM XMLFulltext
where contains(XMLDOC, 'belief')
and
xmldoc.exist('/pd:plays[1]/pd:folder[1]/pd:page/pd:pagetext[contains(.,"belief")]')=1
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
<firechaser@.talk21.com> wrote in message
news:1139433546.490894.304730@.g47g2000cwa.googlegr oups.com...
> Thanks very much for your time Luca.
> Unfortunately I was hoping to use the full-text capabilities in SQL
> Server 2005 to produce *multiple* search results from any single xml
> snippet stored in a single (and known in advance) row.
> All the FTS examples I've seen are of the type
> select xmlcol from t1
> where contains(xmlcol, '"dog cat"')
> which return the whole xml record. Is there a way to accomplish this on
> a single record as per xml snippet in my original post which exists as
> an xml document in a single row in the table?
> I will know *in advance* which record in the table needs to be
> queried, so I need to be able to query the actual content of the xml
> cell and return 0,1 or more matching results based upon that xml
> snippet and *not* based upon matches spanning several rows.
> Looking at the above snippet, I need to be able to search for the word
> "him" which would give me '@.id=9267' and '@.id=8423' returned.
> I'm not looking to search across rows - that appears straightforward -
> I'm trying to perform searches *within* the xml cell that exits in a
> single (and at the moment, unique) record in my table.
> I'd be very grateful for any help in trying to explain this, as I'm at
> a loss at the moment.
> Thanks again, David
>
|||Even with Hilary's workaround there is no good full-text search support at
the sub XML datatype level yet. Mainly because the XQuery language is just
now working on a full-text extension to XQuery.
Can I please ask you to file a feature request at
http://lab.msdn.microsoft.com/productfeedback/
We are certainly looking into adding such functionality and actually
customer use cases are important for us to motivate the investment to our
purse holders.
As a short-term work around, I recommend to split the sections into one per
row (using the nodes() method) and then FT-index that column.
Best regards
Michael
<firechaser@.talk21.com> wrote in message
news:1139433546.490894.304730@.g47g2000cwa.googlegr oups.com...
> Thanks very much for your time Luca.
> Unfortunately I was hoping to use the full-text capabilities in SQL
> Server 2005 to produce *multiple* search results from any single xml
> snippet stored in a single (and known in advance) row.
> All the FTS examples I've seen are of the type
> select xmlcol from t1
> where contains(xmlcol, '"dog cat"')
> which return the whole xml record. Is there a way to accomplish this on
> a single record as per xml snippet in my original post which exists as
> an xml document in a single row in the table?
> I will know *in advance* which record in the table needs to be
> queried, so I need to be able to query the actual content of the xml
> cell and return 0,1 or more matching results based upon that xml
> snippet and *not* based upon matches spanning several rows.
> Looking at the above snippet, I need to be able to search for the word
> "him" which would give me '@.id=9267' and '@.id=8423' returned.
> I'm not looking to search across rows - that appears straightforward -
> I'm trying to perform searches *within* the xml cell that exits in a
> single (and at the moment, unique) record in my table.
> I'd be very grateful for any help in trying to explain this, as I'm at
> a loss at the moment.
> Thanks again, David
>
2012年3月27日星期二
full text search within single xml document
I have a full text indexed xml column in sql server 2005 containing the
following xml snippet:
<plays>
<folder id="681">
<page id="3155">
<submitdate>12-10-02</submitdate>
<pagetext>Horatio says 'tis but our fantasy,</pagetext>
</page>
<page id="9267">
<submitdate>09-04-04</submitdate>
<pagetext>And will not let belief take hold of him</pagetext>
</page>
</folder>
<folder id="902">
<page id="1853">
<submitdate>22-11-05</submitdate>
<pagetext>Touching this dreaded sight, twice seen of us:</pagetext>
</page>
<page id="8423">
<submitdate>31-05-02</submitdate>
<pagetext>Therefore I have entreated him along</pagetext>
</page>
</folder>
</plays>
I'd like to be able to search this document for keywords, such as
"belief" to return the value '@.id=9267' i.e. return the id value of the
containing node ('page') where the string "belief" was found.
Likewise, if the search was for "him" then '@.id=9267' and '@.id=8423'
would be returned.
Could anyone give me pointers as to how to implement this in XQuery /
Full-text?
Many thanks, DavidHere is a possible implementation:
begin
declare @.x xml
set @.x='
<plays>
<folder id="681">
<page id="3155">
<submitdate>12-10-02</submitdate>
<pagetext>Horatio says ''tis but our fantasy,</pagetext>
</page>
<page id="9267">
<submitdate>09-04-04</submitdate>
<pagetext>And will not let belief take hold of him</pagetext>
</page>
</folder>
<folder id="902">
<page id="1853">
<submitdate>22-11-05</submitdate>
<pagetext>Touching this dreaded sight, twice seen of us:</pagetext>
</page>
<page id="8423">
<submitdate>31-05-02</submitdate>
<pagetext>Therefore I have entreated him along</pagetext>
</page>
</folder>
</plays>
'
declare @.x2 xml
set @.x2 = (SELECT T.c.query('.') AS risultato FROM
@.x.nodes('/plays/folder/page[@.id]') T(c) where T.c.value('.','varchar(50)')
like '%Hor%')
select @.x2.value('(/page/@.id)[1]','int')
end
OK?
"firechaser@.talk21.com" wrote:
> I have a full text indexed xml column in sql server 2005 containing the
> following xml snippet:
> <plays>
> <folder id="681">
> <page id="3155">
> <submitdate>12-10-02</submitdate>
> <pagetext>Horatio says 'tis but our fantasy,</pagetext>
> </page>
> <page id="9267">
> <submitdate>09-04-04</submitdate>
> <pagetext>And will not let belief take hold of him</pagetext>
> </page>
> </folder>
> <folder id="902">
> <page id="1853">
> <submitdate>22-11-05</submitdate>
> <pagetext>Touching this dreaded sight, twice seen of us:</pagetext>
> </page>
> <page id="8423">
> <submitdate>31-05-02</submitdate>
> <pagetext>Therefore I have entreated him along</pagetext>
> </page>
> </folder>
> </plays>
>
> I'd like to be able to search this document for keywords, such as
> "belief" to return the value '@.id=9267' i.e. return the id value of the
> containing node ('page') where the string "belief" was found.
> Likewise, if the search was for "him" then '@.id=9267' and '@.id=8423'
> would be returned.
> Could anyone give me pointers as to how to implement this in XQuery /
> Full-text?
> Many thanks, David
>|||Thanks very much for your time Luca.
Unfortunately I was hoping to use the full-text capabilities in SQL
Server 2005 to produce *multiple* search results from any single xml
snippet stored in a single (and known in advance) row.
All the FTS examples I've seen are of the type
select xmlcol from t1
where contains(xmlcol, '"dog cat"')
which return the whole xml record. Is there a way to accomplish this on
a single record as per xml snippet in my original post which exists as
an xml document in a single row in the table?
I will know *in advance* which record in the table needs to be
queried, so I need to be able to query the actual content of the xml
cell and return 0,1 or more matching results based upon that xml
snippet and *not* based upon matches spanning several rows.
Looking at the above snippet, I need to be able to search for the word
"him" which would give me '@.id=9267' and '@.id=8423' returned.
I'm not looking to search across rows - that appears straightforward -
I'm trying to perform searches *within* the xml cell that exits in a
single (and at the moment, unique) record in my table.
I'd be very grateful for any help in trying to explain this, as I'm at
a loss at the moment.
Thanks again, David|||try this
create database FullTextXML
use FullTextXML
GO
drop table XMLFULLText
drop XML SCHEMA COLLECTION PlaysSchema
CREATE XML SCHEMA COLLECTION PlaysSchema AS '
<xsd:schema targetNamespace="http://www.plays.com/plays"
xmlns ="http://www.plays.com/plays"
elementFormDefault="qualified"
attributeFormDefault="unqualified"
xmlns:xsd="http://www.w3.org/2001/XMLSchema" >
<xsd:element name="plays"><xsd:complexType><xsd:complexContent>
<xsd:restriction base="xsd:anyType"><xsd:choice
maxOccurs="unbounded"><xsd:element name="folder">
<xsd:complexType><xsd:complexContent><xsd:restriction
base="xsd:anyType"><xsd:sequence>
<xsd:element name="page" minOccurs="0"
maxOccurs="unbounded"><xsd:complexType>
<xsd:complexContent><xsd:restriction base="xsd:anyType"><xsd:sequence>
<xsd:element name="submitdate" type="xsd:string" minOccurs="0" />
<xsd:element name="pagetext" type="xsd:string" minOccurs="0" />
</xsd:sequence><xsd:attribute name="id" type="xsd:string"
/></xsd:restriction></xsd:complexContent>
</xsd:complexType></xsd:element></xsd:sequence><xsd:attribute name="id"
type="xsd:string" />
</xsd:restriction></xsd:complexContent></xsd:complexType></xsd:element></xsd
:choice></xsd:restriction>
</xsd:complexContent></xsd:complexType></xsd:element></xsd:schema>'
GO
Create Table XMLFULLText (pk int not null identity constraint XMLFULLTEXTPK
primary key, XMLDOC XML(PlaysSchema))
GO
declare @.XMLDOC XML
set @.XMLDOC ='<plays xmlns="http://www.plays.com/plays"><folder id="681">
<page id="3155">
<submitdate>12-10-02</submitdate>
<pagetext>Horatio says ''tis but our
fantasy,</pagetext>
</page>
<page id="9267">
<submitdate>09-04-04</submitdate>
<pagetext>And will not let belief take hold of
him</pagetext>
</page>
</folder>
<folder id="902">
<page id="1853">
<submitdate>22-11-05</submitdate>
<pagetext>Touching this dreaded sight, twice seen of
us:</pagetext>
</page>
<page id="8423">
<submitdate>31-05-02</submitdate>
<pagetext>Therefore I have entreated him
along</pagetext>
</page>
</folder></plays>'
insert into XMLFULLText (XMLDOC) values(@.XMLDOC)
GO
sp_fulltext_database 'enable'
GO
create fulltext catalog test as default
GO
create fulltext index on XMLFULLText (XMLDOC) key index XMLFULLTextPK
GO
select * from XMLFULLText where contains(*,'belief')
GO
--1 row returned. OK, lets make this a little more interesting
GO
declare @.XMLDOC XML
set @.XMLDOC ='<plays xmlns="http://www.plays.com/plays"><folder id="681">
<page id="3155">
<submitdate>12-10-02</submitdate>
<pagetext>Horatio says ''tis but our
fantasy,</pagetext>
</page>
<page id="9267">
<submitdate>09-04-04</submitdate>
<pagetext>And will not let beliefs take hold of
him</pagetext>
</page>
</folder>
<folder id="902">
<page id="1853">
<submitdate>22-11-05</submitdate>
<pagetext>Touching this dreaded sight, twice seen of
us:</pagetext>
</page>
<page id="8423">
<submitdate>31-05-02</submitdate>
<pagetext>Therefore I have entreated him
along</pagetext>
</page>
</folder>
</plays>'
insert into XMLFULLText (XMLDOC) values(@.XMLDOC)
GO
select * from XMLFULLText where contains(*,'belief')
GO
--1 row returned.
select * from XMLFULLText where FREETEXT(*,'belief')
GO
--2 row returned. OK, lets restrict this to contents coming from an element
declare @.XMLDOC XML
set @.XMLDOC ='<plays xmlns="http://www.plays.com/plays"><folder id="681">
<page id="3155">
<submitdate>belief</submitdate>
<pagetext>Horatio says ''tis but our
fantasy,</pagetext>
</page>
<page id="9267">
<submitdate>09-04-04</submitdate>
<pagetext>And will not let take hold of
him</pagetext>
</page>
</folder>
<folder id="902">
<page id="1853">
<submitdate>22-11-05</submitdate>
<pagetext>Touching this dreaded sight, twice seen of
us:</pagetext>
</page>
<page id="8423">
<submitdate>31-05-02</submitdate>
<pagetext>Therefore I have entreated him
along</pagetext>
</page>
</folder>
</plays>'
insert into XMLFULLText (XMLDOC) values(@.XMLDOC)
GO
select * from XMLFULLText where contains(*,'belief')
GO
--2 rows returned, one in page text and one in submit date
select * from XMLFULLText where FREETEXT(*,'belief')
GO
--2 rows returned. OK, lets restrict this to contents coming from an element
select * from XMLFulltext where contains(*,'belief')
WITH XMLNAMESPACES ('http://www.plays.com/plays' AS pd)
SELECT pk, XMLDOC
FROM XMLFulltext
where contains(XMLDOC, 'belief')
and
xmldoc.exist('/plays/folder/page/pagetext/text()[contains(.,"belief")]')=1
--notice we don't get a hit from 3 where belief is in the submitdate column
WITH XMLNAMESPACES ('http://www.plays.com/plays' AS pd)
SELECT pk, XMLDOC.query('
-- <pd:plays>
--{/pd:folder/pd:page/pd:pagetext}
-- </pd:plays>
--') AS Result
FROM XMLFulltext
where contains(XMLDOC, 'belief')
and
xmldoc.exist('/pd:plays/pd:folder/pd:page/pd:pagetext[1]/text()[contains(.,"
belief")]')=1
--notice we don't get a hit from 3 where belief is in the submitdate column
WITH XMLNAMESPACES ('http://www.plays.com/plays' AS pd)
WITH XMLNAMESPACES
('http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductMode
lDescription'
AS pd)
SELECT CatalogDescription.query('
<pd:Product ProductModelID="{
(/pd:ProductDescription/@.ProductModelID)[1] }">
<Picture>
{ /pd:ProductDescription/pd:Picture/pd:Angle }
{ /pd:ProductDescription/pd:Picture/pd:Size }
</Picture>
</pd:Product>
') as Result
FROM Production.ProductModel
WHERE CatalogDescription.exist('/pd:ProductDescription/pd:Picture') = 1
AND
CatalogDescription.value('(/pd:ProductDescription/pd:Picture/pd:Angle)[1]',
'varchar(20)') = 'front'
AND
CatalogDescription.value('(/pd:ProductDescription/pd:Picture/pd:Size)[1]',
'varchar(20)') = 'small'
WITH XMLNAMESPACES ('http://www.plays.com/plays' AS pd)
SELECT pk,*
FROM XMLFulltext
where contains(XMLDOC, 'belief')
and
xmldoc.exist('/pd:plays[1]/pd:folder[1]/pd:page[1]/pd:submitdate[contains(.,
"belief")]')=1
WITH XMLNAMESPACES ('http://www.plays.com/plays' AS pd)
SELECT pk,*
FROM XMLFulltext
where contains(XMLDOC, 'belief')
and
xmldoc.exist('/pd:plays[1]/pd:folder[1]/pd:page[2]/pd:pagetext[contains(.,"b
elief")]')=1
WITH XMLNAMESPACES ('http://www.plays.com/plays' AS pd)
SELECT pk,*
FROM XMLFulltext
where contains(XMLDOC, 'belief')
and
xmldoc.exist('/pd:plays[1]/pd:folder[1]/pd:page/pd:pagetext[contains(.,"beli
ef")]')=1
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
<firechaser@.talk21.com> wrote in message
news:1139433546.490894.304730@.g47g2000cwa.googlegroups.com...
> Thanks very much for your time Luca.
> Unfortunately I was hoping to use the full-text capabilities in SQL
> Server 2005 to produce *multiple* search results from any single xml
> snippet stored in a single (and known in advance) row.
> All the FTS examples I've seen are of the type
> select xmlcol from t1
> where contains(xmlcol, '"dog cat"')
> which return the whole xml record. Is there a way to accomplish this on
> a single record as per xml snippet in my original post which exists as
> an xml document in a single row in the table?
> I will know *in advance* which record in the table needs to be
> queried, so I need to be able to query the actual content of the xml
> cell and return 0,1 or more matching results based upon that xml
> snippet and *not* based upon matches spanning several rows.
> Looking at the above snippet, I need to be able to search for the word
> "him" which would give me '@.id=9267' and '@.id=8423' returned.
> I'm not looking to search across rows - that appears straightforward -
> I'm trying to perform searches *within* the xml cell that exits in a
> single (and at the moment, unique) record in my table.
> I'd be very grateful for any help in trying to explain this, as I'm at
> a loss at the moment.
> Thanks again, David
>|||Even with Hilary's workaround there is no good full-text search support at
the sub XML datatype level yet. Mainly because the XQuery language is just
now working on a full-text extension to XQuery.
Can I please ask you to file a feature request at
http://lab.msdn.microsoft.com/productfeedback/
We are certainly looking into adding such functionality and actually
customer use cases are important for us to motivate the investment to our
purse holders.
As a short-term work around, I recommend to split the sections into one per
row (using the nodes() method) and then FT-index that column.
Best regards
Michael
<firechaser@.talk21.com> wrote in message
news:1139433546.490894.304730@.g47g2000cwa.googlegroups.com...
> Thanks very much for your time Luca.
> Unfortunately I was hoping to use the full-text capabilities in SQL
> Server 2005 to produce *multiple* search results from any single xml
> snippet stored in a single (and known in advance) row.
> All the FTS examples I've seen are of the type
> select xmlcol from t1
> where contains(xmlcol, '"dog cat"')
> which return the whole xml record. Is there a way to accomplish this on
> a single record as per xml snippet in my original post which exists as
> an xml document in a single row in the table?
> I will know *in advance* which record in the table needs to be
> queried, so I need to be able to query the actual content of the xml
> cell and return 0,1 or more matching results based upon that xml
> snippet and *not* based upon matches spanning several rows.
> Looking at the above snippet, I need to be able to search for the word
> "him" which would give me '@.id=9267' and '@.id=8423' returned.
> I'm not looking to search across rows - that appears straightforward -
> I'm trying to perform searches *within* the xml cell that exits in a
> single (and at the moment, unique) record in my table.
> I'd be very grateful for any help in trying to explain this, as I'm at
> a loss at the moment.
> Thanks again, David
>
following xml snippet:
<plays>
<folder id="681">
<page id="3155">
<submitdate>12-10-02</submitdate>
<pagetext>Horatio says 'tis but our fantasy,</pagetext>
</page>
<page id="9267">
<submitdate>09-04-04</submitdate>
<pagetext>And will not let belief take hold of him</pagetext>
</page>
</folder>
<folder id="902">
<page id="1853">
<submitdate>22-11-05</submitdate>
<pagetext>Touching this dreaded sight, twice seen of us:</pagetext>
</page>
<page id="8423">
<submitdate>31-05-02</submitdate>
<pagetext>Therefore I have entreated him along</pagetext>
</page>
</folder>
</plays>
I'd like to be able to search this document for keywords, such as
"belief" to return the value '@.id=9267' i.e. return the id value of the
containing node ('page') where the string "belief" was found.
Likewise, if the search was for "him" then '@.id=9267' and '@.id=8423'
would be returned.
Could anyone give me pointers as to how to implement this in XQuery /
Full-text?
Many thanks, DavidHere is a possible implementation:
begin
declare @.x xml
set @.x='
<plays>
<folder id="681">
<page id="3155">
<submitdate>12-10-02</submitdate>
<pagetext>Horatio says ''tis but our fantasy,</pagetext>
</page>
<page id="9267">
<submitdate>09-04-04</submitdate>
<pagetext>And will not let belief take hold of him</pagetext>
</page>
</folder>
<folder id="902">
<page id="1853">
<submitdate>22-11-05</submitdate>
<pagetext>Touching this dreaded sight, twice seen of us:</pagetext>
</page>
<page id="8423">
<submitdate>31-05-02</submitdate>
<pagetext>Therefore I have entreated him along</pagetext>
</page>
</folder>
</plays>
'
declare @.x2 xml
set @.x2 = (SELECT T.c.query('.') AS risultato FROM
@.x.nodes('/plays/folder/page[@.id]') T(c) where T.c.value('.','varchar(50)')
like '%Hor%')
select @.x2.value('(/page/@.id)[1]','int')
end
OK?
"firechaser@.talk21.com" wrote:
> I have a full text indexed xml column in sql server 2005 containing the
> following xml snippet:
> <plays>
> <folder id="681">
> <page id="3155">
> <submitdate>12-10-02</submitdate>
> <pagetext>Horatio says 'tis but our fantasy,</pagetext>
> </page>
> <page id="9267">
> <submitdate>09-04-04</submitdate>
> <pagetext>And will not let belief take hold of him</pagetext>
> </page>
> </folder>
> <folder id="902">
> <page id="1853">
> <submitdate>22-11-05</submitdate>
> <pagetext>Touching this dreaded sight, twice seen of us:</pagetext>
> </page>
> <page id="8423">
> <submitdate>31-05-02</submitdate>
> <pagetext>Therefore I have entreated him along</pagetext>
> </page>
> </folder>
> </plays>
>
> I'd like to be able to search this document for keywords, such as
> "belief" to return the value '@.id=9267' i.e. return the id value of the
> containing node ('page') where the string "belief" was found.
> Likewise, if the search was for "him" then '@.id=9267' and '@.id=8423'
> would be returned.
> Could anyone give me pointers as to how to implement this in XQuery /
> Full-text?
> Many thanks, David
>|||Thanks very much for your time Luca.
Unfortunately I was hoping to use the full-text capabilities in SQL
Server 2005 to produce *multiple* search results from any single xml
snippet stored in a single (and known in advance) row.
All the FTS examples I've seen are of the type
select xmlcol from t1
where contains(xmlcol, '"dog cat"')
which return the whole xml record. Is there a way to accomplish this on
a single record as per xml snippet in my original post which exists as
an xml document in a single row in the table?
I will know *in advance* which record in the table needs to be
queried, so I need to be able to query the actual content of the xml
cell and return 0,1 or more matching results based upon that xml
snippet and *not* based upon matches spanning several rows.
Looking at the above snippet, I need to be able to search for the word
"him" which would give me '@.id=9267' and '@.id=8423' returned.
I'm not looking to search across rows - that appears straightforward -
I'm trying to perform searches *within* the xml cell that exits in a
single (and at the moment, unique) record in my table.
I'd be very grateful for any help in trying to explain this, as I'm at
a loss at the moment.
Thanks again, David|||try this
create database FullTextXML
use FullTextXML
GO
drop table XMLFULLText
drop XML SCHEMA COLLECTION PlaysSchema
CREATE XML SCHEMA COLLECTION PlaysSchema AS '
<xsd:schema targetNamespace="http://www.plays.com/plays"
xmlns ="http://www.plays.com/plays"
elementFormDefault="qualified"
attributeFormDefault="unqualified"
xmlns:xsd="http://www.w3.org/2001/XMLSchema" >
<xsd:element name="plays"><xsd:complexType><xsd:complexContent>
<xsd:restriction base="xsd:anyType"><xsd:choice
maxOccurs="unbounded"><xsd:element name="folder">
<xsd:complexType><xsd:complexContent><xsd:restriction
base="xsd:anyType"><xsd:sequence>
<xsd:element name="page" minOccurs="0"
maxOccurs="unbounded"><xsd:complexType>
<xsd:complexContent><xsd:restriction base="xsd:anyType"><xsd:sequence>
<xsd:element name="submitdate" type="xsd:string" minOccurs="0" />
<xsd:element name="pagetext" type="xsd:string" minOccurs="0" />
</xsd:sequence><xsd:attribute name="id" type="xsd:string"
/></xsd:restriction></xsd:complexContent>
</xsd:complexType></xsd:element></xsd:sequence><xsd:attribute name="id"
type="xsd:string" />
</xsd:restriction></xsd:complexContent></xsd:complexType></xsd:element></xsd
:choice></xsd:restriction>
</xsd:complexContent></xsd:complexType></xsd:element></xsd:schema>'
GO
Create Table XMLFULLText (pk int not null identity constraint XMLFULLTEXTPK
primary key, XMLDOC XML(PlaysSchema))
GO
declare @.XMLDOC XML
set @.XMLDOC ='<plays xmlns="http://www.plays.com/plays"><folder id="681">
<page id="3155">
<submitdate>12-10-02</submitdate>
<pagetext>Horatio says ''tis but our
fantasy,</pagetext>
</page>
<page id="9267">
<submitdate>09-04-04</submitdate>
<pagetext>And will not let belief take hold of
him</pagetext>
</page>
</folder>
<folder id="902">
<page id="1853">
<submitdate>22-11-05</submitdate>
<pagetext>Touching this dreaded sight, twice seen of
us:</pagetext>
</page>
<page id="8423">
<submitdate>31-05-02</submitdate>
<pagetext>Therefore I have entreated him
along</pagetext>
</page>
</folder></plays>'
insert into XMLFULLText (XMLDOC) values(@.XMLDOC)
GO
sp_fulltext_database 'enable'
GO
create fulltext catalog test as default
GO
create fulltext index on XMLFULLText (XMLDOC) key index XMLFULLTextPK
GO
select * from XMLFULLText where contains(*,'belief')
GO
--1 row returned. OK, lets make this a little more interesting
GO
declare @.XMLDOC XML
set @.XMLDOC ='<plays xmlns="http://www.plays.com/plays"><folder id="681">
<page id="3155">
<submitdate>12-10-02</submitdate>
<pagetext>Horatio says ''tis but our
fantasy,</pagetext>
</page>
<page id="9267">
<submitdate>09-04-04</submitdate>
<pagetext>And will not let beliefs take hold of
him</pagetext>
</page>
</folder>
<folder id="902">
<page id="1853">
<submitdate>22-11-05</submitdate>
<pagetext>Touching this dreaded sight, twice seen of
us:</pagetext>
</page>
<page id="8423">
<submitdate>31-05-02</submitdate>
<pagetext>Therefore I have entreated him
along</pagetext>
</page>
</folder>
</plays>'
insert into XMLFULLText (XMLDOC) values(@.XMLDOC)
GO
select * from XMLFULLText where contains(*,'belief')
GO
--1 row returned.
select * from XMLFULLText where FREETEXT(*,'belief')
GO
--2 row returned. OK, lets restrict this to contents coming from an element
declare @.XMLDOC XML
set @.XMLDOC ='<plays xmlns="http://www.plays.com/plays"><folder id="681">
<page id="3155">
<submitdate>belief</submitdate>
<pagetext>Horatio says ''tis but our
fantasy,</pagetext>
</page>
<page id="9267">
<submitdate>09-04-04</submitdate>
<pagetext>And will not let take hold of
him</pagetext>
</page>
</folder>
<folder id="902">
<page id="1853">
<submitdate>22-11-05</submitdate>
<pagetext>Touching this dreaded sight, twice seen of
us:</pagetext>
</page>
<page id="8423">
<submitdate>31-05-02</submitdate>
<pagetext>Therefore I have entreated him
along</pagetext>
</page>
</folder>
</plays>'
insert into XMLFULLText (XMLDOC) values(@.XMLDOC)
GO
select * from XMLFULLText where contains(*,'belief')
GO
--2 rows returned, one in page text and one in submit date
select * from XMLFULLText where FREETEXT(*,'belief')
GO
--2 rows returned. OK, lets restrict this to contents coming from an element
select * from XMLFulltext where contains(*,'belief')
WITH XMLNAMESPACES ('http://www.plays.com/plays' AS pd)
SELECT pk, XMLDOC
FROM XMLFulltext
where contains(XMLDOC, 'belief')
and
xmldoc.exist('/plays/folder/page/pagetext/text()[contains(.,"belief")]')=1
--notice we don't get a hit from 3 where belief is in the submitdate column
WITH XMLNAMESPACES ('http://www.plays.com/plays' AS pd)
SELECT pk, XMLDOC.query('
-- <pd:plays>
--{/pd:folder/pd:page/pd:pagetext}
-- </pd:plays>
--') AS Result
FROM XMLFulltext
where contains(XMLDOC, 'belief')
and
xmldoc.exist('/pd:plays/pd:folder/pd:page/pd:pagetext[1]/text()[contains(.,"
belief")]')=1
--notice we don't get a hit from 3 where belief is in the submitdate column
WITH XMLNAMESPACES ('http://www.plays.com/plays' AS pd)
WITH XMLNAMESPACES
('http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductMode
lDescription'
AS pd)
SELECT CatalogDescription.query('
<pd:Product ProductModelID="{
(/pd:ProductDescription/@.ProductModelID)[1] }">
<Picture>
{ /pd:ProductDescription/pd:Picture/pd:Angle }
{ /pd:ProductDescription/pd:Picture/pd:Size }
</Picture>
</pd:Product>
') as Result
FROM Production.ProductModel
WHERE CatalogDescription.exist('/pd:ProductDescription/pd:Picture') = 1
AND
CatalogDescription.value('(/pd:ProductDescription/pd:Picture/pd:Angle)[1]',
'varchar(20)') = 'front'
AND
CatalogDescription.value('(/pd:ProductDescription/pd:Picture/pd:Size)[1]',
'varchar(20)') = 'small'
WITH XMLNAMESPACES ('http://www.plays.com/plays' AS pd)
SELECT pk,*
FROM XMLFulltext
where contains(XMLDOC, 'belief')
and
xmldoc.exist('/pd:plays[1]/pd:folder[1]/pd:page[1]/pd:submitdate[contains(.,
"belief")]')=1
WITH XMLNAMESPACES ('http://www.plays.com/plays' AS pd)
SELECT pk,*
FROM XMLFulltext
where contains(XMLDOC, 'belief')
and
xmldoc.exist('/pd:plays[1]/pd:folder[1]/pd:page[2]/pd:pagetext[contains(.,"b
elief")]')=1
WITH XMLNAMESPACES ('http://www.plays.com/plays' AS pd)
SELECT pk,*
FROM XMLFulltext
where contains(XMLDOC, 'belief')
and
xmldoc.exist('/pd:plays[1]/pd:folder[1]/pd:page/pd:pagetext[contains(.,"beli
ef")]')=1
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
<firechaser@.talk21.com> wrote in message
news:1139433546.490894.304730@.g47g2000cwa.googlegroups.com...
> Thanks very much for your time Luca.
> Unfortunately I was hoping to use the full-text capabilities in SQL
> Server 2005 to produce *multiple* search results from any single xml
> snippet stored in a single (and known in advance) row.
> All the FTS examples I've seen are of the type
> select xmlcol from t1
> where contains(xmlcol, '"dog cat"')
> which return the whole xml record. Is there a way to accomplish this on
> a single record as per xml snippet in my original post which exists as
> an xml document in a single row in the table?
> I will know *in advance* which record in the table needs to be
> queried, so I need to be able to query the actual content of the xml
> cell and return 0,1 or more matching results based upon that xml
> snippet and *not* based upon matches spanning several rows.
> Looking at the above snippet, I need to be able to search for the word
> "him" which would give me '@.id=9267' and '@.id=8423' returned.
> I'm not looking to search across rows - that appears straightforward -
> I'm trying to perform searches *within* the xml cell that exits in a
> single (and at the moment, unique) record in my table.
> I'd be very grateful for any help in trying to explain this, as I'm at
> a loss at the moment.
> Thanks again, David
>|||Even with Hilary's workaround there is no good full-text search support at
the sub XML datatype level yet. Mainly because the XQuery language is just
now working on a full-text extension to XQuery.
Can I please ask you to file a feature request at
http://lab.msdn.microsoft.com/productfeedback/
We are certainly looking into adding such functionality and actually
customer use cases are important for us to motivate the investment to our
purse holders.
As a short-term work around, I recommend to split the sections into one per
row (using the nodes() method) and then FT-index that column.
Best regards
Michael
<firechaser@.talk21.com> wrote in message
news:1139433546.490894.304730@.g47g2000cwa.googlegroups.com...
> Thanks very much for your time Luca.
> Unfortunately I was hoping to use the full-text capabilities in SQL
> Server 2005 to produce *multiple* search results from any single xml
> snippet stored in a single (and known in advance) row.
> All the FTS examples I've seen are of the type
> select xmlcol from t1
> where contains(xmlcol, '"dog cat"')
> which return the whole xml record. Is there a way to accomplish this on
> a single record as per xml snippet in my original post which exists as
> an xml document in a single row in the table?
> I will know *in advance* which record in the table needs to be
> queried, so I need to be able to query the actual content of the xml
> cell and return 0,1 or more matching results based upon that xml
> snippet and *not* based upon matches spanning several rows.
> Looking at the above snippet, I need to be able to search for the word
> "him" which would give me '@.id=9267' and '@.id=8423' returned.
> I'm not looking to search across rows - that appears straightforward -
> I'm trying to perform searches *within* the xml cell that exits in a
> single (and at the moment, unique) record in my table.
> I'd be very grateful for any help in trying to explain this, as I'm at
> a loss at the moment.
> Thanks again, David
>
2012年3月22日星期四
Full Text Search and Virtual Server
Does anyone know if there is a way to turn off (and hide
the search form) for a single Virtual Server? I know I
can turn it off across all servers, but I need to turn it
off for just one. I have a site that consists of a lot of
RSS feeds, which are of course not searchable. My users
are getting confused! THANKS.Stephen,
I'm not exactly sure what you're trying to do here. Are you referring to a
cluster's failover virtual server?
Could you provide more details?
Note, you can also post FTS related questions to the newsgroup:
microsoft.public.sqlserver.fulltext.
Regards,
John
"Stephen Haiman" <shaiman@.ctt-inc.com> wrote in message
news:19c001c3a8aa$864e7020$a601280a@.phx.gbl...
> Does anyone know if there is a way to turn off (and hide
> the search form) for a single Virtual Server? I know I
> can turn it off across all servers, but I need to turn it
> off for just one. I have a site that consists of a lot of
> RSS feeds, which are of course not searchable. My users
> are getting confused! THANKS.sql
the search form) for a single Virtual Server? I know I
can turn it off across all servers, but I need to turn it
off for just one. I have a site that consists of a lot of
RSS feeds, which are of course not searchable. My users
are getting confused! THANKS.Stephen,
I'm not exactly sure what you're trying to do here. Are you referring to a
cluster's failover virtual server?
Could you provide more details?
Note, you can also post FTS related questions to the newsgroup:
microsoft.public.sqlserver.fulltext.
Regards,
John
"Stephen Haiman" <shaiman@.ctt-inc.com> wrote in message
news:19c001c3a8aa$864e7020$a601280a@.phx.gbl...
> Does anyone know if there is a way to turn off (and hide
> the search form) for a single Virtual Server? I know I
> can turn it off across all servers, but I need to turn it
> off for just one. I have a site that consists of a lot of
> RSS feeds, which are of course not searchable. My users
> are getting confused! THANKS.sql
Full Text Search across multiple tables
Imagine you're building a search for a movie database which you wanted to
index actors, quotes from scenes and movie names into a single search... how
do you accomplish this using full text search? The closest I can think of
is... (for a sample search for "Walken"
select RANK, actorId as Id from FREETEXTTABLE( actors, *, 'ISABOUT (+ WALKEN
+ WEIGHT(1.0))') a JOIN actors b on a.[key] = b.actorid
UNION ALL
select RANK, sceneId as Id from FREETEXTTABLE( scenes, *, 'ISABOUT (+ WALKEN
+ WEIGHT(1.0))') a JOIN scenes b on a.[key] = b.sceneId
UNION ALL
select RANK, movieId as Id from FREETEXTTABLE( movie, *, 'ISABOUT (+ WALKEN
+ WEIGHT(1.0))') a JOIN movie b on a.[key] = b.movieId
But it doesn't rank correctly. Suggestions? I was thinking of creating a
view to combine all the tables, but that doesn't work either, since the view
wouldn't have a unique column (which FTS needs)
Iron,
If I understand your question this might help. I've done this before with
company names, addresses and phone numbers and other company information
contained in multiple tables.
1. Create another table with a column that concatinates all the columns you
want to index. It would require a composite primary key of actorid, sceneid,
and movieid. I am assuming that this relationship exists in your database.
2. Configure your full text catalog to work with the new table and column.
3. Populate the new table.
4. Apply your full text queries against the new table and join to the other
tables to return the recordsets.
I duplicates data, but it works great.
-- Bill
"IronYuppie" <IronYuppie@.discussions.microsoft.com> wrote in message
news:E5C1BB12-8C96-4EE2-81CA-9157E67C388E@.microsoft.com...
> Imagine you're building a search for a movie database which you wanted to
> index actors, quotes from scenes and movie names into a single search...
> how
> do you accomplish this using full text search? The closest I can think of
> is... (for a sample search for "Walken"
> select RANK, actorId as Id from FREETEXTTABLE( actors, *, 'ISABOUT (+
> WALKEN
> + WEIGHT(1.0))') a JOIN actors b on a.[key] = b.actorid
> UNION ALL
> select RANK, sceneId as Id from FREETEXTTABLE( scenes, *, 'ISABOUT (+
> WALKEN
> + WEIGHT(1.0))') a JOIN scenes b on a.[key] = b.sceneId
> UNION ALL
> select RANK, movieId as Id from FREETEXTTABLE( movie, *, 'ISABOUT (+
> WALKEN
> + WEIGHT(1.0))') a JOIN movie b on a.[key] = b.movieId
> But it doesn't rank correctly. Suggestions? I was thinking of creating a
> view to combine all the tables, but that doesn't work either, since the
> view
> wouldn't have a unique column (which FTS needs)
index actors, quotes from scenes and movie names into a single search... how
do you accomplish this using full text search? The closest I can think of
is... (for a sample search for "Walken"
select RANK, actorId as Id from FREETEXTTABLE( actors, *, 'ISABOUT (+ WALKEN
+ WEIGHT(1.0))') a JOIN actors b on a.[key] = b.actorid
UNION ALL
select RANK, sceneId as Id from FREETEXTTABLE( scenes, *, 'ISABOUT (+ WALKEN
+ WEIGHT(1.0))') a JOIN scenes b on a.[key] = b.sceneId
UNION ALL
select RANK, movieId as Id from FREETEXTTABLE( movie, *, 'ISABOUT (+ WALKEN
+ WEIGHT(1.0))') a JOIN movie b on a.[key] = b.movieId
But it doesn't rank correctly. Suggestions? I was thinking of creating a
view to combine all the tables, but that doesn't work either, since the view
wouldn't have a unique column (which FTS needs)
Iron,
If I understand your question this might help. I've done this before with
company names, addresses and phone numbers and other company information
contained in multiple tables.
1. Create another table with a column that concatinates all the columns you
want to index. It would require a composite primary key of actorid, sceneid,
and movieid. I am assuming that this relationship exists in your database.
2. Configure your full text catalog to work with the new table and column.
3. Populate the new table.
4. Apply your full text queries against the new table and join to the other
tables to return the recordsets.
I duplicates data, but it works great.
-- Bill
"IronYuppie" <IronYuppie@.discussions.microsoft.com> wrote in message
news:E5C1BB12-8C96-4EE2-81CA-9157E67C388E@.microsoft.com...
> Imagine you're building a search for a movie database which you wanted to
> index actors, quotes from scenes and movie names into a single search...
> how
> do you accomplish this using full text search? The closest I can think of
> is... (for a sample search for "Walken"
> select RANK, actorId as Id from FREETEXTTABLE( actors, *, 'ISABOUT (+
> WALKEN
> + WEIGHT(1.0))') a JOIN actors b on a.[key] = b.actorid
> UNION ALL
> select RANK, sceneId as Id from FREETEXTTABLE( scenes, *, 'ISABOUT (+
> WALKEN
> + WEIGHT(1.0))') a JOIN scenes b on a.[key] = b.sceneId
> UNION ALL
> select RANK, movieId as Id from FREETEXTTABLE( movie, *, 'ISABOUT (+
> WALKEN
> + WEIGHT(1.0))') a JOIN movie b on a.[key] = b.movieId
> But it doesn't rank correctly. Suggestions? I was thinking of creating a
> view to combine all the tables, but that doesn't work either, since the
> view
> wouldn't have a unique column (which FTS needs)
Full Text Search across multiple tables
Imagine you're building a search for a movie database which you wanted to
index actors, quotes from scenes and movie names into a single search... ho
w
do you accomplish this using full text search? The closest I can think of
is... (for a sample search for "Walken"
select RANK, actorId as Id from FREETEXTTABLE( actors, *, 'ISABOUT (+ WALKEN
+ WEIGHT(1.0))') a JOIN actors b on a.[key] = b.actorid
UNION ALL
select RANK, sceneId as Id from FREETEXTTABLE( scenes, *, 'ISABOUT (+ WALKEN
+ WEIGHT(1.0))') a JOIN scenes b on a.[key] = b.sceneId
UNION ALL
select RANK, movieId as Id from FREETEXTTABLE( movie, *, 'ISABOUT (+ WALKEN
+ WEIGHT(1.0))') a JOIN movie b on a.[key] = b.movieId
But it doesn't rank correctly. Suggestions? I was thinking of creating a
view to combine all the tables, but that doesn't work either, since the view
wouldn't have a unique column (which FTS needs)Iron,
If I understand your question this might help. I've done this before with
company names, addresses and phone numbers and other company information
contained in multiple tables.
1. Create another table with a column that concatinates all the columns you
want to index. It would require a composite primary key of actorid, sceneid,
and movieid. I am assuming that this relationship exists in your database.
2. Configure your full text catalog to work with the new table and column.
3. Populate the new table.
4. Apply your full text queries against the new table and join to the other
tables to return the recordsets.
I duplicates data, but it works great.
-- Bill
"IronYuppie" <IronYuppie@.discussions.microsoft.com> wrote in message
news:E5C1BB12-8C96-4EE2-81CA-9157E67C388E@.microsoft.com...
> Imagine you're building a search for a movie database which you wanted to
> index actors, quotes from scenes and movie names into a single search...
> how
> do you accomplish this using full text search? The closest I can think of
> is... (for a sample search for "Walken"
> select RANK, actorId as Id from FREETEXTTABLE( actors, *, 'ISABOUT (+
> WALKEN
> + WEIGHT(1.0))') a JOIN actors b on a.[key] = b.actorid
> UNION ALL
> select RANK, sceneId as Id from FREETEXTTABLE( scenes, *, 'ISABOUT (+
> WALKEN
> + WEIGHT(1.0))') a JOIN scenes b on a.[key] = b.sceneId
> UNION ALL
> select RANK, movieId as Id from FREETEXTTABLE( movie, *, 'ISABOUT (+
> WALKEN
> + WEIGHT(1.0))') a JOIN movie b on a.[key] = b.movieId
> But it doesn't rank correctly. Suggestions? I was thinking of creating a
> view to combine all the tables, but that doesn't work either, since the
> view
> wouldn't have a unique column (which FTS needs)
index actors, quotes from scenes and movie names into a single search... ho
w
do you accomplish this using full text search? The closest I can think of
is... (for a sample search for "Walken"
select RANK, actorId as Id from FREETEXTTABLE( actors, *, 'ISABOUT (+ WALKEN
+ WEIGHT(1.0))') a JOIN actors b on a.[key] = b.actorid
UNION ALL
select RANK, sceneId as Id from FREETEXTTABLE( scenes, *, 'ISABOUT (+ WALKEN
+ WEIGHT(1.0))') a JOIN scenes b on a.[key] = b.sceneId
UNION ALL
select RANK, movieId as Id from FREETEXTTABLE( movie, *, 'ISABOUT (+ WALKEN
+ WEIGHT(1.0))') a JOIN movie b on a.[key] = b.movieId
But it doesn't rank correctly. Suggestions? I was thinking of creating a
view to combine all the tables, but that doesn't work either, since the view
wouldn't have a unique column (which FTS needs)Iron,
If I understand your question this might help. I've done this before with
company names, addresses and phone numbers and other company information
contained in multiple tables.
1. Create another table with a column that concatinates all the columns you
want to index. It would require a composite primary key of actorid, sceneid,
and movieid. I am assuming that this relationship exists in your database.
2. Configure your full text catalog to work with the new table and column.
3. Populate the new table.
4. Apply your full text queries against the new table and join to the other
tables to return the recordsets.
I duplicates data, but it works great.
-- Bill
"IronYuppie" <IronYuppie@.discussions.microsoft.com> wrote in message
news:E5C1BB12-8C96-4EE2-81CA-9157E67C388E@.microsoft.com...
> Imagine you're building a search for a movie database which you wanted to
> index actors, quotes from scenes and movie names into a single search...
> how
> do you accomplish this using full text search? The closest I can think of
> is... (for a sample search for "Walken"
> select RANK, actorId as Id from FREETEXTTABLE( actors, *, 'ISABOUT (+
> WALKEN
> + WEIGHT(1.0))') a JOIN actors b on a.[key] = b.actorid
> UNION ALL
> select RANK, sceneId as Id from FREETEXTTABLE( scenes, *, 'ISABOUT (+
> WALKEN
> + WEIGHT(1.0))') a JOIN scenes b on a.[key] = b.sceneId
> UNION ALL
> select RANK, movieId as Id from FREETEXTTABLE( movie, *, 'ISABOUT (+
> WALKEN
> + WEIGHT(1.0))') a JOIN movie b on a.[key] = b.movieId
> But it doesn't rank correctly. Suggestions? I was thinking of creating a
> view to combine all the tables, but that doesn't work either, since the
> view
> wouldn't have a unique column (which FTS needs)
full text search : Search orders of single input of "multiple word
Sql server express advanced edition
Hi
I am using following query to select data using full text search in a stored
procedure:
=====================
SET @.keyword = '"*' + LOWER(@.keyword) + '*"'
Select *
from tablename1
where contains (*, @.keyword)
===========================
It returns me rows for keyword "Fish Chips" which have values (Fish and
chips) but when i change my search keyword to "Chips Fish" it does not return
any rows.
Can I modify my query so that It returms me all the rows containing Fish or
chips in any order?
i.e. In the input screen for users they input a combination of words like
"chinese mexican". My query should spit all rows which have chinese and
mexican in any order in one of indexed columns.
For above input (Chinese mexican) I should have output like :
Chinese mexican food
chinese and mexican food
mexican and chinese food
mexican chinese clothing
mexican food indian food and chinese food
If I need to parse input and divide it in multiple words before searching,
If someone has a example on how to do this, will be great.
ontario, canada
In other words, I have requirement where I need to do full text search on
all the
search text. For example, for the search text "fish chips" , I should get
the following records having "fish chips" as well as "chips fish".
ontario, canada
"db" wrote:
> Sql server express advanced edition
> Hi
> I am using following query to select data using full text search in a stored
> procedure:
> =====================
> SET @.keyword = '"*' + LOWER(@.keyword) + '*"'
> Select *
> from tablename1
> where contains (*, @.keyword)
> ===========================
> It returns me rows for keyword "Fish Chips" which have values (Fish and
> chips) but when i change my search keyword to "Chips Fish" it does not return
> any rows.
> Can I modify my query so that It returms me all the rows containing Fish or
> chips in any order?
> i.e. In the input screen for users they input a combination of words like
> "chinese mexican". My query should spit all rows which have chinese and
> mexican in any order in one of indexed columns.
> For above input (Chinese mexican) I should have output like :
> Chinese mexican food
> chinese and mexican food
> mexican and chinese food
> mexican chinese clothing
> mexican food indian food and chinese food
> If I need to parse input and divide it in multiple words before searching,
> If someone has a example on how to do this, will be great.
>
> --
> ontario, canada
Hi
I am using following query to select data using full text search in a stored
procedure:
=====================
SET @.keyword = '"*' + LOWER(@.keyword) + '*"'
Select *
from tablename1
where contains (*, @.keyword)
===========================
It returns me rows for keyword "Fish Chips" which have values (Fish and
chips) but when i change my search keyword to "Chips Fish" it does not return
any rows.
Can I modify my query so that It returms me all the rows containing Fish or
chips in any order?
i.e. In the input screen for users they input a combination of words like
"chinese mexican". My query should spit all rows which have chinese and
mexican in any order in one of indexed columns.
For above input (Chinese mexican) I should have output like :
Chinese mexican food
chinese and mexican food
mexican and chinese food
mexican chinese clothing
mexican food indian food and chinese food
If I need to parse input and divide it in multiple words before searching,
If someone has a example on how to do this, will be great.
ontario, canada
In other words, I have requirement where I need to do full text search on
all the
search text. For example, for the search text "fish chips" , I should get
the following records having "fish chips" as well as "chips fish".
ontario, canada
"db" wrote:
> Sql server express advanced edition
> Hi
> I am using following query to select data using full text search in a stored
> procedure:
> =====================
> SET @.keyword = '"*' + LOWER(@.keyword) + '*"'
> Select *
> from tablename1
> where contains (*, @.keyword)
> ===========================
> It returns me rows for keyword "Fish Chips" which have values (Fish and
> chips) but when i change my search keyword to "Chips Fish" it does not return
> any rows.
> Can I modify my query so that It returms me all the rows containing Fish or
> chips in any order?
> i.e. In the input screen for users they input a combination of words like
> "chinese mexican". My query should spit all rows which have chinese and
> mexican in any order in one of indexed columns.
> For above input (Chinese mexican) I should have output like :
> Chinese mexican food
> chinese and mexican food
> mexican and chinese food
> mexican chinese clothing
> mexican food indian food and chinese food
> If I need to parse input and divide it in multiple words before searching,
> If someone has a example on how to do this, will be great.
>
> --
> ontario, canada
2012年3月11日星期日
Full text index deteriorating in use
Very strange situation on one of our SQL 2005 boxes.
- A Full text catalog containing a single full text index
- The index is of a single table containing a uniqueidentifier column and five varchar(max) columns, all indexed
- About 300,000 rows in the table
If I create the index and immediately run a query like this:
SELECT COUNT(*) FROM FREETEXTTABLE(tablename, columnname, 'term1 term2 term3', 5000)
I get the expected result: 5000 (there are actually about 185,000 matching rows if that same query is run without the top_n_by_rank parameter).
But then if I run the same query repeatedly over the next 10-15 minutes, the returned value decreases...4616, 3505...ending up around 507. It's like the full text index rots away before my eyes.
The database is purely read-only. We do run stored procedures against it that construct temporary tables, but nothing puts new data into this particular table. Doesn't matter whether the full-text index is set to manual or automatic repopulation. The timestamps on the index files on disk don't change. Nothing odd shows up in the Windows event logs or SQL Server logs.
I'm stumped. Any ideas?
http://www.sql-server-performance.com/full_text_search.asp
http://www.sql-server-performance.com/tb_search_optimization.asp
2012年3月7日星期三
Full Text - Index Population - HDD Access
Hi,
I'm indexing a single field on a table with 10 million rows in it, its
currently taking about 48 hours to build.
I've been looking into performance bottlenecks on the system and see that
this delay is being mainly caused by MSSearch writing massive amounts of
data to the FT Index location. Since the finished index is only about 400mb
I can only imagine that the population process is rewriting the data again
and again to the disk as it is updated.
Is this a fair assumption? If so is there anyway around it, maybe to tell
MSSearch to use ram until the population is build then write the index to
disk?
Thanks
Michael
No.
There is the resource usage setting which may increase performance during
the indexing process (sp_fulltext_service 'catalogname','resource_usgae',5)
The more resources you can spend to improve the indexing process the better,
normally you get the best bang for your buck by investing in the disk
subsystem. For instance placing your catalogs on a RAID 10, 1, or 01 array.
01 is not considered to be a good choice as it does not offer the the
protection 10 does.
Also your database should be raid 5 depending on the level of write activity
on it.
What type of content are you indexing. You will get best performance by
indexing text content as opposed to indexing documents in their native
format (doc, xls, pdf, etc).
Please review the full text white paper on SQL FTS performance at:
http://support.microsoft.com/support...er.asp?GSSNB=1
Save this document as a zip and extract it.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Mike Davies" <michael.davies@.synsoft.co.uk> wrote in message
news:ezLfihZmEHA.3824@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I'm indexing a single field on a table with 10 million rows in it, its
> currently taking about 48 hours to build.
> I've been looking into performance bottlenecks on the system and see that
> this delay is being mainly caused by MSSearch writing massive amounts of
> data to the FT Index location. Since the finished index is only about
400mb
> I can only imagine that the population process is rewriting the data again
> and again to the disk as it is updated.
> Is this a fair assumption? If so is there anyway around it, maybe to tell
> MSSearch to use ram until the population is build then write the index to
> disk?
> Thanks
> Michael
>
|||Mike
What hardware is it running on?
Is the Full-Text Index on the same physical drive/lun as the database? It
should be possible to increase IO throughput by placing the Full-Text Index
on its own drive.
Are you using SCSI disks? If so are you using the faster 15k RPM disks as
apposed to 10k RPM disks.
How much RAM does the machine have?
How many processors and what spec are they?
Is SQL Server configured to leave some RAM free for the Operating System and
other Applications, i.e. if you have 2GB RAM is SQL configured to use max
1.5GB?
You might want to try SQL Server 2005 Beta 2. I understand that Full-Text
Search is much faster at indexing.
http://msdn.microsoft.com/library/en...asp?frame=true
Rich
"Hilary Cotter" wrote:
> No.
> There is the resource usage setting which may increase performance during
> the indexing process (sp_fulltext_service 'catalogname','resource_usgae',5)
> The more resources you can spend to improve the indexing process the better,
> normally you get the best bang for your buck by investing in the disk
> subsystem. For instance placing your catalogs on a RAID 10, 1, or 01 array.
> 01 is not considered to be a good choice as it does not offer the the
> protection 10 does.
> Also your database should be raid 5 depending on the level of write activity
> on it.
> What type of content are you indexing. You will get best performance by
> indexing text content as opposed to indexing documents in their native
> format (doc, xls, pdf, etc).
> Please review the full text white paper on SQL FTS performance at:
> http://support.microsoft.com/support...er.asp?GSSNB=1
> Save this document as a zip and extract it.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> "Mike Davies" <michael.davies@.synsoft.co.uk> wrote in message
> news:ezLfihZmEHA.3824@.TK2MSFTNGP12.phx.gbl...
> 400mb
>
>
|||Mike,
Yes, it is a fair assumption. You should review the SQL Server 2000 BOL
title "Full-text Search Recommendations" as well as the FTS Deployment white
paper. Are there workarounds? Yes. You can ensure that the disk drive that
your FT Catalog resides is separate from your database files as well as very
fast (>10,000 RPMs) and configured as RAID0 or RAID10. The Full Text
Indexing process is primarily I/O bound, but can also consume much memory
and CPU usage from time-to-time during Shadow Merges and at the end of the
process due to Master Merge.
Regards,
John
"Mike Davies" <michael.davies@.synsoft.co.uk> wrote in message
news:ezLfihZmEHA.3824@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I'm indexing a single field on a table with 10 million rows in it, its
> currently taking about 48 hours to build.
> I've been looking into performance bottlenecks on the system and see that
> this delay is being mainly caused by MSSearch writing massive amounts of
> data to the FT Index location. Since the finished index is only about
400mb
> I can only imagine that the population process is rewriting the data again
> and again to the disk as it is updated.
> Is this a fair assumption? If so is there anyway around it, maybe to tell
> MSSearch to use ram until the population is build then write the index to
> disk?
> Thanks
> Michael
>
|||Hi Rich,
It's running on a single P4 Prescott at 3Ghz with 4GB of RAM. SQL Server
currently has access to all system RAM but is sat at approx 1.7Gb during the
indexing process.
Both the FullText index and the SQL database are on separate physical SATA
drives, however because of an apparent incompatibility issue with Windows
and running the two SATA drives on their own bus they are both running
through the standard IDE bus, I imagine this is not doing the performance
any favours.
Thanks for the 2005 tip, I'll give that a try.
Can you offer any other suggestions to the setup?
Thanks
Mike
"Richard Yeo" <RichardYeo@.discussions.microsoft.com> wrote in message
news:692389A4-ED05-42C7-B645-982E7E9CE989@.microsoft.com...
> Mike
> What hardware is it running on?
> Is the Full-Text Index on the same physical drive/lun as the database? It
> should be possible to increase IO throughput by placing the Full-Text
Index
> on its own drive.
> Are you using SCSI disks? If so are you using the faster 15k RPM disks as
> apposed to 10k RPM disks.
> How much RAM does the machine have?
> How many processors and what spec are they?
> Is SQL Server configured to leave some RAM free for the Operating System
and
> other Applications, i.e. if you have 2GB RAM is SQL configured to use max
> 1.5GB?
> You might want to try SQL Server 2005 Beta 2. I understand that Full-Text
> Search is much faster at indexing.
>
http://msdn.microsoft.com/library/en...asp?frame=true[vbcol=seagreen]
> Rich
>
> "Hilary Cotter" wrote:
during[vbcol=seagreen]
'catalogname','resource_usgae',5)[vbcol=seagreen]
better,[vbcol=seagreen]
array.[vbcol=seagreen]
activity[vbcol=seagreen]
http://support.microsoft.com/support...er.asp?GSSNB=1[vbcol=seagreen]
that[vbcol=seagreen]
of[vbcol=seagreen]
again[vbcol=seagreen]
tell[vbcol=seagreen]
to[vbcol=seagreen]
|||Mike
Have you implemented the /3GB switch in the OS boot.ini? If not by default
2GB is set aside for apps and 2GB for OS. By using this switch 3GB is set
aside for apps and 1GB for OS. This probably explains why SQL Server is only
using 1.7GB RAM. It would probably be using more if you had the /3GB switch
set. Try setting /3GB switch, instruct SQL Server to use Physical RAM, and
set max memory for SQL Server to be 2.5GB. This will leave 0.5GB for other
apps including MSSearch (I think), file system caching, etc.
Even if you don't have the budget for a 2 proc SQL license I would always
recommend purchasing a second physical processor and configuring SQL Server
to only use one of the processors. This leaves the second processor free for
other applications, OS, remote administration.
You might want to try the manufacturers web-site regarding BIOS / firmware
updates for your SATA / IDE issue.
Rich
"Mike Davies" wrote:
> Hi Rich,
> It's running on a single P4 Prescott at 3Ghz with 4GB of RAM. SQL Server
> currently has access to all system RAM but is sat at approx 1.7Gb during the
> indexing process.
> Both the FullText index and the SQL database are on separate physical SATA
> drives, however because of an apparent incompatibility issue with Windows
> and running the two SATA drives on their own bus they are both running
> through the standard IDE bus, I imagine this is not doing the performance
> any favours.
> Thanks for the 2005 tip, I'll give that a try.
> Can you offer any other suggestions to the setup?
> Thanks
> Mike
>
> "Richard Yeo" <RichardYeo@.discussions.microsoft.com> wrote in message
> news:692389A4-ED05-42C7-B645-982E7E9CE989@.microsoft.com...
> Index
> and
> http://msdn.microsoft.com/library/en...asp?frame=true
> during
> 'catalogname','resource_usgae',5)
> better,
> array.
> activity
> http://support.microsoft.com/support...er.asp?GSSNB=1
> that
> of
> again
> tell
> to
>
>
|||Mike,
Rich, while the /3GB switch can help SQL Server 2000, it has no affect on
the "Microsoft Search" (mssearch.exe) FT Search engine as it can only use up
to a max of 512MB of physical RAM. However, you must set the resource_usage
value to 5 (dedicated) via sp_fulltext_service 'resource_usage', 5 and you
must have at least 512Mb of RAM reserved for the MSSearch service that is
NOT used by the OS or by SQL Server.
If you can purchase an additional CPU, you can set the MSSearch service's to
use one CPU and then use sp_configure to set the affinity of SQL Server to
that cpu to avoid cpu contention during heavy MSSearch CPU usage via the
following method:
You can launch the Tskmgr.exe from the AT command on the multi-proc server
where sql server resides, as follows:
at <current_time+1min> /interactive taskmgr.exe
when it launches, you can then set "cpu affinity" for the MSSearch service
to a cpu or set of cpu's not being used by SQL Server. Then you would use
sp_configure to set SQL Server's cpu affinity to the other cpu's, thus
preventing the cpu usage of mssearch from affecting your sql server
processing. Note, this only works on multi-processor servers.
Regards,
John
"Richard Yeo" <RichardYeo@.discussions.microsoft.com> wrote in message
news:2AEB4663-795F-48F6-BA68-DEAA0D1C4BD8@.microsoft.com...
> Mike
> Have you implemented the /3GB switch in the OS boot.ini? If not by default
> 2GB is set aside for apps and 2GB for OS. By using this switch 3GB is set
> aside for apps and 1GB for OS. This probably explains why SQL Server is
only
> using 1.7GB RAM. It would probably be using more if you had the /3GB
switch
> set. Try setting /3GB switch, instruct SQL Server to use Physical RAM, and
> set max memory for SQL Server to be 2.5GB. This will leave 0.5GB for other
> apps including MSSearch (I think), file system caching, etc.
> Even if you don't have the budget for a 2 proc SQL license I would always
> recommend purchasing a second physical processor and configuring SQL
Server
> to only use one of the processors. This leaves the second processor free
for[vbcol=seagreen]
> other applications, OS, remote administration.
> You might want to try the manufacturers web-site regarding BIOS / firmware
> updates for your SATA / IDE issue.
> Rich
>
> "Mike Davies" wrote:
Server[vbcol=seagreen]
the[vbcol=seagreen]
SATA[vbcol=seagreen]
Windows[vbcol=seagreen]
performance[vbcol=seagreen]
It[vbcol=seagreen]
as[vbcol=seagreen]
System[vbcol=seagreen]
max[vbcol=seagreen]
Full-Text[vbcol=seagreen]
http://msdn.microsoft.com/library/en...asp?frame=true[vbcol=seagreen]
disk[vbcol=seagreen]
the[vbcol=seagreen]
by[vbcol=seagreen]
native[vbcol=seagreen]
http://support.microsoft.com/support...er.asp?GSSNB=1[vbcol=seagreen]
its[vbcol=seagreen]
see[vbcol=seagreen]
amounts[vbcol=seagreen]
about[vbcol=seagreen]
data[vbcol=seagreen]
to[vbcol=seagreen]
index[vbcol=seagreen]
|||"Richard Yeo" <RichardYeo@.discussions.microsoft.com> wrote in message
news:2AEB4663-795F-48F6-BA68-DEAA0D1C4BD8@.microsoft.com...
> Mike
>
<SNIP>
> Even if you don't have the budget for a 2 proc SQL license I would always
> recommend purchasing a second physical processor and configuring SQL
> Server
> to only use one of the processors. This leaves the second processor free
> for
> other applications, OS, remote administration.
>
</SNIP>
Please be careful about that because, in general, you have to purchase a
processor license for every processor that the OS can see, whether that
processor is configured to run SQL Server or not. For more information,
please refer to the SQL Server 2000 Licensing FAQ web page at
http://www.microsoft.com/sql/howtobuy/faq.asp. The third question under
Licensing is reproduced below for your benefit.
Q. Do I have to license all of the processors in a server?
A. You only have to acquire licenses for processors that are
accessible to any copy of the operating system upon which SQL Server 2000 is
installed.
Sincerely,
Stephen Dybing
This posting is provided "AS IS" with no warranties, and confers no rights.
begin 666 ts.gif
J1TE&.#EA`0`!`( ``````/___R'Y! $`````+ `````!``$```(!1 `[
`
end
|||"Richard Yeo" <RichardYeo@.discussions.microsoft.com> wrote in message
news:2AEB4663-795F-48F6-BA68-DEAA0D1C4BD8@.microsoft.com...
> Mike
> Have you implemented the /3GB switch in the OS boot.ini? If not by default
> 2GB is set aside for apps and 2GB for OS. By using this switch 3GB is set
> aside for apps and 1GB for OS. This probably explains why SQL Server is
only
> using 1.7GB RAM. It would probably be using more if you had the /3GB
switch
> set. Try setting /3GB switch, instruct SQL Server to use Physical RAM, and
> set max memory for SQL Server to be 2.5GB. This will leave 0.5GB for other
> apps including MSSearch (I think), file system caching, etc.
> Even if you don't have the budget for a 2 proc SQL license I would always
> recommend purchasing a second physical processor and configuring SQL
Server
> to only use one of the processors. This leaves the second processor free
for
> other applications, OS, remote administration.
Unless thigns have changed, last I knew, MS didn't allow that.
|||Stephen
Thanks for clarifying that.
Obviously there is a difference between what the product is capable of and
what the licensing folk think the product is or should be capable of.
In this instance I feel the Product Team got it right. In the real world
people often have to run more than one application on a server and should not
be forced to buy additional licenses.
With the move in the industry to consolidate this could a lot of unnecessary
expense.
Lets hope this license issue is addressed in the next release of SQL Server
2005.
Rich
"Stephen Dybing [MSFT]" wrote:
> "Richard Yeo" <RichardYeo@.discussions.microsoft.com> wrote in message
> news:2AEB4663-795F-48F6-BA68-DEAA0D1C4BD8@.microsoft.com...
> <SNIP>
> </SNIP>
> Please be careful about that because, in general, you have to purchase a
> processor license for every processor that the OS can see, whether that
> processor is configured to run SQL Server or not. For more information,
> please refer to the SQL Server 2000 Licensing FAQ web page at
> http://www.microsoft.com/sql/howtobuy/faq.asp. The third question under
> Licensing is reproduced below for your benefit.
> Q. Do I have to license all of the processors in a server?
> A. You only have to acquire licenses for processors that are
> accessible to any copy of the operating system upon which SQL Server 2000 is
> installed.
>
>
> --
> Sincerely,
> Stephen Dybing
> This posting is provided "AS IS" with no warranties, and confers no rights
I'm indexing a single field on a table with 10 million rows in it, its
currently taking about 48 hours to build.
I've been looking into performance bottlenecks on the system and see that
this delay is being mainly caused by MSSearch writing massive amounts of
data to the FT Index location. Since the finished index is only about 400mb
I can only imagine that the population process is rewriting the data again
and again to the disk as it is updated.
Is this a fair assumption? If so is there anyway around it, maybe to tell
MSSearch to use ram until the population is build then write the index to
disk?
Thanks
Michael
No.
There is the resource usage setting which may increase performance during
the indexing process (sp_fulltext_service 'catalogname','resource_usgae',5)
The more resources you can spend to improve the indexing process the better,
normally you get the best bang for your buck by investing in the disk
subsystem. For instance placing your catalogs on a RAID 10, 1, or 01 array.
01 is not considered to be a good choice as it does not offer the the
protection 10 does.
Also your database should be raid 5 depending on the level of write activity
on it.
What type of content are you indexing. You will get best performance by
indexing text content as opposed to indexing documents in their native
format (doc, xls, pdf, etc).
Please review the full text white paper on SQL FTS performance at:
http://support.microsoft.com/support...er.asp?GSSNB=1
Save this document as a zip and extract it.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Mike Davies" <michael.davies@.synsoft.co.uk> wrote in message
news:ezLfihZmEHA.3824@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I'm indexing a single field on a table with 10 million rows in it, its
> currently taking about 48 hours to build.
> I've been looking into performance bottlenecks on the system and see that
> this delay is being mainly caused by MSSearch writing massive amounts of
> data to the FT Index location. Since the finished index is only about
400mb
> I can only imagine that the population process is rewriting the data again
> and again to the disk as it is updated.
> Is this a fair assumption? If so is there anyway around it, maybe to tell
> MSSearch to use ram until the population is build then write the index to
> disk?
> Thanks
> Michael
>
|||Mike
What hardware is it running on?
Is the Full-Text Index on the same physical drive/lun as the database? It
should be possible to increase IO throughput by placing the Full-Text Index
on its own drive.
Are you using SCSI disks? If so are you using the faster 15k RPM disks as
apposed to 10k RPM disks.
How much RAM does the machine have?
How many processors and what spec are they?
Is SQL Server configured to leave some RAM free for the Operating System and
other Applications, i.e. if you have 2GB RAM is SQL configured to use max
1.5GB?
You might want to try SQL Server 2005 Beta 2. I understand that Full-Text
Search is much faster at indexing.
http://msdn.microsoft.com/library/en...asp?frame=true
Rich
"Hilary Cotter" wrote:
> No.
> There is the resource usage setting which may increase performance during
> the indexing process (sp_fulltext_service 'catalogname','resource_usgae',5)
> The more resources you can spend to improve the indexing process the better,
> normally you get the best bang for your buck by investing in the disk
> subsystem. For instance placing your catalogs on a RAID 10, 1, or 01 array.
> 01 is not considered to be a good choice as it does not offer the the
> protection 10 does.
> Also your database should be raid 5 depending on the level of write activity
> on it.
> What type of content are you indexing. You will get best performance by
> indexing text content as opposed to indexing documents in their native
> format (doc, xls, pdf, etc).
> Please review the full text white paper on SQL FTS performance at:
> http://support.microsoft.com/support...er.asp?GSSNB=1
> Save this document as a zip and extract it.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> "Mike Davies" <michael.davies@.synsoft.co.uk> wrote in message
> news:ezLfihZmEHA.3824@.TK2MSFTNGP12.phx.gbl...
> 400mb
>
>
|||Mike,
Yes, it is a fair assumption. You should review the SQL Server 2000 BOL
title "Full-text Search Recommendations" as well as the FTS Deployment white
paper. Are there workarounds? Yes. You can ensure that the disk drive that
your FT Catalog resides is separate from your database files as well as very
fast (>10,000 RPMs) and configured as RAID0 or RAID10. The Full Text
Indexing process is primarily I/O bound, but can also consume much memory
and CPU usage from time-to-time during Shadow Merges and at the end of the
process due to Master Merge.
Regards,
John
"Mike Davies" <michael.davies@.synsoft.co.uk> wrote in message
news:ezLfihZmEHA.3824@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I'm indexing a single field on a table with 10 million rows in it, its
> currently taking about 48 hours to build.
> I've been looking into performance bottlenecks on the system and see that
> this delay is being mainly caused by MSSearch writing massive amounts of
> data to the FT Index location. Since the finished index is only about
400mb
> I can only imagine that the population process is rewriting the data again
> and again to the disk as it is updated.
> Is this a fair assumption? If so is there anyway around it, maybe to tell
> MSSearch to use ram until the population is build then write the index to
> disk?
> Thanks
> Michael
>
|||Hi Rich,
It's running on a single P4 Prescott at 3Ghz with 4GB of RAM. SQL Server
currently has access to all system RAM but is sat at approx 1.7Gb during the
indexing process.
Both the FullText index and the SQL database are on separate physical SATA
drives, however because of an apparent incompatibility issue with Windows
and running the two SATA drives on their own bus they are both running
through the standard IDE bus, I imagine this is not doing the performance
any favours.
Thanks for the 2005 tip, I'll give that a try.
Can you offer any other suggestions to the setup?
Thanks
Mike
"Richard Yeo" <RichardYeo@.discussions.microsoft.com> wrote in message
news:692389A4-ED05-42C7-B645-982E7E9CE989@.microsoft.com...
> Mike
> What hardware is it running on?
> Is the Full-Text Index on the same physical drive/lun as the database? It
> should be possible to increase IO throughput by placing the Full-Text
Index
> on its own drive.
> Are you using SCSI disks? If so are you using the faster 15k RPM disks as
> apposed to 10k RPM disks.
> How much RAM does the machine have?
> How many processors and what spec are they?
> Is SQL Server configured to leave some RAM free for the Operating System
and
> other Applications, i.e. if you have 2GB RAM is SQL configured to use max
> 1.5GB?
> You might want to try SQL Server 2005 Beta 2. I understand that Full-Text
> Search is much faster at indexing.
>
http://msdn.microsoft.com/library/en...asp?frame=true[vbcol=seagreen]
> Rich
>
> "Hilary Cotter" wrote:
during[vbcol=seagreen]
'catalogname','resource_usgae',5)[vbcol=seagreen]
better,[vbcol=seagreen]
array.[vbcol=seagreen]
activity[vbcol=seagreen]
http://support.microsoft.com/support...er.asp?GSSNB=1[vbcol=seagreen]
that[vbcol=seagreen]
of[vbcol=seagreen]
again[vbcol=seagreen]
tell[vbcol=seagreen]
to[vbcol=seagreen]
|||Mike
Have you implemented the /3GB switch in the OS boot.ini? If not by default
2GB is set aside for apps and 2GB for OS. By using this switch 3GB is set
aside for apps and 1GB for OS. This probably explains why SQL Server is only
using 1.7GB RAM. It would probably be using more if you had the /3GB switch
set. Try setting /3GB switch, instruct SQL Server to use Physical RAM, and
set max memory for SQL Server to be 2.5GB. This will leave 0.5GB for other
apps including MSSearch (I think), file system caching, etc.
Even if you don't have the budget for a 2 proc SQL license I would always
recommend purchasing a second physical processor and configuring SQL Server
to only use one of the processors. This leaves the second processor free for
other applications, OS, remote administration.
You might want to try the manufacturers web-site regarding BIOS / firmware
updates for your SATA / IDE issue.
Rich
"Mike Davies" wrote:
> Hi Rich,
> It's running on a single P4 Prescott at 3Ghz with 4GB of RAM. SQL Server
> currently has access to all system RAM but is sat at approx 1.7Gb during the
> indexing process.
> Both the FullText index and the SQL database are on separate physical SATA
> drives, however because of an apparent incompatibility issue with Windows
> and running the two SATA drives on their own bus they are both running
> through the standard IDE bus, I imagine this is not doing the performance
> any favours.
> Thanks for the 2005 tip, I'll give that a try.
> Can you offer any other suggestions to the setup?
> Thanks
> Mike
>
> "Richard Yeo" <RichardYeo@.discussions.microsoft.com> wrote in message
> news:692389A4-ED05-42C7-B645-982E7E9CE989@.microsoft.com...
> Index
> and
> http://msdn.microsoft.com/library/en...asp?frame=true
> during
> 'catalogname','resource_usgae',5)
> better,
> array.
> activity
> http://support.microsoft.com/support...er.asp?GSSNB=1
> that
> of
> again
> tell
> to
>
>
|||Mike,
Rich, while the /3GB switch can help SQL Server 2000, it has no affect on
the "Microsoft Search" (mssearch.exe) FT Search engine as it can only use up
to a max of 512MB of physical RAM. However, you must set the resource_usage
value to 5 (dedicated) via sp_fulltext_service 'resource_usage', 5 and you
must have at least 512Mb of RAM reserved for the MSSearch service that is
NOT used by the OS or by SQL Server.
If you can purchase an additional CPU, you can set the MSSearch service's to
use one CPU and then use sp_configure to set the affinity of SQL Server to
that cpu to avoid cpu contention during heavy MSSearch CPU usage via the
following method:
You can launch the Tskmgr.exe from the AT command on the multi-proc server
where sql server resides, as follows:
at <current_time+1min> /interactive taskmgr.exe
when it launches, you can then set "cpu affinity" for the MSSearch service
to a cpu or set of cpu's not being used by SQL Server. Then you would use
sp_configure to set SQL Server's cpu affinity to the other cpu's, thus
preventing the cpu usage of mssearch from affecting your sql server
processing. Note, this only works on multi-processor servers.
Regards,
John
"Richard Yeo" <RichardYeo@.discussions.microsoft.com> wrote in message
news:2AEB4663-795F-48F6-BA68-DEAA0D1C4BD8@.microsoft.com...
> Mike
> Have you implemented the /3GB switch in the OS boot.ini? If not by default
> 2GB is set aside for apps and 2GB for OS. By using this switch 3GB is set
> aside for apps and 1GB for OS. This probably explains why SQL Server is
only
> using 1.7GB RAM. It would probably be using more if you had the /3GB
switch
> set. Try setting /3GB switch, instruct SQL Server to use Physical RAM, and
> set max memory for SQL Server to be 2.5GB. This will leave 0.5GB for other
> apps including MSSearch (I think), file system caching, etc.
> Even if you don't have the budget for a 2 proc SQL license I would always
> recommend purchasing a second physical processor and configuring SQL
Server
> to only use one of the processors. This leaves the second processor free
for[vbcol=seagreen]
> other applications, OS, remote administration.
> You might want to try the manufacturers web-site regarding BIOS / firmware
> updates for your SATA / IDE issue.
> Rich
>
> "Mike Davies" wrote:
Server[vbcol=seagreen]
the[vbcol=seagreen]
SATA[vbcol=seagreen]
Windows[vbcol=seagreen]
performance[vbcol=seagreen]
It[vbcol=seagreen]
as[vbcol=seagreen]
System[vbcol=seagreen]
max[vbcol=seagreen]
Full-Text[vbcol=seagreen]
http://msdn.microsoft.com/library/en...asp?frame=true[vbcol=seagreen]
disk[vbcol=seagreen]
the[vbcol=seagreen]
by[vbcol=seagreen]
native[vbcol=seagreen]
http://support.microsoft.com/support...er.asp?GSSNB=1[vbcol=seagreen]
its[vbcol=seagreen]
see[vbcol=seagreen]
amounts[vbcol=seagreen]
about[vbcol=seagreen]
data[vbcol=seagreen]
to[vbcol=seagreen]
index[vbcol=seagreen]
|||"Richard Yeo" <RichardYeo@.discussions.microsoft.com> wrote in message
news:2AEB4663-795F-48F6-BA68-DEAA0D1C4BD8@.microsoft.com...
> Mike
>
<SNIP>
> Even if you don't have the budget for a 2 proc SQL license I would always
> recommend purchasing a second physical processor and configuring SQL
> Server
> to only use one of the processors. This leaves the second processor free
> for
> other applications, OS, remote administration.
>
</SNIP>
Please be careful about that because, in general, you have to purchase a
processor license for every processor that the OS can see, whether that
processor is configured to run SQL Server or not. For more information,
please refer to the SQL Server 2000 Licensing FAQ web page at
http://www.microsoft.com/sql/howtobuy/faq.asp. The third question under
Licensing is reproduced below for your benefit.
Q. Do I have to license all of the processors in a server?
A. You only have to acquire licenses for processors that are
accessible to any copy of the operating system upon which SQL Server 2000 is
installed.
Sincerely,
Stephen Dybing
This posting is provided "AS IS" with no warranties, and confers no rights.
begin 666 ts.gif
J1TE&.#EA`0`!`( ``````/___R'Y! $`````+ `````!``$```(!1 `[
`
end
|||"Richard Yeo" <RichardYeo@.discussions.microsoft.com> wrote in message
news:2AEB4663-795F-48F6-BA68-DEAA0D1C4BD8@.microsoft.com...
> Mike
> Have you implemented the /3GB switch in the OS boot.ini? If not by default
> 2GB is set aside for apps and 2GB for OS. By using this switch 3GB is set
> aside for apps and 1GB for OS. This probably explains why SQL Server is
only
> using 1.7GB RAM. It would probably be using more if you had the /3GB
switch
> set. Try setting /3GB switch, instruct SQL Server to use Physical RAM, and
> set max memory for SQL Server to be 2.5GB. This will leave 0.5GB for other
> apps including MSSearch (I think), file system caching, etc.
> Even if you don't have the budget for a 2 proc SQL license I would always
> recommend purchasing a second physical processor and configuring SQL
Server
> to only use one of the processors. This leaves the second processor free
for
> other applications, OS, remote administration.
Unless thigns have changed, last I knew, MS didn't allow that.
|||Stephen
Thanks for clarifying that.
Obviously there is a difference between what the product is capable of and
what the licensing folk think the product is or should be capable of.
In this instance I feel the Product Team got it right. In the real world
people often have to run more than one application on a server and should not
be forced to buy additional licenses.
With the move in the industry to consolidate this could a lot of unnecessary
expense.
Lets hope this license issue is addressed in the next release of SQL Server
2005.
Rich
"Stephen Dybing [MSFT]" wrote:
> "Richard Yeo" <RichardYeo@.discussions.microsoft.com> wrote in message
> news:2AEB4663-795F-48F6-BA68-DEAA0D1C4BD8@.microsoft.com...
> <SNIP>
> </SNIP>
> Please be careful about that because, in general, you have to purchase a
> processor license for every processor that the OS can see, whether that
> processor is configured to run SQL Server or not. For more information,
> please refer to the SQL Server 2000 Licensing FAQ web page at
> http://www.microsoft.com/sql/howtobuy/faq.asp. The third question under
> Licensing is reproduced below for your benefit.
> Q. Do I have to license all of the processors in a server?
> A. You only have to acquire licenses for processors that are
> accessible to any copy of the operating system upon which SQL Server 2000 is
> installed.
>
>
> --
> Sincerely,
> Stephen Dybing
> This posting is provided "AS IS" with no warranties, and confers no rights
full server backup
is that possible to make a full server backup which include all databases in
the current server , DTS packages and everything else in one single backup
file?No. The backup restore mechanism works at database level, not server level.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Oren101" <Oren101@.discussions.microsoft.com> wrote in message
news:AAA53155-0EED-47FA-8CCD-08BF0DF3A3E9@.microsoft.com...
is that possible to make a full server backup which include all databases in
the current server , DTS packages and everything else in one single backup
file?|||Hi,Vyas
He can use undocumented stored procedure to do that
EXEC sp_MSforeachdb
'IF N''?'' NOT IN(''model'' ,''tempdb'', ''distribution'', ''Northwind'',
''pubs'')
BEGIN
DECLARE @.sql varchar(1000);
set @.sql = ''BACKUP DATABASE '' + (''?'') + '' TO DISK =''''D:\MSSQL2000\MSSQL\BACKUP\'' + (''?'') + ''.bak'''' WITH INIT''
EXEC (@.sql)
END'
BTW. What is about .NET examples on your site that you have promised us some
time ago :-)
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:ubAFNs2kFHA.2472@.TK2MSFTNGP15.phx.gbl...
> No. The backup restore mechanism works at database level, not server
> level.
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "Oren101" <Oren101@.discussions.microsoft.com> wrote in message
> news:AAA53155-0EED-47FA-8CCD-08BF0DF3A3E9@.microsoft.com...
> is that possible to make a full server backup which include all databases
> in
> the current server , DTS packages and everything else in one single backup
> file?
>|||Uri, your script is still creating multiple backup files, which is not what
the OP wanted. Anyway, you could still write multiple database backups to a
single backup file though.
Anyway, I just wanted to point out that you cannot run a single command, and
create a single backup with everything that is there on the server.
Regarding the .Net code samples, hopefully before end of this year :-)
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uqh7DY3kFHA.3256@.TK2MSFTNGP12.phx.gbl...
Hi,Vyas
He can use undocumented stored procedure to do that
EXEC sp_MSforeachdb
'IF N''?'' NOT IN(''model'' ,''tempdb'', ''distribution'', ''Northwind'',
''pubs'')
BEGIN
DECLARE @.sql varchar(1000);
set @.sql = ''BACKUP DATABASE '' + (''?'') + '' TO DISK =''''D:\MSSQL2000\MSSQL\BACKUP\'' + (''?'') + ''.bak'''' WITH INIT''
EXEC (@.sql)
END'
BTW. What is about .NET examples on your site that you have promised us some
time ago :-)
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:ubAFNs2kFHA.2472@.TK2MSFTNGP15.phx.gbl...
> No. The backup restore mechanism works at database level, not server
> level.
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "Oren101" <Oren101@.discussions.microsoft.com> wrote in message
> news:AAA53155-0EED-47FA-8CCD-08BF0DF3A3E9@.microsoft.com...
> is that possible to make a full server backup which include all databases
> in
> the current server , DTS packages and everything else in one single backup
> file?
>|||is that code will backup the DTS packeges too?
Regards.
OreN101
"Uri Dimant" wrote:
> Hi,Vyas
> He can use undocumented stored procedure to do that
>
> EXEC sp_MSforeachdb
> 'IF N''?'' NOT IN(''model'' ,''tempdb'', ''distribution'', ''Northwind'',
> ''pubs'')
> BEGIN
> DECLARE @.sql varchar(1000);
> set @.sql = ''BACKUP DATABASE '' + (''?'') + '' TO DISK => ''''D:\MSSQL2000\MSSQL\BACKUP\'' + (''?'') + ''.bak'''' WITH INIT''
> EXEC (@.sql)
> END'
> BTW. What is about .NET examples on your site that you have promised us some
> time ago :-)
>
> "Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
> news:ubAFNs2kFHA.2472@.TK2MSFTNGP15.phx.gbl...
> > No. The backup restore mechanism works at database level, not server
> > level.
> > --
> > HTH,
> > Vyas, MVP (SQL Server)
> > SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
> >
> >
> > "Oren101" <Oren101@.discussions.microsoft.com> wrote in message
> > news:AAA53155-0EED-47FA-8CCD-08BF0DF3A3E9@.microsoft.com...
> > is that possible to make a full server backup which include all databases
> > in
> > the current server , DTS packages and everything else in one single backup
> > file?
> >
> >
>
>|||Yes, if your DTS packages are stored in SQL Server.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Oren101" <Oren101@.discussions.microsoft.com> wrote in message
news:0937D676-861F-4FFB-9174-156131BFC4E8@.microsoft.com...
is that code will backup the DTS packeges too?
Regards.
OreN101
"Uri Dimant" wrote:
> Hi,Vyas
> He can use undocumented stored procedure to do that
>
> EXEC sp_MSforeachdb
> 'IF N''?'' NOT IN(''model'' ,''tempdb'', ''distribution'', ''Northwind'',
> ''pubs'')
> BEGIN
> DECLARE @.sql varchar(1000);
> set @.sql = ''BACKUP DATABASE '' + (''?'') + '' TO DISK => ''''D:\MSSQL2000\MSSQL\BACKUP\'' + (''?'') + ''.bak'''' WITH INIT''
> EXEC (@.sql)
> END'
> BTW. What is about .NET examples on your site that you have promised us
some
> time ago :-)
>
> "Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
> news:ubAFNs2kFHA.2472@.TK2MSFTNGP15.phx.gbl...
> > No. The backup restore mechanism works at database level, not server
> > level.
> > --
> > HTH,
> > Vyas, MVP (SQL Server)
> > SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
> >
> >
> > "Oren101" <Oren101@.discussions.microsoft.com> wrote in message
> > news:AAA53155-0EED-47FA-8CCD-08BF0DF3A3E9@.microsoft.com...
> > is that possible to make a full server backup which include all
databases
> > in
> > the current server , DTS packages and everything else in one single
backup
> > file?
> >
> >
>
>|||how come? the script above make backups to the databases in the sql server
instance ,not to any other objects(like DTS)?
"Oren101" wrote:
> is that code will backup the DTS packeges too?
> Regards.
> OreN101
> "Uri Dimant" wrote:
> > Hi,Vyas
> > He can use undocumented stored procedure to do that
> >
> >
> > EXEC sp_MSforeachdb
> > 'IF N''?'' NOT IN(''model'' ,''tempdb'', ''distribution'', ''Northwind'',
> > ''pubs'')
> > BEGIN
> > DECLARE @.sql varchar(1000);
> > set @.sql = ''BACKUP DATABASE '' + (''?'') + '' TO DISK => > ''''D:\MSSQL2000\MSSQL\BACKUP\'' + (''?'') + ''.bak'''' WITH INIT''
> > EXEC (@.sql)
> > END'
> >
> > BTW. What is about .NET examples on your site that you have promised us some
> > time ago :-)
> >
> >
> >
> > "Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
> > news:ubAFNs2kFHA.2472@.TK2MSFTNGP15.phx.gbl...
> > > No. The backup restore mechanism works at database level, not server
> > > level.
> > > --
> > > HTH,
> > > Vyas, MVP (SQL Server)
> > > SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
> > >
> > >
> > > "Oren101" <Oren101@.discussions.microsoft.com> wrote in message
> > > news:AAA53155-0EED-47FA-8CCD-08BF0DF3A3E9@.microsoft.com...
> > > is that possible to make a full server backup which include all databases
> > > in
> > > the current server , DTS packages and everything else in one single backup
> > > file?
> > >
> > >
> >
> >
> >|||I agree - how does this grab the DTS packages? The DTS packages are
saved to the server - not to a database. The backup command only
references a specific database.|||If you save DTS packages to SQL Server, then they get stored in the msdb
database.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
<unc27932@.yahoo.com> wrote in message
news:1122560371.634115.202710@.z14g2000cwz.googlegroups.com...
I agree - how does this grab the DTS packages? The DTS packages are
saved to the server - not to a database. The backup command only
references a specific database.|||Well, now there's something I didn't realize. These newsgroups are
helpful aren't they. :)|||where exactly can i c it un the msdb?
"Narayana Vyas Kondreddi" wrote:
> If you save DTS packages to SQL Server, then they get stored in the msdb
> database.
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> <unc27932@.yahoo.com> wrote in message
> news:1122560371.634115.202710@.z14g2000cwz.googlegroups.com...
> I agree - how does this grab the DTS packages? The DTS packages are
> saved to the server - not to a database. The backup command only
> references a specific database.
>
>|||sysdtspackages
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Oren101" <Oren101@.discussions.microsoft.com> wrote in message
news:7C78600B-9414-48A8-9B9B-2E8312D9A223@.microsoft.com...
> where exactly can i c it un the msdb?
> "Narayana Vyas Kondreddi" wrote:
>> If you save DTS packages to SQL Server, then they get stored in the msdb
>> database.
>> --
>> HTH,
>> Vyas, MVP (SQL Server)
>> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>>
>> <unc27932@.yahoo.com> wrote in message
>> news:1122560371.634115.202710@.z14g2000cwz.googlegroups.com...
>> I agree - how does this grab the DTS packages? The DTS packages are
>> saved to the server - not to a database. The backup command only
>> references a specific database.
>>|||sysdtspackages
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Oren101" <Oren101@.discussions.microsoft.com> wrote in message
news:7C78600B-9414-48A8-9B9B-2E8312D9A223@.microsoft.com...
where exactly can i c it un the msdb?
"Narayana Vyas Kondreddi" wrote:
> If you save DTS packages to SQL Server, then they get stored in the msdb
> database.
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> <unc27932@.yahoo.com> wrote in message
> news:1122560371.634115.202710@.z14g2000cwz.googlegroups.com...
> I agree - how does this grab the DTS packages? The DTS packages are
> saved to the server - not to a database. The backup command only
> references a specific database.
>
>|||The closest thing to that would be to stop the server and to zip all the
database files (mdf and ldf) to 1 archive.
--
Cheers,
Wojtek
"Oren101" <Oren101@.discussions.microsoft.com> wrote in message
news:AAA53155-0EED-47FA-8CCD-08BF0DF3A3E9@.microsoft.com...
> is that possible to make a full server backup which include all databases
> in
> the current server , DTS packages and everything else in one single backup
> file?|||It would be much easier and supported to just do regular database backups of each database, all
going to the same backup file.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Wojtek Garwol" <garwol@.usunto.poczta.fm> wrote in message news:dcb893$r69$1@.nemesis.news.tpi.pl...
> The closest thing to that would be to stop the server and to zip all the database files (mdf and
> ldf) to 1 archive.
> --
> Cheers,
> Wojtek
>
> "Oren101" <Oren101@.discussions.microsoft.com> wrote in message
> news:AAA53155-0EED-47FA-8CCD-08BF0DF3A3E9@.microsoft.com...
>> is that possible to make a full server backup which include all databases in
>> the current server , DTS packages and everything else in one single backup
>> file?
>
the current server , DTS packages and everything else in one single backup
file?No. The backup restore mechanism works at database level, not server level.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Oren101" <Oren101@.discussions.microsoft.com> wrote in message
news:AAA53155-0EED-47FA-8CCD-08BF0DF3A3E9@.microsoft.com...
is that possible to make a full server backup which include all databases in
the current server , DTS packages and everything else in one single backup
file?|||Hi,Vyas
He can use undocumented stored procedure to do that
EXEC sp_MSforeachdb
'IF N''?'' NOT IN(''model'' ,''tempdb'', ''distribution'', ''Northwind'',
''pubs'')
BEGIN
DECLARE @.sql varchar(1000);
set @.sql = ''BACKUP DATABASE '' + (''?'') + '' TO DISK =''''D:\MSSQL2000\MSSQL\BACKUP\'' + (''?'') + ''.bak'''' WITH INIT''
EXEC (@.sql)
END'
BTW. What is about .NET examples on your site that you have promised us some
time ago :-)
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:ubAFNs2kFHA.2472@.TK2MSFTNGP15.phx.gbl...
> No. The backup restore mechanism works at database level, not server
> level.
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "Oren101" <Oren101@.discussions.microsoft.com> wrote in message
> news:AAA53155-0EED-47FA-8CCD-08BF0DF3A3E9@.microsoft.com...
> is that possible to make a full server backup which include all databases
> in
> the current server , DTS packages and everything else in one single backup
> file?
>|||Uri, your script is still creating multiple backup files, which is not what
the OP wanted. Anyway, you could still write multiple database backups to a
single backup file though.
Anyway, I just wanted to point out that you cannot run a single command, and
create a single backup with everything that is there on the server.
Regarding the .Net code samples, hopefully before end of this year :-)
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uqh7DY3kFHA.3256@.TK2MSFTNGP12.phx.gbl...
Hi,Vyas
He can use undocumented stored procedure to do that
EXEC sp_MSforeachdb
'IF N''?'' NOT IN(''model'' ,''tempdb'', ''distribution'', ''Northwind'',
''pubs'')
BEGIN
DECLARE @.sql varchar(1000);
set @.sql = ''BACKUP DATABASE '' + (''?'') + '' TO DISK =''''D:\MSSQL2000\MSSQL\BACKUP\'' + (''?'') + ''.bak'''' WITH INIT''
EXEC (@.sql)
END'
BTW. What is about .NET examples on your site that you have promised us some
time ago :-)
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:ubAFNs2kFHA.2472@.TK2MSFTNGP15.phx.gbl...
> No. The backup restore mechanism works at database level, not server
> level.
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "Oren101" <Oren101@.discussions.microsoft.com> wrote in message
> news:AAA53155-0EED-47FA-8CCD-08BF0DF3A3E9@.microsoft.com...
> is that possible to make a full server backup which include all databases
> in
> the current server , DTS packages and everything else in one single backup
> file?
>|||is that code will backup the DTS packeges too?
Regards.
OreN101
"Uri Dimant" wrote:
> Hi,Vyas
> He can use undocumented stored procedure to do that
>
> EXEC sp_MSforeachdb
> 'IF N''?'' NOT IN(''model'' ,''tempdb'', ''distribution'', ''Northwind'',
> ''pubs'')
> BEGIN
> DECLARE @.sql varchar(1000);
> set @.sql = ''BACKUP DATABASE '' + (''?'') + '' TO DISK => ''''D:\MSSQL2000\MSSQL\BACKUP\'' + (''?'') + ''.bak'''' WITH INIT''
> EXEC (@.sql)
> END'
> BTW. What is about .NET examples on your site that you have promised us some
> time ago :-)
>
> "Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
> news:ubAFNs2kFHA.2472@.TK2MSFTNGP15.phx.gbl...
> > No. The backup restore mechanism works at database level, not server
> > level.
> > --
> > HTH,
> > Vyas, MVP (SQL Server)
> > SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
> >
> >
> > "Oren101" <Oren101@.discussions.microsoft.com> wrote in message
> > news:AAA53155-0EED-47FA-8CCD-08BF0DF3A3E9@.microsoft.com...
> > is that possible to make a full server backup which include all databases
> > in
> > the current server , DTS packages and everything else in one single backup
> > file?
> >
> >
>
>|||Yes, if your DTS packages are stored in SQL Server.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Oren101" <Oren101@.discussions.microsoft.com> wrote in message
news:0937D676-861F-4FFB-9174-156131BFC4E8@.microsoft.com...
is that code will backup the DTS packeges too?
Regards.
OreN101
"Uri Dimant" wrote:
> Hi,Vyas
> He can use undocumented stored procedure to do that
>
> EXEC sp_MSforeachdb
> 'IF N''?'' NOT IN(''model'' ,''tempdb'', ''distribution'', ''Northwind'',
> ''pubs'')
> BEGIN
> DECLARE @.sql varchar(1000);
> set @.sql = ''BACKUP DATABASE '' + (''?'') + '' TO DISK => ''''D:\MSSQL2000\MSSQL\BACKUP\'' + (''?'') + ''.bak'''' WITH INIT''
> EXEC (@.sql)
> END'
> BTW. What is about .NET examples on your site that you have promised us
some
> time ago :-)
>
> "Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
> news:ubAFNs2kFHA.2472@.TK2MSFTNGP15.phx.gbl...
> > No. The backup restore mechanism works at database level, not server
> > level.
> > --
> > HTH,
> > Vyas, MVP (SQL Server)
> > SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
> >
> >
> > "Oren101" <Oren101@.discussions.microsoft.com> wrote in message
> > news:AAA53155-0EED-47FA-8CCD-08BF0DF3A3E9@.microsoft.com...
> > is that possible to make a full server backup which include all
databases
> > in
> > the current server , DTS packages and everything else in one single
backup
> > file?
> >
> >
>
>|||how come? the script above make backups to the databases in the sql server
instance ,not to any other objects(like DTS)?
"Oren101" wrote:
> is that code will backup the DTS packeges too?
> Regards.
> OreN101
> "Uri Dimant" wrote:
> > Hi,Vyas
> > He can use undocumented stored procedure to do that
> >
> >
> > EXEC sp_MSforeachdb
> > 'IF N''?'' NOT IN(''model'' ,''tempdb'', ''distribution'', ''Northwind'',
> > ''pubs'')
> > BEGIN
> > DECLARE @.sql varchar(1000);
> > set @.sql = ''BACKUP DATABASE '' + (''?'') + '' TO DISK => > ''''D:\MSSQL2000\MSSQL\BACKUP\'' + (''?'') + ''.bak'''' WITH INIT''
> > EXEC (@.sql)
> > END'
> >
> > BTW. What is about .NET examples on your site that you have promised us some
> > time ago :-)
> >
> >
> >
> > "Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
> > news:ubAFNs2kFHA.2472@.TK2MSFTNGP15.phx.gbl...
> > > No. The backup restore mechanism works at database level, not server
> > > level.
> > > --
> > > HTH,
> > > Vyas, MVP (SQL Server)
> > > SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
> > >
> > >
> > > "Oren101" <Oren101@.discussions.microsoft.com> wrote in message
> > > news:AAA53155-0EED-47FA-8CCD-08BF0DF3A3E9@.microsoft.com...
> > > is that possible to make a full server backup which include all databases
> > > in
> > > the current server , DTS packages and everything else in one single backup
> > > file?
> > >
> > >
> >
> >
> >|||I agree - how does this grab the DTS packages? The DTS packages are
saved to the server - not to a database. The backup command only
references a specific database.|||If you save DTS packages to SQL Server, then they get stored in the msdb
database.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
<unc27932@.yahoo.com> wrote in message
news:1122560371.634115.202710@.z14g2000cwz.googlegroups.com...
I agree - how does this grab the DTS packages? The DTS packages are
saved to the server - not to a database. The backup command only
references a specific database.|||Well, now there's something I didn't realize. These newsgroups are
helpful aren't they. :)|||where exactly can i c it un the msdb?
"Narayana Vyas Kondreddi" wrote:
> If you save DTS packages to SQL Server, then they get stored in the msdb
> database.
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> <unc27932@.yahoo.com> wrote in message
> news:1122560371.634115.202710@.z14g2000cwz.googlegroups.com...
> I agree - how does this grab the DTS packages? The DTS packages are
> saved to the server - not to a database. The backup command only
> references a specific database.
>
>|||sysdtspackages
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Oren101" <Oren101@.discussions.microsoft.com> wrote in message
news:7C78600B-9414-48A8-9B9B-2E8312D9A223@.microsoft.com...
> where exactly can i c it un the msdb?
> "Narayana Vyas Kondreddi" wrote:
>> If you save DTS packages to SQL Server, then they get stored in the msdb
>> database.
>> --
>> HTH,
>> Vyas, MVP (SQL Server)
>> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>>
>> <unc27932@.yahoo.com> wrote in message
>> news:1122560371.634115.202710@.z14g2000cwz.googlegroups.com...
>> I agree - how does this grab the DTS packages? The DTS packages are
>> saved to the server - not to a database. The backup command only
>> references a specific database.
>>|||sysdtspackages
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Oren101" <Oren101@.discussions.microsoft.com> wrote in message
news:7C78600B-9414-48A8-9B9B-2E8312D9A223@.microsoft.com...
where exactly can i c it un the msdb?
"Narayana Vyas Kondreddi" wrote:
> If you save DTS packages to SQL Server, then they get stored in the msdb
> database.
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> <unc27932@.yahoo.com> wrote in message
> news:1122560371.634115.202710@.z14g2000cwz.googlegroups.com...
> I agree - how does this grab the DTS packages? The DTS packages are
> saved to the server - not to a database. The backup command only
> references a specific database.
>
>|||The closest thing to that would be to stop the server and to zip all the
database files (mdf and ldf) to 1 archive.
--
Cheers,
Wojtek
"Oren101" <Oren101@.discussions.microsoft.com> wrote in message
news:AAA53155-0EED-47FA-8CCD-08BF0DF3A3E9@.microsoft.com...
> is that possible to make a full server backup which include all databases
> in
> the current server , DTS packages and everything else in one single backup
> file?|||It would be much easier and supported to just do regular database backups of each database, all
going to the same backup file.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Wojtek Garwol" <garwol@.usunto.poczta.fm> wrote in message news:dcb893$r69$1@.nemesis.news.tpi.pl...
> The closest thing to that would be to stop the server and to zip all the database files (mdf and
> ldf) to 1 archive.
> --
> Cheers,
> Wojtek
>
> "Oren101" <Oren101@.discussions.microsoft.com> wrote in message
> news:AAA53155-0EED-47FA-8CCD-08BF0DF3A3E9@.microsoft.com...
>> is that possible to make a full server backup which include all databases in
>> the current server , DTS packages and everything else in one single backup
>> file?
>
full server backup
is that possible to make a full server backup which include all databases in
the current server , DTS packages and everything else in one single backup
file?No. The backup restore mechanism works at database level, not server level.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Oren101" <Oren101@.discussions.microsoft.com> wrote in message
news:AAA53155-0EED-47FA-8CCD-08BF0DF3A3E9@.microsoft.com...
is that possible to make a full server backup which include all databases in
the current server , DTS packages and everything else in one single backup
file?|||Hi,Vyas
He can use undocumented stored procedure to do that
EXEC sp_MSforeachdb
'IF N''?'' NOT IN(''model'' ,''tempdb'', ''distribution'', ''Northwind'',
''pubs'')
BEGIN
DECLARE @.sql varchar(1000);
set @.sql = ''BACKUP DATABASE '' + (''?'') + '' TO DISK =
''''D:\MSSQL2000\MSSQL\BACKUP'' + (''?'') + ''.bak'''' WITH INIT''
EXEC (@.sql)
END'
BTW. What is about .NET examples on your site that you have promised us some
time ago :-)
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:ubAFNs2kFHA.2472@.TK2MSFTNGP15.phx.gbl...
> No. The backup restore mechanism works at database level, not server
> level.
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "Oren101" <Oren101@.discussions.microsoft.com> wrote in message
> news:AAA53155-0EED-47FA-8CCD-08BF0DF3A3E9@.microsoft.com...
> is that possible to make a full server backup which include all databases
> in
> the current server , DTS packages and everything else in one single backup
> file?
>|||Uri, your script is still creating multiple backup files, which is not what
the OP wanted. Anyway, you could still write multiple database backups to a
single backup file though.
Anyway, I just wanted to point out that you cannot run a single command, and
create a single backup with everything that is there on the server.
Regarding the .Net code samples, hopefully before end of this year :-)
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uqh7DY3kFHA.3256@.TK2MSFTNGP12.phx.gbl...
Hi,Vyas
He can use undocumented stored procedure to do that
EXEC sp_MSforeachdb
'IF N''?'' NOT IN(''model'' ,''tempdb'', ''distribution'', ''Northwind'',
''pubs'')
BEGIN
DECLARE @.sql varchar(1000);
set @.sql = ''BACKUP DATABASE '' + (''?'') + '' TO DISK =
''''D:\MSSQL2000\MSSQL\BACKUP'' + (''?'') + ''.bak'''' WITH INIT''
EXEC (@.sql)
END'
BTW. What is about .NET examples on your site that you have promised us some
time ago :-)
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:ubAFNs2kFHA.2472@.TK2MSFTNGP15.phx.gbl...
> No. The backup restore mechanism works at database level, not server
> level.
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "Oren101" <Oren101@.discussions.microsoft.com> wrote in message
> news:AAA53155-0EED-47FA-8CCD-08BF0DF3A3E9@.microsoft.com...
> is that possible to make a full server backup which include all databases
> in
> the current server , DTS packages and everything else in one single backup
> file?
>|||is that code will backup the DTS packeges too?
Regards.
OreN101
"Uri Dimant" wrote:
> Hi,Vyas
> He can use undocumented stored procedure to do that
>
> EXEC sp_MSforeachdb
> 'IF N''?'' NOT IN(''model'' ,''tempdb'', ''distribution'', ''Northwind'',
> ''pubs'')
> BEGIN
> DECLARE @.sql varchar(1000);
> set @.sql = ''BACKUP DATABASE '' + (''?'') + '' TO DISK =
> ''''D:\MSSQL2000\MSSQL\BACKUP'' + (''?'') + ''.bak'''' WITH INIT''
> EXEC (@.sql)
> END'
> BTW. What is about .NET examples on your site that you have promised us so
me
> time ago :-)
>
> "Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
> news:ubAFNs2kFHA.2472@.TK2MSFTNGP15.phx.gbl...
>
>|||Yes, if your DTS packages are stored in SQL Server.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Oren101" <Oren101@.discussions.microsoft.com> wrote in message
news:0937D676-861F-4FFB-9174-156131BFC4E8@.microsoft.com...
is that code will backup the DTS packeges too?
Regards.
OreN101
"Uri Dimant" wrote:
> Hi,Vyas
> He can use undocumented stored procedure to do that
>
> EXEC sp_MSforeachdb
> 'IF N''?'' NOT IN(''model'' ,''tempdb'', ''distribution'', ''Northwind'',
> ''pubs'')
> BEGIN
> DECLARE @.sql varchar(1000);
> set @.sql = ''BACKUP DATABASE '' + (''?'') + '' TO DISK =
> ''''D:\MSSQL2000\MSSQL\BACKUP'' + (''?'') + ''.bak'''' WITH INIT''
> EXEC (@.sql)
> END'
> BTW. What is about .NET examples on your site that you have promised us
some
> time ago :-)
>
> "Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
> news:ubAFNs2kFHA.2472@.TK2MSFTNGP15.phx.gbl...
databases[vbcol=seagreen]
backup[vbcol=seagreen]
>
>|||how come? the script above make backups to the databases in the sql server
instance ,not to any other objects(like DTS)?
"Oren101" wrote:
[vbcol=seagreen]
> is that code will backup the DTS packeges too?
> Regards.
> OreN101
> "Uri Dimant" wrote:
>|||where exactly can i c it un the msdb?
"Narayana Vyas Kondreddi" wrote:
> If you save DTS packages to SQL Server, then they get stored in the msdb
> database.
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> <unc27932@.yahoo.com> wrote in message
> news:1122560371.634115.202710@.z14g2000cwz.googlegroups.com...
> I agree - how does this grab the DTS packages? The DTS packages are
> saved to the server - not to a database. The backup command only
> references a specific database.
>
>|||sysdtspackages
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Oren101" <Oren101@.discussions.microsoft.com> wrote in message
news:7C78600B-9414-48A8-9B9B-2E8312D9A223@.microsoft.com...[vbcol=seagreen]
> where exactly can i c it un the msdb?
> "Narayana Vyas Kondreddi" wrote:
>|||sysdtspackages
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Oren101" <Oren101@.discussions.microsoft.com> wrote in message
news:7C78600B-9414-48A8-9B9B-2E8312D9A223@.microsoft.com...
where exactly can i c it un the msdb?
"Narayana Vyas Kondreddi" wrote:
> If you save DTS packages to SQL Server, then they get stored in the msdb
> database.
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> <unc27932@.yahoo.com> wrote in message
> news:1122560371.634115.202710@.z14g2000cwz.googlegroups.com...
> I agree - how does this grab the DTS packages? The DTS packages are
> saved to the server - not to a database. The backup command only
> references a specific database.
>
>
the current server , DTS packages and everything else in one single backup
file?No. The backup restore mechanism works at database level, not server level.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Oren101" <Oren101@.discussions.microsoft.com> wrote in message
news:AAA53155-0EED-47FA-8CCD-08BF0DF3A3E9@.microsoft.com...
is that possible to make a full server backup which include all databases in
the current server , DTS packages and everything else in one single backup
file?|||Hi,Vyas
He can use undocumented stored procedure to do that
EXEC sp_MSforeachdb
'IF N''?'' NOT IN(''model'' ,''tempdb'', ''distribution'', ''Northwind'',
''pubs'')
BEGIN
DECLARE @.sql varchar(1000);
set @.sql = ''BACKUP DATABASE '' + (''?'') + '' TO DISK =
''''D:\MSSQL2000\MSSQL\BACKUP'' + (''?'') + ''.bak'''' WITH INIT''
EXEC (@.sql)
END'
BTW. What is about .NET examples on your site that you have promised us some
time ago :-)
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:ubAFNs2kFHA.2472@.TK2MSFTNGP15.phx.gbl...
> No. The backup restore mechanism works at database level, not server
> level.
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "Oren101" <Oren101@.discussions.microsoft.com> wrote in message
> news:AAA53155-0EED-47FA-8CCD-08BF0DF3A3E9@.microsoft.com...
> is that possible to make a full server backup which include all databases
> in
> the current server , DTS packages and everything else in one single backup
> file?
>|||Uri, your script is still creating multiple backup files, which is not what
the OP wanted. Anyway, you could still write multiple database backups to a
single backup file though.
Anyway, I just wanted to point out that you cannot run a single command, and
create a single backup with everything that is there on the server.
Regarding the .Net code samples, hopefully before end of this year :-)
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uqh7DY3kFHA.3256@.TK2MSFTNGP12.phx.gbl...
Hi,Vyas
He can use undocumented stored procedure to do that
EXEC sp_MSforeachdb
'IF N''?'' NOT IN(''model'' ,''tempdb'', ''distribution'', ''Northwind'',
''pubs'')
BEGIN
DECLARE @.sql varchar(1000);
set @.sql = ''BACKUP DATABASE '' + (''?'') + '' TO DISK =
''''D:\MSSQL2000\MSSQL\BACKUP'' + (''?'') + ''.bak'''' WITH INIT''
EXEC (@.sql)
END'
BTW. What is about .NET examples on your site that you have promised us some
time ago :-)
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:ubAFNs2kFHA.2472@.TK2MSFTNGP15.phx.gbl...
> No. The backup restore mechanism works at database level, not server
> level.
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "Oren101" <Oren101@.discussions.microsoft.com> wrote in message
> news:AAA53155-0EED-47FA-8CCD-08BF0DF3A3E9@.microsoft.com...
> is that possible to make a full server backup which include all databases
> in
> the current server , DTS packages and everything else in one single backup
> file?
>|||is that code will backup the DTS packeges too?
Regards.
OreN101
"Uri Dimant" wrote:
> Hi,Vyas
> He can use undocumented stored procedure to do that
>
> EXEC sp_MSforeachdb
> 'IF N''?'' NOT IN(''model'' ,''tempdb'', ''distribution'', ''Northwind'',
> ''pubs'')
> BEGIN
> DECLARE @.sql varchar(1000);
> set @.sql = ''BACKUP DATABASE '' + (''?'') + '' TO DISK =
> ''''D:\MSSQL2000\MSSQL\BACKUP'' + (''?'') + ''.bak'''' WITH INIT''
> EXEC (@.sql)
> END'
> BTW. What is about .NET examples on your site that you have promised us so
me
> time ago :-)
>
> "Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
> news:ubAFNs2kFHA.2472@.TK2MSFTNGP15.phx.gbl...
>
>|||Yes, if your DTS packages are stored in SQL Server.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Oren101" <Oren101@.discussions.microsoft.com> wrote in message
news:0937D676-861F-4FFB-9174-156131BFC4E8@.microsoft.com...
is that code will backup the DTS packeges too?
Regards.
OreN101
"Uri Dimant" wrote:
> Hi,Vyas
> He can use undocumented stored procedure to do that
>
> EXEC sp_MSforeachdb
> 'IF N''?'' NOT IN(''model'' ,''tempdb'', ''distribution'', ''Northwind'',
> ''pubs'')
> BEGIN
> DECLARE @.sql varchar(1000);
> set @.sql = ''BACKUP DATABASE '' + (''?'') + '' TO DISK =
> ''''D:\MSSQL2000\MSSQL\BACKUP'' + (''?'') + ''.bak'''' WITH INIT''
> EXEC (@.sql)
> END'
> BTW. What is about .NET examples on your site that you have promised us
some
> time ago :-)
>
> "Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
> news:ubAFNs2kFHA.2472@.TK2MSFTNGP15.phx.gbl...
databases[vbcol=seagreen]
backup[vbcol=seagreen]
>
>|||how come? the script above make backups to the databases in the sql server
instance ,not to any other objects(like DTS)?
"Oren101" wrote:
[vbcol=seagreen]
> is that code will backup the DTS packeges too?
> Regards.
> OreN101
> "Uri Dimant" wrote:
>|||where exactly can i c it un the msdb?
"Narayana Vyas Kondreddi" wrote:
> If you save DTS packages to SQL Server, then they get stored in the msdb
> database.
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> <unc27932@.yahoo.com> wrote in message
> news:1122560371.634115.202710@.z14g2000cwz.googlegroups.com...
> I agree - how does this grab the DTS packages? The DTS packages are
> saved to the server - not to a database. The backup command only
> references a specific database.
>
>|||sysdtspackages
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Oren101" <Oren101@.discussions.microsoft.com> wrote in message
news:7C78600B-9414-48A8-9B9B-2E8312D9A223@.microsoft.com...[vbcol=seagreen]
> where exactly can i c it un the msdb?
> "Narayana Vyas Kondreddi" wrote:
>|||sysdtspackages
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Oren101" <Oren101@.discussions.microsoft.com> wrote in message
news:7C78600B-9414-48A8-9B9B-2E8312D9A223@.microsoft.com...
where exactly can i c it un the msdb?
"Narayana Vyas Kondreddi" wrote:
> If you save DTS packages to SQL Server, then they get stored in the msdb
> database.
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> <unc27932@.yahoo.com> wrote in message
> news:1122560371.634115.202710@.z14g2000cwz.googlegroups.com...
> I agree - how does this grab the DTS packages? The DTS packages are
> saved to the server - not to a database. The backup command only
> references a specific database.
>
>
full server backup
is that possible to make a full server backup which include all databases in
the current server , DTS packages and everything else in one single backup
file?
No. The backup restore mechanism works at database level, not server level.
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Oren101" <Oren101@.discussions.microsoft.com> wrote in message
news:AAA53155-0EED-47FA-8CCD-08BF0DF3A3E9@.microsoft.com...
is that possible to make a full server backup which include all databases in
the current server , DTS packages and everything else in one single backup
file?
|||Hi,Vyas
He can use undocumented stored procedure to do that
EXEC sp_MSforeachdb
'IF N''?'' NOT IN(''model'' ,''tempdb'', ''distribution'', ''Northwind'',
''pubs'')
BEGIN
DECLARE @.sql varchar(1000);
set @.sql = ''BACKUP DATABASE '' + (''?'') + '' TO DISK =
''''D:\MSSQL2000\MSSQL\BACKUP\'' + (''?'') + ''.bak'''' WITH INIT''
EXEC (@.sql)
END'
BTW. What is about .NET examples on your site that you have promised us some
time ago :-)
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:ubAFNs2kFHA.2472@.TK2MSFTNGP15.phx.gbl...
> No. The backup restore mechanism works at database level, not server
> level.
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "Oren101" <Oren101@.discussions.microsoft.com> wrote in message
> news:AAA53155-0EED-47FA-8CCD-08BF0DF3A3E9@.microsoft.com...
> is that possible to make a full server backup which include all databases
> in
> the current server , DTS packages and everything else in one single backup
> file?
>
|||Uri, your script is still creating multiple backup files, which is not what
the OP wanted. Anyway, you could still write multiple database backups to a
single backup file though.
Anyway, I just wanted to point out that you cannot run a single command, and
create a single backup with everything that is there on the server.
Regarding the .Net code samples, hopefully before end of this year :-)
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uqh7DY3kFHA.3256@.TK2MSFTNGP12.phx.gbl...
Hi,Vyas
He can use undocumented stored procedure to do that
EXEC sp_MSforeachdb
'IF N''?'' NOT IN(''model'' ,''tempdb'', ''distribution'', ''Northwind'',
''pubs'')
BEGIN
DECLARE @.sql varchar(1000);
set @.sql = ''BACKUP DATABASE '' + (''?'') + '' TO DISK =
''''D:\MSSQL2000\MSSQL\BACKUP\'' + (''?'') + ''.bak'''' WITH INIT''
EXEC (@.sql)
END'
BTW. What is about .NET examples on your site that you have promised us some
time ago :-)
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:ubAFNs2kFHA.2472@.TK2MSFTNGP15.phx.gbl...
> No. The backup restore mechanism works at database level, not server
> level.
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "Oren101" <Oren101@.discussions.microsoft.com> wrote in message
> news:AAA53155-0EED-47FA-8CCD-08BF0DF3A3E9@.microsoft.com...
> is that possible to make a full server backup which include all databases
> in
> the current server , DTS packages and everything else in one single backup
> file?
>
|||is that code will backup the DTS packeges too?
Regards.
OreN101
"Uri Dimant" wrote:
> Hi,Vyas
> He can use undocumented stored procedure to do that
>
> EXEC sp_MSforeachdb
> 'IF N''?'' NOT IN(''model'' ,''tempdb'', ''distribution'', ''Northwind'',
> ''pubs'')
> BEGIN
> DECLARE @.sql varchar(1000);
> set @.sql = ''BACKUP DATABASE '' + (''?'') + '' TO DISK =
> ''''D:\MSSQL2000\MSSQL\BACKUP\'' + (''?'') + ''.bak'''' WITH INIT''
> EXEC (@.sql)
> END'
> BTW. What is about .NET examples on your site that you have promised us some
> time ago :-)
>
> "Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
> news:ubAFNs2kFHA.2472@.TK2MSFTNGP15.phx.gbl...
>
>
|||Yes, if your DTS packages are stored in SQL Server.
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Oren101" <Oren101@.discussions.microsoft.com> wrote in message
news:0937D676-861F-4FFB-9174-156131BFC4E8@.microsoft.com...
is that code will backup the DTS packeges too?
Regards.
OreN101
"Uri Dimant" wrote:
> Hi,Vyas
> He can use undocumented stored procedure to do that
>
> EXEC sp_MSforeachdb
> 'IF N''?'' NOT IN(''model'' ,''tempdb'', ''distribution'', ''Northwind'',
> ''pubs'')
> BEGIN
> DECLARE @.sql varchar(1000);
> set @.sql = ''BACKUP DATABASE '' + (''?'') + '' TO DISK =
> ''''D:\MSSQL2000\MSSQL\BACKUP\'' + (''?'') + ''.bak'''' WITH INIT''
> EXEC (@.sql)
> END'
> BTW. What is about .NET examples on your site that you have promised us
some[vbcol=seagreen]
> time ago :-)
>
> "Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
> news:ubAFNs2kFHA.2472@.TK2MSFTNGP15.phx.gbl...
databases[vbcol=seagreen]
backup
>
>
|||how come? the script above make backups to the databases in the sql server
instance ,not to any other objects(like DTS)?
"Oren101" wrote:
[vbcol=seagreen]
> is that code will backup the DTS packeges too?
> Regards.
> OreN101
> "Uri Dimant" wrote:
|||I agree - how does this grab the DTS packages? The DTS packages are
saved to the server - not to a database. The backup command only
references a specific database.
|||If you save DTS packages to SQL Server, then they get stored in the msdb
database.
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
<unc27932@.yahoo.com> wrote in message
news:1122560371.634115.202710@.z14g2000cwz.googlegr oups.com...
I agree - how does this grab the DTS packages? The DTS packages are
saved to the server - not to a database. The backup command only
references a specific database.
|||Well, now there's something I didn't realize. These newsgroups are
helpful aren't they.
the current server , DTS packages and everything else in one single backup
file?
No. The backup restore mechanism works at database level, not server level.
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Oren101" <Oren101@.discussions.microsoft.com> wrote in message
news:AAA53155-0EED-47FA-8CCD-08BF0DF3A3E9@.microsoft.com...
is that possible to make a full server backup which include all databases in
the current server , DTS packages and everything else in one single backup
file?
|||Hi,Vyas
He can use undocumented stored procedure to do that
EXEC sp_MSforeachdb
'IF N''?'' NOT IN(''model'' ,''tempdb'', ''distribution'', ''Northwind'',
''pubs'')
BEGIN
DECLARE @.sql varchar(1000);
set @.sql = ''BACKUP DATABASE '' + (''?'') + '' TO DISK =
''''D:\MSSQL2000\MSSQL\BACKUP\'' + (''?'') + ''.bak'''' WITH INIT''
EXEC (@.sql)
END'
BTW. What is about .NET examples on your site that you have promised us some
time ago :-)
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:ubAFNs2kFHA.2472@.TK2MSFTNGP15.phx.gbl...
> No. The backup restore mechanism works at database level, not server
> level.
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "Oren101" <Oren101@.discussions.microsoft.com> wrote in message
> news:AAA53155-0EED-47FA-8CCD-08BF0DF3A3E9@.microsoft.com...
> is that possible to make a full server backup which include all databases
> in
> the current server , DTS packages and everything else in one single backup
> file?
>
|||Uri, your script is still creating multiple backup files, which is not what
the OP wanted. Anyway, you could still write multiple database backups to a
single backup file though.
Anyway, I just wanted to point out that you cannot run a single command, and
create a single backup with everything that is there on the server.
Regarding the .Net code samples, hopefully before end of this year :-)
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uqh7DY3kFHA.3256@.TK2MSFTNGP12.phx.gbl...
Hi,Vyas
He can use undocumented stored procedure to do that
EXEC sp_MSforeachdb
'IF N''?'' NOT IN(''model'' ,''tempdb'', ''distribution'', ''Northwind'',
''pubs'')
BEGIN
DECLARE @.sql varchar(1000);
set @.sql = ''BACKUP DATABASE '' + (''?'') + '' TO DISK =
''''D:\MSSQL2000\MSSQL\BACKUP\'' + (''?'') + ''.bak'''' WITH INIT''
EXEC (@.sql)
END'
BTW. What is about .NET examples on your site that you have promised us some
time ago :-)
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:ubAFNs2kFHA.2472@.TK2MSFTNGP15.phx.gbl...
> No. The backup restore mechanism works at database level, not server
> level.
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "Oren101" <Oren101@.discussions.microsoft.com> wrote in message
> news:AAA53155-0EED-47FA-8CCD-08BF0DF3A3E9@.microsoft.com...
> is that possible to make a full server backup which include all databases
> in
> the current server , DTS packages and everything else in one single backup
> file?
>
|||is that code will backup the DTS packeges too?
Regards.
OreN101
"Uri Dimant" wrote:
> Hi,Vyas
> He can use undocumented stored procedure to do that
>
> EXEC sp_MSforeachdb
> 'IF N''?'' NOT IN(''model'' ,''tempdb'', ''distribution'', ''Northwind'',
> ''pubs'')
> BEGIN
> DECLARE @.sql varchar(1000);
> set @.sql = ''BACKUP DATABASE '' + (''?'') + '' TO DISK =
> ''''D:\MSSQL2000\MSSQL\BACKUP\'' + (''?'') + ''.bak'''' WITH INIT''
> EXEC (@.sql)
> END'
> BTW. What is about .NET examples on your site that you have promised us some
> time ago :-)
>
> "Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
> news:ubAFNs2kFHA.2472@.TK2MSFTNGP15.phx.gbl...
>
>
|||Yes, if your DTS packages are stored in SQL Server.
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Oren101" <Oren101@.discussions.microsoft.com> wrote in message
news:0937D676-861F-4FFB-9174-156131BFC4E8@.microsoft.com...
is that code will backup the DTS packeges too?
Regards.
OreN101
"Uri Dimant" wrote:
> Hi,Vyas
> He can use undocumented stored procedure to do that
>
> EXEC sp_MSforeachdb
> 'IF N''?'' NOT IN(''model'' ,''tempdb'', ''distribution'', ''Northwind'',
> ''pubs'')
> BEGIN
> DECLARE @.sql varchar(1000);
> set @.sql = ''BACKUP DATABASE '' + (''?'') + '' TO DISK =
> ''''D:\MSSQL2000\MSSQL\BACKUP\'' + (''?'') + ''.bak'''' WITH INIT''
> EXEC (@.sql)
> END'
> BTW. What is about .NET examples on your site that you have promised us
some[vbcol=seagreen]
> time ago :-)
>
> "Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
> news:ubAFNs2kFHA.2472@.TK2MSFTNGP15.phx.gbl...
databases[vbcol=seagreen]
backup
>
>
|||how come? the script above make backups to the databases in the sql server
instance ,not to any other objects(like DTS)?
"Oren101" wrote:
[vbcol=seagreen]
> is that code will backup the DTS packeges too?
> Regards.
> OreN101
> "Uri Dimant" wrote:
|||I agree - how does this grab the DTS packages? The DTS packages are
saved to the server - not to a database. The backup command only
references a specific database.
|||If you save DTS packages to SQL Server, then they get stored in the msdb
database.
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
<unc27932@.yahoo.com> wrote in message
news:1122560371.634115.202710@.z14g2000cwz.googlegr oups.com...
I agree - how does this grab the DTS packages? The DTS packages are
saved to the server - not to a database. The backup command only
references a specific database.
|||Well, now there's something I didn't realize. These newsgroups are
helpful aren't they.
2012年2月26日星期日
full database backup
Question 1
3 GHZ CPU (Intel pentium 4) single cpu + 2 GB Memory + SCSI HDD
Database size 10 GB - How long will full database backup take if the
backup is writing a file to the hard disk (separate hard disk)
Question 2
during this full backup are users and application able to access the
database
for example
a) select records
b) insert , update, delete records
or is the database backup causing the database to be exclusively
locked up ?
Thanks in advance"newbiegca_sqlsever2000" <newbiegca_sql2000@.sbcglobal.net> wrote in message
news:d11c4aba.0409192309.414e6edb@.posting.google.c om...
> Question 1
> 3 GHZ CPU (Intel pentium 4) single cpu + 2 GB Memory + SCSI HDD
> Database size 10 GB - How long will full database backup take if the
> backup is writing a file to the hard disk (separate hard disk)
No idea - just try it and see. You can backup to multiple files/devices at
once if you need to speed things up, although this will probably only help
if the devices are physically independent drives. See "Using Multiple Media
or Devices" in Books Online.
> Question 2
> during this full backup are users and application able to access the
> database
> for example
> a) select records
> b) insert , update, delete records
> or is the database backup causing the database to be exclusively
> locked up ?
> Thanks in advance
A backup is an online operation, and users can continue to access the
database, including modifying data, while the backup is running (see "Backup
Restrictions" in Books Online). You might also want to review the whole
"Backing Up and Restoring Databases" topic for more detailed information on
backups, recovery models etc.
Simon|||Thanks Simon
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
2012年2月19日星期日
fts within a single record
I am hoping that someone has an "it's obvious" solution. I have a bunch (~100) of text fields in a single record. I want a user to be able to search all of the fields (within this single record) for a string, and to return the field number(s) of where the string was found. Is there a way to do this within the MSSQL server's built in fts catalog, (or some other product) or do I need to code this by hand? I could limit the searchable strings to a finite set, if needed.I believe that you can do this with Full Text Search. You must, however, have a finite set of columns to search upon. You must also have a primary key for the Full Text Search engine to be able to identify the specific record with matching search criteria.
You must enable install and enable Full Text Search (it is not installed by default). You must then create the Full Text Search process (I cheat like crazy and use the wizard in EM; this is NOT recommended). It is recommended that you use QA and create the processes yourself.
You will need to populate the Full Text catalogue initially and then create a schedule to update the Full Text catalogue periodically.
Regards,
Hugh Scott
Originally posted by wooliewillie
I am hoping that someone has an "it's obvious" solution. I have a bunch (~100) of text fields in a single record. I want a user to be able to search all of the fields (within this single record) for a string, and to return the field number(s) of where the string was found. Is there a way to do this within the MSSQL server's built in fts catalog, (or some other product) or do I need to code this by hand? I could limit the searchable strings to a finite set, if needed.
You must enable install and enable Full Text Search (it is not installed by default). You must then create the Full Text Search process (I cheat like crazy and use the wizard in EM; this is NOT recommended). It is recommended that you use QA and create the processes yourself.
You will need to populate the Full Text catalogue initially and then create a schedule to update the Full Text catalogue periodically.
Regards,
Hugh Scott
Originally posted by wooliewillie
I am hoping that someone has an "it's obvious" solution. I have a bunch (~100) of text fields in a single record. I want a user to be able to search all of the fields (within this single record) for a string, and to return the field number(s) of where the string was found. Is there a way to do this within the MSSQL server's built in fts catalog, (or some other product) or do I need to code this by hand? I could limit the searchable strings to a finite set, if needed.
FTS- not returning results when lines are searched.
Hello everyone,
I am performing full-text-search on a table containing word documents
in it's column. The search results fair when a single word is
searched. i.e. when I fire the following query:
select * from docs where contains(document, '"java"');
but if I try to search the a full line or give multiple words in the
text to be searched, then it shows no results, even if there is same
text present in the document stored in the database.
select * from docs where contains(document, '"this text is in
database"');
what could be the possible cause of the problem. Shouldn't the FTS
return the records which contains even a single word in the queried
text.
Please help me with the issue.
Thanks & Regards,
Varun Narang.
It definitely should return results for both a single token or a phrase.
However in your phrase you have noise words? Did you empty your noise word
list and replace it with a single space?
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Varun" <varunarang@.gmail.com> wrote in message
news:1142867652.058844.39330@.g10g2000cwb.googlegro ups.com...
> Hello everyone,
> I am performing full-text-search on a table containing word documents
> in it's column. The search results fair when a single word is
> searched. i.e. when I fire the following query:
> select * from docs where contains(document, '"java"');
> but if I try to search the a full line or give multiple words in the
> text to be searched, then it shows no results, even if there is same
> text present in the document stored in the database.
> select * from docs where contains(document, '"this text is in
> database"');
> what could be the possible cause of the problem. Shouldn't the FTS
> return the records which contains even a single word in the queried
> text.
> Please help me with the issue.
> Thanks & Regards,
> Varun Narang.
>
|||Varun wrote on 20 Mar 2006 07:14:12 -0800:
> Hello everyone,
> I am performing full-text-search on a table containing word documents
> in it's column. The search results fair when a single word is
> searched. i.e. when I fire the following query:
> select * from docs where contains(document, '"java"');
> but if I try to search the a full line or give multiple words in the
> text to be searched, then it shows no results, even if there is same
> text present in the document stored in the database.
> select * from docs where contains(document, '"this text is in
> database"');
> what could be the possible cause of the problem. Shouldn't the FTS
> return the records which contains even a single word in the queried
> text.
Because you have double quotes around the phrase, it will only look for rows
with that exact phrase in the text. If you want to search for any/all words
in any order and not necessarily in a single phrase, you need to remove
those double quotes. Also check BOL for the use of logical operators.
Dan
I am performing full-text-search on a table containing word documents
in it's column. The search results fair when a single word is
searched. i.e. when I fire the following query:
select * from docs where contains(document, '"java"');
but if I try to search the a full line or give multiple words in the
text to be searched, then it shows no results, even if there is same
text present in the document stored in the database.
select * from docs where contains(document, '"this text is in
database"');
what could be the possible cause of the problem. Shouldn't the FTS
return the records which contains even a single word in the queried
text.
Please help me with the issue.
Thanks & Regards,
Varun Narang.
It definitely should return results for both a single token or a phrase.
However in your phrase you have noise words? Did you empty your noise word
list and replace it with a single space?
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Varun" <varunarang@.gmail.com> wrote in message
news:1142867652.058844.39330@.g10g2000cwb.googlegro ups.com...
> Hello everyone,
> I am performing full-text-search on a table containing word documents
> in it's column. The search results fair when a single word is
> searched. i.e. when I fire the following query:
> select * from docs where contains(document, '"java"');
> but if I try to search the a full line or give multiple words in the
> text to be searched, then it shows no results, even if there is same
> text present in the document stored in the database.
> select * from docs where contains(document, '"this text is in
> database"');
> what could be the possible cause of the problem. Shouldn't the FTS
> return the records which contains even a single word in the queried
> text.
> Please help me with the issue.
> Thanks & Regards,
> Varun Narang.
>
|||Varun wrote on 20 Mar 2006 07:14:12 -0800:
> Hello everyone,
> I am performing full-text-search on a table containing word documents
> in it's column. The search results fair when a single word is
> searched. i.e. when I fire the following query:
> select * from docs where contains(document, '"java"');
> but if I try to search the a full line or give multiple words in the
> text to be searched, then it shows no results, even if there is same
> text present in the document stored in the database.
> select * from docs where contains(document, '"this text is in
> database"');
> what could be the possible cause of the problem. Shouldn't the FTS
> return the records which contains even a single word in the queried
> text.
Because you have double quotes around the phrase, it will only look for rows
with that exact phrase in the text. If you want to search for any/all words
in any order and not necessarily in a single phrase, you need to remove
those double quotes. Also check BOL for the use of logical operators.
Dan
订阅：
博文 (Atom)