This has to do with the way that ARS wraps all select statements with a <BEGIN> and <END> transaction.
The MS DTC (Data Transaction Coordinator) on windows server 2005 is by default turned OFF, so these types of statements fail because it chops off the <BEGIN> and <END> transaction statements. This only affects you if you are doing selects from remote databases, and the option has to be turned on on both servers. Have your DBAs work with it, I know mine got it working. Also, you can search the list archives from posts from me a few months ago regarding this whenever I ran into the same problem. Just search for DTC or something like that. It's an easy fix on the DBAs side once they identify what needs to be fixed. It took me two weeks to finally get my issue resolved because I didn't even know where to look, but this should point you in the right direction. Thanks, Gary Opela, Jr Sr. Remedy Developer Leader Communications, Inc. -----Original Message----- From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of brooksm Sent: Wednesday, November 14, 2007 10:14 PM To: [email protected] 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"
