There are commercial packages that do the conversion quickly and easily. The best one is obscenely expensive (the price isn't on the website, you have to write and request a quote, and a sales rep get back. I think it was around $3000. I don't remember the name of the software, because it was so expensive. But the demo worked perfectly).
The solution I used, which was a bit more work, was to use an Access-to-MySQL program, which was about $39 ($75, I think for both directions). I export the SQL-Server to Access, then use Access-to-MySQL. We need to do this monthly for a newsletter (ODBC over the network drags the newsletter delivery out from 24 hours to 56 hours!), and it works fine. Unfortunately, it doesn't preserve indices, so they need to be recreated by hand. The company with the low-cost converters is Intelligent Converters. Of course, you're going to lose some of the constraints just using MySQL, but the converter may make your life a bit simpler. Or, if you have the $3k, do a google search for SQL MySQL converter Tac ----- Original Message ----- From: "Christopher Bort" <[EMAIL PROTECTED]> To: "MySQL List" <[EMAIL PROTECTED]> Sent: Tuesday, July 02, 2002 1:59 PM Subject: Recreating an MS-SQL database in MySQL: redux > No response from the first post, so here goes for one more try... > > My employer recently acquired an existing web site that uses a MicroSoft > SQL database and I've been directed to get it running on an in-house > server. As we don't have any Windows servers, I need to recreate the > database in MySQL. The site's developer has supplied me with the schema of > the MS-SQL database and I'm hoping to be able to turn it into a file that I > can run in batch mode through the mysql client to create the database > structure. I'm relatively new to SQL in general and MySQL specifically, so > I'm a little over my head at this point. I will, I'm sure, be learning all > I need to know over time, but I need to get this one project going now and > I would appreciate some guidance to at least tell me if I'm headed in the > right direction. > > Anyway, the MS-SQL schema file consists of CREATE TABLE commands for each > table, followed by a series of ALTER TABLE commands. I've been able to get > the CREATE TABLE commands to run by changing, e.g.: > > CREATE TABLE [dbo].[clients] ( > [login] [varchar] (50) NOT NULL , > [password] [varchar] (50) NULL > ) ON [PRIMARY] > GO > > to: > > CREATE TABLE clients ( > login VARCHAR(50) NOT NULL , > password VARCHAR(50) NULL > ); > > So far, so good. There are a couple of column types that I've had to guess > at, but I think I've found reasonable equivalents ([money] becomes > DECIMAL(6,2) and [phone] becomes VARCHAR(20) ). > > I'm getting stuck on the ALTER TABLE commands, though. I'm afraid I don't > know quite enough MySQL (or SQL in general) to confidently figure out which > MS-SQL bits are server-specific and can be ignored, and which need to be > changed into equivalent MySQL commands. If I change: > > ALTER TABLE [dbo].[dining_cat] WITH NOCHECK ADD > CONSTRAINT [PK__dining_cat__07C12930] PRIMARY KEY CLUSTERED > ( > [cat_id] > ) WITH FILLFACTOR = 90 ON [PRIMARY] > GO > > to: > > ALTER TABLE dining_cat ADD > CONSTRAINT PK__dining_cat__07C12930 PRIMARY KEY CLUSTERED > ( > cat_id > ); > > it works, although I'm not sure what, if anything, is being lost by getting > rid of 'WITH NOCHECK' and 'WITH FILLFACTOR = 90'. Then there are commands > that look like: > > ALTER TABLE [dbo].[dining_loc] WITH NOCHECK ADD > CONSTRAINT [DF_dining_loc_loc_venue] DEFAULT (0) FOR [loc_venue], > CONSTRAINT [PK_dining_loc] PRIMARY KEY NONCLUSTERED > ( > [loc_id] > ) WITH FILLFACTOR = 90 ON [PRIMARY] > GO > > MySQL gives an error 1064 at 'DEFAULT (0) FOR loc_venue' and I'm not sure > what to do with it. If I knew what the intended effect is, I'm sure I could > figure out the equivalent MySQL syntax. At this point, though, it's still a > bit opaque to me. Any gentle nudge in the right direction would be greatly > appreciated. > > TIA! > > -- > Christopher Bort | [EMAIL PROTECTED] > Webmaster, Global Homes | [EMAIL PROTECTED] > <http://www.globalhomes.com/> > > --------------------------------------------------------------------- > Before posting, please check: > http://www.mysql.com/manual.php (the manual) > http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
