It sure would have been nice to have received an authentication error instead of an SQL error then. I love the descriptive errors that we get sometimes.
I'm glad you figured this out, and now there is another solution to a yet puzzling error message. 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: Friday, November 16, 2007 6:40 PM To: [email protected] Subject: Re: Direct SQL Active Link to query Oracle from SQL Server I figured it out. In the Linked Server Properties under security, I needed to select "Be made using this security context" and fill in the Remote login and password with the ARAdmin credentials. So it worked fine when I did a query in SSMS, but when I tried the query in Remedy it did not work. Thank you all for your help with this problem. Have a great weekend. Brooks Pierson, Shawn-2 wrote: > > Every time this has happened to me, it has been because they don't have > the MSDTC service running on the SQL Server, specifically. I would have > the DBAs check that and if it's not running, turn it on. > > Shawn Pierson > > -----Original Message----- > From: Action Request System discussion list(ARSList) > [mailto:[EMAIL PROTECTED] On Behalf Of Opela, Gary L Contr > OC-ALC/ITMA > Sent: Friday, November 16, 2007 7:16 AM > To: [email protected] > Subject: Re: Direct SQL Active Link to query Oracle from SQL Server > > 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" > > Private and confidential as detailed > http://www.sug.com/disclaimers/default.htm#Mail here . If you cannot > access hyperlink, please e-mail sender. > > ________________________________________________________________________ _______ > 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#a13804553 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"
