SQL 2000 SP3a on W2K SP4 server. I've created a new database with only three
tables and set the Recovery Model to Simple. The reason being, these tables
are essentially "temp" tables who's data will be constantly deleted and
re-inserted. Since it is all calculated data, I have no need for recovery of
any kind. My problem is this: Even with the Recovery Model set to Simple,
the Transaction Log grows exponentially. This is a real problem for me since
these tables may be emptied and re-populated 1,500 times in one evening.
What recovery model should I use in this situation in order to keep the Log
file size under control? Or should I just run SHRINKDATABASE or some other
utility after each cleaning?
Hi
Keep your transactions short and sweet. If it is all one transaction, and
you update 10 million rows, you will need a lot of space.
Shrink DB will not help unless the transaction is committed or rolled back.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Ron Hinds" <__ron__dontspamme@.wedontlikespam_garageiq.com> wrote in message
news:O5ijLRPAGHA.3136@.TK2MSFTNGP15.phx.gbl...
> SQL 2000 SP3a on W2K SP4 server. I've created a new database with only
> three
> tables and set the Recovery Model to Simple. The reason being, these
> tables
> are essentially "temp" tables who's data will be constantly deleted and
> re-inserted. Since it is all calculated data, I have no need for recovery
> of
> any kind. My problem is this: Even with the Recovery Model set to Simple,
> the Transaction Log grows exponentially. This is a real problem for me
> since
> these tables may be emptied and re-populated 1,500 times in one evening.
> What recovery model should I use in this situation in order to keep the
> Log
> file size under control? Or should I just run SHRINKDATABASE or some other
> utility after each cleaning?
>
Showing posts with label ive. Show all posts
Showing posts with label ive. Show all posts
Sunday, March 11, 2012
Thursday, March 8, 2012
"second newest" record?
I've seen a number of solution to get the "newest record" from a time series
-- and extensively benchmarked them on our 2 million+ row prices database. In
a similar task I'm trying to get the "right" record from a series of
transactions that include cancel/corrections.
For instance, let's say the operator fat-fingers an order for IBM and gets
ten times too much. We'll notice the error, and they'll issue a
cancel/correct, like this...
DAY 1: buy 50000 IBM #1028A
DAY 2: buy -50000 IBM #1028A
buy 5000 IBM #1028A
So in this case they cancel the original mistake, then send out the
correction. When I import these into our DB I give them row numbers and
timestamp them. In order to get the "right record" I subselect the maximum id
for all orders with the same ID...
SELECT *
FROM import
INNER JOIN
(SELECT MAX(id) AS MAXID
FROM import
GROUP BY OrderNumber) o
ON id= o.MAXID
Ok, so now what if they actually report it this way instead...
DAY 1: buy 50000 IBM #1028A
DAY 2: buy 5000 IBM #1028A
buy -50000 IBM #1028A
Yes, that's right, they don't report cancel/correct, but correct/cancel. Grrr.
So how do I adjust my SQL to get the "second most maximum ID"? I can't
figure this out.
Maury
Maury,
Aren't you saying that for some orders the order you want is the last,
and for others it's the next-to-last? Selecting the second-largest ID
for every order doesn't sound likely to solve your problem. While you
can do that, is there another way you can describe the row you want,
such as the most recent row with a positive number of shares, or the
result of adding up all shares in an order?
Anyway, you can get the second largest id for each OrderNumber like this
(untested):
select * from import as I1
where id = (
select top 1 T.id
from (
select top 2 I2.id
from import as I2
where I2.OrderNumber = I1.OrderNumber
order by id desc
) T
order by id
)
Steve Kass
Drew University
Maury Markowitz wrote:
>I've seen a number of solution to get the "newest record" from a time series
>-- and extensively benchmarked them on our 2 million+ row prices database. In
>a similar task I'm trying to get the "right" record from a series of
>transactions that include cancel/corrections.
>For instance, let's say the operator fat-fingers an order for IBM and gets
>ten times too much. We'll notice the error, and they'll issue a
>cancel/correct, like this...
>DAY 1: buy 50000 IBM #1028A
>DAY 2: buy -50000 IBM #1028A
> buy 5000 IBM #1028A
>So in this case they cancel the original mistake, then send out the
>correction. When I import these into our DB I give them row numbers and
>timestamp them. In order to get the "right record" I subselect the maximum id
>for all orders with the same ID...
>SELECT *
>FROM import
>INNER JOIN
> (SELECT MAX(id) AS MAXID
> FROM import
> GROUP BY OrderNumber) o
>ON id= o.MAXID
>Ok, so now what if they actually report it this way instead...
>DAY 1: buy 50000 IBM #1028A
>DAY 2: buy 5000 IBM #1028A
> buy -50000 IBM #1028A
>Yes, that's right, they don't report cancel/correct, but correct/cancel. Grrr.
>So how do I adjust my SQL to get the "second most maximum ID"? I can't
>figure this out.
>Maury
>
|||"Steve Kass" wrote:
> Aren't you saying that for some orders the order you want is the last,
> and for others it's the next-to-last?
No, for some brokers (ie, the smart ones) it's the last record, but in this
case the "correct" record will ALWAYS be the second-to-last.
> can do that, is there another way you can describe the row you want,
> such as the most recent row with a positive number of shares, or the
> result of adding up all shares in an order?
I considered the last idea, but it only works for quantity. Other changes,
like the security name or price, can't be added up.
I'm going to try your SQL suggestion now!
Maury
|||"Steve Kass" wrote:
Your SQL worked great Steve. Sadly your other comment turned out to be true:
they DO sometimes put the correction as the second record, and sometimes the
third. I've looked through the data for some sort of determinant, but I can't
seem to find it. It might be possible to compare the side (buy/sell) with the
quantity or something, but that seems pretty nasty too.
|||On Thu, 6 Jan 2005 11:21:02 -0800, Maury Markowitz wrote:
>"Steve Kass" wrote:
>Your SQL worked great Steve. Sadly your other comment turned out to be true:
>they DO sometimes put the correction as the second record, and sometimes the
>third. I've looked through the data for some sort of determinant, but I can't
>seem to find it. It might be possible to compare the side (buy/sell) with the
>quantity or something, but that seems pretty nasty too.
Hi Maury,
Sorry to hear about the mess you're finding yourself in. I don't think I
can help you sort this out (at least not based on the info you've posted
so far), but once you have this nder control, I suggest you prevent this
from happenning again by adding one column:
ALTER TABLE import
ADD COLUMN correction_to INT
DEFAULT NULL
REFERENCES import(ID)
(Change the datatype from INT to the datatype of your import.ID column)
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
-- and extensively benchmarked them on our 2 million+ row prices database. In
a similar task I'm trying to get the "right" record from a series of
transactions that include cancel/corrections.
For instance, let's say the operator fat-fingers an order for IBM and gets
ten times too much. We'll notice the error, and they'll issue a
cancel/correct, like this...
DAY 1: buy 50000 IBM #1028A
DAY 2: buy -50000 IBM #1028A
buy 5000 IBM #1028A
So in this case they cancel the original mistake, then send out the
correction. When I import these into our DB I give them row numbers and
timestamp them. In order to get the "right record" I subselect the maximum id
for all orders with the same ID...
SELECT *
FROM import
INNER JOIN
(SELECT MAX(id) AS MAXID
FROM import
GROUP BY OrderNumber) o
ON id= o.MAXID
Ok, so now what if they actually report it this way instead...
DAY 1: buy 50000 IBM #1028A
DAY 2: buy 5000 IBM #1028A
buy -50000 IBM #1028A
Yes, that's right, they don't report cancel/correct, but correct/cancel. Grrr.
So how do I adjust my SQL to get the "second most maximum ID"? I can't
figure this out.
Maury
Maury,
Aren't you saying that for some orders the order you want is the last,
and for others it's the next-to-last? Selecting the second-largest ID
for every order doesn't sound likely to solve your problem. While you
can do that, is there another way you can describe the row you want,
such as the most recent row with a positive number of shares, or the
result of adding up all shares in an order?
Anyway, you can get the second largest id for each OrderNumber like this
(untested):
select * from import as I1
where id = (
select top 1 T.id
from (
select top 2 I2.id
from import as I2
where I2.OrderNumber = I1.OrderNumber
order by id desc
) T
order by id
)
Steve Kass
Drew University
Maury Markowitz wrote:
>I've seen a number of solution to get the "newest record" from a time series
>-- and extensively benchmarked them on our 2 million+ row prices database. In
>a similar task I'm trying to get the "right" record from a series of
>transactions that include cancel/corrections.
>For instance, let's say the operator fat-fingers an order for IBM and gets
>ten times too much. We'll notice the error, and they'll issue a
>cancel/correct, like this...
>DAY 1: buy 50000 IBM #1028A
>DAY 2: buy -50000 IBM #1028A
> buy 5000 IBM #1028A
>So in this case they cancel the original mistake, then send out the
>correction. When I import these into our DB I give them row numbers and
>timestamp them. In order to get the "right record" I subselect the maximum id
>for all orders with the same ID...
>SELECT *
>FROM import
>INNER JOIN
> (SELECT MAX(id) AS MAXID
> FROM import
> GROUP BY OrderNumber) o
>ON id= o.MAXID
>Ok, so now what if they actually report it this way instead...
>DAY 1: buy 50000 IBM #1028A
>DAY 2: buy 5000 IBM #1028A
> buy -50000 IBM #1028A
>Yes, that's right, they don't report cancel/correct, but correct/cancel. Grrr.
>So how do I adjust my SQL to get the "second most maximum ID"? I can't
>figure this out.
>Maury
>
|||"Steve Kass" wrote:
> Aren't you saying that for some orders the order you want is the last,
> and for others it's the next-to-last?
No, for some brokers (ie, the smart ones) it's the last record, but in this
case the "correct" record will ALWAYS be the second-to-last.
> can do that, is there another way you can describe the row you want,
> such as the most recent row with a positive number of shares, or the
> result of adding up all shares in an order?
I considered the last idea, but it only works for quantity. Other changes,
like the security name or price, can't be added up.
I'm going to try your SQL suggestion now!
Maury
|||"Steve Kass" wrote:
Your SQL worked great Steve. Sadly your other comment turned out to be true:
they DO sometimes put the correction as the second record, and sometimes the
third. I've looked through the data for some sort of determinant, but I can't
seem to find it. It might be possible to compare the side (buy/sell) with the
quantity or something, but that seems pretty nasty too.
|||On Thu, 6 Jan 2005 11:21:02 -0800, Maury Markowitz wrote:
>"Steve Kass" wrote:
>Your SQL worked great Steve. Sadly your other comment turned out to be true:
>they DO sometimes put the correction as the second record, and sometimes the
>third. I've looked through the data for some sort of determinant, but I can't
>seem to find it. It might be possible to compare the side (buy/sell) with the
>quantity or something, but that seems pretty nasty too.
Hi Maury,
Sorry to hear about the mess you're finding yourself in. I don't think I
can help you sort this out (at least not based on the info you've posted
so far), but once you have this nder control, I suggest you prevent this
from happenning again by adding one column:
ALTER TABLE import
ADD COLUMN correction_to INT
DEFAULT NULL
REFERENCES import(ID)
(Change the datatype from INT to the datatype of your import.ID column)
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
"Resource is low, some results are dropped"
I've been getting this message in a dialog box intermittently. Most recently
when trying to execute 'select @.@.trancount' after a statement attempted to
insert 100 rows into a table with only 10 rather small, varchar(256) columns
Everything that turned up on a Google search basically said throw more
hardware at it. I'd like to know what the underlying issue is. I'm on a 3ghz
pentium 4 with 1 gig of ram. Task Manager shows total Physical Memory(K) of
1039304, available memory = 333208(K) and System Cache = 507972 along with
2% CPU so I don't believe that it's a memory issue or more hardware will
solve the underlying issue.
Does anyone have an answer for this other than throw more hardware at it?
Where do you see this error? Query Analyzer?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Barry Forrest" <barry.forrest@.no-spam.ps.net> wrote in message
news:DB948FD8-DF27-4EC3-AA40-59F9718318FB@.microsoft.com...
> I've been getting this message in a dialog box intermittently. Most recently
> when trying to execute 'select @.@.trancount' after a statement attempted to
> insert 100 rows into a table with only 10 rather small, varchar(256) columns
> Everything that turned up on a Google search basically said throw more
> hardware at it. I'd like to know what the underlying issue is. I'm on a 3ghz
> pentium 4 with 1 gig of ram. Task Manager shows total Physical Memory(K) of
> 1039304, available memory = 333208(K) and System Cache = 507972 along with
> 2% CPU so I don't believe that it's a memory issue or more hardware will
> solve the underlying issue.
> Does anyone have an answer for this other than throw more hardware at it?
|||Hi,
Looks like you are executing the query in GRID result pane in query
analyzer. Could you change the mode to Text and try.
How to change:-
1. In query analyzer
2. Go to Query menu
3. Select "Result in Text"
4. Execute the query.
Thanks
Hari
SQL Server MVP
"Barry Forrest" <barry.forrest@.no-spam.ps.net> wrote in message
news:DB948FD8-DF27-4EC3-AA40-59F9718318FB@.microsoft.com...
> I've been getting this message in a dialog box intermittently. Most
> recently
> when trying to execute 'select @.@.trancount' after a statement attempted to
> insert 100 rows into a table with only 10 rather small, varchar(256)
> columns
> Everything that turned up on a Google search basically said throw more
> hardware at it. I'd like to know what the underlying issue is. I'm on a
> 3ghz
> pentium 4 with 1 gig of ram. Task Manager shows total Physical Memory(K)
> of
> 1039304, available memory = 333208(K) and System Cache = 507972 along
> with
> 2% CPU so I don't believe that it's a memory issue or more hardware will
> solve the underlying issue.
> Does anyone have an answer for this other than throw more hardware at it?
|||Or use the shortcuts CTRL + T for text mode, CTRL + D for grid
http://sqlservercode.blogspot.com/
"Hari Prasad" wrote:
> Hi,
> Looks like you are executing the query in GRID result pane in query
> analyzer. Could you change the mode to Text and try.
>
> How to change:-
> 1. In query analyzer
> 2. Go to Query menu
> 3. Select "Result in Text"
> 4. Execute the query.
> Thanks
> Hari
> SQL Server MVP
>
>
> "Barry Forrest" <barry.forrest@.no-spam.ps.net> wrote in message
> news:DB948FD8-DF27-4EC3-AA40-59F9718318FB@.microsoft.com...
>
>
|||re: Resource is low, some results are dropped
There are a few different reasons this could happen.
1) Not patched up! Make sure you have the latest patches from Microsoft.
Service Packs
http://www.microsoft.com/sql/downloads/2000/sp4.mspx
Security Patches
You can check Technet for the latest patches...
http://www.microsoft.com/technet/security/current.aspx
2) How, and how many, results are returned.
Query Analyzer returns results in one of two ways when you execute SQL Statements (Text or Grid) It can execute and return the most complicated queries from huge databases with large result sets.
However, it has a problem returning multiple results to grid. Each grid requires a certain amount of resources, and if you execute a large number of queries, it eventually will run out memory and drop some results.
I was looping throogh sysobjects and syscolumns, executing a select statement on every column for every table. (Code GEnerator) It was trying to open a grid for every column in my database. Not that Pubs or Northwind would cause it, but my production da
tabase had a lot more objects. My guess is that this requires too many resources to complete.
If I changed the output for executing the queries to text mode, or I printed the information to the screen instead of executing t-SQL I did not get the error.
Try running it in text mode. Menu Bar - Query - Results in grid or Results in Text.
The keyboard shortcuts are Ctrl+D for Grid and Ctrl+T for Text.
Mike Pittser
Database Architect
sql2k5dba@.yahoo.com
************************************************** ********************
Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
Comprehensive, categorised, searchable collection of links to ASP & ASP.NET resources...
when trying to execute 'select @.@.trancount' after a statement attempted to
insert 100 rows into a table with only 10 rather small, varchar(256) columns
Everything that turned up on a Google search basically said throw more
hardware at it. I'd like to know what the underlying issue is. I'm on a 3ghz
pentium 4 with 1 gig of ram. Task Manager shows total Physical Memory(K) of
1039304, available memory = 333208(K) and System Cache = 507972 along with
2% CPU so I don't believe that it's a memory issue or more hardware will
solve the underlying issue.
Does anyone have an answer for this other than throw more hardware at it?
Where do you see this error? Query Analyzer?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Barry Forrest" <barry.forrest@.no-spam.ps.net> wrote in message
news:DB948FD8-DF27-4EC3-AA40-59F9718318FB@.microsoft.com...
> I've been getting this message in a dialog box intermittently. Most recently
> when trying to execute 'select @.@.trancount' after a statement attempted to
> insert 100 rows into a table with only 10 rather small, varchar(256) columns
> Everything that turned up on a Google search basically said throw more
> hardware at it. I'd like to know what the underlying issue is. I'm on a 3ghz
> pentium 4 with 1 gig of ram. Task Manager shows total Physical Memory(K) of
> 1039304, available memory = 333208(K) and System Cache = 507972 along with
> 2% CPU so I don't believe that it's a memory issue or more hardware will
> solve the underlying issue.
> Does anyone have an answer for this other than throw more hardware at it?
|||Hi,
Looks like you are executing the query in GRID result pane in query
analyzer. Could you change the mode to Text and try.
How to change:-
1. In query analyzer
2. Go to Query menu
3. Select "Result in Text"
4. Execute the query.
Thanks
Hari
SQL Server MVP
"Barry Forrest" <barry.forrest@.no-spam.ps.net> wrote in message
news:DB948FD8-DF27-4EC3-AA40-59F9718318FB@.microsoft.com...
> I've been getting this message in a dialog box intermittently. Most
> recently
> when trying to execute 'select @.@.trancount' after a statement attempted to
> insert 100 rows into a table with only 10 rather small, varchar(256)
> columns
> Everything that turned up on a Google search basically said throw more
> hardware at it. I'd like to know what the underlying issue is. I'm on a
> 3ghz
> pentium 4 with 1 gig of ram. Task Manager shows total Physical Memory(K)
> of
> 1039304, available memory = 333208(K) and System Cache = 507972 along
> with
> 2% CPU so I don't believe that it's a memory issue or more hardware will
> solve the underlying issue.
> Does anyone have an answer for this other than throw more hardware at it?
|||Or use the shortcuts CTRL + T for text mode, CTRL + D for grid
http://sqlservercode.blogspot.com/
"Hari Prasad" wrote:
> Hi,
> Looks like you are executing the query in GRID result pane in query
> analyzer. Could you change the mode to Text and try.
>
> How to change:-
> 1. In query analyzer
> 2. Go to Query menu
> 3. Select "Result in Text"
> 4. Execute the query.
> Thanks
> Hari
> SQL Server MVP
>
>
> "Barry Forrest" <barry.forrest@.no-spam.ps.net> wrote in message
> news:DB948FD8-DF27-4EC3-AA40-59F9718318FB@.microsoft.com...
>
>
|||re: Resource is low, some results are dropped
There are a few different reasons this could happen.
1) Not patched up! Make sure you have the latest patches from Microsoft.
Service Packs
http://www.microsoft.com/sql/downloads/2000/sp4.mspx
Security Patches
You can check Technet for the latest patches...
http://www.microsoft.com/technet/security/current.aspx
2) How, and how many, results are returned.
Query Analyzer returns results in one of two ways when you execute SQL Statements (Text or Grid) It can execute and return the most complicated queries from huge databases with large result sets.
However, it has a problem returning multiple results to grid. Each grid requires a certain amount of resources, and if you execute a large number of queries, it eventually will run out memory and drop some results.
I was looping throogh sysobjects and syscolumns, executing a select statement on every column for every table. (Code GEnerator) It was trying to open a grid for every column in my database. Not that Pubs or Northwind would cause it, but my production da
tabase had a lot more objects. My guess is that this requires too many resources to complete.
If I changed the output for executing the queries to text mode, or I printed the information to the screen instead of executing t-SQL I did not get the error.
Try running it in text mode. Menu Bar - Query - Results in grid or Results in Text.
The keyboard shortcuts are Ctrl+D for Grid and Ctrl+T for Text.
Mike Pittser
Database Architect
sql2k5dba@.yahoo.com
************************************************** ********************
Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
Comprehensive, categorised, searchable collection of links to ASP & ASP.NET resources...
"replace value of" and enum
I've created XML SCHEMA COLLECTION AS
<?xml version="1.0" encoding="utf-8"?>
<xs:schema elementFormDefault="qualified"
xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xs:element name="Order" nillable="true" type="Order" />
<xs:complexType name="Order">
<xs:sequence>
<xs:element minOccurs="1" maxOccurs="1" name="Status"
type="OrderStatus" />
</xs:sequence>
</xs:complexType>
<xs:simpleType name="OrderStatus">
<xs:restriction base="xs:string">
<xs:enumeration value="New" />
<xs:enumeration value="Pending" />
<xs:enumeration value="Complete" />
</xs:restriction>
</xs:simpleType>
</xs:schema>
Now I'd like to update OrderStatus:
UPDATE Orders
SET OrderXml.modify('replace value of (/Order[1]/Status[1]) with "Expired"')
I get:
Msg 2247, Level 16, State 1, Procedure Orders_Select
XQuery [dbo.Orders.OrderXml.modify()]: The value is of type "xs:string",
which is not a subtype of the expected type "OrderStatus".
The below does not work either:
UPDATE Orders
SET OrderXml.modify('replace value of (/Order[1]/Status[1]) with
OrderStatus("Expired")')
How do I make this work?
Thanks
Never mind, ("Expired" cast as OrderStatus) does the trick.
"Chris Carter" <anonymous@.discussions.microsoft.com> wrote in message
news:%23AHmjrHKGHA.2392@.TK2MSFTNGP09.phx.gbl...
> I've created XML SCHEMA COLLECTION AS
> <?xml version="1.0" encoding="utf-8"?>
> <xs:schema elementFormDefault="qualified"
> xmlns:xs="http://www.w3.org/2001/XMLSchema">
> <xs:element name="Order" nillable="true" type="Order" />
> <xs:complexType name="Order">
> <xs:sequence>
> <xs:element minOccurs="1" maxOccurs="1" name="Status"
> type="OrderStatus" />
> </xs:sequence>
> </xs:complexType>
> <xs:simpleType name="OrderStatus">
> <xs:restriction base="xs:string">
> <xs:enumeration value="New" />
> <xs:enumeration value="Pending" />
> <xs:enumeration value="Complete" />
> </xs:restriction>
> </xs:simpleType>
> </xs:schema>
>
> Now I'd like to update OrderStatus:
> UPDATE Orders
> SET OrderXml.modify('replace value of (/Order[1]/Status[1]) with
> "Expired"')
> I get:
> Msg 2247, Level 16, State 1, Procedure Orders_Select
> XQuery [dbo.Orders.OrderXml.modify()]: The value is of type "xs:string",
> which is not a subtype of the expected type "OrderStatus".
> The below does not work either:
> UPDATE Orders
> SET OrderXml.modify('replace value of (/Order[1]/Status[1]) with
> OrderStatus("Expired")')
>
> How do I make this work?
>
> Thanks
>
>
>
<?xml version="1.0" encoding="utf-8"?>
<xs:schema elementFormDefault="qualified"
xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xs:element name="Order" nillable="true" type="Order" />
<xs:complexType name="Order">
<xs:sequence>
<xs:element minOccurs="1" maxOccurs="1" name="Status"
type="OrderStatus" />
</xs:sequence>
</xs:complexType>
<xs:simpleType name="OrderStatus">
<xs:restriction base="xs:string">
<xs:enumeration value="New" />
<xs:enumeration value="Pending" />
<xs:enumeration value="Complete" />
</xs:restriction>
</xs:simpleType>
</xs:schema>
Now I'd like to update OrderStatus:
UPDATE Orders
SET OrderXml.modify('replace value of (/Order[1]/Status[1]) with "Expired"')
I get:
Msg 2247, Level 16, State 1, Procedure Orders_Select
XQuery [dbo.Orders.OrderXml.modify()]: The value is of type "xs:string",
which is not a subtype of the expected type "OrderStatus".
The below does not work either:
UPDATE Orders
SET OrderXml.modify('replace value of (/Order[1]/Status[1]) with
OrderStatus("Expired")')
How do I make this work?
Thanks
Never mind, ("Expired" cast as OrderStatus) does the trick.
"Chris Carter" <anonymous@.discussions.microsoft.com> wrote in message
news:%23AHmjrHKGHA.2392@.TK2MSFTNGP09.phx.gbl...
> I've created XML SCHEMA COLLECTION AS
> <?xml version="1.0" encoding="utf-8"?>
> <xs:schema elementFormDefault="qualified"
> xmlns:xs="http://www.w3.org/2001/XMLSchema">
> <xs:element name="Order" nillable="true" type="Order" />
> <xs:complexType name="Order">
> <xs:sequence>
> <xs:element minOccurs="1" maxOccurs="1" name="Status"
> type="OrderStatus" />
> </xs:sequence>
> </xs:complexType>
> <xs:simpleType name="OrderStatus">
> <xs:restriction base="xs:string">
> <xs:enumeration value="New" />
> <xs:enumeration value="Pending" />
> <xs:enumeration value="Complete" />
> </xs:restriction>
> </xs:simpleType>
> </xs:schema>
>
> Now I'd like to update OrderStatus:
> UPDATE Orders
> SET OrderXml.modify('replace value of (/Order[1]/Status[1]) with
> "Expired"')
> I get:
> Msg 2247, Level 16, State 1, Procedure Orders_Select
> XQuery [dbo.Orders.OrderXml.modify()]: The value is of type "xs:string",
> which is not a subtype of the expected type "OrderStatus".
> The below does not work either:
> UPDATE Orders
> SET OrderXml.modify('replace value of (/Order[1]/Status[1]) with
> OrderStatus("Expired")')
>
> How do I make this work?
>
> Thanks
>
>
>
Thursday, February 16, 2012
"Filling in the gaps" with a single-line query
Hi,
I've got the following scenario:
Files are being stored in a database, with a number of name-value
pairs associated with each file. This happens by storing the files in
one table (File), the list of property names in another table
(FileMetaDataSchema) and the values of properties in a third table
(FileMetaData), which references both File and FileMetaDataSchema.
The frontend of my application assumes there is a record for each
property of each file in the FileMetaData table, even if the value is
an empty string. In other words, if i have a list of 3 properties and
2 files, FileMetaData will contain 6 records.
Due to a bug in the system, this does not always happen. Suppose one
adds a new property and neglects to insert the "blank" records for the
new properties for all the files, or a new file is added, but the
associated meta-data records are not... (why and how this happens is
not the topic of discussion, so don't worry about that).
I have written a sql script to insert all the missing "blank" records,
but I feel it is very clumsy and intuitively, I just know there must
be a simpler way, my knowledge is just too limited. What it does is,
it iterates (using cursors) through all the files and all the
properties, checks if there is a record for each combination FileID
and FileMetaDataSchemaID and if not, it inserts one. I am looking for
a better way out of curiosity, for my own benefit.
Here's the script and thanks for any input:
DECLARE MetadataSchemaCursor CURSOR FOR
SELECT FileMetaDataSchemaID FROM FileMetaDataSchema
DECLARE @.FileMetaDataSchemaID INT,
@.FileID INT
OPEN MetadataSchemaCursor
FETCH NEXT FROM MetaDataSchemaCursor INTO @.FileMetaDataSchemaID
WHILE (@.@.FETCH_STATUS = 0)
BEGIN
DECLARE FileCursor CURSOR FOR
SELECT FileID FROM [File]
OPEN FileCursor
FETCH NEXT FROM FileCursor INTO @.FileID
WHILE (@.@.FETCH_STATUS = 0)
BEGIN
IF NOT EXISTS (SELECT 1 FROM FileMetaData WHERE FileID = @.FileID AND
FileMetaDataSchemaID = @.FileMetaDataSchemaID)
INSERT INTO FileMetaData (FileID, FileMetaDataSchemaID,
PropertyValue) SELECT @.FileID, @.FileMetaDataSchemaID, ''
FETCH NEXT FROM FileCursor INTO @.FileID
END
CLOSE FileCursor
DEALLOCATE FileCursor
FETCH NEXT FROM MetaDataSchemaCursor INTO @.FileMetaDataSchemaID
END
CLOSE MetadataSchemaCursor
DEALLOCATE MetadataSchemaCursor
>I just know there must
>be a simpler way, my knowledge is just too limited.
You are correct, there is a simpler way.
INSERT INTO FileMetaData (FileID, FileMetaDataSchemaID, PropertyValue)
SELECT A.FileID, B.FileMetaDataSchemaID, ''
FROM [File] as A
CROSS
JOIN FileMetaDataSchema as B
WHERE NOT EXISTS
(select * from FileMetaData as X
where A.FileID = X.FileID
and B.FileMetaDataSchemaID = X.FileMetaDataSchemaID)
Roy Harvey
Beacon Falls, CT
On 22 Feb 2007 06:48:53 -0800, "Velislav" <vgebrev@.gmail.com> wrote:
>Hi,
>I've got the following scenario:
>Files are being stored in a database, with a number of name-value
>pairs associated with each file. This happens by storing the files in
>one table (File), the list of property names in another table
>(FileMetaDataSchema) and the values of properties in a third table
>(FileMetaData), which references both File and FileMetaDataSchema.
>The frontend of my application assumes there is a record for each
>property of each file in the FileMetaData table, even if the value is
>an empty string. In other words, if i have a list of 3 properties and
>2 files, FileMetaData will contain 6 records.
>Due to a bug in the system, this does not always happen. Suppose one
>adds a new property and neglects to insert the "blank" records for the
>new properties for all the files, or a new file is added, but the
>associated meta-data records are not... (why and how this happens is
>not the topic of discussion, so don't worry about that).
>I have written a sql script to insert all the missing "blank" records,
>but I feel it is very clumsy and intuitively, I just know there must
>be a simpler way, my knowledge is just too limited. What it does is,
>it iterates (using cursors) through all the files and all the
>properties, checks if there is a record for each combination FileID
>and FileMetaDataSchemaID and if not, it inserts one. I am looking for
>a better way out of curiosity, for my own benefit.
>Here's the script and thanks for any input:
>DECLARE MetadataSchemaCursor CURSOR FOR
>SELECT FileMetaDataSchemaID FROM FileMetaDataSchema
>DECLARE @.FileMetaDataSchemaID INT,
>@.FileID INT
>OPEN MetadataSchemaCursor
>FETCH NEXT FROM MetaDataSchemaCursor INTO @.FileMetaDataSchemaID
>WHILE (@.@.FETCH_STATUS = 0)
>BEGIN
>DECLARE FileCursor CURSOR FOR
>SELECT FileID FROM [File]
>OPEN FileCursor
>FETCH NEXT FROM FileCursor INTO @.FileID
>WHILE (@.@.FETCH_STATUS = 0)
>BEGIN
>IF NOT EXISTS (SELECT 1 FROM FileMetaData WHERE FileID = @.FileID AND
>FileMetaDataSchemaID = @.FileMetaDataSchemaID)
>INSERT INTO FileMetaData (FileID, FileMetaDataSchemaID,
>PropertyValue) SELECT @.FileID, @.FileMetaDataSchemaID, ''
>FETCH NEXT FROM FileCursor INTO @.FileID
>END
>CLOSE FileCursor
>DEALLOCATE FileCursor
>FETCH NEXT FROM MetaDataSchemaCursor INTO @.FileMetaDataSchemaID
>END
>CLOSE MetadataSchemaCursor
>DEALLOCATE MetadataSchemaCursor
|||On Feb 22, 5:42 pm, Roy Harvey <roy_har...@.snet.net> wrote:
> You are correct, there is a simpler way.
> INSERT INTO FileMetaData (FileID, FileMetaDataSchemaID, PropertyValue)
> SELECT A.FileID, B.FileMetaDataSchemaID, ''
> FROM [File] as A
> CROSS
> JOIN FileMetaDataSchema as B
> WHERE NOT EXISTS
> (select * from FileMetaData as X
> where A.FileID = X.FileID
> and B.FileMetaDataSchemaID = X.FileMetaDataSchemaID)
> Roy Harvey
> Beacon Falls, CT
>
Thank you
Note to self - look up cross joins.
I've got the following scenario:
Files are being stored in a database, with a number of name-value
pairs associated with each file. This happens by storing the files in
one table (File), the list of property names in another table
(FileMetaDataSchema) and the values of properties in a third table
(FileMetaData), which references both File and FileMetaDataSchema.
The frontend of my application assumes there is a record for each
property of each file in the FileMetaData table, even if the value is
an empty string. In other words, if i have a list of 3 properties and
2 files, FileMetaData will contain 6 records.
Due to a bug in the system, this does not always happen. Suppose one
adds a new property and neglects to insert the "blank" records for the
new properties for all the files, or a new file is added, but the
associated meta-data records are not... (why and how this happens is
not the topic of discussion, so don't worry about that).
I have written a sql script to insert all the missing "blank" records,
but I feel it is very clumsy and intuitively, I just know there must
be a simpler way, my knowledge is just too limited. What it does is,
it iterates (using cursors) through all the files and all the
properties, checks if there is a record for each combination FileID
and FileMetaDataSchemaID and if not, it inserts one. I am looking for
a better way out of curiosity, for my own benefit.
Here's the script and thanks for any input:
DECLARE MetadataSchemaCursor CURSOR FOR
SELECT FileMetaDataSchemaID FROM FileMetaDataSchema
DECLARE @.FileMetaDataSchemaID INT,
@.FileID INT
OPEN MetadataSchemaCursor
FETCH NEXT FROM MetaDataSchemaCursor INTO @.FileMetaDataSchemaID
WHILE (@.@.FETCH_STATUS = 0)
BEGIN
DECLARE FileCursor CURSOR FOR
SELECT FileID FROM [File]
OPEN FileCursor
FETCH NEXT FROM FileCursor INTO @.FileID
WHILE (@.@.FETCH_STATUS = 0)
BEGIN
IF NOT EXISTS (SELECT 1 FROM FileMetaData WHERE FileID = @.FileID AND
FileMetaDataSchemaID = @.FileMetaDataSchemaID)
INSERT INTO FileMetaData (FileID, FileMetaDataSchemaID,
PropertyValue) SELECT @.FileID, @.FileMetaDataSchemaID, ''
FETCH NEXT FROM FileCursor INTO @.FileID
END
CLOSE FileCursor
DEALLOCATE FileCursor
FETCH NEXT FROM MetaDataSchemaCursor INTO @.FileMetaDataSchemaID
END
CLOSE MetadataSchemaCursor
DEALLOCATE MetadataSchemaCursor
>I just know there must
>be a simpler way, my knowledge is just too limited.
You are correct, there is a simpler way.
INSERT INTO FileMetaData (FileID, FileMetaDataSchemaID, PropertyValue)
SELECT A.FileID, B.FileMetaDataSchemaID, ''
FROM [File] as A
CROSS
JOIN FileMetaDataSchema as B
WHERE NOT EXISTS
(select * from FileMetaData as X
where A.FileID = X.FileID
and B.FileMetaDataSchemaID = X.FileMetaDataSchemaID)
Roy Harvey
Beacon Falls, CT
On 22 Feb 2007 06:48:53 -0800, "Velislav" <vgebrev@.gmail.com> wrote:
>Hi,
>I've got the following scenario:
>Files are being stored in a database, with a number of name-value
>pairs associated with each file. This happens by storing the files in
>one table (File), the list of property names in another table
>(FileMetaDataSchema) and the values of properties in a third table
>(FileMetaData), which references both File and FileMetaDataSchema.
>The frontend of my application assumes there is a record for each
>property of each file in the FileMetaData table, even if the value is
>an empty string. In other words, if i have a list of 3 properties and
>2 files, FileMetaData will contain 6 records.
>Due to a bug in the system, this does not always happen. Suppose one
>adds a new property and neglects to insert the "blank" records for the
>new properties for all the files, or a new file is added, but the
>associated meta-data records are not... (why and how this happens is
>not the topic of discussion, so don't worry about that).
>I have written a sql script to insert all the missing "blank" records,
>but I feel it is very clumsy and intuitively, I just know there must
>be a simpler way, my knowledge is just too limited. What it does is,
>it iterates (using cursors) through all the files and all the
>properties, checks if there is a record for each combination FileID
>and FileMetaDataSchemaID and if not, it inserts one. I am looking for
>a better way out of curiosity, for my own benefit.
>Here's the script and thanks for any input:
>DECLARE MetadataSchemaCursor CURSOR FOR
>SELECT FileMetaDataSchemaID FROM FileMetaDataSchema
>DECLARE @.FileMetaDataSchemaID INT,
>@.FileID INT
>OPEN MetadataSchemaCursor
>FETCH NEXT FROM MetaDataSchemaCursor INTO @.FileMetaDataSchemaID
>WHILE (@.@.FETCH_STATUS = 0)
>BEGIN
>DECLARE FileCursor CURSOR FOR
>SELECT FileID FROM [File]
>OPEN FileCursor
>FETCH NEXT FROM FileCursor INTO @.FileID
>WHILE (@.@.FETCH_STATUS = 0)
>BEGIN
>IF NOT EXISTS (SELECT 1 FROM FileMetaData WHERE FileID = @.FileID AND
>FileMetaDataSchemaID = @.FileMetaDataSchemaID)
>INSERT INTO FileMetaData (FileID, FileMetaDataSchemaID,
>PropertyValue) SELECT @.FileID, @.FileMetaDataSchemaID, ''
>FETCH NEXT FROM FileCursor INTO @.FileID
>END
>CLOSE FileCursor
>DEALLOCATE FileCursor
>FETCH NEXT FROM MetaDataSchemaCursor INTO @.FileMetaDataSchemaID
>END
>CLOSE MetadataSchemaCursor
>DEALLOCATE MetadataSchemaCursor
|||On Feb 22, 5:42 pm, Roy Harvey <roy_har...@.snet.net> wrote:
> You are correct, there is a simpler way.
> INSERT INTO FileMetaData (FileID, FileMetaDataSchemaID, PropertyValue)
> SELECT A.FileID, B.FileMetaDataSchemaID, ''
> FROM [File] as A
> CROSS
> JOIN FileMetaDataSchema as B
> WHERE NOT EXISTS
> (select * from FileMetaData as X
> where A.FileID = X.FileID
> and B.FileMetaDataSchemaID = X.FileMetaDataSchemaID)
> Roy Harvey
> Beacon Falls, CT
>
Thank you
Note to self - look up cross joins.
Saturday, February 11, 2012
"CONTAINS" ignores "fahrenheit"?
I've posted this before in microsoft.public.sqlserver.programming, and
someone suggested me to post to this newsgroup.
Anyway, I have articles table like this:
tblArticle
article_id
article_title
article_text
article_title and article_text are part of full-catalog.
And among those article records there is one record like this:
article_id: 999
article_title: "Michael Moore..."
article_text: "...Fahrenheit 9/11..."
When I do search like this:
SELECT *
FROM tblArticle
WHERE CONTAINS(article_text, ' "*Fahrenheit 9/11*" ')
it returned me no result.
However, if I do search using LIKE:
SELECT *
FROM tblArticle
WHERE article_text LIKE '%Fahrenheit 9/11%'
it returned me that article_id: 999
More interesting is if I run this SQL Statement:
SELECT *
FROM tblArticle
WHERE CONTAINS(article_text, ' "*Fahrenheit 9/11*" ')
it returned me records that contains "9/11" but not "Fahrenheit 9/11",
for example:
it returns -> article_text: ... in 9/11 event...
it doesn't return -> article_text: ... Michael Moore who wrote Fahrenheit
9/11 ...
At first, I thought probably full-text catalog were not populated.
So, I did repopulate the full-text catalog, yet it still didn't work.
Also, I thought "Fahrenheit" is part of "noise" words, but it's not listed
in the noise.enu.
Is there any way to resolve this "CONTAINS" issue?
Or is this SQL Server bug?
Thanks in advance,
Danny
Correction:
> More interesting is if I run this SQL Statement:
> SELECT *
> FROM tblArticle
> WHERE CONTAINS(article_text, ' "*9/11*" ')
> it returned me records that contains "9/11" but not "Fahrenheit 9/11",
> for example:
> it returns -> article_text: ... in 9/11 event...
> it doesn't return -> article_text: ... Michael Moore who wrote Fahrenheit
> 9/11 ...
>
|||Hi Danny,
Hmm... that someone would be me? <G>
Ok, you need to provide some additional info, specifically, run and post the
full output of the following SQL script:
use <your_database_name_here>
go
SELECT @.@.language
SELECT @.@.version
-- Note, you may need to set advance options on
sp_configure 'default full-text language'
EXEC sp_help_fulltext_catalogs
EXEC sp_help_fulltext_tables
EXEC sp_help_fulltext_columns
EXEC sp_help tblArticle
go
Depending upon the language (the FULLTEXT_LANGUAGE column from
sp_help_fulltext_columns) of the wordbreaker you are using, have you removed
all single digits from the noise.<language> (noise.enu = US_English) file
under the folder: \FTDATA\SQLServer\Config ? If not, then you should and
then run a Full Population. Note, you will need to stop the "Microsoft
Search" service first in order to save the changes to the noise.* files.
Additionally, the preceding asterisk "*" in your query ' "*9/11*" ' is
always ignored and therefore adds no value to your query. SQL FTS support
only "word prefix" wildcard searches with the asterisk and not "word
suffix", for example ' "*og" ' would find dog and log, but these "words"
are not related. However, using a "word prefix" search such as ' "9/11*" '
returns rows that contain "9/11", or ' "fish*" ' will return "fish",
"fishes" or "fishing" as these words are inflectionally related...
Regards,
John
"Danny" <daniel_c@.NOSPAMmyrealbox.com> wrote in message
news:uoE953peEHA.2440@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> Correction:
Fahrenheit
>
|||John, I am confused by this sentance:
"Additionally, the preceding asterisk "*" in your query ' "*9/11*" ' is
always ignored and therefore adds no value to your query. SQL FTS support
only "word prefix" wildcard searches with the asterisk and not "word
suffix", for example ' "*og" ' would find dog and log, but these "words"
are not related."
In the first part you seem to be saying that the * in the prefix is always
ingored - ie ""Additionally, the preceding asterisk "*" in your query '
"*9/11*" ' is always ignored and therefore adds no value to your query."
and then you go on to say "SQL FTS support only "word prefix" wildcard
searches with the asterisk and not "word suffix", for example ' "*og" '
would find dog and log, but these "words" are not related."
Prefix goes in front, suffix goes behind. Your example of *og matching with
dog and log does not work.
Don't you mean to say "SQL FTS support only "word suffix" wildcard searches
with the asterisk and not "word prefix", for example ' "wild*" ' would find
wildcard and wild, but these "words" are not related."?
Then you go on to say "However, using a "word prefix" search such as '
"9/11*" ' returns rows that contain "9/11", or ' "fish*" ' will return
"fish", "fishes" or "fishing" as these words are inflectionally related..."
I think you mean to say "However, using a "word suffix" search such as '
"9/11*" ' returns rows that contain "9/11", or ' "fish*" ' will return
"fish", "fishes" or "fishing" as these words are inflectionally related...""
And further more the wild card operator does not do stemming, it simply
returns hits to words that start with the letters in front of the *. You are
thinking on the Inflectional operator. To get an idea of what I am talking
add the words mouse to one row and mice to another
Then do this search:
select * from tablename where contains(*,'FormsOF(INFLECTIONAL, Mouse)')
you will get hits to mouse and mice as mouse and mice are inflectionally
related.
as opposed to select * from tablename where contains(*,'mi*')
which will only return hits to mice. So your statement "fish*" ' will
return "fish", "fishes" or "fishing" as these words are inflectionally
related..." seems to be incorrect, or will only hold true if the
inflectionally related terms have the same stems, like with fish, fishes,
and fishing, but not for many English words which do not have the same
stems, like mouse, mice/tooth, teeth/wake,woke/fight, fought/wear,
wore/teach, taught/win, won/sit, sat/write, wrote/take, took/sleep,
slept/run, ran/tell, told/hold, held, off the top of my head .
Daniel, 9/11 as a search phrase works fine for me. Is is possibly your
catalog had not completely built? After you removed 9 and 1 from your noise
word list, did you rebuild your catalog?
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"John Kane" <jt-kane@.comcast.net> wrote in message
news:uFZGJPreEHA.2804@.TK2MSFTNGP11.phx.gbl...
> Hi Danny,
> Hmm... that someone would be me? <G>
> Ok, you need to provide some additional info, specifically, run and post
the
> full output of the following SQL script:
> use <your_database_name_here>
> go
> SELECT @.@.language
> SELECT @.@.version
> -- Note, you may need to set advance options on
> sp_configure 'default full-text language'
> EXEC sp_help_fulltext_catalogs
> EXEC sp_help_fulltext_tables
> EXEC sp_help_fulltext_columns
> EXEC sp_help tblArticle
> go
> Depending upon the language (the FULLTEXT_LANGUAGE column from
> sp_help_fulltext_columns) of the wordbreaker you are using, have you
removed
> all single digits from the noise.<language> (noise.enu = US_English) file
> under the folder: \FTDATA\SQLServer\Config ? If not, then you should and
> then run a Full Population. Note, you will need to stop the "Microsoft
> Search" service first in order to save the changes to the noise.* files.
> Additionally, the preceding asterisk "*" in your query ' "*9/11*" ' is
> always ignored and therefore adds no value to your query. SQL FTS support
> only "word prefix" wildcard searches with the asterisk and not "word
> suffix", for example ' "*og" ' would find dog and log, but these "words"
> are not related. However, using a "word prefix" search such as ' "9/11*"
'
> returns rows that contain "9/11", or ' "fish*" ' will return "fish",
> "fishes" or "fishing" as these words are inflectionally related...
> Regards,
> John
>
>
> "Danny" <daniel_c@.NOSPAMmyrealbox.com> wrote in message
> news:uoE953peEHA.2440@.tk2msftngp13.phx.gbl...
> Fahrenheit
>
|||Hilary,
All I was trying to explain (late at night my time ;-0) was that the
preceding asterisk is ignored in SQL FTS as seems to be a consistent problem
for many people understanding FTS relative to T-SQL LIKE. We still need the
OS and SQL configuration info from Danny. If you want we can discuss this
off-line...
Danny, could you provide your server's configuration info and table info via
the following SQL script and post the full output?
use <your_database_name_here>
go
SELECT @.@.language
SELECT @.@.version
-- Note, you may need to set advance options on
sp_configure 'default full-text language'
EXEC sp_help_fulltext_catalogs
EXEC sp_help_fulltext_tables
EXEC sp_help_fulltext_columns
EXEC sp_help tblArticle
go
Thanks,
John
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:ONGxDZueEHA.236@.tk2msftngp13.phx.gbl...
> John, I am confused by this sentance:
> "Additionally, the preceding asterisk "*" in your query ' "*9/11*" ' is
> always ignored and therefore adds no value to your query. SQL FTS support
> only "word prefix" wildcard searches with the asterisk and not "word
> suffix", for example ' "*og" ' would find dog and log, but these "words"
> are not related."
> In the first part you seem to be saying that the * in the prefix is always
> ingored - ie ""Additionally, the preceding asterisk "*" in your query '
> "*9/11*" ' is always ignored and therefore adds no value to your query."
> and then you go on to say "SQL FTS support only "word prefix" wildcard
> searches with the asterisk and not "word suffix", for example ' "*og" '
> would find dog and log, but these "words" are not related."
> Prefix goes in front, suffix goes behind. Your example of *og matching
with
> dog and log does not work.
> Don't you mean to say "SQL FTS support only "word suffix" wildcard
searches
> with the asterisk and not "word prefix", for example ' "wild*" ' would
find
> wildcard and wild, but these "words" are not related."?
> Then you go on to say "However, using a "word prefix" search such as '
> "9/11*" ' returns rows that contain "9/11", or ' "fish*" ' will return
> "fish", "fishes" or "fishing" as these words are inflectionally
related..."
> I think you mean to say "However, using a "word suffix" search such as '
> "9/11*" ' returns rows that contain "9/11", or ' "fish*" ' will return
> "fish", "fishes" or "fishing" as these words are inflectionally
related...""
> And further more the wild card operator does not do stemming, it simply
> returns hits to words that start with the letters in front of the *. You
are
> thinking on the Inflectional operator. To get an idea of what I am talking
> add the words mouse to one row and mice to another
> Then do this search:
> select * from tablename where contains(*,'FormsOF(INFLECTIONAL, Mouse)')
> you will get hits to mouse and mice as mouse and mice are inflectionally
> related.
> as opposed to select * from tablename where contains(*,'mi*')
> which will only return hits to mice. So your statement "fish*" ' will
> return "fish", "fishes" or "fishing" as these words are inflectionally
> related..." seems to be incorrect, or will only hold true if the
> inflectionally related terms have the same stems, like with fish, fishes,
> and fishing, but not for many English words which do not have the same
> stems, like mouse, mice/tooth, teeth/wake,woke/fight, fought/wear,
> wore/teach, taught/win, won/sit, sat/write, wrote/take, took/sleep,
> slept/run, ran/tell, told/hold, held, off the top of my head .
> Daniel, 9/11 as a search phrase works fine for me. Is is possibly your
> catalog had not completely built? After you removed 9 and 1 from your
noise[vbcol=seagreen]
> word list, did you rebuild your catalog?
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "John Kane" <jt-kane@.comcast.net> wrote in message
> news:uFZGJPreEHA.2804@.TK2MSFTNGP11.phx.gbl...
> the
> removed
file[vbcol=seagreen]
support[vbcol=seagreen]
"words"[vbcol=seagreen]
"9/11*"[vbcol=seagreen]
> '
9/11",
>
|||> Hi Danny,
> Hmm... that someone would be me? <G>
* Yes, it would be you John
> Ok, you need to provide some additional info, specifically, run and post the
> full output of the following SQL script:
> use <your_database_name_here>
> go
> SELECT @.@.language
Returned:
us_english
> SELECT @.@.version
Returned:
Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05 Copyright (c) 1988-2003 Microsoft Corporation Enterprise Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
> -- Note, you may need to set advance options on
> sp_configure 'default full-text language'
Returned:
name | minimum | maximum | config_value | run_value
--+--+--+--+--
default full-text language| 0 |2147483647 | 1033 | 1033
--+--+--+--+--
> EXEC sp_help_fulltext_catalogs
Returned:
ftcatid | Name | Path | Status | Number_Of_Full_Text_Tables
--+--+--+--+--
6 | FT_CSPDB | d:\SQL\MSSQL\FTDATA | 0 | 10
--+--+--+--+--
> EXEC sp_help_fulltext_tables
Returned several records:
Table_Owner | Table_Name | FullText_Key_Index_Name | FullText_Key_ColID | FullText_Index_Active | FullText_Catalog_Name
--+--+--+--+--+--
...
dbo | tblArticle | PK_tblArticle | 1 | 1 | FT_CSPDB
...
> EXEC sp_help_fulltext_columns
Returned many records:
Table_Owner | Table_ID | Table_Name | FullText_Column_Name | FullText_ColID | FullText_Blobtp_ColName | FullText_Bloptp_ColID | FullText_Language
--+--+--+--+--+--+--+--
...
dbo |1721877301| tblArticle | title | 4 | NULL | NULL | 1033
dbo |1721877301| tblArticle | description | 7 | NULL | NULL | 1033
...
> EXEC sp_help tblArticle
This returned many results. Any specific data you want to know John?
> Depending upon the language (the FULLTEXT_LANGUAGE column from
> sp_help_fulltext_columns) of the wordbreaker you are using, have you removed
> all single digits from the noise.<language> (noise.enu = US_English) file
> under the folder: \FTDATA\SQLServer\Config ? If not, then you should and
> then run a Full Population. Note, you will need to stop the "Microsoft
> Search" service first in order to save the changes to the noise.* files.
* Yes, I did
> Additionally, the preceding asterisk "*" in your query ' "*9/11*" ' is
> always ignored and therefore adds no value to your query. SQL FTS support
> only "word prefix" wildcard searches with the asterisk and not "word
> suffix", for example ' "*og" ' would find dog and log, but these "words"
> are not related. However, using a "word prefix" search such as ' "9/11*" '
> returns rows that contain "9/11", or ' "fish*" ' will return "fish",
> "fishes" or "fishing" as these words are inflectionally related...
So, logically if I use CONTAINS(description, ' "9/11" '), I should get record of "Fahrenheit 9/11" right?
(Since it contains 9/11). The fact is, I don't get that record.
Any more ideas John?
Thanks a lot for your help so far.
|||"John Kane" <jt-kane@.comcast.net> wrote in message
news:OvFUl7veEHA.3792@.TK2MSFTNGP09.phx.gbl...
> Yes, I have an idea as to why "Fahrenheit 9/11" is not returned as you are
> using SQL 2000 on Windows 2000 (Win2K), there is a bug relative to
searching
> for words that may have punctuation characters "touching" or in contact
with
> the search word or phrase. The workaround for this bug is to use the
Neutral
> "Language for Word Breaker" on your FT-enable column and then run a Full
> Population. Note, if you change the language for word breaker, be sure to
> remove single numbers from noise.dat (Neutral noise word file) prior to
> running the Full Population.
* Pardon my lack of understanding, could you elaborate more detail on how to
do this John?
> Could you provide the exact content from the FT-enable column where
> "Fahrenheit 9/11" (including any html tags, if present) as well as the
rows
> that contain only "9/11" ?
* The exact content of "Fahrenheit 9/11" is:
Filmmaker Michael Moore is attacking President George W. Bush and the war in
Iraq with <EM>Fahrenheit 9/11</EM>.
* The exact content of "9/11" is:
The 9/11 crisis has provided a dramatic opportunity for manifesting the
gradual strategic shift in the country's domestic and foreign policy
priorities.
|||"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:ONGxDZueEHA.236@.tk2msftngp13.phx.gbl...
> Daniel, 9/11 as a search phrase works fine for me. Is is possibly your
> catalog had not completely built? After you removed 9 and 1 from your
noise
> word list, did you rebuild your catalog?
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
Thanks Hilary for your response.
First of all, my noise word list is completely blank (I have removed them
all).
Then I rebuilt my catalog, and restarted Microsoft Search agent.
Also, if I search 9/11, it returns all records contain 9/11, but NOT records
contain "Fahrenheit 9/11".
It's odd, and I can't figure it out.
- Danny
|||what happens if you remove the <EM>, </EM> tags?
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"DC" <dc@.office> wrote in message
news:%23OzB5GweEHA.140@.TK2MSFTNGP12.phx.gbl...[vbcol=seagreen]
> "John Kane" <jt-kane@.comcast.net> wrote in message
> news:OvFUl7veEHA.3792@.TK2MSFTNGP09.phx.gbl...
are[vbcol=seagreen]
> searching
> with
> Neutral
to
> * Pardon my lack of understanding, could you elaborate more detail on how
to
> do this John?
> rows
> * The exact content of "Fahrenheit 9/11" is:
> Filmmaker Michael Moore is attacking President George W. Bush and the war
in
> Iraq with <EM>Fahrenheit 9/11</EM>.
> * The exact content of "9/11" is:
> The 9/11 crisis has provided a dramatic opportunity for
manifesting the
> gradual strategic shift in the country's domestic and foreign policy
> priorities.
>
|||Stop mssearch. Place a single blank space in your noise word list. Restart
MSSearch and rebuild your catalog.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"DC" <dc@.office> wrote in message
news:OnHHDJweEHA.1652@.TK2MSFTNGP09.phx.gbl...
> "Hilary Cotter" <hilaryk@.att.net> wrote in message
> news:ONGxDZueEHA.236@.tk2msftngp13.phx.gbl...
> noise
> Thanks Hilary for your response.
> First of all, my noise word list is completely blank (I have removed them
> all).
> Then I rebuilt my catalog, and restarted Microsoft Search agent.
> Also, if I search 9/11, it returns all records contain 9/11, but NOT
records
> contain "Fahrenheit 9/11".
> It's odd, and I can't figure it out.
> - Danny
>
someone suggested me to post to this newsgroup.
Anyway, I have articles table like this:
tblArticle
article_id
article_title
article_text
article_title and article_text are part of full-catalog.
And among those article records there is one record like this:
article_id: 999
article_title: "Michael Moore..."
article_text: "...Fahrenheit 9/11..."
When I do search like this:
SELECT *
FROM tblArticle
WHERE CONTAINS(article_text, ' "*Fahrenheit 9/11*" ')
it returned me no result.
However, if I do search using LIKE:
SELECT *
FROM tblArticle
WHERE article_text LIKE '%Fahrenheit 9/11%'
it returned me that article_id: 999
More interesting is if I run this SQL Statement:
SELECT *
FROM tblArticle
WHERE CONTAINS(article_text, ' "*Fahrenheit 9/11*" ')
it returned me records that contains "9/11" but not "Fahrenheit 9/11",
for example:
it returns -> article_text: ... in 9/11 event...
it doesn't return -> article_text: ... Michael Moore who wrote Fahrenheit
9/11 ...
At first, I thought probably full-text catalog were not populated.
So, I did repopulate the full-text catalog, yet it still didn't work.
Also, I thought "Fahrenheit" is part of "noise" words, but it's not listed
in the noise.enu.
Is there any way to resolve this "CONTAINS" issue?
Or is this SQL Server bug?
Thanks in advance,
Danny
Correction:
> More interesting is if I run this SQL Statement:
> SELECT *
> FROM tblArticle
> WHERE CONTAINS(article_text, ' "*9/11*" ')
> it returned me records that contains "9/11" but not "Fahrenheit 9/11",
> for example:
> it returns -> article_text: ... in 9/11 event...
> it doesn't return -> article_text: ... Michael Moore who wrote Fahrenheit
> 9/11 ...
>
|||Hi Danny,
Hmm... that someone would be me? <G>
Ok, you need to provide some additional info, specifically, run and post the
full output of the following SQL script:
use <your_database_name_here>
go
SELECT @.@.language
SELECT @.@.version
-- Note, you may need to set advance options on
sp_configure 'default full-text language'
EXEC sp_help_fulltext_catalogs
EXEC sp_help_fulltext_tables
EXEC sp_help_fulltext_columns
EXEC sp_help tblArticle
go
Depending upon the language (the FULLTEXT_LANGUAGE column from
sp_help_fulltext_columns) of the wordbreaker you are using, have you removed
all single digits from the noise.<language> (noise.enu = US_English) file
under the folder: \FTDATA\SQLServer\Config ? If not, then you should and
then run a Full Population. Note, you will need to stop the "Microsoft
Search" service first in order to save the changes to the noise.* files.
Additionally, the preceding asterisk "*" in your query ' "*9/11*" ' is
always ignored and therefore adds no value to your query. SQL FTS support
only "word prefix" wildcard searches with the asterisk and not "word
suffix", for example ' "*og" ' would find dog and log, but these "words"
are not related. However, using a "word prefix" search such as ' "9/11*" '
returns rows that contain "9/11", or ' "fish*" ' will return "fish",
"fishes" or "fishing" as these words are inflectionally related...
Regards,
John
"Danny" <daniel_c@.NOSPAMmyrealbox.com> wrote in message
news:uoE953peEHA.2440@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> Correction:
Fahrenheit
>
|||John, I am confused by this sentance:
"Additionally, the preceding asterisk "*" in your query ' "*9/11*" ' is
always ignored and therefore adds no value to your query. SQL FTS support
only "word prefix" wildcard searches with the asterisk and not "word
suffix", for example ' "*og" ' would find dog and log, but these "words"
are not related."
In the first part you seem to be saying that the * in the prefix is always
ingored - ie ""Additionally, the preceding asterisk "*" in your query '
"*9/11*" ' is always ignored and therefore adds no value to your query."
and then you go on to say "SQL FTS support only "word prefix" wildcard
searches with the asterisk and not "word suffix", for example ' "*og" '
would find dog and log, but these "words" are not related."
Prefix goes in front, suffix goes behind. Your example of *og matching with
dog and log does not work.
Don't you mean to say "SQL FTS support only "word suffix" wildcard searches
with the asterisk and not "word prefix", for example ' "wild*" ' would find
wildcard and wild, but these "words" are not related."?
Then you go on to say "However, using a "word prefix" search such as '
"9/11*" ' returns rows that contain "9/11", or ' "fish*" ' will return
"fish", "fishes" or "fishing" as these words are inflectionally related..."
I think you mean to say "However, using a "word suffix" search such as '
"9/11*" ' returns rows that contain "9/11", or ' "fish*" ' will return
"fish", "fishes" or "fishing" as these words are inflectionally related...""
And further more the wild card operator does not do stemming, it simply
returns hits to words that start with the letters in front of the *. You are
thinking on the Inflectional operator. To get an idea of what I am talking
add the words mouse to one row and mice to another
Then do this search:
select * from tablename where contains(*,'FormsOF(INFLECTIONAL, Mouse)')
you will get hits to mouse and mice as mouse and mice are inflectionally
related.
as opposed to select * from tablename where contains(*,'mi*')
which will only return hits to mice. So your statement "fish*" ' will
return "fish", "fishes" or "fishing" as these words are inflectionally
related..." seems to be incorrect, or will only hold true if the
inflectionally related terms have the same stems, like with fish, fishes,
and fishing, but not for many English words which do not have the same
stems, like mouse, mice/tooth, teeth/wake,woke/fight, fought/wear,
wore/teach, taught/win, won/sit, sat/write, wrote/take, took/sleep,
slept/run, ran/tell, told/hold, held, off the top of my head .
Daniel, 9/11 as a search phrase works fine for me. Is is possibly your
catalog had not completely built? After you removed 9 and 1 from your noise
word list, did you rebuild your catalog?
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"John Kane" <jt-kane@.comcast.net> wrote in message
news:uFZGJPreEHA.2804@.TK2MSFTNGP11.phx.gbl...
> Hi Danny,
> Hmm... that someone would be me? <G>
> Ok, you need to provide some additional info, specifically, run and post
the
> full output of the following SQL script:
> use <your_database_name_here>
> go
> SELECT @.@.language
> SELECT @.@.version
> -- Note, you may need to set advance options on
> sp_configure 'default full-text language'
> EXEC sp_help_fulltext_catalogs
> EXEC sp_help_fulltext_tables
> EXEC sp_help_fulltext_columns
> EXEC sp_help tblArticle
> go
> Depending upon the language (the FULLTEXT_LANGUAGE column from
> sp_help_fulltext_columns) of the wordbreaker you are using, have you
removed
> all single digits from the noise.<language> (noise.enu = US_English) file
> under the folder: \FTDATA\SQLServer\Config ? If not, then you should and
> then run a Full Population. Note, you will need to stop the "Microsoft
> Search" service first in order to save the changes to the noise.* files.
> Additionally, the preceding asterisk "*" in your query ' "*9/11*" ' is
> always ignored and therefore adds no value to your query. SQL FTS support
> only "word prefix" wildcard searches with the asterisk and not "word
> suffix", for example ' "*og" ' would find dog and log, but these "words"
> are not related. However, using a "word prefix" search such as ' "9/11*"
'
> returns rows that contain "9/11", or ' "fish*" ' will return "fish",
> "fishes" or "fishing" as these words are inflectionally related...
> Regards,
> John
>
>
> "Danny" <daniel_c@.NOSPAMmyrealbox.com> wrote in message
> news:uoE953peEHA.2440@.tk2msftngp13.phx.gbl...
> Fahrenheit
>
|||Hilary,
All I was trying to explain (late at night my time ;-0) was that the
preceding asterisk is ignored in SQL FTS as seems to be a consistent problem
for many people understanding FTS relative to T-SQL LIKE. We still need the
OS and SQL configuration info from Danny. If you want we can discuss this
off-line...
Danny, could you provide your server's configuration info and table info via
the following SQL script and post the full output?
use <your_database_name_here>
go
SELECT @.@.language
SELECT @.@.version
-- Note, you may need to set advance options on
sp_configure 'default full-text language'
EXEC sp_help_fulltext_catalogs
EXEC sp_help_fulltext_tables
EXEC sp_help_fulltext_columns
EXEC sp_help tblArticle
go
Thanks,
John
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:ONGxDZueEHA.236@.tk2msftngp13.phx.gbl...
> John, I am confused by this sentance:
> "Additionally, the preceding asterisk "*" in your query ' "*9/11*" ' is
> always ignored and therefore adds no value to your query. SQL FTS support
> only "word prefix" wildcard searches with the asterisk and not "word
> suffix", for example ' "*og" ' would find dog and log, but these "words"
> are not related."
> In the first part you seem to be saying that the * in the prefix is always
> ingored - ie ""Additionally, the preceding asterisk "*" in your query '
> "*9/11*" ' is always ignored and therefore adds no value to your query."
> and then you go on to say "SQL FTS support only "word prefix" wildcard
> searches with the asterisk and not "word suffix", for example ' "*og" '
> would find dog and log, but these "words" are not related."
> Prefix goes in front, suffix goes behind. Your example of *og matching
with
> dog and log does not work.
> Don't you mean to say "SQL FTS support only "word suffix" wildcard
searches
> with the asterisk and not "word prefix", for example ' "wild*" ' would
find
> wildcard and wild, but these "words" are not related."?
> Then you go on to say "However, using a "word prefix" search such as '
> "9/11*" ' returns rows that contain "9/11", or ' "fish*" ' will return
> "fish", "fishes" or "fishing" as these words are inflectionally
related..."
> I think you mean to say "However, using a "word suffix" search such as '
> "9/11*" ' returns rows that contain "9/11", or ' "fish*" ' will return
> "fish", "fishes" or "fishing" as these words are inflectionally
related...""
> And further more the wild card operator does not do stemming, it simply
> returns hits to words that start with the letters in front of the *. You
are
> thinking on the Inflectional operator. To get an idea of what I am talking
> add the words mouse to one row and mice to another
> Then do this search:
> select * from tablename where contains(*,'FormsOF(INFLECTIONAL, Mouse)')
> you will get hits to mouse and mice as mouse and mice are inflectionally
> related.
> as opposed to select * from tablename where contains(*,'mi*')
> which will only return hits to mice. So your statement "fish*" ' will
> return "fish", "fishes" or "fishing" as these words are inflectionally
> related..." seems to be incorrect, or will only hold true if the
> inflectionally related terms have the same stems, like with fish, fishes,
> and fishing, but not for many English words which do not have the same
> stems, like mouse, mice/tooth, teeth/wake,woke/fight, fought/wear,
> wore/teach, taught/win, won/sit, sat/write, wrote/take, took/sleep,
> slept/run, ran/tell, told/hold, held, off the top of my head .
> Daniel, 9/11 as a search phrase works fine for me. Is is possibly your
> catalog had not completely built? After you removed 9 and 1 from your
noise[vbcol=seagreen]
> word list, did you rebuild your catalog?
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "John Kane" <jt-kane@.comcast.net> wrote in message
> news:uFZGJPreEHA.2804@.TK2MSFTNGP11.phx.gbl...
> the
> removed
file[vbcol=seagreen]
support[vbcol=seagreen]
"words"[vbcol=seagreen]
"9/11*"[vbcol=seagreen]
> '
9/11",
>
|||> Hi Danny,
> Hmm... that someone would be me? <G>
* Yes, it would be you John
> Ok, you need to provide some additional info, specifically, run and post the
> full output of the following SQL script:
> use <your_database_name_here>
> go
> SELECT @.@.language
Returned:
us_english
> SELECT @.@.version
Returned:
Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05 Copyright (c) 1988-2003 Microsoft Corporation Enterprise Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
> -- Note, you may need to set advance options on
> sp_configure 'default full-text language'
Returned:
name | minimum | maximum | config_value | run_value
--+--+--+--+--
default full-text language| 0 |2147483647 | 1033 | 1033
--+--+--+--+--
> EXEC sp_help_fulltext_catalogs
Returned:
ftcatid | Name | Path | Status | Number_Of_Full_Text_Tables
--+--+--+--+--
6 | FT_CSPDB | d:\SQL\MSSQL\FTDATA | 0 | 10
--+--+--+--+--
> EXEC sp_help_fulltext_tables
Returned several records:
Table_Owner | Table_Name | FullText_Key_Index_Name | FullText_Key_ColID | FullText_Index_Active | FullText_Catalog_Name
--+--+--+--+--+--
...
dbo | tblArticle | PK_tblArticle | 1 | 1 | FT_CSPDB
...
> EXEC sp_help_fulltext_columns
Returned many records:
Table_Owner | Table_ID | Table_Name | FullText_Column_Name | FullText_ColID | FullText_Blobtp_ColName | FullText_Bloptp_ColID | FullText_Language
--+--+--+--+--+--+--+--
...
dbo |1721877301| tblArticle | title | 4 | NULL | NULL | 1033
dbo |1721877301| tblArticle | description | 7 | NULL | NULL | 1033
...
> EXEC sp_help tblArticle
This returned many results. Any specific data you want to know John?
> Depending upon the language (the FULLTEXT_LANGUAGE column from
> sp_help_fulltext_columns) of the wordbreaker you are using, have you removed
> all single digits from the noise.<language> (noise.enu = US_English) file
> under the folder: \FTDATA\SQLServer\Config ? If not, then you should and
> then run a Full Population. Note, you will need to stop the "Microsoft
> Search" service first in order to save the changes to the noise.* files.
* Yes, I did
> Additionally, the preceding asterisk "*" in your query ' "*9/11*" ' is
> always ignored and therefore adds no value to your query. SQL FTS support
> only "word prefix" wildcard searches with the asterisk and not "word
> suffix", for example ' "*og" ' would find dog and log, but these "words"
> are not related. However, using a "word prefix" search such as ' "9/11*" '
> returns rows that contain "9/11", or ' "fish*" ' will return "fish",
> "fishes" or "fishing" as these words are inflectionally related...
So, logically if I use CONTAINS(description, ' "9/11" '), I should get record of "Fahrenheit 9/11" right?
(Since it contains 9/11). The fact is, I don't get that record.
Any more ideas John?
Thanks a lot for your help so far.
|||"John Kane" <jt-kane@.comcast.net> wrote in message
news:OvFUl7veEHA.3792@.TK2MSFTNGP09.phx.gbl...
> Yes, I have an idea as to why "Fahrenheit 9/11" is not returned as you are
> using SQL 2000 on Windows 2000 (Win2K), there is a bug relative to
searching
> for words that may have punctuation characters "touching" or in contact
with
> the search word or phrase. The workaround for this bug is to use the
Neutral
> "Language for Word Breaker" on your FT-enable column and then run a Full
> Population. Note, if you change the language for word breaker, be sure to
> remove single numbers from noise.dat (Neutral noise word file) prior to
> running the Full Population.
* Pardon my lack of understanding, could you elaborate more detail on how to
do this John?
> Could you provide the exact content from the FT-enable column where
> "Fahrenheit 9/11" (including any html tags, if present) as well as the
rows
> that contain only "9/11" ?
* The exact content of "Fahrenheit 9/11" is:
Filmmaker Michael Moore is attacking President George W. Bush and the war in
Iraq with <EM>Fahrenheit 9/11</EM>.
* The exact content of "9/11" is:
The 9/11 crisis has provided a dramatic opportunity for manifesting the
gradual strategic shift in the country's domestic and foreign policy
priorities.
|||"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:ONGxDZueEHA.236@.tk2msftngp13.phx.gbl...
> Daniel, 9/11 as a search phrase works fine for me. Is is possibly your
> catalog had not completely built? After you removed 9 and 1 from your
noise
> word list, did you rebuild your catalog?
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
Thanks Hilary for your response.
First of all, my noise word list is completely blank (I have removed them
all).
Then I rebuilt my catalog, and restarted Microsoft Search agent.
Also, if I search 9/11, it returns all records contain 9/11, but NOT records
contain "Fahrenheit 9/11".
It's odd, and I can't figure it out.
- Danny
|||what happens if you remove the <EM>, </EM> tags?
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"DC" <dc@.office> wrote in message
news:%23OzB5GweEHA.140@.TK2MSFTNGP12.phx.gbl...[vbcol=seagreen]
> "John Kane" <jt-kane@.comcast.net> wrote in message
> news:OvFUl7veEHA.3792@.TK2MSFTNGP09.phx.gbl...
are[vbcol=seagreen]
> searching
> with
> Neutral
to
> * Pardon my lack of understanding, could you elaborate more detail on how
to
> do this John?
> rows
> * The exact content of "Fahrenheit 9/11" is:
> Filmmaker Michael Moore is attacking President George W. Bush and the war
in
> Iraq with <EM>Fahrenheit 9/11</EM>.
> * The exact content of "9/11" is:
> The 9/11 crisis has provided a dramatic opportunity for
manifesting the
> gradual strategic shift in the country's domestic and foreign policy
> priorities.
>
|||Stop mssearch. Place a single blank space in your noise word list. Restart
MSSearch and rebuild your catalog.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"DC" <dc@.office> wrote in message
news:OnHHDJweEHA.1652@.TK2MSFTNGP09.phx.gbl...
> "Hilary Cotter" <hilaryk@.att.net> wrote in message
> news:ONGxDZueEHA.236@.tk2msftngp13.phx.gbl...
> noise
> Thanks Hilary for your response.
> First of all, my noise word list is completely blank (I have removed them
> all).
> Then I rebuilt my catalog, and restarted Microsoft Search agent.
> Also, if I search 9/11, it returns all records contain 9/11, but NOT
records
> contain "Fahrenheit 9/11".
> It's odd, and I can't figure it out.
> - Danny
>
Thursday, February 9, 2012
"Cannot start more transactions on this session" - SQL 2005 via ADO
Hi all,
I've just installed a new server, and migrated across our SQL 2000
database to SQL 2005 Workgroup Edition (bundled with SBS 2003 R2
Premium).
I migrated the databse via a backup/restore.
We have an ASP application which connects to the database from our
intranet. When we issue a "connection.begintrans" we get a hard error:
"cannot start more transactions on this session"
We *know* this is not a nested transaction as we only have one
instance of begintrans in our code, and it's only being called once.
At least, it's not a nesting that WE have introduced.
If we set the "SQL Compatibility" option to "SQL Server 2000 (80)" we
do not experience the error. But if we set the "SQL Compatibility"
option to "SQL Server 2005 (90)" we do experience the error.
Can anyone suggest what has changed (or what needs to be changed) to
resolve this? Our connection string to the database is:
pCn.ConnectionString = "Provider=SQLOLEDB.1;" & _
"User ID=" & pUser & _
";Password=" & pPassword & _
";Database=" & pDatabase & _
";Server=" & pServer
Many thanks in advance,
Jim
> If we set the "SQL Compatibility" option to "SQL Server 2000 (80)" we
> do not experience the error. But if we set the "SQL Compatibility"
> option to "SQL Server 2005 (90)" we do experience the error.
I'm not aware of anything related to the database compatibility level that
would cause these symptoms. I haven't been able to repro this error
(VBScript below) so it may be related to the specifics of your data access
within the client transaction. You might try running a Profiler trace to
see if you can spot differences based on the compatibility level. If that
doesn't help, try posting code that can be run to reproduce the issue.
Set conn = CreateObject("ADODB.Connection")
conn.Open "Provider=SQLOLEDB;Data Source=MyServer;Initial
Catalog=Test;Integrated Security=SSPI"
conn.BeginTrans
conn.Execute "INSERT INTO dbo.MyTable VALUES(1) SELECT 1"
'conn.BeginTrans 'causes error if comment removed
conn.Execute "INSERT INTO dbo.MyTable VALUES(1)"
conn.CommitTrans
conn.Close
MsgBox "Done"
Hope this helps.
Dan Guzman
SQL Server MVP
"Jim" <jim@.nospam.com> wrote in message
news:l9h2539dc08v081npkeu9ot7d5fo4e97jh@.4ax.com...
> Hi all,
> I've just installed a new server, and migrated across our SQL 2000
> database to SQL 2005 Workgroup Edition (bundled with SBS 2003 R2
> Premium).
> I migrated the databse via a backup/restore.
> We have an ASP application which connects to the database from our
> intranet. When we issue a "connection.begintrans" we get a hard error:
> "cannot start more transactions on this session"
> We *know* this is not a nested transaction as we only have one
> instance of begintrans in our code, and it's only being called once.
> At least, it's not a nesting that WE have introduced.
> If we set the "SQL Compatibility" option to "SQL Server 2000 (80)" we
> do not experience the error. But if we set the "SQL Compatibility"
> option to "SQL Server 2005 (90)" we do experience the error.
> Can anyone suggest what has changed (or what needs to be changed) to
> resolve this? Our connection string to the database is:
> pCn.ConnectionString = "Provider=SQLOLEDB.1;" & _
> "User ID=" & pUser & _
> ";Password=" & pPassword & _
> ";Database=" & pDatabase & _
> ";Server=" & pServer
>
> Many thanks in advance,
>
> Jim
I've just installed a new server, and migrated across our SQL 2000
database to SQL 2005 Workgroup Edition (bundled with SBS 2003 R2
Premium).
I migrated the databse via a backup/restore.
We have an ASP application which connects to the database from our
intranet. When we issue a "connection.begintrans" we get a hard error:
"cannot start more transactions on this session"
We *know* this is not a nested transaction as we only have one
instance of begintrans in our code, and it's only being called once.
At least, it's not a nesting that WE have introduced.
If we set the "SQL Compatibility" option to "SQL Server 2000 (80)" we
do not experience the error. But if we set the "SQL Compatibility"
option to "SQL Server 2005 (90)" we do experience the error.
Can anyone suggest what has changed (or what needs to be changed) to
resolve this? Our connection string to the database is:
pCn.ConnectionString = "Provider=SQLOLEDB.1;" & _
"User ID=" & pUser & _
";Password=" & pPassword & _
";Database=" & pDatabase & _
";Server=" & pServer
Many thanks in advance,
Jim
> If we set the "SQL Compatibility" option to "SQL Server 2000 (80)" we
> do not experience the error. But if we set the "SQL Compatibility"
> option to "SQL Server 2005 (90)" we do experience the error.
I'm not aware of anything related to the database compatibility level that
would cause these symptoms. I haven't been able to repro this error
(VBScript below) so it may be related to the specifics of your data access
within the client transaction. You might try running a Profiler trace to
see if you can spot differences based on the compatibility level. If that
doesn't help, try posting code that can be run to reproduce the issue.
Set conn = CreateObject("ADODB.Connection")
conn.Open "Provider=SQLOLEDB;Data Source=MyServer;Initial
Catalog=Test;Integrated Security=SSPI"
conn.BeginTrans
conn.Execute "INSERT INTO dbo.MyTable VALUES(1) SELECT 1"
'conn.BeginTrans 'causes error if comment removed
conn.Execute "INSERT INTO dbo.MyTable VALUES(1)"
conn.CommitTrans
conn.Close
MsgBox "Done"
Hope this helps.
Dan Guzman
SQL Server MVP
"Jim" <jim@.nospam.com> wrote in message
news:l9h2539dc08v081npkeu9ot7d5fo4e97jh@.4ax.com...
> Hi all,
> I've just installed a new server, and migrated across our SQL 2000
> database to SQL 2005 Workgroup Edition (bundled with SBS 2003 R2
> Premium).
> I migrated the databse via a backup/restore.
> We have an ASP application which connects to the database from our
> intranet. When we issue a "connection.begintrans" we get a hard error:
> "cannot start more transactions on this session"
> We *know* this is not a nested transaction as we only have one
> instance of begintrans in our code, and it's only being called once.
> At least, it's not a nesting that WE have introduced.
> If we set the "SQL Compatibility" option to "SQL Server 2000 (80)" we
> do not experience the error. But if we set the "SQL Compatibility"
> option to "SQL Server 2005 (90)" we do experience the error.
> Can anyone suggest what has changed (or what needs to be changed) to
> resolve this? Our connection string to the database is:
> pCn.ConnectionString = "Provider=SQLOLEDB.1;" & _
> "User ID=" & pUser & _
> ";Password=" & pPassword & _
> ";Database=" & pDatabase & _
> ";Server=" & pServer
>
> Many thanks in advance,
>
> Jim
Subscribe to:
Posts (Atom)
