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 [email protected]
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"
<<attachment: pic17589.gif>>
