Thanks Pavel, I'll take a look.... I'm pretty sure that I can do this from both the AL and Filter (I've done this before), I think the key here is that it's a call to a remote server. We just discovered that remedy is truncating the call and dropping off the server name! That would screw it up for sure....
Should be: EXEC [baker].[amcaslocal].[dbo].[AA_Disadvantaged_WordSearch_01] @lineup_input_appl_id = 160240 But is being sent as: EXEC [amcaslocal].[dbo].[AA_Disadvantaged_WordSearch_01] @lineup_input_appl_id = 160240 And your English is quite good! Sure beats my spanish! Warren On 6/2/08, Pavel Guedez <[EMAIL PROTECTED]> wrote: > > ** 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: [email protected] > 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___ > > > __Platinum Sponsor: www.rmsportal.com ARSlist: "Where the Answers Are" > html___ > -- 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"
