In my database I created a SQL Server user - malphaTest
In my WebConfig.xml
<add key="SQLConn"
value="Data Source=HIDSCFILE002;Initial Catalog=Membership;User
ID=malphaTest;Password=12345;Trusted_Con
nection=False"/>
</appSettings>
The error returned when press the search button to query the database is:
EXECUTE permission denied on object 'qMemberSelect', database 'Membership',
owner 'dbo'
Shouldn't it refer to malphaTest? not dbo?No, the owner of the object that you aquired is dbo. So you have to
grant the user malphaTest EXECUTE rights on the procedure
/dbo.'qMemberSelect') to get around this error.
HTH, Jens Suessmeyer.
Showing posts with label key. Show all posts
Showing posts with label key. Show all posts
Thursday, March 29, 2012
Getting a permission denied - but not using that user?
In my database I created a SQL Server user - malphaTest
In my WebConfig.xml
<add key="SQLConn"
value="Data Source=HIDSCFILE002;Initial Catalog=Membership;User
ID=malphaTest;Password=12345;Trusted_Connection=Fa lse"/>
</appSettings>
The error returned when press the search button to query the database is:
EXECUTE permission denied on object 'qMemberSelect', database 'Membership',
owner 'dbo'
Shouldn't it refer to malphaTest? not dbo?
No, the owner of the object that you aquired is dbo. So you have to
grant the user malphaTest EXECUTE rights on the procedure
/dbo.'qMemberSelect') to get around this error.
HTH, Jens Suessmeyer.
sql
In my WebConfig.xml
<add key="SQLConn"
value="Data Source=HIDSCFILE002;Initial Catalog=Membership;User
ID=malphaTest;Password=12345;Trusted_Connection=Fa lse"/>
</appSettings>
The error returned when press the search button to query the database is:
EXECUTE permission denied on object 'qMemberSelect', database 'Membership',
owner 'dbo'
Shouldn't it refer to malphaTest? not dbo?
No, the owner of the object that you aquired is dbo. So you have to
grant the user malphaTest EXECUTE rights on the procedure
/dbo.'qMemberSelect') to get around this error.
HTH, Jens Suessmeyer.
sql
Monday, March 12, 2012
Get the SQL Server Unique ID of the Server
Hi All,
Is there any ID / Key which identify the server of the SQL Server
installation uniquely? I need this ID to make sure the database installed in
a SQL server can't be transferred to other SQL Server, for licencing issues.
Or, is there other ways to achieve this?
Thanks & Regards,
EdwardIf you mean you want to find the name of the Windows machine that the
SQL instance is running on then you could use "SELECT
SERVERPROPERTY('MachineName')" or if you're after the name of the SQL
instance itself then you could use "SELECT
SERVERPROPERTY('ServerName')", which will return the Windows machine
name and the SQL instance name.
*mike hodgson*
http://sqlnerd.blogspot.com
Edward Low wrote:
>Hi All,
>Is there any ID / Key which identify the server of the SQL Server
>installation uniquely? I need this ID to make sure the database installed i
n
>a SQL server can't be transferred to other SQL Server, for licencing issues
.
>Or, is there other ways to achieve this?
>
>Thanks & Regards,
>Edward
>
>|||Hi mike,
thanks for your reply. But, "SERVERPROPERTY('ServerName')" still not a uniqu
e, mean someone can have the same servername on other machine. I need someth
ing unique like the hard drive serial no which is unique in each machine. ca
n we get this information from sql server?
Best regards,
Edward
"Mike Hodgson" <e1minst3r@.gmail.com> wrote in message news:e7vcOyvfGHA.4304@.
TK2MSFTNGP05.phx.gbl...
If you mean you want to find the name of the Windows machine that the SQL in
stance is running on then you could use "SELECT SERVERPROPERTY('MachineName'
)" or if you're after the name of the SQL instance itself then you could use
"SELECT SERVERPROPERTY('ServerName')", which will return the Windows machin
e name and the SQL instance name.
mike hodgson
http://sqlnerd.blogspot.com
Edward Low wrote:
Hi All,
Is there any ID / Key which identify the server of the SQL Server
installation uniquely? I need this ID to make sure the database installed in
a SQL server can't be transferred to other SQL Server, for licencing issues.
Or, is there other ways to achieve this?
Thanks & Regards,
Edward|||For SQL Server 2005 you could write an Unsafe CLR procedure which can get th
e Hard drive serial number or any other metric tha you want.
However!? What happens if said hard drive breaks and the server has to be
re-built with a different hard drive and a database backup restored?
Regards
Colin Dawson
www.cjdawson.com
"Edward Low" <wc_low@.hotmail.com> wrote in message news:ORW6yDwfGHA.5088@.TK2
MSFTNGP02.phx.gbl...
Hi mike,
thanks for your reply. But, "SERVERPROPERTY('ServerName')" still not a uniqu
e, mean someone can have the same servername on other machine. I need someth
ing unique like the hard drive serial no which is unique in each machine. ca
n we get this information from sql server?
Best regards,
Edward
"Mike Hodgson" <e1minst3r@.gmail.com> wrote in message news:e7vcOyvfGHA.4304@.
TK2MSFTNGP05.phx.gbl...
If you mean you want to find the name of the Windows machine that the SQL in
stance is running on then you could use "SELECT SERVERPROPERTY('MachineName'
)" or if you're after the name of the SQL instance itself then you could use
"SELECT SERVERPROPERTY('ServerName')", which will return the Windows machin
e name and the SQL instance name.
mike hodgson
http://sqlnerd.blogspot.com
Edward Low wrote:
Hi All,
Is there any ID / Key which identify the server of the SQL Server
installation uniquely? I need this ID to make sure the database installed in
a SQL server can't be transferred to other SQL Server, for licencing issues.
Or, is there other ways to achieve this?
Thanks & Regards,
Edward|||If you could get the Windows domain name (there's no real kosher way
that I know of to do this purely with SQL (you'd have to shell out to
DOS with xp_cmdshell (yuk), like
exec xp_cmdshell 'echo %USERDOMAIN%'
)) then the domain/machine/instance has to be unique (although you could
argue that different unrelated companies, or individuals, could create
domains with the same name).
Alternately I think the ProductID (stored in the registry under
HKLM\SOFTWARE\Microsoft\Windows\CurrentV
ersion\ProductId) is supposed to
be unique for a Windows installation, so that, combined with the
instance name, ought to be a unique SQL instance. However, once again,
there's no real kosher way to read registry keys. In SQL 2000 you can
use the proc xp_regread, like this:
declare @.ProductId varchar(100)
exec xp_regread
'HKEY_LOCAL_MACHINE',
'SOFTWARE\Microsoft\Windows\CurrentVersi
on',
'ProductId',
@.ProductId OUTPUT
select @.ProductId
but this is an undocumented proc and in SQL 2005 Microsoft have changed
its behaviour (the permissions around what keys it can and cannot read)
and so it probably won't work in SQL 2005 (although I just tried it on
both a Dev Edition & Ent Edition of SQL 2005 SP1 (x64) and it worked on
both but the trusted login I used was a local admin so that might have
skewed the result a little). But, in any case, it's an undocumented
proc so you probably shouldn't use it (if you want it to continue
working in future versions of SQL Server) and also, if the SQL instance
is installed on a cluster you'd get a different ProductId value
depending on which node the SQL instance happened to be running on at
the time. There's a couple other reg keys you might be able to read to
get a unique ID (like the 180 byte binary SQL setup checksum) but they
all still have the "read from the registry" problem (and on a cluster
reading from the SQL branches of the registry is very messy because
you've got to look up the installed instances, get the one you want,
look up which reg key that instance has its values stored in, go to that
reg key and get the value you're after).
There's no real nice way I can think of to tie a DB to a specific SQL
instance, not with T-SQL code from within a SQL Server session anyway
(you could store a hash, generated outside of a SQL session like in VB
or C# code for example, of some unique attributes of the SQL
installation, like domainname/servername/instancename or
IPaddress/TCPportnumber but those attributes may, validly, change over
time...).
*mike hodgson*
http://sqlnerd.blogspot.com
Edward Low wrote:
> Hi mike,
> thanks for your reply. But, "SERVERPROPERTY('ServerName')" still not a
> unique, mean someone can have the same servername on other machine. I
> need something unique like the hard drive serial no which is unique in
> each machine. can we get this information from sql server?
> Best regards,
> Edward
>
> "Mike Hodgson" <e1minst3r@.gmail.com <mailto:e1minst3r@.gmail.com>>
> wrote in message news:e7vcOyvfGHA.4304@.TK2MSFTNGP05.phx.gbl...
> If you mean you want to find the name of the Windows machine that
> the SQL instance is running on then you could use "SELECT
> SERVERPROPERTY('MachineName')" or if you're after the name of the
> SQL instance itself then you could use "SELECT
> SERVERPROPERTY('ServerName')", which will return the Windows
> machine name and the SQL instance name.
> --
> *mike hodgson*
> http://sqlnerd.blogspot.com
>
> Edward Low wrote:
>|||Hi Mike,
Thanks for your explanation.
Edward
"Mike Hodgson" <e1minst3r@.gmail.com> wrote in message news:uOGY$d6fGHA.5092@.
TK2MSFTNGP04.phx.gbl...
If you could get the Windows domain name (there's no real kosher way that I
know of to do this purely with SQL (you'd have to shell out to DOS with xp_c
mdshell (yuk), like
exec xp_cmdshell 'echo %USERDOMAIN%'
)) then the domain/machine/instance has to be unique (although you could arg
ue that different unrelated companies, or individuals, could create domains
with the same name).
Alternately I think the ProductID (stored in the registry under HKLM\SOFTWAR
E\Microsoft\Windows\CurrentVersion\Produ
ctId) is supposed to be unique for a
Windows installation, so that, combined with the instance name, ought to be
a unique SQL instance. However, once again, there's no real kosher way to
read registry keys. In SQL 2000 you can use the proc xp_regread, like this:
declare @.ProductId varchar(100)
exec xp_regread
'HKEY_LOCAL_MACHINE',
'SOFTWARE\Microsoft\Windows\CurrentVersi
on',
'ProductId',
@.ProductId OUTPUT
select @.ProductId
but this is an undocumented proc and in SQL 2005 Microsoft have changed its
behaviour (the permissions around what keys it can and cannot read) and so i
t probably won't work in SQL 2005 (although I just tried it on both a Dev Ed
ition & Ent Edition of SQL 2005 SP1 (x64) and it worked on both but the trus
ted login I used was a local admin so that might have skewed the result a li
ttle). But, in any case, it's an undocumented proc so you probably shouldn'
t use it (if you want it to continue working in future versions of SQL Serve
r) and also, if the SQL instance is installed on a cluster you'd get a diffe
rent ProductId value depending on which node the SQL instance happened to be
running on at the time. There's a couple other reg keys you might be able
to read to get a unique ID (like the 180 byte binary SQL setup checksum) but
they all still have the "read from the registry" problem (and on a cluster
reading from the SQL branches of the registry is very messy because you've g
ot to look up the installed instances, get the one you want, look up which r
eg key that instance has its values stored in, go to that reg key and get th
e value you're after).
There's no real nice way I can think of to tie a DB to a specific SQL instan
ce, not with T-SQL code from within a SQL Server session anyway (you could s
tore a hash, generated outside of a SQL session like in VB or C# code for ex
ample, of some unique attributes of the SQL installation, like domainname/se
rvername/instancename or IPaddress/TCPportnumber but those attributes may, v
alidly, change over time...).
mike hodgson
http://sqlnerd.blogspot.com
Edward Low wrote:
Hi mike,
thanks for your reply. But, "SERVERPROPERTY('ServerName')" still not a uniqu
e, mean someone can have the same servername on other machine. I need someth
ing unique like the hard drive serial no which is unique in each machine. ca
n we get this information from sql server?
Best regards,
Edward
"Mike Hodgson" <e1minst3r@.gmail.com> wrote in message news:e7vcOyvfGHA.4304@.
TK2MSFTNGP05.phx.gbl...
If you mean you want to find the name of the Windows machine that the SQL in
stance is running on then you could use "SELECT SERVERPROPERTY('MachineName'
)" or if you're after the name of the SQL instance itself then you could use
"SELECT SERVERPROPERTY('ServerName')", which will return the Windows machin
e name and the SQL instance name.
mike hodgson
http://sqlnerd.blogspot.com
Edward Low wrote:
Hi All,
Is there any ID / Key which identify the server of the SQL Server
installation uniquely? I need this ID to make sure the database installed in
a SQL server can't be transferred to other SQL Server, for licencing issues.
Or, is there other ways to achieve this?
Thanks & Regards,
Edward
Is there any ID / Key which identify the server of the SQL Server
installation uniquely? I need this ID to make sure the database installed in
a SQL server can't be transferred to other SQL Server, for licencing issues.
Or, is there other ways to achieve this?
Thanks & Regards,
EdwardIf you mean you want to find the name of the Windows machine that the
SQL instance is running on then you could use "SELECT
SERVERPROPERTY('MachineName')" or if you're after the name of the SQL
instance itself then you could use "SELECT
SERVERPROPERTY('ServerName')", which will return the Windows machine
name and the SQL instance name.
*mike hodgson*
http://sqlnerd.blogspot.com
Edward Low wrote:
>Hi All,
>Is there any ID / Key which identify the server of the SQL Server
>installation uniquely? I need this ID to make sure the database installed i
n
>a SQL server can't be transferred to other SQL Server, for licencing issues
.
>Or, is there other ways to achieve this?
>
>Thanks & Regards,
>Edward
>
>|||Hi mike,
thanks for your reply. But, "SERVERPROPERTY('ServerName')" still not a uniqu
e, mean someone can have the same servername on other machine. I need someth
ing unique like the hard drive serial no which is unique in each machine. ca
n we get this information from sql server?
Best regards,
Edward
"Mike Hodgson" <e1minst3r@.gmail.com> wrote in message news:e7vcOyvfGHA.4304@.
TK2MSFTNGP05.phx.gbl...
If you mean you want to find the name of the Windows machine that the SQL in
stance is running on then you could use "SELECT SERVERPROPERTY('MachineName'
)" or if you're after the name of the SQL instance itself then you could use
"SELECT SERVERPROPERTY('ServerName')", which will return the Windows machin
e name and the SQL instance name.
mike hodgson
http://sqlnerd.blogspot.com
Edward Low wrote:
Hi All,
Is there any ID / Key which identify the server of the SQL Server
installation uniquely? I need this ID to make sure the database installed in
a SQL server can't be transferred to other SQL Server, for licencing issues.
Or, is there other ways to achieve this?
Thanks & Regards,
Edward|||For SQL Server 2005 you could write an Unsafe CLR procedure which can get th
e Hard drive serial number or any other metric tha you want.
However!? What happens if said hard drive breaks and the server has to be
re-built with a different hard drive and a database backup restored?
Regards
Colin Dawson
www.cjdawson.com
"Edward Low" <wc_low@.hotmail.com> wrote in message news:ORW6yDwfGHA.5088@.TK2
MSFTNGP02.phx.gbl...
Hi mike,
thanks for your reply. But, "SERVERPROPERTY('ServerName')" still not a uniqu
e, mean someone can have the same servername on other machine. I need someth
ing unique like the hard drive serial no which is unique in each machine. ca
n we get this information from sql server?
Best regards,
Edward
"Mike Hodgson" <e1minst3r@.gmail.com> wrote in message news:e7vcOyvfGHA.4304@.
TK2MSFTNGP05.phx.gbl...
If you mean you want to find the name of the Windows machine that the SQL in
stance is running on then you could use "SELECT SERVERPROPERTY('MachineName'
)" or if you're after the name of the SQL instance itself then you could use
"SELECT SERVERPROPERTY('ServerName')", which will return the Windows machin
e name and the SQL instance name.
mike hodgson
http://sqlnerd.blogspot.com
Edward Low wrote:
Hi All,
Is there any ID / Key which identify the server of the SQL Server
installation uniquely? I need this ID to make sure the database installed in
a SQL server can't be transferred to other SQL Server, for licencing issues.
Or, is there other ways to achieve this?
Thanks & Regards,
Edward|||If you could get the Windows domain name (there's no real kosher way
that I know of to do this purely with SQL (you'd have to shell out to
DOS with xp_cmdshell (yuk), like
exec xp_cmdshell 'echo %USERDOMAIN%'
)) then the domain/machine/instance has to be unique (although you could
argue that different unrelated companies, or individuals, could create
domains with the same name).
Alternately I think the ProductID (stored in the registry under
HKLM\SOFTWARE\Microsoft\Windows\CurrentV
ersion\ProductId) is supposed to
be unique for a Windows installation, so that, combined with the
instance name, ought to be a unique SQL instance. However, once again,
there's no real kosher way to read registry keys. In SQL 2000 you can
use the proc xp_regread, like this:
declare @.ProductId varchar(100)
exec xp_regread
'HKEY_LOCAL_MACHINE',
'SOFTWARE\Microsoft\Windows\CurrentVersi
on',
'ProductId',
@.ProductId OUTPUT
select @.ProductId
but this is an undocumented proc and in SQL 2005 Microsoft have changed
its behaviour (the permissions around what keys it can and cannot read)
and so it probably won't work in SQL 2005 (although I just tried it on
both a Dev Edition & Ent Edition of SQL 2005 SP1 (x64) and it worked on
both but the trusted login I used was a local admin so that might have
skewed the result a little). But, in any case, it's an undocumented
proc so you probably shouldn't use it (if you want it to continue
working in future versions of SQL Server) and also, if the SQL instance
is installed on a cluster you'd get a different ProductId value
depending on which node the SQL instance happened to be running on at
the time. There's a couple other reg keys you might be able to read to
get a unique ID (like the 180 byte binary SQL setup checksum) but they
all still have the "read from the registry" problem (and on a cluster
reading from the SQL branches of the registry is very messy because
you've got to look up the installed instances, get the one you want,
look up which reg key that instance has its values stored in, go to that
reg key and get the value you're after).
There's no real nice way I can think of to tie a DB to a specific SQL
instance, not with T-SQL code from within a SQL Server session anyway
(you could store a hash, generated outside of a SQL session like in VB
or C# code for example, of some unique attributes of the SQL
installation, like domainname/servername/instancename or
IPaddress/TCPportnumber but those attributes may, validly, change over
time...).
*mike hodgson*
http://sqlnerd.blogspot.com
Edward Low wrote:
> Hi mike,
> thanks for your reply. But, "SERVERPROPERTY('ServerName')" still not a
> unique, mean someone can have the same servername on other machine. I
> need something unique like the hard drive serial no which is unique in
> each machine. can we get this information from sql server?
> Best regards,
> Edward
>
> "Mike Hodgson" <e1minst3r@.gmail.com <mailto:e1minst3r@.gmail.com>>
> wrote in message news:e7vcOyvfGHA.4304@.TK2MSFTNGP05.phx.gbl...
> If you mean you want to find the name of the Windows machine that
> the SQL instance is running on then you could use "SELECT
> SERVERPROPERTY('MachineName')" or if you're after the name of the
> SQL instance itself then you could use "SELECT
> SERVERPROPERTY('ServerName')", which will return the Windows
> machine name and the SQL instance name.
> --
> *mike hodgson*
> http://sqlnerd.blogspot.com
>
> Edward Low wrote:
>|||Hi Mike,
Thanks for your explanation.
Edward
"Mike Hodgson" <e1minst3r@.gmail.com> wrote in message news:uOGY$d6fGHA.5092@.
TK2MSFTNGP04.phx.gbl...
If you could get the Windows domain name (there's no real kosher way that I
know of to do this purely with SQL (you'd have to shell out to DOS with xp_c
mdshell (yuk), like
exec xp_cmdshell 'echo %USERDOMAIN%'
)) then the domain/machine/instance has to be unique (although you could arg
ue that different unrelated companies, or individuals, could create domains
with the same name).
Alternately I think the ProductID (stored in the registry under HKLM\SOFTWAR
E\Microsoft\Windows\CurrentVersion\Produ
ctId) is supposed to be unique for a
Windows installation, so that, combined with the instance name, ought to be
a unique SQL instance. However, once again, there's no real kosher way to
read registry keys. In SQL 2000 you can use the proc xp_regread, like this:
declare @.ProductId varchar(100)
exec xp_regread
'HKEY_LOCAL_MACHINE',
'SOFTWARE\Microsoft\Windows\CurrentVersi
on',
'ProductId',
@.ProductId OUTPUT
select @.ProductId
but this is an undocumented proc and in SQL 2005 Microsoft have changed its
behaviour (the permissions around what keys it can and cannot read) and so i
t probably won't work in SQL 2005 (although I just tried it on both a Dev Ed
ition & Ent Edition of SQL 2005 SP1 (x64) and it worked on both but the trus
ted login I used was a local admin so that might have skewed the result a li
ttle). But, in any case, it's an undocumented proc so you probably shouldn'
t use it (if you want it to continue working in future versions of SQL Serve
r) and also, if the SQL instance is installed on a cluster you'd get a diffe
rent ProductId value depending on which node the SQL instance happened to be
running on at the time. There's a couple other reg keys you might be able
to read to get a unique ID (like the 180 byte binary SQL setup checksum) but
they all still have the "read from the registry" problem (and on a cluster
reading from the SQL branches of the registry is very messy because you've g
ot to look up the installed instances, get the one you want, look up which r
eg key that instance has its values stored in, go to that reg key and get th
e value you're after).
There's no real nice way I can think of to tie a DB to a specific SQL instan
ce, not with T-SQL code from within a SQL Server session anyway (you could s
tore a hash, generated outside of a SQL session like in VB or C# code for ex
ample, of some unique attributes of the SQL installation, like domainname/se
rvername/instancename or IPaddress/TCPportnumber but those attributes may, v
alidly, change over time...).
mike hodgson
http://sqlnerd.blogspot.com
Edward Low wrote:
Hi mike,
thanks for your reply. But, "SERVERPROPERTY('ServerName')" still not a uniqu
e, mean someone can have the same servername on other machine. I need someth
ing unique like the hard drive serial no which is unique in each machine. ca
n we get this information from sql server?
Best regards,
Edward
"Mike Hodgson" <e1minst3r@.gmail.com> wrote in message news:e7vcOyvfGHA.4304@.
TK2MSFTNGP05.phx.gbl...
If you mean you want to find the name of the Windows machine that the SQL in
stance is running on then you could use "SELECT SERVERPROPERTY('MachineName'
)" or if you're after the name of the SQL instance itself then you could use
"SELECT SERVERPROPERTY('ServerName')", which will return the Windows machin
e name and the SQL instance name.
mike hodgson
http://sqlnerd.blogspot.com
Edward Low wrote:
Hi All,
Is there any ID / Key which identify the server of the SQL Server
installation uniquely? I need this ID to make sure the database installed in
a SQL server can't be transferred to other SQL Server, for licencing issues.
Or, is there other ways to achieve this?
Thanks & Regards,
Edward
Wednesday, March 7, 2012
get text near key words
When i query key words , i can get the docname,doctype
etc.
Can i get some texts near the searching words ? like the
result fo google !
Does the MSSearch.Query offer one interface ?
Does the IFilters(according the doc type) offer one
interface to get some text from the doc?
thanks
Rainbow,
Are these questions related to "Indexing Service" or SQL Server's Full-text
Search (FTS) components? If the latter, then have you checked out the NEAR
parameter within CONTAINS or CONTAINSTABLE, for example:
E. Use CONTAINS with <proximity_term>
This example returns all product names that have the word "Boysenberry" near
the word "spread".
USE Northwind
GO
SELECT ProductName FROM Products WHERE CONTAINS(ProductName, 'spread NEAR
Boysenberry')orSELECT FT_TBL.Description, FT_TBL.CategoryName,
KEY_TBL.RANKFROM Categories AS FT_TBL INNER JOIN
CONTAINSTABLE (Categories, Description,
'("sweet and savory" NEAR sauces) OR
("sweet and savory" NEAR candies)' , 10 ) AS KEY_TBL
ON FT_TBL.CategoryID = KEY_TBL.[KEY]
Both examples are from SQL Server 2000 BOL (books online).
If your questions are related to the Indexing Service, could you be more
specific about what you mean by one interface?
Thanks,
John
"rainbow" <anonymous@.discussions.microsoft.com> wrote in message
news:1a9601c4bc9d$09f53a10$a501280a@.phx.gbl...
> When i query key words , i can get the docname,doctype
> etc.
> Can i get some texts near the searching words ? like the
> result fo google !
> Does the MSSearch.Query offer one interface ?
> Does the IFilters(according the doc type) offer one
> interface to get some text from the doc?
> thanks
>
|||Thanks , John , you misunderstand me .
What i want is to get the pure-text about 20 bytes near th
the searching words , it looks like :
When i search 'full-text' in one doc: "sql server full-
text searching reference, ..." , i also want to get the
text 'full-text searching reference,...' and so on.
like google , When you search something , you can get some
hints texts containing the searching words !
So , maybe MSSearch will return the offset of the doc ,
then reading 20 bytes beside the offset , using IFilter to
translate the bytes into text!
Just one idea , and i don't know how to implement !
thanks ~
>--Original Message--
>Rainbow,
>Are these questions related to "Indexing Service" or SQL
Server's Full-text
>Search (FTS) components? If the latter, then have you
checked out the NEAR
>parameter within CONTAINS or CONTAINSTABLE, for example:
>E. Use CONTAINS with <proximity_term>
>This example returns all product names that have the
word "Boysenberry" near
>the word "spread".
>USE Northwind
>GO
>SELECT ProductName FROM Products WHERE CONTAINS
(ProductName, 'spread NEAR
>Boysenberry')orSELECT FT_TBL.Description,
FT_TBL.CategoryName,
>KEY_TBL.RANKFROM Categories AS FT_TBL INNER JOIN
> CONTAINSTABLE (Categories, Description,
> '("sweet and savory" NEAR sauces) OR
> ("sweet and savory" NEAR candies)' , 10 ) AS
KEY_TBL
> ON FT_TBL.CategoryID = KEY_TBL.[KEY]
>Both examples are from SQL Server 2000 BOL (books online).
>If your questions are related to the Indexing Service,
could you be more
>specific about what you mean by one interface?
>Thanks,
>John
>
|||You're welcome, Rainbow,
That is why I asked the questions, to get more information and a better
clarification on what you are really after!
While not exactly what Google provides (or even the Indexing Services'
abstract/characterization), the following is as close as you can get using
T-SQL and SQL Server 2000 Full-text Search (FTS):
-- The following SQL FTS query on the pubs table pub_info will return rows
that match the FTS search word (books) and return test that is near the
search word from 20 characters before the search keyword (books) for a total
length of 100 characters.
use pubs
SELECT pub_id, SubString(pr_info,PatIndex ('%books%',pr_info)-20,100)
FROM pub_info
WHERE Contains(pr_info, 'books')
You can vary the patindex parameters (20 & 100 in the above query) to expand
or contract the amount of text that will be displayed. However, the MSSearch
does not provide any direct method of returning an offset of the word in the
row/document, nor do the various IFilters provide an means of translating
the bytes into text. In order to get this level of granularity, you would
have to write your own IFilter for types of document you would want store
and search in order to get the information you are looking for and this is a
non-trivial effort...
This is not an un-common request, given the impact that Google has had in
Full Text Search over the last couple of years, but alas this type of
feature is not available in SQL Server 2000...
Let me know if this comes closer to what you're interested in doing!
Thanks,
John
"rainbow" <anonymous@.discussions.microsoft.com> wrote in message
news:1aba01c4bcb0$2412bbd0$a501280a@.phx.gbl...
> Thanks , John , you misunderstand me .
> What i want is to get the pure-text about 20 bytes near th
> the searching words , it looks like :
> When i search 'full-text' in one doc: "sql server full-
> text searching reference, ..." , i also want to get the
> text 'full-text searching reference,...' and so on.
> like google , When you search something , you can get some
> hints texts containing the searching words !
> So , maybe MSSearch will return the offset of the doc ,
> then reading 20 bytes beside the offset , using IFilter to
> translate the bytes into text!
> Just one idea , and i don't know how to implement !
> thanks ~
>
> Server's Full-text
> checked out the NEAR
> word "Boysenberry" near
> (ProductName, 'spread NEAR
> FT_TBL.CategoryName,
> KEY_TBL
> could you be more
>
|||Your question pertains to Site Server which is no longer supported by
Microsoft. There is still a site server newsgroup
microsoft.public.siteserver.search
To answer your question I suggest you follow this link
http://www.indexserverfaq.com/sqlhithighlighting.htm
Here is my schema
CREATE TABLE [dbo].[Parent] (
[pk] [int] IDENTITY (1, 1) NOT NULL ,
[Title] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Author] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[vpath] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[characterization] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[Size] [int] NULL ,
[CreateDate] [datetime] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[TextTable] (
[pk] [int] IDENTITY (1, 1) NOT NULL ,
[textcol] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
Here is my query
CREATE PROC search @.search nvarchar(20), @.@.strSearch nvarchar(500)
AS
SET @.@.strSearch='SELECT Title, author, vpath, characterization, size,
createDate from parent as P,_ CONTAINSTABLE(TextTable, TextCol,
'+char(39)+char(34)
SELECT @.@.strSearch=@.@.strSearch + @.search +char(34)+char(39)
SELECT @.@.strSearch=@.@.strSearch+ ', 100) AS FTTABLE WHERE P.pk=
FTTABLE.[key]'
SELECT @.@.strSearch= @.@.strSearch + ' AND size > 10000 ORDER BY FTTABLE.rank
desc,[KEY] '
exec (@.@.strSearch)
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"rainbow" <anonymous@.discussions.microsoft.com> wrote in message
news:1a9601c4bc9d$09f53a10$a501280a@.phx.gbl...
> When i query key words , i can get the docname,doctype
> etc.
> Can i get some texts near the searching words ? like the
> result fo google !
> Does the MSSearch.Query offer one interface ?
> Does the IFilters(according the doc type) offer one
> interface to get some text from the doc?
> thanks
>
begin 666 citrixNew.asp
M/"4-"F]P=&EO;B!E>'!L:6-I= T*0V]N<W0@.8615<V5#;&EE;G0@./2 S#0I#
M;VYS="!A9%5S95-E<G9E<B ](#(-"D-O;G-T(&%D3W!E;D9O<G=A<F1/;FQY
M(#T@., T*0V]N<W0@.861/<&5N4W1A=&EC(#T@.,PT*0V]N<W0@.861,;V-K4F5A
M9$]N;'D@./2 Q#0I#;VYS="!A9$-M9%-T;W)E9%!R;V,@./2 T#0I#;VYS="!A
M9%!A<F%M26YP=70@./2 Q#0I#;VYS="!A9%!A<F%M4F5T=7)N5F%L=64@./2 F
M2# P,#0-"D-O;G-T(&%D5F%R0VAA<B ](#(P, T*0V]N<W0@.861);G1E9V5R
M(#T@.,PT*0V]N<W0@.:5)O=W-097)086=E(#T@.,3 -"D-O;G-T('-H;W=";&%N
M:R ]("(F;F)S<#LB#0I#;VYS="!S:&]W3G5L;" ](").54Q,(@.T*#0I$:6T@.
M:5-T87)T+"!2971U<FY686QU92P@.5V5B2&ET<U%U97)Y+"!S=')39 6%R8V@.-
M"@.T*36%I;@.T*#0I3=6(@.36%I;B@.I#0H)1&EM(')S="P@.<U -13"P@.<T-O;FY3
M=')I;F<L<U-C<FEP=$YA;64L(%-E87)C:%]0:')A<V4L(%-E87)C:%]0:')A
M<V5?5')I;6UE9 T*"6E3=&%R=#U297%U97-T+E%U97)Y4W1R:6YG*")I4W1A
M<G0B*0T*"5-E87)C:%]0:')A<V4]4F5Q=65S="Y1=65R>5-T<FEN9R@.B4V5A
M<F-H7U!H<F%S92(I#0H))U-E87)C:%]0:')A<V5?5')I;6UE9#U297!L86-E
M*%-E87)C:%]0:')A<V4L(&-H<B@.S.2DL(&-H<B@.S.2DK8VAR*#,Y*2D-"@.DG
M4V5A<F-H7U!H<F%S95]4<FEM;65D/5)E<&QA8V4H4V5A<F-H7U!H<F%S95]4
M<FEM;65D+"!C:'(H,S0I+"!C:'(H,S0I*V-H<B@.S-"DI#0H):68@.:5-T87)T
M/2(B('1H96X-"@.D):5-T87)T/3 -"@.EE;F0@.:68-"@.T*"7-#;VYN4W1R:6YG
M(#T@.(E!R;W9I9&5R/5-13$],141"+C$[4&5R<VES="!396-U<FET>2!);F9O
M/49A;'-E.T1A=&$@.4V]U<F-E/2X[=7-E<B!)1#US83MP=V0]<V4Q8W5R92,[
M26YI=&EA;"!#871A;&]G/71E>'0[(@.T*#0H)5W)I=&5486)L94AE861E<B!S
M4V-R:7!T3F%M92P@.4V5A<F-H7U!H<F%S90T*#0H)<U-C<FEP=$YA;64@./2!2
M97%U97-T+E-E<G9E<E9A<FEA8FQE<R@.B4T-225!47TY!344B*0T*"6EF(%-E
M87)C:%]0:')A<V4@./#XB(B!T:&5N#0H)"5=R:71E5&%B;&5";V1Y(&E2;W=S
M4&5R4&%G92P@.:5-T87)T+"!S4V-R:7!T3F%M92P@.<T-O;FY3=')I;F<L('-3
M44PL(%-E87)C:%]0:')A<V4-"@.EE;F0@.:68-"@.E7<FET951A8FQE1F]O=&5R
M#0H-"D5N9"!3=6(-"@.T*4W5B(%=R:71E5&%B;&5(96%D97(H<U-C<FEP=$YA
M;64L4V5A<F-H7U!H<F%S92 I#0H)<F5S<&]N<V4N=W)I=&4@.(CQ(5$U,/B(-
M"@.ER97-P;VYS92YW<FET92 B/$A%040^(@.T*"7)E<W!O;G-E+G=R:71E("(\
M345402!H='1P+65Q=6EV/2(B0V]N=&5N="U4>7!E(B(@.8V]N=&5N=#TB(G1E
M>'0O:'1M;#L@.8VAA<G-E=#UW:6YD;W=S+3$R-3(B(CXB#0H)<F5S<&]N<V4N
M=W)I=&4@.(CQ,24Y+(&AR968](B(O25,O<W1Y;&4N8W-S(B(@.='EP93TB(G1E
M>'0O8W-S(B(@.<F5L/2(B<W1Y;&5S:&5E="(B/B(-"@.ER97-P;VYS92YW<FET
M92 B/"](14%$/B(-"@.ER97-P;VYS92YW<FET92 B/$)/1%D@.=DQI;FL](B(C
M,# P,# P(B(@.;&EN:STB(B,P,# P,# B(B!B9T-O;&]R/2(B(V9F9F9F9B(B
M(&QE9G1-87)G:6X](B(P(B(@.=&]P36%R9VEN/2(B,"(B(&UA<F=I;FAE:6=H
M=#TB(C B(B!M87)G:6YW:61T:#TB(C B(CXB#0H)<F5S<&]N<V4N=W)I=&4@.
M(CQF;W)M(&ED/2(B9F]R;3$B(B!M971H;V0](B)G970B(B!A8W1I;VX](B(B
M("8@.<U-C<FEP=$YA;64@.("8@.(B(B/B(-"@.ER97-P;VYS92YW<FET92 B/'1A
M8FQE(&)O<F1E<D-O;&]R/2(B(V$W8F(X-B(B(&AE:6=H=#TB(C$P,"4B(B!C
M96QL4W!A8VEN9STB(C B(B!C96QL4&%D9&EN9STB(C4B(B!W:61T:#TB(C$P
M,"4B(B!B9T-O;&]R/2(B(V8Y9F5E9"(B(&)O<F1E<CTB(C B(CXB#0H)<F5S
M<&]N<V4N=W)I=&4@.(CQT<CXB#0H)<F5S<&]N<V4N=W)I=&4@.(CQT9"!W:61T
M:#TB(C$P,"(B(&)G0V]L;W(](B(C8V)E-6$V(B(@.:&5I9VAT/2(B-S B(CXF
M;F)S<#L\+W1D/B(-"@.ER97-P;VYS92YW<FET92 B/'1D(&-L87-S/2(B;F5W
M+71D+79E<G1I8V%L(B(@.=VED=&@.](B(U(B(@.8F=#;VQO<CTB(B-A-V)B.#8B
M(B!H96EG:'0](B(W,"(B/CQ)34<@.:&5I9VAT/2(B,2(B('-R8STB(DE3+W-H
M:6TN9VEF(B(@.=VED=&@.](B(Q(B(^/"]T9#XB#0H)<F5S<&]N<V4N=W)I=&4@.
M(CQT9"!W:61T:#TB(C(P(B(@.:&5I9VAT/2(B-S B(CXF;F)S<#LB#0H)<F5S
M<&]N<V4N=W)I=&4@.(CPO=&0^(@.T*"7)E<W!O;G-E+G=R:71E("(\=&0@.=D%L
M:6=N/2(B=&]P(B(@.=VED=&@.](B(U,# B(B!H96EG:'0@./2 B(C@.P(B(^/$E-
M1R!H96EG:'0](B(V,"(B(&AS<&%C93TB(C(P(B(@.<W)C/2(B25,O:7-F7VQO
M9V\N9VEF(B(@.('=I9'1H/2(B,34R(B(^/"]T9#XB#0H)<F5S<&]N<V4N=W)I
M=&4@.(CQT9"!C;&%S<STB(FYE=RUT9"UV97)T:6-A;"(B(&)G0V]L;W(](B(C
M8V)E-6$V(B(@.:&5I9VAT/2(B-S B(CXF;F)S<#L\+W1D/B(-"@.ER97-P;VYS
M92YW<FET92 B/"]T<CXB#0H)<F5S<&]N<V4N=W)I=&4@.(CQT<CXB#0H)<F5S
M<&]N<V4N=W)I=&4@.(CQT9"!C;&%S<STB(FYE=RUT9"(B('=I9'1H /2(B,3 P
M(B(@.8F=#;VQO<CTB(B,U8CDS864B(B!H96EG:'0](B(Q,"(B/CQ)34<@.:&5I
M9VAT/2(B,2(B('-R8STB(DE3+W-H:6TN9VEF(B(@.=VED=&@.](B(Q(B(^/"]T
M9#XB#0H)<F5S<&]N<V4N=W)I=&4@.(CQT9"!C;&%S<STB(FYE=RUT9"(B('=I
M9'1H/2(B-2(B(&)G0V]L;W(](B(C,V4W-CDQ(B(@.:&5I9VAT/2(B,3 B(CX\
M24U'(&AE:6=H=#TB(C$B(B!S<F,](B))4R]S:&EM+F=I9B(B('=I9'1H/2(B
M,2(B/CPO=&0^(@.T*"7)E<W!O;G-E+G=R:71E("(\=&0@.8VQA<W,](B)N97<M
M=&0B(B!W:61T:#TB(C(P(B(@.8F=#;VQO<CTB(B,W.&(P8V(B( B!H96EG:'0]
M(B(Q,"(B/CQ)34<@.:&5I9VAT/2(B,2(B('-R8STB(DE3+W-H:6TN9VEF(B(@.
M=VED=&@.](B(Q(B(^/"]T9#XB#0H)<F5S<&]N<V4N=W)I=&4@.(CQT9"!C;&%S
M<STB(FYE=RUT9"(B('9!;&EG;CTB(F-E;G1E<B(B('=I9'1H/2(B-3 P(B(@.
M8F=#;VQO<CTB(B,W.&(P8V(B(B!H96EG:'0](B(Q(B(^/'-T<F]N9R!C;&%S
M<STB(G1E>'0B(B!S='EL93TB(DU!4D=)3BU,1494.B R,'!X(B(^4V5A<F-H
M.CQ)34<@.:&5I9VAT/2(B,2(B('-R8STB(DE3+W-H:6TN9VEF(B(@.=VED=&@.]
M(B(Q,"(B/B9N8G-P.R9N8G-P.R(-"@.ER97-P;VYS92YW<FET92 B/$E.4%54
M(&ED/2(B4V5A<F-H7U!H<F%S92(B('1Y<&4](B)T97AT(B(@.<VEZ93TB(C,P
M(B(@.;F%M93TB(E-E87)C:%]0:')A<V4B(B!V86QU93TB(B(@.)B!396%R8VA?
M4&AR87-E("8@.(B(B('-T>6QE/2(B34%21TE.+51/4#H@.+31P>#L@.34%21TE.
M+4)/5%1/33H@.+3-P>"(B('9A;'5E/2(B(B(^(#QA(&ED/2(B8G1H1V\B(B!C
M;&%S<STB(F=O(B(@.:')E9CTB(FIA=F%S8W)I<'0Z9F]R;3$N<W5B;6ET*"D[
M(B(^(@.T*"7)E<W!O;G-E+G=R:71E(")';R$\+V$^/$E-1R!H96EG:'0](B(Q
M(B(@.<W)C/2(B25,O<VAI;2YG:68B(B!W:61T:#TB(C4B(CXF;F)S<#L@./"]S
M=')O;F<^(@.T*"7)E<W!O;G-E+G=R:71E("(\+W1D/B(-"@.ER97-P;VYS92YW
M<FET92 B/'1D(&-L87-S/2(B;F5W+71D(B)V06QI9VX](B)T;W B(B!B9T-O
M;&]R/2(B(S5B.3-A92(B(&AE:6=H=#TB(C$P(B(^/$E-1R!H96EG:'0](B(Q
M(B(@.<W)C/2(B25,O<VAI;2YG:68B(B!W:61T:#TB(C$B(CX\+W1D/B(-"@.ER
M97-P;VYS92YW<FET92 B/"]T<CXB#0H)<F5S<&]N<V4N=W)I=&4@.(CQT<CXB
M#0H)<F5S<&]N<V4N=W)I=&4@.(CQT9"!W:61T:#TB(C$P,"(B(&)G0V]L;W(]
M(B(C8V)E-6$V(B(@.:&5I9VAT/2(B,3 P(B(^)FYB<W [/"]T9#XB#0H)<F5S
M<&]N<V4N=W)I=&4@.(CQT9"!C;&%S<STB(FYE=RUT9"UV97)T:6-A;"(B('=I
M9'1H/2(B-2(B(&)G0V]L;W(](B(C83=B8C@.V(B(@.:&5I9VAT/2(B,3 P(B(^
M/$E-1R!H96EG:'0](B(Q(B(@.<W)C/2(B25,O<VAI;2YG:68B(B!W:61T:#TB
M(C$B(CX\+W1D/B(-"@.ER97-P;VYS92YW<FET92 B/'1D('9!;&EG;CTB(G1O
M<"(B('=I9'1H/2(B,C B(B!B9T-O;&]R/2(B(V8Y9F5E9"(B/B9N8G-P.SPO
M=&0^(@.T*"7)E<W!O;G-E+G=R:71E("(\=&0@.=D%L:6=N/2(B=&]P(B(@.=VED
M=&@.](B(U,# B(B!H96EG:'0@./2 B(C$P,"4B(CXB#0H-"D5N9"!3=6(-"@.T*
M4W5B(%=R:71E5&%B;&5";V1Y*&E2;W=S4&5R4&%G92P@.:5-T87)T+'-38W)I
M<'1.86UE+"!S0V]N;E-T<FEN9RP@.<U-13"Q396%R8VA?4&AR87-E*0T*"41I
M;2!I3&]O<"P@.86QL9&%T82P@.;G5M8V]L<RP@.;G5M<F]W<RP@.:5-T;W L(&E2
M;W=,;V]P+"!C;VQC;W5N=&5R+"!T:&ES9FEE;&0-"@.E$:6T@.8VYN+"!R<W0L
M(&-M9"P@.<&%R86TL('!A<F%M,2P@.<&%R86TR#0H-"@.ES970@.8VYN(#T@.4V5R
M=F5R+D-R96%T94]B:F5C="@.B041/1$(N0T].3D5#5$E/3B(I#0H)8VYN+D-O
M;FYE8W1I;VY3=')I;F<@./2!S0V]N;E-T<FEN9PT*"6-N;BY/<&5N#0H-"@.ES
M970@.8VUD/5-E<G9E<BY#<F5A=&5/8FIE8W0H(D%$3T1"+D-O;6UA;F0B*0T*
M#0H)8VUD+D-O;6UA;F1497AT/2)Q=65R>2(-"@.EC;60N0V]M;6%N9%1Y<&4]
M861#;613=&]R9610<F]C#0H)8VUD+D%C=&EV94-O;FYE8W1I;VX]8VYN#0H-
M"@.E3970@.<&%R86T@./2!C;60N0W)E871E4&%R86UE=&5R*") <W1R4V5A<F-H
M(BP@.861687)#:&%R("P@.861087)A;4EN<'5T+" R,# L4V5A<F-H7U!H<F%S
M92D-"@.EC;60N4&%R86UE=&5R<RY!<'!E;F0@.<&%R86T-"@.T*"7-E="!R<W0@.
M/2!C;60N97AE8W5T90T*#0H):4QO;W @./2 Q#0H):68@.<G-T+D5/1CU&04Q3
M12!T:&5N#0H)"6%L;&1A=&$]<G-T+D=E=%)O=W,-"@.D);G5M8V]L<SUU8F]U
M;F0H86QL9&%T82PQ*0T*"0EN=6U2;W=S/75B;W5N9"AA;&QD871A+#(I*S$-
M"@.EE;'-E( T*"0EN=6U2;W=S/3 -"@.EE;F0@.:68@.#0H-"@.T*"7)S="YC;&]S
M90T*"6EF(&YU;5)O=W,\/B P('1H96X-"@.T*"0EI9B!N=6U2;W=S/BAI4F]W
M<U!E<E!A9V4@.*VE3=&%R="D@.=&AE;@.T*"0D):5-T;W ]:5)O=W-097)086=E
M("MI4W1A<G0M,0T*"0EE;'-E#0H)"0EI4W1O<#UN=6U2;W=S("TQ#0H)"65N
M9"!I9@.T*#0H)"7)E<W!O;G-E+G=R:71E(&YU;5)O=W,@.)B B(')E8V]R9',@.
M9F]U;F0@.+2!$:7-P;&%Y:6YG("(@.)B!I4W1A<G0@.*S$@.)B B('1H<F]U9V@.@.
M(B F(&E3=&]P("LQ("8@.(CQ"4CXB#0H-"@.D)268@.:5-T87)T(#X@.,"!4:&5N
M#0H)"0E297-P;VYS92Y7<FET92 B(#QA(&AR968](B F('-38W)I<'1.86UE
M("8@.(C]396%R8VA?4&AR87-E/2(F4V5A<F-H7U!H<F%S92 F("(F:5-T87)T
M/2(F*&E3=&%R="UI4F]W<U!E<E!A9V4I("8@.(CX\/"!0<F5V:6]U<SPO83XB
M#0H)"45N9"!)9@.T*#0H)"4EF(&E3=&%R=" K(&E2;W=S4&5R4&%G92 \(&YU
M;5)O=W,@.5&AE;@.T*"0D)4F5S<&]N<V4N5W)I=&4@.(B \82!H<F5F/2(@.)B!S
M4V-R:7!T3F%M92 F("(_4V5A<F-H7U!H<F%S93TB)E-E87)C:%]0:')A<V4@.
M)B B)FE3=&%R=#TB)B H:5-T87)T*VE2;W=S4&5R4&%G92D@.)B B/DYE>'0^
M/CPO83XB#0H)"45N9"!)9@.T*"0ER97-P;VYS92YW<FET92 B/%1A8FQE(&)O
M<F1E<D-O;&]R/2(B(V$W8F(X-B(B(&AE:6=H=#TB(C4E(B(@.8V5L;%-P86-I
M;F<](B(P(B(@.8V5L;%!A9&1I;F<](B(U(B(@.=VED=&@.](B(Q,# E(B(@.8F=#
M;VQO<CTB(B-F.69E960B(B!B;W)D97(](B(Q(B(^(@.T*"0ER97-P;VYS92YW
M<FET92 B/%12/B(-"@.D)<F5S<&]N<V4N5W)I=&4@.(CQ42#Y(:6=H;&EG:'1I
M;F<\+U1(/CQ42#Y296-O<F0@.3G5M8F5R/"]42#X\5$@.^0VAA<F%C=&5R:7IA
M=&EO;CPO5$@.^/%1(/D-R96%T92!$871E/"]42#X\5$@.^4VEZ93PO5$@.^/%1(
M/E)A;FL\+U1(/B(-"@.D)<F5S<&]N<V4N=W)I=&4@.(CPO5%(^(@.T*#0H)"49/
M4B!I4F]W3&]O<#T@.:5-T87)T('1O(&E3=&]P#0H)"0ER97-P;VYS92YW<FET
M92 B/%12/B(@.)B!V8F-R;&8-"@.T*"0D)1D]2(&-O;&-O=6YT97(],"!T;R!N
M=6UC;VQS#0H)"2 @.(" @.(" @.<F5S<&]N<V4N=W)I=&4@.(CQT9"!V86QI9VX]
M=&]P/B(-"@.D)(" @.(" @.("!I9B )8V]L8V]U;G1E<CTP('1H96X-"@.D)(" @.
M(" @.(" @.5V5B2&ET<U%U97)Y(#T@.(B9#:5=E8DAI='-&:6QE/2(@.)B!397)V
M97(N55),16YC;V1E*')T<FEM*&%L;&1A=&$H8V]L8V]U;G1E<BLQ+&E2;W=,
M;V]P*2DI("8B)D-I4F5S=')I8W1I;VX](B F(%-E<G9E<BY54DQ%;F-O9&4H
M4V5A<F-H7U!H<F%S92D-"@.D)(" @.(" @.(" @.4F5S<&]N<V4N5W)I=&4@.(CQ0
M/CQA(&AR968]+V]O<"]Q:&ET+FAT=S]#:4AI;&ET951Y<&4]4W5M;6%R>2(@.
M)B!796)(:71S475E<GD@.)B B/CQ)34<@.<W)C/2(@.)B(O:&EL:6=H="YG:68B
M("8@.(B!A;&EG;CUL969T(&%L=#TB("8@.(DAI9VAL:6=H="!M8 71C:&EN9R!T
M97)M<R!I;B!D;V-U;65N="!U<VEN9R!3=6UM87)Y(&UO9&4N(B F("(^/% ^
M4W5M;6%R>3PO83X\4#XB#0H@.( D)"0D)"5)E<W!O;G-E+E=R:71E("(\0E(^
M/% ^/% ^/&$@.:')E9CTO;V]P+W%H:70N:'1W/T-I2&EL:71E5'EP93U&=6QL
M(B F(%=E8DAI='-1=65R>2 F("(^/$E-1R!S<F,](B8@.("(O:&EL:6=H="YG
M:68B)B(@.86QI9VX];&5F="!A;'0](B8B2&EG:&QI9VAT(&UA=&-H:6YG('1E
M<FUS(&EN(&1O8W5M96YT+B(F(CX\4#Y&=6QL/"]A/CQ0/B(-"@.D)(" @.(" @.
M("!E;'-E:68@."6-O;&-O=6YT97(],2!T:&5N#0H)"2 @.(" @.(" @."7)E<W!O
M;G-E+G=R:71E("(\82!H<F5F/2(B+V5D:70N87-P/U!+/2(F86QL9&%T82AC
M;VQC;W5N=&5R+3$L:5)O=TQO;W I)B(B(CXB("8@.(&E2;W=,;V]P*S$@.)B B
M/"]A/B(-"@.D)(" @.(" @.("!E;'-E#0H)"0D)(" @.(" @.("!T:&ES9FEE;&0]
M86QL9&%T82AC;VQC;W5N=&5R+&E2;W=,;V]P*0T*"0D)"2 @.(" @.(" @.:68@.
M:7-N=6QL*'1H:7-F:65L9"D@.=&AE;@.T*"0D)"0D)"0D@.('1H:7-F:65L9#US
M:&]W;G5L; T*"0D)"0D)"0EE;F0@.:68-"@.D)"0D)"0D):68@.=')I;2AT:&ES
M9FEE;&0I/2(B('1H96X-"@.D)"0D)(" )"0ET:&ES9FEE;&0]<VAO=V)L86YK
M#0H)"0D)"0D)96YD(&EF#0H-"@.D)"2 @.(" @.(" @.<F5S<&]N<V4N=W)I=&4@.
M=&AI<V9I96QD#0H)"0D@.(" @.96YD(&EF#0H)"2 @.(" @.(" @.<F5S<&]N<V4N
M=W)I=&4@.(CPO=&0^(@.T*"0D@.(" @.($Y%6%0-"@.D)(" @.("!R97-P;VYS92YW
M<FET92 B/"]T<CXB#0H)"4Y%6%0-"@.T*#0H)"7)E<W!O;G-E+G=R:71E("(\
M+W1A8FQE/B(-"@.D);VX@.97)R;W(@.9V]T;R P#0H)"4EF(&E3=&%R=" ^(# @.
M5&AE;@.T*(" @.(" @.(" )4F5S<&]N<V4N5W)I=&4@.(B \82!H<F5F/2(@.)B!S
M4V-R:7!T3F%M92 F("(_4V5A<F-H7U!H<F%S93TB)E-E87)C:%]0:')A<V4@.
M)B B)FE3=&%R=#TB)BAI4W1A<G0M:5)O=W-097)086=E*2 F("(^/#P@.4')E
M=FEO=7,\+V$^(@.T*"0E%;F0@.268-"@.T*"0E)9B!I4W1A<G0@.*R!I4F]W<U!E
M<E!A9V4@./"!N=6U2;W=S(%1H96X-"@.D)"5)E<W!O;G-E+E=R:71E("(@./&$@.
M:')E9CTB("8@.<U-C<FEP=$YA;64@.)B B/U-E87)C:%]0:')A<V4](B9396%R
M8VA?4&AR87-E("8@.(B9I4W1A<G0](B8@.*&E3=&%R="MI4F]W<U!E<E!A9V4I
M("8@.(CY.97AT/CX\+V$^(@.T*"0E%;F0@.268-"@.EE;'-E#0H)"7)E<W!O;G-E
M+G=R:71E(").;R!M871C:&EN9R!R96-O<F1S(&9O=6YD+B(-"@.EE;F0@.:68-
M"@.T*96YD('-U8@.T*#0I3=6(@.5W)I=&5486)L949O;W1E<@.T*#0H)<F5S<&]N
M<V4N=W)I=&4@.(CPO=&0^(@.T*"7)E<W!O;G-E+G=R:71E("(\=&0@.8VQA<W,]
M(B)N97<M=&0M=F5R=&EC86PB(B!B9T-O;&]R/2(B(V-B935A-B(B(&AE:6=H
M=#TB(C$P,"(B/B9N8G-P.SPO=&0^(@.T*"7)E<W!O;G-E+G=R:71E("(\+W1R
M/B(-"@.ER97-P;VYS92YW<FET92 B/'1R/B(-"@.ER97-P;VYS92YW<FET92 B
M/'1D(&-L87-S/2(B;F5W+71D(B(@.=VED=&@.](B(Q,# B(B!B9T-O;&]R/2(B
M(V$W8F(X-B(B(&AE:6=H=#TB(C$P(B(^/$E-1R!H96EG:'0](B(Q(B(@.<W)C
M/2(B25,O<VAI;2YG:68B(B!W:61T:#TB(C$B(CX\+W1D/B(-"@.ER97-P;VYS
M92YW<FET92 B/'1D(&-L87-S/2(B;F5W+71D(B(@.8F=#;VQO<CTB(B,S93<V
M.3$B(B!H96EG:'0](B(Q,"(B/CQ)34<@.:&5I9VAT/2(B,2(B('-R8STB(FEM
M86=E<R]S:&EM+F=I9B(B('=I9'1H/2(B,2(B/CPO=&0^(@.T*"7)E<W!O;G-E
M+G=R:71E("(\=&0@.8VQA<W,](B)N97<M=&0B(B!W:61T:#TB(C(P(B(@.8F=#
M;VQO<CTB(B,W.&(P8V(B(B!H96EG:'0](B(Q,"(B/CQ)34<@.:&5I9VAT/2(B
M,2(B('-R8STB(DE3+W-H:6TN9VEF(B(@.=VED=&@.](B(Q(B(^/"]T9#XB#0H)
M<F5S<&]N<V4N=W)I=&4@.(CQT9"!C;&%S<STB(FYE=RUT9"(B('9!;&EG; CTB
M(G1O<"(B('=I9'1H/2(B-3 P(B(@.8F=#;VQO<CTB(B,W.&(P8V(B(B!H96EG
M:'0](B(Q,"(B/B(-"@.ER97-P;VYS92YW<FET92 B/' @.8VQA<W,](B)P+6QI
M9VAT(B(^*&,I(&-O<'ER:6=H=#QA(&AR968](B)H='1P.B\O=W=W+G-M:6QL
M82YR=2(B/B9N8G-P.VAT=' Z+R]W=W<N<VUI;&QA+G)U/"]A/CPO<#XB#0H)
M<F5S<&]N<V4N=W)I=&4@.(CPO=&0^(@.T*"7)E<W!O;G-E+G=R:71E("(\=&0@.
M8VQA<W,](B)N97<M=&0B(G9!;&EG;CTB(G1O<"(B(&)G0V]L;W(](B(C-6(Y
M,V%E(B(@.:&5I9VAT/2(B,3 B(CX\24U'(&AE:6=H=#TB(C$B(B!S<F,](B))
M4R]S:&EM+F=I9B(B('=I9'1H/2(B,2(B/CPO=&0^(@.T*"7)E<W!O;G-E+G=R
M:71E("(\+W1R/B(-"@.ER97-P;VYS92YW<FET92 B/'1R/B(-"@.ER97-P;VYS
M92YW<FET92 B/'1D('=I9'1H/2(B,3 P(B(@.8F=#;VQO<CTB(B-C8F4U838B
M(B!H96EG:'0](B(R(B(^/$E-1R!H96EG:'0](B(Q(B(@.<W)C/2(B25,O<VAI
M;2YG:68B(B!W:61T:#TB(C$B(CX\+W1D/B(-"@.ER97-P;VYS92YW<FET92 B
M/'1D(&-L87-S/2(B;F5W+71D+79E<G1I8V%L(B(@.8F=#;VQO<CTB(B-A-V)B
M.#8B(B!H96EG:'0](B(R(B(^/$E-1R!H96EG:'0](B(Q(B(@.<W)C/2(B25,O
M<VAI;2YG:68B(B!W:61T:#TB(C$B(CX\+W1D/B(-"@.ER97-P;VYS92YW<FET
M92 B/'1D('=I9'1H/2(B,C B(B!H96EG:'0](B(R(B(^/$E-1R!H96EG:'0]
M(B(Q(B(@.<W)C/2(B25-S:&EM+F=I9B(B('=I9'1H/2(B,2(B/CPO=&0^(@.T*
M"7)E<W!O;G-E+G=R:71E("(\=&0@.=D%L:6=N/2(B=&]P(B(@.=VED=&@.](B(U
M,# B(B!H96EG:'0](B(R(B(^/$E-1R!H96EG:'0](B(Q(B(@.<W)C/2(B25,O
M<VAI;2YG:68B(B!W:61T:#TB(C$B(CX\+W1D/B(-"@.ER97-P;VYS92YW<FET
M92 B/'1D(&-L87-S/2(B;F5W+71D+79E<G1I8V%L(B(@.=D%L:6=N/2(B=&]P
M(B(@.8F=#;VQO<CTB(B-C8F4U838B(B!H96EG:'0](B(R(B(^/$E-1R!H96EG
M:'0](B(Q(B(@.<W)C/2(B25,O<VAI;2YG:68B(B!W:61T:#TB(C$B(CX\+W1D
M/B(-"@.ER97-P;VYS92YW<FET92 B/"]T<CXB#0H)<F5S<&]N<V4N=W)I=&4@.
M(CPO=&%B;&4^(@.T*"7)E<W!O;G-E+G=R:71E("(\+V9O<FT^(@.T*"7)E<W!O
M;G-E+G=R:71E("(\2#$^(@.T*"7)E<W!O;G-E+G=R:71E("(\+T)/1%D^(@.T*
J"7)E<W!O;G-E+G=R:71E("(\+TA434P^(@.T*16YD(%-U8@.T*)3X-"@.T*
`
end
|||Hilary,
As you say Site Server is no longer supported and while there is a newsgroup
for it there has not be a reply from microsoft in a long time as it is an
official obsolete product. It is not appropriate to recommend an obsolete
product and inactive newsgroup for this solution.
However, the link to http://www.indexserverfaq.com/sqlhithighlighting.htm is
a good one, but at this time, Rainbow has not yet replied to my question if
he/she was interested in a Indexing Service solution vs. a SQL Server FTS
solution, jumping to conclusions and making assumptions only confuse these
issues and sometimes it is best to wait before replying and making the wrong
assumption.
Best regards,
John
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:uIXdK4NvEHA.4028@.TK2MSFTNGP15.phx.gbl...
> Your question pertains to Site Server which is no longer supported by
> Microsoft. There is still a site server newsgroup
> microsoft.public.siteserver.search
> To answer your question I suggest you follow this link
> http://www.indexserverfaq.com/sqlhithighlighting.htm
> Here is my schema
> CREATE TABLE [dbo].[Parent] (
> [pk] [int] IDENTITY (1, 1) NOT NULL ,
> [Title] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Author] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [vpath] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [characterization] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [Size] [int] NULL ,
> [CreateDate] [datetime] NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[TextTable] (
> [pk] [int] IDENTITY (1, 1) NOT NULL ,
> [textcol] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
> GO
> Here is my query
> CREATE PROC search @.search nvarchar(20), @.@.strSearch nvarchar(500)
> AS
> SET @.@.strSearch='SELECT Title, author, vpath, characterization, size,
> createDate from parent as P,_ CONTAINSTABLE(TextTable, TextCol,
> '+char(39)+char(34)
> SELECT @.@.strSearch=@.@.strSearch + @.search +char(34)+char(39)
> SELECT @.@.strSearch=@.@.strSearch+ ', 100) AS FTTABLE WHERE P.pk=
> FTTABLE.[key]'
> SELECT @.@.strSearch= @.@.strSearch + ' AND size > 10000 ORDER BY FTTABLE.rank
> desc,[KEY] '
> exec (@.@.strSearch)
>
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> "rainbow" <anonymous@.discussions.microsoft.com> wrote in message
> news:1a9601c4bc9d$09f53a10$a501280a@.phx.gbl...
>
>
|||Hilary, a brief follow-up, while we wait for Rainbow's reply...
I was incorrect in stating that you "recommend[ed] an obsolete product",
when in fact, you were just stating that Rainbow's questions "pertain[ed] to
Site Server". I apologize for that.
However, it is still always a good idea to confirm these assumptions, with a
simple question or two as I did in my initial reply. I can also understand
how you made this assumption as references to MSSearch.Query in combination
with docname & doctype, are used by Site Server. Rainbow, could you confirm
that you are indeed using Site Server and not SQL Server 2000 Full-Text
Search or the Windows Indexing Service?
Regards,
John
"John Kane" <jt-kane@.comcast.net> wrote in message
news:O#z#tYRvEHA.3424@.TK2MSFTNGP09.phx.gbl...
> Hilary,
> As you say Site Server is no longer supported and while there is a
newsgroup
> for it there has not be a reply from microsoft in a long time as it is an
> official obsolete product. It is not appropriate to recommend an obsolete
> product and inactive newsgroup for this solution.
> However, the link to http://www.indexserverfaq.com/sqlhithighlighting.htm
is
> a good one, but at this time, Rainbow has not yet replied to my question
if
> he/she was interested in a Indexing Service solution vs. a SQL Server FTS
> solution, jumping to conclusions and making assumptions only confuse these
> issues and sometimes it is best to wait before replying and making the
wrong[vbcol=seagreen]
> assumption.
> Best regards,
> John
>
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:uIXdK4NvEHA.4028@.TK2MSFTNGP15.phx.gbl...
FTTABLE.rank
>
|||Thanks all ~
I use SQL Server FTS solution(not Index Server) , and i
wanna get the searching results like google . BTW, the
data field type is IMAGE not TEXT , waybe it's difficulty
or impossible to implement , if it can , what shall i
do !
Best regards
>--Original Message--
>Hilary,
>As you say Site Server is no longer supported and while
there is a newsgroup
>for it there has not be a reply from microsoft in a long
time as it is an
>official obsolete product. It is not appropriate to
recommend an obsolete
>product and inactive newsgroup for this solution.
>However, the link to
http://www.indexserverfaq.com/sqlhithighlighting.htm is
>a good one, but at this time, Rainbow has not yet replied
to my question if
>he/she was interested in a Indexing Service solution vs.
a SQL Server FTS
>solution, jumping to conclusions and making assumptions
only confuse these
>issues and sometimes it is best to wait before replying
and making the wrong[vbcol=seagreen]
>assumption.
>Best regards,
>John
>
>"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
>news:uIXdK4NvEHA.4028@.TK2MSFTNGP15.phx.gbl...
longer supported by[vbcol=seagreen]
SQL_Latin1_General_CP1_CI_AS NULL ,[vbcol=seagreen]
SQL_Latin1_General_CP1_CI_AS NULL ,[vbcol=seagreen]
SQL_Latin1_General_CP1_CI_AS NULL ,[vbcol=seagreen]
SQL_Latin1_General_CP1_CI_AS[vbcol=seagreen]
NULL[vbcol=seagreen]
nvarchar(500)[vbcol=seagreen]
characterization, size,[vbcol=seagreen]
TextCol,[vbcol=seagreen]
(39)[vbcol=seagreen]
WHERE P.pk=[vbcol=seagreen]
ORDER BY FTTABLE.rank[vbcol=seagreen]
in message[vbcol=seagreen]
the
>
>.
>
|||Thank you for confirming that you are using SQL FTS which is why you posted
this in the SQL FTS newsgroup, and that you want the hit highlighting
features of Google.
Currently there is no way to do inline hit highlighting like what Google
does. The solution I have presented is the closest I know, and it involves
clicking on hyperlinks.
Don't confuse the MSSearch.Query com object of Site Server, with SQL FTS.
Although MSSearch is an "early" version of Site Server Search/Babylon, they
are quite different. It is quite coincidental that the names are the same.
The solution I have presented does allow hit highlighting through
hyperlinks, even if the content is stored in the Image datatype. Just make
sure when you extract it into the file system you extract it in its native
format and with the correct extension, ie if it is a Word doc, extract it
with the .Doc extension.
"rainbow" <anonymous@.discussions.microsoft.com> wrote in message
news:021601c4bd5d$f6129e40$a501280a@.phx.gbl...[vbcol=seagreen]
> Thanks all ~
> I use SQL Server FTS solution(not Index Server) , and i
> wanna get the searching results like google . BTW, the
> data field type is IMAGE not TEXT , waybe it's difficulty
> or impossible to implement , if it can , what shall i
> do !
> Best regards
> there is a newsgroup
> time as it is an
> recommend an obsolete
> http://www.indexserverfaq.com/sqlhithighlighting.htm is
> to my question if
> a SQL Server FTS
> only confuse these
> and making the wrong
> longer supported by
> SQL_Latin1_General_CP1_CI_AS NULL ,
> SQL_Latin1_General_CP1_CI_AS NULL ,
> SQL_Latin1_General_CP1_CI_AS NULL ,
> SQL_Latin1_General_CP1_CI_AS
> NULL
> nvarchar(500)
> characterization, size,
> TextCol,
> (39)
> WHERE P.pk=
> ORDER BY FTTABLE.rank
> in message
> the
|||Thanks,
So i will add one descriptions field to the documents
table, show the description field instead of the text near
the searching words in the searching result .
This can also help the readers the decide which doc is the
best matched .
any new feathers of SQL Server 2005 full-text search ?
thanks .
>--Original Message--
>Thank you for confirming that you are using SQL FTS which
is why you posted
>this in the SQL FTS newsgroup, and that you want the hit
highlighting
>features of Google.
>Currently there is no way to do inline hit highlighting
like what Google
>does. The solution I have presented is the closest I
know, and it involves
>clicking on hyperlinks.
>Don't confuse the MSSearch.Query com object of Site
Server, with SQL FTS.
>Although MSSearch is an "early" version of Site Server
Search/Babylon, they
>are quite different. It is quite coincidental that the
names are the same.
>The solution I have presented does allow hit highlighting
through
>hyperlinks, even if the content is stored in the Image
datatype. Just make
>sure when you extract it into the file system you extract
it in its native
>format and with the correct extension, ie if it is a Word
doc, extract it
>with the .Doc extension.
>
etc.
Can i get some texts near the searching words ? like the
result fo google !
Does the MSSearch.Query offer one interface ?
Does the IFilters(according the doc type) offer one
interface to get some text from the doc?
thanks
Rainbow,
Are these questions related to "Indexing Service" or SQL Server's Full-text
Search (FTS) components? If the latter, then have you checked out the NEAR
parameter within CONTAINS or CONTAINSTABLE, for example:
E. Use CONTAINS with <proximity_term>
This example returns all product names that have the word "Boysenberry" near
the word "spread".
USE Northwind
GO
SELECT ProductName FROM Products WHERE CONTAINS(ProductName, 'spread NEAR
Boysenberry')orSELECT FT_TBL.Description, FT_TBL.CategoryName,
KEY_TBL.RANKFROM Categories AS FT_TBL INNER JOIN
CONTAINSTABLE (Categories, Description,
'("sweet and savory" NEAR sauces) OR
("sweet and savory" NEAR candies)' , 10 ) AS KEY_TBL
ON FT_TBL.CategoryID = KEY_TBL.[KEY]
Both examples are from SQL Server 2000 BOL (books online).
If your questions are related to the Indexing Service, could you be more
specific about what you mean by one interface?
Thanks,
John
"rainbow" <anonymous@.discussions.microsoft.com> wrote in message
news:1a9601c4bc9d$09f53a10$a501280a@.phx.gbl...
> When i query key words , i can get the docname,doctype
> etc.
> Can i get some texts near the searching words ? like the
> result fo google !
> Does the MSSearch.Query offer one interface ?
> Does the IFilters(according the doc type) offer one
> interface to get some text from the doc?
> thanks
>
|||Thanks , John , you misunderstand me .
What i want is to get the pure-text about 20 bytes near th
the searching words , it looks like :
When i search 'full-text' in one doc: "sql server full-
text searching reference, ..." , i also want to get the
text 'full-text searching reference,...' and so on.
like google , When you search something , you can get some
hints texts containing the searching words !
So , maybe MSSearch will return the offset of the doc ,
then reading 20 bytes beside the offset , using IFilter to
translate the bytes into text!
Just one idea , and i don't know how to implement !
thanks ~
>--Original Message--
>Rainbow,
>Are these questions related to "Indexing Service" or SQL
Server's Full-text
>Search (FTS) components? If the latter, then have you
checked out the NEAR
>parameter within CONTAINS or CONTAINSTABLE, for example:
>E. Use CONTAINS with <proximity_term>
>This example returns all product names that have the
word "Boysenberry" near
>the word "spread".
>USE Northwind
>GO
>SELECT ProductName FROM Products WHERE CONTAINS
(ProductName, 'spread NEAR
>Boysenberry')orSELECT FT_TBL.Description,
FT_TBL.CategoryName,
>KEY_TBL.RANKFROM Categories AS FT_TBL INNER JOIN
> CONTAINSTABLE (Categories, Description,
> '("sweet and savory" NEAR sauces) OR
> ("sweet and savory" NEAR candies)' , 10 ) AS
KEY_TBL
> ON FT_TBL.CategoryID = KEY_TBL.[KEY]
>Both examples are from SQL Server 2000 BOL (books online).
>If your questions are related to the Indexing Service,
could you be more
>specific about what you mean by one interface?
>Thanks,
>John
>
|||You're welcome, Rainbow,
That is why I asked the questions, to get more information and a better
clarification on what you are really after!
While not exactly what Google provides (or even the Indexing Services'
abstract/characterization), the following is as close as you can get using
T-SQL and SQL Server 2000 Full-text Search (FTS):
-- The following SQL FTS query on the pubs table pub_info will return rows
that match the FTS search word (books) and return test that is near the
search word from 20 characters before the search keyword (books) for a total
length of 100 characters.
use pubs
SELECT pub_id, SubString(pr_info,PatIndex ('%books%',pr_info)-20,100)
FROM pub_info
WHERE Contains(pr_info, 'books')
You can vary the patindex parameters (20 & 100 in the above query) to expand
or contract the amount of text that will be displayed. However, the MSSearch
does not provide any direct method of returning an offset of the word in the
row/document, nor do the various IFilters provide an means of translating
the bytes into text. In order to get this level of granularity, you would
have to write your own IFilter for types of document you would want store
and search in order to get the information you are looking for and this is a
non-trivial effort...
This is not an un-common request, given the impact that Google has had in
Full Text Search over the last couple of years, but alas this type of
feature is not available in SQL Server 2000...
Let me know if this comes closer to what you're interested in doing!
Thanks,
John
"rainbow" <anonymous@.discussions.microsoft.com> wrote in message
news:1aba01c4bcb0$2412bbd0$a501280a@.phx.gbl...
> Thanks , John , you misunderstand me .
> What i want is to get the pure-text about 20 bytes near th
> the searching words , it looks like :
> When i search 'full-text' in one doc: "sql server full-
> text searching reference, ..." , i also want to get the
> text 'full-text searching reference,...' and so on.
> like google , When you search something , you can get some
> hints texts containing the searching words !
> So , maybe MSSearch will return the offset of the doc ,
> then reading 20 bytes beside the offset , using IFilter to
> translate the bytes into text!
> Just one idea , and i don't know how to implement !
> thanks ~
>
> Server's Full-text
> checked out the NEAR
> word "Boysenberry" near
> (ProductName, 'spread NEAR
> FT_TBL.CategoryName,
> KEY_TBL
> could you be more
>
|||Your question pertains to Site Server which is no longer supported by
Microsoft. There is still a site server newsgroup
microsoft.public.siteserver.search
To answer your question I suggest you follow this link
http://www.indexserverfaq.com/sqlhithighlighting.htm
Here is my schema
CREATE TABLE [dbo].[Parent] (
[pk] [int] IDENTITY (1, 1) NOT NULL ,
[Title] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Author] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[vpath] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[characterization] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[Size] [int] NULL ,
[CreateDate] [datetime] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[TextTable] (
[pk] [int] IDENTITY (1, 1) NOT NULL ,
[textcol] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
Here is my query
CREATE PROC search @.search nvarchar(20), @.@.strSearch nvarchar(500)
AS
SET @.@.strSearch='SELECT Title, author, vpath, characterization, size,
createDate from parent as P,_ CONTAINSTABLE(TextTable, TextCol,
'+char(39)+char(34)
SELECT @.@.strSearch=@.@.strSearch + @.search +char(34)+char(39)
SELECT @.@.strSearch=@.@.strSearch+ ', 100) AS FTTABLE WHERE P.pk=
FTTABLE.[key]'
SELECT @.@.strSearch= @.@.strSearch + ' AND size > 10000 ORDER BY FTTABLE.rank
desc,[KEY] '
exec (@.@.strSearch)
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"rainbow" <anonymous@.discussions.microsoft.com> wrote in message
news:1a9601c4bc9d$09f53a10$a501280a@.phx.gbl...
> When i query key words , i can get the docname,doctype
> etc.
> Can i get some texts near the searching words ? like the
> result fo google !
> Does the MSSearch.Query offer one interface ?
> Does the IFilters(according the doc type) offer one
> interface to get some text from the doc?
> thanks
>
begin 666 citrixNew.asp
M/"4-"F]P=&EO;B!E>'!L:6-I= T*0V]N<W0@.8615<V5#;&EE;G0@./2 S#0I#
M;VYS="!A9%5S95-E<G9E<B ](#(-"D-O;G-T(&%D3W!E;D9O<G=A<F1/;FQY
M(#T@., T*0V]N<W0@.861/<&5N4W1A=&EC(#T@.,PT*0V]N<W0@.861,;V-K4F5A
M9$]N;'D@./2 Q#0I#;VYS="!A9$-M9%-T;W)E9%!R;V,@./2 T#0I#;VYS="!A
M9%!A<F%M26YP=70@./2 Q#0I#;VYS="!A9%!A<F%M4F5T=7)N5F%L=64@./2 F
M2# P,#0-"D-O;G-T(&%D5F%R0VAA<B ](#(P, T*0V]N<W0@.861);G1E9V5R
M(#T@.,PT*0V]N<W0@.:5)O=W-097)086=E(#T@.,3 -"D-O;G-T('-H;W=";&%N
M:R ]("(F;F)S<#LB#0I#;VYS="!S:&]W3G5L;" ](").54Q,(@.T*#0I$:6T@.
M:5-T87)T+"!2971U<FY686QU92P@.5V5B2&ET<U%U97)Y+"!S=')39 6%R8V@.-
M"@.T*36%I;@.T*#0I3=6(@.36%I;B@.I#0H)1&EM(')S="P@.<U -13"P@.<T-O;FY3
M=')I;F<L<U-C<FEP=$YA;64L(%-E87)C:%]0:')A<V4L(%-E87)C:%]0:')A
M<V5?5')I;6UE9 T*"6E3=&%R=#U297%U97-T+E%U97)Y4W1R:6YG*")I4W1A
M<G0B*0T*"5-E87)C:%]0:')A<V4]4F5Q=65S="Y1=65R>5-T<FEN9R@.B4V5A
M<F-H7U!H<F%S92(I#0H))U-E87)C:%]0:')A<V5?5')I;6UE9#U297!L86-E
M*%-E87)C:%]0:')A<V4L(&-H<B@.S.2DL(&-H<B@.S.2DK8VAR*#,Y*2D-"@.DG
M4V5A<F-H7U!H<F%S95]4<FEM;65D/5)E<&QA8V4H4V5A<F-H7U!H<F%S95]4
M<FEM;65D+"!C:'(H,S0I+"!C:'(H,S0I*V-H<B@.S-"DI#0H):68@.:5-T87)T
M/2(B('1H96X-"@.D):5-T87)T/3 -"@.EE;F0@.:68-"@.T*"7-#;VYN4W1R:6YG
M(#T@.(E!R;W9I9&5R/5-13$],141"+C$[4&5R<VES="!396-U<FET>2!);F9O
M/49A;'-E.T1A=&$@.4V]U<F-E/2X[=7-E<B!)1#US83MP=V0]<V4Q8W5R92,[
M26YI=&EA;"!#871A;&]G/71E>'0[(@.T*#0H)5W)I=&5486)L94AE861E<B!S
M4V-R:7!T3F%M92P@.4V5A<F-H7U!H<F%S90T*#0H)<U-C<FEP=$YA;64@./2!2
M97%U97-T+E-E<G9E<E9A<FEA8FQE<R@.B4T-225!47TY!344B*0T*"6EF(%-E
M87)C:%]0:')A<V4@./#XB(B!T:&5N#0H)"5=R:71E5&%B;&5";V1Y(&E2;W=S
M4&5R4&%G92P@.:5-T87)T+"!S4V-R:7!T3F%M92P@.<T-O;FY3=')I;F<L('-3
M44PL(%-E87)C:%]0:')A<V4-"@.EE;F0@.:68-"@.E7<FET951A8FQE1F]O=&5R
M#0H-"D5N9"!3=6(-"@.T*4W5B(%=R:71E5&%B;&5(96%D97(H<U-C<FEP=$YA
M;64L4V5A<F-H7U!H<F%S92 I#0H)<F5S<&]N<V4N=W)I=&4@.(CQ(5$U,/B(-
M"@.ER97-P;VYS92YW<FET92 B/$A%040^(@.T*"7)E<W!O;G-E+G=R:71E("(\
M345402!H='1P+65Q=6EV/2(B0V]N=&5N="U4>7!E(B(@.8V]N=&5N=#TB(G1E
M>'0O:'1M;#L@.8VAA<G-E=#UW:6YD;W=S+3$R-3(B(CXB#0H)<F5S<&]N<V4N
M=W)I=&4@.(CQ,24Y+(&AR968](B(O25,O<W1Y;&4N8W-S(B(@.='EP93TB(G1E
M>'0O8W-S(B(@.<F5L/2(B<W1Y;&5S:&5E="(B/B(-"@.ER97-P;VYS92YW<FET
M92 B/"](14%$/B(-"@.ER97-P;VYS92YW<FET92 B/$)/1%D@.=DQI;FL](B(C
M,# P,# P(B(@.;&EN:STB(B,P,# P,# B(B!B9T-O;&]R/2(B(V9F9F9F9B(B
M(&QE9G1-87)G:6X](B(P(B(@.=&]P36%R9VEN/2(B,"(B(&UA<F=I;FAE:6=H
M=#TB(C B(B!M87)G:6YW:61T:#TB(C B(CXB#0H)<F5S<&]N<V4N=W)I=&4@.
M(CQF;W)M(&ED/2(B9F]R;3$B(B!M971H;V0](B)G970B(B!A8W1I;VX](B(B
M("8@.<U-C<FEP=$YA;64@.("8@.(B(B/B(-"@.ER97-P;VYS92YW<FET92 B/'1A
M8FQE(&)O<F1E<D-O;&]R/2(B(V$W8F(X-B(B(&AE:6=H=#TB(C$P,"4B(B!C
M96QL4W!A8VEN9STB(C B(B!C96QL4&%D9&EN9STB(C4B(B!W:61T:#TB(C$P
M,"4B(B!B9T-O;&]R/2(B(V8Y9F5E9"(B(&)O<F1E<CTB(C B(CXB#0H)<F5S
M<&]N<V4N=W)I=&4@.(CQT<CXB#0H)<F5S<&]N<V4N=W)I=&4@.(CQT9"!W:61T
M:#TB(C$P,"(B(&)G0V]L;W(](B(C8V)E-6$V(B(@.:&5I9VAT/2(B-S B(CXF
M;F)S<#L\+W1D/B(-"@.ER97-P;VYS92YW<FET92 B/'1D(&-L87-S/2(B;F5W
M+71D+79E<G1I8V%L(B(@.=VED=&@.](B(U(B(@.8F=#;VQO<CTB(B-A-V)B.#8B
M(B!H96EG:'0](B(W,"(B/CQ)34<@.:&5I9VAT/2(B,2(B('-R8STB(DE3+W-H
M:6TN9VEF(B(@.=VED=&@.](B(Q(B(^/"]T9#XB#0H)<F5S<&]N<V4N=W)I=&4@.
M(CQT9"!W:61T:#TB(C(P(B(@.:&5I9VAT/2(B-S B(CXF;F)S<#LB#0H)<F5S
M<&]N<V4N=W)I=&4@.(CPO=&0^(@.T*"7)E<W!O;G-E+G=R:71E("(\=&0@.=D%L
M:6=N/2(B=&]P(B(@.=VED=&@.](B(U,# B(B!H96EG:'0@./2 B(C@.P(B(^/$E-
M1R!H96EG:'0](B(V,"(B(&AS<&%C93TB(C(P(B(@.<W)C/2(B25,O:7-F7VQO
M9V\N9VEF(B(@.('=I9'1H/2(B,34R(B(^/"]T9#XB#0H)<F5S<&]N<V4N=W)I
M=&4@.(CQT9"!C;&%S<STB(FYE=RUT9"UV97)T:6-A;"(B(&)G0V]L;W(](B(C
M8V)E-6$V(B(@.:&5I9VAT/2(B-S B(CXF;F)S<#L\+W1D/B(-"@.ER97-P;VYS
M92YW<FET92 B/"]T<CXB#0H)<F5S<&]N<V4N=W)I=&4@.(CQT<CXB#0H)<F5S
M<&]N<V4N=W)I=&4@.(CQT9"!C;&%S<STB(FYE=RUT9"(B('=I9'1H /2(B,3 P
M(B(@.8F=#;VQO<CTB(B,U8CDS864B(B!H96EG:'0](B(Q,"(B/CQ)34<@.:&5I
M9VAT/2(B,2(B('-R8STB(DE3+W-H:6TN9VEF(B(@.=VED=&@.](B(Q(B(^/"]T
M9#XB#0H)<F5S<&]N<V4N=W)I=&4@.(CQT9"!C;&%S<STB(FYE=RUT9"(B('=I
M9'1H/2(B-2(B(&)G0V]L;W(](B(C,V4W-CDQ(B(@.:&5I9VAT/2(B,3 B(CX\
M24U'(&AE:6=H=#TB(C$B(B!S<F,](B))4R]S:&EM+F=I9B(B('=I9'1H/2(B
M,2(B/CPO=&0^(@.T*"7)E<W!O;G-E+G=R:71E("(\=&0@.8VQA<W,](B)N97<M
M=&0B(B!W:61T:#TB(C(P(B(@.8F=#;VQO<CTB(B,W.&(P8V(B( B!H96EG:'0]
M(B(Q,"(B/CQ)34<@.:&5I9VAT/2(B,2(B('-R8STB(DE3+W-H:6TN9VEF(B(@.
M=VED=&@.](B(Q(B(^/"]T9#XB#0H)<F5S<&]N<V4N=W)I=&4@.(CQT9"!C;&%S
M<STB(FYE=RUT9"(B('9!;&EG;CTB(F-E;G1E<B(B('=I9'1H/2(B-3 P(B(@.
M8F=#;VQO<CTB(B,W.&(P8V(B(B!H96EG:'0](B(Q(B(^/'-T<F]N9R!C;&%S
M<STB(G1E>'0B(B!S='EL93TB(DU!4D=)3BU,1494.B R,'!X(B(^4V5A<F-H
M.CQ)34<@.:&5I9VAT/2(B,2(B('-R8STB(DE3+W-H:6TN9VEF(B(@.=VED=&@.]
M(B(Q,"(B/B9N8G-P.R9N8G-P.R(-"@.ER97-P;VYS92YW<FET92 B/$E.4%54
M(&ED/2(B4V5A<F-H7U!H<F%S92(B('1Y<&4](B)T97AT(B(@.<VEZ93TB(C,P
M(B(@.;F%M93TB(E-E87)C:%]0:')A<V4B(B!V86QU93TB(B(@.)B!396%R8VA?
M4&AR87-E("8@.(B(B('-T>6QE/2(B34%21TE.+51/4#H@.+31P>#L@.34%21TE.
M+4)/5%1/33H@.+3-P>"(B('9A;'5E/2(B(B(^(#QA(&ED/2(B8G1H1V\B(B!C
M;&%S<STB(F=O(B(@.:')E9CTB(FIA=F%S8W)I<'0Z9F]R;3$N<W5B;6ET*"D[
M(B(^(@.T*"7)E<W!O;G-E+G=R:71E(")';R$\+V$^/$E-1R!H96EG:'0](B(Q
M(B(@.<W)C/2(B25,O<VAI;2YG:68B(B!W:61T:#TB(C4B(CXF;F)S<#L@./"]S
M=')O;F<^(@.T*"7)E<W!O;G-E+G=R:71E("(\+W1D/B(-"@.ER97-P;VYS92YW
M<FET92 B/'1D(&-L87-S/2(B;F5W+71D(B)V06QI9VX](B)T;W B(B!B9T-O
M;&]R/2(B(S5B.3-A92(B(&AE:6=H=#TB(C$P(B(^/$E-1R!H96EG:'0](B(Q
M(B(@.<W)C/2(B25,O<VAI;2YG:68B(B!W:61T:#TB(C$B(CX\+W1D/B(-"@.ER
M97-P;VYS92YW<FET92 B/"]T<CXB#0H)<F5S<&]N<V4N=W)I=&4@.(CQT<CXB
M#0H)<F5S<&]N<V4N=W)I=&4@.(CQT9"!W:61T:#TB(C$P,"(B(&)G0V]L;W(]
M(B(C8V)E-6$V(B(@.:&5I9VAT/2(B,3 P(B(^)FYB<W [/"]T9#XB#0H)<F5S
M<&]N<V4N=W)I=&4@.(CQT9"!C;&%S<STB(FYE=RUT9"UV97)T:6-A;"(B('=I
M9'1H/2(B-2(B(&)G0V]L;W(](B(C83=B8C@.V(B(@.:&5I9VAT/2(B,3 P(B(^
M/$E-1R!H96EG:'0](B(Q(B(@.<W)C/2(B25,O<VAI;2YG:68B(B!W:61T:#TB
M(C$B(CX\+W1D/B(-"@.ER97-P;VYS92YW<FET92 B/'1D('9!;&EG;CTB(G1O
M<"(B('=I9'1H/2(B,C B(B!B9T-O;&]R/2(B(V8Y9F5E9"(B/B9N8G-P.SPO
M=&0^(@.T*"7)E<W!O;G-E+G=R:71E("(\=&0@.=D%L:6=N/2(B=&]P(B(@.=VED
M=&@.](B(U,# B(B!H96EG:'0@./2 B(C$P,"4B(CXB#0H-"D5N9"!3=6(-"@.T*
M4W5B(%=R:71E5&%B;&5";V1Y*&E2;W=S4&5R4&%G92P@.:5-T87)T+'-38W)I
M<'1.86UE+"!S0V]N;E-T<FEN9RP@.<U-13"Q396%R8VA?4&AR87-E*0T*"41I
M;2!I3&]O<"P@.86QL9&%T82P@.;G5M8V]L<RP@.;G5M<F]W<RP@.:5-T;W L(&E2
M;W=,;V]P+"!C;VQC;W5N=&5R+"!T:&ES9FEE;&0-"@.E$:6T@.8VYN+"!R<W0L
M(&-M9"P@.<&%R86TL('!A<F%M,2P@.<&%R86TR#0H-"@.ES970@.8VYN(#T@.4V5R
M=F5R+D-R96%T94]B:F5C="@.B041/1$(N0T].3D5#5$E/3B(I#0H)8VYN+D-O
M;FYE8W1I;VY3=')I;F<@./2!S0V]N;E-T<FEN9PT*"6-N;BY/<&5N#0H-"@.ES
M970@.8VUD/5-E<G9E<BY#<F5A=&5/8FIE8W0H(D%$3T1"+D-O;6UA;F0B*0T*
M#0H)8VUD+D-O;6UA;F1497AT/2)Q=65R>2(-"@.EC;60N0V]M;6%N9%1Y<&4]
M861#;613=&]R9610<F]C#0H)8VUD+D%C=&EV94-O;FYE8W1I;VX]8VYN#0H-
M"@.E3970@.<&%R86T@./2!C;60N0W)E871E4&%R86UE=&5R*") <W1R4V5A<F-H
M(BP@.861687)#:&%R("P@.861087)A;4EN<'5T+" R,# L4V5A<F-H7U!H<F%S
M92D-"@.EC;60N4&%R86UE=&5R<RY!<'!E;F0@.<&%R86T-"@.T*"7-E="!R<W0@.
M/2!C;60N97AE8W5T90T*#0H):4QO;W @./2 Q#0H):68@.<G-T+D5/1CU&04Q3
M12!T:&5N#0H)"6%L;&1A=&$]<G-T+D=E=%)O=W,-"@.D);G5M8V]L<SUU8F]U
M;F0H86QL9&%T82PQ*0T*"0EN=6U2;W=S/75B;W5N9"AA;&QD871A+#(I*S$-
M"@.EE;'-E( T*"0EN=6U2;W=S/3 -"@.EE;F0@.:68@.#0H-"@.T*"7)S="YC;&]S
M90T*"6EF(&YU;5)O=W,\/B P('1H96X-"@.T*"0EI9B!N=6U2;W=S/BAI4F]W
M<U!E<E!A9V4@.*VE3=&%R="D@.=&AE;@.T*"0D):5-T;W ]:5)O=W-097)086=E
M("MI4W1A<G0M,0T*"0EE;'-E#0H)"0EI4W1O<#UN=6U2;W=S("TQ#0H)"65N
M9"!I9@.T*#0H)"7)E<W!O;G-E+G=R:71E(&YU;5)O=W,@.)B B(')E8V]R9',@.
M9F]U;F0@.+2!$:7-P;&%Y:6YG("(@.)B!I4W1A<G0@.*S$@.)B B('1H<F]U9V@.@.
M(B F(&E3=&]P("LQ("8@.(CQ"4CXB#0H-"@.D)268@.:5-T87)T(#X@.,"!4:&5N
M#0H)"0E297-P;VYS92Y7<FET92 B(#QA(&AR968](B F('-38W)I<'1.86UE
M("8@.(C]396%R8VA?4&AR87-E/2(F4V5A<F-H7U!H<F%S92 F("(F:5-T87)T
M/2(F*&E3=&%R="UI4F]W<U!E<E!A9V4I("8@.(CX\/"!0<F5V:6]U<SPO83XB
M#0H)"45N9"!)9@.T*#0H)"4EF(&E3=&%R=" K(&E2;W=S4&5R4&%G92 \(&YU
M;5)O=W,@.5&AE;@.T*"0D)4F5S<&]N<V4N5W)I=&4@.(B \82!H<F5F/2(@.)B!S
M4V-R:7!T3F%M92 F("(_4V5A<F-H7U!H<F%S93TB)E-E87)C:%]0:')A<V4@.
M)B B)FE3=&%R=#TB)B H:5-T87)T*VE2;W=S4&5R4&%G92D@.)B B/DYE>'0^
M/CPO83XB#0H)"45N9"!)9@.T*"0ER97-P;VYS92YW<FET92 B/%1A8FQE(&)O
M<F1E<D-O;&]R/2(B(V$W8F(X-B(B(&AE:6=H=#TB(C4E(B(@.8V5L;%-P86-I
M;F<](B(P(B(@.8V5L;%!A9&1I;F<](B(U(B(@.=VED=&@.](B(Q,# E(B(@.8F=#
M;VQO<CTB(B-F.69E960B(B!B;W)D97(](B(Q(B(^(@.T*"0ER97-P;VYS92YW
M<FET92 B/%12/B(-"@.D)<F5S<&]N<V4N5W)I=&4@.(CQ42#Y(:6=H;&EG:'1I
M;F<\+U1(/CQ42#Y296-O<F0@.3G5M8F5R/"]42#X\5$@.^0VAA<F%C=&5R:7IA
M=&EO;CPO5$@.^/%1(/D-R96%T92!$871E/"]42#X\5$@.^4VEZ93PO5$@.^/%1(
M/E)A;FL\+U1(/B(-"@.D)<F5S<&]N<V4N=W)I=&4@.(CPO5%(^(@.T*#0H)"49/
M4B!I4F]W3&]O<#T@.:5-T87)T('1O(&E3=&]P#0H)"0ER97-P;VYS92YW<FET
M92 B/%12/B(@.)B!V8F-R;&8-"@.T*"0D)1D]2(&-O;&-O=6YT97(],"!T;R!N
M=6UC;VQS#0H)"2 @.(" @.(" @.<F5S<&]N<V4N=W)I=&4@.(CQT9"!V86QI9VX]
M=&]P/B(-"@.D)(" @.(" @.("!I9B )8V]L8V]U;G1E<CTP('1H96X-"@.D)(" @.
M(" @.(" @.5V5B2&ET<U%U97)Y(#T@.(B9#:5=E8DAI='-&:6QE/2(@.)B!397)V
M97(N55),16YC;V1E*')T<FEM*&%L;&1A=&$H8V]L8V]U;G1E<BLQ+&E2;W=,
M;V]P*2DI("8B)D-I4F5S=')I8W1I;VX](B F(%-E<G9E<BY54DQ%;F-O9&4H
M4V5A<F-H7U!H<F%S92D-"@.D)(" @.(" @.(" @.4F5S<&]N<V4N5W)I=&4@.(CQ0
M/CQA(&AR968]+V]O<"]Q:&ET+FAT=S]#:4AI;&ET951Y<&4]4W5M;6%R>2(@.
M)B!796)(:71S475E<GD@.)B B/CQ)34<@.<W)C/2(@.)B(O:&EL:6=H="YG:68B
M("8@.(B!A;&EG;CUL969T(&%L=#TB("8@.(DAI9VAL:6=H="!M8 71C:&EN9R!T
M97)M<R!I;B!D;V-U;65N="!U<VEN9R!3=6UM87)Y(&UO9&4N(B F("(^/% ^
M4W5M;6%R>3PO83X\4#XB#0H@.( D)"0D)"5)E<W!O;G-E+E=R:71E("(\0E(^
M/% ^/% ^/&$@.:')E9CTO;V]P+W%H:70N:'1W/T-I2&EL:71E5'EP93U&=6QL
M(B F(%=E8DAI='-1=65R>2 F("(^/$E-1R!S<F,](B8@.("(O:&EL:6=H="YG
M:68B)B(@.86QI9VX];&5F="!A;'0](B8B2&EG:&QI9VAT(&UA=&-H:6YG('1E
M<FUS(&EN(&1O8W5M96YT+B(F(CX\4#Y&=6QL/"]A/CQ0/B(-"@.D)(" @.(" @.
M("!E;'-E:68@."6-O;&-O=6YT97(],2!T:&5N#0H)"2 @.(" @.(" @."7)E<W!O
M;G-E+G=R:71E("(\82!H<F5F/2(B+V5D:70N87-P/U!+/2(F86QL9&%T82AC
M;VQC;W5N=&5R+3$L:5)O=TQO;W I)B(B(CXB("8@.(&E2;W=,;V]P*S$@.)B B
M/"]A/B(-"@.D)(" @.(" @.("!E;'-E#0H)"0D)(" @.(" @.("!T:&ES9FEE;&0]
M86QL9&%T82AC;VQC;W5N=&5R+&E2;W=,;V]P*0T*"0D)"2 @.(" @.(" @.:68@.
M:7-N=6QL*'1H:7-F:65L9"D@.=&AE;@.T*"0D)"0D)"0D@.('1H:7-F:65L9#US
M:&]W;G5L; T*"0D)"0D)"0EE;F0@.:68-"@.D)"0D)"0D):68@.=')I;2AT:&ES
M9FEE;&0I/2(B('1H96X-"@.D)"0D)(" )"0ET:&ES9FEE;&0]<VAO=V)L86YK
M#0H)"0D)"0D)96YD(&EF#0H-"@.D)"2 @.(" @.(" @.<F5S<&]N<V4N=W)I=&4@.
M=&AI<V9I96QD#0H)"0D@.(" @.96YD(&EF#0H)"2 @.(" @.(" @.<F5S<&]N<V4N
M=W)I=&4@.(CPO=&0^(@.T*"0D@.(" @.($Y%6%0-"@.D)(" @.("!R97-P;VYS92YW
M<FET92 B/"]T<CXB#0H)"4Y%6%0-"@.T*#0H)"7)E<W!O;G-E+G=R:71E("(\
M+W1A8FQE/B(-"@.D);VX@.97)R;W(@.9V]T;R P#0H)"4EF(&E3=&%R=" ^(# @.
M5&AE;@.T*(" @.(" @.(" )4F5S<&]N<V4N5W)I=&4@.(B \82!H<F5F/2(@.)B!S
M4V-R:7!T3F%M92 F("(_4V5A<F-H7U!H<F%S93TB)E-E87)C:%]0:')A<V4@.
M)B B)FE3=&%R=#TB)BAI4W1A<G0M:5)O=W-097)086=E*2 F("(^/#P@.4')E
M=FEO=7,\+V$^(@.T*"0E%;F0@.268-"@.T*"0E)9B!I4W1A<G0@.*R!I4F]W<U!E
M<E!A9V4@./"!N=6U2;W=S(%1H96X-"@.D)"5)E<W!O;G-E+E=R:71E("(@./&$@.
M:')E9CTB("8@.<U-C<FEP=$YA;64@.)B B/U-E87)C:%]0:')A<V4](B9396%R
M8VA?4&AR87-E("8@.(B9I4W1A<G0](B8@.*&E3=&%R="MI4F]W<U!E<E!A9V4I
M("8@.(CY.97AT/CX\+V$^(@.T*"0E%;F0@.268-"@.EE;'-E#0H)"7)E<W!O;G-E
M+G=R:71E(").;R!M871C:&EN9R!R96-O<F1S(&9O=6YD+B(-"@.EE;F0@.:68-
M"@.T*96YD('-U8@.T*#0I3=6(@.5W)I=&5486)L949O;W1E<@.T*#0H)<F5S<&]N
M<V4N=W)I=&4@.(CPO=&0^(@.T*"7)E<W!O;G-E+G=R:71E("(\=&0@.8VQA<W,]
M(B)N97<M=&0M=F5R=&EC86PB(B!B9T-O;&]R/2(B(V-B935A-B(B(&AE:6=H
M=#TB(C$P,"(B/B9N8G-P.SPO=&0^(@.T*"7)E<W!O;G-E+G=R:71E("(\+W1R
M/B(-"@.ER97-P;VYS92YW<FET92 B/'1R/B(-"@.ER97-P;VYS92YW<FET92 B
M/'1D(&-L87-S/2(B;F5W+71D(B(@.=VED=&@.](B(Q,# B(B!B9T-O;&]R/2(B
M(V$W8F(X-B(B(&AE:6=H=#TB(C$P(B(^/$E-1R!H96EG:'0](B(Q(B(@.<W)C
M/2(B25,O<VAI;2YG:68B(B!W:61T:#TB(C$B(CX\+W1D/B(-"@.ER97-P;VYS
M92YW<FET92 B/'1D(&-L87-S/2(B;F5W+71D(B(@.8F=#;VQO<CTB(B,S93<V
M.3$B(B!H96EG:'0](B(Q,"(B/CQ)34<@.:&5I9VAT/2(B,2(B('-R8STB(FEM
M86=E<R]S:&EM+F=I9B(B('=I9'1H/2(B,2(B/CPO=&0^(@.T*"7)E<W!O;G-E
M+G=R:71E("(\=&0@.8VQA<W,](B)N97<M=&0B(B!W:61T:#TB(C(P(B(@.8F=#
M;VQO<CTB(B,W.&(P8V(B(B!H96EG:'0](B(Q,"(B/CQ)34<@.:&5I9VAT/2(B
M,2(B('-R8STB(DE3+W-H:6TN9VEF(B(@.=VED=&@.](B(Q(B(^/"]T9#XB#0H)
M<F5S<&]N<V4N=W)I=&4@.(CQT9"!C;&%S<STB(FYE=RUT9"(B('9!;&EG; CTB
M(G1O<"(B('=I9'1H/2(B-3 P(B(@.8F=#;VQO<CTB(B,W.&(P8V(B(B!H96EG
M:'0](B(Q,"(B/B(-"@.ER97-P;VYS92YW<FET92 B/' @.8VQA<W,](B)P+6QI
M9VAT(B(^*&,I(&-O<'ER:6=H=#QA(&AR968](B)H='1P.B\O=W=W+G-M:6QL
M82YR=2(B/B9N8G-P.VAT=' Z+R]W=W<N<VUI;&QA+G)U/"]A/CPO<#XB#0H)
M<F5S<&]N<V4N=W)I=&4@.(CPO=&0^(@.T*"7)E<W!O;G-E+G=R:71E("(\=&0@.
M8VQA<W,](B)N97<M=&0B(G9!;&EG;CTB(G1O<"(B(&)G0V]L;W(](B(C-6(Y
M,V%E(B(@.:&5I9VAT/2(B,3 B(CX\24U'(&AE:6=H=#TB(C$B(B!S<F,](B))
M4R]S:&EM+F=I9B(B('=I9'1H/2(B,2(B/CPO=&0^(@.T*"7)E<W!O;G-E+G=R
M:71E("(\+W1R/B(-"@.ER97-P;VYS92YW<FET92 B/'1R/B(-"@.ER97-P;VYS
M92YW<FET92 B/'1D('=I9'1H/2(B,3 P(B(@.8F=#;VQO<CTB(B-C8F4U838B
M(B!H96EG:'0](B(R(B(^/$E-1R!H96EG:'0](B(Q(B(@.<W)C/2(B25,O<VAI
M;2YG:68B(B!W:61T:#TB(C$B(CX\+W1D/B(-"@.ER97-P;VYS92YW<FET92 B
M/'1D(&-L87-S/2(B;F5W+71D+79E<G1I8V%L(B(@.8F=#;VQO<CTB(B-A-V)B
M.#8B(B!H96EG:'0](B(R(B(^/$E-1R!H96EG:'0](B(Q(B(@.<W)C/2(B25,O
M<VAI;2YG:68B(B!W:61T:#TB(C$B(CX\+W1D/B(-"@.ER97-P;VYS92YW<FET
M92 B/'1D('=I9'1H/2(B,C B(B!H96EG:'0](B(R(B(^/$E-1R!H96EG:'0]
M(B(Q(B(@.<W)C/2(B25-S:&EM+F=I9B(B('=I9'1H/2(B,2(B/CPO=&0^(@.T*
M"7)E<W!O;G-E+G=R:71E("(\=&0@.=D%L:6=N/2(B=&]P(B(@.=VED=&@.](B(U
M,# B(B!H96EG:'0](B(R(B(^/$E-1R!H96EG:'0](B(Q(B(@.<W)C/2(B25,O
M<VAI;2YG:68B(B!W:61T:#TB(C$B(CX\+W1D/B(-"@.ER97-P;VYS92YW<FET
M92 B/'1D(&-L87-S/2(B;F5W+71D+79E<G1I8V%L(B(@.=D%L:6=N/2(B=&]P
M(B(@.8F=#;VQO<CTB(B-C8F4U838B(B!H96EG:'0](B(R(B(^/$E-1R!H96EG
M:'0](B(Q(B(@.<W)C/2(B25,O<VAI;2YG:68B(B!W:61T:#TB(C$B(CX\+W1D
M/B(-"@.ER97-P;VYS92YW<FET92 B/"]T<CXB#0H)<F5S<&]N<V4N=W)I=&4@.
M(CPO=&%B;&4^(@.T*"7)E<W!O;G-E+G=R:71E("(\+V9O<FT^(@.T*"7)E<W!O
M;G-E+G=R:71E("(\2#$^(@.T*"7)E<W!O;G-E+G=R:71E("(\+T)/1%D^(@.T*
J"7)E<W!O;G-E+G=R:71E("(\+TA434P^(@.T*16YD(%-U8@.T*)3X-"@.T*
`
end
|||Hilary,
As you say Site Server is no longer supported and while there is a newsgroup
for it there has not be a reply from microsoft in a long time as it is an
official obsolete product. It is not appropriate to recommend an obsolete
product and inactive newsgroup for this solution.
However, the link to http://www.indexserverfaq.com/sqlhithighlighting.htm is
a good one, but at this time, Rainbow has not yet replied to my question if
he/she was interested in a Indexing Service solution vs. a SQL Server FTS
solution, jumping to conclusions and making assumptions only confuse these
issues and sometimes it is best to wait before replying and making the wrong
assumption.
Best regards,
John
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:uIXdK4NvEHA.4028@.TK2MSFTNGP15.phx.gbl...
> Your question pertains to Site Server which is no longer supported by
> Microsoft. There is still a site server newsgroup
> microsoft.public.siteserver.search
> To answer your question I suggest you follow this link
> http://www.indexserverfaq.com/sqlhithighlighting.htm
> Here is my schema
> CREATE TABLE [dbo].[Parent] (
> [pk] [int] IDENTITY (1, 1) NOT NULL ,
> [Title] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Author] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [vpath] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [characterization] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [Size] [int] NULL ,
> [CreateDate] [datetime] NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[TextTable] (
> [pk] [int] IDENTITY (1, 1) NOT NULL ,
> [textcol] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
> GO
> Here is my query
> CREATE PROC search @.search nvarchar(20), @.@.strSearch nvarchar(500)
> AS
> SET @.@.strSearch='SELECT Title, author, vpath, characterization, size,
> createDate from parent as P,_ CONTAINSTABLE(TextTable, TextCol,
> '+char(39)+char(34)
> SELECT @.@.strSearch=@.@.strSearch + @.search +char(34)+char(39)
> SELECT @.@.strSearch=@.@.strSearch+ ', 100) AS FTTABLE WHERE P.pk=
> FTTABLE.[key]'
> SELECT @.@.strSearch= @.@.strSearch + ' AND size > 10000 ORDER BY FTTABLE.rank
> desc,[KEY] '
> exec (@.@.strSearch)
>
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> "rainbow" <anonymous@.discussions.microsoft.com> wrote in message
> news:1a9601c4bc9d$09f53a10$a501280a@.phx.gbl...
>
>
|||Hilary, a brief follow-up, while we wait for Rainbow's reply...
I was incorrect in stating that you "recommend[ed] an obsolete product",
when in fact, you were just stating that Rainbow's questions "pertain[ed] to
Site Server". I apologize for that.
However, it is still always a good idea to confirm these assumptions, with a
simple question or two as I did in my initial reply. I can also understand
how you made this assumption as references to MSSearch.Query in combination
with docname & doctype, are used by Site Server. Rainbow, could you confirm
that you are indeed using Site Server and not SQL Server 2000 Full-Text
Search or the Windows Indexing Service?
Regards,
John
"John Kane" <jt-kane@.comcast.net> wrote in message
news:O#z#tYRvEHA.3424@.TK2MSFTNGP09.phx.gbl...
> Hilary,
> As you say Site Server is no longer supported and while there is a
newsgroup
> for it there has not be a reply from microsoft in a long time as it is an
> official obsolete product. It is not appropriate to recommend an obsolete
> product and inactive newsgroup for this solution.
> However, the link to http://www.indexserverfaq.com/sqlhithighlighting.htm
is
> a good one, but at this time, Rainbow has not yet replied to my question
if
> he/she was interested in a Indexing Service solution vs. a SQL Server FTS
> solution, jumping to conclusions and making assumptions only confuse these
> issues and sometimes it is best to wait before replying and making the
wrong[vbcol=seagreen]
> assumption.
> Best regards,
> John
>
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:uIXdK4NvEHA.4028@.TK2MSFTNGP15.phx.gbl...
FTTABLE.rank
>
|||Thanks all ~
I use SQL Server FTS solution(not Index Server) , and i
wanna get the searching results like google . BTW, the
data field type is IMAGE not TEXT , waybe it's difficulty
or impossible to implement , if it can , what shall i
do !
Best regards
>--Original Message--
>Hilary,
>As you say Site Server is no longer supported and while
there is a newsgroup
>for it there has not be a reply from microsoft in a long
time as it is an
>official obsolete product. It is not appropriate to
recommend an obsolete
>product and inactive newsgroup for this solution.
>However, the link to
http://www.indexserverfaq.com/sqlhithighlighting.htm is
>a good one, but at this time, Rainbow has not yet replied
to my question if
>he/she was interested in a Indexing Service solution vs.
a SQL Server FTS
>solution, jumping to conclusions and making assumptions
only confuse these
>issues and sometimes it is best to wait before replying
and making the wrong[vbcol=seagreen]
>assumption.
>Best regards,
>John
>
>"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
>news:uIXdK4NvEHA.4028@.TK2MSFTNGP15.phx.gbl...
longer supported by[vbcol=seagreen]
SQL_Latin1_General_CP1_CI_AS NULL ,[vbcol=seagreen]
SQL_Latin1_General_CP1_CI_AS NULL ,[vbcol=seagreen]
SQL_Latin1_General_CP1_CI_AS NULL ,[vbcol=seagreen]
SQL_Latin1_General_CP1_CI_AS[vbcol=seagreen]
NULL[vbcol=seagreen]
nvarchar(500)[vbcol=seagreen]
characterization, size,[vbcol=seagreen]
TextCol,[vbcol=seagreen]
(39)[vbcol=seagreen]
WHERE P.pk=[vbcol=seagreen]
ORDER BY FTTABLE.rank[vbcol=seagreen]
in message[vbcol=seagreen]
the
>
>.
>
|||Thank you for confirming that you are using SQL FTS which is why you posted
this in the SQL FTS newsgroup, and that you want the hit highlighting
features of Google.
Currently there is no way to do inline hit highlighting like what Google
does. The solution I have presented is the closest I know, and it involves
clicking on hyperlinks.
Don't confuse the MSSearch.Query com object of Site Server, with SQL FTS.
Although MSSearch is an "early" version of Site Server Search/Babylon, they
are quite different. It is quite coincidental that the names are the same.
The solution I have presented does allow hit highlighting through
hyperlinks, even if the content is stored in the Image datatype. Just make
sure when you extract it into the file system you extract it in its native
format and with the correct extension, ie if it is a Word doc, extract it
with the .Doc extension.
"rainbow" <anonymous@.discussions.microsoft.com> wrote in message
news:021601c4bd5d$f6129e40$a501280a@.phx.gbl...[vbcol=seagreen]
> Thanks all ~
> I use SQL Server FTS solution(not Index Server) , and i
> wanna get the searching results like google . BTW, the
> data field type is IMAGE not TEXT , waybe it's difficulty
> or impossible to implement , if it can , what shall i
> do !
> Best regards
> there is a newsgroup
> time as it is an
> recommend an obsolete
> http://www.indexserverfaq.com/sqlhithighlighting.htm is
> to my question if
> a SQL Server FTS
> only confuse these
> and making the wrong
> longer supported by
> SQL_Latin1_General_CP1_CI_AS NULL ,
> SQL_Latin1_General_CP1_CI_AS NULL ,
> SQL_Latin1_General_CP1_CI_AS NULL ,
> SQL_Latin1_General_CP1_CI_AS
> NULL
> nvarchar(500)
> characterization, size,
> TextCol,
> (39)
> WHERE P.pk=
> ORDER BY FTTABLE.rank
> in message
> the
|||Thanks,
So i will add one descriptions field to the documents
table, show the description field instead of the text near
the searching words in the searching result .
This can also help the readers the decide which doc is the
best matched .
any new feathers of SQL Server 2005 full-text search ?
thanks .
>--Original Message--
>Thank you for confirming that you are using SQL FTS which
is why you posted
>this in the SQL FTS newsgroup, and that you want the hit
highlighting
>features of Google.
>Currently there is no way to do inline hit highlighting
like what Google
>does. The solution I have presented is the closest I
know, and it involves
>clicking on hyperlinks.
>Don't confuse the MSSearch.Query com object of Site
Server, with SQL FTS.
>Although MSSearch is an "early" version of Site Server
Search/Babylon, they
>are quite different. It is quite coincidental that the
names are the same.
>The solution I have presented does allow hit highlighting
through
>hyperlinks, even if the content is stored in the Image
datatype. Just make
>sure when you extract it into the file system you extract
it in its native
>format and with the correct extension, ie if it is a Word
doc, extract it
>with the .Doc extension.
>
Friday, February 24, 2012
Get Record Number
I have a table containing 1000 records. I do have a primary key associated with the table. But I want to get the Record Number of each record. Is there a way to get it. That means for first record it should be 1 and so on and so forth.
That depends on wheter you are using SQL Server 2005 or any version below. SQL 2k5 introduced the ROWNUMBER() function which will let your create a rownumber according to some rules (only a new number if a grouping changes, etc.) if you are using SQL2k or bwlo you will have to go another way. What are you currently using ?
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
|||Currently using SQL-2000|||There is no straight-forward solution in SQL 2000.
Basically, you can create a temp table with an identity column (record number) and insert each row of your table into it. After that, select all the rows in the temp table out with the record number.
You can also only insert the PK column into the temp table and select using a join of the temp table and the original table.
|||I tried to get thru this problem and found out a way to do it. the query specified below can solve my purpose:
SELECT emp_id, lname, fname, job_id, (SELECT COUNT(*) FROM employee e2 WHERE e2.lname <= e.lname) AS rownumber
FROM employee e
ORDER BY lname
But again there is a catch. This particular query will work only in case I have a unique field (like lname in this case). Now I again to resolve this thing by assigning a new unique ID to each row with the use of function NEWID(). But when I am trying to use NEWID() instead of lname it is not working. I tried as
select newid() NN, e.*, (select count(*) from (select newid() NID,* from employee) e2 where e2.NID <= e.NN ) as rownumber
from employee e
it gives me an error "Invalid column name 'NN' " Can anyone help me out in sorting this error and using the NEWID() as a unique field. I do not wish to use temp table.
|||I tried to get thru this problem and found out a way to do it. the query specified below can solve my purpose:
SELECT emp_id, lname, fname, job_id, (SELECT COUNT(*) FROM employee e2 WHERE e2.lname <= e.lname) AS rownumber
FROM employee e
ORDER BY lname
But again there is a catch. This particular query will work only in case I have a unique field (like lname in this case). Now I again to resolve this thing by assigning a new unique ID to each row with the use of function NEWID(). But when I am trying to use NEWID() instead of lname it is not working. I tried as
select newid() NN, e.*, (select count(*) from (select newid() NID,* from employee) e2 where e2.NID <= e.NN ) as rownumber
from employee e
it gives me an error "Invalid column name 'NN' " Can anyone help me out in sorting this error and using the NEWID() as a unique field. I do not wish to use temp table.
get primary key of last inserted record
Ok I know this might not be the most accurate place to post this but I know someone here has an answer for me on it.
I need to get the product_ID of the new record that is created by this insert statement
INSERT
INTO products
( class_ID,category_ID,product_name,product_desc,product_image,product_dimension,product_o1,product_o2,product_o3,product_ac,product_ph,product_photo )
SELECT class_ID,category_ID,product_name,product_desc,product_image,product_dimension,product_o1,product_o2,product_o3,product_ac,product_ph,product_photo
FROM products
WHERE product_ID = @.productID
The answer you seek is here:
http://www.mikesdotnetting.com/Article.aspx?ArticleID=54
Get Primary Key from another Table
Hello everyone,
I've just tried realizing following project:
Currently i have separate Tables with separate Primary Keys (ascending separate Number regions).
Now I want to do the following:
Table - NewID
Field - ID - Identity
Field - IDproc - Sub-Data Number
Table - Data1 (IDProc=1)
Field - ID - Primary Key, generated NEW from NEWiD
Now great would be if I could add a new Recordset in Data1 and I could generate a New ID in 'NewID' trough a Trigger and Set it on the inserted Data, but that doesn't seem possible.
I was able to do it with the following trigger:
CREATE TRIGGER [getNewID] ON [dbo].[Data1]
FOR INSERT
AS
DECLARE @.newID int
INSERT INTO NewID (IDProc) VALUES(1)
SELECT @.newID=(SELECT TOP 1 id FROM NewID WHERE IDProc=1 ORDER BY ID DESC)
UPDATE Data1 SET ID=@.newID WHERE ID IS NULL
But as I can imagine this would get some problems if two users insert a Recordset at the same Time - right?
:mad:
Is there somehow the possiblity to 'SET INSERTED.ID = @.NewID' ??, because it's the only real ID I have in this table (beneath a TimeStamp wich I also couldn't use).
Thanks for any Help!!!First off, if you are using the NewID() function, you will be generating Globally Unique Identifiers, not sequential numbers, so your ORDER BY ID DESC logic won't work.
Are you trying to get the most recent ID inserted? Try creating your ID as a variable within the trigger and then inserting it into both your tables. The NewID() function works well for this.
blindman|||Originally posted by blindman
First off, if you are using the NewID() function, you will be generating Globally Unique Identifiers, not sequential numbers, so your ORDER BY ID DESC logic won't work.
Are you trying to get the most recent ID inserted? Try creating your ID as a variable within the trigger and then inserting it into both your tables. The NewID() function works well for this.
blindman
Hi Blindman
NewID was just a variable for my thoughts, i don't use the function NewID - this is just the tablename and var-name of the trigger...
The idea is to have a main table with all the ID's and sub-data information so I can have a table Data1 Data2 and Data3 wich dont get themselves into the way with their ID's.|||If you are trying to keep IDs from being duplicated within or between data tables, then you should consider using the uniqueidentifier datatype and the NewID function. Its easy to use and will always give you a unique ID. I think it might solve your problem.
blindman|||Originally posted by blindman
If you are trying to keep IDs from being duplicated within or between data tables, then you should consider using the uniqueidentifier datatype and the NewID function. Its easy to use and will always give you a unique ID. I think it might solve your problem.
blindman
Wow, i've just took a look at this NewID function, that's a pretty big thing :-).
I would be pretty happy if i could handle the ID with numbers, not that a customer has to spell me those 20 chars :), do you know any way I could do this trough triggers?
Or is there a possibility to convert the NewID-var to some kind of number?|||NewID produces a standard UniqueIdentifier data type which can be implicitily converted to a char(36) or nchar(36) data type. It's good practice to make your IDs invisible to Users anyway and keep them for internal data processing.
blindman
I've just tried realizing following project:
Currently i have separate Tables with separate Primary Keys (ascending separate Number regions).
Now I want to do the following:
Table - NewID
Field - ID - Identity
Field - IDproc - Sub-Data Number
Table - Data1 (IDProc=1)
Field - ID - Primary Key, generated NEW from NEWiD
Now great would be if I could add a new Recordset in Data1 and I could generate a New ID in 'NewID' trough a Trigger and Set it on the inserted Data, but that doesn't seem possible.
I was able to do it with the following trigger:
CREATE TRIGGER [getNewID] ON [dbo].[Data1]
FOR INSERT
AS
DECLARE @.newID int
INSERT INTO NewID (IDProc) VALUES(1)
SELECT @.newID=(SELECT TOP 1 id FROM NewID WHERE IDProc=1 ORDER BY ID DESC)
UPDATE Data1 SET ID=@.newID WHERE ID IS NULL
But as I can imagine this would get some problems if two users insert a Recordset at the same Time - right?
:mad:
Is there somehow the possiblity to 'SET INSERTED.ID = @.NewID' ??, because it's the only real ID I have in this table (beneath a TimeStamp wich I also couldn't use).
Thanks for any Help!!!First off, if you are using the NewID() function, you will be generating Globally Unique Identifiers, not sequential numbers, so your ORDER BY ID DESC logic won't work.
Are you trying to get the most recent ID inserted? Try creating your ID as a variable within the trigger and then inserting it into both your tables. The NewID() function works well for this.
blindman|||Originally posted by blindman
First off, if you are using the NewID() function, you will be generating Globally Unique Identifiers, not sequential numbers, so your ORDER BY ID DESC logic won't work.
Are you trying to get the most recent ID inserted? Try creating your ID as a variable within the trigger and then inserting it into both your tables. The NewID() function works well for this.
blindman
Hi Blindman
NewID was just a variable for my thoughts, i don't use the function NewID - this is just the tablename and var-name of the trigger...
The idea is to have a main table with all the ID's and sub-data information so I can have a table Data1 Data2 and Data3 wich dont get themselves into the way with their ID's.|||If you are trying to keep IDs from being duplicated within or between data tables, then you should consider using the uniqueidentifier datatype and the NewID function. Its easy to use and will always give you a unique ID. I think it might solve your problem.
blindman|||Originally posted by blindman
If you are trying to keep IDs from being duplicated within or between data tables, then you should consider using the uniqueidentifier datatype and the NewID function. Its easy to use and will always give you a unique ID. I think it might solve your problem.
blindman
Wow, i've just took a look at this NewID function, that's a pretty big thing :-).
I would be pretty happy if i could handle the ID with numbers, not that a customer has to spell me those 20 chars :), do you know any way I could do this trough triggers?
Or is there a possibility to convert the NewID-var to some kind of number?|||NewID produces a standard UniqueIdentifier data type which can be implicitily converted to a char(36) or nchar(36) data type. It's good practice to make your IDs invisible to Users anyway and keep them for internal data processing.
blindman
Get Primary Key Column
Hello, this SQL query gets all columns of a table:
SELECT syscolumns.name AS [Fields in Items Database], syscolumns.type,syscolumns.length, syscolumns.isnullable FROM sysobjects INNER JOINsyscolumns ON sysobjects.id = syscolumns.id WHERE sysobjects.name ='IssueTracker_IssueAttachments' ORDER BY syscolumns.colid
I would like to know, is there a way to get a column saying who is theprimary key in the table ? I am reading columns for tables andprocessing them, but I would like to know who is the primary key, isthat possible ?
regards
Your query is running in the Master Database, you need to run it in the Issue Tracker database and to see the primary key of a table go to Query Analyzer and run a Select all query with show results as a grid. But most large databases uses Identity column for keys so the clustered index will be small. Hope this helps.|||
You can check a script listing Primary Key columns of a table by using this linkhttp://www.kodyaz.com/ShowPost.aspx?PostID=204
A simplified version is as below
declare @.tablename as sysname
set @.tablename = 'Customers'
declare @.tableid as int
select @.tableid = id from sysobjects where name = @.tablename
SELECT *
FROM syscolumns
INNER JOIN (
SELECT
*
FROM SysIndexKeys IK
WHERE
IK.Id = (select id from sysobjects where name = @.tablename)
AND IK.IndId = (select indid fromsysindexes where name = (select name from sysobjects where xtype = 'PK'and parent_obj = (select id from sysobjects where name = @.tablename)))
) PKColumns ON PKColumns.id = syscolumns.id AND PKColumns.colid = syscolumns.colid
I hope this helps
Eralper
http://www.kodyaz.com
|||Guys that was great, thanks a lot
I never used those system tables, are there any reference for them ? Are the SQL Server books good ?
thanks|||
Not exactly. the system tables are not removed. They are not tables anymore. They are just made as VIEWS now. So you can only do selectagainst them.
|||I did not say there are System tables in SQL Server 2005, I said the SQL Server 2000 poster was removed from the online location because SQL Server 2005 is almost here. I have known they are now views for a long time and I have always advised people not to use the System tables.
SELECT syscolumns.name AS [Fields in Items Database], syscolumns.type,syscolumns.length, syscolumns.isnullable FROM sysobjects INNER JOINsyscolumns ON sysobjects.id = syscolumns.id WHERE sysobjects.name ='IssueTracker_IssueAttachments' ORDER BY syscolumns.colid
I would like to know, is there a way to get a column saying who is theprimary key in the table ? I am reading columns for tables andprocessing them, but I would like to know who is the primary key, isthat possible ?
regards
Your query is running in the Master Database, you need to run it in the Issue Tracker database and to see the primary key of a table go to Query Analyzer and run a Select all query with show results as a grid. But most large databases uses Identity column for keys so the clustered index will be small. Hope this helps.|||
Well no, I am running this script against the IssueTrackerStarterKit database. I am getting that table for sure, listing al columns, but I would like to know if I can know the primary key programmatically !!
regards
|||Run a search for sp_helpindex in SQL Server BOL(books online). And your select statement is using SQL- DMO(data management object) which is Microsoft property and all service packs makes changes which will make you code out dated because the tables have moved. Go to Query Analyzer open the object browser and right click on your table and you will have options of select statements without Syscolumns and Sysobjects. Hope this helps.|||Hi,You can check a script listing Primary Key columns of a table by using this linkhttp://www.kodyaz.com/ShowPost.aspx?PostID=204
A simplified version is as below
declare @.tablename as sysname
set @.tablename = 'Customers'
declare @.tableid as int
select @.tableid = id from sysobjects where name = @.tablename
SELECT *
FROM syscolumns
INNER JOIN (
SELECT
*
FROM SysIndexKeys IK
WHERE
IK.Id = (select id from sysobjects where name = @.tablename)
AND IK.IndId = (select indid fromsysindexes where name = (select name from sysobjects where xtype = 'PK'and parent_obj = (select id from sysobjects where name = @.tablename)))
) PKColumns ON PKColumns.id = syscolumns.id AND PKColumns.colid = syscolumns.colid
I hope this helps
Eralper
http://www.kodyaz.com
|||Guys that was great, thanks a lot
I never used those system tables, are there any reference for them ? Are the SQL Server books good ?
thanks|||
The Systems tables have a poster but since SQL Server 2005 is almost here Microsoft have removed it and have made others to remove it. But they are all in the SQL Server Master database with the System title next to them. A good book for starting SQL Server for a developer is SQL Server a beginner's guide by Dusan Petkovic but don't let the title fool you it is not really a beginner's book it was given that title because English is not the writer's first language, he is German. The book covers everything usefull to a developer including complex configurations and the language, he also covered XML and Full text that most other books did not cover. I have a lot of them I call crappy but his book is worth the money for a C# developer, while SQL Server Developer's guide is good for VB developer. Try this link for T-SQL tutorial but I have sent you an ANSI SQL tutorial in the mail. Dowload the file because the site is now part of a consolidator so that file may be removed soon. Hope this helps.
http://www.mssqlserver.com/tsql/
|||
The Systems tables have a poster but since SQL Server 2005 is almosthere Microsoft have removed it and have made others to remove it. Butthey are all in the SQL Server Master database with the System titlenext to them.
Not exactly. the system tables are not removed. They are not tables anymore. They are just made as VIEWS now. So you can only do selectagainst them.
|||I did not say there are System tables in SQL Server 2005, I said the SQL Server 2000 poster was removed from the online location because SQL Server 2005 is almost here. I have known they are now views for a long time and I have always advised people not to use the System tables.
Get PK for inserted record in SQLdatasource
I have a table named invoice that contains the following columns
-invoiceno - Primary key and is set to autonumber
-customerno
-incoicedate
and on my VB code i did the following InsertCommand
SqlDataSource1.InsertCommand = "INSERT INTO invoice(customerno, invoicedate) VALUES('" & Session("UID") & "', GetDate()) "
SqlDataSource1.Insert()
My Question is how do i get the Primary Key Value it generated during the insert operation(invoice['incoiceno'])? Besides the creationg a stored procedere like the one in the MSDN Library
Well, since you really aren't using the sqldatasource as a datasource, just do it manually.
Dim conn as new sqlconnection("{Your connect string}")
conn.open
dim cmd as new sqlcommand("INSERT INTO invoice(customerno, invoicedate) VALUES(@.customerno,getdate()) SELECT @.newid=SCOPE_IDENTITY()",conn)
cmd.parameters.add("@.customerno",sqldbtype.uniqueidentifier).value=session("UID")
cmd.parameters.add("@.newid",sqldbtype.int).direction=output
cmd.executenonquery()
dim newid as integer=cmd.parameters("@.newid").value
conn.close
I believe you can also try to create a parameter on the insert with a direction of output as well, but I think you have to actually pull the value in sqldatasource1_inserted by referencing e.command.parameters("@.newid").value, but you can try and see if you can pull it directly after your insert too.
Subscribe to:
Posts (Atom)