Re: Another view form question...
Look into using a sequence via a trigger to populate the column. Axton Grams On Wed, Aug 20, 2008 at 4:13 PM, Moore, Christopher Allen [EMAIL PROTECTED] wrote: Drew- I figured I'd have to create a new column, but how can I populate it? Is there a way in SQL to generate a GUID for each entry? Forgive my ignorance here; I don't know a lot on the SQL side. Thanks, Chris -Original Message- From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of Drew Shuller Sent: Wednesday, August 20, 2008 3:09 PM To: arslist@ARSLIST.ORG Subject: Re: Another view form question... You've got to have that key field and it must be in the format that is in the documentation. Create a new column if you don't have a suitable one already. Drew On Wed, 20 Aug 2008, Moore, Christopher Allen wrote: I have a problem creating a view form- thanks to Joe's advice I am now able to see all the columns. My problem now is designating a key field. The only column which is guaranteed to be unique is a GUID passed from the external data source, but it's too long- 25 characters. Has anyone else run into this problem, and how did you get around it? I can't use ROW_ID because this table will be overwritten monthly. I know just enough SQL to be dangerous...is there some sort of way to add a column and ensure it's unique via SQL? ITSM 7.0.1 p6 SQL 2005 SP2 Win 2003 svr I appreciate any advice! Chris ___ 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 ARSlist: Where the Answers Are ___ 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 ARSlist: Where the Answers Are
Re: Another view form question...
SQL2005 has a newid() function that you can set a column to. It creates a 16-byte unique identifier...however, that may still be too long for Remedy...but it is worth a shot. http://msdn.microsoft.com/en-us/library/ms190348(SQL.90).aspx Note that this may affect the way data is imported into the table from the external source. If your insert statements don't list the column names then changing or adding a column to the table may cause insert issues. If, on the other hand, your insert statements do list field names you should be okay. Here's a clearer explanation. Say your table (MyTable) has 5 columns named A, B, C, D and E. An insert can be implicit like so: INSERT MyTable VALUES (ValueA, ValueB, ValueC, ValueD, ValueE) SQL knows what to do because you have one value for each column. However if you were then to add a GUID column, defined as newid() you'd get an error trying to perform the insert because there would be more columns than data. If, however, your insert statement is explicit like so: INSERT MyTable (A, B, C, D, E) VALUES (ValueA, ValueB, ValueC, ValueD, ValueE) You should be okay because SQL would know which value goes with which column. The newid() function, however, may require a value be sent to it (see Example B on the URL above) which may require changing your data import method anyway. Sorry if this is overly technical...I was a DBA in a previous life and its hard to shake old habits. --- J.T. Shyman -Original Message- From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of Axton Sent: Thursday, August 21, 2008 9:29 AM To: arslist@ARSLIST.ORG Subject: Re: Another view form question... Look into using a sequence via a trigger to populate the column. Axton Grams On Wed, Aug 20, 2008 at 4:13 PM, Moore, Christopher Allen [EMAIL PROTECTED] wrote: Drew- I figured I'd have to create a new column, but how can I populate it? Is there a way in SQL to generate a GUID for each entry? Forgive my ignorance here; I don't know a lot on the SQL side. Thanks, Chris ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: www.rmsportal.com ARSlist: Where the Answers Are
Re: Another view form question...
Why can't you use ROW_ID? You only need the unique ID for displaying the View Form. When selecting records from the View form save the GUID and work with that (just make sure that field is indexed on your table). When the table is overwritten there is no problem because you have never saved the temporary ROW_ID value to any other table. Fred -Original Message- From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of Moore, Christopher Allen Sent: Wednesday, August 20, 2008 3:13 PM To: arslist@ARSLIST.ORG Subject: Re: Another view form question... Drew- I figured I'd have to create a new column, but how can I populate it? Is there a way in SQL to generate a GUID for each entry? Forgive my ignorance here; I don't know a lot on the SQL side. Thanks, Chris -Original Message- From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of Drew Shuller Sent: Wednesday, August 20, 2008 3:09 PM To: arslist@ARSLIST.ORG Subject: Re: Another view form question... You've got to have that key field and it must be in the format that is in the documentation. Create a new column if you don't have a suitable one already. Drew On Wed, 20 Aug 2008, Moore, Christopher Allen wrote: I have a problem creating a view form- thanks to Joe's advice I am now able to see all the columns. My problem now is designating a key field. The only column which is guaranteed to be unique is a GUID passed from the external data source, but it's too long- 25 characters. Has anyone else run into this problem, and how did you get around it? I can't use ROW_ID because this table will be overwritten monthly. I know just enough SQL to be dangerous...is there some sort of way to add a column and ensure it's unique via SQL? ITSM 7.0.1 p6 SQL 2005 SP2 Win 2003 svr I appreciate any advice! Chris ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: www.rmsportal.com ARSlist: Where the Answers Are
Re: Another view form question...
I use the SQL 2005 row_number function to do something that sounds like what you want, in a SQL view, and then expose that as a View Form in remedy: SELECT cast(row_number() OVER (ORDER BY somecolumn) AS int) AS Record, column2, column3. One note, don't do this. Since the row_number is generated dynamically it is not permanently bound to a certain record unless you export it into a regular table (or a regular remedy form). I didn't think would be a problem until I found this out the hard way: Search results would sometimes display record A in the results section, and record B in the details section. This was not immediately obvious, because it only has this behavior when you have search criteria that filters some records. Hope this helps somehow! Brien On Wed, Aug 20, 2008 at 12:53 PM, Moore, Christopher Allen [EMAIL PROTECTED] wrote: ** I have a problem creating a view form- thanks to Joe's advice I am now able to see all the columns. My problem now is designating a key field. The only column which is guaranteed to be unique is a GUID passed from the external data source, but it's too long- 25 characters. Has anyone else run into this problem, and how did you get around it? I can't use ROW_ID because this table will be overwritten monthly. I know just enough SQL to be dangerous…is there some sort of way to add a column and ensure it's unique via SQL? ITSM 7.0.1 p6 SQL 2005 SP2 Win 2003 svr I appreciate any advice! Chris __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
Another view form question...
I have a problem creating a view form- thanks to Joe's advice I am now able to see all the columns. My problem now is designating a key field. The only column which is guaranteed to be unique is a GUID passed from the external data source, but it's too long- 25 characters. Has anyone else run into this problem, and how did you get around it? I can't use ROW_ID because this table will be overwritten monthly. I know just enough SQL to be dangerous...is there some sort of way to add a column and ensure it's unique via SQL? ITSM 7.0.1 p6 SQL 2005 SP2 Win 2003 svr I appreciate any advice! Chris ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: www.rmsportal.com ARSlist: Where the Answers Are
Re: Another view form question...
You've got to have that key field and it must be in the format that is in the documentation. Create a new column if you don't have a suitable one already. Drew On Wed, 20 Aug 2008, Moore, Christopher Allen wrote: I have a problem creating a view form- thanks to Joe's advice I am now able to see all the columns. My problem now is designating a key field. The only column which is guaranteed to be unique is a GUID passed from the external data source, but it's too long- 25 characters. Has anyone else run into this problem, and how did you get around it? I can't use ROW_ID because this table will be overwritten monthly. I know just enough SQL to be dangerous...is there some sort of way to add a column and ensure it's unique via SQL? ITSM 7.0.1 p6 SQL 2005 SP2 Win 2003 svr I appreciate any advice! Chris ___ 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 ARSlist: Where the Answers Are
Re: Another view form question...
Drew- I figured I'd have to create a new column, but how can I populate it? Is there a way in SQL to generate a GUID for each entry? Forgive my ignorance here; I don't know a lot on the SQL side. Thanks, Chris -Original Message- From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of Drew Shuller Sent: Wednesday, August 20, 2008 3:09 PM To: arslist@ARSLIST.ORG Subject: Re: Another view form question... You've got to have that key field and it must be in the format that is in the documentation. Create a new column if you don't have a suitable one already. Drew On Wed, 20 Aug 2008, Moore, Christopher Allen wrote: I have a problem creating a view form- thanks to Joe's advice I am now able to see all the columns. My problem now is designating a key field. The only column which is guaranteed to be unique is a GUID passed from the external data source, but it's too long- 25 characters. Has anyone else run into this problem, and how did you get around it? I can't use ROW_ID because this table will be overwritten monthly. I know just enough SQL to be dangerous...is there some sort of way to add a column and ensure it's unique via SQL? ITSM 7.0.1 p6 SQL 2005 SP2 Win 2003 svr I appreciate any advice! Chris ___ 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 ARSlist: Where the Answers Are ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: www.rmsportal.com ARSlist: Where the Answers Are
Re: Another view form question...
It would be a varchar field of 15 (??) characters, starting with 0 and then write something that increments that every new entry. I'm not a SQL person so I don't know how to do that. Drew Wed, 20 Aug 2008, Moore, Christopher Allen wrote: Drew- I figured I'd have to create a new column, but how can I populate it? Is there a way in SQL to generate a GUID for each entry? Forgive my ignorance here; I don't know a lot on the SQL side. Thanks, Chris -Original Message- From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of Drew Shuller Sent: Wednesday, August 20, 2008 3:09 PM To: arslist@ARSLIST.ORG Subject: Re: Another view form question... You've got to have that key field and it must be in the format that is in the documentation. Create a new column if you don't have a suitable one already. Drew On Wed, 20 Aug 2008, Moore, Christopher Allen wrote: I have a problem creating a view form- thanks to Joe's advice I am now able to see all the columns. My problem now is designating a key field. The only column which is guaranteed to be unique is a GUID passed from the external data source, but it's too long- 25 characters. Has anyone else run into this problem, and how did you get around it? I can't use ROW_ID because this table will be overwritten monthly. I know just enough SQL to be dangerous...is there some sort of way to add a column and ensure it's unique via SQL? ITSM 7.0.1 p6 SQL 2005 SP2 Win 2003 svr I appreciate any advice! Chris ___ 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 ARSlist: Where the Answers Are ___ 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 ARSlist: Where the Answers Are
Re: Another view form question...
Using ROWID (if that pseudo column is what you meant) from that table is longer than 15 characters anyway.. If I am not mistaken it is 18 characters.. ROWNUM would be more your solution than ROWID.. Why would using ROWNUM not be appropriate? The RequestID field is just for the purpose of building your view form. You can keep your GUID field that you get from that table as another unique ID, which you could use for identifying your record instead of the Request ID.. Use the Request Id only for the purpose of respecting what ARS needs to create your view form.. Joe - Original Message From: Moore, Christopher Allen [EMAIL PROTECTED] To: arslist@ARSLIST.ORG Sent: Wednesday, August 20, 2008 3:53:42 PM Subject: Another view form question... ** I have a problem creating a view form- thanks to Joe’s advice I am now able to see all the columns. My problem now is designating a key field. The only column which is guaranteed to be unique is a GUID passed from the external data source, but it’s too long- 25 characters. Has anyone else run into this problem, and how did you get around it? I can’t use ROW_ID because this table will be overwritten monthly. I know just enough SQL to be dangerous…is there some sort of way to add a column and ensure it’s unique via SQL? ITSM 7.0.1 p6 SQL 2005 SP2 Win 2003 svr I appreciate any advice! Chris ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: www.rmsportal.com ARSlist: Where the Answers Are
Re: Another view form question...
Yes, ROWNUM is another pseudo column besides ROWID (which is longer than 15 characters anyways).. Joe - Original Message From: Moore, Christopher Allen [EMAIL PROTECTED] To: arslist@ARSLIST.ORG Sent: Wednesday, August 20, 2008 4:13:13 PM Subject: Re: Another view form question... Drew- I figured I'd have to create a new column, but how can I populate it? Is there a way in SQL to generate a GUID for each entry? Forgive my ignorance here; I don't know a lot on the SQL side. Thanks, Chris -Original Message- From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of Drew Shuller Sent: Wednesday, August 20, 2008 3:09 PM To: arslist@ARSLIST.ORG Subject: Re: Another view form question... You've got to have that key field and it must be in the format that is in the documentation. Create a new column if you don't have a suitable one already. Drew On Wed, 20 Aug 2008, Moore, Christopher Allen wrote: I have a problem creating a view form- thanks to Joe's advice I am now able to see all the columns. My problem now is designating a key field. The only column which is guaranteed to be unique is a GUID passed from the external data source, but it's too long- 25 characters. Has anyone else run into this problem, and how did you get around it? I can't use ROW_ID because this table will be overwritten monthly. I know just enough SQL to be dangerous...is there some sort of way to add a column and ensure it's unique via SQL? ITSM 7.0.1 p6 SQL 2005 SP2 Win 2003 svr I appreciate any advice! Chris ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: www.rmsportal.com ARSlist: Where the Answers Are
Re: Another view form question...
Good point- once we get the data from our view into out regular form we don't care if the ID is being re-used. Thanks again Joe! From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of Joe DeSouza Sent: Wednesday, August 20, 2008 3:20 PM To: arslist@ARSLIST.ORG Subject: Re: Another view form question... Using ROWID (if that pseudo column is what you meant) from that table is longer than 15 characters anyway.. If I am not mistaken it is 18 characters.. ROWNUM would be more your solution than ROWID.. Why would using ROWNUM not be appropriate? The RequestID field is just for the purpose of building your view form. You can keep your GUID field that you get from that table as another unique ID, which you could use for identifying your record instead of the Request ID.. Use the Request Id only for the purpose of respecting what ARS needs to create your view form.. Joe - Original Message From: Moore, Christopher Allen [EMAIL PROTECTED] To: arslist@ARSLIST.ORG Sent: Wednesday, August 20, 2008 3:53:42 PM Subject: Another view form question... ** I have a problem creating a view form- thanks to Joe's advice I am now able to see all the columns. My problem now is designating a key field. The only column which is guaranteed to be unique is a GUID passed from the external data source, but it's too long- 25 characters. Has anyone else run into this problem, and how did you get around it? I can't use ROW_ID because this table will be overwritten monthly. I know just enough SQL to be dangerous...is there some sort of way to add a column and ensure it's unique via SQL? ITSM 7.0.1 p6 SQL 2005 SP2 Win 2003 svr I appreciate any advice! Chris __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: Another view form question...
No problem.. and use ROWNUM and not ROWID.. ROWNUM starts with 1 and increments by 1 so you are very unlikely to hit the 15 character limit. ROWNUM like the ROWID is also a pseudo column and you will not need to do anything else to set a value to it - its already there once a row is created.. either in a table or a view or in the results of a select statement as below.. Look at the example below.. SQL select rownum||' '||table_name from all_Tables where owner = 'ARADMIN' and table_name like 'F%'; 1 FT_PENDING 2 FILTER_SQL 3 FILTER_SET 4 FILTER_PUSH 5 FILTER_PROCESS 6 FILTER_NOTIFY_IDS 7 FILTER_NOTIFY 8 FILTER_MESSAGE 9 FILTER_MAPPING 10 FILTER_LOG 11 FILTER_GOTOACTION 12 FILTER_GOTO 13 FILTER_EXIT 14 FILTER_CALL 15 FILTER 16 FIELD_VIEW 17 FIELD_TABLE 18 FIELD_REAL 19 FIELD_PERMISSIONS 20 FIELD_INT 21 FIELD_ENUM_VALUES 22 FIELD_ENUM 23 FIELD_DISPPROP 24 FIELD_DISPLAY 25 FIELD_DIARY 26 FIELD_DEC 27 FIELD_DATE 28 FIELD_CURR 29 FIELD_COLUMN 30 FIELD_CHAR 31 FIELD_ATTACH 32 FIELD 32 rows selected. Joe - Original Message From: Moore, Christopher Allen [EMAIL PROTECTED] To: arslist@ARSLIST.ORG Sent: Wednesday, August 20, 2008 4:22:35 PM Subject: Re: Another view form question... ** Good point- once we get the data from our view into out regular form we don’t care if the ID is being re-used. Thanks again Joe! From:Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of Joe DeSouza Sent: Wednesday, August 20, 2008 3:20 PM To: arslist@ARSLIST.ORG Subject: Re: Another view form question... Using ROWID (if that pseudo column is what you meant) from that table is longer than 15 characters anyway.. If I am not mistaken it is 18 characters.. ROWNUM would be more your solution than ROWID.. Why would using ROWNUM not be appropriate? The RequestID field is just for the purpose of building your view form. You can keep your GUID field that you get from that table as another unique ID, which you could use for identifying your record instead of the Request ID.. Use the Request Id only for the purpose of respecting what ARS needs to create your view form.. Joe - Original Message From: Moore, Christopher Allen [EMAIL PROTECTED] To: arslist@ARSLIST.ORG Sent: Wednesday, August 20, 2008 3:53:42 PM Subject: Another view form question... ** I have a problem creating a view form- thanks to Joe’s advice I am now able to see all the columns. My problem now is designating a key field. The only column which is guaranteed to be unique is a GUID passed from the external data source, but it’s too long- 25 characters. Has anyone else run into this problem, and how did you get around it? I can’t use ROW_ID because this table will be overwritten monthly. I know just enough SQL to be dangerous…is there some sort of way to add a column and ensure it’s unique via SQL? ITSM 7.0.1 p6 SQL 2005 SP2 Win 2003 svr I appreciate any advice! Chris ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: www.rmsportal.com ARSlist: Where the Answers Are
Re: Another view form question...
Does anyone have any idea why the import.exe works from the c:drive on your machine and does not work from the server Import.exe working on the windows side, here is the command, but when I try it on the server side it does not work. The arimportcmd.exe did not work on the windows side This is from my c:drive: and it works arimport.exe -u x -p x -x adaprmwn03 -n -l arlog.txt -d C:\Documents and Settings\TrevinR\Application Data\AR System\HOME\ARCmds -m ImportComputerData from the server, it does not work. Neither does the arimportcmd.exe arimport.exe -u x -p x -x 168.44.246.22 -n -l arlog.txt -d F:\Program Files\AR System\HOME\ARCmds -m ImportComputerData arimportcmd.exe -u x -p x -x 168.44.246.22 -a 51000 -d F:\Program Files\AR System\HOME\ARCmds -l arlog.txt -m ImportComputerData Error: Unable to log on to any server (ARERR 7091) __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
