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
6  4567 
In article <3f**********@a thenanews.com>, no****@nospam.o rg says... 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.
--
-- sp*********@rro hio.com
(Remove 999 to reply to me)
bcp
"Murtix Van Basten" <no****@nospam. org> wrote in message
news:3f******** **@athenanews.c om...
....[trim]... 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
"Murtix Van Basten" <no****@nospam. org> wrote in message
news:3f******** **@athenanews.c om... 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 <OI************ **@TK2MSFTNGP12 .phx.gbl>, allan@no-
spam.sqldts.com says... 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.
--
-- sp*********@rro hio.com
(Remove 999 to reply to me)
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" <no****@nospam. org> wrote in message
news:3f******** **@athenanews.c om... 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
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
"Murtix Van Basten" <no****@nospam. org> wrote in message news:<3f******* ***@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" <no****@nospam. org> wrote in message
news:3f******** **@athenanews.c om... 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
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics | |
by: Google Mike |
last post by:
I have RH9 and am using the PHP and MySQL that came with it. I was
doing fine with all manner of my web pages for this app until I
started having this very strange problem.
It's a work order mgmt system. I have 3 tables for the orders:
TicketsOpen, TicketsVoided, and TicketsResolved. When one wants to
void a ticket, they click it, choose Void, and it is copied to the
TicketsVoided table, then removed from the TicketsOpen. And I can...
|
by: robin |
last post by:
I need to do a search through about 50 million records, each of which
are less than 100 bytes wide. A database is actually too slow for
this, so I thought of optimising the data and putting it all in
memory.
There is a single key field, so a dictionary is an obvious choice for
a structure, since Python optimises these nicely.
But is there a better choice? Is it worth building some sort of tree?
|
by: Robert Misiorowski |
last post by:
Hello,
I have a very perplexing (at least to me) problem that hopefully
someone can help me with. I'm making a site with a 3 column layout. In the
middle column (my fluid column) I am trying to put a table of 99% width.
When I do this the table actually extends to the right over my right column
and off the screen. After much debugging and trying of different things, I
change this line in my code:
<!DOCTYPE HTML PUBLIC "-//W3C//DTD...
|
by: DJJ |
last post by:
I am using the MySQL ODBC 3.51 driver to link three relatively small MySQL
tables to a Microsoft Access 2003 database. I am finding that the data from
the MySQL tables takes a hell of a long time to load making any kind linkage
with my Access data virtually useless.
I have the MySQL driver setup in as a USER DSN. The MySQL data is sitting
out on a server and the Access database is running locally. The network
connection is very...
|
by: mamo74 |
last post by:
Hello.
I am administering a SQL Server (Enterprise Edition on Windows 2003)
from some month and can't understand what is going on in the latest
week (when the db grow a lot).
The DB is around 250G, and has one table with 1 billion rows. It is
performing in a decent way, but can't understand why a particolar table
has strong performance problem.
| | |
by: news |
last post by:
I seriously doubt this is possible...but you never know, so here goes.
Due to bad pre-planning I have a Web page that is VERY table heavy with
a very complicated and delicate setup. Any changes to the table
structure, as in size changes or additions of new cells, throws the
whole thing out of whack.
(Let me pause here to say I understand quite well that tables in
general are not a good idea anymore. Didn't know that until after I...
|
by: roiavidan |
last post by:
Hi,
I'm having a bit of a problem with a small application I wrote in C#,
which uses an Access database (mdb file) for storing financial data.
After looking for a similiar topic and failing to find one, I'm posting
the question in hope some one of you guys out there will have the
answer for me...!
I'll start with what I have, then I'll continue to the problem itself.
|
by: Cornelius Buschka |
last post by:
Hi,
we saw the following problem:
We deleted all rows from a table B referencing table A (~500000 records). No
problem, but the following try to delete all records from table A (~180000) lead
to a "never ending" statement. We found out, that vacuuming table B after delete
did the trick.
It seems to us the database has to do scan thru deleted records on B while
|
by: pelletier.thomas |
last post by:
Hello everybody. I'm trying to code a very little OS.
But I have a problem with the keyboard input:
It show thechar + "~" :s
Can you help me ?
The source is there : http://thonix.tuxfamily.org/pub/0.0.1/src/
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| | |
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| | |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |
