James, Good suggestion, but when I use ARAdmin when I login to the query analyzer just so I can avoid that very issue. I've confirmed accounts on both servers.... ARAdmin is there...
Thanks for the idea though! Warren On 6/2/08, James Van Sickle <[EMAIL PROTECTED]> wrote: > > Warren > > Have you checked to confirm that the Database user, typically > ARAdmin, that the Remedy servers uses has access to this remote database? > Your SP is most likely working in Query Analyzer because your personal > login you are using has access to the DB. When you attempt to run it in > Remedy, the system uses the user ARAdmin, which may not have the required > access. > > (Embedded image moved to file: pic17589.gif)Countrywide > > > James Van Sickle > Remedy Developer II > Enterprise Service Management > > > > 972-696-5779 Office 1000 Coit Road > 92-540-5779 Mail Stop: PCRD-2-19 > Internal Plano, TX 75075 > > > > > > > > > Warren Baltimore > <warrenbaltimore@ > GMAIL.COM> To > Sent by: "Action arslist@ARSLIST.ORG > Request System cc > discussion > list(ARSList)" Subject > <[EMAIL PROTECTED] [ARSLIST] SQL Stored procedures > ORG> > > > 06/02/2008 11:08 > AM > > > Please respond to > [EMAIL PROTECTED] > RG > > > > > > > ** > 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___ > > > > ====================================================================== > > Confidentiality Notice: The information contained in and transmitted with > this communication is strictly confidential, is intended only for the use of > the intended recipient, and is the property of Countrywide Financial > Corporation or its affiliates and subsidiaries. If you are not the intended > recipient, you are hereby notified that any use of the information contained > in or transmitted with the communication or dissemination, distribution, or > copying of this communication is strictly prohibited by law. If you have > received this communication in error, please immediately return this > communication to the sender and delete the original message and any copy of > it in your possession. > > ====================================================================== > > > > _______________________________________________________________________________ > UNSUBSCRIBE or access ARSlist Archives at www.arslist.org > Platinum Sponsor: www.rmsportal.com ARSlist: "Where the Answers Are" > > -- 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"
