|
|
You're in the
sql server dts
group:Very Big Table Problem
sql server dts:
I am trying to migrate a database from mysql to mssql2k. I use myODBC to connect to mysql server to pull the database from DTS and insert in to sql server. But in mysql server, there is one huge table. Size of it is 1.3 GB and more than 12 million rows. While I try with DTS, it transfers all tables in the database except this huge table. First I thought it would be the resources of the computer not enough (512MB physical ram and P3-866 MHZ cpu), so I have enlarged the swap size to 4GB. But each time I run DTS over this table, it dies after wasting around total 2.5 GB of memory. (Since it is less then 4GB, I guess it would not be a memory problem) To connect to Mysql server (by the way, mysql and mssql is on same physical server), I use myODBC connectors. And DTS connecting it via an ODBC driver I create to connect to Mysql server thru myODBC. I wonder if, myODBC is not meant to handle big tables (such as bigger than 1 GB tables in this case) ? What would be an alternate solution to this problem ? Best Regards. Murtix Van Basten
In article <3feb5a8d$1_6@athenanews.com>, nospam@nospam.org says...
[quoted text, click to view] > HI all, > > I am trying to migrate a database from mysql to mssql2k. I use myODBC to > connect to mysql server to pull the database from DTS and insert in to sql > server. But in mysql server, there is one huge table. Size of it is 1.3 GB > and more than 12 million rows. > > While I try with DTS, it transfers all tables in the database except this > huge table. First I thought it would be the resources of the computer not > enough (512MB physical ram and P3-866 MHZ cpu), so I have enlarged the swap > size to 4GB. But each time I run DTS over this table, it dies after wasting > around total 2.5 GB of memory. (Since it is less then 4GB, I guess it would > not be a memory problem) > > To connect to Mysql server (by the way, mysql and mssql is on same > physical server), I use myODBC connectors. And DTS connecting it via an ODBC > driver I create to connect to Mysql server thru myODBC. I wonder if, myODBC > is not meant to handle big tables (such as bigger than 1 GB tables in this > case) ? > > What would be an alternate solution to this problem ? Export the data and BCP it into the 2000 DB. -- -- spamfree999@rrohio.com
bcp
[quoted text, click to view] "Murtix Van Basten" <nospam@nospam.org> wrote in message news:3feb5a8d$1_6@athenanews.com... ....[trim]... [quoted text, click to view] > What would be an alternate solution to this problem ? > > Best Regards. > > Murtix Van Basten > >
I regularly transfer tables of > 13 million rows from Informix to SQL Server
and never have memory problems (7 or 2000). What SP level of SQL Server 2000 are you using ? Latest ODBC ? After making sure that the two things above are the latest then You can try using smaller batches (set on the last tab of the datapump task) If you want to stage the data then you can BCP out (to text file) and either BCP in or BULK INSERT in. -- -- Allan Mitchell (Microsoft SQL Server MVP) MCSE,MCDBA www.SQLDTS.com I support PASS - the definitive, global community for SQL Server professionals - http://www.sqlpass.org [quoted text, click to view] "Murtix Van Basten" <nospam@nospam.org> wrote in message news:3feb5a8d$1_6@athenanews.com... > HI all, > > I am trying to migrate a database from mysql to mssql2k. I use myODBC to > connect to mysql server to pull the database from DTS and insert in to sql > server. But in mysql server, there is one huge table. Size of it is 1.3 GB > and more than 12 million rows. > > While I try with DTS, it transfers all tables in the database except this > huge table. First I thought it would be the resources of the computer not > enough (512MB physical ram and P3-866 MHZ cpu), so I have enlarged the swap > size to 4GB. But each time I run DTS over this table, it dies after wasting > around total 2.5 GB of memory. (Since it is less then 4GB, I guess it would > not be a memory problem) > > To connect to Mysql server (by the way, mysql and mssql is on same > physical server), I use myODBC connectors. And DTS connecting it via an ODBC > driver I create to connect to Mysql server thru myODBC. I wonder if, myODBC > is not meant to handle big tables (such as bigger than 1 GB tables in this > case) ? > > What would be an alternate solution to this problem ? > > Best Regards. > > Murtix Van Basten > >
In article <OIcOAN4yDHA.1740@TK2MSFTNGP12.phx.gbl>, allan@no-
spam.sqldts.com says... [quoted text, click to view] > I regularly transfer tables of > 13 million rows from Informix to SQL Server > and never have memory problems (7 or 2000). While he's using MySQL, I was doing a Sybase 5.0 to SQL 2000 conversion - one the same machine. I found that DTS would only work if I didn't select more than 8 tables at a time. -- -- spamfree999@rrohio.com
Hi,
thank you very much for all your recommendations. I have already started to use OSQL commands before I saw your replies. It is still going. If it crashes I will use BCP method. But I guess in BCP method, I won't be able to use SQL structured file (I mean the file I have created by using MYSQLDUMP, it has all the table structures in it too). I guess I will have to dump only the data of the specific table from MYSQL and then use BCP for MSSQL to insert all of the "tab and newline structured" file to an existing database's existing table (the same structure of course which requires manual table creation). This is what I understand about how to use BCP. I hope I am not wrong. Regards. Murtix Van Basten. [quoted text, click to view] "Murtix Van Basten" <nospam@nospam.org> wrote in message news:3feb5a8d$1_6@athenanews.com... > HI all, > > I am trying to migrate a database from mysql to mssql2k. I use myODBC to > connect to mysql server to pull the database from DTS and insert in to sql > server. But in mysql server, there is one huge table. Size of it is 1.3 GB > and more than 12 million rows. > > While I try with DTS, it transfers all tables in the database except this > huge table. First I thought it would be the resources of the computer not > enough (512MB physical ram and P3-866 MHZ cpu), so I have enlarged the swap > size to 4GB. But each time I run DTS over this table, it dies after wasting > around total 2.5 GB of memory. (Since it is less then 4GB, I guess it would > not be a memory problem) > > To connect to Mysql server (by the way, mysql and mssql is on same > physical server), I use myODBC connectors. And DTS connecting it via an ODBC > driver I create to connect to Mysql server thru myODBC. I wonder if, myODBC > is not meant to handle big tables (such as bigger than 1 GB tables in this > case) ? > > What would be an alternate solution to this problem ? > > Best Regards. > > Murtix Van Basten > >
You Must use DTS, dont use BCP.....
Hi,
You must use BCP in following manner to transfer data from one database (A) to another database (B). 1. Use BCP to extract all the data from database A, in txt files... 2. Generate SQL Scripts for database object i.e tables, SPs, UDFs etc. from Database A. 3. Create a database B 4. Execute the SQL Scripts generated in step 2, on database B 5. Now use BCP or Bulk Insert to insert all data in databse B. By following above steps, you will able to get data structure SQL tables. It i.e. database B will be same as database A. Make sure while inserting data in tables with Identity field, you must switch Identity Insert On. Just do this......., I am sure this will work for you........ Regards Hari Sharma [quoted text, click to view] "Murtix Van Basten" <nospam@nospam.org> wrote in message news:<3feccd3a$1_5@athenanews.com>...
> Hi, > > thank you very much for all your recommendations. I have already started > to use OSQL commands before I saw your replies. It is still going. If it > crashes I will use BCP method. > > But I guess in BCP method, I won't be able to use SQL structured file (I > mean the file I have created by using MYSQLDUMP, it has all the table > structures in it too). I guess I will have to dump only the data of the > specific table from MYSQL and then use BCP for MSSQL to insert all of the > "tab and newline structured" file to an existing database's existing table > (the same structure of course which requires manual table creation). This is > what I understand about how to use BCP. I hope I am not wrong. > > Regards. > > Murtix Van Basten. > > "Murtix Van Basten" <nospam@nospam.org> wrote in message > news:3feb5a8d$1_6@athenanews.com... > > HI all, > > > > I am trying to migrate a database from mysql to mssql2k. I use myODBC > to > > connect to mysql server to pull the database from DTS and insert in to sql > > server. But in mysql server, there is one huge table. Size of it is 1.3 GB > > and more than 12 million rows. > > > > While I try with DTS, it transfers all tables in the database except > this > > huge table. First I thought it would be the resources of the computer not > > enough (512MB physical ram and P3-866 MHZ cpu), so I have enlarged the > swap > > size to 4GB. But each time I run DTS over this table, it dies after > wasting > > around total 2.5 GB of memory. (Since it is less then 4GB, I guess it > would > > not be a memory problem) > > > > To connect to Mysql server (by the way, mysql and mssql is on same > > physical server), I use myODBC connectors. And DTS connecting it via an > ODBC > > driver I create to connect to Mysql server thru myODBC. I wonder if, > myODBC > > is not meant to handle big tables (such as bigger than 1 GB tables in this > > case) ? > > > > What would be an alternate solution to this problem ? > > > > Best Regards. > > > > Murtix Van Basten > >
In DTS there is a setting in the options which relates to something or
other about batch commit. Set this value (try starting with 1000 rows), otherwise it will create
In DTS there is a setting in the options which relates to something or
other about batch commit. Set this value (try starting with 1000 rows), otherwise it will create
Don't see what you're looking for? Try a search.
|
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
| home · blog · groups | Questions? Comments? Contact the dn |