Problem with Linked Server and Text fields

Hi all:
I have following problem.
When I try to run an INSERT from Query Analyser to a table of a linked
server I get
a bug about a TEXT field of table:
INSERT is like this one:
INSERT INTO [SERVIDOR].basedatos.dbo.fmafam (empresa,familia,descripcion)
values ('090','Fam1','Familia 1')
Bug is like this one:
The provider OLE DB 'SQLOLEDB' can`t INSERT INTO table
'[SERVIDOR].[basedatos].[dbo].[fmafam]', by column 'observaciones'.
OLE DB [OLE/DB Provider 'SQLOLEDB'
IRowsetChange::InsertRow returned 0x80040e21: Data status sent to the
provider: [COLUMN_NAME=empresa STATUS=DBSTATUS_S_OK], [COLUMN_NAME=familia
STATUS=DBSTATUS_S_OK], [COLUMN_NAME=descripcion STATUS=DBSTATUS_S_DEFAULT],
[COLUMN_NAME=observaciones STATUS=DBSTATUS_S_ISNULL],...
Provider OLE DB 'SQLOLEDB' can't INSERT INTO table
[SERVIDOR].[basedatos].[dbo].[fmafam], by column 'observaciones'.
INSERT hasn't field "Observaciones" (TEXT type) because it has a default
value.
Running same instruction locally there isn't any problem.
How can I solve this problem ?
Luis,
I have reproduced this error. I have never seen this before,
and I'll ask for some help and get back to you.
Steve Kass
Drew University
Luis Soler wrote:

>Hi all:
>I have following problem.
>When I try to run an INSERT from Query Analyser to a table of a linked
>server I get
>a bug about a TEXT field of table:
>INSERT is like this one:
>INSERT INTO [SERVIDOR].basedatos.dbo.fmafam (empresa,familia,descripcion)
> values ('090','Fam1','Familia 1')
>Bug is like this one:
>The provider OLE DB 'SQLOLEDB' can`t INSERT INTO table
>'[SERVIDOR].[basedatos].[dbo].[fmafam]', by column 'observaciones'.
>OLE DB [OLE/DB Provider 'SQLOLEDB'
>IRowsetChange::InsertRow returned 0x80040e21: Data status sent to the
>provider: [COLUMN_NAME=empresa STATUS=DBSTATUS_S_OK], [COLUMN_NAME=familia
>STATUS=DBSTATUS_S_OK], [COLUMN_NAME=descripcion STATUS=DBSTATUS_S_DEFAULT],
>[COLUMN_NAME=observaciones STATUS=DBSTATUS_S_ISNULL],...
>Provider OLE DB 'SQLOLEDB' can't INSERT INTO table
>[SERVIDOR].[basedatos].[dbo].[fmafam], by column 'observaciones'.
>INSERT hasn't field "Observaciones" (TEXT type) because it has a default
>value.
>Running same instruction locally there isn't any problem.
>How can I solve this problem ?
>
>
>
|||Hi Steve:
Table has one TEXT type field and a default value of ''.
I get error only in TEXT fields.
Boths servers have SQL Server 2000 with SP3.
Do you need anything more ?
"Steve Kass" <skass@.drew.edu> escribi en el mensaje
news:uvDbD8DFFHA.2156@.TK2MSFTNGP10.phx.gbl...[vbcol=seagreen]
> Luis,
> I have reproduced this error. I have never seen this before,
> and I'll ask for some help and get back to you.
> Steve Kass
> Drew University
> Luis Soler wrote:
|||Luis Soler (none@.none) writes:
> When I try to run an INSERT from Query Analyser to a table of a linked
> server I get
> a bug about a TEXT field of table:
> INSERT is like this one:
> INSERT INTO [SERVIDOR].basedatos.dbo.fmafam (empresa,familia,descripcion)
> values ('090','Fam1','Familia 1')
> Bug is like this one:
> The provider OLE DB 'SQLOLEDB' can`t INSERT INTO table
> '[SERVIDOR].[basedatos].[dbo].[fmafam]', by column 'observaciones'.
> OLE DB [OLE/DB Provider 'SQLOLEDB'
> IRowsetChange::InsertRow returned 0x80040e21: Data status sent to the
> provider: [COLUMN_NAME=empresa STATUS=DBSTATUS_S_OK], [COLUMN_NAME=familia
> STATUS=DBSTATUS_S_OK], [COLUMN_NAME=descripcion
> STATUS=DBSTATUS_S_DEFAULT],
> [COLUMN_NAME=observaciones STATUS=DBSTATUS_S_ISNULL],...
> Provider OLE DB 'SQLOLEDB' can't INSERT INTO table
> [SERVIDOR].[basedatos].[dbo].[fmafam], by column 'observaciones'.
> INSERT hasn't field "Observaciones" (TEXT type) because it has a default
> value.
> Running same instruction locally there isn't any problem.
> How can I solve this problem ?
You will have to find a workaround of some sort. It appears that the
SQLOLEDB provider does not support default values with text columns.
Or maybe I should say do not expect them to have a default value. So
when the the data is entered into the rowset, SQLOLEDB things have
gone sour.
I also tested this on the latest (semi-)public build of SQL 2005, and
when the local server is SQL 2005, the INSERT works. This indicates
that the problem is fixed in SQL Native Client, an SQL Server OLE DB
provider that comes with SQL 2005.
What the best workaround is for you depends on your application, but
presumably the easiest way out is to make the column nullable.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
|||OK, already I have thinking about the option to make the field nullable
Thanks Erland
"Erland Sommarskog" <esquel@.sommarskog.se> escribi en el mensaje
news:Xns9600E1BB44115Yazorman@.127.0.0.1...
> Luis Soler (none@.none) writes:
> You will have to find a workaround of some sort. It appears that the
> SQLOLEDB provider does not support default values with text columns.
> Or maybe I should say do not expect them to have a default value. So
> when the the data is entered into the rowset, SQLOLEDB things have
> gone sour.
> I also tested this on the latest (semi-)public build of SQL 2005, and
> when the local server is SQL 2005, the INSERT works. This indicates
> that the problem is fixed in SQL Native Client, an SQL Server OLE DB
> provider that comes with SQL 2005.
> What the best workaround is for you depends on your application, but
> presumably the easiest way out is to make the column nullable.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techinf...2000/books.asp

Problem with Linked Server and Text fields

Hi all:
I have following problem.
When I try to run an INSERT from Query Analyser to a table of a linked
server I get
a bug about a TEXT field of table:
INSERT is like this one:
INSERT INTO [SERVIDOR].basedatos.dbo.fmafam (empresa,familia,descripcion)
values ('090','Fam1','Familia 1')
Bug is like this one:
The provider OLE DB 'SQLOLEDB' can`t INSERT INTO table
'[SERVIDOR].[basedatos].[dbo].[fmafam]', by column 'observaciones'.
OLE DB [OLE/DB Provider 'SQLOLEDB'
IRowsetChange::InsertRow returned 0x80040e21: Data status sent to the
provider: [COLUMN_NAME=empresa STATUS=DBSTATUS_S_OK], [COLUMN_NAME=familia
STATUS=DBSTATUS_S_OK], [COLUMN_NAME=descripcion STATUS=DBSTATUS_S_DEFAULT],
[COLUMN_NAME=observaciones STATUS=DBSTATUS_S_ISNULL],...
Provider OLE DB 'SQLOLEDB' can't INSERT INTO table
[SERVIDOR].[basedatos].[dbo].[fmafam], by column 'observaciones'.
INSERT hasn't field "Observaciones" (TEXT type) because it has a default
value.
Running same instruction locally there isn't any problem.
How can I solve this problem ?Luis,
I have reproduced this error. I have never seen this before,
and I'll ask for some help and get back to you.
Steve Kass
Drew University
Luis Soler wrote:
>Hi all:
>I have following problem.
>When I try to run an INSERT from Query Analyser to a table of a linked
>server I get
>a bug about a TEXT field of table:
>INSERT is like this one:
>INSERT INTO [SERVIDOR].basedatos.dbo.fmafam (empresa,familia,descripcion)
> values ('090','Fam1','Familia 1')
>Bug is like this one:
>The provider OLE DB 'SQLOLEDB' can`t INSERT INTO table
>'[SERVIDOR].[basedatos].[dbo].[fmafam]', by column 'observaciones'.
>OLE DB [OLE/DB Provider 'SQLOLEDB'
>IRowsetChange::InsertRow returned 0x80040e21: Data status sent to the
>provider: [COLUMN_NAME=empresa STATUS=DBSTATUS_S_OK], [COLUMN_NAME=familia
>STATUS=DBSTATUS_S_OK], [COLUMN_NAME=descripcion STATUS=DBSTATUS_S_DEFAULT],
>[COLUMN_NAME=observaciones STATUS=DBSTATUS_S_ISNULL],...
>Provider OLE DB 'SQLOLEDB' can't INSERT INTO table
>[SERVIDOR].[basedatos].[dbo].[fmafam], by column 'observaciones'.
>INSERT hasn't field "Observaciones" (TEXT type) because it has a default
>value.
>Running same instruction locally there isn't any problem.
>How can I solve this problem ?
>
>
>|||Hi Steve:
Table has one TEXT type field and a default value of ''.
I get error only in TEXT fields.
Boths servers have SQL Server 2000 with SP3.
Do you need anything more ?
"Steve Kass" <skass@.drew.edu> escribió en el mensaje
news:uvDbD8DFFHA.2156@.TK2MSFTNGP10.phx.gbl...
> Luis,
> I have reproduced this error. I have never seen this before,
> and I'll ask for some help and get back to you.
> Steve Kass
> Drew University
> Luis Soler wrote:
>>Hi all:
>>I have following problem.
>>When I try to run an INSERT from Query Analyser to a table of a linked
>>server I get
>>a bug about a TEXT field of table:
>>INSERT is like this one:
>>INSERT INTO [SERVIDOR].basedatos.dbo.fmafam (empresa,familia,descripcion)
>> values ('090','Fam1','Familia 1')
>>Bug is like this one:
>>The provider OLE DB 'SQLOLEDB' can`t INSERT INTO table
>>'[SERVIDOR].[basedatos].[dbo].[fmafam]', by column 'observaciones'.
>>OLE DB [OLE/DB Provider 'SQLOLEDB'
>>IRowsetChange::InsertRow returned 0x80040e21: Data status sent to the
>>provider: [COLUMN_NAME=empresa STATUS=DBSTATUS_S_OK], [COLUMN_NAME=familia
>>STATUS=DBSTATUS_S_OK], [COLUMN_NAME=descripcion
>>STATUS=DBSTATUS_S_DEFAULT],
>>[COLUMN_NAME=observaciones STATUS=DBSTATUS_S_ISNULL],...
>>Provider OLE DB 'SQLOLEDB' can't INSERT INTO table
>>[SERVIDOR].[basedatos].[dbo].[fmafam], by column 'observaciones'.
>>INSERT hasn't field "Observaciones" (TEXT type) because it has a default
>>value.
>>Running same instruction locally there isn't any problem.
>>How can I solve this problem ?
>>
>>|||Luis Soler (none@.none) writes:
> When I try to run an INSERT from Query Analyser to a table of a linked
> server I get
> a bug about a TEXT field of table:
> INSERT is like this one:
> INSERT INTO [SERVIDOR].basedatos.dbo.fmafam (empresa,familia,descripcion)
> values ('090','Fam1','Familia 1')
> Bug is like this one:
> The provider OLE DB 'SQLOLEDB' can`t INSERT INTO table
> '[SERVIDOR].[basedatos].[dbo].[fmafam]', by column 'observaciones'.
> OLE DB [OLE/DB Provider 'SQLOLEDB'
> IRowsetChange::InsertRow returned 0x80040e21: Data status sent to the
> provider: [COLUMN_NAME=empresa STATUS=DBSTATUS_S_OK], [COLUMN_NAME=familia
> STATUS=DBSTATUS_S_OK], [COLUMN_NAME=descripcion
> STATUS=DBSTATUS_S_DEFAULT],
> [COLUMN_NAME=observaciones STATUS=DBSTATUS_S_ISNULL],...
> Provider OLE DB 'SQLOLEDB' can't INSERT INTO table
> [SERVIDOR].[basedatos].[dbo].[fmafam], by column 'observaciones'.
> INSERT hasn't field "Observaciones" (TEXT type) because it has a default
> value.
> Running same instruction locally there isn't any problem.
> How can I solve this problem ?
You will have to find a workaround of some sort. It appears that the
SQLOLEDB provider does not support default values with text columns.
Or maybe I should say do not expect them to have a default value. So
when the the data is entered into the rowset, SQLOLEDB things have
gone sour.
I also tested this on the latest (semi-)public build of SQL 2005, and
when the local server is SQL 2005, the INSERT works. This indicates
that the problem is fixed in SQL Native Client, an SQL Server OLE DB
provider that comes with SQL 2005.
What the best workaround is for you depends on your application, but
presumably the easiest way out is to make the column nullable.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp|||OK, already I have thinking about the option to make the field nullable
Thanks Erland
"Erland Sommarskog" <esquel@.sommarskog.se> escribió en el mensaje
news:Xns9600E1BB44115Yazorman@.127.0.0.1...
> Luis Soler (none@.none) writes:
>> When I try to run an INSERT from Query Analyser to a table of a linked
>> server I get
>> a bug about a TEXT field of table:
>> INSERT is like this one:
>> INSERT INTO [SERVIDOR].basedatos.dbo.fmafam (empresa,familia,descripcion)
>> values ('090','Fam1','Familia 1')
>> Bug is like this one:
>> The provider OLE DB 'SQLOLEDB' can`t INSERT INTO table
>> '[SERVIDOR].[basedatos].[dbo].[fmafam]', by column 'observaciones'.
>> OLE DB [OLE/DB Provider 'SQLOLEDB'
>> IRowsetChange::InsertRow returned 0x80040e21: Data status sent to the
>> provider: [COLUMN_NAME=empresa STATUS=DBSTATUS_S_OK],
>> [COLUMN_NAME=familia
>> STATUS=DBSTATUS_S_OK], [COLUMN_NAME=descripcion
>> STATUS=DBSTATUS_S_DEFAULT],
>> [COLUMN_NAME=observaciones STATUS=DBSTATUS_S_ISNULL],...
>> Provider OLE DB 'SQLOLEDB' can't INSERT INTO table
>> [SERVIDOR].[basedatos].[dbo].[fmafam], by column 'observaciones'.
>> INSERT hasn't field "Observaciones" (TEXT type) because it has a default
>> value.
>> Running same instruction locally there isn't any problem.
>> How can I solve this problem ?
> You will have to find a workaround of some sort. It appears that the
> SQLOLEDB provider does not support default values with text columns.
> Or maybe I should say do not expect them to have a default value. So
> when the the data is entered into the rowset, SQLOLEDB things have
> gone sour.
> I also tested this on the latest (semi-)public build of SQL 2005, and
> when the local server is SQL 2005, the INSERT works. This indicates
> that the problem is fixed in SQL Native Client, an SQL Server OLE DB
> provider that comes with SQL 2005.
> What the best workaround is for you depends on your application, but
> presumably the easiest way out is to make the column nullable.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

Problem with Linked Server and Text fields

Hi all:
I have following problem.
When I try to run an INSERT from Query Analyser to a table of a linked
server I get
a bug about a TEXT field of table:
INSERT is like this one:
INSERT INTO [SERVIDOR].basedatos.dbo.fmafam (empresa,familia,descripcion
)
values ('090','Fam1','Familia 1')
Bug is like this one:
The provider OLE DB 'SQLOLEDB' can`t INSERT INTO table
'[SERVIDOR].[basedatos].[dbo].[fmafam]', by column 'observa
ciones'.
OLE DB [OLE/DB Provider 'SQLOLEDB'
IRowsetChange::InsertRow returned 0x80040e21: Data status sent to the
provider: [COLUMN_NAME=empresa STATUS=DBSTATUS_S_OK], [COLUMN_NAME=f
amilia
STATUS=DBSTATUS_S_OK], [COLUMN_NAME=descripcion STATUS=DBSTATUS_S_DEFAUL
T],
[COLUMN_NAME=observaciones STATUS=DBSTATUS_S_ISNULL],...
Provider OLE DB 'SQLOLEDB' can't INSERT INTO table
[SERVIDOR].[basedatos].[dbo].[fmafam], by column 'observacio
nes'.
INSERT hasn't field "Observaciones" (TEXT type) because it has a default
value.
Running same instruction locally there isn't any problem.
How can I solve this problem ?Luis,
I have reproduced this error. I have never seen this before,
and I'll ask for some help and get back to you.
Steve Kass
Drew University
Luis Soler wrote:

>Hi all:
>I have following problem.
>When I try to run an INSERT from Query Analyser to a table of a linked
>server I get
>a bug about a TEXT field of table:
>INSERT is like this one:
>INSERT INTO [SERVIDOR].basedatos.dbo.fmafam (empresa,familia,descripcio
n)
> values ('090','Fam1','Familia 1')
>Bug is like this one:
>The provider OLE DB 'SQLOLEDB' can`t INSERT INTO table
>'[SERVIDOR].[basedatos].[dbo].[fmafam]', by column 'observ
aciones'.
>OLE DB [OLE/DB Provider 'SQLOLEDB'
>IRowsetChange::InsertRow returned 0x80040e21: Data status sent to the
>provider: [COLUMN_NAME=empresa STATUS=DBSTATUS_S_OK], [COLUMN_NAME=
familia
>STATUS=DBSTATUS_S_OK], [COLUMN_NAME=descripcion STATUS=DBSTATUS_S_DEFAU
LT],
>[COLUMN_NAME=observaciones STATUS=DBSTATUS_S_ISNULL],...
>Provider OLE DB 'SQLOLEDB' can't INSERT INTO table
>[SERVIDOR].[basedatos].[dbo].[fmafam], by column 'observaci
ones'.
>INSERT hasn't field "Observaciones" (TEXT type) because it has a default
>value.
>Running same instruction locally there isn't any problem.
>How can I solve this problem ?
>
>
>|||Hi Steve:
Table has one TEXT type field and a default value of ''.
I get error only in TEXT fields.
Boths servers have SQL Server 2000 with SP3.
Do you need anything more ?
"Steve Kass" <skass@.drew.edu> escribi en el mensaje
news:uvDbD8DFFHA.2156@.TK2MSFTNGP10.phx.gbl...[vbcol=seagreen]
> Luis,
> I have reproduced this error. I have never seen this before,
> and I'll ask for some help and get back to you.
> Steve Kass
> Drew University
> Luis Soler wrote:
>|||Luis Soler (none@.none) writes:
> When I try to run an INSERT from Query Analyser to a table of a linked
> server I get
> a bug about a TEXT field of table:
> INSERT is like this one:
> INSERT INTO [SERVIDOR].basedatos.dbo.fmafam (empresa,familia,descripci
on)
> values ('090','Fam1','Familia 1')
> Bug is like this one:
> The provider OLE DB 'SQLOLEDB' can`t INSERT INTO table
> '[SERVIDOR].[basedatos].[dbo].[fmafam]', by column 'obser
vaciones'.
> OLE DB [OLE/DB Provider 'SQLOLEDB'
> IRowsetChange::InsertRow returned 0x80040e21: Data status sent to the
> provider: [COLUMN_NAME=empresa STATUS=DBSTATUS_S_OK], [COLUMN_NAME
=familia
> STATUS=DBSTATUS_S_OK], [COLUMN_NAME=descripcion
> STATUS=DBSTATUS_S_DEFAULT],
> [COLUMN_NAME=observaciones STATUS=DBSTATUS_S_ISNULL],...
> Provider OLE DB 'SQLOLEDB' can't INSERT INTO table
> [SERVIDOR].[basedatos].[dbo].[fmafam], by column 'observac
iones'.
> INSERT hasn't field "Observaciones" (TEXT type) because it has a default
> value.
> Running same instruction locally there isn't any problem.
> How can I solve this problem ?
You will have to find a workaround of some sort. It appears that the
SQLOLEDB provider does not support default values with text columns.
Or maybe I should say do not expect them to have a default value. So
when the the data is entered into the rowset, SQLOLEDB things have
gone sour.
I also tested this on the latest (semi-)public build of SQL 2005, and
when the local server is SQL 2005, the INSERT works. This indicates
that the problem is fixed in SQL Native Client, an SQL Server OLE DB
provider that comes with SQL 2005.
What the best workaround is for you depends on your application, but
presumably the easiest way out is to make the column nullable.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||OK, already I have thinking about the option to make the field nullable
Thanks Erland
"Erland Sommarskog" <esquel@.sommarskog.se> escribi en el mensaje
news:Xns9600E1BB44115Yazorman@.127.0.0.1...
> Luis Soler (none@.none) writes:
> You will have to find a workaround of some sort. It appears that the
> SQLOLEDB provider does not support default values with text columns.
> Or maybe I should say do not expect them to have a default value. So
> when the the data is entered into the rowset, SQLOLEDB things have
> gone sour.
> I also tested this on the latest (semi-)public build of SQL 2005, and
> when the local server is SQL 2005, the INSERT works. This indicates
> that the problem is fixed in SQL Native Client, an SQL Server OLE DB
> provider that comes with SQL 2005.
> What the best workaround is for you depends on your application, but
> presumably the easiest way out is to make the column nullable.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp

Problem with liinked server

This is a multi-part message in MIME format.
--=_NextPart_000_00A3_01C79966.58D0C7A0
Content-Type: text/plain;
charset="iso-8859-2"
Content-Transfer-Encoding: quoted-printable
Hi,
I have two sql server. One is sql 2000, and another is sql 2005
On sql 2000 I try to add linked to sql 2005.
(like this)
exec sp_addlinkedserver @.server =3D N'ZAGREB_NEW',
@.srvproduct =3D N' ',
@.provider =3D N'SQLNCLI',
@.datasrc =3D N'',
@.catalog =3D N'komitenti',
@.provstr =3D N'SERVER=3DZAGREB_NEW;uid=3Dradovan;pwd=3DDELTA555'
GO
I try
SELECT top 1 adresa from zagreb_new.komitenti.dbo.komitenti
and have followig error:
OLE DB error trace [Non-interface error: Column 'MB' (compile-time = ordinal 4) of object '"komitenti"."dbo"."komitenti"' was reported to = have changed. The exact nature of the change is unknown].
Msg 7356, Level 16, State 1, Line 1
OLE DB provider 'SQLNCLI' supplied inconsistent metadata for a column. = Metadata information was changed at execution time.
When I try:
SELECT * FROM OPENQUERY(ZAGREB_NEW, 'SELECT TOP 1 * FROM = komitenti.dbo.komitenti')
I got result.
How can I exec stored procedure on linked server, because
exec Zagreb_new.database.dbo.Mystored doesn't work
error is:
Msg 7212, Level 17, State 1, Line 1
Could not execute procedure 'spG_ProvjeriMB' on remote server = 'zagreb_new'.
--=_NextPart_000_00A3_01C79966.58D0C7A0
Content-Type: text/html;
charset="iso-8859-2"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Hi,
I have two sql server. One is sql 2000, = and another is sql 2005

On sql 2000 I try to add linked to sql 2005.

(like this)
exec sp_addlinkedserver @.server =3D N'ZAGREB_NEW',
@.srvproduct =3D N' ',
@.provider =3D N'SQLNCLI',
@.datasrc =3D N'',
@.catalog =3D N'komitenti',
@.provstr =3D N'SERVER=3DZAGREB_NEW;uid=3Dradovan;pwd=3DDELTA555'
GO

I try
SELECT top 1 adresa from zagreb_new.komitenti.dbo.komitenti
and have followig error:

OLE DB error trace [Non-interface error: = Column 'MB' (compile-time ordinal 4) of object '"komitenti"."dbo"."komitenti"' was = reported to have changed. The exact nature of the change is unknown].
Msg 7356, Level 16, State 1, Line = 1
OLE DB provider 'SQLNCLI' supplied = inconsistent metadata for a column. Metadata information was changed at execution time.

When I try:
SELECT * FROM OPENQUERY(ZAGREB_NEW, = 'SELECT TOP 1 * FROM komitenti.dbo.komitenti')
I got result.

How can I exec stored procedure on linked = server, because
exec Zagreb_new.database.dbo.Mystored = doesn't work
error is:

Msg 7212, Level 17, State 1, Line 1
Could not execute procedure 'spG_ProvjeriMB' on remote = server 'zagreb_new'.

--=_NextPart_000_00A3_01C79966.58D0C7A0--Hi
"Radovan DobriÄ?" wrote:
> Hi,
> I have two sql server. One is sql 2000, and another is sql 2005
> On sql 2000 I try to add linked to sql 2005.
> (like this)
> exec sp_addlinkedserver @.server = N'ZAGREB_NEW',
> @.srvproduct = N' ',
> @.provider = N'SQLNCLI',
> @.datasrc = N'',
> @.catalog = N'komitenti',
> @.provstr = N'SERVER=ZAGREB_NEW;uid=radovan;pwd=DELTA555'
> GO
>
> I try
> SELECT top 1 adresa from zagreb_new.komitenti.dbo.komitenti
> and have followig error:
>
> OLE DB error trace [Non-interface error: Column 'MB' (compile-time ordinal 4) of object '"komitenti"."dbo"."komitenti"' was reported to have changed. The exact nature of the change is unknown].
> Msg 7356, Level 16, State 1, Line 1
> OLE DB provider 'SQLNCLI' supplied inconsistent metadata for a column. Metadata information was changed at execution time.
>
> When I try:
> SELECT * FROM OPENQUERY(ZAGREB_NEW, 'SELECT TOP 1 * FROM komitenti.dbo.komitenti')
> I got result.
>
> How can I exec stored procedure on linked server, because
> exec Zagreb_new.database.dbo.Mystored doesn't work
> error is:
>
> Msg 7212, Level 17, State 1, Line 1
> Could not execute procedure 'spG_ProvjeriMB' on remote server 'zagreb_new'
The only thing I have found about this is that "it could be fixed in SP2"
which versions are the servers running? Is the client one of the servers?
Is a login defined for the linked server? See
http://msdn2.microsoft.com/en-us/library/ms189811.aspx or are you using the
default mapping, in which case will the login have permissions on the remote
server? Your error message when running the remote procedure would indicate
that they don't have permissions to run this.
John|||Hi,
I have sp2 on server (I try it on another server with sp2 in another firm
and have exactly same result).
I try it with sql server account (and did mapping on that account). That
account have admin privil.
radovan
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:BE5DF476-CF21-4F8C-8634-D4461EA9132F@.microsoft.com...
> Hi
> "Radovan Dobriæ" wrote:
>> Hi,
>> I have two sql server. One is sql 2000, and another is sql 2005
>> On sql 2000 I try to add linked to sql 2005.
>> (like this)
>> exec sp_addlinkedserver @.server = N'ZAGREB_NEW',
>> @.srvproduct = N' ',
>> @.provider = N'SQLNCLI',
>> @.datasrc = N'',
>> @.catalog = N'komitenti',
>> @.provstr = N'SERVER=ZAGREB_NEW;uid=radovan;pwd=DELTA555'
>> GO
>>
>> I try
>> SELECT top 1 adresa from zagreb_new.komitenti.dbo.komitenti
>> and have followig error:
>>
>> OLE DB error trace [Non-interface error: Column 'MB' (compile-time
>> ordinal 4) of object '"komitenti"."dbo"."komitenti"' was reported to have
>> changed. The exact nature of the change is unknown].
>> Msg 7356, Level 16, State 1, Line 1
>> OLE DB provider 'SQLNCLI' supplied inconsistent metadata for a column.
>> Metadata information was changed at execution time.
>>
>> When I try:
>> SELECT * FROM OPENQUERY(ZAGREB_NEW, 'SELECT TOP 1 * FROM
>> komitenti.dbo.komitenti')
>> I got result.
>>
>> How can I exec stored procedure on linked server, because
>> exec Zagreb_new.database.dbo.Mystored doesn't work
>> error is:
>>
>> Msg 7212, Level 17, State 1, Line 1
>> Could not execute procedure 'spG_ProvjeriMB' on remote server
>> 'zagreb_new'
> The only thing I have found about this is that "it could be fixed in SP2"
> which versions are the servers running? Is the client one of the servers?
> Is a login defined for the linked server? See
> http://msdn2.microsoft.com/en-us/library/ms189811.aspx or are you using
> the
> default mapping, in which case will the login have permissions on the
> remote
> server? Your error message when running the remote procedure would
> indicate
> that they don't have permissions to run this.
> John
>|||Hi
Have you tried creating the linked server as
EXEC sp_addlinkedserver @.server = 'ZAGREB_NEW',
@.srvproduct = N'',
@.provider = N'SQLNCLI',
@.datasrc = N'ZAGREB_NEW'
or
EXEC sp_addlinkedserver 'ZAGREB_NEW', N'SQL Server'
John
"Radovan Dobriæ" wrote:
> Hi,
> I have sp2 on server (I try it on another server with sp2 in another firm
> and have exactly same result).
> I try it with sql server account (and did mapping on that account). That
> account have admin privil.
> radovan
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:BE5DF476-CF21-4F8C-8634-D4461EA9132F@.microsoft.com...
> > Hi
> >
> > "Radovan Dobriæ" wrote:
> >
> >> Hi,
> >> I have two sql server. One is sql 2000, and another is sql 2005
> >>
> >> On sql 2000 I try to add linked to sql 2005.
> >>
> >> (like this)
> >> exec sp_addlinkedserver @.server = N'ZAGREB_NEW',
> >>
> >> @.srvproduct = N' ',
> >>
> >> @.provider = N'SQLNCLI',
> >>
> >> @.datasrc = N'',
> >>
> >> @.catalog = N'komitenti',
> >>
> >> @.provstr = N'SERVER=ZAGREB_NEW;uid=radovan;pwd=DELTA555'
> >>
> >> GO
> >>
> >>
> >>
> >> I try
> >>
> >> SELECT top 1 adresa from zagreb_new.komitenti.dbo.komitenti
> >>
> >> and have followig error:
> >>
> >>
> >>
> >> OLE DB error trace [Non-interface error: Column 'MB' (compile-time
> >> ordinal 4) of object '"komitenti"."dbo"."komitenti"' was reported to have
> >> changed. The exact nature of the change is unknown].
> >>
> >> Msg 7356, Level 16, State 1, Line 1
> >>
> >> OLE DB provider 'SQLNCLI' supplied inconsistent metadata for a column.
> >> Metadata information was changed at execution time.
> >>
> >>
> >>
> >> When I try:
> >>
> >> SELECT * FROM OPENQUERY(ZAGREB_NEW, 'SELECT TOP 1 * FROM
> >> komitenti.dbo.komitenti')
> >>
> >> I got result.
> >>
> >>
> >>
> >> How can I exec stored procedure on linked server, because
> >>
> >> exec Zagreb_new.database.dbo.Mystored doesn't work
> >>
> >> error is:
> >>
> >>
> >>
> >> Msg 7212, Level 17, State 1, Line 1
> >>
> >> Could not execute procedure 'spG_ProvjeriMB' on remote server
> >> 'zagreb_new'
> >
> > The only thing I have found about this is that "it could be fixed in SP2"
> > which versions are the servers running? Is the client one of the servers?
> >
> > Is a login defined for the linked server? See
> > http://msdn2.microsoft.com/en-us/library/ms189811.aspx or are you using
> > the
> > default mapping, in which case will the login have permissions on the
> > remote
> > server? Your error message when running the remote procedure would
> > indicate
> > that they don't have permissions to run this.
> >
> > John
> >
> >
>
>|||That was my first try.
Linked server works fine with
SELECT * FROM OPENQUERY(ZAGREB_NEW, 'SELECT TOP 1 * FROM
komitenti.dbo.komitenti')
but not with
SELECT TOP 1 * FROM zagreb_new.komitenti.dbo.komitenti'
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:03897BB2-0508-4F7C-A923-090DB6AE439B@.microsoft.com...
> Hi
> Have you tried creating the linked server as
> EXEC sp_addlinkedserver @.server = 'ZAGREB_NEW',
> @.srvproduct = N'',
> @.provider = N'SQLNCLI',
> @.datasrc = N'ZAGREB_NEW'
> or
> EXEC sp_addlinkedserver 'ZAGREB_NEW', N'SQL Server'
>
> John
> "Radovan Dobria" wrote:
>> Hi,
>> I have sp2 on server (I try it on another server with sp2 in another firm
>> and have exactly same result).
>> I try it with sql server account (and did mapping on that account). That
>> account have admin privil.
>> radovan
>> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
>> news:BE5DF476-CF21-4F8C-8634-D4461EA9132F@.microsoft.com...
>> > Hi
>> >
>> > "Radovan Dobria" wrote:
>> >
>> >> Hi,
>> >> I have two sql server. One is sql 2000, and another is sql 2005
>> >>
>> >> On sql 2000 I try to add linked to sql 2005.
>> >>
>> >> (like this)
>> >> exec sp_addlinkedserver @.server = N'ZAGREB_NEW',
>> >>
>> >> @.srvproduct = N' ',
>> >>
>> >> @.provider = N'SQLNCLI',
>> >>
>> >> @.datasrc = N'',
>> >>
>> >> @.catalog = N'komitenti',
>> >>
>> >> @.provstr = N'SERVER=ZAGREB_NEW;uid=radovan;pwd=DELTA555'
>> >>
>> >> GO
>> >>
>> >>
>> >>
>> >> I try
>> >>
>> >> SELECT top 1 adresa from zagreb_new.komitenti.dbo.komitenti
>> >>
>> >> and have followig error:
>> >>
>> >>
>> >>
>> >> OLE DB error trace [Non-interface error: Column 'MB' (compile-time
>> >> ordinal 4) of object '"komitenti"."dbo"."komitenti"' was reported to
>> >> have
>> >> changed. The exact nature of the change is unknown].
>> >>
>> >> Msg 7356, Level 16, State 1, Line 1
>> >>
>> >> OLE DB provider 'SQLNCLI' supplied inconsistent metadata for a column.
>> >> Metadata information was changed at execution time.
>> >>
>> >>
>> >>
>> >> When I try:
>> >>
>> >> SELECT * FROM OPENQUERY(ZAGREB_NEW, 'SELECT TOP 1 * FROM
>> >> komitenti.dbo.komitenti')
>> >>
>> >> I got result.
>> >>
>> >>
>> >>
>> >> How can I exec stored procedure on linked server, because
>> >>
>> >> exec Zagreb_new.database.dbo.Mystored doesn't work
>> >>
>> >> error is:
>> >>
>> >>
>> >>
>> >> Msg 7212, Level 17, State 1, Line 1
>> >>
>> >> Could not execute procedure 'spG_ProvjeriMB' on remote server
>> >> 'zagreb_new'
>> >
>> > The only thing I have found about this is that "it could be fixed in
>> > SP2"
>> > which versions are the servers running? Is the client one of the
>> > servers?
>> >
>> > Is a login defined for the linked server? See
>> > http://msdn2.microsoft.com/en-us/library/ms189811.aspx or are you using
>> > the
>> > default mapping, in which case will the login have permissions on the
>> > remote
>> > server? Your error message when running the remote procedure would
>> > indicate
>> > that they don't have permissions to run this.
>> >
>> > John
>> >
>> >
>>|||Hi
On May 23, 3:40 pm, "Radovan Dobri=E6" <rado...@.servis24.hr> wrote:
> That was my first try.
> Linked server works fine with
> SELECT * FROM OPENQUERY(ZAGREB_NEW, 'SELECT TOP 1 * FROM
> komitenti.dbo.komitenti')
> but not with
> SELECT TOP 1 * FROM zagreb_new.komitenti.dbo.komitenti'
>
The error you are getting would indicate that the user context that
each one is running under is different in each case.
Try mapping the local login to a different remote login using
sp_addlinkedsrvlogin
e=2Eg.
EXEC sp_addlinkedsrvlogin 'Accounts', 'false', 'Domain\Mary', 'MaryP',
'd89q3w4u'
and/or make sure that you can connect as the same user on the remote
system and execute the procedure.
John

Problem with Japanese Inflectional search

Hi,
We have Full Text enabled for five languages, four of them
are latin based and the other in Japanese.
We have built catalogs by specifying the respective
languages for word breakers.
Now the inflectional search is working fine for the latin
based languages but not working for Japanese.
Can anyone tell me if any settings needs to be changed to
make the Inflectional search work for Japanese?
Regards
Anand
Anand,
What is the SQL Server version (7.0 or 2000) and on what OS Platform (NT4.0,
WinXP, Win2000 or Win2003) is it installed on?
Could you post the full output of the following SQL script:
use <your_database_name>
go
SELECT @.@.language
SELECT @.@.version
EXEC sp_help_fulltext_catalogs
EXEC sp_help_fulltext_tables
EXEC sp_help_fulltext_columns
EXEC sp_help <your_FT-enabled_table_name_here>
sp_configure 'default full-text language'
go
Also, what is the default language setting for the server where SQL Server
is installed? Assuming the correct language settings and "Language for Word
Breaker", there should be nothing to change to make FORMSOF(Inflectional)
search work for your FT-enable table column that contains Japanese text.
Thanks,
John
"Anand" <anonymous@.discussions.microsoft.com> wrote in message
news:1b0d301c44f7e$cb917e40$a501280a@.phx.gbl...
> Hi,
> We have Full Text enabled for five languages, four of them
> are latin based and the other in Japanese.
> We have built catalogs by specifying the respective
> languages for word breakers.
> Now the inflectional search is working fine for the latin
> based languages but not working for Japanese.
> Can anyone tell me if any settings needs to be changed to
> make the Inflectional search work for Japanese?
> Regards
> Anand
>

problem with insert command

Hi,
i try to insert values into a sql server table like this (code-behind):
comm1 = Textbox1.Text
comm2=Textbox2.Text
......
comd.CommandText = "insert into [dbo].[mytable] (field1, field2,...)
values(comm1, comm2, ...)"
i get the error:
The name "field1l" is not permitted in this context. Valid expressions are
constants, constant expressions, and (in some contexts) variables. Column
names are not permitted.
Can someone give me the right syntax?
Thanks in advance.
Dan
Dan wrote:
> Hi,
> i try to insert values into a sql server table like this (code-behind):
> comm1 = Textbox1.Text
> comm2=Textbox2.Text
> .....
> comd.CommandText = "insert into [dbo].[mytable] (field1, field2,...)
> values(comm1, comm2, ...)"
> i get the error:
> The name "field1l" is not permitted in this context. Valid expressions are
> constants, constant expressions, and (in some contexts) variables. Column
> names are not permitted.
> Can someone give me the right syntax?
> Thanks in advance.
> Dan
The database is not aware of the variables that you use in your code.
Put parameters in the query, and add the values as parameter objects to
the command object.
Gran Andersson
_____
http://www.guffa.com
|||"Dan" <d@.er.df> wrote in message
news:eF%23nAdXwHHA.4736@.TK2MSFTNGP05.phx.gbl...
> Hi,
> i try to insert values into a sql server table like this (code-behind):
> comm1 = Textbox1.Text
> comm2=Textbox2.Text
> .....
> comd.CommandText = "insert into [dbo].[mytable] (field1, field2,...)
> values(comm1, comm2, ...)"
> i get the error:
> The name "field1l" is not permitted in this context. Valid expressions are
> constants, constant expressions, and (in some contexts) variables. Column
> names are not permitted.
> Can someone give me the right syntax?
> Thanks in advance.
> Dan
>
>
Your SQL statement is in quotes so the comm1, comm2 are sent as those values
to the server which has no idea what you want to do.
Use parameters to pass the values of the fields. Something like "insert
into mytable (field1,field2) values (@.field1, @.field2)
Then in your code create the parameters, add them to the SQLCommand and then
execute.
Hope this gets you going in the right direction.
Lloyd Sheen
|||On Sun, 8 Jul 2007 17:47:11 +0200, "Dan" <d@.er.df> wrote:

>Hi,
>i try to insert values into a sql server table like this (code-behind):
>comm1 = Textbox1.Text
>comm2=Textbox2.Text
>.....
>comd.CommandText = "insert into [dbo].[mytable] (field1, field2,...)
>values(comm1, comm2, ...)"
>i get the error:
>The name "field1l" is not permitted in this context. Valid expressions are
>constants, constant expressions, and (in some contexts) variables. Column
>names are not permitted.
>Can someone give me the right syntax?
>Thanks in advance.
>Dan
>
You need to read up on parameterized queries ... then you'll know how
to safely pass user entered variables to database queries
http://bytes.thinkersroom.com
|||Of course, thanks ..
"Lloyd Sheen" <a@.b.c> schreef in bericht
news:DADD47DE-3006-4C63-8087-F368EFA9EFFF@.microsoft.com...
> "Dan" <d@.er.df> wrote in message
> news:eF%23nAdXwHHA.4736@.TK2MSFTNGP05.phx.gbl...
> Your SQL statement is in quotes so the comm1, comm2 are sent as those
> values to the server which has no idea what you want to do.
> Use parameters to pass the values of the fields. Something like "insert
> into mytable (field1,field2) values (@.field1, @.field2)
> Then in your code create the parameters, add them to the SQLCommand and
> then execute.
> Hope this gets you going in the right direction.
> Lloyd Sheen

problem with insert command

Hi,
i try to insert values into a sql server table like this (code-behind):
comm1 = Textbox1.Text
comm2=Textbox2.Text
.....
comd.CommandText = "insert into [dbo].[mytable] (field1, field2,...
)
values(comm1, comm2, ...)"
i get the error:
The name "field1l" is not permitted in this context. Valid expressions are
constants, constant expressions, and (in some contexts) variables. Column
names are not permitted.
Can someone give me the right syntax?
Thanks in advance.
DanDan wrote:
> Hi,
> i try to insert values into a sql server table like this (code-behind):
> comm1 = Textbox1.Text
> comm2=Textbox2.Text
> .....
> comd.CommandText = "insert into [dbo].[mytable] (field1, field2,.
.)
> values(comm1, comm2, ...)"
> i get the error:
> The name "field1l" is not permitted in this context. Valid expressions are
> constants, constant expressions, and (in some contexts) variables. Column
> names are not permitted.
> Can someone give me the right syntax?
> Thanks in advance.
> Dan
The database is not aware of the variables that you use in your code.
Put parameters in the query, and add the values as parameter objects to
the command object.
Gran Andersson
_____
http://www.guffa.com|||"Dan" <d@.er.df> wrote in message
news:eF%23nAdXwHHA.4736@.TK2MSFTNGP05.phx.gbl...
> Hi,
> i try to insert values into a sql server table like this (code-behind):
> comm1 = Textbox1.Text
> comm2=Textbox2.Text
> .....
> comd.CommandText = "insert into [dbo].[mytable] (field1, field2,.
.)
> values(comm1, comm2, ...)"
> i get the error:
> The name "field1l" is not permitted in this context. Valid expressions are
> constants, constant expressions, and (in some contexts) variables. Column
> names are not permitted.
> Can someone give me the right syntax?
> Thanks in advance.
> Dan
>
>
Your SQL statement is in quotes so the comm1, comm2 are sent as those values
to the server which has no idea what you want to do.
Use parameters to pass the values of the fields. Something like "insert
into mytable (field1,field2) values (@.field1, @.field2)
Then in your code create the parameters, add them to the SQLCommand and then
execute.
Hope this gets you going in the right direction.
Lloyd Sheen|||On Sun, 8 Jul 2007 17:47:11 +0200, "Dan" <d@.er.df> wrote:

>Hi,
>i try to insert values into a sql server table like this (code-behind):
>comm1 = Textbox1.Text
>comm2=Textbox2.Text
>.....
>comd.CommandText = "insert into [dbo].[mytable] (field1, field2,..
.)
>values(comm1, comm2, ...)"
>i get the error:
>The name "field1l" is not permitted in this context. Valid expressions are
>constants, constant expressions, and (in some contexts) variables. Column
>names are not permitted.
>Can someone give me the right syntax?
>Thanks in advance.
>Dan
>
You need to read up on parameterized queries ... then you'll know how
to safely pass user entered variables to database queries
http://bytes.thinkersroom.com|||Of course, thanks ..
"Lloyd Sheen" <a@.b.c> schreef in bericht
news:DADD47DE-3006-4C63-8087-F368EFA9EFFF@.microsoft.com...
> "Dan" <d@.er.df> wrote in message
> news:eF%23nAdXwHHA.4736@.TK2MSFTNGP05.phx.gbl...
> Your SQL statement is in quotes so the comm1, comm2 are sent as those
> values to the server which has no idea what you want to do.
> Use parameters to pass the values of the fields. Something like "insert
> into mytable (field1,field2) values (@.field1, @.field2)
> Then in your code create the parameters, add them to the SQLCommand and
> then execute.
> Hope this gets you going in the right direction.
> Lloyd Sheensql

Problem with Hyphen in Full Text Catalog Search

Hi All,

we have a problem with the Full Text Catalog Search.
We use the following SQL Statement for matching companies from a table:

select company, lastname, firstname, pkcustomers, fkcustomers,
location, title, fkFunktionen, TypeOfPosition
from customers
where (fkcustomers = 0 or fkcustomers is null)
and active = 1
and pkCustomers in (select [KEY] from CONTAINSTABLE(Customers, Company,
'"*SEARCHTERMS*"'))
order by company asc

The search so far is working perfect.
Now the problem: There are two companies in the table called "i-fabrik"
and "b-wise". Theres no way to find these two companies. I find out
that the search is successful if there are more than three letters in
front of the hyphen (for exampe iii-fabrik or bbb-wise). How can that
be? Why exactly 3 letters?

I hope somebody can help me.

Best regards
Markus Weberhttp://support.microsoft.com/defaul...b;en-us;Q200043

Simon

Problem with full text searching

I'm wrtiting a local site search egine but I need to make use of functions like FREETEXTABLE
for instance. In order to use this I have to have the' Full Text Searchtable' enabled for a table. Now the Microsoft Search Service is runningin MSDE; of that I'm sure. However when I try to enable the full textsearching on a table or database the option that I'm supposed to chooseis grayed out which means I can't select it. What's wrong? What do Ihave to do to be able to use that option? I' short of time and wouldappreciate n answer a.s.a.p. Thanks
MSDE does not support the full text service, sorry :-(
|||I am using sql server 2000 trial edition and I'm sure I checked the full text search option during the installion.
|||Figured out the problem thanks!

Problem with Full Text Search and Joined Tables

I have a sproc that accepts variables from an ASP page to process the
search for the user. The user can choose 3 methods:
1.)Any Occurence
2.)Word/Phrase
3.)Exact Word/Phrase
The sproc goes through some if statments to decide which sql statement
to execute based on the method chosen by the user. The user can also
type in a keyword (of course) but can apply the key word to one column
or two columns. The columns are Name and Description (even though
they may not be Name or Description in the database...just a generic
name to use). Since the sproc is not my problem i'll just give a
little exampe of how it works instead of posting all that code, heh.
if method = 1
Begin
if name and description = 1
Begin
execute sql
End
if name = 1
Begin
execute sql
End
if description = 1
Begin
execute sql
End
End
And the code would be the same for the next two methods. My problem
is with the sql statment that executes when the name and description
are equal to 1. I create views and usually do a join between the view
and the table i want to search on. Then i return the results and that
works fine. The problem with this search is that the Name column is
in one table and the Description in another. I still need the view
cause it contains the other 15 tables of info i use to display and
filter data. But i don't know how to search on both tables and get
the resutls i want. Here is my code so far...
Set @.stmt = 'Select * From cuPolicyMemosView
RIGHT OUTER JOIN CONTAINSTABLE(PolicyMemos, Name, ' + "'" + '"' +
@.strSearchLike + '"' + "'" + ') AS Search ON
cuPolicyMemosView.PolicyMemosID = Search.[KEY]
LEFT JOIN CONTAINSTABLE(PolicyMemosFormNumber, Description, ' + "'" +
'"' + @.strSearchLike + '"' + "'" + ') AS Search2 ON
cuPolicyMemosView.PMFNID = Search2.[KEY] Where ((DATEDIFF(d,
ExpirationDate + 30, GETDATE()) < 0) OR (ExpirationDate IS NULL))
ORDER BY ' + @.strSort
Exec(@.stmt)
I can get the Name just fine when i do a search. But i can't get the
Description. I've tried RIGHT, LEFT, FULL joins...can't get the
Description. I've heard that the only way might be to put the info i
get back from each table seperatley and the put that info in a temp
table and search off of that. That might be a possiblity, but i'm not
100% sure how to do it, heh. I would rather just have a way to alter
my current SQL statment, but if that can't be done then i'm open to
any other suggestions. I hope this all makes sense, heh, i know its a
little unclear...but if anyone has dealt with the joined tables search
problem and knows a good way around that problem in general then thats
all the help i need. Thanks in advance.
Taylor
Taylor,
I've read your posting with interest and it seems that this is the main
issue: "The problem with this search is that the Name column is
in one table and the Description in another. I still need the view cause it
contains the other 15 tables of info i use to display and filter data. But
i don't know how to search on both tables and get the results i want."
The problem of searching across tables with SQL FTS is well know as I posted
three times on this issue just yesterday... I've attached a SQL script file
(Containstable_multiple_tables.sql) with schema and inserted data that I
*think* is related to what you are trying to do, although it does not use
dynamic SQL code... Also, in your code snippet example below could you
provide the name of the view &/or code. If that's not possible, you can just
create a similar view with new tables that work with the tables in the
attached file.
Regards,
John
PS: If you cannot access the attached file, feel free to email me directly
and I'll email it to you.
"Taylor" <dryrye@.juno.com> wrote in message
news:9c0e390c.0409010657.4167393d@.posting.google.c om...
> I have a sproc that accepts variables from an ASP page to process the
> search for the user. The user can choose 3 methods:
> 1.)Any Occurence
> 2.)Word/Phrase
> 3.)Exact Word/Phrase
> The sproc goes through some if statments to decide which sql statement
> to execute based on the method chosen by the user. The user can also
> type in a keyword (of course) but can apply the key word to one column
> or two columns. The columns are Name and Description (even though
> they may not be Name or Description in the database...just a generic
> name to use). Since the sproc is not my problem i'll just give a
> little exampe of how it works instead of posting all that code, heh.
> if method = 1
> Begin
> if name and description = 1
> Begin
> execute sql
> End
> if name = 1
> Begin
> execute sql
> End
> if description = 1
> Begin
> execute sql
> End
> End
> And the code would be the same for the next two methods. My problem
> is with the sql statment that executes when the name and description
> are equal to 1. I create views and usually do a join between the view
> and the table i want to search on. Then i return the results and that
> works fine. The problem with this search is that the Name column is
> in one table and the Description in another. I still need the view
> cause it contains the other 15 tables of info i use to display and
> filter data. But i don't know how to search on both tables and get
> the resutls i want. Here is my code so far...
> Set @.stmt = 'Select * From cuPolicyMemosView
> RIGHT OUTER JOIN CONTAINSTABLE(PolicyMemos, Name, ' + "'" + '"' +
> @.strSearchLike + '"' + "'" + ') AS Search ON
> cuPolicyMemosView.PolicyMemosID = Search.[KEY]
> LEFT JOIN CONTAINSTABLE(PolicyMemosFormNumber, Description, ' + "'" +
> '"' + @.strSearchLike + '"' + "'" + ') AS Search2 ON
> cuPolicyMemosView.PMFNID = Search2.[KEY] Where ((DATEDIFF(d,
> ExpirationDate + 30, GETDATE()) < 0) OR (ExpirationDate IS NULL))
> ORDER BY ' + @.strSort
> Exec(@.stmt)
> I can get the Name just fine when i do a search. But i can't get the
> Description. I've tried RIGHT, LEFT, FULL joins...can't get the
> Description. I've heard that the only way might be to put the info i
> get back from each table seperatley and the put that info in a temp
> table and search off of that. That might be a possiblity, but i'm not
> 100% sure how to do it, heh. I would rather just have a way to alter
> my current SQL statment, but if that can't be done then i'm open to
> any other suggestions. I hope this all makes sense, heh, i know its a
> little unclear...but if anyone has dealt with the joined tables search
> problem and knows a good way around that problem in general then thats
> all the help i need. Thanks in advance.
> Taylor
begin 666 Containstable_mutiple_tables.sql
M#0HM+0EF:6QE;F%M93H@.0V]N=&%I;G-T86)L95]M=71I<&QE7W1A8FQE<RYS
M<6P-"BTM"7!U<G!O<V4Z('1O(&1O8W5M96YT(&AO=R!T;R!U<V4@.8V ]N=&%I
M;G-T86)L92!A;F0@.9G)E971E>'1T86)L92!I;B!M=71I<&QE($944 R!Q=65R
M:65S#0HM+0EM;V1I9FEE9#H@.,3 Z,#4@.4$T@.-R\Q,B\R,# T#0H-"G5S92!S
M<6QF=',-"F=O#0IS96QE8W0@.0$!V97)S:6]N("TM($UI8W)O<V]F="!344P@.
M4V5R=F5R(" R,# P("T@.."XP,"XW-C @.;VX@.16YT97)P<FES92!%9&ET:6]N
M(&]N(%=I;F1O=W,@.3E0@.-2XR("A"=6EL9" S-SDP.B I#0HO*@.T*+2TM+2TM
M+2TM+2TM+2TM+2TM+2TM+2TM+2TM+2TM+2TM+2TM+2TM+2TM+ 2TM#0I-:6-R
M;W-O9G0@.4U%,(%-E<G9E<B @.,C P," M(#@.N,# N-S8P("A);G1E;"!8.#8I
M("T@.1&5V96QO<&5R($5D:71I;VX@.;VX@.5VEN9&]W<R!.5" U+C$@.*$)U:6QD
M(#(V,# Z(%-E<G9I8V4@.4&%C:R Q*0T*4$LO1DL@.<F5L871I;VYS:&EP(&)E
M='=E96X@.271E;5-T;V-K+D]R9&5R3F\@./2!)=&5M5&ET;&5S+D]R9&5R3F\@.
M86YD(&%L<V\@.271E;5-T;V-K+D]R9&5R3F\@./2!)=&5M2&%R9'=A<F4N3W)D
M97).;RX@.#0HJ+PT*#0HM+2!$969I;F4@.=&%B;&5S+BXN#0I#4 D5!5$4@.5$%"
M3$4@.6V1B;UTN6TET96U3=&]C:UT@.* T*(%M/<F1E<DYO72!;=F%R8VAA<ET@.
M*#4P*2!#3TQ,051%(%-13%],871I;C%?1V5N97)A;%]#4#%?0TE?05,@.3D]4
M($Y53$P@.+" M+2!02R!)=&5M4W1O8VL@.+2!U;FEQ=64@./PT*(%M,86)E;%T@.
M6W9A<F-H87)=("@.U,"D@.0T],3$%412!344Q?3&%T:6XQ7T=E;F5R86Q?0U Q
M7T-)7T%3($Y/5"!.54Q,("P-"B!;4V]F=$AA<F1=(%MV87)C:&%R72 H-3 I
M($-/3$Q!5$4@.4U%,7TQA=&EN,5]'96YE<F%L7T-0,5]#25]!4R!.54Q,("P-
M"B!;36%N0V]D95T@.6W9A<F-H87)=("@.U,"D@.0T],3$%412!344Q?3&%T:6XQ
M7T=E;F5R86Q?0U Q7T-)7T%3($Y53$P@.+ T*(%M$97-C<FEP=&EO;ET@.6W9A
M<F-H87)=("@.U,"D@.0T],3$%412!344Q?3&%T:6XQ7T=E;F5R86Q?0U Q7T-)
M7T%3($Y53$P@.+ T*(%MD7U5P9&%T95T@.6V1A=&5T:6UE72!.54Q,("P-"B!;
M17AC;'5S:79E72!;=F%R8VAA<ET@.*#4P*2!#3TQ,051%(%-13%],871I;C%?
M1V5N97)A;%]#4#%?0TE?05,@.3E5,3" L#0H@.6T%F9FEL:6%T95T@.6W9A<F-H
M87)=("@.U,"D@.0T],3$%412!344Q?3&%T:6XQ7T=E;F5R86Q?0U Q7T-)7T%3
M($Y53$P@.+ T*(%M.97=296QE87-E72!;=F%R8VAA<ET@.*#4P*2!#3TQ,051%
M(%-13%],871I;C%?1V5N97)A;%]#4#%?0TE?05,@.3E5,3" L#0H@.6U-T871U
M<UT@.6W9A<F-H87)=("@.U,"D@.0T],3$%412!344Q?3&%T:6XQ7T=E;F5R86Q?
M0U Q7T-)7T%3($Y53$P@.+ T*(%M);G9=(%MD96-I;6%L72@.U+" P*2!.54Q,
M("P-"B!;9%]$871E72!;9&%T971I;65=($Y53$P@.+ T*(%M#871E9V]R>5T@.
M6W9A<F-H87)=("@.U,"D@.0T],3$%412!344Q?3&%T:6XQ7T=E;F5R86Q?0U Q
M7T-)7T%3($Y53$P@.+ T*(%M3:6YG;&5!<G1I<W1=(%MV87)C:&%R72 H,RD@.
M0T],3$%412!344Q?3&%T:6XQ7T=E;F5R86Q?0U Q7T-)7T%3($Y53$P@.+ T*
M(%MS7U1Y<&5=(%MV87)C:&%R72 H-3 I($-/3$Q!5$4@.4U%,7TQA=&EN,5]'
M96YE<F%L7T-0,5]#25]!4R!.54Q,("P-"B!;5'EP941E<V-R:7!=(%MV87)C
M:&%R72 H,3 P*2!#3TQ,051%(%-13%],871I;C%?1V5N97)A;%]#4#%?0TE?
M05,@.3E5,3" L#0H@.6T-A<V5D72!;=F%R8VAA<ET@.*#4P*2!#3TQ,051%(%-1
M3%],871I;C%?1V5N97)A;%]#4#%?0TE?05,@.3E5,3" L#0H@.6U!A8VME9%T@.
M6W9A<F-H87)=("@.U,"D@.0T],3$%412!344Q?3&%T:6XQ7T=E;F5R86Q?0U Q
M7T-)7T%3($Y53$P@.+ T*(%M-86YU9ET@.6W9A<F-H87)=("@.U,"D@.0T],3$%4
M12!344Q?3&%T:6XQ7T=E;F5R86Q?0U Q7T-)7T%3($Y53$P@.+ T*(%M-86Y3
M;W)T72!;:6YT72!.54Q,("P-"B!;5V5I9VAT72!;9&5C:6UA;%TH-2P@.,BD@.
M3E5,3" L#0H@.6TEC;VYS-S5=(%MV87)C:&%R72 H,3 P*2!#3TQ,051%(%-1
M3%],871I;C%?1V5N97)A;%]#4#%?0TE?05,@.3E5,3" L#0H@.6TEC;VYS,3 P
M72!;=F%R8VAA<ET@.*#$P,"D@.0T],3$%412!344Q?3&%T:6XQ7T=E;F5R86Q?
M0U Q7T-)7T%3($Y53$P@.+ T*(%M)8V]N<S(P,%T@.6W9A<F-H87)=("@.Q,# I
M($-/3$Q!5$4@.4U%,7TQA=&EN,5]'96YE<F%L7T-0,5]#25]!4R!.54Q,("P-
M"B!;26-O;G,S,#!=(%MV87)C:&%R72 H,3 P*2!#3TQ,051%(%-13%],871I
M;C%?1V5N97)A;%]#4#%?0TE?05,@.3E5,3" L#0H@.6TE$72!;;G5M97)I8UTH
M,3 L(# I($E$14Y42519("@.Q+" Q*2!.3U0@.3E5,3" M+2TM+2TM+2TM+2TM
M+2TM+2TM+2TM+2TM+2TM/B!O<B!02R _(&IU<W0@.9F]R($94($EN9&5X(#\-
M"BD@.3TX@.6U!224U!4EE=#0I'3PT*04Q415(@.5$%"3$4@.271E; 5-T;V-K(%=)
M5$@.@.3D]#2$5#2R!!1$0@.4%))34%262!+15D@.0TQ54U1%4D5$("A/<F1E<DYO
M*0T*9V\-"BTM($EN<V5R="!T97-T('-A;7!L92!D871A#0II;G-E<G0@.:6YT
M;R!)=&5M4W1O8VL@.=F%L=65S("@.G,# P,2<L("=4:&ES(&ES($QA8F5L(# P
M,#$@.;V8@.<F]W(&]N92!":6QL>2!*;V5L)RQ.54Q,+$Y53$PL3E5,3"Q.54Q,
M+$Y53$PL3E5,3"Q.54Q,+$Y53$PL3E5,3"Q.54Q,+$Y53$PL3 E5,3"Q.54Q,
M+$Y53$PL3E5,3"Q.54Q,+$Y53$PL3E5,3"Q.54Q,+$Y53$PL3 E5,3"Q.54Q,
M+$Y53$PI#0II;G-E<G0@.:6YT;R!)=&5M4W1O8VL@.=F%L=65S("@.G,# P,B<L
M("=4:&ES(&ES($QA8F5L(# P,#(@.;V8@.<F]W('1W;R!3=&EN9R<L3E5,3"Q.
M54Q,+$Y53$PL3E5,3"Q.54Q,+$Y53$PL3E5,3"Q.54Q,+$Y53 $PL3E5,3"Q.
M54Q,+$Y53$PL3E5,3"Q.54Q,+$Y53$PL3E5,3"Q.54Q,+$Y53 $PL3E5,3"Q.
M54Q,+$Y53$PL3E5,3"Q.54Q,*0T*:6YS97)T(&EN=&\@.271E; 5-T;V-K('9A
M;'5E<R H)S P,#,G+" G5&AI<R!I<R!,86)E;" P,# S(&]F(')O=R!T:')E
M92!344P@.4V5R=F5R)RQ.54Q,+$Y53$PL3E5,3"Q.54Q,+$Y53 $PL3E5,3"Q.
M54Q,+$Y53$PL3E5,3"Q.54Q,+$Y53$PL3E5,3"Q.54Q,+$Y53 $PL3E5,3"Q.
M54Q,+$Y53$PL3E5,3"Q.54Q,+$Y53$PL3E5,3"Q.54Q,+$Y53 $PI#0II;G-E
M<G0@.:6YT;R!)=&5M4W1O8VL@.=F%L=65S("@.G,# P-"<L("=4:&ES(&ES($QA
M8F5L(# P,#0@.;V8@.<F]W(&9O=7(@.3U)!0TQ%)RQ.54Q,+$Y53$PL3E5,3"Q.
M54Q,+$Y53$PL3E5,3"Q.54Q,+$Y53$PL3E5,3"Q.54Q,+$Y53 $PL3E5,3"Q.
M54Q,+$Y53$PL3E5,3"Q.54Q,+$Y53$PL3E5,3"Q.54Q,+$Y53 $PL3E5,3"Q.
M54Q,+$Y53$PI#0HM+2!R;W<@.3D]4(&EN($ET96U4:71L97,L(&)U="!I<R!I
M;B!)=&5M2&%R9'=A<F4-"FEN<V5R="!I;G1O($ET96U3=&]C:R!V86QU97,@.
M*"<P,# U)RP@.)U1H:7,@.:7,@.3&%B96P@.,# P-2!O9B!R;W<@.9FEV92!);G1E
M<F9A8V4G+$Y53$PL3E5,3"Q.54Q,+$Y53$PL3E5,3"Q.54Q,+ $Y53$PL3E5,
M3"Q.54Q,+$Y53$PL3E5,3"Q.54Q,+$Y53$PL3E5,3"Q.54Q,+ $Y53$PL3E5,
M3"Q.54Q,+$Y53$PL3E5,3"Q.54Q,+$Y53$PL3E5,3"D-"F=O#0H-"D-214%4
M12!404),12!;9&)O72Y;271E;51I=&QE<UT@.* T*(%M/<F1E<DYO72!;=F%R
M8VAA<ET@.*#4P*2!#3TQ,051%(%-13%],871I;C%?1V5N97)A;%]#4#%?0TE?
M05,@.3D]4($Y53$P@.+ T*(%M4:71L95T@.6W9A<F-H87)=("@.W,"D@.0T],3$%4
M12!344Q?3&%T:6XQ7T=E;F5R86Q?0U Q7T-)7T%3($Y/5"!.54Q,("P-"B!;
M07)T:7-T72!;=F%R8VAA<ET@.*#8P*2!#3TQ,051%(%-13%],871I;C%?1V5N
M97)A;%]#4#%?0TE?05,@.3D]4($Y53$P@.+ T*(%M,;V-A=&EO;ET@.6W9A<F-H
M87)=("@.U,"D@.0T],3$%412!344Q?3&%T:6XQ7T=E;F5R86Q?0U Q7T-)7T%3
M($Y/5"!.54Q,("P-"B!;4V]R=$ME>5T@.6V1E8VEM86Q=*#4L(# I($Y/5"!.
M54Q,("P-"B!;1&ES8U])1%T@.6W9A<F-H87)=("@.U,"D@.0T],3$%412!344Q?
M3&%T:6XQ7T=E;F5R86Q?0U Q7T-)7T%3($Y53$P@.+ T*(%M-4#-&:6QE<UT@.
M6W9A<F-H87)=("@.Q,# I($-/3$Q!5$4@.4U%,7TQA=&EN,5]'96YE<F%L7T-0
M,5]#25]!4R!.54Q,("P-"B!;241=(%MN=6UE<FEC72@.Q,"P@.,"D@.241%3E1)
M5%D@.*#$L(#$I($Y/5"!.54Q,#0HI($].(%M04DE-05)970T*1T\-"D%,5$52
M(%1!0DQ%($ET96U4:71L97,@.5TE42"!.3T-(14-+($%$1"!04DE-05)9($M%
M62!#3%535$52140@.*$]R9&5R3F\I#0IG;PT*+2T@.26YS97)T('1E<W0@.<V%M
M<&QE(&1A=&$-"FEN<V5R="!I;G1O($ET96U4:71L97,@.=F%L=65S("@.G,# P
M,2<L)U1H:7,@.:7,@.5&ET;&4@.,# P,2!O9B!R;W<@.;VYE(%-T<F%N9V5R)RPG
M0FEL;'D@.2F]E;"<L)R!3;VUE<&QA8V4@.:&5R92<L,2Q.54Q,+$Y53$PI#0I I
M;G-E<G0@.:6YT;R!)=&5M5&ET;&5S('9A;'5E<R H)S P,#(G+"=4:&ES(&ES
M(%1I=&QE(# P,#(@.;V8@.<F]W('1W;R!3=&EN9R<L)U-T:6YG)RPG(%-O;65P
M;&%C92!T:&5R92<L.2Q.54Q,+$Y53$PI#0II;G-E<G0@.:6YT;R!)=&5M5&ET
M;&5S('9A;'5E<R H)S P,#,G+"=4:&ES(&ES(%1I=&QE(# P,#,@.;V8@.<F]W
M('1H<F5E(%-13"!397)V97(G+"=-:6-R;W-O9G0G+"<@.4V]M97!L86-E(&5L
M<V4G+#@.L3E5,3"Q.54Q,*0T*:6YS97)T(&EN=&\@.271E;51I= &QE<R!V86QU
M97,@.*"<P,# T)RPG5&AI<R!I<R!4:71L92 P,# T(&]F(')O=R!F;W5R($]2
M04-,12<L)T]R86-L92<L)R!3;VUE<&QA8V4@.96QS92<L-"Q.54Q,+$Y53$PI
M#0IG;PT*#0HM+2!)=&5M4W1O8VLN3W)D97).;R ]($ET96U(87)D=V%R92Y/
M<F1E<DYO+B -"D-214%412!404),12!;9&)O72Y;271E;4AA<F1W87)E72 H
M#0H@.6T]R9&5R3F]=(%MV87)C:&%R72 H-3 I($-/3$Q!5$4@.4U%,7TQA=&EN
M,5]'96YE<F%L7T-0,5]#25]!4R!.3U0@.3E5,3" L#0H@.6W-?<&%G95T@.6W9A
M<F-H87)=("@.V,"D@.0T],3$%412!344Q?3&%T:6XQ7T=E;F5R86Q?0U Q7T-)
M7T%3($Y53$P@.+ T*(%MS7U1H=6UB72!;=F%R8VAA<ET@.*#$U,"D@.0T],3$%4
M12!344Q?3&%T:6XQ7T=E;F5R86Q?0U Q7T-)7T%3($Y53$P@.+ T*(%MS7W!I
M8UT@.6W9A<F-H87)=("@.Q-3 I($-/3$Q!5$4@.4U%,7TQA=&EN,5]'96YE<F%L
M7T-0,5]#25]!4R!.54Q,("P-"B!;4$1&4V-A;&5=(%MV87)C:&%R72 H-3 I
M($-/3$Q!5$4@.4U%,7TQA=&EN,5]'96YE<F%L7T-0,5]#25]!4R!.54Q,("P-
M"B!;;5]3<&5C<UT@.6W9A<F-H87)=("@.X,# P*2!#3TQ,051%(%-13%],871I
M;C%?1V5N97)A;%]#4#%?0TE?05,@.3E5,3" L#0H@.6U!$1E-P96-S72!;=F%R
M8VAA<ET@.*#@.P,# I($-/3$Q!5$4@.4U%,7TQA=&EN,5]'96YE<F%L7T-0,5]#
M25]!4R!.54Q,("P-"B!;3F]T97-=(%MV87)C:&%R72 H.# P,"D@.0T],3$%4
M12!344Q?3&%T:6XQ7T=E;F5R86Q?0U Q7T-)7T%3($Y53$P@.+ T*(%M-961I
M85T@.6W9A<F-H87)=("@.S*2!#3TQ,051%(%-13%],871I;C%?1V5N97)A;%]#
M4#%?0TE?05,@.3E5,3" L#0H@.6T-A<W-=(%MV87)C:&%R72 H,RD@.0T],3$%4
M12!344Q?3&%T:6XQ7T=E;F5R86Q?0U Q7T-)7T%3($Y53$P@.+ T*(%M#1$==
M(%MV87)C:&%R72 H,RD@.0T],3$%412!344Q?3&%T:6XQ7T=E;F5R86Q?0U Q
M7T-)7T%3($Y53$P@.+ T*(%M60T1=(%MV87)C:&%R72 H,RD@.0T],3$%412!3
M44Q?3&%T:6XQ7T=E;F5R86Q?0U Q7T-)7T%3($Y53$P@.+ T*(%M$5D1=(%MV
M87)C:&%R72 H,RD@.0T],3$%412!344Q?3&%T:6XQ7T=E;F5R86Q?0U Q7T-)
M7T%3($Y53$P@.+ T*(%M)1%T@.6VYU;65R:6-=*#$P+" P*2!)1$5.5$E462 H
M,2P@.,2D@.3D]4($Y53$P-"BD@.3TX@.6U!224U!4EE=#0I'3PT*04Q415(@.5$%"
M3$4@.271E;4AA<F1W87)E(%=)5$@.@.3D]#2$5#2R!!1$0@.4%))34%262!+15D@.
M0TQ54U1%4D5$("A/<F1E<DYO*0T*9V\-"B\J("TM($=O="!T:&4@.9F]L;&]W
M:6YG('=A<FYI;F<@.=VAE;B!C<F5A=&EN9R!T86)L93H@.271E; 4AA<F1W87)E
M#0I787)N:6YG.B!4:&4@.=&%B;&4@.)TET96U(87)D=V%R92<@.: &%S(&)E96X@.
M8W)E871E9"!B=70@.:71S(&UA>&EM=6T@.<F]W('-I>F4@.*#(T-3,R*2!E>&-E
M961S('1H92!M87AI;75M(&YU;6)E<B!O9B!B>71E<R!P97(@.< F]W("@.X,#8P
M*2X@.#0I)3E-%4E0@.;W(@.55!$051%(&]F(&$@.<F]W(&EN('1H:7,@.=&%B;&4@.
M=VEL;"!F86EL(&EF('1H92!R97-U;'1I;F<@.<F]W(&QE;F=T:"!E>&-E961S
M(#@.P-C @.8GET97,N#0HJ+PT*+2T@.26YS97)T(%-A;7!L92!D871A#0II;G-E
M<G0@.:6YT;R!)=&5M2&%R9'=A<F4@.=F%L=65S("@.G,# P,2<L("=4:&ES(&ES
M($QA8F5L(# P,#$@.;V8@.<F]W(&]N92!3=')A;F=E<B<L3E5,3"Q.54Q,+$Y5
M3$PL3E5,3"Q.54Q,+$Y53$PL3E5,3"Q.54Q,+$Y53$PL3E5,3 "Q.54Q,*0T*
M:6YS97)T(&EN=&\@.271E;4AA<F1W87)E('9A;'5E<R H)S P,#(G+" G5&AI
M<R!I<R!,86)E;" P,# R(&]F(')O=R!T=V\@.4W1I;F<G+$Y53$PL3E5,3"Q.
M54Q,+$Y53$PL3E5,3"Q.54Q,+$Y53$PL3E5,3"Q.54Q,+$Y53 $PL3E5,3"D-
M"FEN<V5R="!I;G1O($ET96U(87)D=V%R92!V86QU97,@.*"<P, # S)RP@.)U1H
M:7,@.:7,@.3&%B96P@.,# P,R!O9B!R;W<@.=&AR964@.4U%,(%-E<G9E<B<L3E5,
M3"Q.54Q,+$Y53$PL3E5,3"Q.54Q,+$Y53$PL3E5,3"Q.54Q,+ $Y53$PL3E5,
M3"Q.54Q,*0T*:6YS97)T(&EN=&\@.271E;4AA<F1W87)E('9A; '5E<R H)S P
M,#0G+" G5&AI<R!I<R!,86)E;" P,# T(&]F(')O=R!F;W5R($]204-,12<L
M3E5,3"Q.54Q,+$Y53$PL3E5,3"Q.54Q,+$Y53$PL3E5,3"Q.5 4Q,+$Y53$PL
M3E5,3"Q.54Q,*0T*+2T@.<F]W($Y/5"!I;B!)=&5M5&ET;&5S+"!B=70@.:7,@.
M:6X@.271E;5-T;V-K#0II;G-E<G0@.:6YT;R!)=&5M2&%R9'=A<F4@.=F%L=65S
M("@.G,# P-2<L("=4:&ES(&ES($QA8F5L(# P,#4@.;V8@.<F]W(&9I=F4@.26YT
M97)F86-E)RQ.54Q,+$Y53$PL3E5,3"Q.54Q,+$Y53$PL3E5,3"Q.54Q,+ $Y5
M3$PL3E5,3"Q.54Q,+$Y53$PI#0IG;PT*#0HM+2!C;VYF:7)M( '-A;7!L92!D
M871A(&%N9"!&5"UE;F%B;&4@.=&%B;&5S('9I82!%;G1E<G!R: 7-E($UA;F%G
M97(N+BX-"G-E;&5C=" J(&9R;VT@.271E;5-T;V-K#0HM+2!T<G5N8V%T92!T
M86)L92!)=&5M4W1O8VL-"G-E;&5C=" J(&9R;VT@.271E;51I=&QE<PT*+2T@.
M=')U;F-A=&4@.=&%B;&4@.271E;51I=&QE<PT*<V5L96-T("H@.9G)O;2!)=&5M
M2&%R9'=A<F4-"BTM('1R=6YC871E('1A8FQE($ET96U(87)D=V%R90T*9V \-
M"@.T*+2T@.#0HM+2!/<FEG:6YA;"!P<F]B;&5M('%U97)Y+"!W;W)K<R!W:71H
M('1H92!A8F]V92!D871A+"!B=70@.;VYL>2!W:71H(&$@.8V]M;6]N('=O<F0@.
M(G)O=R(@.:7,@.<75E<FEE9"!F<F]M(&%L;"!T:')E92!T86)L97,N#0I$14-,
M05)%($!396%R8VA#<FET97)I82!V87)C:&%R*#$P,"D-"E-%5"! 4V5A<F-H
M0W)I=&5R:6$@./2 G(")R;W<J(B G("TM('!R:6UA<GD@.<V5A<F-H(&-O;'5M
M;B!I<R!)=&5M4W1O8VLN3&%B96P@.*&]T:&5R(&-O;'5M;G,L(&QE9G0@.3E5,
M3"D-"E-%3$5#5"!)=&5M4W1O8VLN3W)D97).;RQ)=&5M4W1O8VLN3&%B9 6P@.
M#0I&4D]-($ET96U3=&]C:PT*("!)3DY%4B!*3TE.($ET96U4:71L97,@.("!/
M3B!)=&5M4W1O8VLN3W)D97).;R ]($ET96U4:71L97,N3W)D97).;PT*("!)
M3DY%4B!*3TE.($ET96U(87)D=V%R92!/3B!)=&5M4W1O8VLN3W)D97).;R ]
M($ET96U(87)D=V%R92Y/<F1E<DYO#0H@.($E.3D52($I/24X@.1E)%151%6%14
M04),12A)=&5M4W1O8VLL("HL($!396%R8VA#<FET97)I82D@.0 5,@.1E-?5$%"
M3$4@.3TX@.1E-?5$%"3$4N6TM%65T@./2!)=&5M4W1O8VLN3W)D97).;PT*(" @.
M("!/4D1%4B!"62!&4U]404),12Y286YK($1%4T,-"BTM(#%S="!T97-T.B T
M(')O=W,@.8F5C87-U92!O;FQY($ET96U3=&]C:R!I<R!R969E<F5N8V5D(&EN
M($92145415A45$%"3$4@.8VQA<W5E#0H-"@.T*+2T@.36]D:69E9"!/<FEG:6YA
M;"!Q=65R>0T*1$5#3$%212! 4V5A<F-H0W)I=&5R:6$@.=F%R8VAA<B@.Q,# I
M#0I3150@.0%-E87)C:$-R:71E<FEA(#T@.)R B<F]W*B(@.)R @.+2T@.5&5S="!W
M:71H(&)O=&@.@.8V]N=&%I;G-T86)L92 F(&9R965T97AT($%.1"!/4B!"971E
M=V5N(&9O<B!E>'!E8W1E9"!R97-U;'1S#0I314Q%0U0@.9&ES=&EN8W0@.92Y/
M<F1E<DYO+"!E+DQA8F5L("TM(&1I<V-T:6YC="!R97%U<FEE9"!T;R!G970@.
M-2!R;W=S+"!S=&EL;"!N;W0@.97AA8W1L>2!S86UE(&%S(&%B;W9 E+BXN#0IF
M<F]M($ET96U3=&]C:R!!4R!E+"!)=&5M5&ET;&5S('0L($ET96U(87)D=V%R
M92!H+ T*(" @.("!F<F5E=&5X='1A8FQE*$ET96U3=&]C:RP@.3&%B96PL($!3
M96%R8VA#<FET97)I82D@.87,@.02P-"B @.(" @.9G)E971E>'1T86)L92A)=&5M
M5&ET;&5S+"!4:71L92P@.0%-E87)C:$-R:71E<FEA*2!A<R!"+ T*(" @.("!F
M<F5E=&5X='1A8FQE*$ET96U(87)D=V%R92P@.<U]P86=E+"! 4V5A<F-H0W)I
M=&5R:6$I(&%S($,-"B @.(" @.("!W:&5R90T*(" @.(" @.(" @.02Y;2T5972 ]
M(&4N3W)D97).;R!A;F0-"B @.(" @.(" @.($(N6TM%65T@./2!T+D]R9&5R3F\@.
M86YD#0H@.(" @.(" @.("!#+EM+15E=(#T@.:"Y/<F1E<DYO#0HM+2 Q<W0@.=&5S
M=#H@.3D]4('1H92!S86UE(&%S(&%B;W9E(&%S('1H:7,@.<75E<GD@.9V5T< R U
M(')O=W,L(&%D9&ET:6]N86P@.<F]W(&ES(")4:&ES(&ES($QA8F5L(# P,#4@.
M;V8@.<F]W(&9I=F4@.26YT97)F86-E(@.T*#0H-"BTM(%1E<W0@.=VET:"!B;W1H
M(&-O;G1A:6YS=&%B;&4@.)B!F<F5E=&5X="!!3D0@.3U(@.0F5T97=E; B!E86-H
M(0T*4T5,14-4(&1I<W1I;F-T(&4N3W)D97).;RP@.92Y,86)E; T*9G)O;2!)
M=&5M4W1O8VL@.05,@.92P@.271E;51I=&QE<R!T+ T*(" @.("!C;VYT86EN<W1A
M8FQE*$ET96U3=&]C:RP@.3&%B96PL("=":6QL>2<I(&%S($$L#0H@.(" @.(&-O
M;G1A:6YS=&%B;&4H271E;51I=&QE<RP@.5&ET;&4L("=3=')A; F=E<B<I(&%S
M($(@.+2T@.;F]W(&=E='1I;F<@.<F5S=6QT<R A($UU<W0@.:&%V92!C;VQU;6XM
M<W!E8VEF:6,@.<V5A<F-H('=O<F0A#0H@.(" @.(" @.=VAE<F4-"B @.(" @.(" @.
M($$N6TM%65T@./2!E+D]R9&5R3F\@.;W(@.+2T@.3U(@.+2T@.9V5N97)A=&5S(&QA
M<F=E<B!R97-U;'0L('-O('5S92!D:7-T:6YC="!E+D]R9&5R3F\L(')E='5R
M;G,@.-2!R;W=S('=I=&@.@.9&ES=&EN8W0-"B @.(" @.(" @.($(N6TM%65T@./2!T
M+D]R9&5R3F\-"@.T*<V5L96-T("H@.9G)O;2!)=&5M4W1O8VL@.=VAE<F4@.8V]N
M=&%I;G,H*BPG0FEL;'DG*2 M+2!R971U<FYS(#$@.<F]W+@.T*#0H-"BTM($UO
M9&EF:65D($]R:6=I;F%L('%U97)Y+"!R96UO=F5D('1H92! 4V5A<F-H0W)I
M=&5R:6$@.=F%R:6%B;&4@.=&\@.=&5S="!W:71H('1A8FQE(&%N9 "!C;VQU;6XM
M<W!E8VEF:6,@.<V5A<F-H('=O<F1S#0I314Q%0U0@.9&ES=&EN8W0@.92Y/<F1E
M<DYO+"!E+DQA8F5L#0IF<F]M($ET96U3=&]C:R!!4R!E+"!)=&5M5&ET;&5S
M('0L($ET96U(87)D=V%R92!H+ T*(" @.("!C;VYT86EN<W1A8FQE*$ET96U3
M=&]C:RP@.3&%B96PL("=":6QL>2<I(&%S($$L#0H@.(" @.(&-O;G1A:6YS=&%B
M;&4H271E;51I=&QE<RP@.5&ET;&4L("=3=')A;F=E<B<I(&%S( $(L#0H@.(" @.
M(&-O;G1A:6YS=&%B;&4H271E;4AA<F1W87)E+"!S7W!A9V4L("=R; W<G*2!A
M<R!##0H@.(" @.(" @.=VAE<F4-"B @.(" @.(" @.($$N6TM%65T@./2!E+D]R9&5R
M3F\@.86YD("TM($]2(#T@.9V5N97)A=&5S(&UU=&EP;&4@.<F]W<RP@.86YD('1H
M97)E9F]R92!N965D<R @.9&ES=&EN8W0@.92Y/<F1E<DYO+@.T*(" @.(" @.(" @.
M0BY;2T5972 ]('0N3W)D97).;R!A;F0-"B @.(" @.(" @.($,N6TM%65T@./2!H
M+D]R9&5R3F\-"BTM(#%S="!T97-T.B P(')O=W,L('-A;64@.87,@.86)O=F4-
9"@.T*+2T@./&5O9CX-"@.T*#0H-"@.T*#0H-"@.``
`
end
|||Here's my stab at this. Note that you have to change the @.separator value
from ' AND' to ' OR ' when you want to change from all the words to some of
the words.
create database test
go
use test
go
create table PolicyMemos
(
PolicyMemosID int not null primary key identity(1,1),
[Name] varchar(200),
ExpirationDate datetime
)
go
create table PolicyMemosFormNumber
(
PMFNID int not null primary key identity(1,1),
PolicyMemosID int not null foreign key references
policyMemos(PolicyMemosID ),
[Description] varchar(200)
)
go
alter View cuPolicyMemosView
as
select [Name], ExpirationDate, [Description]
,[policymemosid]=PolicyMemos.PolicyMemosID,PMFNID
from PolicyMemos, PolicyMemosFormNumber
where PolicyMemos.PolicyMemosID =PolicyMemosFormNumber.PolicyMemosID
go
sp_fulltext_database 'enable'
go
sp_fulltext_catalog 'test','create'
go
declare @.indexname varchar(130)
select @.indexname = name from sysindexes where id=object_id('PolicyMemos')
exec sp_fulltext_table 'PolicyMemos', 'create', 'test', @.indexname
go
sp_fulltext_column 'PolicyMemos','Name','add',1033
go
sp_fulltext_table 'PolicyMemos', 'activate'
go
sp_fulltext_table 'PolicyMemos', 'start_full'
go
declare @.indexname varchar(130)
select @.indexname = name from sysindexes where
id=object_id('PolicyMemosFormNumber')
exec sp_fulltext_table 'PolicyMemosFormNumber', 'create', 'test', @.indexname
go
sp_fulltext_column 'PolicyMemosFormNumber','Description','add',1033
go
sp_fulltext_table 'PolicyMemosFormNumber', 'activate'
go
sp_fulltext_table 'PolicyMemosFormNumber', 'start_full'
go
insert into PolicyMemos ([name], expirationdate) values('New York City',
getdate()-20)
go
insert into PolicyMemos ([name], expirationdate) values('Manhattan',
getdate()-30)
go
insert into PolicyMemos ([name], expirationdate) values('Jersey City',
getdate()-30)
go
insert into PolicyMemos ([name], expirationdate) values('Englishtown',
getdate()-30)
go
insert into PolicyMemosFormNumber (PolicyMemosID , description )
values(1,'study of water table levels')
go
insert into PolicyMemosFormNumber (PolicyMemosID , description )
values(1,'study of soil samples')
go
insert into PolicyMemosFormNumber (PolicyMemosID , description )
values(1,'study of granite formations')
go
insert into PolicyMemosFormNumber (PolicyMemosID , description )
values(2,'study of shale formations')
go
insert into PolicyMemosFormNumber (PolicyMemosID , description )
values(2,'study of marble formations')
go
insert into PolicyMemosFormNumber (PolicyMemosID , description )
values(2,'study of top soil')
go
declare @.strSort varchar(20)
set @.strSort ='Name'
declare @.stmt varchar(2000)
declare @.strSearchLike varchar(200)
declare @.strSearchParsed varchar(200)
declare @.separator varchar(5)
set @.separator=' OR '
set @.strSearchLike ='manhattan soil'
select @.strSearchLike=case when left(@.strSearchLike,1)=char(32) then
substring(@.strSearchLike,2,len(@.strSearchLike)-1) else @.strSearchLike end
set @.strSearchParsed=''
select @.strSearchlike =replace(@.strsearchLike,' ',char(32))
declare @.counter int
declare @.NumberOfWhiteSpaces int
set @.NumberOfWhiteSpaces=len(@.strSearchLike)-len(replace(@.strSearchLike,'
',''))
set @.counter=@.NumberOfWhiteSpaces
while @.counter >0
begin
select @.strSearchParsed=@.strSearchParsed+left(@.strSearchL ike, charindex('
',@.strSearchLike)-1)+ char(34)+@.separator+char(34)
if @.counter<>0
select @.strSearchlike=substring(@.strSearchLike,charindex( '
',@.strSearchLike)+1,len(@.strSearchLike))
select @.counter=@.counter-1
end
select @.strSearchParsed=char(34)+ @.strSearchParsed+
char(32)+@.strSearchLike+char(34)
Set @.stmt = 'Select * From cuPolicyMemosView
RIGHT OUTER JOIN CONTAINSTABLE(PolicyMemos, Name, ' ++char(39)+
@.strSearchParsed + char(39)+ ') AS Search ON
cuPolicyMemosView.PolicyMemosID = Search.[KEY]
LEFT JOIN CONTAINSTABLE(PolicyMemosFormNumber, Description, ' + char(39) +
@.strSearchParsed +char(39) + ') AS Search2 ON
cuPolicyMemosView.PMFNID = Search2.[KEY]
--Where ((DATEDIFF(d,ExpirationDate + 30, GETDATE()) < 0) OR (ExpirationDate
IS NULL))
ORDER BY ' + @.strSort
exec (@.stmt)
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Taylor" <dryrye@.juno.com> wrote in message
news:9c0e390c.0409010657.4167393d@.posting.google.c om...
> I have a sproc that accepts variables from an ASP page to process the
> search for the user. The user can choose 3 methods:
> 1.)Any Occurence
> 2.)Word/Phrase
> 3.)Exact Word/Phrase
> The sproc goes through some if statments to decide which sql statement
> to execute based on the method chosen by the user. The user can also
> type in a keyword (of course) but can apply the key word to one column
> or two columns. The columns are Name and Description (even though
> they may not be Name or Description in the database...just a generic
> name to use). Since the sproc is not my problem i'll just give a
> little exampe of how it works instead of posting all that code, heh.
> if method = 1
> Begin
> if name and description = 1
> Begin
> execute sql
> End
> if name = 1
> Begin
> execute sql
> End
> if description = 1
> Begin
> execute sql
> End
> End
> And the code would be the same for the next two methods. My problem
> is with the sql statment that executes when the name and description
> are equal to 1. I create views and usually do a join between the view
> and the table i want to search on. Then i return the results and that
> works fine. The problem with this search is that the Name column is
> in one table and the Description in another. I still need the view
> cause it contains the other 15 tables of info i use to display and
> filter data. But i don't know how to search on both tables and get
> the resutls i want. Here is my code so far...
> Set @.stmt = 'Select * From cuPolicyMemosView
> RIGHT OUTER JOIN CONTAINSTABLE(PolicyMemos, Name, ' + "'" + '"' +
> @.strSearchLike + '"' + "'" + ') AS Search ON
> cuPolicyMemosView.PolicyMemosID = Search.[KEY]
> LEFT JOIN CONTAINSTABLE(PolicyMemosFormNumber, Description, ' + "'" +
> '"' + @.strSearchLike + '"' + "'" + ') AS Search2 ON
> cuPolicyMemosView.PMFNID = Search2.[KEY] Where ((DATEDIFF(d,
> ExpirationDate + 30, GETDATE()) < 0) OR (ExpirationDate IS NULL))
> ORDER BY ' + @.strSort
> Exec(@.stmt)
> I can get the Name just fine when i do a search. But i can't get the
> Description. I've tried RIGHT, LEFT, FULL joins...can't get the
> Description. I've heard that the only way might be to put the info i
> get back from each table seperatley and the put that info in a temp
> table and search off of that. That might be a possiblity, but i'm not
> 100% sure how to do it, heh. I would rather just have a way to alter
> my current SQL statment, but if that can't be done then i'm open to
> any other suggestions. I hope this all makes sense, heh, i know its a
> little unclear...but if anyone has dealt with the joined tables search
> problem and knows a good way around that problem in general then thats
> all the help i need. Thanks in advance.
> Taylor
|||note that in this statement select @.strSearchlike =replace(@.strsearchLike,'
',char(32))
I am replacing two consecutive white spaces with a single white space.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:ezWC7UPkEHA.2412@.TK2MSFTNGP15.phx.gbl...
> Here's my stab at this. Note that you have to change the @.separator value
> from ' AND' to ' OR ' when you want to change from all the words to some
of
> the words.
> create database test
> go
> use test
> go
> create table PolicyMemos
> (
> PolicyMemosID int not null primary key identity(1,1),
> [Name] varchar(200),
> ExpirationDate datetime
> )
> go
> create table PolicyMemosFormNumber
> (
> PMFNID int not null primary key identity(1,1),
> PolicyMemosID int not null foreign key references
> policyMemos(PolicyMemosID ),
> [Description] varchar(200)
> )
> go
> alter View cuPolicyMemosView
> as
> select [Name], ExpirationDate, [Description]
> ,[policymemosid]=PolicyMemos.PolicyMemosID,PMFNID
> from PolicyMemos, PolicyMemosFormNumber
> where PolicyMemos.PolicyMemosID =PolicyMemosFormNumber.PolicyMemosID
> go
> sp_fulltext_database 'enable'
> go
> sp_fulltext_catalog 'test','create'
> go
> declare @.indexname varchar(130)
> select @.indexname = name from sysindexes where id=object_id('PolicyMemos')
> exec sp_fulltext_table 'PolicyMemos', 'create', 'test', @.indexname
> go
> sp_fulltext_column 'PolicyMemos','Name','add',1033
> go
> sp_fulltext_table 'PolicyMemos', 'activate'
> go
> sp_fulltext_table 'PolicyMemos', 'start_full'
> go
> declare @.indexname varchar(130)
> select @.indexname = name from sysindexes where
> id=object_id('PolicyMemosFormNumber')
> exec sp_fulltext_table 'PolicyMemosFormNumber', 'create', 'test',
@.indexname
> go
> sp_fulltext_column 'PolicyMemosFormNumber','Description','add',1033
> go
> sp_fulltext_table 'PolicyMemosFormNumber', 'activate'
> go
> sp_fulltext_table 'PolicyMemosFormNumber', 'start_full'
> go
> insert into PolicyMemos ([name], expirationdate) values('New York City',
> getdate()-20)
> go
> insert into PolicyMemos ([name], expirationdate) values('Manhattan',
> getdate()-30)
> go
> insert into PolicyMemos ([name], expirationdate) values('Jersey City',
> getdate()-30)
> go
> insert into PolicyMemos ([name], expirationdate) values('Englishtown',
> getdate()-30)
> go
> insert into PolicyMemosFormNumber (PolicyMemosID , description )
> values(1,'study of water table levels')
> go
> insert into PolicyMemosFormNumber (PolicyMemosID , description )
> values(1,'study of soil samples')
> go
> insert into PolicyMemosFormNumber (PolicyMemosID , description )
> values(1,'study of granite formations')
> go
> insert into PolicyMemosFormNumber (PolicyMemosID , description )
> values(2,'study of shale formations')
> go
> insert into PolicyMemosFormNumber (PolicyMemosID , description )
> values(2,'study of marble formations')
> go
> insert into PolicyMemosFormNumber (PolicyMemosID , description )
> values(2,'study of top soil')
> go
>
> declare @.strSort varchar(20)
> set @.strSort ='Name'
> declare @.stmt varchar(2000)
> declare @.strSearchLike varchar(200)
> declare @.strSearchParsed varchar(200)
> declare @.separator varchar(5)
> set @.separator=' OR '
> set @.strSearchLike ='manhattan soil'
> select @.strSearchLike=case when left(@.strSearchLike,1)=char(32) then
> substring(@.strSearchLike,2,len(@.strSearchLike)-1) else @.strSearchLike end
> set @.strSearchParsed=''
> select @.strSearchlike =replace(@.strsearchLike,' ',char(32))
> declare @.counter int
> declare @.NumberOfWhiteSpaces int
> set @.NumberOfWhiteSpaces=len(@.strSearchLike)-len(replace(@.strSearchLike,'
> ',''))
> set @.counter=@.NumberOfWhiteSpaces
> while @.counter >0
> begin
> select @.strSearchParsed=@.strSearchParsed+left(@.strSearchL ike, charindex('
> ',@.strSearchLike)-1)+ char(34)+@.separator+char(34)
> if @.counter<>0
> select @.strSearchlike=substring(@.strSearchLike,charindex( '
> ',@.strSearchLike)+1,len(@.strSearchLike))
> select @.counter=@.counter-1
> end
> select @.strSearchParsed=char(34)+ @.strSearchParsed+
> char(32)+@.strSearchLike+char(34)
> Set @.stmt = 'Select * From cuPolicyMemosView
> RIGHT OUTER JOIN CONTAINSTABLE(PolicyMemos, Name, ' ++char(39)+
> @.strSearchParsed + char(39)+ ') AS Search ON
> cuPolicyMemosView.PolicyMemosID = Search.[KEY]
> LEFT JOIN CONTAINSTABLE(PolicyMemosFormNumber, Description, ' + char(39)
+
> @.strSearchParsed +char(39) + ') AS Search2 ON
> cuPolicyMemosView.PMFNID = Search2.[KEY]
> --Where ((DATEDIFF(d,ExpirationDate + 30, GETDATE()) < 0) OR
(ExpirationDate
> IS NULL))
> ORDER BY ' + @.strSort
> exec (@.stmt)
>
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> "Taylor" <dryrye@.juno.com> wrote in message
> news:9c0e390c.0409010657.4167393d@.posting.google.c om...
>

Problem with full text search

Hello!
First of all sorry for my poor english... I write from Italy...
I'm using full-text search with SQL Express 2005 and I can't undestand a
lot...
This is the situation:
- I'm using Italian word breaker;
- I've removed all words from my noise word list (noiseITA.txt)
- I've these entries in my thesaurus file (tsITA.xml):
<expansion>
_{REGGIO NELL'EMILIA}
_{REGGIO EMILIA}
_{REGGIO DI EMILIA}
</expansion>
- I have these field's value in my records:
id location
-- --
1REGGIO EMILIA
2REGGIO-EMILIA
3REGGIO NELL'EMILIA
4REGGIO DI EMILIA
Case 1 (without thesaurus)
SELECT id FROM table1 WHERE CONTAINS(location , '"REGGIO-EMILIA"')
Result set: 2
- Why not "REGGIO EMILIA" too?
Case 2 (without thesaurus)
SELECT id FROM table1 WHERE CONTAINS(location , '"REGGIO EMILIA"')
Result set: 1, 3
- Why "REGGIO NELL'EMILIA" too?
Case 3 (without thesaurus)
SELECT id FROM table1 WHERE CONTAINS(location , '"REGGIO NELL''EMILIA"')
Result set: 1, 3
- Why "REGGIO EMILIA" too?
Case 4 (without thesaurus)
SELECT id FROM table1 WHERE CONTAINS(location , '"REGGIO DI EMILIA"')
Result set: 4
Case 5 (with thesaurus)
SELECT id FROM table1 WHERE CONTAINS(location , 'FORMSOF
(THESAURUS,"REGGIO-EMILIA")',LANGUAGE 'Italian')
Result set: 2
- Why only "REGGIO-EMILIA" ?
Case 6 (with thesaurus)
SELECT id FROM table1 WHERE CONTAINS(location , 'FORMSOF (THESAURUS,"REGGIO
EMILIA")',LANGUAGE 'Italian')
SELECT id FROM table1 WHERE CONTAINS(location , 'FORMSOF (THESAURUS,"REGGIO
NELL''EMILIA")',LANGUAGE 'Italian')
SELECT id FROM table1 WHERE CONTAINS(location , 'FORMSOF (THESAURUS,"REGGIO
DI EMILIA")',LANGUAGE 'Italian')
Result set: 1, 3, 4
- Why not "REGGIO-EMILIA" too?
I can't understand... is the hyphen (-) a word breaker sign?
What shall I do to obtain all the four records when I search for each of the
locations above ?
It doesn't work fine if I add "REGGIO-EMILIA" in the thesaurus file... :-(
Thank you very much!!!!
Paola
YOUR PROBLEM IS THAT YOUR CONTENT IS ALL UPPERCASE. THE ITALIAN WORD
BREAKER WILL BREAK REGGIO-EMILIA AS ONE WORD. HOWEVER IT WILL BREAK
Reggio-Emilia AS TWO WORDS. HAVE A LOOK AT THIS:
create database italian1
go
use italian1
GO
create fulltext catalog italian as default
go
create table italian (id int not null identity constraint italianPK primary
key, location varchar(30))
GO
create fulltext index on italian(location language italian) key index
italianpk
GO
insert into italian (location) values('REGGIO EMILIA')
insert into italian (location) values('REGGIO-EMILIA')
insert into italian (location) values('REGGIOEMILIA')
insert into italian (location) values('REGGIO NELL''EMILIA')
insert into italian (location) values('REGGIO DI EMILIA')
insert into italian (location) values('Reggio Emilia')
insert into italian (location) values('Reggio-Emilia')
insert into italian (location) values('Reggioemilia')
insert into italian (location) values('Reggio Nell''Emilia')
insert into italian (location) values('Reggio nell''Emilia')
insert into italian (location) values('Reggio Di Emilia')
insert into italian (location) values('Reggio di Emilia')
GO
SELECT id FROM italian WHERE CONTAINS(location , '"REGGIO-EMILIA"')
Result set: 2
-- Why not "REGGIO EMILIA" too?
--all uppercase will consider the word as one token
SELECT id FROM italian WHERE CONTAINS(location , '"Reggio-Emilia"')
--returns, 1, 3, 5, 6, 7, 8, 9, & 10
Case 2 (without thesaurus)
SELECT id,location FROM italian WHERE CONTAINS(location , '"REGGIO EMILIA"')
Result set: 1, 3
-- Why "REGGIO NELL'EMILIA" too? --nell is a noise word and as such is
discarded in the search
SELECT id FROM italian WHERE CONTAINS(location , '"Reggio Emilia"')
--returns, 1, 3, 6, 7, & 10
Case 3 (without thesaurus)
SELECT id,location FROM italian WHERE CONTAINS(location , '"REGGIO
NELL''EMILIA"')
Result set: 1, 3
-- Why "REGGIO EMILIA" too? nella is a noise word and thrown away
Case 4 (without thesaurus)
SELECT id,location FROM italian WHERE CONTAINS(location , '"REGGIO DI
EMILIA"')
Result set: 4
Case 5 (with thesaurus)
SELECT id FROM italian WHERE CONTAINS(location , 'FORMSOF
(THESAURUS,"REGGIO-EMILIA")',LANGUAGE 'Italian')
Result set: 2
-- Why only "REGGIO-EMILIA" ? Cause its uppper case, try this
SELECT id FROM italian WHERE CONTAINS(location , 'FORMSOF
(THESAURUS,"Reggio-Emilia")',LANGUAGE 'Italian')
--1, 3, 5, 6, 7, 8, 9, & 10 are returned
Case 6 (with thesaurus)
SELECT id FROM italian WHERE CONTAINS(location , 'FORMSOF (THESAURUS,"REGGIO
EMILIA")',LANGUAGE 'Italian')
SELECT id FROM italian WHERE CONTAINS(location , 'FORMSOF (THESAURUS,"REGGIO
NELL''EMILIA")',LANGUAGE 'Italian')
-- I can't figure out case 6 however
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"paola" <paola@.discussions.microsoft.com> wrote in message
news:FF634D5E-B3B9-412A-89AC-CA4C10F1A364@.microsoft.com...
> Hello!
> First of all sorry for my poor english... I write from Italy...
> I'm using full-text search with SQL Express 2005 and I can't undestand a
> lot...
> This is the situation:
> - I'm using Italian word breaker;
> - I've removed all words from my noise word list (noiseITA.txt)
> - I've these entries in my thesaurus file (tsITA.xml):
> <expansion>
> _{REGGIO NELL'EMILIA}
> _{REGGIO EMILIA}
> _{REGGIO DI EMILIA}
> </expansion>
>
> - I have these field's value in my records:
> id location
> -- --
> 1 REGGIO EMILIA
> 2 REGGIO-EMILIA
> 3 REGGIO NELL'EMILIA
> 4 REGGIO DI EMILIA
>
> Case 1 (without thesaurus)
> SELECT id FROM table1 WHERE CONTAINS(location , '"REGGIO-EMILIA"')
> Result set: 2
> - Why not "REGGIO EMILIA" too?
> Case 2 (without thesaurus)
> SELECT id FROM table1 WHERE CONTAINS(location , '"REGGIO EMILIA"')
> Result set: 1, 3
> - Why "REGGIO NELL'EMILIA" too?
> Case 3 (without thesaurus)
> SELECT id FROM table1 WHERE CONTAINS(location , '"REGGIO NELL''EMILIA"')
> Result set: 1, 3
> - Why "REGGIO EMILIA" too?
> Case 4 (without thesaurus)
> SELECT id FROM table1 WHERE CONTAINS(location , '"REGGIO DI EMILIA"')
> Result set: 4
> Case 5 (with thesaurus)
> SELECT id FROM table1 WHERE CONTAINS(location , 'FORMSOF
> (THESAURUS,"REGGIO-EMILIA")',LANGUAGE 'Italian')
> Result set: 2
> - Why only "REGGIO-EMILIA" ?
> Case 6 (with thesaurus)
> SELECT id FROM table1 WHERE CONTAINS(location , 'FORMSOF
> (THESAURUS,"REGGIO
> EMILIA")',LANGUAGE 'Italian')
> SELECT id FROM table1 WHERE CONTAINS(location , 'FORMSOF
> (THESAURUS,"REGGIO
> NELL''EMILIA")',LANGUAGE 'Italian')
> SELECT id FROM table1 WHERE CONTAINS(location , 'FORMSOF
> (THESAURUS,"REGGIO
> DI EMILIA")',LANGUAGE 'Italian')
> Result set: 1, 3, 4
> - Why not "REGGIO-EMILIA" too?
>
> I can't understand... is the hyphen (-) a word breaker sign?
> What shall I do to obtain all the four records when I search for each of
> the
> locations above ?
> It doesn't work fine if I add "REGGIO-EMILIA" in the thesaurus file...
> :-(
> Thank you very much!!!!
> Paola
>
>
|||Ok, thanks a lot Hilary...
I'm doing some tests to undestand... and so... why the query:
SELECT * FROM italian WHERE CONTAINS(location ,
'"Reggio Emilia"',LANGUAGE 'Italian')
doesn't return "Reggio di Emilia" ?
And :
SELECT * FROM italian WHERE CONTAINS(location ,
'"Reggio di Emilia"',LANGUAGE 'Italian')
returns only:
REGGIO DI EMILIA
Reggio Di Emilia
Reggio di Emilia
"di" is a noise word, isn't it?...
And so I suppose that "di" is ignored in search string and in field value
too. Why not?
Thanks a lot...
Paola
"Hilary Cotter" wrote:

> YOUR PROBLEM IS THAT YOUR CONTENT IS ALL UPPERCASE. THE ITALIAN WORD
> BREAKER WILL BREAK REGGIO-EMILIA AS ONE WORD. HOWEVER IT WILL BREAK
> Reggio-Emilia AS TWO WORDS. HAVE A LOOK AT THIS:
> create database italian1
> go
> use italian1
> GO
> create fulltext catalog italian as default
> go
> create table italian (id int not null identity constraint italianPK primary
> key, location varchar(30))
> GO
> create fulltext index on italian(location language italian) key index
> italianpk
> GO
> insert into italian (location) values('REGGIO EMILIA')
> insert into italian (location) values('REGGIO-EMILIA')
> insert into italian (location) values('REGGIOEMILIA')
> insert into italian (location) values('REGGIO NELL''EMILIA')
> insert into italian (location) values('REGGIO DI EMILIA')
> insert into italian (location) values('Reggio Emilia')
> insert into italian (location) values('Reggio-Emilia')
> insert into italian (location) values('Reggioemilia')
> insert into italian (location) values('Reggio Nell''Emilia')
> insert into italian (location) values('Reggio nell''Emilia')
> insert into italian (location) values('Reggio Di Emilia')
> insert into italian (location) values('Reggio di Emilia')
> GO
> SELECT id FROM italian WHERE CONTAINS(location , '"REGGIO-EMILIA"')
> Result set: 2
> -- Why not "REGGIO EMILIA" too?
> --all uppercase will consider the word as one token
> SELECT id FROM italian WHERE CONTAINS(location , '"Reggio-Emilia"')
> --returns, 1, 3, 5, 6, 7, 8, 9, & 10
> Case 2 (without thesaurus)
> SELECT id,location FROM italian WHERE CONTAINS(location , '"REGGIO EMILIA"')
> Result set: 1, 3
> -- Why "REGGIO NELL'EMILIA" too? --nell is a noise word and as such is
> discarded in the search
> SELECT id FROM italian WHERE CONTAINS(location , '"Reggio Emilia"')
> --returns, 1, 3, 6, 7, & 10
> Case 3 (without thesaurus)
> SELECT id,location FROM italian WHERE CONTAINS(location , '"REGGIO
> NELL''EMILIA"')
> Result set: 1, 3
> -- Why "REGGIO EMILIA" too? nella is a noise word and thrown away
> Case 4 (without thesaurus)
> SELECT id,location FROM italian WHERE CONTAINS(location , '"REGGIO DI
> EMILIA"')
> Result set: 4
> Case 5 (with thesaurus)
> SELECT id FROM italian WHERE CONTAINS(location , 'FORMSOF
> (THESAURUS,"REGGIO-EMILIA")',LANGUAGE 'Italian')
> Result set: 2
> -- Why only "REGGIO-EMILIA" ? Cause its uppper case, try this
> SELECT id FROM italian WHERE CONTAINS(location , 'FORMSOF
> (THESAURUS,"Reggio-Emilia")',LANGUAGE 'Italian')
> --1, 3, 5, 6, 7, 8, 9, & 10 are returned
> Case 6 (with thesaurus)
> SELECT id FROM italian WHERE CONTAINS(location , 'FORMSOF (THESAURUS,"REGGIO
> EMILIA")',LANGUAGE 'Italian')
> SELECT id FROM italian WHERE CONTAINS(location , 'FORMSOF (THESAURUS,"REGGIO
> NELL''EMILIA")',LANGUAGE 'Italian')
> -- I can't figure out case 6 however
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "paola" <paola@.discussions.microsoft.com> wrote in message
> news:FF634D5E-B3B9-412A-89AC-CA4C10F1A364@.microsoft.com...
>
>
|||Because it knows there is something in-between. Try this
insert into italian (location) values('Reggio XX Emilia')
and now this
SELECT * FROM italian WHERE CONTAINS(location ,
'"Reggio di Emilia"',LANGUAGE 'Italian')
you will see the hit to
Reggio di Emilia
and
Reggio XX Emilia
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"paola" <paola@.discussions.microsoft.com> wrote in message
news:7319F1AA-1CFC-42BF-9A70-F81C38E70840@.microsoft.com...[vbcol=seagreen]
> Ok, thanks a lot Hilary...
> I'm doing some tests to undestand... and so... why the query:
> SELECT * FROM italian WHERE CONTAINS(location ,
> '"Reggio Emilia"',LANGUAGE 'Italian')
> doesn't return "Reggio di Emilia" ?
> And :
> SELECT * FROM italian WHERE CONTAINS(location ,
> '"Reggio di Emilia"',LANGUAGE 'Italian')
> returns only:
> REGGIO DI EMILIA
> Reggio Di Emilia
> Reggio di Emilia
> "di" is a noise word, isn't it?...
> And so I suppose that "di" is ignored in search string and in field value
> too. Why not?
> Thanks a lot...
> Paola
>
>
>
> "Hilary Cotter" wrote: