You could also use a SELECT statement to get the structure for each of
the tables... I just threw this query together but it works for me;
you don't need direct access or anything:
SELECT SO.Name as TableName, SC.Name as ColumnName,
ST.Name + CASE WHEN ST.Status = 2 THEN '(' + CONVERT(VarChar(6),
SC.Length) + ')' ELSE
CASE WHEN SC.AutoVal IS NOT NULL THEN ' IDENTITY' END END as ColumnType,
CASE WHEN Left(SysIndexLookup.IndexType, 2) = 'PK' THEN 'PRIMARY KEY'
ELSE '' END as KeyText
FROM SYSOBJECTS SO INNER JOIN SysColumns SC ON
SO.ID = SC.ID INNER JOIN SysTypes ST ON
SC.xtype = ST.xtype LEFT OUTER JOIN
(SELECT SI.id, SI.Name as IndexType, SIK.colid
FROM SysIndexes SI INNER JOIN SysIndexKeys SIK ON
SI.ID = SIK.ID AND
SI.IndID = SIK.IndID) SysIndexLookup ON
SO.ID = SysIndexLookup.ID AND SC.ColID = SysIndexLookup.ColID
WHERE so.xtype = 'U'
That will at least give you a query that contains the actual structure
of the table including primary keys and indexes. From there you can
use either CF or SQL to generate your actual CREATE TABLE statements -
then it's up to you to decide whether you want to use CF and two DSNs
to copy the data across.
HTH!
Hatton
On 7/16/05, Rob <[EMAIL PROTECTED]> wrote:
> > i have to move a ms sql db to a mysql db, whats the best way?
> >
> > Remember I am on a mac and our good friends at m$ don't make cross
> > platform products (cuz they suck) but I guess I can fire up a pc if need be.
>
> Does the server running mssql server have terminal service running? If
> so you can just use this
> http://www.microsoft.com/mac/downloads.aspx?pid=download&location=/mac/download/misc/rdc_update_103.xml&secid=80&ssid=10&flgnosysreq=True
>
> Remote desktop in, use Enterprise manager. Then right click on the
> database (or selected tables I forget which, and do export schema. In
> there (somewhere) there is as sql scripts - there similar thing to
> generate sql inserts for the data too
>
> > Anyone have any good programs to transfer or extract an sql file? I tried
> > a few but they wont let me transfer to localhost grrrrrrrrrrrr
>
> Sucks that it is mssql server, if it were access you could try this
> http://mdbtools.sourceforge.net/ (though I don't know if it'll run on
> the mac).
>
> Good luck
>
> --
> ~Blog~
> http://www.robrohan.com
> ~The cfml plug-in for eclipse~
> http://cfeclipse.tigris.org
> ~open source xslt IDE~
> http://treebeard.sourceforge.net
>
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Logware (www.logware.us): a new and convenient web-based time tracking
application. Start tracking and documenting hours spent on a project or with a
client with Logware today. Try it for free with a 15 day trial account.
http://www.houseoffusion.com/banners/view.cfm?bannerid=67
Message: http://www.houseoffusion.com/lists.cfm/link=i:4:212069
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
