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!
|
|
DB Manager crashes WA when reaches 100%...
Posted: 10 Dec 08 10:16 AM
|
This is unfortunately an older version of SLX...5.2.4. When I try to go into DB Manager in Worgroup Administrator, it immediately begins analyzing the tables. When it reaches the last table, WORKAREA, the processor on PC starts going nuts and then it crashes the Workgroup Administrator. Nothing is reported to the event logs. Any help would be greatly appreciated.
Thanks in advance...
CD |
|
|
| |
| |
|
Re: DB Manager crashes WA when reaches 100%...
Posted: 11 Dec 08 2:42 PM
|
This should work for you. Many variations have been around for a long long time. I don't remember the orgins of this or I would give credit. 
////////////// Check for Circular Joins //////////////////////////
select j1.fromtable fromtable1, j1.fromfield fromfield1,
j1.totable totable1, j1.tofield tofield1
FROM sysdba.joindata j1, sysdba.joindata j2
WHERE j1.fromtable = j2.totable
AND j1.totable = j2.fromtable
AND j1.fromfield = j2.tofield
AND j1.tofield = j2.fromfield
|
|
|
|
Re: DB Manager crashes WA when reaches 100%...
Posted: 11 Dec 08 3:08 PM
|
And how should the results be read?
Here is what I received...
fromtable1 fromfield1 totable1 tofield1
CONTACT CONTACTID CONTACTEXTENSION CONTACTID
CONTACTEXTENSION CONTACTID CONTACT CONTACTID
SALESORDER SALESORDERID SALESORDERDETAIL SALESORDERID
SALESORDERDETAIL SALESORDERID SALESORDER SALESORDERID
USERINFO USERID USERINFO USERID
Thanks for you help...cd |
|
|
|
Re: DB Manager crashes WA when reaches 100%...
Posted: 11 Dec 08 3:18 PM
|
Disclaimer: For Testing purposes only -this is not the final solution
In a test environment, back-up the joindata table
Select * into sysdba.joindata_BkUp From sysdba.joindata
Delete 1 out of each set that creates the circular join. (you do not need to delete both)
See if that resolves the DB Manager issue.
Restore the backup table when ready to resume.
Do not permenentlydelete these joins without further research. This could cause unexpected results in the database!
 |
|
|
|
Re: DB Manager crashes WA when reaches 100%...
Posted: 11 Dec 08 3:29 PM
|
Curious...
I did a fresh install of the blank db that is used for initial implementation. It contained nothing when I ran that script.
I also did this for the eval db with the same result. Doesn't that suggest that none of them should be needed?
I will post the results of the steps you mentioned above tomorrow. Thanks again and I will let you know what I find.
cd |
|
|
|
Re: DB Manager crashes WA when reaches 100%...
Posted: 11 Dec 08 3:37 PM
|
Nah, you do not need those steps if you have already tested the DBManager against an eval and blank DB with the same results.
Has anything changed on your machine(s)? Any patches applied, etc.? |
|
|
|
Re: DB Manager crashes WA when reaches 100%...
Posted: 11 Dec 08 4:44 PM
|
Let me clarify. I have not tested the DBManager against those two as of yet. I was just running the script against their dbs for reference.
In test, I was able to get to DB Manager for the Blank and Eval Databases. Still waiting on restore of live data to test machine.
I will test it later and post the results of that test.
Thanks again....cd |
|
|
|
Re: DB Manager crashes WA when reaches 100%...
Posted: 12 Dec 08 7:27 AM
|
Very nice call...
When I delete the two lines in question, DB Manager works great. When I put the two lines back in, it crashes again.
Granted, there are disclaimers all over the place when deleting records, however....anything else I should be concerned with or check into when considering permanently deleting these entries?
Thanks again for all of the help...
cd |
|
|
|
Re: DB Manager crashes WA when reaches 100%...
Posted: 12 Dec 08 8:26 AM
|
Actually, it's a pretty big deal. The joindata holds the information of how to tie one table to another so data from both tables can be used in a single call. This is employed throughout the system. You will see it is used in forms, grids and groups as well as multiple functions such as a Cascade Delete, Merging Contacts, etc. (Etc includes DBManager!)
Deleting a joindata entry * could * cause any of these operations to fail inside of SLX. That doesn't mean you can't go back and fix them properly; it just means you need to know the ramifications and know how to fix up custom forms, grids and groups. Then test, test, test. 
Carla |
|
|
|
Re: DB Manager crashes WA when reaches 100%...
Posted: 12 Dec 08 9:05 AM
|
Ok.
So, if there is nothing developed against these joins, then no loss.
The primary difference on both of those double entries was the cascade type. I will remove those two entries in test and verify all of our custom code to make sure that nothing breaks.
One other small thing. One of those original five was the userinfo:userid to userinfo:userid. Why would such a link exist or even need to? I am thinking that someone had a little too much fun in the join department and had no business being there.
Again, I will test, but do you think there is any reason why it could not be removed?
Again, I appreciate the assistance. |
|
|
| |
|
Re: DB Manager crashes WA when reaches 100%...
Posted: 13 Dec 08 9:05 PM
|
Originally posted by Carla Tillman
This should work for you. Many variations have been around for a long long time. I don't remember the orgins of this or I would give credit.
////////////// Check for Circular Joins //////////////////////////
select j1.fromtable fromtable1, j1.fromfield fromfield1,
j1.totable totable1, j1.tofield tofield1
FROM sysdba.joindata j1, sysdba.joindata j2
WHERE j1.fromtable = j2.totable
AND j1.totable = j2.fromtable
AND j1.fromfield = j2.tofield
AND j1.tofield = j2.fromfield
|
|
It came from the BP NG a long time ago.. The earliest posting I can find is:
September 3, 2001 at 12:57 pm EDT
by Amedeo
However, a slightly later posting by Ted Sturr indicates that it actually came from a "book" at the business conference. I'm assuming he is refering to a SalesLogix BP conference that happened long before BEST (now Sage) bought SalesLogix.
--
RJLedger - www.SlxWizard.com
rjlSystems |
|
|
|
Re: DB Manager crashes WA when reaches 100%...
Posted: 16 Dec 08 9:57 AM
|
Just to let this thread know...the deleting of the two duplicated records as well as the userinfo record have been done and after much testing, everything appears to be working fine. I would like to thank everyone for their assistance.
CD |
|
|
|
Re: DB Manager crashes WA when reaches 100%...
Posted: 23 Dec 08 8:57 AM
|
Originally posted by Bob (RJ)Ledger
It came from the BP NG a long time ago.. The earliest posting I can find is:
September 3, 2001 at 12:57 pm EDT
by Amedeo
However, a slightly later posting by Ted Sturr indicates that it actually came from a "book" at the business conference. I'm assuming he is refering to a SalesLogix BP conference that happened long before BEST (now Sage) bought SalesLogix.
--
RJLedger - www.SlxWizard.com
rjlSystems |
|
Amadeo Tarzia and I got this from ADAM STEFL in June 2001, it's in ,my bag of tools to fix corrupt SLX databases:
*****************************************************************
This Script is designed to find all the user (SYSDBA) table references in the system catalog on a SQL7/8 database, that are not in ResyncTableDefs or SecTableDefs.
6-22-2001 Written by Adam Stefl Saleslogix SDK Support
*/
go
select name as "Not in ResyncTableDefs" from dbo.sysobjects
where name not in (select tablename from sysdba.Resynctabledefs) and xtype = 'U' and
( name <> 'dtproperties')
go
select so.name as "TableName",sc.name as "FieldName"
from sysobjects so
left outer join syscolumns sc
on so.id = sc.id
where (so.xtype = 'U') and ( so.name <> 'dtproperties') and (so.name + sc.name not in (select tablename + fieldname from sysdba.sectabledefs))
order by so.name
8********************
SELECT DISTINCT SC.NAME FIELD_NAME, SO.NAME TABLE_NAME FROM SYSCOLUMNS SC,
SYSOBJECTS SO JOIN sysdba.SECTABLEDEFS STD ON SO.NAME=STD.TABLENAME WHERE
SO.ID = SC.ID AND NOT EXISTS(SELECT * FROM sysdba.SECTABLEDEFS SD WHERE
SD.TABLENAME=SO.NAME AND SD.FIELDNAME=SC.NAME) ORDER BY SC.NAME
There is a problem created when a db is upgraded from vers 3.x, and/or 4.x to 5.x. Thai problem has to do with fields that have had their data types changed:
Table: Field: Old: New:
OPPORTUNITY CLOSEPROBABILITY PERCENT INT
OPPORTUNITY SALESPOTENTIAL CURRENCY FLOAT
OPPORTUNITY SALESAMOUNT CURRENCY FLOAT
OPPORTUNITY ACTUALAMOUNT CURRENCY FLOAT
You need to use the WGAdmin's Execute SQL feature and sync out the following SQL commands to fix these problems.
A - Run each of these statements (separately) and change all remotes - but NOT the main db.
B - Run the sync server and send out the changes
C - Run each of the statements against the main db only.... but wait until the db is "quiet".. that is.. nobody logged into Slx and no sync, etc. happening.
ALTER TABLE sysdba.OPPORTUNITY ALTER COLUMN CLOSEPROBABILITY INT
GO
ALTER TABLE sysdba.OPPORTUNITY ALTER COLUMN SALESPOTENTIAL FLOAT
GO
ALTER TABLE sysdba.OPPORTUNITY ALTER COLUMN SALESAMOUNT FLOAT
GO
ALTER TABLE sysdba.OPPORTUNITY ALTER COLUMN ACTUALAMOUNT FLOAT
NOTE:
1. These changes have been check and verified against V5.2SP1w/HF1 on SQL7 (w/sp3), SQL2000 w/sp2, MSDE1.0 (w/SP3), and MSDE2000 (w/sp2).
2.. I have NOT been able to verify and/or test against an Oracle install....so.. I suggest (some)one (RJ?) give this an Oracle check...
What lead me to this???.. an upgraded client said that they were not seeing CLOSEPROBABILITY in the main db.. but the remotes (which had created the opportunity record) did have the correct values.. and sure enough.. tracking the sync logs, etc.. I was able to see that there was NO data in the sync (in the fields in question) from the remotes!... so when I looked at the WGAdmin DBManager... the field types said they were N/A!... but SQL Enterprise manager told me what was really there!
PS.. I think there is a problem in the PRODUCT.PRICE too.. but not able to confirm it yet... This whole mess was caused by a change in the column/field types and the user defined field/columns..
ALSO SalesStage, SalesStep…..Opportunity_SALESSTAGE, OPPORTUNITY_SALESSTEP
... nuff said.. for now..
RJLedger - thg
Select * from sysdba.ACCOUNT where ACCOUNT_UC is NULL
If you get hits.. then
UPDATE sysdba.ACCOUNT Set ACCOUNT_UC = UPPER(ACCOUNT) where ACCOUNT_UC is NULL
and also against remotes...
also check:
Select * from sysdba.CONTACT where LASTNAME_UC is NULL
If you get hits.. then
Update sysdba.CONTACT Set LASTNAME_UC = UPPER(LASTNAME) where LASTNAME_UC is NULL
also against remotes...
********************************************************************
select maintable, searchfield, count(*) as Count from sysdba.lookup group by
maintable, searchfield having count(*) > 1
Add additional fields such as the idfield or namefield if needed both to the
select and group by clauses.
****************************************************************************************************************
UPDATE sysdba.RESYNCTABLEDEFS Set DISPLAYNAME = TABLENAME WHERE DISPLAYNAME IS NULL
DELETE FROM sysdba.JOINDATA WHERE FROMTABLE = 'DEFECTLINKACTSTATUS' AND FROMFIELD = 'RESULTINGSTATUS'
select JOINID, FROMTABLE, FROMFIELD, TOTABLE, TOFIELD FROM sysdba.JOINDATA j1
where FROMFIELD not in (select FIELDNAME from sysdba.sectabledefs)
GO
select JOINID, FROMTABLE, FROMFIELD, TOTABLE, TOFIELD FROM sysdba.JOINDATA j1
where TOFIELD not in (select FIELDNAME from sysdba.sectabledefs)
GO
select distinct TOTABLE FROM sysdba.JOINDATA j1
where TOTABLE not in (select tablename from sysdba.sectabledefs)
GO
select distinct FROMTABLE FROM sysdba.JOINDATA j1
where FROMTABLE not in (select tablename from sysdba.sectabledefs) AND FROMTABLE <>'*'
GO
select distinct tablename from sysdba.resynctabledefs
where tablename not in (select maintable from sysdba.lookup)
GO
select distinct tablename from sysdba.resynctabledefs
where tablename not in (select tablename from sysdba.sectabledefs)
GO
select distinct tablename from sysdba.sectabledefs
where tablename not in (select tablename from sysdba.resynctabledefs)
GO
&**********************&&&&&&&&&&&&&&&&&&&&&&&&&&&&&***********************************************************************************************
use SLX_TM_rjsamp
Exec sp_msforeachtable @command1="print '?' dbcc dbreindex('?','',0,sorted_data_reorg)"
DELETE FROM JOINDATA WHERE JOINID LIKE 'JFULFILL%'
select j.* from sysdba.joindata j
left outer join sysobjects so1 on j.fromtable = so1.name
left outer join sysobjects so2 on j.totable = so2.name
where (so1.name is null or so2.name is null) and j.fromtable <>'*'
GO
DELETE from sysdba.joindata
WHERE fromtable = 'USER2'
OR TOTABLE ='USER2'
DELETE from sysdba.joindata
WHERE fromtable = 'DEFECTACTIVITY' AND fromfield = 'DEFECTACTIVITYID'
and TOTABLE = 'DEFECTACTIVITY' and TOfield = 'DEFECTACTIVITYID'
@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
DELETE from sysdba.joindata WHERE JOINID = 'jLS61A00006Q'
DELETE from sysdba.joindata WHERE JOINID = 'JSYST0000127'
DELETE from sysdba.joindata WHERE JOINID = 'jQF8AA000014'
SELECT j1.fromtable fromtable1, j1.fromfield fromfield1, j1.totable totable1, j1.tofield tofield1
FROM sysdba.JOINDATA j1, sysdba.joindata j2
where j1.fromtable = j2.totable
AND j1.totable = j2.fromtable
AND j1.fromfield = j2.tofield
AND j1.tofield = j2.fromfield
|
|
|
|