Hi Warren, First, sorry for my english, I'm form Venezuela (spanish speaker), I don't know if on a Set Fields action from a filter o Active link you can call to a procedure, you have to call it from a "Direct SQL" action. But if so, I think you need to use an OUTPUT parameter, to get some answer from the procedure. I guess you try to use @ANSWER as an output parameter, but if I'm not wrong, you need to explicit declare it as an OUTPUT, and maybe beacuse of that the process times out waiting for an answer. Check this web page, for help about procedure on SQL http://msdn.microsoft.com/en-us/library/aa258259(SQL.80).aspx
Pavel Guédez GSM +58 412 808.33.16 ----- Original Message ---- From: Warren Baltimore <[EMAIL PROTECTED]> To: arslist@ARSLIST.ORG Sent: Monday, June 2, 2008 11:37:30 AM Subject: SQL Stored procedures ** 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. __Platinum Sponsor: www.rmsportal.com ARSlist: "Where the Answers Are" html___ _______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: www.rmsportal.com ARSlist: "Where the Answers Are"
