Okay - I'll try that. Thanks! -----Original Message----- From: Action Request System discussion list(ARSList) [mailto:arslist@ARSLIST.ORG] On Behalf Of Grooms, Frederick W Sent: Thursday, May 10, 2012 1:33 PM To: arslist@ARSLIST.ORG Subject: Re: Direct SQL in Active Link problems
Mid-Tier may have a different format of date string and that would cause an error since you are relying on a string being in a specific format. It is best to do a set fields of $SERVERTIMESTAMP$ to an integer display only field and use that integer field in the SQL. Fred -----Original Message----- From: Action Request System discussion list(ARSList) [mailto:arslist@ARSLIST.ORG] On Behalf Of Joe Martin D'Souza Sent: Thursday, May 10, 2012 12:11 PM To: arslist@ARSLIST.ORG Subject: Re: Direct SQL in Active Link problems ** Yes $SERVERTIMESTAMP$ is a better candidate when you need to qualify the time on the server.. Local Time Stamp is useful only when you specifically need local information.. Going back to your problem, that however may not be the reasons you are experiencing problems with the query itself. You mentioned getting an error. What is that error? Joe -----Original Message----- From: Nancy Tietz Sent: Thursday, May 10, 2012 12:00 PM Newsgroups: public.remedy.arsystem.general To: arslist@ARSLIST.ORG Subject: Re: Direct SQL in Active Link problems ** Hi Axton - Oh that's right - somebody said to use '$SERVERTIMESTAMP$'. -----Original Message----- From: Action Request System discussion list(ARSList) [mailto:arslist@ARSLIST.ORG] On Behalf Of Axton Sent: Thursday, May 10, 2012 11:50 AM To: arslist@ARSLIST.ORG Subject: Re: Direct SQL in Active Link problems ** I would not use TIMESTAMP in direct sql in active links. You will get different times from different users, depending on what timezone the client machine is configured for. Axton Grams -----Original Message----- On Thu, May 10, 2012 at 10:37 AM, smiley wrote: Hi all - This Direct SQL seems like it should work, but I am getting an error message (in Mid-Tier, 7.6.03). UPDATE HPD_Help_Desk SET UM_Opened_By = '$USER$', UM_Opened_Date = DATEDIFF(second,'01/01/1970 00:00:00','$TIMESTAMP$') +14400 WHERE Incident_Number = '$Incident Number$' ; The above Direct SQL should translate to this: (which actually works as is in ARUtilities' SQL); UPDATE HPD_Help_Desk SET UM_Opened_By = 'myuserid', UM_Opened_Date = DATEDIFF(second,'01/01/1970 00:00:00','05/09/2012 03:00:00 PM') +14400 WHERE Incident_Number = 'INC000000031826' ; So what is Direct SQL doing to this sQL??? Is it the $Incident Number$ or $USER$ or $TIMESTAMP$ ??? I actually tried it withOUT the $TIMESTAMP$... and I had some NULL / IS NULL etc in there and had to take that out as I couldn't get the nulls to work in the WHERE statement. I thought it was working --- although that was in the BMC Remedy Client, not Mid-Tier. It is easier for me to test in the Client because I don't have to Flush Cached every time I tinker with the AL's. So close.... On the Client I was testing and found that the fields were being updated every Other time! However I am looking at ARUtilites in a SELECT statement after every step to see what happened... and maybe IT is only working every other time. Good grief! Any other ideas out there? Or maybe sympathy?? I know the Client works differently than the Mid-Tier but this is very tiring. Thanks for listening!! __________________________________________________________________________ _____ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org attend wwrug12 www.wwrug12.com ARSList: "Where the Answers Are" _______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org attend wwrug12 www.wwrug12.com ARSList: "Where the Answers Are"