sql full text search + cfstoredproc

[chrisbrickhouse]

this is the weirdest thing. i have a proc that does a full text search on a table. the proc is

ALTER PROCEDURE [dbo].[spSearchMain_members]
@searchstring varchar(50)
AS
set @searchstring = replace(@searchstring, ' ', '*')
select top 20 m_name, member_id,txtLocation,txtState,M_Country,dbo.fn_CalculateAge(birthdate, getdate()) as age,sex
from members where contains(favoritebands,@searchstring)

so if i run this from sql with this call it runs quickly and returns the 20 records i am expecting: exec spSearchMain_members 'poison the well'

however, when i run this with cfstoredproc, it returns an empty recordset if i specify more than one keyword. one keyword works fine. i also tried cfquery using exec and no results. has anyone ever run itno this before?

[Adam Cameron.]

If you run the SQL Server Profiler as you make the call from CF do you see anything that might hint at what's going wrong?

What's your <cfstoredproc> call look like?

--

Adam

[chrisbrickhouse]

proc looks like this

<cfstoredproc procedure="spSearchMain_members" datasource="#application.appDataSource#">
        <cfprocparam type="In" cfsqltype="CF_SQL_varchar" value="#lcase(form.txtsearchstring)#">
        <cfprocresult name="getMembers">
</cfstoredproc>

[chrisbrickhouse]

never mind, i have finally found the solution to this problem.

i created a new dsn specifically for full text search and followed these steps

1)  I downloaded the latest JDBC driver from MS
2)  Copied the  sqljdbc4.jar to the cfusion/lib directory
3)  Restarted CF instance
4)   Created a new DSN using "other" and used the following connection  string:   jdbc:sqlserver://xxx.xxx.xxx.xxx;databaseName=someDatabase;SelectMethod=direct;sendStringParametersAsUnicode=false;MaxPooledStatements=1000;
5)   Used the following driver class:   com.microsoft.sqlserver.jdbc.SQLServerDriver

it works fine. i have one dsn for my regular queries and one for full text.