The Forums on slxdeveloper.com are now retired. The forum archive will remain available for the time being. Thank you for your participation on slxdeveloper.com!
|
|
Error Deleting an Account
Posted: 30 Jan 09 8:37 AM
|
Good morning! I am not sure if I am posting this in the right spot or not, but here goes. I am trying to delete an account record on my dev side and I am getting the following error:
An unusual error has occurred when executing Edit:DeleteItem. Contact your system administrator. 0x1 @0:Function Edit:DeleteItem Execption: Cannot perform this operation on a closed dataset
Can someone tell me what this is and a possible way to fix it? It is working fine on the production side, so it must be something that was changed on the dev side but I don't know where to look for it to fix it.
Thanks,
Renee |
|
|
|
Re: Error Deleting an Account
Posted: 30 Jan 09 9:54 AM
|
I'm getting the same error in a 7.22 and 7.5.1 database.....
Duplicate JOIN?
I haven't been able to fix it...SLX wants me to test it in an eval database (so what does that have to do with MY database)....
Ticket 001-00-155220 has been submitted for this issue. |
|
|
|
Re: Error Deleting an Account
Posted: 30 Jan 09 10:19 AM
|
Well, I'm glad to hear that I am not the only one. My concern is that if I bundle my changes and put them into production, will it then break on the production side? Or is this just something weird on the dev side. I am currently working in 6.2.6.1007.
Renee |
|
|
|
Re: Error Deleting an Account
Posted: 01 Feb 09 10:38 AM
|
OK I dropped 1 table: ACCOUNT_MAXOPPPRODUCT. and every thing is fine (other than I lost that table's functionality).
Tablename too long?
Primary Index name based on table name too long.
SECCODEID present or not present, with a value or not.
There are 6 clustered indexes on this table as well, AccountID + the Field.
<<<<<<<<<<<<<<<<<<<<<<>>>>>>>>>>>>>>>>>>>>>>>>>
What I'm focusing on is a custom 1:1 table off of Account:
ACCOUNT_MAXOPPPRODUCT
the Primary Index is the AccountID, index name is
PK_ACCOUNT_MAXOPPPRODUCT_PRIMARY
This is too many characters? 32.
Anyway, you CAN'T bundle it into Production, the bundle step action fails.....
If you create the table in production....then bundle in the create table from DEV, it will only add the fields to production that it was missing from DEV.
Am also looking at the presence or Absence of a SECCODEID field (created in 7.2+ for 1:X tables off of Account), and a correctcorresponding value.
Dropping tables fixed the error....my problem for debugging is I dropped some pretty exotic tables, and maybe six of them.....at once time. But looking at other databases (Nov Dev, Dec Dev, PreStage, et al). I should be able to get at the bottom of this.......
|
|
|
|
Re: Error Deleting an Account
Posted: 02 Feb 09 7:46 AM
|
Sounds like a start.
I too added some new tables to the process. Maybe that is the problem. Something isn't right with the new tables added.
I have already created the tables in production. The tables that I created are not off of the Account table. I have a main one called Account_Serviceagreem which I have the Accountid field in there as just a field that I update when the record is created and then two tables off of that called Svc_Agreement_Info and Svc_Agreement_Ins which has the Account_Servicagreemid as the index.
Maybe that is what I am doing wrong. Maybe I need to take the Account_Serviceagreem table and change the Accountid field to be indexed????
Or maybe I should change it so that it is part of the Account table?????
Thanks for all of your assistance!
Renee |
|
|
|
Re: Error Deleting an Account
Posted: 02 Feb 09 8:12 AM
|
Hi guys,
This error sparked my curiosity. 
- Has anything popped up in the SQL logs?
- Have either of you had an oppotrtunity to run a SQL trace while performing the bundle or table additions? If you do run one, I'd suggest adding events from the "Errors and Warnings" and the "DB locks and Escalations" and remote functions (RPC) where appropriate
- If nothing turns up on SQL trace; what about SLXProfiler?
Carla |
|
|
|
Re: Error Deleting an Account
Posted: 02 Feb 09 8:56 AM
|
OK, got into work this morning and compared 4 different (but the 'same' databases.....two of them I can delete the account (and its records), two I can't.
Good news! It has nothing to do with SECCODEID, the length of the table name (and primary index), etc.
It does have to do with clustered indexes put on the table (and possibly triggers)...if I remove the clustered indexes, all is well.
Here's one (of 2) indexes:
/****** Object: Index [IX_MULTI_ACCOUNTID_CWIDS] Script Date: 02/02/2009 08:47:03 ******/
CREATE NONCLUSTERED INDEX [IX_MULTI_ACCOUNTID_CWIDS] ON [sysdba].[ACCOUNT_MAXOPPPRODUCT]
(
[ACCOUNTID] ASC,
[DES_CW_OPPPRODUCTID] ASC,
[DEH_CW_OPPPRODUCTID] ASC,
[DESC_CW_OPPPRODUCTID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = OFF) ON [PRIMARY]
AccountID is the primary index. This basically is a table hanging off of Account that stores the latest OppProductID for that Account for three different Product Categories (DES, DEH, DESc).
Here's the other one (same table):
USE [SalesLogix]
GO
/****** Object: Index [PK_ACCOUNT_MAXOPPPRODUCT] Script Date: 02/02/2009 09:14:47 ******/
ALTER TABLE [sysdba].[ACCOUNT_MAXOPPPRODUCT] ADD CONSTRAINT [PK_ACCOUNT_MAXOPPPRODUCT] PRIMARY KEY CLUSTERED
(
[ACCOUNTID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
The contstraint on this Primary key is the delete killer.
Someone else was posting about problems with Address? deletions in the 7.5.1 beta, there's a trigger in Sage ExchangeLink...... |
|
|
|
Re: Error Deleting an Account
Posted: 02 Feb 09 9:22 AM
|
Thanks for your help!
This is all very new to me. I don't know how to run a SQL trace but my error message is coming up when I try to delete an account in the dev environment. I have not tried to bundle my changes yet and I have no problems when I am adding new information, only when I try to delete.
I just ran the SLXProfiler and I am getting this error message:
Syntax error converting the varchar value 'A9T7UA3000RZ' to a column of data type int.
Any idea if this has something to do with my new tables?
Renee |
|
|
|
Re: Error Deleting an Account
Posted: 02 Feb 09 9:45 AM
|
Hi Renee,
It sounds like you just need to change a field type. You may have to move data around, but nothing major. 
Pop into DBManager and navigate to your custom tables.
Review your fields that hold ID data. Make sure they are all set to "StandardID" for field type..
Carla |
|
|
|
Re: Error Deleting an Account
Posted: 02 Feb 09 10:11 AM
|
I found a table that has a Id field in it. It is not the Id field for the table but is a stored ID for another table. It is set as varchar(32). When I right click on the field and go to the properties I cannot change the field type. Is there a way that I can do this, or do I have to delete the field and re-enter it?
Thanks,
Renee |
|
|
|
Re: Error Deleting an Account
Posted: 02 Feb 09 10:19 AM
|
You will need to delete and recreate it with the correct type. However... This one (as it is a Varchar) does not sound like the problem child.
Make sure you are reviewing the field types via the DBManger as opposed to a SQL interface. Saleslogix has to 'handle' data types before sending to the database; so even if you fix the field by back-end means, it still may not function the way you want. Review your fields again and look at those fields that are of short or long integer types and see if anything looks 'out of place'.
Carla
|
|
|
|
Re: Error Deleting an Account
Posted: 02 Feb 09 11:43 AM
|
Okay, I think I am getting closer to the problem. I have found this statement that is incorrect but I don't know how to tell where it is being run from:
SELECT DISTINCT ACCOUNT_SERVICEAGREEMID FROM ACCOUNT_SERVICEAGREEM WHERE PARENT_NO = 'AQF8AA001PSK'
*** The parent_no field is a long integer.
Now, can you tell me how to find out where this statement is being executed? Like I said earlier, I am having this problem when I try and delete an account. I don't know where to go to check the code that does the deleting. Can you please point me in the right direction?
Thank you very much for all of you help and patience!
Renee |
|
|
|
Re: Error Deleting an Account
Posted: 02 Feb 09 11:57 AM
|
You are right on target!. Just change Parent_NO to StandardID and you should be good to go.
The deletion is an internal function that is generating the code to delete main table and related tables. This information is set in the Join Manager.
Carla |
|
|
|
Re: Error Deleting an Account
Posted: 02 Feb 09 12:56 PM
|
Carla,
I am sorry to be such a bother. My problem is that I don't know how the Parent_No field was chosen. I don't want the Parent_No field to be a StandardID field. It is actually suppose to be a long integer.
Do you know where I might have gone wrong with this? Here is some background info:
I have the main table Account.
Then I have another table called Account_Misc that is a 1:1 with Account and has the AccountID field but no Account_MiscID field. This table has a field that is a long integer field called Parent.
Then I have another table called Account_ServiceAgreem that is not associated with any table but in the Global Join it is linked with the Account_Misc table by Account_Misc.Parent and Account_ServiceAgreem.Parent_No.
Thanks again! |
|
|
|
Re: Error Deleting an Account
Posted: 02 Feb 09 1:39 PM
|
Get yourself a back up of the table or whole db then try removing the join 'Account_Misc table by Account_Misc.Parent and Account_ServiceAgreem.Parent_No' join.
It 'sounds' incorrect. If this causes any unwanted repercussions, restore the join.
c |
|
|
|
Re: Error Deleting an Account
Posted: 02 Feb 09 3:04 PM
|
Perfect! I removed that join and everything seems to be working fine now!
Thanks for all of you assistance! It was greatly appreciated!
Renee |
|
|
| |
|