Accessing directly to a Full Index Catalog

Hi,
Suppose that I have a Full Text catalog that indexes 3 tables. I want to search from an asp page for some text in the tables. How can I perform this search directly in the catalog ? Right now I must perform 3 queries, one for each table, but then I must
store the parcial results and in the end order by rank the retrieved results. This doesn′t seem to be a good solution. Does any one know if I can do this with the ixsso object (Query and Util)?. Any ideas ?
Thanks in advance.
Bart.
Bartolomeu,
Unfortunately, direct access to the FT Catalog files is not supported in SQL
Server 2000. However it has been reported publicly by Microsoft that it will
be supported via a command line utility in SQL Server 2005 (Yukon).
For SQL Server 2000, you must rely on using the FTS predicates of CONTAINS*
or FREETEXT*.
Regards,
John
"Bartolomeu" <Bartolomeu@.discussions.microsoft.com> wrote in message
news:90467F8E-3CFD-430A-A155-4BA09806F7CB@.microsoft.com...
> Hi,
> Suppose that I have a Full Text catalog that indexes 3 tables. I want to
search from an asp page for some text in the tables. How can I perform this
search directly in the catalog ? Right now I must perform 3 queries, one for
each table, but then I must store the parcial results and in the end order
by rank the retrieved results. This doesnt seem to be a good solution.
Does any one know if I can do this with the ixsso object (Query and Util)?.
Any ideas ?
> Thanks in advance.
> Bart.

Access to TEXT column

Hi,

I have a table with a TEXT column and this column contains an XML document. I'm developing a TSQL stored procedure which reads the content of this column and accesses the values in the XML elements but I have a lot of problems...

1) How can I read the text column and store the value in a variable? I tried

declare @.a varchar(2048)
set @.a = (SELECT TEXT_COLUMN FROM MY_TABLE)

but it returns

Server: Msg 279, Level 16, State 3, Line 2
The text, ntext, and image data types are invalid in this subquery or aggregate expression.

I tried also with the READTEXT function but I just can't find how to store data read in a variable...

2) which length should I use for the VARCHAR variable which will store the data? Is it possible not to specify a length with SQL Server 7 or 2000?

Thanks!

Andrea

In SQL Server 7/2000 local variables cannot have a text data type. My advice would be if you know the length of the xml data is not going to be greater than 4000 chars (aprox.), use an NVARCHAR, that is going to allow you to be more flexible with your code.

Hope this helps,

Roberto Hernández-Pou
http://community.rhpconsulting.net

|||

you can try this

declare @.a varchar(8000)

set @.a = (SELECT convert(varchar(8000),text1) FROM texttest where text1like'some%' )

select @.a

You 'll be able to use text column in procedures but it has to converted to an accepted datatype in procedures

|||Just keep in mind that a decent sized XML document can easily go past 8000 characters causing any extra data to be truncated.|||

Hi,

thanks Gopi, Roberto and Whitney! Useful answers and comments!

Andrea

Access Text Within A SQL Binary Field

Is there any way to access the text within a SQL Binary object? For example
if I wanted to count the number of words within a Word document that had bee
n
added to a binary field in the database.
I figure the IFilters used by the Full-Text indexing service must do
something like this in order to create it's keyword index database.Look for: textptr, readtext, writetext, updatetext, textvalid and
sp_invalidate_textptr in the BOL.
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF
"Anthony" <Anthony@.discussions.microsoft.com> wrote in message
news:3CFF2482-3A52-40E1-B629-3873ACAEDC41@.microsoft.com...
> Is there any way to access the text within a SQL Binary object? For
> example
> if I wanted to count the number of words within a Word document that had
> been
> added to a binary field in the database.
> I figure the IFilters used by the Full-Text indexing service must do
> something like this in order to create it's keyword index database.|||Anthony,
Out-of-the box with either SQL Server 2000 or SQL Server 2005 (Yukon) this
is not possible. You would need to either parse the text of the MS Word
documents before you inserted the documents into an IMAGE datatype or write
your own IFilter *wrapper* to capture the text into a separate text based
column. The MSDN Platform SDK has code examples and utilities that can be
used to the later, if you want to do this.
Yes, you are correct, that is what the IFilters (Microsoft's and 3rd party)
do.
Regards,
John
--
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Anthony" <Anthony@.discussions.microsoft.com> wrote in message
news:3CFF2482-3A52-40E1-B629-3873ACAEDC41@.microsoft.com...
> Is there any way to access the text within a SQL Binary object? For
> example
> if I wanted to count the number of words within a Word document that had
> been
> added to a binary field in the database.
> I figure the IFilters used by the Full-Text indexing service must do
> something like this in order to create it's keyword index database.

Access Text of Parameter

I have a report that uses parameters from a query. The query returns two
columns. One for the paramerter value and one for the parameter title. I want
the header of the report to access the same fild that I have set as the Label
Field in my report parameter dropdown.
I can add the actual parameter to the header using:
=Parameters!Property.Value
But I don't want the actual parameter, i want the alias from the second
colum of the parameter query.
I tried
=Parameters!Property.Text
but it didn't work.
Any ideas?
Thanks!try =Parameters!Property.Label
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Eric Langland [MSFT]" <EricLanglandMSFT@.discussions.microsoft.com> wrote in
message news:C60A3AB7-7C07-418B-B2F7-ECA395614511@.microsoft.com...
>I have a report that uses parameters from a query. The query returns two
> columns. One for the paramerter value and one for the parameter title. I
> want
> the header of the report to access the same fild that I have set as the
> Label
> Field in my report parameter dropdown.
> I can add the actual parameter to the header using:
> =Parameters!Property.Value
> But I don't want the actual parameter, i want the alias from the second
> colum of the parameter query.
> I tried
> =Parameters!Property.Text
> but it didn't work.
> Any ideas?
> Thanks!|||That did it.
Many thanks Lev!
"Lev Semenets [MSFT]" wrote:
> try =Parameters!Property.Label
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Eric Langland [MSFT]" <EricLanglandMSFT@.discussions.microsoft.com> wrote in
> message news:C60A3AB7-7C07-418B-B2F7-ECA395614511@.microsoft.com...
> >I have a report that uses parameters from a query. The query returns two
> > columns. One for the paramerter value and one for the parameter title. I
> > want
> > the header of the report to access the same fild that I have set as the
> > Label
> > Field in my report parameter dropdown.
> >
> > I can add the actual parameter to the header using:
> >
> > =Parameters!Property.Value
> >
> > But I don't want the actual parameter, i want the alias from the second
> > colum of the parameter query.
> >
> > I tried
> > =Parameters!Property.Text
> > but it didn't work.
> >
> > Any ideas?
> >
> > Thanks!
>
>

Access object within report

Is it possible to access a text box within a report? Something like this:
Report.TextBoxName.Value
Thanksyes, try:
Reports!ReportItem.Value
"Mark Goldin" wrote:
> Is it possible to access a text box within a report? Something like this:
> Report.TextBoxName.Value
> Thanks
>
>|||Sorry that should have been:
ReportItems!ReportItem.Value
Michael C
"Mark Goldin" wrote:
> Is it possible to access a text box within a report? Something like this:
> Report.TextBoxName.Value
> Thanks
>
>|||Thank you. Will try.
"Michael C" <MichaelC@.discussions.microsoft.com> wrote in message
news:F586D1A0-9489-4183-9DEA-B3F209FCE4B2@.microsoft.com...
> Sorry that should have been:
> ReportItems!ReportItem.Value
> Michael C
> "Mark Goldin" wrote:
>> Is it possible to access a text box within a report? Something like this:
>> Report.TextBoxName.Value
>> Thanks
>>

Access Memo field to SQL Server Text field

Hi,

I'm importing an Access database to SQL Server 2000.
The issue I ran into is pretty frustrating... All Memo fields that get copied over (as Text fields) appear to be fine and visible in SQL Server Enterprise Manager... except when I display them on the web via ASP - everything is blank (no content at all).

I didn't have that problem with Access, so I ruled out the possibility that there's something wrong with the original data.

Is this some sort of an encoding problem that arose during database import?
I would appreciate any pointers.If the data is visible within SQL Server, but not from your ASP page, then the problem is with your ASP page and the way it is pulling the text data.
Do you really need this to be a text field? The varchar datatype will handle up to 8000 bytes.|||That's a good point - I might be able to get away with varchar. It's something I'll look into, but I'm still curious as to why the data behaves this way (I ran into similar problems in the past while trying to import Access data to SQL server).

My ASP page is functioning properly. I use a simple "SELECT *" statement to pull the data and display it on the page. With an Access DSN it encounters no problems and shows all the data as intended. When I import the data into SQL Server and swap the DSN to point to the newly-created database (while not modifying the ASP page itself in any way) the text fields display no content.|||A text field in SQL Server is not stored within the record. The record actually only stores a pointer to the location of the actual data. This may be why your page is not handling it properly, but I don't know the work-around. You could try posting your question on the ASP forum.|||Interesting... I was aware that Text fields are stored differently and separately from the regular data in the database, but I didn't think it would affect the way they're displayed through the recordset...

Maybe there's something that can be done on the ASP side - I'll ask some ASP guys.

Thanks.|||Interesting... I was aware that Text fields are stored differently and separately from the regular data in the database, but I didn't think it would affect the way they're displayed through the recordset...I wouldn't have thought so either, but it is my best guess.
What happens in your ASP code if you enumerate the column names rather than using "select *" (which is a bad practice anyway)?|||I use a simple "SELECT *" statement to pull the data and display it on the page.

ahhhhhhhhhhhhhhhhhhhhhhhhhhh

http://weblogs.sqlteam.com/brettk/archive/2004/04/22/1272.aspx|||I try to do it properly with larger systems and database, but, yeah, for me it's laziness that makes me use "Select *"...|||I try to do it properly with larger systems and database, but, yeah, for me it's laziness that makes me use "Select *"...

Hey! No one's lazier than me....

SELECT ', ' + COLUMN_NAME FROM INFORMATION_SCHEMA.Columns
WHERE TABLE_NAME = 'xxx'
ORDER BY ORDINAL_POSITION

SELECT * can buy you a boatload of trouble...so it's amatter how you want to spend your laziness...you have no choice when you have exploding code|||I wouldn't have thought so either, but it is my best guess.
What happens in your ASP code if you enumerate the column names rather than using "select *" (which is a bad practice anyway)?

Well, that's an interesting question, mostly because it gives me some sort of a deja vu feeling - as if I encountered something like this several years ago...
It's possible that I did something like this in the past and fixed the problem that way.
I'll check into this on Thursday when I'm back at work.

Thanks.|||You are off UNTIL Thursday?

Okay, but this Thursday is Turkey Day for us Yanks, so you'll have to depend upon the Pootle Flumps and Cannucks of the world to help you out.|||Hey! No one's lazier than me....

SELECT ', ' + COLUMN_NAME FROM INFORMATION_SCHEMA.Columns
WHERE TABLE_NAME = 'xxx'
ORDER BY ORDINAL_POSITION

SELECT * can buy you a boatload of trouble...so it's amatter how you want to spend your laziness...you have no choice when you have exploding code

That's an interesting way to structure a query - I've never done it this way before.

Dare to dream... Sorry, you think that you're the laziness champ, but you're not. Your laziness doesn't compare to mine - not even close.|||You are off UNTIL Thursday?

Okay, but this Thursday is Turkey Day for us Yanks, so you'll have to depend upon the Pootle Flumps and Cannucks of the world to help you out.

Yes, it sounds a bit odd. I'm actually at work as we speak, but I also have another part time job as a contractor with my previous employer. Thanksgiving will be a good time to come in and get a bunch of work out of the way.|||See but here's the rub, my laziness is born out of economy of effort..the smarter you work, the less you have to

some laziness makes your life more simple, other types make your life much more difficult, you have to decide|||See but here's the rub, my laziness is born out of economy of effort..the smarter you work, the less you have to

some laziness makes your life more simple, other types make your life much more difficult, you have to decide

Agree. Mine usually gets me in trouble somewhere down the line.

... but it still feels good to be the "Champ" :)