full text searching

Ok

Im trying to get full text searching to work with parameterised stored procedures.

basicly the proc is

SELECT IndustrySector.title, BiblioHeadings.BiblioHeading, ReferenceTypes.ReferenceType, Bibliographies.Authors, Bibliographies.PublishDate, Bibliographies.BiblioTitle,

Publishers.Publisher, Bibliographies.Journal, Bibliographies.Issue, Bibliographies.BiblioKeyWords, Bibliographies.BiblioAnnotation, Bibliographies.Note,

Bibliographies.BiblioURL, IndustrySector.id

FROM Bibliographies INNER JOIN

BiblioHeadings ON Bibliographies.BiblioHeadingID = BiblioHeadings.BiblioHeadingID INNER JOIN

Publishers ON Bibliographies.PublisherID = Publishers.PublisherID INNER JOIN

ReferenceTypes ON Bibliographies.Reference = ReferenceTypes.ReferenceTypeID INNER JOIN

IndustrySector ON Bibliographies.SectorID = IndustrySector.id

WHERE CONTAINS(BiblioAnnotation, @.SearchFor_txt )

this works as long as only one word is in the @.SearchFor_txt parameter.

How to I get it to work with more than one word?

can I put the parameters in quote marks such as '" @.SearchFor_txt "' ? (I know this dosnt work with parameters but I need to contain the string)

any know how to resolve this issue please

jim

Books on line says

CONTAINS can search for:

A word or phrase.

The prefix of a word or phrase.

A word near another word.

A word inflectionally generated from another (for example, the word drive is the inflectional stem of drives, drove, driving, and driven).

A word that is a synonym of another word using thesaurus (for example, the word metal can have synonyms such as aluminum and steel).

Full text manual, where are pending changes stored

Created a full text index on table met_db_prcd. This table has an id of 658101385.

CREATE FULLTEXT INDEX ON met_db_prcd(db_prcd_dsc, db_prcd_nm)
KEY INDEX Xmet_db_prcd2 on EMART_MET
WITH CHANGE_TRACKING MANUAL

I can see that I have pending changes by running the following command:

sp_fulltext_pendingchanges 658101385

1. Where are the pending changes stored?

In SQL Server 2005 the changes are stored in an Internal Map Table,unlike SQL Server 2000 which had a table sysfulltextnotify table. The stored procedure sp_fulltext_pendingchanges uses the underlining Map table to get relevent information

HTH

Vishal

|||Do you know what the name of the internal map table?

Full text manual, where are pending changes stored

Created a full text index on table met_db_prcd. This table has an id of 658101385.

CREATE FULLTEXT INDEX ON met_db_prcd(db_prcd_dsc, db_prcd_nm)
KEY INDEX Xmet_db_prcd2 on EMART_MET
WITH CHANGE_TRACKING MANUAL

I can see that I have pending changes by running the following command:

sp_fulltext_pendingchanges 658101385

1. Where are the pending changes stored?

In SQL Server 2005 the changes are stored in an Internal Map Table,unlike SQL Server 2000 which had a table sysfulltextnotify table. The stored procedure sp_fulltext_pendingchanges uses the underlining Map table to get relevent information

HTH

Vishal

|||Do you know what the name of the internal map table?

FULL SEARCH

Is there a simple way to search all the views/stored procedures in a database which contain particular text without having to open each one?

The object search which i thought might work in query analyzer will only search by object name so thats a dead end.

A point to make here though... you should have ALL of your system object create/alter scripts stored to file and preferably a source control system (like VSS). You could take this as an opportunity to save everything to file. You can then easily use the windows search function (or similar) to search inside those files.

Fujitsu call to sproc via ADO using a parameter

I am trying to call a stored proc using a parameter via ADO exceute,
but cannot get the syntac correct.
Any help is much appreciated.
thanks,If your stored procedures doesn't have any output parameter then you can use the execute method of connection object as given below

Conn.Execute "Usp_GetEmp 1, '01/01/2002'"

assuming the sp has two input parameters, integer datatype as first parameter and date as second parameter.

If your stored procedure have output parameters then you have to use the command object of the ADO Object Library.

I hope this helps you

:)|||If you need to have a resultset returned from the stored procedure you would have to use the command object as well. If you need to use the command object, please post the stored procedure parameters.|||Getting a returned result set via exec is OK.

The problem is with trying to figure out how to set up the parameters array in order to pass the parameters!

e.g. how would I run something like the [Sales by Year] from Northwind, which requires 2 parameters? (let's say we ignore the ouput)|||Try the following:

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim cmd As ADODB.Command

Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset
Set cmd = New ADODB.Command

cn.Open "Your Connection String"
With cmd
Set .ActiveConnection = cn
.CommandText = "[Sales by Year]"
.CommandType = adCmdStoredProc
.Parameters.Append .CreateParameter("Beginning_Date", _
adDate, adParamInput)
.Parameters("Beginning_Date").Value = "1/1/1980"
.Parameters.Append .CreateParameter("Ending_Date", _
adDate, adParamInput)
.Parameters("Ending_Date").Value = "1/1/2000"
End With
rs.Open cmd

' close/destroy the connection, command, recordset objects

rs.Close
Set rs = Nothing
Set cmd = Nothing
cn.Close
Set cn = Nothing|||Thanks.

Unfortunately I'm looking for Fujitsu COBOL syntax , but I do appreciate your help.|||Do you have a help file on how to use ado with fujitsu cobol ? If so, please post and I will check it out.|||Ahhh. If I had that I wouldn't have had to have posted!! Fujitsu's support refers one to MSDN where one finds solutions such as yours.

They don't have any documentation re ADOor any examples of how to use it, although their marketing material states specifically that they support it.

Which is why I posted here in the first place.

Thanks again.|||You mean to say there is no online help for the ado syntax. What about an object reference - not necessarily a help document. Do you have connection, command and recordset objects ? If all else fails, you could create a com object in vb and reference that in your cobol colde.|||To say that the documentation is sparse is an understatement. Also what's there is incomplete and/or misleading in some cases, so it's not of much use.

We could create a component in VB, well Delphi probably, as that's our preferred development platform, but we specifically bought Fujitsu due to its claimed support for ADO. (To be honest, the compiler probably does support ADO, but the documentation isn't saying how, especially in the realm of parameter passing ...