sql server full text search: *also posted on microsoft.public.sqlserver*

Hello,

I am trying to do a text compare (case sensitive compare in sql server
2000) on a column of type text with a variable of type text. This
compare doesn't seem to be working as coded in the below code
segment. It worked when the variables were of type VARCHAR but that
proved to be too small. The situation is a user is importing a file
where a field in a record is about 27000 characters long, and if the
file is reimported, (or an identical record is found) we dont want a
new record added to the table. The table is supposed to contain only
unique records.

Here is the code from the stored procedure:
CREATE PROCEDURE dbo.sp_CheckUniqueResults (@Server VARCHAR(100),
@Type VARCHAR(100), @Policy VARCHAR(100), @Check VARCHAR(100), @Detail
TEXT) AS
SET NOCOUNT ON
DECLARE @RCnt INT

BEGIN
SELECT * FROM dbo.Scan_UniqueFindings
WHERE [Server] = @Server AND [Policy] = @Policy AND [Check] = @Check
AND [Detail] LIKE @Detail COLLATE SQL_Latin1_General_CP1_CS_AS

--Store the number of records found
SELECT @RCnt = @@ROWCOUNT

--Return the rows affected (to be used by the MSACCESS application)
Return @RCnt
END
GO

Thanks for any and all help!
Ben

Please, explain what "doesn't seem to be working" means. Are you getting
errors? Are you getting no or unexpected relsults?


ML

---
Matija Lah, SQL Server MVP

[quoted text, click to view]

Matija,

I was getting unexpected results. The stored procedure was returning
a "0" which for my code ment the data sent was not found in the unique
table. The application would then add the data to the table, thereby
creating duplicate records. The code worked flawlessly when the
column was a varchar(8000). I cannot seem to find the 'hole' im my
logic.

Thanks for any and all help.

Ben

How have you verified that duplicates exist?


ML

---
Matija Lah, SQL Server MVP

[quoted text, click to view]

The way my code is set up, is that when a 0 is returned (ie no
corresponding records found in the unique table), the code (in
msaccess) will add the new records. I have run this code with the
same piece of input data many times, and each time certain records are
never found in the unique table and inserted into the unique table,
leading to duplicate records.

Let's start by optimizing your procedure:

1) I'll rename it because prefixing user procedures with sp_ is simply not
good practice (the database engine starts looking for procedures with names
that start with "sp_" in the master database which means that a procedure
with the same name in the current database may never be executed).

CREATE PROCEDURE dbo.CheckUniqueResults
(
@Server VARCHAR(100)
,@Type VARCHAR(100)
,@Policy VARCHAR(100)
,@Check VARCHAR(100)
,@Detail TEXT
)
AS
BEGIN
SET NOCOUNT ON
DECLARE @RCnt INT

if exists (
SELECT *
FROM dbo.Scan_UniqueFindings
WHERE (dbo.Scan_UniqueFindings.[Server] = @Server)
AND (dbo.Scan_UniqueFindings.[Policy] = @Policy)
AND (dbo.Scan_UniqueFindings.[Check] = @Check)
AND (dbo.Scan_UniqueFindings.[Detail] LIKE @Detail COLLATE
SQL_Latin1_General_CP1_CS_AS)
)
begin
set @RCnt = 1 -- replace with select count(*)... if you need the number
of rows
end
else
begin
set @RCnt = 0
end

--Return the rows affected (to be used by the MSACCESS application)
return @RCnt
END
GO

Try it and tell us how it worked.


ML

---
Matija Lah, SQL Server MVP

Thanks for the reply.

I tried your code and it seems to continue to fail on the same
records. I have pasted a copy of the detail field to hopefully give
you/me an insight into what may be causing this. I dont see any
special characters within the code.

Here is what it looks like in my database (each Machine.... record is
a single line, ie. 3 lines total)
MACHINE=xxx LOGNAME=xxx REQUEST=yes SENDFILES=yes COMMANDS=ALL READ=/
usr/spool/uucppublic WRITE=/usr/spool/uucppublic:/u/fcci1/rbin:/code/
ci
MACHINE=xxxx LOGNAME=xxxx REQUEST=yes SENDFILES=yes COMMANDS=ALL READ=/
usr/spool/uucppublic WRITE=/usr/spool/uucppublic:/u/fcci1/rbin:/code/
ci
MACHINE=xxxxx LOGNAME=xxxxx REQUEST=yes SENDFILES=yes COMMANDS=ALL
READ=/usr/spool/uucppublic WRITE=/usr/spool/uucppublic:/u/fccil/rbin:/
code/ci

Here is the data being sent to the stored procedure (each line below
is a single line, ie. 12 linee)
MACHINE=xxx LOGNAME=xxx \
REQUEST=yes SENDFILES=yes COMMANDS=ALL \
READ=/usr/spool/uucppublic \
WRITE=/usr/spool/uucppublic:/u/fcci1/rbin:/code/ci
MACHINE=xxxx LOGNAME=xxxx \
REQUEST=yes SENDFILES=yes COMMANDS=ALL \
READ=/usr/spool/uucppublic \
WRITE=/usr/spool/uucppublic:/u/fcci1/rbin:/code/ci
MACHINE=xxxxx LOGNAME=xxxxx \
REQUEST=yes SENDFILES=yes COMMANDS=ALL \
READ=/usr/spool/uucppublic \
WRITE=/usr/spool/uucppublic:/u/fccil/rbin:/code/ci

My code is not doing any formatting to the data so I am not sure why
there is a change between what is being sent to the table and what is
in the table. Maybe that has something to do with this problem?
Could SQL be doing some formatting? Is "\" with a line feed a speical
character?

Thanks for any and all help!
Ben

What character(s) are you using to separate lines of text? IOW: make sure
both CR as well as LF are present.


ML

---
Matija Lah, SQL Server MVP

I add to the strings: chr(13) & chr(10) to add CR and LFs.

This must be a problem with how MSACCESS is sending the data to the
stored procedure beacuse I ran a test using both formats (copy-paste
into query analyzer) and both returned the correct results. When I do
the same through MSACCESS it returns the wrong results.