sql full text search + cfstoredproc

Question

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?

chrisbrickhouse · Answer

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.

Sign up

To post, reply, or follow discussions, please sign in with your Adobe ID.

Sign in to Adobe Community

To post, reply, or follow discussions, please sign in with your Adobe ID.

Scanning file for viruses.

This file cannot be downloaded