Nicky, Thanks. That really helped. For some reason the old setup was working fine. Using the new ARS 7.1 and a New DB version seems to be causing conflicts with the column names. It looks like I need to rewrite my Direct SQL and correct the column names. Thanks for your help.
--- John J. Reiser Senior 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 ________________________________ From: Action Request System discussion list(ARSList) [mailto:[email protected]] On Behalf Of Nicky Madjarov Sent: Friday, April 03, 2009 10:51 AM To: [email protected] Subject: Re: Direct SQL action to a Linked MS SQL Server db ** John, I'm sorry, I did not realized that you can run your insert statement from the sql directly. Select into copies records from one table to another, if you specify fields, it will just copy these fields. Now, on your problem. When you run your insert statement from the sql prompt SQL> Insert INTO SW_License.License_Management_Test.dbo.ARS_View1 (MR_Date, EDD) VALUES ( '1/7/2009 12:00:00 AM', '2/19/2009 12:00:00 AM') you will either reproduce the error message or it will be completed with success. This will narrow your effort to: if error - you will see the complete details and will be dealing with some incompatibility between 2 sql servers/versions if success - then the problem is somewhere between Remedy database layer and the linked table. It is not a lot, but each follow completely defferent resolution route. Regards, Nicky Madjarov phone: 973-202-4278 Find out how to bust your AR System performance @ http://www.SpeedUpARS.com ----- Original Message ----- From: Reiser, John J <mailto:[email protected]> Newsgroups: public.remedy.arsystem.general To: [email protected] Sent: Friday, April 03, 2009 10:28 AM Subject: Re: Direct SQL action to a Linked MS SQL Server db ** Nicky, Thanks. I tried this select ProductType='HARDWARE' into SW_License from PL_MR_LineItems where MRNumber = 'TM0000099' and got "1 row affected". I am not a dba so I am winging this. I don't fully understand what this statement means. ProductType='HARDWARE' is the column and data in my ARS Form. SW_License is the name of the linked db as it appears on the SQL Server supporting the ARSystem db. PL_MR_Lineitems is the ARS form MRNumber = 'TM0000099' is also from the ARS Form. Did I get things in the right order? Because if I run it again I get an error. There is already an object named 'SW_License' in the database. Thanks, --- John J. Reiser Senior 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 ________________________________ From: Action Request System discussion list(ARSList) [mailto:[email protected]] On Behalf Of Nicky Madjarov Sent: Thursday, April 02, 2009 7:09 PM To: [email protected] Subject: Re: Direct SQL action to a Linked MS SQL Server db ** Hi, this may not resolve your problem but it will help you narrow it change you statement to the following and run it from sql client on the remedy server select values_from_your_form into your_dblink_table from your_form where put_condition_to_return_one_record I think that at least you will be able to see the entire error message, if not more details. Regards, Nicky Madjarov phone: 973-202-4278 Find out how to bust your AR System performance @ http://www.SpeedUpARS.com ----- Original Message ----- From: Reiser, John J <mailto:[email protected]> Newsgroups: public.remedy.arsystem.general To: [email protected] Sent: Thursday, April 02, 2009 5:28 PM Subject: Direct SQL action to a Linked MS SQL Server db ** Hello Listers, ARS 7.1 Patch 4 MS SQL Version 9.0.3007 I have a remote linked MS SQL Server (probably SQL 2000) that I have been pushing data to for years. Now that the Remedy ARS db is on a new box and a newer version of MS SQL I can not longer push the data. The SQL statement is Insert INTO SW_License.License_Management_Test.dbo.ARS_View1 (MR_Date, EDD) VALUES ( '1/7/2009 12:00:00 AM', '2/19/2009 12:00:00 AM') I get the following message in the arsql.log file. * WARNING * The operation could not be performed because OLE DB provider "SQLNCLI" for linked server "SW_License" was unable to begin a distributed transaction. (SQL Server 7391)OLE DB provider "SQLNCLI" for linked server "SW_License" returned message "No transaction It gets truncated in the log so I don't know what the returned message is indicating. the ARS_View1 is a view that was created on the remote DB that converts date values to integers so we can display dates in ARSystem using a view form. I am wondering if that is causing a problem when pushing the values back to the remote view. If I am missing some information needed to understand this question please feel free to ping me. Thanks, --- John J. Reiser Senior 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 __Platinum Sponsor: RMI Solutions ARSlist: "Where the Answers Are" html___ __Platinum Sponsor: RMI Solutions ARSlist: "Where the Answers Are" html___ __Platinum Sponsor: RMI Solutions ARSlist: "Where the Answers Are" html___ __Platinum Sponsor: RMI Solutions ARSlist: "Where the Answers Are" html___ _______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: RMI Solutions ARSlist: "Where the Answers Are"
