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-Server-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"
