Hmmmmm. I reran it as BEGIN TRANSACTION Insert INTO SW_License . . . Commit And I got the same error about nested transaction s and XACT_ABORT set to off.
Then I ran BEGIN TRANSACTION SET XACT_ABORT ON Insert INTO SW_License . . . Commit and it worked. Apparently the ardb.cfg is not affecting the transaction. Oh BMC SUPPORT? any clues? Thanks Fred. John J. Reiser Software Development Analyst Remedy Administrator/Developer Lockheed Martin - MS2 The star that burns twice as bright burns half as long. Pay close attention and be illuminated by its brilliance. - paraphrased by me -----Original Message----- From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of Grooms, Frederick W Sent: Friday, January 05, 2007 11:27 AM To: arslist@ARSLIST.ORG Subject: Re: ROLLBACK - Distributed Transaction issues. No just the Remedy Server needed to be restarted. In the query analyzer did you wrap your insert into a transaction (so it mimics the AR System more closely) Begin Tran Insert ... Commit Tran -----Original Message----- From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of Reiser, John J Sent: Friday, January 05, 2007 9:56 AM To: arslist@ARSLIST.ORG Subject: Re: ROLLBACK - Distributed Transaction issues. Fred, I did the change with notepad and restarted the services. When I checked the Admintool I see DB-Options:SQL Server { name:XACT_ABORT value:ON } in the Database Configuration File setting box. Does MS SQL server need to be restarted too? Because that is a major issue. I have no control over the DB Server. It's clustered and on a SAN for disaster recovery. The DBA assures me that the settings listed in the MS knowledge base concerning network access is set correctly. And it appears to be because I can use the Query Analyzer from the ARS DB Server and issue the insert command to the remote DB. So it's off to see the Stored Procedure Wizard, the Wonderful Wizard of Odd. Thanks, John J. Reiser Software Development Analyst Remedy Administrator/Developer Lockheed Martin - MS2 The star that burns twice as bright burns half as long. Pay close attention and be illuminated by its brilliance. - paraphrased by me -----Original Message----- From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of Grooms, Frederick W Sent: Friday, January 05, 2007 10:48 AM To: arslist@ARSLIST.ORG Subject: Re: ROLLBACK - Distributed Transaction issues. According to the error message the XACT_ABORT option in the ardb.cfg file is not being seen since the message is that the option is OFF. Double check the config file. Did you add the option thru an external editor or thru the Admin Tool ( File -> Server Information -> Database Tab, the Database Configuration File setting ), and did you restart the server after adding the option? Fred -----Original Message----- From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of Reiser, John J Sent: Friday, January 05, 2007 9:17 AM To: arslist@ARSLIST.ORG Subject: ROLLBACK - Distributed Transaction issues. Hello Listers, ARServer:ARS 6.03.00 Patch14 ARSystemDB: Remote MS SQL cluster running SQL 2003 Remote Linked DB: MS SQL 2000 MSDTC on SQL 2003 server is set to allow remote access. ardb.cfg has the xact_abort parameters set to ON I've gone through much of the ARSList archives and tried many of the suggestions to get this working. I have the Linked Server set up on my ARSystem DB, I have xact_abort settings in the ardb.cfg, I can use query analyzer to run the SQL statement as it exists in the arsql.log. That works. I actually perform an insert into the remote DB from the ARSystem SQL Server. The arsql.log also lists "WARNING * Unable to start a nested transaction for OLE DB provider 'SQLOLEDB'. A nested transaction was required because the XACT_ABORT option was set to OFF. (SQL Server 7395)OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ITransactionLocal::StartTransaction retur" that gets cut off here. I have the ARAdmin account created on the remote nonARS DB with the same password and the ARAdmin account is in the dbowner permissions there. I'm guessing my only solution is to run the SQL statement as a scheduled Job and pull the info from ARSystem DB. And perform a DB backend update on my ARS form to set an integer flag. If anyone has any hints I would be most appreciative. Sorry, I'm not in the market for 3rd party add-ons. Management spent all of the budget Christmas shopping ;^> John J. Reiser Software Development Analyst Remedy Administrator/Developer Lockheed Martin - MS2 The star that burns twice as bright burns half as long. Pay close attention and be illuminated by its brilliance. - paraphrased by me ________________________________________________________________________ _______ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org ARSlist:"Where the Answers Are" _______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org ARSlist:"Where the Answers Are"
