Importing the Data

MSSQL defaults to Ansi-Padding Off, which means if you have fields in your database that end with spaces those spaces will be removed when imported into MSSQL.  If you wish to keep those spaces, be sure to create your MSSQL database with Ansi-Padding On.

To facilitate the import, List manager disables all constraints before the import and enables them after the import.

1. Install ListManager on a new server pointing to a new database.
2. Copy the export file to that machine
3. Import the file

• Under unix/linux - In the <listmanager>/bin directory, execute “./lm importfromfile <myfile>”  where <myfile> is the export file generated above

• Under Windows - In C:\Program Files\List Manager (or wherever you install ListManager), execute “lm importfromfile <myfile>”  where <myfile> is the export file generated above

After the Import

**IMPORTANT** 

Check for any errors or warnings that may have occurred during IMPORT by reviewing the most recent LM log (ex: log#.txt)

INDEX ERRORS

Any existing indexes that require unique values for the columns in that index will not be created if there are rows with duplicate values for those columns.  Check the Import log for these warnings and fix the data in your database

EXAMPLE QUERY FOR FINDING DUPLICATES

CREATE TABLE TMP_MYDATA (

somefld1 varchar(60),

somefld2 varchar(250),

somefld3 varchar(100))

INSERT INTO TMP_MYDATA (somefld1, somefld2, somefld3)

SELECT List_, Domain_, UserNameLC_

FROM members_

GROUP BY [List_], [UserNameLC_], [Domain_]

HAVING COUNT(*)>1

GO

Once you have fixed any problems that have prevented the Indexes from being created, you’ll need to recreate the Index manually.  The SQL will be in the error message in the log, or you can call Tech Support for help.

CONSTRAINT ERRORS

At the end of the Import process, all constraints will be re-enabled.  If the database server has a problem re-enabling the constraint due to data inconsistency an error will show up in the Import log and that constraint will not be enabled.  An example of an error you may see is:

code: 20001

 msgtext: ORA-20001: table wordmessage_ constraint LISTID_470934578 ORA -2298

table wordmessage_ constraint MESSAGEID_477095552 ORA -2298

ORA-06512: at line 20

If you run the appropriate script (based on whichever database you have imported into) - /sql/oracle/oracle_enable_constraints.sql or /sql/msslq/mssql_enable_constraints.sql – you can see the errors in a clearer format such as

 

TABLE_NAME	CONSTRAINT_NAME	ERR_MSG
DOCS_	SYS_C002209375	ORA-02293: cannot validate
(LM93GJY_0903.SYS_C002209375) - check constraint
WORDMESSAGE_	LISTID_470934578	ORA-02298: cannot validate
(LM93GJY_0903.LISTID_470934578) - parent keys not found
WORDMESSAGE_	MESSAGEID_477095552	ORA-02298: cannot validate
(LM93GJY_0903.MESSAGEID_477095552) - parent keys not found

 And for further information you can use the following:

For MSSQL – dbcc checkconstraints

For Oracle - SELECT CONSTRAINT_NAME ,   TABLE_NAME,  SEARCH_CONDITION   FROM user_constraints  WHERE status = 'DISABLED'

RE-ENABLE CONSTRAINTS AFTER FIXING ERRORS

Run the appropriate script (based on whichever database you have imported into) - /sql/oracle/oracle_enable_constraints.sql

or

/sql/msslq/mssql_enable_constraints.sql 

to re-enable all constraints.  This should run cleanly once all the database issues are resolved.

Next: Moving ListManager to Another Machine