Brooks, I really think that it is the issue I pointed out the other day. I've run into this three times so far -- Once whenever they upgraded my remote server from server 2k to server 2k3, once when they upgraded my current server from server 2k to server 2k3, and once when they applied patches on the remote server that reversed all of the DTS settings.
All three times it was the same issue, with the same error message. Remedy has to have the <BEGIN> and <END> transaction, and windows server 2k3 will chop that off by default in the event it comes from a remote source. This is why it works on your local queries, but not on your remote queries. Thanks, Gary Opela, Jr Sr. Remedy Developer Leader Communications, Inc. 405 736 3211 -----Original Message----- From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of brooksm Sent: Thursday, November 15, 2007 4:55 PM To: [email protected] Subject: Re: Direct SQL Active Link to query Oracle from SQL Server I'm trying to connect to an Oracle Production database link and an Oracle Staging database link. I just realized that active link SQL for the Production link works flawlessly. It's the Staging link that has problems. No matter how simple the SQL in the AL is, I get the error. ARADMIN has read access on both the production and staging Oracle databases. I've used this login for years. When I login to the ARSystem sql server as SA or ARADMIN and query the staging db link directly from SQL Server Management Studio I am able to connect and query the staging db fine. It's only when I try to apply the SQL in the Active link that I get the error. Help...... Brooks Sam Ferguson-3 wrote: > > Brooks, I suspect the database account ARAdmin doesn't have access to > your remote DB. I suggest checking the account on your remote DB and > ensure it exists and has select access to the necessary table. > Sam > > -----Original Message----- > From: Action Request System discussion list(ARSList) > [mailto:[EMAIL PROTECTED] On Behalf Of brooksm > Sent: Thursday, 15 November 2007 5:14 p.m. > To: ARSList > Subject: Re: Direct SQL Active Link to query Oracle from SQL Server > > Sam, I turned on SQL logging and here are the errors. > What stands out is this line: Access to the remote server is denied > because > no login-mapping exists. (SQL Server 7416) > > Do you know how to get this corrected? > > > <SQL > <TID: 0000004040> <RPC ID: 0000370729> <Queue: List > > <Client-RPC: 390620 > <USER: brooksm > >> /* Wed Nov 14 2007 19:56:34.7100 */*** ERROR *** Access to the remote > server is denied because no login-mapping exists. (SQL Server 7416) > <SQL > <TID: 0000004040> <RPC ID: 0000370729> <Queue: List > > <Client-RPC: 390620 > <USER: brooksm > >> /* Wed Nov 14 2007 19:56:34.7100 */ROLLBACK TRANSACTION > <SQL > <TID: 0000004040> <RPC ID: 0000370729> <Queue: List > > <Client-RPC: 390620 > <USER: brooksm > >> /* Wed Nov 14 2007 19:56:34.7100 */*** ERROR *** The ROLLBACK > TRANSACTION > request has no corresponding BEGIN TRANSACTION. (SQL Server 3903) > <SQL > <TID: 0000003912> <RPC ID: 0000370730> <Queue: Admin > > <Client-RPC: 390600 > <USER: brooksm > >> /* Wed Nov 14 2007 19:56:41.5850 */SQL Trace Log -- OFF > > > > > Sam Ferguson-3 wrote: >> >> Brooks, try turning on SQL logging via the client and see the content > of >> SQL statement being issued to the database, this may help show you > what >> the issue is. >> Sam >> >> -----Original Message----- >> From: Action Request System discussion list(ARSList) >> [mailto:[EMAIL PROTECTED] On Behalf Of brooksm >> Sent: Thursday, 15 November 2007 2:21 p.m. >> To: ARSList >> Subject: Direct SQL Active Link to query Oracle from SQL Server >> >> Hi Listers >> I've built a query using openquery that queries an oracle view from my >> ARSystem on SQL Server 2005 >> >> This doesn't work: >> select * from openquery(STG,'select * >> from USER.HOUSEHOLD_VIEW >> where HOUSEHOLD_ID = ''$Email Address$'') >> >> Produces this error: >> ARERR [552] Failure during SQL operation to the database : The > ROLLBACK >> TRANSACTION request has no corresponding BEGIN TRANSACTION. (SQL > Server >> 3903) >> The preceding message occurred during the execution of active link >> Retail >> Help Desk - SQL -- action 1. (ARNOTE 1101) >> >> >> If I change the (= ''$Email Address$'') to (= ''[EMAIL PROTECTED]'') it >> works >> fine. I'd ultimately like to use (LIKE ''$Email Address$'' + ''%'') > but >> just >> can't get the syntax correct. >> >> Is there anyone with this kind of experience that can help me figure > out >> the >> correct syntax? >> >> Thanks >> Brooks >> -- >> View this message in context: >> > http://www.nabble.com/Direct-SQL-Active-Link-to-query-Oracle-from-SQL-Se >> rver-tf4809105.html#a13759981 >> Sent from the ARS (Action Request System) mailing list archive at >> Nabble.com. >> >> > ________________________________________________________________________ >> _______ >> UNSUBSCRIBE or access ARSlist Archives at www.arslist.org >> Platinum Sponsor: www.rmsportal.com ARSlist: "Where the Answers Are" >> >> > ________________________________________________________________________ > _______ >> UNSUBSCRIBE or access ARSlist Archives at www.arslist.org >> Platinum Sponsor: www.rmsportal.com ARSlist: "Where the Answers Are" >> >> > > -- > View this message in context: > http://www.nabble.com/Direct-SQL-Active-Link-to-query-Oracle-from-SQL-Se > rver-tf4809105.html#a13761024 > Sent from the ARS (Action Request System) mailing list archive at > Nabble.com. > > ________________________________________________________________________ > _______ > UNSUBSCRIBE or access ARSlist Archives at www.arslist.org > Platinum Sponsor: www.rmsportal.com ARSlist: "Where the Answers Are" > > ________________________________________________________________________ _______ > UNSUBSCRIBE or access ARSlist Archives at www.arslist.org > Platinum Sponsor: www.rmsportal.com ARSlist: "Where the Answers Are" > > -- View this message in context: http://www.nabble.com/Direct-SQL-Active-Link-to-query-Oracle-from-SQL-Se rver-tf4809105.html#a13783781 Sent from the ARS (Action Request System) mailing list archive at Nabble.com. ________________________________________________________________________ _______ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: www.rmsportal.com ARSlist: "Where the Answers Are" _______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: www.rmsportal.com ARSlist: "Where the Answers Are"
