John, >From what we have seen in the SQL Log it will look like: SELECT TOP 2147483648 T983.C1,C1000000161,C1000000000,C7,C1000000164,C1000000169,... FROM T983 ORDER BY 2 DESC, 1 ASC
Notice: no WHERE clause. This was issued by an end user, who then cancelled it after ARS had issued it to the DB. So it got left in suspended state while still locking the table (this was the Help Desk form too). In SQL 2005 you have something called the Activity Monitor where you can see all the threads connected to the DB. >From there you can see threads that are blocking the DB, and what the actual >SQL call is. Then, if you have SQL logs running, you can open the log and find the person who did it. The other thing we've seen with the WHERE 1=1 clause has to do with table fields that use an external qualification. If the Table field is using an external qualification: EXT($FIELD_NAME$), if you refresh the table field while $FIELD_NAME$ is blank/NULL, ARS will translate that into a 1=1 and do a full table scan of whatever form you have specified the Table field to look at. This in my opinion is B A D. It should return nothing (translate it to 1 = 0), as nothing is the criteria that was specified. HTH Matt P. From: Action Request System discussion list(ARSList) [mailto:[email protected]] On Behalf Of John Sundberg Sent: Friday, July 16, 2010 8:29 AM To: [email protected] Subject: Re: Capturing SQL Statement ** Would the SQL log of ARS show that query? (I hope so) And if so - any idea what it would look like in the log? -John On Jul 16, 2010, at 9:18 AM, LJ LongWing wrote: ** John, I know the question comes up every once in awhile....and the common agreement is that yes, remedy will pass a 1=0 to the DB...the argument typically occurs around what happens when it hits the DB. Well, from personal experience, I have seen remedy pass a 1=1 to the DB and have that query BLOCK other queries to the table...so in the instance of SQL Server 2005, I guarantee you that it's passed to the DB, and the DB does a table scan. _______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org attend wwrug10 www.wwrug.com ARSlist: "Where the Answers Are"
