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!
|
|
How to Copy SLX DB fields
Posted: 06 Apr 07 1:53 PM
|
fiogf49gjkf0d Is there an easy way to copy fields from one SLX DB table to another?
We are on SLX 6.2.3. At the push of a button, the user would like to archive or restore individual records for several custom tables. I plan to create archive tables to hold the data, but don’t look forward to typing in all the field names into Database Manager in Architect. I can’t use SQL2005 since remotes are involved. I’m still pretty new to SLX development so I’m hoping there is a trick that I just don’t know yet. Any constructive suggestions would be appreciated.
|
|
|
|
Re: How to Copy SLX DB fields
Posted: 07 Apr 07 7:30 AM
|
fiogf49gjkf0d A "simple" code loop will do it.
You need to write a vbscript that:
A - Opens the source table and selects the recordset to copy
B - Opens the target table for AddNew records
C - Loops thru the source recordset and writes (addnew) records in the target.
Take a look at the following for how recordsets are handled in vbscripting via ADO:
http://www.slxdeveloper.com/page.aspx?action=viewarticle&articleid=76
Also, you should get a copy of Sussman's ADO Programmer's ref (look at Amazon).. and get a copy of Steve Redmond's Devlogix book (both advertised on this site)
Probably the best advice is to sit down w/your BP (or get in touch with a Certified/experienced SalesLogix consultant) to get some SalesLogix 101 training.
--
rjl |
|
|
|
Re: How to Copy SLX DB fields
Posted: 09 Apr 07 7:25 AM
|
RJL,
My problem is not with coping the data within a table, but with actually copying the field definitions from one table to another new table. Copying the data is pretty simple once the target table exists. I'm looking for an easier way to set up the target table that replicates dozens of fields from the source table.
Still, thanks for the advise.
John |
|
|
|
Re: How to Copy SLX DB fields
Posted: 09 Apr 07 9:27 AM
|
Find the table references in ReSyncTebleDefs and SecTableDefs.
Modify the table Name definitions in a backup copy and then Bundle them into the SLX DB.
Note: These will endup as freestanding tables since no join data if being ported.
The table will be created in the production db using a bundle, so sync will be supported if desired.
You might want to turn that off, if its for archival purposes.
Also note: Restoring archived data with remotes requires a more than a little knowledge of the Sync process. I'd recommend training and a BP assist you to get it configured correctly the first time. |
|
|
|
Re: How to Copy SLX DB fields
Posted: 10 Apr 07 9:14 AM
|
WARNING: this is a destructive process so do NOT do this directly against a production db!
A - Setup an off-line copy of the db
B - Logged into Enterprise Manager as sysdba do:
SELECT * Into MYTABLE_ARCHIVE
FROM MYTABLE
WHERE 1 = 2
C - Bundle the MYTABLE_ARCHIVE
D - Using Enterprise Manager DRP MYTABLE_ARCHIVE
E - Test run the bundle into the test db.. it should show up just fine in the Slx DBManager
F - Run the bundle into the target production db..
all done!
The above process has proven to work in 100% of the cases where I have used it... and I've been using it (with SalesLogix) for many years.
NOTE: IF you need global joins w/the ARCHIVE table(s) then use teh Join Manager to do it between steps B & C. Also be sure to include the join(s) when creating the bundle. Remember to also DELETE the join between steps C & D
DISCLAIMER.. don't come back to me or the Slx Developer site if you trash your system...
--
rjl |
|
|
|
Re: How to Copy SLX DB fields
Posted: 27 Apr 07 8:48 AM
|
fiogf49gjkf0d There are a couple of good suggestions above, but I am new to Sale Logix and am primarily a developer not a DBA. Consequently, I was not comfortable attempting these suggestions. Here is what I did instead:
1) I used SQL Enterprise Manager to generate an SQL Script to create the tables I needed.
2) If I already had a new version of the table without all the needed fields, I changed the CREATE to an ALTER.
3) I saved this script to a .txt file.
4) In SLX Administrator, I got into the Execute SQL pane. (This tool has a bad habit of trying to split a complex SQL statement into multiple statements. Therefore, I was forced to open a .txt file with simple SQL. I then edited that simple SQL and replaced it with my more complex Alter or Create statement.)
5) I executed that SQL on the Host Database.
6) I have tested enough to be sure that these changes are making it to my remote users.
Although my process may not be as fast as the other suggestions, it did save me from manually entering in hundreds of fields that are copies of fields in other tables.
Thanks again to all,
John
|
|
|
|
Re: How to Copy SLX DB fields
Posted: 28 Apr 07 7:18 AM
|
fiogf49gjkf0d Originally posted by John Steele
There are a couple of good suggestions above, but I am new to Sale Logix and am primarily a developer not a DBA. Consequently, I was not comfortable attempting these suggestions. Here is what I did instead:
1) I used SQL Enterprise Manager to generate an SQL Script to create the tables I needed.
2) If I already had a new version of the table without all the needed fields, I changed the CREATE to an ALTER.
3) I saved this script to a .txt file.
4) In SLX Administrator, I got into the Execute SQL pane. (This tool has a bad habit of trying to split a complex SQL statement into multiple statements. Therefore, I was forced to open a .txt file with simple SQL. I then edited that simple SQL and replaced it with my more complex Alter or Create statement.)
5) I executed that SQL on the Host Database.
6) I have tested enough to be sure that these changes are making it to my remote users.
Although my process may not be as fast as the other suggestions, it did save me from manually entering in hundreds of fields that are copies of fields in other tables.
Thanks again to all,
John
|
|
Interesting.. but your approach did not build some of the "SalesLogix Metatdata" correctly. You would not have known this since you did say you are new to SalesLogix. FYI... SalesLogix has a "bunch" of metadata that is not documentated anywhere.
Lane works for a BP , I own a Partnership... we've been down this road many many times... His approach & mine both take advantage of the fact that "bundling" smooths out the wrinkles and causes the table (and appropriate metadata) to be created 100% correctly every time on the target system.
--
rjl |
|
|
|