11/26/2024 8:26:09 AM
|
| |
| slxdeveloper.com Community Forums |
|
| |
|
|
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!
Forum to discuss writing script in Architect plugins for SalesLogix & general SalesLogix customization topics (for Windows client only). View the code of conduct for posting guidelines.
|
|
|
|
Migrating to a new copy of SLX Database
Posted: 25 Jul 06 11:16 AM
|
fiogf49gjkf0d Hello all,
Not sure if this is the right forum, but here goes. So our SLX database has been worked on by at least 5-6 different developers over the past 6 years or so. Some have decided to use Enterprise Manager instead of the Work Group Administrator causing ansi-padding problems as well as making it impossible to work with those tables/field in Workgroup Administrator. Also, many unused fields currently exist. We are in the process of upgrading from 5.2.4 up to 6.2. We decided to take this opportunity to establish which fields we wanted and which we didn't as well as to completely re-make our database. Our idea was to take a blank 6.2 database, add all of our custom fields and tables through WGA, then port the data over from our exisiting database (after running the 6.2 upgrade on it). I'm wondering if anyone has tried this and if there are any problems with it. I'm thinking there may be a problem copying over the SLX IDs that are generated. Just trying to get a general idea if what we're doing makes any sense. |
|
|
|
Re: Migrating to a new copy of SLX Database
Posted: 26 Jul 06 1:16 AM
|
fiogf49gjkf0d Here's the route I took:
Development db
1) SLXBlank + New Licenses.
2) Configured as close as possible to production in options and users.
3) No bundles outside of service packs.
4) Create projects based on functionality.
5) Create new custom tables (can be done in Architect now).
6) If reusing Legacy plugins, import bare minimum from production (omit tables definately).
7) Save as a new version and make sure project points to the new version only.
8) Modify VBScript Account Details instead of using Legacy. Repeat for versions based on SLX customizations. It's usually a lot of work trying to make the Legacy versions function as well as the newer ones.
9) If redoing Legacy in VBScript, create from scratch without the Legacy plugin in the database.
10) Bundle project and tables together.
Production db
1) Upgrade database, apply SLX bundles + service packs. Follow the guides.
2) Add new customer id, licenses, etc. Fix broken ones as close as possible.
3) Analyze existing tables by looking for distinct values. Take the opportunity to make development tables the bare minimum.
4) Install custom bundles from development.
5) DTS copy each custom table only.
6) Uninstall old custom bundles, newer bundles should be versioned including a different company name than Legacy.
7) Get rid of SLX Legacy plugins no longer used by comparing plugin table to development. Verious degrees of anality can be applied here. |
|
|
|
Re: Migrating to a new copy of SLX Database
Posted: 27 Jul 06 8:42 AM
|
fiogf49gjkf0d Here's a SQL script you can run from Query Analyzer (not sure how bad Execute SQL in the WGA would choke on this  ) that will give you a census of all the columns of all the user tables in a given database, which will make life much easier in Production DB Step 3 above. It lists the table name, the column name, and how many rows in the table have non-empty/non-NULL values for the column. Be warned, this query can take a long time to run, so I would recommend running either off-hours or on a backup of the database restored to a non-production server.
--------------------
DECLARE @SQL NVARCHAR(4000),
@TABLE SYSNAME,
@COLUMN SYSNAME,
@TYPE VARCHAR(30),
@LOOP CURSOR
CREATE TABLE ##COLUMN_CENSUS (
[TABLENAME] SYSNAME,
[COLUMNNAME] SYSNAME,
[DATATYPE] VARCHAR(30),
[NUMROWS] INT
)
INSERT INTO ##COLUMN_CENSUS
SELECT A.TABLE_NAME TABLENAME,
B.COLUMN_NAME COLUMNNAME,
B.DATA_TYPE DATATYPE,
0 NUMROWS
FROM INFORMATION_SCHEMA.TABLES A LEFT OUTER JOIN INFORMATION_SCHEMA.COLUMNS B
ON (A.TABLE_NAME = B.TABLE_NAME)
WHERE A.TABLE_SCHEMA = 'sysdba' AND
A.TABLE_TYPE = 'BASE TABLE'
SET @LOOP = CURSOR FOR (
SELECT TABLENAME,
COLUMNNAME,
DATATYPE
FROM ##COLUMN_CENSUS
)
OPEN @LOOP
FETCH NEXT
FROM @LOOP
INTO @TABLE, @COLUMN, @TYPE
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SQL = 'DECLARE @COUNT INT SELECT @COUNT = CAST(COUNT(*) AS INT) FROM [SYSDBA].[' + @TABLE + '] '
SET @SQL = @SQL + CASE
WHEN @TYPE LIKE '%TEXT%' OR @TYPE LIKE '%IMAGE%' THEN
'WHERE DATALENGTH([' + @COLUMN + ']) > 0 '
WHEN @TYPE LIKE '%NUMERIC%' THEN
'WHERE [' + @COLUMN + '] IS NOT NULL '
ELSE
'WHERE [' + @COLUMN + '] IS NOT NULL AND [' + @COLUMN + '] <> '''' '
END
SET @SQL = @SQL + 'UPDATE ##COLUMN_CENSUS SET NUMROWS = @COUNT WHERE TABLENAME = ''' + @TABLE + ''' AND COLUMNNAME = ''' + @COLUMN + ''''
EXEC SP_EXECUTESQL @SQL
FETCH NEXT
FROM @LOOP
INTO @TABLE, @COLUMN, @TYPE
END
CLOSE @LOOP
DEALLOCATE @LOOP
SELECT *
FROM ##COLUMN_CENSUS
ORDER BY TABLENAME, COLUMNNAME
DROP TABLE ##COLUMN_CENSUS
|
|
|
|
Re: Migrating to a new copy of SLX Database
Posted: 29 Jul 06 10:22 AM
|
fiogf49gjkf0d I've done this a couple of times.
1. Create the custom tables and fields in your v6.2 database.
2. Bundle all the latest versions of your custom plugins. You should have all this documented, but otherwise just filter by company name.
3. Apply the bundle to v6.2.
4. Do functional tests to insure your plugins are working correctly in v6.2.
5. Make any necessary corrections to your plugins.
6. Create your users and configure the sytem for v6.2
6. Import all your data from the v5 installation. You can do this with the import wizard (not recommended, by me anyway), using a 3rd party tool like scribe, or just write your own program (with the oledb provider is easier than ever). |
|
|
|
You can
subscribe to receive a daily forum digest in your
user profile. View the site code
of conduct for posting guidelines.
 Forum RSS Feed - Subscribe to the forum RSS feed to keep on top of the latest forum activity!
|
|
|
|
|
|
|
|