sql server full text search: Roy,
What version of SQL Server (7.0 or 2000) are you using and on what OS
platform is it installed?
Could you post the full output of: SELECT @@version

I'm assuming that you want to use SQL Server's Full-text Search (FTS)
features to search the contents of these RTF files as you've posted your
VB/ADO questions in the fulltext newsgroup. Correct?

Did you chose the right field type to store it? That depend upon the version
of SQL Server you're using and if you want to use FTS...
Regards,
John



[quoted text, click to view]

Hi Roy,
Good, you're using SQL Server 2000 as that means you can use a column
defined with the IMAGE datatype and a "file extension" column to (assuming)
FT Index and Search as well as view the RTF documents directly and with
correct formatting from SQL Server. The latter I expect you should be able
to handle with one or more of the below KB articles, and for the former, you
can use the following code to import and FT Index the RTF documents.
Firstly, you will need to download the RTF IFilter (Indexing Filter) from
http://www.microsoft.com/sharepoint/server/techinfo/reskit/rtf_filter.asp
and install it.

Secondly, you can use the following T-SQL code to create a table for your
RTF documents and use TextCopy.exe (shipped with SQL 2000) as the upload
method in this example. Note, you can also use ADO Stream or other methods
referenced in the KB's:

TextCopy.exe as well as ADO Stream are both methods that have been used with
successfully loading files into SQL Server and then FT Indexing them. Also,
a key concern is the "file extension" and the "type" column must be defined
as char(3) or varchar(4) or sysname in order for the MSSearch to correctly
recognize the document type. Below is some code example of both TextCopy.exe
as well as ADO Stream:

CREATE TABLE FTSTable (
KeyCol int IDENTITY (1,1) NOT NULL
CONSTRAINT FTSTable_IDX PRIMARY KEY CLUSTERED,
TextCol text NULL,
ImageCol image NULL,
-- Note: can ONLY be can be char(3), sysname or varchar(4)
ExtCol char(3) NULL,
TimeStampCol timestamp NULL
) ON [PRIMARY]
go
-- Insert data...
-- Note: Initalizing IMAGE column with 0xFFFFFFFF for use with TextCopy.exe
-- also note populating the ExtCol column with 'rtf' for valid RTF files
INSERT FTSTable values('Test TEXT Data for row 1', 0xFFFFFFFF, 'rtf', NULL)
go
declare @query varchar(200)
-- Insert <your_RTF_doc_file> here
set @query = '<path_for_textcopy>\Binn\textcopy /s '+@@servername+' /u sa /p
/d pubs /t FTSTable /c ImageCol /f <your_RTF_doc_file> /i /k 5000 /w "where
KeyCol=1"'
print @query
exec master..xp_cmdshell @query
go
-- Create FT Catalog, FT Index on the image column and run a Full Population
and review the event log.
-- SQL Server 2000 Books Online will have more info or see KB article:
-- 240867 (Q240867) "INF: How to Move, Copy, and Backup Full-Text Catalog
Folders and Files"

'-- Ado Stream method:
'<code start>
Sub Usage
WScript.Echo
WScript.Echo "Usage: ADOStream <KeyCol_value> <C:\File_Name>"
WScript.Echo "Example: cscript //nologo ADOStream.vbs 5 C:\Non_Accent.doc"
WScript.Echo
end Sub
Sub Main
if WScript.Arguments.Count = 1 then
if WScript.Arguments(0) = "/?" or WScript.Arguments(0) = "-?" then
Usage
WScript.Quit(1)
end if
end if
' Open a connection.
set cn = CreateObject ("ADODB.Connection")
cn.Open "Trusted_Connection=yes;server=.;provider=sqloledb"
Set rs = CreateObject ("ADODB.Recordset")
' Write file into FTSTable.
rs.Open "select KeyCol, ImageCol from TestFTS.dbo.FTSTable where KeyCol =
" & WScript.Arguments(0), cn, 1, 3
' adOpenKeyset=1, adLockOptimistic=3
Set mstream = CreateObject ("ADODB.Stream")
mstream.Type = 1 ' adTypeBinary=1
mstream.Open
mstream.LoadFromFile WScript.Arguments(1) ' "<your_RTF_doc_file>"
rs.Fields ("ImageCol").Value = mstream.Read
rs.Update
rs.Close
cn.Close
End Sub
call Main
'<code end>


Copy Text or Image into or out of SQL Server
http://www.databasejournal.com/features/mssql/article.php/1443521

How To Insert A Text Document Into A Text Column using the Texcopy.exe
program
http://www.allisonmitchell.com/Articles/TextCopy.htm
309158 (Q309158) HOW TO: Read and Write BLOB Data by Using ADO.NET with C#
http://support.microsoft.com/default.aspx?scid=kb;EN-US;309158

308042 (Q308042) HOW TO: Read and Write BLOB Data by Using ADO.NET with
VB.NET
http://support.microsoft.com/default.aspx?scid=kb;EN-US;308042

326502 (Q326502) HOW TO: Read and Write BLOB Data by Using ADO.NET Through
ASP.NET
http://support.microsoft.com/?id=326502

242875 How to Use WinWord to Convert RTF Files to Windows DOC Files
http://support.microsoft.com/default.aspx?scid=kb;en-us;242875

Regards,
John



[quoted text, click to view]

RTF's should be stored using char/varchar/text or image. This is probably
why you are running into this problem.

RTFs contain text and formatting information. It should be plain to the
naked eye what you are looking at. If you store any images or objects in the
rtf these will appear in binary format.

[quoted text, click to view]

Hello there

I have database designed on sql server

The application is VB or Access ADO.

On the application i have rich text control.

On the rich text control i type charecters or i Load RTF file.

If I store the Object.Text on nvarchar the letters are stored but none of
the special design of the rtf

If i stor the Object balue i see somthing don't realy understand (Maybe the
rich text code) and when i load it by placing the code on the rich text the
code is showed and not what i stored.

Do i choose the right field type to store it?

Well john

I use SQL server 2000 with Visual Studio 6

It seems that the problem is on Sql server by choosing the field type.

Maybe the problem is on the vb application

can you help me on it?
[quoted text, click to view]