Developer Forum

I'm trying to move a database from MySQL to SQL 2000 Server, but I
succeed only partially. Especially the primary indexes are not
transferred.

I'm doing this because I have a database model developed with
fabForce.NET DBDesigner which is then transferred to MySQL (with a few
problems)

Since BDS 2006 Architect don't support reverse engineering in ECO III
from MySQL tables, but do so with SQL Server 2000, I need to transfer
the database layout to SLQ Server, but in vain. Only a few tables are
transferred, most gives error messages that is impossible to decipher
(an error occured near ')' without being able to open the SQL
statement that was formed)
And the tables that are transferred loose the primary indexes :-(

Is the problem that the importer don't support MySQL 4.1 or 5?

Or is there a problem in general supporting MySQL import at all?

Can you backup the database and then open it in a text editor. You should
see schema statements there. Try to run them in QA. It should intuitive how
to modify the schema for SQL 2000. Post here if you have problems.


--
Hilary Cotter
Looking for a SQL Server replication book?
http://www.yqcomputer.com/

Looking for a FAQ on Indexing Services/SQL FTS
http://www.yqcomputer.com/

On Tue, 18 Jul 2006 15:33:49 -0400, "Hilary Cotter"



Could it be the size of VARCHAR that is the culprit?

This SQL works:

DROP TABLE IF EXISTS `comments`;
CREATE TABLE `comments` (
`CommentsID` int(10) unsigned NOT NULL auto_increment,
`EkteskapsID` bigint(20) NOT NULL default '0',
`PersonID` bigint(20) NOT NULL default '0',
`ContributionsID` int(10) unsigned default NULL,
`Time` datetime default NULL,
`From_` bigint(20) default NULL,
`To_` bigint(20) default NULL,
`Text` text,
`Read_` tinyint(1) default NULL,
`From_Forening` int(10) unsigned default NULL,
`To_Forening` int(10) unsigned default NULL,
PRIMARY KEY (`CommentsID`,`EkteskapsID`,`PersonID`),
KEY `EkteskapsID` (`EkteskapsID`,`PersonID`),
CONSTRAINT `comments_ibfk_1` FOREIGN KEY (`EkteskapsID`, `PersonID`)
REFERENCES `persons` (`EkteskapsID`, `PersonID`) ON DELETE NO ACTION
ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


This don't:

DROP TABLE IF EXISTS `counties`;
CREATE TABLE `counties` (
`CountyID` bigint(20) NOT NULL auto_increment,
`CountryID` bigint(20) default NULL,
`Name` varchar(255) default '',
PRIMARY KEY (`CountyID`),
KEY `CountryID` (`CountryID`),
CONSTRAINT `counties_ibfk_1` FOREIGN KEY (`CountryID`) REFERENCES
`countries` (`CountryID`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


In case, what is max supported by SQL SERVER ?

8k on sql 2000 (depending on the width of the other columns) and infinite on
sql 2005 (well almost).

CREATE TABLE counties (
CountyID bigint NOT NULL identity primary key,
CountryID bigint default NULL,
Name varchar(255) default ''
)

Here is what table countries should look like. I need more info on the
primary key constraint counties_ibfk_1 that references country. Do you have
a table called ibfk?


--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.

This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.

Looking for a SQL Server replication book?
http://www.yqcomputer.com/

Looking for a FAQ on Indexing Services/SQL FTS
http://www.yqcomputer.com/