Function to determine weekoftheyear.

I looking a function to determine the w of the year. In excel I use
WNum, but I need this for sql server.
thank you.select DATEPART(wk,getdate())
or
select DATEPART(ww,getdate())
"Miguel Arenas" wrote:

> I looking a function to determine the w of the year. In excel I use
> WNum, but I need this for sql server.
> thank you.|||Both work.
MattB wrote:
> select DATEPART(wk,getdate())
> or
> select DATEPART(ww,getdate())
> "Miguel Arenas" wrote:
>|||SQL Server w is different than the ISO w
Do a search for FUNCTION dbo.ISOw, you can also find the code here
(http://msdn2.microsoft.com/en-us/library/ms186755.aspx) if you scroll
down
Denis the SQL Menace
http://sqlservercode.blogspot.com/
MattB wrote:
> select DATEPART(wk,getdate())
> or
> select DATEPART(ww,getdate())
> "Miguel Arenas" wrote:
>

Fulltext Stops (Deadlocks Occurs)

How can I determine if I need to use the SQL Server
Fulltext services? This error messages occurs when the
CPU is greater than 80% for extended period of time.
Usually the error message occurs when the system has a
major deadlock and is using all the resources to resolve
the deadlock.
Please help me resolve this issue.
Thank You,
Mike
Error Messages from Cluster:
JobID = 881
KSName = 1838:General_EventLogV1-DBSpecial
MC MachineName = GHLDBB01A
Object Name = <NT_MachineFolder:GHLDBB01A>
EventMsg = 10 NT Events from System - batch 7
LongMsg = Typemm/dd/yyyy hh:mm:ssSource
CategoryEventUserMachineName
Description
Error4/17/2004 7:34:45 PMClusSvc
(4)1069N/AGHLDBB01A
Cluster resource 'SQL Server Fulltext (JSQL_OLTP)' failed.
Error4/17/2004 7:34:45 PMService Control Manager
None7031N/AGHLDBB01A
The Microsoft Search service terminated unexpectedly. It
has done this 5 time(s). The following corrective action
will be taken in 0 milliseconds: No action.
Error4/17/2004 7:34:46 PMClusSvc
(4)1069N/AGHLDBB01A
Cluster resource 'SQL Server Fulltext (JSQL_OLTP)' failed.
Error4/17/2004 7:36:39 PMService Control Manager
None7009N/AGHLDBB01A
Timeout (30000 milliseconds) waiting for the Microsoft
Search service to connect.
Error4/17/2004 7:36:49 PMService Control Manager
None7000N/AGHLDBB01A
The Microsoft Search service failed to start due to the
following error:
The service did not respond to the start or control
request in a timely fashion.
Error4/17/2004 7:36:50 PMDCOM
None10005ecmsvcGHLDBB01A
DCOM got error '%%%1' attempting to start the service %2
with arguments '%3'
in order to run the server:
%4
Error4/17/2004 7:38:23 PMService Control Manager
None7009N/AGHLDBB01A
Timeout (30000 milliseconds) waiting for the Microsoft
Search service to connect.
Error4/17/2004 7:38:32 PMService Control Manager
None7000N/AGHLDBB01A
The Microsoft Search service failed to start due to the
following error:
The service did not respond to the start or control
request in a timely fashion.
Error4/17/2004 7:38:38 PMDCOM
None10005ecmsvcGHLDBB01A
DCOM got error '%%%1' attempting to start the service %2
with arguments '%3'
in order to run the server:
%4
Error4/17/2004 7:38:46 PMClusSvc
(4)1069N/AGHLDBB01A
Cluster resource 'SQL Server Fulltext (JSQL_OLTP)' failed.
Mike,
First of all, can I assume that this is a SQL Server 2000 clustered
environment? Could you post the full output of SELECT @.@.version -- as this
is helpful in troubleshooting SQL FTS issues.
Secondly, could you post any "Microsoft Search" or MssCi source events
(warnings & errors) from your server JSQL_OLTP that occurred at or near the
time of the below error messages? As well as any Microsoft Search entries in
the cluster.log file.
Yes, the "Microsoft Search" (mssearch.exe) service and does use up to 90% of
CPU usage, for brief periods of time, either during shadow merge or master
merge that occurs during either Full or Incremental Populations or at
midnight. How long is the "extended period of time" that the CPU is greater
than 80%? Does the MSSearch service ever hit 100% CPU usage or does it just
peak at 80% and then go down to normal usage levels?
Also, do this server have multiple CPU's? If so, then the MSSearch service
can be "assigned" or bound to a specific or set of specific CPU's, while SQL
Server 2000 can be set to a specific set of CPU's (via sp_configure), such
that the MSSearch CPU usage will not affect your SQL Server 2000 processing.
Regards,
John
"Mike" <anonymous@.discussions.microsoft.com> wrote in message
news:12c001c42642$0cd095f0$a001280a@.phx.gbl...
> How can I determine if I need to use the SQL Server
> Fulltext services? This error messages occurs when the
> CPU is greater than 80% for extended period of time.
> Usually the error message occurs when the system has a
> major deadlock and is using all the resources to resolve
> the deadlock.
> Please help me resolve this issue.
> Thank You,
> Mike
>
> Error Messages from Cluster:
> JobID = 881
> KSName = 1838:General_EventLogV1-DBSpecial
> MC MachineName = GHLDBB01A
> Object Name = <NT_MachineFolder:GHLDBB01A>
> EventMsg = 10 NT Events from System - batch 7
> LongMsg = Type mm/dd/yyyy hh:mm:ss Source
> Category Event User MachineName
> Description
> Error 4/17/2004 7:34:45 PM ClusSvc
> (4) 1069 N/A GHLDBB01A
> Cluster resource 'SQL Server Fulltext (JSQL_OLTP)' failed.
>
> Error 4/17/2004 7:34:45 PM Service Control Manager
> None 7031 N/A GHLDBB01A
> The Microsoft Search service terminated unexpectedly. It
> has done this 5 time(s). The following corrective action
> will be taken in 0 milliseconds: No action.
>
> Error 4/17/2004 7:34:46 PM ClusSvc
> (4) 1069 N/A GHLDBB01A
> Cluster resource 'SQL Server Fulltext (JSQL_OLTP)' failed.
>
> Error 4/17/2004 7:36:39 PM Service Control Manager
> None 7009 N/A GHLDBB01A
> Timeout (30000 milliseconds) waiting for the Microsoft
> Search service to connect.
>
> Error 4/17/2004 7:36:49 PM Service Control Manager
> None 7000 N/A GHLDBB01A
> The Microsoft Search service failed to start due to the
> following error:
> The service did not respond to the start or control
> request in a timely fashion.
>
> Error 4/17/2004 7:36:50 PM DCOM
> None 10005 ecmsvc GHLDBB01A
> DCOM got error '%%%1' attempting to start the service %2
> with arguments '%3'
> in order to run the server:
> %4
>
> Error 4/17/2004 7:38:23 PM Service Control Manager
> None 7009 N/A GHLDBB01A
> Timeout (30000 milliseconds) waiting for the Microsoft
> Search service to connect.
>
> Error 4/17/2004 7:38:32 PM Service Control Manager
> None 7000 N/A GHLDBB01A
> The Microsoft Search service failed to start due to the
> following error:
> The service did not respond to the start or control
> request in a timely fashion.
>
> Error 4/17/2004 7:38:38 PM DCOM
> None 10005 ecmsvc GHLDBB01A
> DCOM got error '%%%1' attempting to start the service %2
> with arguments '%3'
> in order to run the server:
> %4
>
> Error 4/17/2004 7:38:46 PM ClusSvc
> (4) 1069 N/A GHLDBB01A
> Cluster resource 'SQL Server Fulltext (JSQL_OLTP)' failed.
>
|||I tried to answer several questions listed below.
Thank You,
The server CPU can be greater than 90% for 5 - 7 minutes.
The server has 8 processors at 2 GHZs.
__________________________________________________ _________
Event Viewer Errors
Cluster resource 'SQL Server Fulltext (VSQL_OLTP)' failed.
The Microsoft Search service terminated unexpectedly. It
has done this 5 time(s). The following corrective action
will be taken in 0 milliseconds: No action.
DCOM got error "The service did not respond to the start
or control request in a timely fashion. " attempting to
start the service mssearch with arguments "" in order to
run the server:
{C731055A-AC80-11D1-8DF3-00C04FB6EF4F}
Timeout (30000 milliseconds) waiting for the Microsoft
Search service to connect.
__________________________________________________ _________
Select @.@.Version
Output:
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 3)
(1 row(s) affected)

>--Original Message--
>Mike,
>First of all, can I assume that this is a SQL Server 2000
clustered
>environment? Could you post the full output of SELECT
@.@.version -- as this
>is helpful in troubleshooting SQL FTS issues.
>Secondly, could you post any "Microsoft Search" or MssCi
source events
>(warnings & errors) from your server JSQL_OLTP that
occurred at or near the
>time of the below error messages? As well as any
Microsoft Search entries in
>the cluster.log file.
>Yes, the "Microsoft Search" (mssearch.exe) service and
does use up to 90% of
>CPU usage, for brief periods of time, either during
shadow merge or master
>merge that occurs during either Full or Incremental
Populations or at
>midnight. How long is the "extended period of time" that
the CPU is greater
>than 80%? Does the MSSearch service ever hit 100% CPU
usage or does it just
>peak at 80% and then go down to normal usage levels?
>Also, do this server have multiple CPU's? If so, then the
MSSearch service
>can be "assigned" or bound to a specific or set of
specific CPU's, while SQL
>Server 2000 can be set to a specific set of CPU's (via
sp_configure), such
>that the MSSearch CPU usage will not affect your SQL
Server 2000 processing.
>Regards,
>John
>
>"Mike" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:12c001c42642$0cd095f0$a001280a@.phx.gbl...
failed.[vbcol=seagreen]
It[vbcol=seagreen]
action[vbcol=seagreen]
failed.[vbcol=seagreen]
failed.
>
>.
>
|||You're welcome, Mike,
Relative to DCOM on this server, have you made any changes to the default
DCOM configuration? Specifically, confirm that the 'Default Authentication
Level' in the DCOMCNFG's Default Properties tab is set to "None" and that
the 'Default Impersonation Level' (on the same tab) is set to "Anonymous".
Both are the default DCOM settings.
Also, I should of been more specific with the event log messages... The
"Microsoft Search" and MssCi are only recorded in the Application event log
and not the System log. Please, review the Application event log as well for
related messages.
Finally, the server's CPU usage of greater than 90% for 5 - 7 minutes, is
normal and expected during either the "shadow merge" or "Master Merge"
processes that the MSSearch service does to merge new "word lists" into it's
file system and then at the end of this process or at midnight (controllable
via a registry key). This process occurs during either a Full or Incremental
Population. Do you have frequently scheduled SQLServerAgent jobs that
execute either a Full or Incremental Population? If so, you may want to
either reduce the frequency or checkout the new SQL Server 2000 feature
"change tracking" and "update index in background" that will give you near
real-time updates of the FT Catalog when the FT-enable table(s) column(s)
are updated. Review the SQL Server 2000 BOL for more info on CT and UIiB.
Once the "Microsoft Search service terminated unexpectedly" issue is
identified and resolved, and if the MSSearch CPU usage is affecting your SQL
Server process, I can show you how to set the MSSearch process to one or
more of your 8 procs.
Regards,
John
"Mike" <anonymous@.discussions.microsoft.com> wrote in message
news:138a01c4265b$cb6eeb60$a401280a@.phx.gbl...[vbcol=seagreen]
> I tried to answer several questions listed below.
> Thank You,
>
> The server CPU can be greater than 90% for 5 - 7 minutes.
> The server has 8 processors at 2 GHZs.
> __________________________________________________ _________
> Event Viewer Errors
> Cluster resource 'SQL Server Fulltext (VSQL_OLTP)' failed.
> The Microsoft Search service terminated unexpectedly. It
> has done this 5 time(s). The following corrective action
> will be taken in 0 milliseconds: No action.
> DCOM got error "The service did not respond to the start
> or control request in a timely fashion. " attempting to
> start the service mssearch with arguments "" in order to
> run the server:
> {C731055A-AC80-11D1-8DF3-00C04FB6EF4F}
> Timeout (30000 milliseconds) waiting for the Microsoft
> Search service to connect.
> __________________________________________________ _________
> Select @.@.Version
> Output:
> 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 3)
>
> (1 row(s) affected)
>
>
>
> clustered
> @.@.version -- as this
> source events
> occurred at or near the
> Microsoft Search entries in
> does use up to 90% of
> shadow merge or master
> Populations or at
> the CPU is greater
> usage or does it just
> MSSearch service
> specific CPU's, while SQL
> sp_configure), such
> Server 2000 processing.
> message
> failed.
> It
> action
> failed.
> failed.

Full-Text Search - How to determine word offset in CONTAINS query.

Does anyone know if it is possible to determine the relative word offset (the Occ) from a simple-term query such as:-

SELECT Comments
FROM Production.ProductReview
WHERE CONTAINS(Comments, ' "mountain biking" ');

So, given the text:-

"Maybe it's just because I'm new to mountain biking, but I had a terrible time getting used to these pedals."

I would like the query would return both the text and the word offset of 8. To me, it seems like this would be quite useful as I want to highlight the found text for the user to see. Obviously I can do a post-SELECT scan of the string to find the values but this would seem unnecessary.

If anyone can give me any pointers I'd be very grateful.

Thanks

Mark,
While the word offset (8 in your example) is not directly available via a SQL FTS query, you can use PATINDEX to achieve the same effect:

The following SQL FTS query on the pubs table pub_info will return rows that match the FTS search word (books) and the near by words from 20 characters before the searched keyword(books) for a total lenght of 100 characters.

SELECT pub_id, SubString(pr_info,PatIndex ('%books%',pr_info)-20,100)
FROM pub_info
WHERE Contains(pr_info, 'books')

Highlighting the word or sometimes referred to as Hit-Highlighting is a very important psychological help to searchers looking for the keyword or phrase "in context" with the words near (before and following) the keyword/phrase. This is a FAQ in the public fulltext newsgroup that doesn't have a clean implemention yet...

Regards,
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/

|||John

I suppose there must be a reason why the offset is not available - I just can't think of it at the moment. It would be good to ask the SQL Server 2005 development team for their response as to why it's not available. Probably something to do with inflectional forms and stemming, as well as the more advanced query features available through the FREETEXT predicate.

I agree the hit-highlighting is an important aid to the user - I think I'll have to implement something along the lines that you've suggested, and maybe if Microsoft provide with a mechanism in the future then retro-fit it.

Many thanks.

Regards
Mark|||

Mark,
I've waited until a SQL Server 2005 development team member would reply to this posting, but they are most likely busy getting Yukon ready to ship... In the meantime, I can only speculate why the offsets are not available... Most likely this is due to either disk space considerations (related to performance) or functionality as offsets are normally used to help determine the "nearness" of one token (word or term) to another. The "nearness" in SQL Server 2000 and SQL Server 2005 is hardcoded to approx. 50 words / tokens / terms. Nearness is also affected by noise (stop) words as well as end-of-sentence and end-of-paragraph characters. I know for a fact (direct experiene) that at least one potential SQL Server (2000 and 2005) FTS customer decided to go with a non-Microsoft solution solely based upon the lack of functionality and lack of customization of the NEAR functionality, I'm sad to say... Note, they didn't implement ORACLE or IBM DB2, but stayed with SQL Server as their appliation was written to SQL Server, it was just the FTS functionality that was not sufficient .

As for hit-highlighting (or highlighting of search keywords or phrases), I too agree that this is an important functionality that is missing in SQL Server 2000 and 2005 Full-text Search (SQL FTS) and hopefully this will be implemented in a post-Yukon version of SQL Server. Note, this feature would have to be newly implemented and most likely Microsoft will not be able to retro-fit it back to SQL Server 2000 or SQL Server 2005, I'm sad to say...

In retrospect, both of these issues are well known to the Microsoft SQL Server 2005 FTS development team, but the overall performance of SQL FTS (greatly improved!) had to take priority and in the long term, there is only so much that can be done with a software server product as large as SQL Server 2005 and still keep it stable, reliable, performant and meet the RTM shipping deadline! My hat is off to the SQL FTS Dev team!

Thanks,
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/

|||

John

We've come to the same conclusion that they have other priorities - and we can't blame them. FTS is so close to what we need/want, but falls at the final hurdle for our particular requirements. We will continue to use SQL Server 2005 for all other data management because it's a superb platform, and we will use it as the basis for engineering our own specific FTS tool.

We will, of course, keep abreast of all new FTS features in future releases with much anticipation.

Many thanks for your feedback.

Regards
Mark

Feedback-Ferret?
www.metriq.co.uk