Search on Numeric property on Sharepoint 2003
Tako
I have problems searching numeric attributes in a SPS2003, I have found
this post:
anybody can verify that it is a Microsoftæ„€ bug?
Thanksss
rdcpro
I can't use a CAST in the where clause to cast the custom property string to
an integer like:
WHERE (CAST("urn:schemas.microsoft.com:htmlinfo:metainfo:YEAR" AS DBTYPE_I8)
= 2002)
yet this cast works fine in the SELECT clause:
SELECT CAST ("urn:schemas.microsoft.com:htmlinfo:metainfo:YEAR" AS DBTYPE_I8),
which produces:
<Property>
<Name>urn:schemas.microsoft.com:htmlinfo:metainfo:YEAR</Name>
<Type>Int64</Type>
<Value>2002</Value>
</Property>
I'm "expecting" (hoping, really) to find that there is a hotfix for this, as
it sure looks like a bug to me. If and when I find out more, I'll post it
here as well as in a separate thread I started in the managed newsgroups at:
Regards,
Mike Sharp
"Tako" wrote:
> anybody can verify that it is a Microsoft´s bug?
>
> Thanksss
>
>
>
>
Niklas E
where clause. We want to find all old documents and we have a meta-tag which
is a expiredate.
Did you receive any workaround or Hotfix? Any ideas?
Best regards
Niklas E
"rdcpro" <rdc...@discussions.microsoft.com> wrote in message
news:0F0C4514-0F45-4DF2...@microsoft.com...
> > anybody can verify that it is a Microsoftæ„€ bug?
> >
> > Thanksss
> >
> >
> >
> >
Tako
> We are having the same problem with a date comparison on a column in
> the where clause. We want to find all old documents and we have a
> meta-tag which is a expiredate.
>
Look this:
http://support.microsoft.com/?id=883919
But I have not try the patch
rdcpro
PSS support engineer hasn't mentioned it.
So far I've obtained a copy of htmlprop.dll, which came from the Platform
SDK. You register this dll in place of the nlhtml.dll that normally
processes HTML pages. The source code indicates that htmlprop.dll processes
certain meta tags, and passes the rest back to nlhtml.dll.
However I was unable to compile the source code in the 2003 SDK without
issues, but obtained a version dated Dec. 2000. Unfortunately, it does not
seem to work, event though processexplorer shows the dll is loading.
I'll be downloading and installing the patch mentioned in the SDK shortly,
and will post on the results later today.
Regards,
Mike Sharp
"Tako" wrote:
> >>> anybody can verify that it is a Microsoft´s bug?
> >>>
> >>> Thanksss
>
>
>
>
Niklas E
I will try such a hotfix as well. I found a later one, which I am going to
download and try out, which you might want to use instead:
http://support.microsoft.com/Default.aspx?id=887819
Does that compiling of htmlprop.dll include C++ programming or is it C#
code? Where did you find the 2003 SDK?
If one only could get integer search to work in the WHERE clause then you
could make a workaround around date problems and have three separated meta
tags; year, month and day.
I have tried with a lot of different casts to DATE, DATETIME, DBTYPE* etc,
e.g. CAST ('2004-01-13' AS DATE) and CONVERT(DATE, '2004-01-13', 120), but
none works as you say. They all work like a charm in QA though.
One of my queries:
-------------------------
SELECT "urn:schemas.microsoft.com:htmlinfo:metainfo:DISPLAYNAME",
"DAV:href",
"urn:schemas.microsoft.com:fulltextqueryinfo:description",
"urn:schemas.microsoft.com:fulltextqueryinfo:sitename",
"urn:schemas.microsoft.com:fulltextqueryinfo:rank",
"urn:schemas.microsoft.com:fulltextqueryinfo:sdid",
"urn:schemas.microsoft.com:htmlinfo:metainfo:INFOOBJECTTYPE"
FROM ABIndex..SCOPE()
WHERE (CAST("urn:schemas.microsoft.com:htmlinfo:metainfo:STARTDATE" AS
DBTYPE_DBDATE) >= CAST('2004-01-01' as DBTYPE_DBDATE))
--------------------------
It is very frustrating that SharePoint (search.asmx) suppresses the error
messages and only returns the very informative: Internal Server Error as
well instead of passing on the real error from the database. Anyone knows
how to get the real errors?
Keep us posted.
Best regards
Niklas E
"rdcpro" <rdc...@discussions.microsoft.com> wrote in message
news:B6BEBFF7-0EF7-4935...@microsoft.com...
> > >>> anybody can verify that it is a Microsoftæ„€ bug?
> > >>>
> > >>> Thanksss
> >
> >
> >
> >
rdcpro
And yeah, you're compiling a bunch of C++ code. I downloaded the platform
SDK from my Subscriber Downloads...but you can get it from
http://www.microsoft.com/msdownload/platformsdk/sdkupdate/
Yes, an error message would be very nice... However the Sharepoint log files
can be accessed through Central Admin:
http://SharePointServerName:PortNumber/sps/DiagnosticLogs.aspx
Go to Central Admin, and it's at the very bottom. Configure the the process
you're interested in tolog tracing information...
Also, regarding your sample query, I believe I read in the documentation
that the left hand side of a query must be a column identifier, and the right
hand side must be a literal, though DATEADD is ok. You cannot cast the right
side of a predicate. So even if Search.asmx worked as advertised, this query
wouldn't work:
WHERE (CAST("urn:schemas.microsoft.com:htmlinfo:metainfo:STARTDATE" AS
DBTYPE_DBDATE) >= CAST('2004-01-01' as DBTYPE_DBDATE))
Of course, this one *should*:
WHERE (CAST("urn:schemas.microsoft.com:htmlinfo:metainfo:YEAR" AS
DBTYPE_I8) >= 2002)
I have noticed also that when you cast a date in the select clause, this
seems to work:
CAST("urn:schemas.microsoft.com:htmlinfo:metainfo:STARTDATE" AS DBTYPE_DATE)
IOW, the date type is DBTYPE_DATE, not DBTYPE_DBDATE (though it may be that
both work).
Regards,
Mike Sharp
"Niklas E" wrote:
> > > >>> anybody can verify that it is a Microsoft´s bug?
> > > >>>
> > > >>> Thanksss
> > >
> > >
> > >
> > >
>
>
>
Niklas E
Then this SHOULD (if SharePoint didn't have bugs) work too I guess:
WHERE (CAST("urn:schemas.microsoft.com:htmlinfo:metainfo:STARTDATE" AS
DBTYPE_DATE) >= '2004-10-10')
The hotfix I mentioned includes the same files if you look in the list of
files, but they are of later versions so they should contain both the old
hotfix collection and the later hotfixes. This seems to be some cumulative
collection of hotfixes.
This is what I get in my error log which I have enabled now in the page you
mentioned:
11/23/04 10:26:59:375 UNK 00000000 00000624 TRIPOLIQP : Oledb Exception :
The query is CMD-PROP="Defer catalog verification=1;Always use content
index=1;Maximum Rows=552;Catalog List ID=3;"Select
"urn:schemas.microsoft.com:fulltextqueryinfo:sdid",
"urn:schemas.microsoft.com:htmlinfo:metainfo:DISPLAYNAME", "DAV:href",
"urn:schemas.microsoft.com:fulltextqueryinfo:description",
"urn:schemas.microsoft.com:fulltextqueryinfo:sitename",
"urn:schemas.microsoft.com:fulltextqueryinfo:rank",
"urn:schemas.microsoft.com:fulltextqueryinfo:sdid",
"urn:schemas.microsoft.com:htmlinfo:metainfo:INFOOBJECTTYPE" FROM
ABIndex..SCOPE() WHERE
(CAST("urn:schemas.microsoft.com:htmlinfo:metainfo:STARTDATE" AS
DBTYPE_DATE) >= '2004-10-10') ORDER BY
"urn:schemas.microsoft.com:fulltextqueryinfo:rank" DESC
11/23/04 10:26:59:375 UNK 00000000 00000624 SPS QP Exception in Execute
method : HR = One or more errors occurred during processing of command.
Exception Info : System.Data.OleDb.OleDbException: One or more errors
occurred during processing of command. at
Microsoft.SharePoint.Portal.Search.f.a(String A_0, String A_1, g A_2, String
A_3, Boolean A_4, Int64 A_5, String& A_6, UInt32& A_7, UInt32& A_8, Boolean
A_9) at Microsoft.SharePoint.Portal.Search.QueryProvider.a(String A_0,
Boolean A_1, String& A_2, UInt32& A_3, UInt32& A_4, Boolean A_5, HttpContext
A_6, PortalContext A_7)
Best regards
Niklas E
"rdcpro" <rdc...@discussions.microsoft.com> wrote in message
news:CB752FF0-E35F-4989...@microsoft.com...
> > > > >>> anybody can verify that it is a Microsoftæ„€ bug?
> > > > >>>
> > > > >>> Thanksss
> > > >
> > > >
> > > >
> > > >
> >
> >
> >
Niklas E
MX_Properties
In there there are property-types that might be altered to change the
data-type.
This seems like a bad hack, but if it works, it would be nice. Date seems to
be of type 8 and string of type 1.
I did a quick change, restart of SP-services, iisreset and searched with
WHERE "urn:schemas.microsoft.com:htmlinfo:metainfo:STARTDATE" >= '2004-2-4
5:0:0'
and
WHERE "urn:schemas.microsoft.com:htmlinfo:metainfo:STARTDATE" >=
'2004-10-10'
but they didn't return any data :(
/Niklas E
"Niklas E" <raven_t...@hotmail.com> wrote in message
news:#q#h9GU0E...@TK2MSFTNGP09.phx.gbl...
rdcpro
this should return data:
"urn:schemas.microsoft.com:htmlinfo:metainfo:STARTDATE" >
DATEADD(MONTH,-9, GETGMTDATE())
I'll have to take a look at that table myself...I doubt I'd be able to use
such a hack for our production site, but it would be good information to know.
I have been working on a "fallback" approach that I'll use if all else
fails. We only have about 3000 documents on our site, so in the case where a
user does a date range search, I'll have to set the number of returned
documents to a very large number (more than the number of documents in our
site), and handle the returned XML as a whole, doing the date filtering in an
XSLT.
This is clearly not the best solution, but at the moment it's the only
approach that I'm sure will work, and I really do have to get rid of Site
Server! ;^)
My other needs for date filtering are only by year, so I can do that with a
simple string comparison. But I'm still hoping my PSS case bears fruit.
Regards,
Mike Sharp
"Niklas E" wrote:
> > > > > > >>> anybody can verify that it is a Microsoft´s bug?
> > > > > > >>>
> > > > > > >>> Thanksss
> > > > > >
> > > > > >
> > > > > >
> > > > > >
> > > >
> > > >
> > > >
> >
> >
>
>
>
Niklas E
I have also tried to use the htmlprop.dll solution described here:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/indexsrv/html/ixufilt_0lwl.asp
Although both I and a colleague followed it step by step, we didn't get any
results :( You might have better luck?
Best regards
Niklas E
"rdcpro" <rdc...@discussions.microsoft.com> wrote in message
news:57416460-9390-4A7C...@microsoft.com...
> > > > > > > >>> anybody can verify that it is a Microsoftæ„€ bug?
> > > > > > > >>>
> > > > > > > >>> Thanksss
> > > > > > >
> > > > > > >
> > > > > > >
> > > > > > >
> > > > >
> > > > >
> > > > >
> > >
> > >
> >
> >
> >
rdcpro
with no discernable difference in behavior. I used Process Explorer (from
sysinternals), and I can see that htmlprop.dll is being loaded by the search
service when it crawls, but it looks as though all IFilter processing is
being handed back to the original nlhtml.dll filter. If I could get the
htmlprop.dll project file to compile, I could probably produces some sort of
debug spew to make sure that the htmlprop.dll is even recognizing the meta
tag content.
But I'm still waiting on my PSS case, so maybe there will be a solution in
the near future.
Keep me posted if you have any breaks, and I'll do the same.
rdcpro
In case the previous posts aren't available anymore, the issue is that
htmlprop.dll, found in the Windows Platform SDK, is supposed to convert
a variety of DateTime meta tags (with certain formats) to a DateTime
datatype, so that functions such as DATEADD() can be used in an SPS
query. You can't use CAST in a where clause (a limitation of the
MSSQLFT language), and by default all custom metadata is returned as
String.
A similar situation exists with integer types as well. The problem is,
no matter what I did, the custom metadata was always coming back as a
string. I followed all the instructions posted in the platform SDK,
plus a bunch that PSS provided, to no avail.
While this isn't documented anywhere, it turns out that this key:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\SPSSearch\ContentIndexCommon\Filters\CLSID\{25336920-03F9-11CF-8FD0-00AA00686F13}
contained the GUID of nlhtml.dll, not HTMLProp.dll.
The GUID {25336920-03F9-11CF-8FD0-00AA00686F13} is used to identify
file and MIME types for HTML, GIF, JPEG, plain text and similar files.
It's sort of like an extension. I had placed the GUID of htmlprop.dll
in all the right places but this one, as it wasn't specified.
I also found the old nlhtml.dll GUID under the following keys, neither
of which seem to be registered to any file types:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\SPSSearch\ContentIndexCommon\Filters\CLSID\{7F73B8F6-C19C-11D0-AA66-00C04FC2EDDC}
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\SPSSearch\ContentIndexCommon\Filters\CLSID\{BD70C020-2D24-11D0-9110-00004C752752}
PSS recommends replacing all occurances of the nlhtml.dll GUID with
HTML prop's GUID, since htmlprop.dll will call nlhtml.dll if it finds a
custom metaproperty it can't handle. However, since these don't map to
anything, I just replaced the necessary one for now.
In any event, once I deleted the custom properties from my SPS Manage
Custom Properties for Search, reset *all* the content indexes,
restarted the Microsoft SharePointPS Search service and recrawled the
content, I now find my Date meta tag is coming back as a DateTime type!
Woo Hoo!
Here's my htmlprop.ini:
[Names]
#
# These lines define datatypes for HTML meta properties.
# The HTML property filter will convert properties from strings to
# these types.
#
YEAR (DBTYPE_I8) = d1b5d3f0-c0b3-11cf-9a92-00a0c908dbf1 YEAR
MONTH (DBTYPE_I8) = d1b5d3f0-c0b3-11cf-9a92-00a0c908dbf1 MONTH
DAY (DBTYPE_I8) = d1b5d3f0-c0b3-11cf-9a92-00a0c908dbf1 DAY
Date (VT_FILETIME) = d1b5d3f0-c0b3-11cf-9a92-00a0c908dbf1 Date
# htmlprop.dll clsid is {f4309e80-a1db-11d1-a8fb-00e098006ed3}
#
# Default HTML IFilter clsid is {E0CA5340-4534-11CF-B952-00AA0051FE20}
I put together a document, modified from one I received from Microsoft
(among other things, I added the missing step!) that details the steps.
Here it is:
How to Register HTMLProp.dll
1. Copy HTMLProp.dll to C:\Windows\System32
2. Copy HTMLProp.ini to C:\Windows\System32
3. Modify HTMLProp.ini to include the value-type properties you're
interested in. For example, add following lines at the bottom of the
file:
YEAR (DBTYPE_I8) = d1b5d3f0-c0b3-11cf-9a92-00a0c908dbf1 YEAR
MONTH (DBTYPE_I8) = d1b5d3f0-c0b3-11cf-9a92-00a0c908dbf1 MONTH
DAY (DBTYPE_I8) = d1b5d3f0-c0b3-11cf-9a92-00a0c908dbf1 DAY
Date (VT_FILETIME) = d1b5d3f0-c0b3-11cf-9a92-00a0c908dbf1 Date
4. Important Note: Stop the Microsoft SharePointPS Search service
before proceeding. Do not attempt the steps below if scans are in
progress. Wait for scans to complete, or halt all scans.
5. Enable automatic registration by adding the following line:
C:\WINDOWS\system32\htmlprop.dll
To the registry key:
HKLM\SYSTEM\CurrentControlSet\Control\ContentIndex\DLLsToRegister
6. At the command-line prompt, self register the filter by typing:
"regsvr32.exe %windir%\System32\HtmlProp.dll".
7. Search in the registry under HKEY_CLASSES_ROOT for HTMLProp.dll.
You are looking for the CLSID of this file, which should be returned
something like this:
HKEY_CLASSES_ROOT\CLSID\{f4309e80-a1db-11d1-a8fb-00e098006ed3}
If you built the source code from the platform SDK, the GUID will be as
listed above.
8. The CLSID of the htmlprop.dll needs to be registered in the
SharePoint Portal Server 2003 IFilter registration to override the
CLSID of the default html filter. The SharePoint Portal Server 2003
filters are registered in the Windows Registry under
HKLM\Software\Microsoft\SPSSearch\ContentIndexCommon\Filters\CLSID\
and
HKLM\Software\Microsoft\SPSSearch\ContentIndexCommon\Filters\Extension\
There are several places where this is done. Some are under the
Extension subkey, and there are others under the CLSID subkey. Steps 8a
and 8b detail these changes.
8a. The CLSID of the default html filter is
{E0CA5340-4534-11CF-B952-00AA0051FE20}. Replace this value with the
new value {f4309e80-a1db-11d1-a8fb-00e098006ed3} in:
HKLM\SOFTWARE\Microsoft\SPSSearch\ContentIndexCommon\Filters\Extension\.htm
HKLM\SOFTWARE\Microsoft\SPSSearch\ContentIndexCommon\Filters\Extension\.html
As well as any other file extensions you want handled by htmlprop.dll.
8b. There are several CLSID GUIDs that are mapped to the default html
filter nlhtml.dll. The GUID {25336920-03F9-11CF-8FD0-00AA00686F13} is
used to identify file and MIME types for HTML, GIF, JPEG, plain text
and similar files. It can be thought of as something similar to a file
extension. The GUIDs {7F73B8F6-C19C-11D0-AA66-00C04FC2EDDC} and
{BD70C020-2D24-11D0-9110-00004C752752} might also be found, but these
may not be mapped to any file types (search the registry for
occurrences of these GUIDs to be sure). Search for occurrences of the
old GUID for nlhtml.dll {E0CA5340-4534-11CF-B952-00AA0051FE20} and
replace with {f4309e80-a1db-11d1-a8fb-00e098006ed3} in:
HKLM\SOFTWARE\Microsoft\SPSSearch\ContentIndexCommon\Filters\CLSID\
Please note this step is not documented elsewhere, but it has been
found to be necessary.
9. Go to "Manage Properties of Crawled Content", and delete all the
value-type properties in the
urn:schemas.microsoft.com:htmlinfo:metainfo namespace that you are
trying to process with htmlprop.dll. This is important because if any
content index has crawled content containing the value-type properties
you're trying to process, you will not be able to coerce the type.
10. Reset all content indexes. This is important because if any
content index contains the value-type properties you're trying to
process, you will not be able to coerce the type.
11. Restart the Microsoft SharePointPS Search service
12. Initiate a Full Update of all content indexes that contain the
value-type properties. If this crawl would take a long time, you can
create a temporary content index with one document to crawl that
contains the value-type Meta tags you want to process with
htmlprop.dll, and crawl only that one content index.
13. Go to "Manage Properties of Crawled Content", locate the
value-type properties in the
urn:schemas.microsoft.com:htmlinfo:metainfo namespace, and check the
option, "Included in the Advanced Search options", which exposes
the meta property to the Sharepoint Search Service
14. Initiate another Full Update of all content indexes.
15. After crawl and propagation has completed, you will be able to
select the value-type properties on the Advanced Search page and
specific value-type criteria. For instance, for an integer property
you can specify >, >=, =, <, or <= criteria. You will also be able to
run programmatically run queries with a WHERE clause that contains
these criteria, and you will be able to use date functions like
DATEADD() in a WHERE clause.
Thanks to Anant Dimri with Microsoft PSS, for catching that
undocumented registry key!
Regards,
Mike Sharp
Hollis D. Paul
wrote:
> I finally got resolution on the custom metadata and HTMLProp.dll issue.
>
blog, if you don't mind.
Hollis D. Paul [MVP - Outlook]
Hol...@outhousebythesound.com
http://msmvps.com/obts/archive/2005/01.aspx
Mukilteo, WA USA
rdcpro
explanation of what's going on. I see I'm not too far south of you. I'm
working in the Eastlake area of Seattle.
Regards,
Mike Sharp
rdc...@hotmail.com
http://rdcpro.com
Hollis D. Paul
> Be my guest! I've also submitted it to MSD2D, along with a somewhat lengthy
> explanation of what's going on. I see I'm not too far south of you. I'm
> working in the Eastlake area of Seattle.
>
that where the condo's by the I5 tilted, and were left hanging for years while
the city and insurance companies squabbled over who should pay? Or are we
talking the Wooden Boat museum at the Mercer street exit jog to main road to
the World's Fair site?
Hollis D. Paul [MVP - Outlook]
Hol...@outhousebythesound.com
Mukilteo, WA USA
rdcpro
Closer to the leaning tower of Condos...
Regards,
Mike Sharp
Hollis D. Paul
> explanation of what's going on. I see I'm not too far south of you. I'm
> working in the Eastlake area of Seattle.
>
http://msmvps.com/obts/archive/2005/01/13/32086.aspx
Hollis D. Paul [MVP - Outlook]
Hol...@outhousebythesound.com
Mukilteo, WA USA