Re: SQL Statement
OPENDATASOURCE is a SQL Server-specific function. I don't know whether there is a similar function for Oracle, as I am not very familiar with the product. However I did find the following link which discusses using Oracle to connect to an Access database, which may be of some use to you: http://www.orafaq.com/node/60 Hope this helps, Jim ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor:rmisoluti...@verizon.net ARSlist: Where the Answers Are
Re: SQL statement
Thanks
With my statement
SELECT * FROM OPENDATASOURCE ('Microsoft.jet.OLEDB.4.0',
'Data Source=h:\Shopinfo\shopdetail.mdb')...ShopDetail WHERE SHOP_PFCNTR =
$Shop Number$
The error I get is the following
ARERR [552] Failure during SQL operation to the database : ORA-00933: SQL
command not properly ended
The MDB does not have a passowrd. I think I'm missing a quote somewhere.
A question I had is, do I need to setup a System Data source on the server
to this Microsoft access db or can that be done on the client?
Ashton, Jim (JUS) jim.ash...@ontario.ca
Sent by: Action Request System discussion list(ARSList)
arslist@ARSLIST.ORG
04/17/2009 10:34 AM
Please respond to
arslist@ARSLIST.ORG
To
arslist@ARSLIST.ORG
cc
Subject
Re: SQL statement
**
The following works for SQL Server in a similar situation. Not sure what
the corollary to OPENDATASOURCE is for Oracle.
SELECT X.* FROM OPENDATASOURCE('Microsoft.jet.OLEDB.4.0',
'Data Source=\\usranfb\transfer\Shopinfo\shopdetail.mdb;
Jet OLEDB:Database Password=password;')...table name X
WHERE SHOP_PFCNTR = $Shop number$
password is an optional database level password - if there is no
database password on the .mdb file I think you can omit the entire Jet
OLEDB:Database Password=password; phrase.
table name is the name of the table containing the SHOP_PFCNTR field in
your Access database.
Hope this helps,
Jim.
_Platinum Sponsor: rmisoluti...@verizon.net ARSlist: Where the Answers
Are_
*
This e-mail message, including any attachments, is for the sole use of the
addressee(s) to whom it has been sent, and may contain information that is
confidential or legally protected. If you are not the intended recipient or
have received this message in error, you are not authorized to copy,
distribute, or otherwise use this message or its attachments. Please notify
the sender immediately by return e-mail and permanently delete this message and
any attachments. Dunkin' Brands Inc. makes no warranty that this e-mail is
error or virus free.
Re: SQL statement
John You'll need a database link set up. One for your dba I would imagine. Matt From: Action Request System discussion list(ARSList) [mailto:arsl...@arslist.org] On Behalf Of John Kelley Sent: 16 April 2009 22:14 To: arslist@ARSLIST.ORG Subject: SQL statement Need a little help with an SQL statement. I am trying to populate fields in INC management from an Access Database on a shared network drive. My set field SQL statement so far is : SELECT * FROM File: //usranfb/transfer/Shopinfo/shopdetail.mdb WHERE SHOP_PFCNTR = $Shop number$ When I populate the shop number in my INC I get the following error. ARERR [552] Failure during SQL operation to the database : ORA-00903: invalid table name I don't think it knows where the location is. Help * This e-mail message, including any attachments, is for the sole use of the addressee(s) to whom it has been sent, and may contain information that is confidential or legally protected. If you are not the intended recipient or have received this message in error, you are not authorized to copy, distribute, or otherwise use this message or its attachments. Please notify the sender immediately by return e-mail and permanently delete this message and any attachments. Dunkin' Brands Inc. makes no warranty that this e-mail is error or virus free. ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor:rmisoluti...@verizon.net ARSlist: Where the Answers Are
Re: SQL statement
The following works for SQL Server in a similar situation. Not sure
what the corollary to OPENDATASOURCE is for Oracle.
SELECT X.* FROM OPENDATASOURCE('Microsoft.jet.OLEDB.4.0',
'Data Source=\\usranfb\transfer\Shopinfo\shopdetail.mdb;
Jet OLEDB:Database Password=password;')...table name X
WHERE SHOP_PFCNTR = $Shop number$
password is an optional database level password - if there is no
database password on the .mdb file I think you can omit the entire Jet
OLEDB:Database Password=password; phrase.
table name is the name of the table containing the SHOP_PFCNTR field
in your Access database.
Hope this helps,
Jim.
___
UNSUBSCRIBE or access ARSlist Archives at www.arslist.org
Platinum Sponsor:rmisoluti...@verizon.net ARSlist: Where the Answers Are
Re: SQL statement
I don't know if you can do what you are attempting to do. Normally a Set Fields SQL statement reads from the ARSystem database. Look at your error message it's an Oracle error. I'm assuming your underlying database is Oracle, correct? -- Chris Danaceau AttivaSoft Solutions Architect From: Action Request System discussion list(ARSList) on behalf of John Kelley Sent: Thu 4/16/2009 5:13 PM To: arslist@ARSLIST.ORG Subject: SQL statement Need a little help with an SQL statement. I am trying to populate fields in INC management from an Access Database on a shared network drive. My set field SQL statement so far is : SELECT * FROM File: //usranfb/transfer/Shopinfo/shopdetail.mdb WHERE SHOP_PFCNTR = $Shop number$ When I populate the shop number in my INC I get the following error. ARERR [552] Failure during SQL operation to the database : ORA-00903: invalid table name I don't think it knows where the location is. Help * This e-mail message, including any attachments, is for the sole use of the addressee(s) to whom it has been sent, and may contain information that is confidential or legally protected. If you are not the intended recipient or have received this message in error, you are not authorized to copy, distribute, or otherwise use this message or its attachments. Please notify the sender immediately by return e-mail and permanently delete this message and any attachments. Dunkin' Brands Inc. makes no warranty that this e-mail is error or virus free. ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor:rmisoluti...@verizon.net ARSlist: Where the Answers Are
Re: SQL statement error
Looks like $PO Number$ is a field reference and Remedy is passing the contents of PO Number (not the field name) to SQL, which is likely NULL because the field is empty when you run this. -Original Message- From: Action Request System discussion list(ARSList) [mailto:arsl...@arslist.org] On Behalf Of Muhlethaler, Laurie Sent: Wednesday, December 17, 2008 1:11 PM To: arslist@ARSLIST.ORG Subject: SQL statement error ** Listers ~ I am getting the following error during a SQL set fields action: ARERR [552] Failure during SQL operation to the database : The max function requires 1 argument(s). (SQL Server 174) My SQL command is listed below and I can successfully run it from SQL Server: SELECT max($PO Number$) + 1 FROM AST_PurchaseOrder What am I doing wrong here? Thank you in advance. Windows Server 2003 SQL Server 2005 7.1.00 Patch 002 AR Server 7.0.03 Patch 006 Service Desk Asset Management CMDB - 2.1.00 Patch 002 DSL - 1.0.00 Patch 1516 Laurie Muhlethaler First Republic Bank Remedy Developer / Administrator 415.364.4436 [First Republic Bank logo]http://logos.firstrepublic.com/FRB.jpg The information transmitted is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited. If you received this in error, please contact the sender and delete the material from any computer. This message cannot be guaranteed to be secure or error-free. First Republic Bank and its related entities do not take responsibility for, or accept time-sensitive instructions sent by email including orders, funds transfer instructions or stop payments on checks. All instructions of this nature must be handled by direct communication, not email. We reserve the right to monitor and review the content of all email communications sent or received. Emails sent to or from this address may be stored in accordance with regulatory requirements. First Republic Bank is a Division of Merrill Lynch Bank Trust Co., FSB __Platinum Sponsor: www.rmsportal.com ARSlist: Where the Answers Are html___ ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: www.rmsportal.com ARSlist: Where the Answers Are
Re: SQL statement error
I'm not sure I understand. I want to auto-populate the PO Number field with the next available PO #. I would like to accomplish this through direct SQL rather than creating an additional field(s) and workflow? Laurie Muhlethaler Remedy Developer / Administrator On Wed, 17 Dec 2008 13:15:41 -0600, Kaiser, Norm E CIV USAF AFMC 96 CS/SCCE norm.kai...@eglin.af.mil wrote: Looks like $PO Number$ is a field reference and Remedy is passing the contents of PO Number (not the field name) to SQL, which is likely NULL because the field is empty when you run this. -Original Message- From: Action Request System discussion list(ARSList) [mailto:arsl...@arslist.org] On Behalf Of Muhlethaler, Laurie Sent: Wednesday, December 17, 2008 1:11 PM To: arslist@ARSLIST.ORG Subject: SQL statement error ** Listers ~ I am getting the following error during a SQL set fields action: ARERR [552] Failure during SQL operation to the database : The max function requires 1 argument(s). (SQL Server 174) My SQL command is listed below and I can successfully run it from SQL Server: SELECT max($PO Number$) + 1 FROM AST_PurchaseOrder What am I doing wrong here? Thank you in advance. Windows Server 2003 SQL Server 2005 7.1.00 Patch 002 AR Server 7.0.03 Patch 006 Service Desk Asset Management CMDB - 2.1.00 Patch 002 DSL - 1.0.00 Patch 1516 Laurie Muhlethaler First Republic Bank Remedy Developer / Administrator 415.364.4436 [First Republic Bank logo]http://logos.firstrepublic.com/FRB.jpg The information transmitted is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited. If you received this in error, please contact the sender and delete the material from any computer. This message cannot be guaranteed to be secure or error-free. First Republic Bank and its related entities do not take responsibility for, or accept time-sensitive instructions sent by email including orders, funds transfer instructions or stop payments on checks. All instructions of this nature must be handled by direct communication, not email. We reserve the right to monitor and review the content of all email communications sent or received. Emails sent to or from this address may be stored in accordance with regulatory requirements. First Republic Bank is a Division of Merrill Lynch Bank Trust Co., FSB __Platinum Sponsor: www.rmsportal.com ARSlist: Where the Answers Are html___ ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: www.rmsportal.com ARSlist: Where the Answers Are === = -- The information transmitted is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited. If you received this in error, please contact the sender and delete the material from any computer. This message cannot be guaranteed to be secure or error-free. First Republic Bank and its related entities do not take responsibility for, or accept time-sensitive instructions sent by email including orders, funds transfer instructions or stop payments on checks. All instructions of this nature must be handled by direct communication, not email. We reserve the right to monitor and review the content of all email communications sent or received. Emails sent to or from this address may be stored in accordance with regulatory requirements. First Republic Bank is a Division of Merrill Lynch Bank Trust Co., FSB ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: www.rmsportal.com ARSlist: Where the Answers Are
Re: SQL statement error
Do a set fields from direct SQL. Have your sql query be: SELECT max($PO Number$) + 1 FROM AST_PurchaseOrder Map your PO Number field at the bottom to $1$ Is this what you are doing? Thanks, Gary Opela, Jr. -Original Message- From: Action Request System discussion list(ARSList) [mailto:arsl...@arslist.org] On Behalf Of Muhlethaler, Laurie Sent: Wednesday, December 17, 2008 1:29 PM To: arslist@ARSLIST.ORG Subject: Re: SQL statement error I'm not sure I understand. I want to auto-populate the PO Number field with the next available PO #. I would like to accomplish this through direct SQL rather than creating an additional field(s) and workflow? Laurie Muhlethaler Remedy Developer / Administrator On Wed, 17 Dec 2008 13:15:41 -0600, Kaiser, Norm E CIV USAF AFMC 96 CS/SCCE norm.kai...@eglin.af.mil wrote: Looks like $PO Number$ is a field reference and Remedy is passing the contents of PO Number (not the field name) to SQL, which is likely NULL because the field is empty when you run this. -Original Message- From: Action Request System discussion list(ARSList) [mailto:arsl...@arslist.org] On Behalf Of Muhlethaler, Laurie Sent: Wednesday, December 17, 2008 1:11 PM To: arslist@ARSLIST.ORG Subject: SQL statement error ** Listers ~ I am getting the following error during a SQL set fields action: ARERR [552] Failure during SQL operation to the database : The max function requires 1 argument(s). (SQL Server 174) My SQL command is listed below and I can successfully run it from SQL Server: SELECT max($PO Number$) + 1 FROM AST_PurchaseOrder What am I doing wrong here? Thank you in advance. Windows Server 2003 SQL Server 2005 7.1.00 Patch 002 AR Server 7.0.03 Patch 006 Service Desk Asset Management CMDB - 2.1.00 Patch 002 DSL - 1.0.00 Patch 1516 Laurie Muhlethaler First Republic Bank Remedy Developer / Administrator 415.364.4436 [First Republic Bank logo]http://logos.firstrepublic.com/FRB.jpg The information transmitted is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited. If you received this in error, please contact the sender and delete the material from any computer. This message cannot be guaranteed to be secure or error-free. First Republic Bank and its related entities do not take responsibility for, or accept time-sensitive instructions sent by email including orders, funds transfer instructions or stop payments on checks. All instructions of this nature must be handled by direct communication, not email. We reserve the right to monitor and review the content of all email communications sent or received. Emails sent to or from this address may be stored in accordance with regulatory requirements. First Republic Bank is a Division of Merrill Lynch Bank Trust Co., FSB __Platinum Sponsor: www.rmsportal.com ARSlist: Where the Answers Are html___ ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: www.rmsportal.com ARSlist: Where the Answers Are === = -- The information transmitted is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited. If you received this in error, please contact the sender and delete the material from any computer. This message cannot be guaranteed to be secure or error-free. First Republic Bank and its related entities do not take responsibility for, or accept time-sensitive instructions sent by email including orders, funds transfer instructions or stop payments on checks. All instructions of this nature must be handled by direct communication, not email. We reserve the right to monitor and review the content of all email communications sent or received. Emails sent to or from this address may be stored in accordance with regulatory requirements. First Republic Bank is a Division of Merrill Lynch Bank Trust Co., FSB ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: www.rmsportal.com ARSlist: Where the Answers Are ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: www.rmsportal.com
Re: SQL statement error
Yes - that's exactly what I'm doing. I am doing it via a Set Fields action. I don't want to do this through a Direct SQL If Action, do I? Laurie Muhlethaler Remedy Developer / Administrator On Wed, 17 Dec 2008 13:31:06 -0600, Opela, Gary L CTR USAF AFMC 72 CS/SCBAH gary.opela@tinker.af.mil wrote: Do a set fields from direct SQL. Have your sql query be: SELECT max($PO Number$) + 1 FROM AST_PurchaseOrder Map your PO Number field at the bottom to $1$ Is this what you are doing? Thanks, Gary Opela, Jr. -Original Message- From: Action Request System discussion list(ARSList) [mailto:arsl...@arslist.org] On Behalf Of Muhlethaler, Laurie Sent: Wednesday, December 17, 2008 1:29 PM To: arslist@ARSLIST.ORG Subject: Re: SQL statement error I'm not sure I understand. I want to auto-populate the PO Number field with the next available PO #. I would like to accomplish this through direct SQL rather than creating an additional field(s) and workflow? Laurie Muhlethaler Remedy Developer / Administrator On Wed, 17 Dec 2008 13:15:41 -0600, Kaiser, Norm E CIV USAF AFMC 96 CS/SCCE norm.kai...@eglin.af.mil wrote: Looks like $PO Number$ is a field reference and Remedy is passing the contents of PO Number (not the field name) to SQL, which is likely NULL because the field is empty when you run this. -Original Message- From: Action Request System discussion list(ARSList) [mailto:arsl...@arslist.org] On Behalf Of Muhlethaler, Laurie Sent: Wednesday, December 17, 2008 1:11 PM To: arslist@ARSLIST.ORG Subject: SQL statement error ** Listers ~ I am getting the following error during a SQL set fields action: ARERR [552] Failure during SQL operation to the database : The max function requires 1 argument(s). (SQL Server 174) My SQL command is listed below and I can successfully run it from SQL Server: SELECT max($PO Number$) + 1 FROM AST_PurchaseOrder What am I doing wrong here? Thank you in advance. Windows Server 2003 SQL Server 2005 7.1.00 Patch 002 AR Server 7.0.03 Patch 006 Service Desk Asset Management CMDB - 2.1.00 Patch 002 DSL - 1.0.00 Patch 1516 Laurie Muhlethaler First Republic Bank Remedy Developer / Administrator 415.364.4436 [First Republic Bank logo]http://logos.firstrepublic.com/FRB.jpg The information transmitted is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited. If you received this in error, please contact the sender and delete the material from any computer. This message cannot be guaranteed to be secure or error-free. First Republic Bank and its related entities do not take responsibility for, or accept time-sensitive instructions sent by email including orders, funds transfer instructions or stop payments on checks. All instructions of this nature must be handled by direct communication, not email. We reserve the right to monitor and review the content of all email communications sent or received. Emails sent to or from this address may be stored in accordance with regulatory requirements. First Republic Bank is a Division of Merrill Lynch Bank Trust Co., FSB __Platinum Sponsor: www.rmsportal.com ARSlist: Where the Answers Are html___ __ _ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: www.rmsportal.com ARSlist: Where the Answers Are == = = --- - -- The information transmitted is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited. If you received this in error, please contact the sender and delete the material from any computer. This message cannot be guaranteed to be secure or error-free. First Republic Bank and its related entities do not take responsibility for, or accept time-sensitive instructions sent by email including orders, funds transfer instructions or stop payments on checks. All instructions of this nature must be handled by direct communication, not email. We reserve the right to monitor and review the content of all email communications sent or received. Emails sent to or from this address may be stored in accordance with regulatory requirements. First Republic Bank is a Division of Merrill Lynch Bank Trust Co., FSB
Re: SQL statement error
If you want to use select MAX, then you'll have to use direct SQL. -Original Message- From: Action Request System discussion list(ARSList) [mailto:arsl...@arslist.org] On Behalf Of Muhlethaler, Laurie Sent: Wednesday, December 17, 2008 1:38 PM To: arslist@ARSLIST.ORG Subject: Re: SQL statement error Yes - that's exactly what I'm doing. I am doing it via a Set Fields action. I don't want to do this through a Direct SQL If Action, do I? Laurie Muhlethaler Remedy Developer / Administrator On Wed, 17 Dec 2008 13:31:06 -0600, Opela, Gary L CTR USAF AFMC 72 CS/SCBAH gary.opela@tinker.af.mil wrote: Do a set fields from direct SQL. Have your sql query be: SELECT max($PO Number$) + 1 FROM AST_PurchaseOrder Map your PO Number field at the bottom to $1$ Is this what you are doing? Thanks, Gary Opela, Jr. -Original Message- From: Action Request System discussion list(ARSList) [mailto:arsl...@arslist.org] On Behalf Of Muhlethaler, Laurie Sent: Wednesday, December 17, 2008 1:29 PM To: arslist@ARSLIST.ORG Subject: Re: SQL statement error I'm not sure I understand. I want to auto-populate the PO Number field with the next available PO #. I would like to accomplish this through direct SQL rather than creating an additional field(s) and workflow? Laurie Muhlethaler Remedy Developer / Administrator On Wed, 17 Dec 2008 13:15:41 -0600, Kaiser, Norm E CIV USAF AFMC 96 CS/SCCE norm.kai...@eglin.af.mil wrote: Looks like $PO Number$ is a field reference and Remedy is passing the contents of PO Number (not the field name) to SQL, which is likely NULL because the field is empty when you run this. -Original Message- From: Action Request System discussion list(ARSList) [mailto:arsl...@arslist.org] On Behalf Of Muhlethaler, Laurie Sent: Wednesday, December 17, 2008 1:11 PM To: arslist@ARSLIST.ORG Subject: SQL statement error ** Listers ~ I am getting the following error during a SQL set fields action: ARERR [552] Failure during SQL operation to the database : The max function requires 1 argument(s). (SQL Server 174) My SQL command is listed below and I can successfully run it from SQL Server: SELECT max($PO Number$) + 1 FROM AST_PurchaseOrder What am I doing wrong here? Thank you in advance. Windows Server 2003 SQL Server 2005 7.1.00 Patch 002 AR Server 7.0.03 Patch 006 Service Desk Asset Management CMDB - 2.1.00 Patch 002 DSL - 1.0.00 Patch 1516 Laurie Muhlethaler First Republic Bank Remedy Developer / Administrator 415.364.4436 [First Republic Bank logo]http://logos.firstrepublic.com/FRB.jpg The information transmitted is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited. If you received this in error, please contact the sender and delete the material from any computer. This message cannot be guaranteed to be secure or error-free. First Republic Bank and its related entities do not take responsibility for, or accept time-sensitive instructions sent by email including orders, funds transfer instructions or stop payments on checks. All instructions of this nature must be handled by direct communication, not email. We reserve the right to monitor and review the content of all email communications sent or received. Emails sent to or from this address may be stored in accordance with regulatory requirements. First Republic Bank is a Division of Merrill Lynch Bank Trust Co., FSB __Platinum Sponsor: www.rmsportal.com ARSlist: Where the Answers Are html___ __ _ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: www.rmsportal.com ARSlist: Where the Answers Are == = = --- - -- The information transmitted is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited. If you received this in error, please contact the sender and delete the material from any computer. This message cannot be guaranteed to be secure or error-free. First Republic Bank and its related entities do not take responsibility for, or accept time-sensitive instructions sent by email including orders, funds transfer instructions or stop payments on checks. All instructions of this nature must be handled by direct communication, not email. We reserve the right to monitor
Re: SQL statement error
If you want to use select MAX, then you'll have to use direct SQL in a set fields action. -Original Message- From: Action Request System discussion list(ARSList) [mailto:arsl...@arslist.org] On Behalf Of Muhlethaler, Laurie Sent: Wednesday, December 17, 2008 1:38 PM To: arslist@ARSLIST.ORG Subject: Re: SQL statement error Yes - that's exactly what I'm doing. I am doing it via a Set Fields action. I don't want to do this through a Direct SQL If Action, do I? Laurie Muhlethaler Remedy Developer / Administrator On Wed, 17 Dec 2008 13:31:06 -0600, Opela, Gary L CTR USAF AFMC 72 CS/SCBAH gary.opela@tinker.af.mil wrote: Do a set fields from direct SQL. Have your sql query be: SELECT max($PO Number$) + 1 FROM AST_PurchaseOrder Map your PO Number field at the bottom to $1$ Is this what you are doing? Thanks, Gary Opela, Jr. -Original Message- From: Action Request System discussion list(ARSList) [mailto:arsl...@arslist.org] On Behalf Of Muhlethaler, Laurie Sent: Wednesday, December 17, 2008 1:29 PM To: arslist@ARSLIST.ORG Subject: Re: SQL statement error I'm not sure I understand. I want to auto-populate the PO Number field with the next available PO #. I would like to accomplish this through direct SQL rather than creating an additional field(s) and workflow? Laurie Muhlethaler Remedy Developer / Administrator On Wed, 17 Dec 2008 13:15:41 -0600, Kaiser, Norm E CIV USAF AFMC 96 CS/SCCE norm.kai...@eglin.af.mil wrote: Looks like $PO Number$ is a field reference and Remedy is passing the contents of PO Number (not the field name) to SQL, which is likely NULL because the field is empty when you run this. -Original Message- From: Action Request System discussion list(ARSList) [mailto:arsl...@arslist.org] On Behalf Of Muhlethaler, Laurie Sent: Wednesday, December 17, 2008 1:11 PM To: arslist@ARSLIST.ORG Subject: SQL statement error ** Listers ~ I am getting the following error during a SQL set fields action: ARERR [552] Failure during SQL operation to the database : The max function requires 1 argument(s). (SQL Server 174) My SQL command is listed below and I can successfully run it from SQL Server: SELECT max($PO Number$) + 1 FROM AST_PurchaseOrder What am I doing wrong here? Thank you in advance. Windows Server 2003 SQL Server 2005 7.1.00 Patch 002 AR Server 7.0.03 Patch 006 Service Desk Asset Management CMDB - 2.1.00 Patch 002 DSL - 1.0.00 Patch 1516 Laurie Muhlethaler First Republic Bank Remedy Developer / Administrator 415.364.4436 [First Republic Bank logo]http://logos.firstrepublic.com/FRB.jpg The information transmitted is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited. If you received this in error, please contact the sender and delete the material from any computer. This message cannot be guaranteed to be secure or error-free. First Republic Bank and its related entities do not take responsibility for, or accept time-sensitive instructions sent by email including orders, funds transfer instructions or stop payments on checks. All instructions of this nature must be handled by direct communication, not email. We reserve the right to monitor and review the content of all email communications sent or received. Emails sent to or from this address may be stored in accordance with regulatory requirements. First Republic Bank is a Division of Merrill Lynch Bank Trust Co., FSB __Platinum Sponsor: www.rmsportal.com ARSlist: Where the Answers Are html___ __ _ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: www.rmsportal.com ARSlist: Where the Answers Are == = = --- - -- The information transmitted is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited. If you received this in error, please contact the sender and delete the material from any computer. This message cannot be guaranteed to be secure or error-free. First Republic Bank and its related entities do not take responsibility for, or accept time-sensitive instructions sent by email including orders, funds transfer instructions or stop payments on checks. All instructions of this nature must be handled by direct communication, not email. We reserve
Re: SQL statement error
What I was trying to say in my original reply (and communicated poorly) was that $PO Number$ tells Remedy to translate what's currently in the PO Number field and poke it into the SQL statement. So if PO Number currently contains the value skizzle, you'll end up passing... max(skizzle) + 1 FROM AST_PurchaseOrder to the database! Which results in...error! So you need to supply the actual field name, as it's represented in the database, which is probably PO_Number. So the SQL would be... max(PO_Number) + 1 FROM AST_PurchaseOrder And then just set PO Number in the SET FIELDS action to $1$. Norm -Original Message- From: Action Request System discussion list(ARSList) [mailto:arsl...@arslist.org] On Behalf Of Muhlethaler, Laurie Sent: Wednesday, December 17, 2008 1:38 PM To: arslist@ARSLIST.ORG Subject: Re: SQL statement error Yes - that's exactly what I'm doing. I am doing it via a Set Fields action. I don't want to do this through a Direct SQL If Action, do I? Laurie Muhlethaler Remedy Developer / Administrator On Wed, 17 Dec 2008 13:31:06 -0600, Opela, Gary L CTR USAF AFMC 72 CS/SCBAH gary.opela@tinker.af.mil wrote: Do a set fields from direct SQL. Have your sql query be: SELECT max($PO Number$) + 1 FROM AST_PurchaseOrder Map your PO Number field at the bottom to $1$ Is this what you are doing? Thanks, Gary Opela, Jr. -Original Message- From: Action Request System discussion list(ARSList) [mailto:arsl...@arslist.org] On Behalf Of Muhlethaler, Laurie Sent: Wednesday, December 17, 2008 1:29 PM To: arslist@ARSLIST.ORG Subject: Re: SQL statement error I'm not sure I understand. I want to auto-populate the PO Number field with the next available PO #. I would like to accomplish this through direct SQL rather than creating an additional field(s) and workflow? Laurie Muhlethaler Remedy Developer / Administrator On Wed, 17 Dec 2008 13:15:41 -0600, Kaiser, Norm E CIV USAF AFMC 96 CS/SCCE norm.kai...@eglin.af.mil wrote: Looks like $PO Number$ is a field reference and Remedy is passing the contents of PO Number (not the field name) to SQL, which is likely NULL because the field is empty when you run this. -Original Message- From: Action Request System discussion list(ARSList) [mailto:arsl...@arslist.org] On Behalf Of Muhlethaler, Laurie Sent: Wednesday, December 17, 2008 1:11 PM To: arslist@ARSLIST.ORG Subject: SQL statement error ** Listers ~ I am getting the following error during a SQL set fields action: ARERR [552] Failure during SQL operation to the database : The max function requires 1 argument(s). (SQL Server 174) My SQL command is listed below and I can successfully run it from SQL Server: SELECT max($PO Number$) + 1 FROM AST_PurchaseOrder What am I doing wrong here? Thank you in advance. Windows Server 2003 SQL Server 2005 7.1.00 Patch 002 AR Server 7.0.03 Patch 006 Service Desk Asset Management CMDB - 2.1.00 Patch 002 DSL - 1.0.00 Patch 1516 Laurie Muhlethaler First Republic Bank Remedy Developer / Administrator 415.364.4436 [First Republic Bank logo]http://logos.firstrepublic.com/FRB.jpg The information transmitted is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited. If you received this in error, please contact the sender and delete the material from any computer. This message cannot be guaranteed to be secure or error-free. First Republic Bank and its related entities do not take responsibility for, or accept time-sensitive instructions sent by email including orders, funds transfer instructions or stop payments on checks. All instructions of this nature must be handled by direct communication, not email. We reserve the right to monitor and review the content of all email communications sent or received. Emails sent to or from this address may be stored in accordance with regulatory requirements. First Republic Bank is a Division of Merrill Lynch Bank Trust Co., FSB __Platinum Sponsor: www.rmsportal.com ARSlist: Where the Answers Are html___ __ _ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: www.rmsportal.com ARSlist: Where the Answers Are == = = --- - -- The information transmitted is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by persons
Re: SQL statement error
Okay, I see what's going on. You need to use direct SQL nad drop the $ around the PO Number. I was off a bit in my previous post. Norm nailed it on the head, as usual. Good job, Norm. Gary -Original Message- From: Action Request System discussion list(ARSList) [mailto:arsl...@arslist.org] On Behalf Of Kaiser, Norm E CIV USAF AFMC 96 CS/SCCE Sent: Wednesday, December 17, 2008 1:49 PM To: arslist@ARSLIST.ORG Subject: Re: SQL statement error What I was trying to say in my original reply (and communicated poorly) was that $PO Number$ tells Remedy to translate what's currently in the PO Number field and poke it into the SQL statement. So if PO Number currently contains the value skizzle, you'll end up passing... max(skizzle) + 1 FROM AST_PurchaseOrder to the database! Which results in...error! So you need to supply the actual field name, as it's represented in the database, which is probably PO_Number. So the SQL would be... max(PO_Number) + 1 FROM AST_PurchaseOrder And then just set PO Number in the SET FIELDS action to $1$. Norm -Original Message- From: Action Request System discussion list(ARSList) [mailto:arsl...@arslist.org] On Behalf Of Muhlethaler, Laurie Sent: Wednesday, December 17, 2008 1:38 PM To: arslist@ARSLIST.ORG Subject: Re: SQL statement error Yes - that's exactly what I'm doing. I am doing it via a Set Fields action. I don't want to do this through a Direct SQL If Action, do I? Laurie Muhlethaler Remedy Developer / Administrator On Wed, 17 Dec 2008 13:31:06 -0600, Opela, Gary L CTR USAF AFMC 72 CS/SCBAH gary.opela@tinker.af.mil wrote: Do a set fields from direct SQL. Have your sql query be: SELECT max($PO Number$) + 1 FROM AST_PurchaseOrder Map your PO Number field at the bottom to $1$ Is this what you are doing? Thanks, Gary Opela, Jr. -Original Message- From: Action Request System discussion list(ARSList) [mailto:arsl...@arslist.org] On Behalf Of Muhlethaler, Laurie Sent: Wednesday, December 17, 2008 1:29 PM To: arslist@ARSLIST.ORG Subject: Re: SQL statement error I'm not sure I understand. I want to auto-populate the PO Number field with the next available PO #. I would like to accomplish this through direct SQL rather than creating an additional field(s) and workflow? Laurie Muhlethaler Remedy Developer / Administrator On Wed, 17 Dec 2008 13:15:41 -0600, Kaiser, Norm E CIV USAF AFMC 96 CS/SCCE norm.kai...@eglin.af.mil wrote: Looks like $PO Number$ is a field reference and Remedy is passing the contents of PO Number (not the field name) to SQL, which is likely NULL because the field is empty when you run this. -Original Message- From: Action Request System discussion list(ARSList) [mailto:arsl...@arslist.org] On Behalf Of Muhlethaler, Laurie Sent: Wednesday, December 17, 2008 1:11 PM To: arslist@ARSLIST.ORG Subject: SQL statement error ** Listers ~ I am getting the following error during a SQL set fields action: ARERR [552] Failure during SQL operation to the database : The max function requires 1 argument(s). (SQL Server 174) My SQL command is listed below and I can successfully run it from SQL Server: SELECT max($PO Number$) + 1 FROM AST_PurchaseOrder What am I doing wrong here? Thank you in advance. Windows Server 2003 SQL Server 2005 7.1.00 Patch 002 AR Server 7.0.03 Patch 006 Service Desk Asset Management CMDB - 2.1.00 Patch 002 DSL - 1.0.00 Patch 1516 Laurie Muhlethaler First Republic Bank Remedy Developer / Administrator 415.364.4436 [First Republic Bank logo]http://logos.firstrepublic.com/FRB.jpg The information transmitted is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited. If you received this in error, please contact the sender and delete the material from any computer. This message cannot be guaranteed to be secure or error-free. First Republic Bank and its related entities do not take responsibility for, or accept time-sensitive instructions sent by email including orders, funds transfer instructions or stop payments on checks. All instructions of this nature must be handled by direct communication, not email. We reserve the right to monitor and review the content of all email communications sent or received. Emails sent to or from this address may be stored in accordance with regulatory requirements. First Republic Bank is a Division of Merrill Lynch Bank Trust Co., FSB __Platinum Sponsor: www.rmsportal.com ARSlist: Where the Answers Are html___ __ _ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: www.rmsportal.com ARSlist: Where
Re: SQL statement error
usually the value within max() is a field name, not what I would expect you have in the $PO Number$ field. You need to replace $PO Number$ with a field name that you are wanting the max value of. _ From: Action Request System discussion list(ARSList) [mailto:arsl...@arslist.org] On Behalf Of Muhlethaler, Laurie Sent: Wednesday, December 17, 2008 12:11 PM To: arslist@ARSLIST.ORG Subject: SQL statement error ** Listers ~ I am getting the following error during a SQL set fields action: ARERR [552] Failure during SQL operation to the database : The max function requires 1 argument(s). (SQL Server 174) My SQL command is listed below and I can successfully run it from SQL Server: SELECT max($PO Number$) + 1 FROM AST_PurchaseOrder What am I doing wrong here? Thank you in advance. Windows Server 2003 SQL Server 2005 7.1.00 Patch 002 AR Server 7.0.03 Patch 006 Service Desk Asset Management CMDB - 2.1.00 Patch 002 DSL - 1.0.00 Patch 1516 Laurie Muhlethaler First Republic Bank Remedy Developer / Administrator 415.364.4436 [First Republic Bank logo] http://logos.firstrepublic.com/FRB.jpg _ The information transmitted is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited. If you received this in error, please contact the sender and delete the material from any computer. This message cannot be guaranteed to be secure or error-free. First Republic Bank and its related entities do not take responsibility for, or accept time-sensitive instructions sent by email including orders, funds transfer instructions or stop payments on checks. All instructions of this nature must be handled by direct communication, not email. We reserve the right to monitor and review the content of all email communications sent or received. Emails sent to or from this address may be stored in accordance with regulatory requirements. First Republic Bank is a Division of Merrill Lynch Bank Trust Co., FSB __Platinum Sponsor: www.rmsportal.com ARSlist: Where the Answers Are html___ ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: www.rmsportal.com ARSlist: Where the Answers Are
Re: SQL statement error
Check your ASR SQL log files and see exactly what the statement looks like. On Wed, Dec 17, 2008 at 2:10 PM, Muhlethaler, Laurie lmuhletha...@firstrepublic.com wrote: ** Listers ~ I am getting the following error during a SQL set fields action: ARERR [552] Failure during SQL operation to the database : The max function requires 1 argument(s). (SQL Server 174) My SQL command is listed below and I can successfully run it from SQL Server: SELECT max($PO Number$) + 1 FROM AST_PurchaseOrder What am I doing wrong here? Thank you in advance. Windows Server 2003 SQL Server 2005 * * *7.1.00 Patch 002* AR Server *7.0.03 Patch 006* Service Desk Asset Management CMDB - 2.1.00 Patch 002 DSL - 1.0.00 Patch 1516 *Laurie Muhlethaler* First Republic Bank Remedy Developer / Administrator 415.364.4436 [image: [First Republic Bank logo]] -- The information transmitted is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited. If you received this in error, please contact the sender and delete the material from any computer. This message cannot be guaranteed to be secure or error-free. First Republic Bank and its related entities do not take responsibility for, or accept time-sensitive instructions sent by email including orders, funds transfer instructions or stop payments on checks. All instructions of this nature must be handled by direct communication, not email. We reserve the right to monitor and review the content of all email communications sent or received. Emails sent to or from this address may be stored in accordance with regulatory requirements. First Republic Bank is a Division of Merrill Lynch Bank Trust Co., FSB __Platinum Sponsor: www.rmsportal.com ARSlist: Where the Answers Are html___ ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: www.rmsportal.com ARSlist: Where the Answers Are
Re: SQL statement error
then your statement should be SELECT max(po_number) FROM AST_PurchaseOrder assuming the DB name of the field is po_number and then I personally would do a second setfield to set $PO Number$ = $PO Number$+1 -Original Message- From: Action Request System discussion list(ARSList) [mailto:arsl...@arslist.org] On Behalf Of Muhlethaler, Laurie Sent: Wednesday, December 17, 2008 12:29 PM To: arslist@ARSLIST.ORG Subject: Re: SQL statement error I'm not sure I understand. I want to auto-populate the PO Number field with the next available PO #. I would like to accomplish this through direct SQL rather than creating an additional field(s) and workflow? Laurie Muhlethaler Remedy Developer / Administrator On Wed, 17 Dec 2008 13:15:41 -0600, Kaiser, Norm E CIV USAF AFMC 96 CS/SCCE norm.kai...@eglin.af.mil wrote: Looks like $PO Number$ is a field reference and Remedy is passing the contents of PO Number (not the field name) to SQL, which is likely NULL because the field is empty when you run this. -Original Message- From: Action Request System discussion list(ARSList) [mailto:arsl...@arslist.org] On Behalf Of Muhlethaler, Laurie Sent: Wednesday, December 17, 2008 1:11 PM To: arslist@ARSLIST.ORG Subject: SQL statement error ** Listers ~ I am getting the following error during a SQL set fields action: ARERR [552] Failure during SQL operation to the database : The max function requires 1 argument(s). (SQL Server 174) My SQL command is listed below and I can successfully run it from SQL Server: SELECT max($PO Number$) + 1 FROM AST_PurchaseOrder What am I doing wrong here? Thank you in advance. Windows Server 2003 SQL Server 2005 7.1.00 Patch 002 AR Server 7.0.03 Patch 006 Service Desk Asset Management CMDB - 2.1.00 Patch 002 DSL - 1.0.00 Patch 1516 Laurie Muhlethaler First Republic Bank Remedy Developer / Administrator 415.364.4436 [First Republic Bank logo]http://logos.firstrepublic.com/FRB.jpg The information transmitted is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited. If you received this in error, please contact the sender and delete the material from any computer. This message cannot be guaranteed to be secure or error-free. First Republic Bank and its related entities do not take responsibility for, or accept time-sensitive instructions sent by email including orders, funds transfer instructions or stop payments on checks. All instructions of this nature must be handled by direct communication, not email. We reserve the right to monitor and review the content of all email communications sent or received. Emails sent to or from this address may be stored in accordance with regulatory requirements. First Republic Bank is a Division of Merrill Lynch Bank Trust Co., FSB __Platinum Sponsor: www.rmsportal.com ARSlist: Where the Answers Are html___ ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: www.rmsportal.com ARSlist: Where the Answers Are === = -- The information transmitted is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited. If you received this in error, please contact the sender and delete the material from any computer. This message cannot be guaranteed to be secure or error-free. First Republic Bank and its related entities do not take responsibility for, or accept time-sensitive instructions sent by email including orders, funds transfer instructions or stop payments on checks. All instructions of this nature must be handled by direct communication, not email. We reserve the right to monitor and review the content of all email communications sent or received. Emails sent to or from this address may be stored in accordance with regulatory requirements. First Republic Bank is a Division of Merrill Lynch Bank Trust Co., FSB ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: www.rmsportal.com ARSlist: Where the Answers Are ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: www.rmsportal.com
Re: SQL statement error
That did it! Thank you very much Norm and Conny (actually Conny Martin clued me in first)! And thank you to everyone else who responded! Laurie Muhlethaler Remedy Developer / Administrator On Wed, 17 Dec 2008 13:49:26 -0600, Kaiser, Norm E CIV USAF AFMC 96 CS/SCCE norm.kai...@eglin.af.mil wrote: What I was trying to say in my original reply (and communicated poorly) was that $PO Number$ tells Remedy to translate what's currently in the PO Number field and poke it into the SQL statement. So if PO Number currently contains the value skizzle, you'll end up passing... max(skizzle) + 1 FROM AST_PurchaseOrder to the database! Which results in...error! So you need to supply the actual field name, as it's represented in the database, which is probably PO_Number. So the SQL would be... max(PO_Number) + 1 FROM AST_PurchaseOrder And then just set PO Number in the SET FIELDS action to $1$. Norm -Original Message- From: Action Request System discussion list(ARSList) [mailto:arsl...@arslist.org] On Behalf Of Muhlethaler, Laurie Sent: Wednesday, December 17, 2008 1:38 PM To: arslist@ARSLIST.ORG Subject: Re: SQL statement error Yes - that's exactly what I'm doing. I am doing it via a Set Fields action. I don't want to do this through a Direct SQL If Action, do I? Laurie Muhlethaler Remedy Developer / Administrator On Wed, 17 Dec 2008 13:31:06 -0600, Opela, Gary L CTR USAF AFMC 72 CS/SCBAH gary.opela@tinker.af.mil wrote: Do a set fields from direct SQL. Have your sql query be: SELECT max($PO Number$) + 1 FROM AST_PurchaseOrder Map your PO Number field at the bottom to $1$ Is this what you are doing? Thanks, Gary Opela, Jr. -Original Message- From: Action Request System discussion list(ARSList) [mailto:arsl...@arslist.org] On Behalf Of Muhlethaler, Laurie Sent: Wednesday, December 17, 2008 1:29 PM To: arslist@ARSLIST.ORG Subject: Re: SQL statement error I'm not sure I understand. I want to auto-populate the PO Number field with the next available PO #. I would like to accomplish this through direct SQL rather than creating an additional field(s) and workflow? Laurie Muhlethaler Remedy Developer / Administrator On Wed, 17 Dec 2008 13:15:41 -0600, Kaiser, Norm E CIV USAF AFMC 96 CS/SCCE norm.kai...@eglin.af.mil wrote: Looks like $PO Number$ is a field reference and Remedy is passing the contents of PO Number (not the field name) to SQL, which is likely NULL because the field is empty when you run this. -Original Message- From: Action Request System discussion list(ARSList) [mailto:arsl...@arslist.org] On Behalf Of Muhlethaler, Laurie Sent: Wednesday, December 17, 2008 1:11 PM To: arslist@ARSLIST.ORG Subject: SQL statement error ** Listers ~ I am getting the following error during a SQL set fields action: ARERR [552] Failure during SQL operation to the database : The max function requires 1 argument(s). (SQL Server 174) My SQL command is listed below and I can successfully run it from SQL Server: SELECT max($PO Number$) + 1 FROM AST_PurchaseOrder What am I doing wrong here? Thank you in advance. Windows Server 2003 SQL Server 2005 7.1.00 Patch 002 AR Server 7.0.03 Patch 006 Service Desk Asset Management CMDB - 2.1.00 Patch 002 DSL - 1.0.00 Patch 1516 Laurie Muhlethaler First Republic Bank Remedy Developer / Administrator 415.364.4436 [First Republic Bank logo]http://logos.firstrepublic.com/FRB.jpg The information transmitted is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited. If you received this in error, please contact the sender and delete the material from any computer. This message cannot be guaranteed to be secure or error-free. First Republic Bank and its related entities do not take responsibility for, or accept time-sensitive instructions sent by email including orders, funds transfer instructions or stop payments on checks. All instructions of this nature must be handled by direct communication, not email. We reserve the right to monitor and review the content of all email communications sent or received. Emails sent to or from this address may be stored in accordance with regulatory requirements. First Republic Bank is a Division of Merrill Lynch Bank Trust Co., FSB __Platinum Sponsor: www.rmsportal.com ARSlist: Where the Answers Are html___ _ _ _ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: www.rmsportal.com ARSlist: Where the Answers
Re: SQL statement error
setfield with direct SQL :) -Original Message- From: Action Request System discussion list(ARSList) [mailto:arsl...@arslist.org] On Behalf Of Opela, Gary L CTR USAF AFMC 72 CS/SCBAH Sent: Wednesday, December 17, 2008 12:41 PM To: arslist@ARSLIST.ORG Subject: Re: SQL statement error If you want to use select MAX, then you'll have to use direct SQL. -Original Message- From: Action Request System discussion list(ARSList) [mailto:arsl...@arslist.org] On Behalf Of Muhlethaler, Laurie Sent: Wednesday, December 17, 2008 1:38 PM To: arslist@ARSLIST.ORG Subject: Re: SQL statement error Yes - that's exactly what I'm doing. I am doing it via a Set Fields action. I don't want to do this through a Direct SQL If Action, do I? Laurie Muhlethaler Remedy Developer / Administrator On Wed, 17 Dec 2008 13:31:06 -0600, Opela, Gary L CTR USAF AFMC 72 CS/SCBAH gary.opela@tinker.af.mil wrote: Do a set fields from direct SQL. Have your sql query be: SELECT max($PO Number$) + 1 FROM AST_PurchaseOrder Map your PO Number field at the bottom to $1$ Is this what you are doing? Thanks, Gary Opela, Jr. -Original Message- From: Action Request System discussion list(ARSList) [mailto:arsl...@arslist.org] On Behalf Of Muhlethaler, Laurie Sent: Wednesday, December 17, 2008 1:29 PM To: arslist@ARSLIST.ORG Subject: Re: SQL statement error I'm not sure I understand. I want to auto-populate the PO Number field with the next available PO #. I would like to accomplish this through direct SQL rather than creating an additional field(s) and workflow? Laurie Muhlethaler Remedy Developer / Administrator On Wed, 17 Dec 2008 13:15:41 -0600, Kaiser, Norm E CIV USAF AFMC 96 CS/SCCE norm.kai...@eglin.af.mil wrote: Looks like $PO Number$ is a field reference and Remedy is passing the contents of PO Number (not the field name) to SQL, which is likely NULL because the field is empty when you run this. -Original Message- From: Action Request System discussion list(ARSList) [mailto:arsl...@arslist.org] On Behalf Of Muhlethaler, Laurie Sent: Wednesday, December 17, 2008 1:11 PM To: arslist@ARSLIST.ORG Subject: SQL statement error ** Listers ~ I am getting the following error during a SQL set fields action: ARERR [552] Failure during SQL operation to the database : The max function requires 1 argument(s). (SQL Server 174) My SQL command is listed below and I can successfully run it from SQL Server: SELECT max($PO Number$) + 1 FROM AST_PurchaseOrder What am I doing wrong here? Thank you in advance. Windows Server 2003 SQL Server 2005 7.1.00 Patch 002 AR Server 7.0.03 Patch 006 Service Desk Asset Management CMDB - 2.1.00 Patch 002 DSL - 1.0.00 Patch 1516 Laurie Muhlethaler First Republic Bank Remedy Developer / Administrator 415.364.4436 [First Republic Bank logo]http://logos.firstrepublic.com/FRB.jpg The information transmitted is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited. If you received this in error, please contact the sender and delete the material from any computer. This message cannot be guaranteed to be secure or error-free. First Republic Bank and its related entities do not take responsibility for, or accept time-sensitive instructions sent by email including orders, funds transfer instructions or stop payments on checks. All instructions of this nature must be handled by direct communication, not email. We reserve the right to monitor and review the content of all email communications sent or received. Emails sent to or from this address may be stored in accordance with regulatory requirements. First Republic Bank is a Division of Merrill Lynch Bank Trust Co., FSB __Platinum Sponsor: www.rmsportal.com ARSlist: Where the Answers Are html___ __ _ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: www.rmsportal.com ARSlist: Where the Answers Are == = = --- - -- The information transmitted is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited. If you received this in error, please contact the sender and delete the material from any computer. This message cannot be guaranteed to be secure or error-free. First
