Trying to migrate to SQL Server 2005

[Marc Scheuner]

Folks,

I'm tasked with migrating data from FM Server 8 to SQL Server 2005. I was trying to use the ODBC driver for FM Server, and the SQL Server 2005 Data Import functionality.

I keep having odd and inexplicable OLE and COM errors when trying to move the data (less than 100'000 rows - really not a huge amount).

Any ideas? Is there another way to do this efficiently (on about 25 tables or so)? What can I check or tweak to make the import work?

I have the same problems when trying to migrate the FM data to MS Access 2003, too.

Any help would be highly appreciated !

[fmsavey]

There are, I'm am sure, a couple of ways to do this. One way is to push the information from FM to SQL. To do this the tables would have to be created first in SQL. The basic sytax fo the CREATE TABLE statement is ...

CREATE TABLE table_name

(column_name_1 data_type [column attributes]

After the tables are created you would then create a script in FM to insert the FM data into the SQL tables using a loop and the "Execute SQL" script step. The basic syntax of an insert statement is ...

INSERT [iNTO] table_name [(column list)]

VALUES (data_1, data_2 ...)

The insert statement would be "built" using calcuated text. The values in the insert statement would be the fields from FM. Doing it this way only requires a datasource definition for the SQL table.

Good luck.

Edited February 26, 2007 by Guest