ARS 7.0.1, SQL 2000 Let me start out by saying that as a SQL dba, I am quite the novice!
I have written a simple stored procedure that does a Full Text Search against a SQL database that is NOT the Remedy database. This database is on a seperate server. The procedure lives on that seperate server in the target database (amcaslocal). Here is my issue. If I send the command: EXEC [baker].[amcaslocal].[dbo].[AA_Disadvantaged_WordSearch_01] @lineup_input_appl_id = 160240 >From the query analyzer logged into my local (remedy) sql database. I get an answer! If however I send that same command in a set fields from either a filter or an Active link, the process times out and the whole thing dies.... Incidently, when I developed this process, the database I was searching on was on the local server. I have set up a linked server on the local box to connect to the remote. Any input would be most welcome! The procedure is as follows: SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS OFF GO /* Stored procedure used to find instances of special needs OR disadvantaged words. */ CREATE PROC AA_Disadvantaged_WordSearch_01 (@lineup_input_appl_id int) --SOMID AS DECLARE @ANSWER INT SELECT APPL_PERSON_ID, ESSAY INTO #ESSAY_TBL FROM DBO.ESSAY WHERE APPL_PERSON_ID = @lineup_input_appl_id AND CONTAINS(ESSAY, '"AMERICORP" OR "BATTERED" OR "GATES SCHOLAR" OR "GENOCIDE" OR "HABITAT" OR "HANDICAPPED" OR "HRSA" OR "IMMIGRANT" OR "NHSSSRAP" OR "PEACE CORPS" OR "PIPELINE" OR "POVERTY" OR "REFUGEE" OR "RESERVATION" OR "SHELTER" OR "SMDEP" OR "SOCIAL JUSTICE" OR "TRIBAL" OR "UDOC"') -- SELECT APPL_PERSON_ID AS ESSAY_APPL_PERSON_ID -- FROM #ESSAY_TBL IF (SELECT COUNT (*) FROM #ESSAY_TBL) > 0 BEGIN DROP TABLE #ESSAY_TBL SET @ANSWER = 1 -- SELECT @ANSWER AS iFeSSAYaNSWER GOTO finishline END ELSE BEGIN DROP TABLE #ESSAY_TBL SET @ANSWER = 0 -- SELECT @ANSWER AS eLSEeSSAYaNSWER SELECT APPL_PERSON_ID, ORG_NAME, EXP_TITLE, EXP_DESC, EXP_NAME INTO #EXPERIENCE_TBL FROM DBO.EXPERIENCE WHERE APPL_PERSON_ID = @lineup_input_appl_id AND ((CONTAINS(ORG_NAME, '"AMERICORP" OR "BATTERED" OR "GATES SCHOLAR" OR "GENOCIDE" OR "HABITAT" OR "HANDICAPPED" OR "HRSA" OR "IMMIGRANT" OR "NHSSSRAP" OR "PEACE CORPS" OR "PIPELINE" OR "POVERTY" OR "REFUGEE" OR "RESERVATION" OR "SHELTER" OR "SMDEP" OR "SOCIAL JUSTICE" OR "TRIBAL" OR "UDOC"')) OR (CONTAINS(EXP_TITLE, '"AMERICORP" OR "BATTERED" OR "GATES SCHOLAR" OR "GENOCIDE" OR "HABITAT" OR "HANDICAPPED" OR "HRSA" OR "IMMIGRANT" OR "NHSSSRAP" OR "PEACE CORPS" OR "PIPELINE" OR "POVERTY" OR "REFUGEE" OR "RESERVATION" OR "SHELTER" OR "SMDEP" OR "SOCIAL JUSTICE" OR "TRIBAL" OR "UDOC"')) OR (CONTAINS(EXP_DESC, '"AMERICORP" OR "BATTERED" OR "GATES SCHOLAR" OR "GENOCIDE" OR "HABITAT" OR "HANDICAPPED" OR "HRSA" OR "IMMIGRANT" OR "NHSSSRAP" OR "PEACE CORPS" OR "PIPELINE" OR "POVERTY" OR "REFUGEE" OR "RESERVATION" OR "SHELTER" OR "SMDEP" OR "SOCIAL JUSTICE" OR "TRIBAL" OR "UDOC"')) OR (CONTAINS(EXP_NAME, '"AMERICORP" OR "BATTERED" OR "GATES SCHOLAR" OR "GENOCIDE" OR "HABITAT" OR "HANDICAPPED" OR "HRSA" OR "IMMIGRANT" OR "NHSSSRAP" OR "PEACE CORPS" OR "PIPELINE" OR "POVERTY" OR "REFUGEE" OR "RESERVATION" OR "SHELTER" OR "SMDEP" OR "SOCIAL JUSTICE" OR "TRIBAL" OR "UDOC"'))) END -- SELECT APPL_PERSON_ID AS EXPERIENCE_APPL_PERSON_ID -- FROM #EXPERIENCE_TBL IF(SELECT COUNT (*) FROM #EXPERIENCE_TBL) > 0 BEGIN DROP TABLE #EXPERIENCE_TBL SET @ANSWER = 1 -- SELECT @ANSWER AS EXPERIENCE_IF_ANSWER GOTO finishline END ELSE BEGIN DROP TABLE #EXPERIENCE_TBL SET @ANSWER = 0 -- SELECT @ANSWER AS EXPERIENCE_ELSE_ANSWER END finishline: SELECT @ANSWER AS ANSWER GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO -- Warren R. Baltimore II Remedy Developer UW Medicine IT Services School of Medicine University of Washington Box 358220 1325 Fourth Ave, Suite 2000 Seattle, WA 98101 The opinions expressed in this e-mail are in no way those of the University of Washington, or the State of Washington. They are my own. _______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: www.rmsportal.com ARSlist: "Where the Answers Are"
