Re: AW: Remedy 7.0 issue with Direct SQL in a filter
All,
Thanks for your suggestions. I looked into the logs and played with the SQL
some more and got resolution. First thing I noticed once it started working
is that when you trigger a filter on save and/or modify of a field and
reference that field from the database, you get the value BEFORE the update
and not after. So, I re-worked it and used the power of SQL against the
field record and not the database entry and it worked. Below is the query I
used in case any are interested.
update HPD_Help_Desk set SLM_EMAIL_TO = (rtrim(ltrim(substring('$Assigned
Group$', Charindex('-', '$Assigned Group$') + 1, Len('$Assigned Group$'
+ 't...@client.com'), SLM_EMAIL_CC = (rtrim(ltrim(substring('$Assigned
Group$', Charindex('-', '$Assigned Group$') + 1, Len('$Assigned Group$'
+ 't...@client.com') + '; ' + (rtrim(ltrim(substring('$Assigned Group$',
Charindex('-', '$Assigned Group$') + 1, Len('$Assigned Group$' +
't...@client.com'), SLM_EMAIL_BCC = 'serviced...@gssamerica.com' where
(Incident_Number = '$Incident Number$' and (upper('$Assigned Group$') like
upper('%Tier 4%')))
Thanks,
Scott
Scott Fluegge
CIO Sr. VP of Operations
GSS America Infotech Ltd., A SEI-CMMI Level 3 certified company
Third Floor, Block B,
Cyber Gateway, HITEC City, Madhapur,
Hyderabad - 500 081. AP. India
Phone : 91 40 44556600 ext: 312
Mobile: 91 970118
VoIP: (847) 307 7606 ext: 250
Email: scott.flue...@gssamerica.com
Visit: http://www.gssamerica.com
-Original Message-
From: Peter Romain [mailto:p.romain.arsl...@parsolutions.co.uk]
Sent: Wednesday, June 03, 2009 3:15 PM
Subject: Re: AW: Remedy 7.0 issue with Direct SQL in a filter
Scott,
You may find extra quotes are being inserted into the SQL.
Try building the complete SQL statement in a Remedy temp field and using
just this field in the direct sql action (Direct SQL - $temp field$).
Cheers
Peter
Scott,
turn on SQL-Logging and you'll see the statement wich the arserver is
issueing against the database. I think this should point you in the
right direction.
HTH
Kind Regards Conny
Von: Action Request System discussion list(ARSList)
[mailto:arsl...@arslist.org] Im Auftrag von Scott Fluegge
Gesendet: Mittwoch, 3. Juni 2009 09:53
An: arslist@ARSLIST.ORG
Betreff: Remedy 7.0 issue with Direct SQL in a filter
Wichtigkeit: Hoch
**
Anyone able to help me on a potentially simple Remedy question that has
my team stumped?
This is in regards to a Remedy customization we need to do in our
environment. It is with respect to custom filters invoking Direct SQL
(MS SQL 2005 database). The SQL works PERFECT from the enterprise
manager but doesn't execute the complex query in Remedy. I did 4 direct
SQL statements. 3 are complex and don't do anything. The simple 4th
test (all are table updates) does work. I can't for the life of me
figure out why the others fail. I am pasting the SQL here in case you
can look into it... I am desperate!!! We are writing to hidden objects
within the incident record which SLM will use for notification.
Here is a sample that works:
update HPD_Help_Desk set SLP_EMAIL_TO = 'email_address_here' where
Incident_Number = '$Incident_Number$' and Assigned_Group like '%TIER 2%'
And this one does nothing... (No errors unless I invalidate the SQL so
seems a matching issue):
update HPD_Help_Desk set SLM_EMAIL_TO =
(rtrim(ltrim(substring(Assigned_Group, Charindex('-', Assigned_Group) +
1, Len(Assigned_Group + 't...@client.com'), SLM_EMAIL_BCC =
'serviced...@gssamerica.com' where Incident_Number = '$Incident_Number$'
and Assigned_Group like '%TIER 2%'
Can you help me??? Just to re-iterate, the SQL works perfect from SQL
Enterprise Manager (just changing the $Incident_Number$ to a real
number) so I know it is not a simple SQL error...
Thanks,
Scott
_Platinum Sponsor: rmisoluti...@verizon.net ARSlist: Where the Answers
Are_
___
UNSUBSCRIBE or access ARSlist Archives at www.arslist.org
Platinum Sponsor:rmisoluti...@verizon.net ARSlist: Where the Answers Are
___
UNSUBSCRIBE or access ARSlist Archives at www.arslist.org
Platinum Sponsor:rmisoluti...@verizon.net ARSlist: Where the Answers Are
___
UNSUBSCRIBE or access ARSlist Archives at www.arslist.org
Platinum Sponsor:rmisoluti...@verizon.net ARSlist: Where the Answers Are
Remedy 7.0 issue with Direct SQL in a filter
Anyone able to help me on a potentially simple Remedy question that has my
team stumped?
This is in regards to a Remedy customization we need to do in our
environment. It is with respect to custom filters invoking Direct SQL (MS
SQL 2005 database). The SQL works PERFECT from the enterprise manager but
doesn't execute the complex query in Remedy. I did 4 direct SQL statements.
3 are complex and don't do anything. The simple 4th test (all are table
updates) does work. I can't for the life of me figure out why the others
fail. I am pasting the SQL here in case you can look into it... I am
desperate!!! We are writing to hidden objects within the incident record
which SLM will use for notification.
Here is a sample that works:
update HPD_Help_Desk set SLP_EMAIL_TO = 'email_address_here' where
Incident_Number = '$Incident_Number$' and Assigned_Group like '%TIER 2%'
And this one does nothing... (No errors unless I invalidate the SQL so seems
a matching issue):
update HPD_Help_Desk set SLM_EMAIL_TO =
(rtrim(ltrim(substring(Assigned_Group, Charindex('-', Assigned_Group) + 1,
Len(Assigned_Group + 't...@client.com'), SLM_EMAIL_BCC =
'serviced...@gssamerica.com' where Incident_Number = '$Incident_Number$' and
Assigned_Group like '%TIER 2%'
Can you help me??? Just to re-iterate, the SQL works perfect from SQL
Enterprise Manager (just changing the $Incident_Number$ to a real number) so
I know it is not a simple SQL error.
Thanks,
Scott
___
UNSUBSCRIBE or access ARSlist Archives at www.arslist.org
Platinum Sponsor:rmisoluti...@verizon.net ARSlist: Where the Answers Are
AW: Remedy 7.0 issue with Direct SQL in a filter
Scott,
turn on SQL-Logging and you'll see the statement wich the arserver is
issueing against the database. I think this should point you in the
right direction.
HTH
Kind Regards Conny
Von: Action Request System discussion list(ARSList)
[mailto:arsl...@arslist.org] Im Auftrag von Scott Fluegge
Gesendet: Mittwoch, 3. Juni 2009 09:53
An: arslist@ARSLIST.ORG
Betreff: Remedy 7.0 issue with Direct SQL in a filter
Wichtigkeit: Hoch
**
Anyone able to help me on a potentially simple Remedy question that has
my team stumped?
This is in regards to a Remedy customization we need to do in our
environment. It is with respect to custom filters invoking Direct SQL
(MS SQL 2005 database). The SQL works PERFECT from the enterprise
manager but doesn't execute the complex query in Remedy. I did 4 direct
SQL statements. 3 are complex and don't do anything. The simple 4th
test (all are table updates) does work. I can't for the life of me
figure out why the others fail. I am pasting the SQL here in case you
can look into it... I am desperate!!! We are writing to hidden objects
within the incident record which SLM will use for notification.
Here is a sample that works:
update HPD_Help_Desk set SLP_EMAIL_TO = 'email_address_here' where
Incident_Number = '$Incident_Number$' and Assigned_Group like '%TIER 2%'
And this one does nothing... (No errors unless I invalidate the SQL so
seems a matching issue):
update HPD_Help_Desk set SLM_EMAIL_TO =
(rtrim(ltrim(substring(Assigned_Group, Charindex('-', Assigned_Group) +
1, Len(Assigned_Group + 't...@client.com'), SLM_EMAIL_BCC =
'serviced...@gssamerica.com' where Incident_Number = '$Incident_Number$'
and Assigned_Group like '%TIER 2%'
Can you help me??? Just to re-iterate, the SQL works perfect from SQL
Enterprise Manager (just changing the $Incident_Number$ to a real
number) so I know it is not a simple SQL error...
Thanks,
Scott
_Platinum Sponsor: rmisoluti...@verizon.net ARSlist: Where the Answers
Are_
___
UNSUBSCRIBE or access ARSlist Archives at www.arslist.org
Platinum Sponsor:rmisoluti...@verizon.net ARSlist: Where the Answers Are
Re: AW: Remedy 7.0 issue with Direct SQL in a filter
Scott,
You may find extra quotes are being inserted into the SQL.
Try building the complete SQL statement in a Remedy temp field and using
just this field in the direct sql action (Direct SQL - $temp field$).
Cheers
Peter
Scott,
turn on SQL-Logging and you'll see the statement wich the arserver is
issueing against the database. I think this should point you in the
right direction.
HTH
Kind Regards Conny
Von: Action Request System discussion list(ARSList)
[mailto:arsl...@arslist.org] Im Auftrag von Scott Fluegge
Gesendet: Mittwoch, 3. Juni 2009 09:53
An: arslist@ARSLIST.ORG
Betreff: Remedy 7.0 issue with Direct SQL in a filter
Wichtigkeit: Hoch
**
Anyone able to help me on a potentially simple Remedy question that has
my team stumped?
This is in regards to a Remedy customization we need to do in our
environment. It is with respect to custom filters invoking Direct SQL
(MS SQL 2005 database). The SQL works PERFECT from the enterprise
manager but doesn't execute the complex query in Remedy. I did 4 direct
SQL statements. 3 are complex and don't do anything. The simple 4th
test (all are table updates) does work. I can't for the life of me
figure out why the others fail. I am pasting the SQL here in case you
can look into it... I am desperate!!! We are writing to hidden objects
within the incident record which SLM will use for notification.
Here is a sample that works:
update HPD_Help_Desk set SLP_EMAIL_TO = 'email_address_here' where
Incident_Number = '$Incident_Number$' and Assigned_Group like '%TIER 2%'
And this one does nothing... (No errors unless I invalidate the SQL so
seems a matching issue):
update HPD_Help_Desk set SLM_EMAIL_TO =
(rtrim(ltrim(substring(Assigned_Group, Charindex('-', Assigned_Group) +
1, Len(Assigned_Group + 't...@client.com'), SLM_EMAIL_BCC =
'serviced...@gssamerica.com' where Incident_Number = '$Incident_Number$'
and Assigned_Group like '%TIER 2%'
Can you help me??? Just to re-iterate, the SQL works perfect from SQL
Enterprise Manager (just changing the $Incident_Number$ to a real
number) so I know it is not a simple SQL error...
Thanks,
Scott
_Platinum Sponsor: rmisoluti...@verizon.net ARSlist: Where the Answers
Are_
___
UNSUBSCRIBE or access ARSlist Archives at www.arslist.org
Platinum Sponsor:rmisoluti...@verizon.net ARSlist: Where the Answers Are
___
UNSUBSCRIBE or access ARSlist Archives at www.arslist.org
Platinum Sponsor:rmisoluti...@verizon.net ARSlist: Where the Answers Are
