I'm trying to migrate a mysql database to sql server 2000 (then I'm
going to upgrade that to 2005). I downloaded myODBC and was able to
import most of the tables, however when I try to import the largest
table (about 18GB) it fails by running out of memory. The machine I'm
using has 4 gigs of memory in it, so I don't think it's the machine,
however SQL Server 2000 Standard can only take up to 2 gigs of memory
which I think is the problem. How can I work around this? I'm
thinking of trying to use mysqldump to dump pieces of the table at a
time and import it that way, but i think that will take an
unreasonably long time.
Thanks for any help!
What method are you using to load the data from the MySQL database to the MS
SQL database?
Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration /
Microsoft Office SharePoint Server 2007: Configuration)
MCITP (dbadmin, dbdev)
"dylan" wrote:
> I'm trying to migrate a mysql database to sql server 2000 (then I'm
> going to upgrade that to 2005). I downloaded myODBC and was able to
> import most of the tables, however when I try to import the largest
> table (about 18GB) it fails by running out of memory. The machine I'm
> using has 4 gigs of memory in it, so I don't think it's the machine,
> however SQL Server 2000 Standard can only take up to 2 gigs of memory
> which I think is the problem. How can I work around this? I'm
> thinking of trying to use mysqldump to dump pieces of the table at a
> time and import it that way, but i think that will take an
> unreasonably long time.
> Thanks for any help!
>
|||On May 30, 3:06 pm, mrdenny <mrde...@.discussions.microsoft.com> wrote:[vbcol=seagreen]
> What method are you using to load the data from the MySQL database to the MS
> SQL database?
> --
> Denny
> MCSA (2003) / MCDBA (SQL 2000)
> MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration /
> Microsoft Office SharePoint Server 2007: Configuration)
> MCITP (dbadmin, dbdev)
> "dylan" wrote:
Hi,
Thanks for the response.
I'm using myODBC to connect to the mysql server through an ODBC
connection and Data Transformation Services to import the data.
Dylan
|||You can set the transformation to batch the load, which might release the
memory stress.
I would probably export the data using a mySQL tool, then BCP the data into
the SQL Server.
Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration /
Microsoft Office SharePoint Server 2007: Configuration)
MCITP (dbadmin, dbdev)
"dylan" wrote:
> On May 30, 3:06 pm, mrdenny <mrde...@.discussions.microsoft.com> wrote:
> Hi,
> Thanks for the response.
> I'm using myODBC to connect to the mysql server through an ODBC
> connection and Data Transformation Services to import the data.
> Dylan
>
|||On May 30, 3:40 pm, mrdenny <mrde...@.discussions.microsoft.com> wrote:[vbcol=seagreen]
> You can set the transformation to batch the load, which might release the
> memory stress.
> I would probably export the data using a mySQL tool, then BCP the data into
> the SQL Server.
> --
> Denny
> MCSA (2003) / MCDBA (SQL 2000)
> MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration /
> Microsoft Office SharePoint Server 2007: Configuration)
> MCITP (dbadmin, dbdev)
> "dylan" wrote:
>
Ok, thanks. I'll look for the batching option, and if that doesn't
work I'll look at bcp. I've never used that but it looks easy enough.
|||Hello,
Use the below steps:-
1. In the MySQL Side extract the data from table to a comma seperated text
file
2. Copy the file to SQL Server machine
3. Create the table structure with out indexes and triggers
4. Load the table using BCP IN or BULK INSERT with batch commit options.
5. Create indexes and triggers
6. If you database is in FULL recovery mode, make sure you backup the
transaction log every 2 minutes or so while loading the data.
Thanks
Hari
"dylan" <dylan.roehrig@.gmail.com> wrote in message
news:1180554251.701728.181170@.k79g2000hse.googlegr oups.com...
> On May 30, 3:40 pm, mrdenny <mrde...@.discussions.microsoft.com> wrote:
> Ok, thanks. I'll look for the batching option, and if that doesn't
> work I'll look at bcp. I've never used that but it looks easy enough.
>
|||On May 30, 10:03 pm, "Hari Prasad" <hari_prasa...@.hotmail.com> wrote:[vbcol=seagreen]
> Hello,
> Use the below steps:-
> 1. In the MySQL Side extract the data from table to a comma seperated text
> file
> 2. Copy the file to SQL Server machine
> 3. Create the table structure with out indexes and triggers
> 4. Load the table using BCP IN or BULK INSERT with batch commit options.
> 5. Create indexes and triggers
> 6. If you database is in FULL recovery mode, make sure you backup the
> transaction log every 2 minutes or so while loading the data.
> Thanks
> Hari
> "dylan" <dylan.roeh...@.gmail.com> wrote in message
> news:1180554251.701728.181170@.k79g2000hse.googlegr oups.com...
>
>
>
Thanks Hari, I'm running into a problem dumping the data into a a
comma separated text file though. Is it possible to do that when the
data in the source table is a blob?
Showing posts with label migrate. Show all posts
Showing posts with label migrate. Show all posts
Wednesday, March 28, 2012
import large table from mysql
I'm trying to migrate a mysql database to sql server 2000 (then I'm
going to upgrade that to 2005). I downloaded myODBC and was able to
import most of the tables, however when I try to import the largest
table (about 18GB) it fails by running out of memory. The machine I'm
using has 4 gigs of memory in it, so I don't think it's the machine,
however SQL Server 2000 Standard can only take up to 2 gigs of memory
which I think is the problem. How can I work around this? I'm
thinking of trying to use mysqldump to dump pieces of the table at a
time and import it that way, but i think that will take an
unreasonably long time.
Thanks for any help!What method are you using to load the data from the MySQL database to the MS
SQL database?
--
Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration /
Microsoft Office SharePoint Server 2007: Configuration)
MCITP (dbadmin, dbdev)
"dylan" wrote:
> I'm trying to migrate a mysql database to sql server 2000 (then I'm
> going to upgrade that to 2005). I downloaded myODBC and was able to
> import most of the tables, however when I try to import the largest
> table (about 18GB) it fails by running out of memory. The machine I'm
> using has 4 gigs of memory in it, so I don't think it's the machine,
> however SQL Server 2000 Standard can only take up to 2 gigs of memory
> which I think is the problem. How can I work around this? I'm
> thinking of trying to use mysqldump to dump pieces of the table at a
> time and import it that way, but i think that will take an
> unreasonably long time.
> Thanks for any help!
>|||On May 30, 3:06 pm, mrdenny <mrde...@.discussions.microsoft.com> wrote:
> What method are you using to load the data from the MySQL database to the MS
> SQL database?
> --
> Denny
> MCSA (2003) / MCDBA (SQL 2000)
> MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration /
> Microsoft Office SharePoint Server 2007: Configuration)
> MCITP (dbadmin, dbdev)
> "dylan" wrote:
> > I'm trying to migrate a mysql database to sql server 2000 (then I'm
> > going to upgrade that to 2005). I downloaded myODBC and was able to
> > import most of the tables, however when I try to import the largest
> > table (about 18GB) it fails by running out of memory. The machine I'm
> > using has 4 gigs of memory in it, so I don't think it's the machine,
> > however SQL Server 2000 Standard can only take up to 2 gigs of memory
> > which I think is the problem. How can I work around this? I'm
> > thinking of trying to use mysqldump to dump pieces of the table at a
> > time and import it that way, but i think that will take an
> > unreasonably long time.
> > Thanks for any help!
Hi,
Thanks for the response.
I'm using myODBC to connect to the mysql server through an ODBC
connection and Data Transformation Services to import the data.
Dylan|||You can set the transformation to batch the load, which might release the
memory stress.
I would probably export the data using a mySQL tool, then BCP the data into
the SQL Server.
--
Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration /
Microsoft Office SharePoint Server 2007: Configuration)
MCITP (dbadmin, dbdev)
"dylan" wrote:
> On May 30, 3:06 pm, mrdenny <mrde...@.discussions.microsoft.com> wrote:
> > What method are you using to load the data from the MySQL database to the MS
> > SQL database?
> > --
> > Denny
> > MCSA (2003) / MCDBA (SQL 2000)
> > MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration /
> > Microsoft Office SharePoint Server 2007: Configuration)
> > MCITP (dbadmin, dbdev)
> >
> > "dylan" wrote:
> > > I'm trying to migrate a mysql database to sql server 2000 (then I'm
> > > going to upgrade that to 2005). I downloaded myODBC and was able to
> > > import most of the tables, however when I try to import the largest
> > > table (about 18GB) it fails by running out of memory. The machine I'm
> > > using has 4 gigs of memory in it, so I don't think it's the machine,
> > > however SQL Server 2000 Standard can only take up to 2 gigs of memory
> > > which I think is the problem. How can I work around this? I'm
> > > thinking of trying to use mysqldump to dump pieces of the table at a
> > > time and import it that way, but i think that will take an
> > > unreasonably long time.
> > > Thanks for any help!
> Hi,
> Thanks for the response.
> I'm using myODBC to connect to the mysql server through an ODBC
> connection and Data Transformation Services to import the data.
> Dylan
>|||On May 30, 3:40 pm, mrdenny <mrde...@.discussions.microsoft.com> wrote:
> You can set the transformation to batch the load, which might release the
> memory stress.
> I would probably export the data using a mySQL tool, then BCP the data into
> the SQL Server.
> --
> Denny
> MCSA (2003) / MCDBA (SQL 2000)
> MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration /
> Microsoft Office SharePoint Server 2007: Configuration)
> MCITP (dbadmin, dbdev)
> "dylan" wrote:
> > On May 30, 3:06 pm, mrdenny <mrde...@.discussions.microsoft.com> wrote:
> > > What method are you using to load the data from the MySQL database to the MS
> > > SQL database?
> > > --
> > > Denny
> > > MCSA (2003) / MCDBA (SQL 2000)
> > > MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration /
> > > Microsoft Office SharePoint Server 2007: Configuration)
> > > MCITP (dbadmin, dbdev)
> > > "dylan" wrote:
> > > > I'm trying to migrate a mysql database to sql server 2000 (then I'm
> > > > going to upgrade that to 2005). I downloaded myODBC and was able to
> > > > import most of the tables, however when I try to import the largest
> > > > table (about 18GB) it fails by running out of memory. The machine I'm
> > > > using has 4 gigs of memory in it, so I don't think it's the machine,
> > > > however SQL Server 2000 Standard can only take up to 2 gigs of memory
> > > > which I think is the problem. How can I work around this? I'm
> > > > thinking of trying to use mysqldump to dump pieces of the table at a
> > > > time and import it that way, but i think that will take an
> > > > unreasonably long time.
> > > > Thanks for any help!
> > Hi,
> > Thanks for the response.
> > I'm using myODBC to connect to the mysql server through an ODBC
> > connection and Data Transformation Services to import the data.
> > Dylan
Ok, thanks. I'll look for the batching option, and if that doesn't
work I'll look at bcp. I've never used that but it looks easy enough.|||Hello,
Use the below steps:-
1. In the MySQL Side extract the data from table to a comma seperated text
file
2. Copy the file to SQL Server machine
3. Create the table structure with out indexes and triggers
4. Load the table using BCP IN or BULK INSERT with batch commit options.
5. Create indexes and triggers
6. If you database is in FULL recovery mode, make sure you backup the
transaction log every 2 minutes or so while loading the data.
Thanks
Hari
"dylan" <dylan.roehrig@.gmail.com> wrote in message
news:1180554251.701728.181170@.k79g2000hse.googlegroups.com...
> On May 30, 3:40 pm, mrdenny <mrde...@.discussions.microsoft.com> wrote:
>> You can set the transformation to batch the load, which might release the
>> memory stress.
>> I would probably export the data using a mySQL tool, then BCP the data
>> into
>> the SQL Server.
>> --
>> Denny
>> MCSA (2003) / MCDBA (SQL 2000)
>> MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration
>> /
>> Microsoft Office SharePoint Server 2007: Configuration)
>> MCITP (dbadmin, dbdev)
>> "dylan" wrote:
>> > On May 30, 3:06 pm, mrdenny <mrde...@.discussions.microsoft.com> wrote:
>> > > What method are you using to load the data from the MySQL database to
>> > > the MS
>> > > SQL database?
>> > > --
>> > > Denny
>> > > MCSA (2003) / MCDBA (SQL 2000)
>> > > MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0:
>> > > Configuration /
>> > > Microsoft Office SharePoint Server 2007: Configuration)
>> > > MCITP (dbadmin, dbdev)
>> > > "dylan" wrote:
>> > > > I'm trying to migrate a mysql database to sql server 2000 (then I'm
>> > > > going to upgrade that to 2005). I downloaded myODBC and was able
>> > > > to
>> > > > import most of the tables, however when I try to import the largest
>> > > > table (about 18GB) it fails by running out of memory. The machine
>> > > > I'm
>> > > > using has 4 gigs of memory in it, so I don't think it's the
>> > > > machine,
>> > > > however SQL Server 2000 Standard can only take up to 2 gigs of
>> > > > memory
>> > > > which I think is the problem. How can I work around this? I'm
>> > > > thinking of trying to use mysqldump to dump pieces of the table at
>> > > > a
>> > > > time and import it that way, but i think that will take an
>> > > > unreasonably long time.
>> > > > Thanks for any help!
>> > Hi,
>> > Thanks for the response.
>> > I'm using myODBC to connect to the mysql server through an ODBC
>> > connection and Data Transformation Services to import the data.
>> > Dylan
> Ok, thanks. I'll look for the batching option, and if that doesn't
> work I'll look at bcp. I've never used that but it looks easy enough.
>|||On May 30, 10:03 pm, "Hari Prasad" <hari_prasa...@.hotmail.com> wrote:
> Hello,
> Use the below steps:-
> 1. In the MySQL Side extract the data from table to a comma seperated text
> file
> 2. Copy the file to SQL Server machine
> 3. Create the table structure with out indexes and triggers
> 4. Load the table using BCP IN or BULK INSERT with batch commit options.
> 5. Create indexes and triggers
> 6. If you database is in FULL recovery mode, make sure you backup the
> transaction log every 2 minutes or so while loading the data.
> Thanks
> Hari
> "dylan" <dylan.roeh...@.gmail.com> wrote in message
> news:1180554251.701728.181170@.k79g2000hse.googlegroups.com...
> > On May 30, 3:40 pm, mrdenny <mrde...@.discussions.microsoft.com> wrote:
> >> You can set the transformation to batch the load, which might release the
> >> memory stress.
> >> I would probably export the data using a mySQL tool, then BCP the data
> >> into
> >> the SQL Server.
> >> --
> >> Denny
> >> MCSA (2003) / MCDBA (SQL 2000)
> >> MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration
> >> /
> >> Microsoft Office SharePoint Server 2007: Configuration)
> >> MCITP (dbadmin, dbdev)
> >> "dylan" wrote:
> >> > On May 30, 3:06 pm, mrdenny <mrde...@.discussions.microsoft.com> wrote:
> >> > > What method are you using to load the data from the MySQL database to
> >> > > the MS
> >> > > SQL database?
> >> > > --
> >> > > Denny
> >> > > MCSA (2003) / MCDBA (SQL 2000)
> >> > > MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0:
> >> > > Configuration /
> >> > > Microsoft Office SharePoint Server 2007: Configuration)
> >> > > MCITP (dbadmin, dbdev)
> >> > > "dylan" wrote:
> >> > > > I'm trying to migrate a mysql database to sql server 2000 (then I'm
> >> > > > going to upgrade that to 2005). I downloaded myODBC and was able
> >> > > > to
> >> > > > import most of the tables, however when I try to import the largest
> >> > > > table (about 18GB) it fails by running out of memory. The machine
> >> > > > I'm
> >> > > > using has 4 gigs of memory in it, so I don't think it's the
> >> > > > machine,
> >> > > > however SQL Server 2000 Standard can only take up to 2 gigs of
> >> > > > memory
> >> > > > which I think is the problem. How can I work around this? I'm
> >> > > > thinking of trying to use mysqldump to dump pieces of the table at
> >> > > > a
> >> > > > time and import it that way, but i think that will take an
> >> > > > unreasonably long time.
> >> > > > Thanks for any help!
> >> > Hi,
> >> > Thanks for the response.
> >> > I'm using myODBC to connect to the mysql server through an ODBC
> >> > connection and Data Transformation Services to import the data.
> >> > Dylan
> > Ok, thanks. I'll look for the batching option, and if that doesn't
> > work I'll look at bcp. I've never used that but it looks easy enough.
Thanks Hari, I'm running into a problem dumping the data into a a
comma separated text file though. Is it possible to do that when the
data in the source table is a blob?|||On May 31, 9:19 am, dylan <dylan.roeh...@.gmail.com> wrote:
> On May 30, 10:03 pm, "Hari Prasad" <hari_prasa...@.hotmail.com> wrote:
>
> > Hello,
> > Use the below steps:-
> > 1. In the MySQL Side extract the data from table to a comma seperated text
> > file
> > 2. Copy the file to SQL Server machine
> > 3. Create the table structure with out indexes and triggers
> > 4. Load the table using BCP IN or BULK INSERT with batch commit options.
> > 5. Create indexes and triggers
> > 6. If you database is in FULL recovery mode, make sure you backup the
> > transaction log every 2 minutes or so while loading the data.
> > Thanks
> > Hari
> > "dylan" <dylan.roeh...@.gmail.com> wrote in message
> >news:1180554251.701728.181170@.k79g2000hse.googlegroups.com...
> > > On May 30, 3:40 pm, mrdenny <mrde...@.discussions.microsoft.com> wrote:
> > >> You can set the transformation to batch the load, which might release the
> > >> memory stress.
> > >> I would probably export the data using a mySQL tool, then BCP the data
> > >> into
> > >> the SQL Server.
> > >> --
> > >> Denny
> > >> MCSA (2003) / MCDBA (SQL 2000)
> > >> MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration
> > >> /
> > >> Microsoft Office SharePoint Server 2007: Configuration)
> > >> MCITP (dbadmin, dbdev)
> > >> "dylan" wrote:
> > >> > On May 30, 3:06 pm, mrdenny <mrde...@.discussions.microsoft.com> wrote:
> > >> > > What method are you using to load the data from the MySQL database to
> > >> > > the MS
> > >> > > SQL database?
> > >> > > --
> > >> > > Denny
> > >> > > MCSA (2003) / MCDBA (SQL 2000)
> > >> > > MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0:
> > >> > > Configuration /
> > >> > > Microsoft Office SharePoint Server 2007: Configuration)
> > >> > > MCITP (dbadmin, dbdev)
> > >> > > "dylan" wrote:
> > >> > > > I'm trying to migrate a mysql database to sql server 2000 (then I'm
> > >> > > > going to upgrade that to 2005). I downloaded myODBC and was able
> > >> > > > to
> > >> > > > import most of the tables, however when I try to import the largest
> > >> > > > table (about 18GB) it fails by running out of memory. The machine
> > >> > > > I'm
> > >> > > > using has 4 gigs of memory in it, so I don't think it's the
> > >> > > > machine,
> > >> > > > however SQL Server 2000 Standard can only take up to 2 gigs of
> > >> > > > memory
> > >> > > > which I think is the problem. How can I work around this? I'm
> > >> > > > thinking of trying to use mysqldump to dump pieces of the table at
> > >> > > > a
> > >> > > > time and import it that way, but i think that will take an
> > >> > > > unreasonably long time.
> > >> > > > Thanks for any help!
> > >> > Hi,
> > >> > Thanks for the response.
> > >> > I'm using myODBC to connect to the mysql server through an ODBC
> > >> > connection and Data Transformation Services to import the data.
> > >> > Dylan
> > > Ok, thanks. I'll look for the batching option, and if that doesn't
> > > work I'll look at bcp. I've never used that but it looks easy enough.
> Thanks Hari, I'm running into a problem dumping the data into a a
> comma separated text file though. Is it possible to do that when the
> data in the source table is a blob?
Well, we ended up having to write a small app to import the data - but
it works.
going to upgrade that to 2005). I downloaded myODBC and was able to
import most of the tables, however when I try to import the largest
table (about 18GB) it fails by running out of memory. The machine I'm
using has 4 gigs of memory in it, so I don't think it's the machine,
however SQL Server 2000 Standard can only take up to 2 gigs of memory
which I think is the problem. How can I work around this? I'm
thinking of trying to use mysqldump to dump pieces of the table at a
time and import it that way, but i think that will take an
unreasonably long time.
Thanks for any help!What method are you using to load the data from the MySQL database to the MS
SQL database?
--
Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration /
Microsoft Office SharePoint Server 2007: Configuration)
MCITP (dbadmin, dbdev)
"dylan" wrote:
> I'm trying to migrate a mysql database to sql server 2000 (then I'm
> going to upgrade that to 2005). I downloaded myODBC and was able to
> import most of the tables, however when I try to import the largest
> table (about 18GB) it fails by running out of memory. The machine I'm
> using has 4 gigs of memory in it, so I don't think it's the machine,
> however SQL Server 2000 Standard can only take up to 2 gigs of memory
> which I think is the problem. How can I work around this? I'm
> thinking of trying to use mysqldump to dump pieces of the table at a
> time and import it that way, but i think that will take an
> unreasonably long time.
> Thanks for any help!
>|||On May 30, 3:06 pm, mrdenny <mrde...@.discussions.microsoft.com> wrote:
> What method are you using to load the data from the MySQL database to the MS
> SQL database?
> --
> Denny
> MCSA (2003) / MCDBA (SQL 2000)
> MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration /
> Microsoft Office SharePoint Server 2007: Configuration)
> MCITP (dbadmin, dbdev)
> "dylan" wrote:
> > I'm trying to migrate a mysql database to sql server 2000 (then I'm
> > going to upgrade that to 2005). I downloaded myODBC and was able to
> > import most of the tables, however when I try to import the largest
> > table (about 18GB) it fails by running out of memory. The machine I'm
> > using has 4 gigs of memory in it, so I don't think it's the machine,
> > however SQL Server 2000 Standard can only take up to 2 gigs of memory
> > which I think is the problem. How can I work around this? I'm
> > thinking of trying to use mysqldump to dump pieces of the table at a
> > time and import it that way, but i think that will take an
> > unreasonably long time.
> > Thanks for any help!
Hi,
Thanks for the response.
I'm using myODBC to connect to the mysql server through an ODBC
connection and Data Transformation Services to import the data.
Dylan|||You can set the transformation to batch the load, which might release the
memory stress.
I would probably export the data using a mySQL tool, then BCP the data into
the SQL Server.
--
Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration /
Microsoft Office SharePoint Server 2007: Configuration)
MCITP (dbadmin, dbdev)
"dylan" wrote:
> On May 30, 3:06 pm, mrdenny <mrde...@.discussions.microsoft.com> wrote:
> > What method are you using to load the data from the MySQL database to the MS
> > SQL database?
> > --
> > Denny
> > MCSA (2003) / MCDBA (SQL 2000)
> > MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration /
> > Microsoft Office SharePoint Server 2007: Configuration)
> > MCITP (dbadmin, dbdev)
> >
> > "dylan" wrote:
> > > I'm trying to migrate a mysql database to sql server 2000 (then I'm
> > > going to upgrade that to 2005). I downloaded myODBC and was able to
> > > import most of the tables, however when I try to import the largest
> > > table (about 18GB) it fails by running out of memory. The machine I'm
> > > using has 4 gigs of memory in it, so I don't think it's the machine,
> > > however SQL Server 2000 Standard can only take up to 2 gigs of memory
> > > which I think is the problem. How can I work around this? I'm
> > > thinking of trying to use mysqldump to dump pieces of the table at a
> > > time and import it that way, but i think that will take an
> > > unreasonably long time.
> > > Thanks for any help!
> Hi,
> Thanks for the response.
> I'm using myODBC to connect to the mysql server through an ODBC
> connection and Data Transformation Services to import the data.
> Dylan
>|||On May 30, 3:40 pm, mrdenny <mrde...@.discussions.microsoft.com> wrote:
> You can set the transformation to batch the load, which might release the
> memory stress.
> I would probably export the data using a mySQL tool, then BCP the data into
> the SQL Server.
> --
> Denny
> MCSA (2003) / MCDBA (SQL 2000)
> MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration /
> Microsoft Office SharePoint Server 2007: Configuration)
> MCITP (dbadmin, dbdev)
> "dylan" wrote:
> > On May 30, 3:06 pm, mrdenny <mrde...@.discussions.microsoft.com> wrote:
> > > What method are you using to load the data from the MySQL database to the MS
> > > SQL database?
> > > --
> > > Denny
> > > MCSA (2003) / MCDBA (SQL 2000)
> > > MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration /
> > > Microsoft Office SharePoint Server 2007: Configuration)
> > > MCITP (dbadmin, dbdev)
> > > "dylan" wrote:
> > > > I'm trying to migrate a mysql database to sql server 2000 (then I'm
> > > > going to upgrade that to 2005). I downloaded myODBC and was able to
> > > > import most of the tables, however when I try to import the largest
> > > > table (about 18GB) it fails by running out of memory. The machine I'm
> > > > using has 4 gigs of memory in it, so I don't think it's the machine,
> > > > however SQL Server 2000 Standard can only take up to 2 gigs of memory
> > > > which I think is the problem. How can I work around this? I'm
> > > > thinking of trying to use mysqldump to dump pieces of the table at a
> > > > time and import it that way, but i think that will take an
> > > > unreasonably long time.
> > > > Thanks for any help!
> > Hi,
> > Thanks for the response.
> > I'm using myODBC to connect to the mysql server through an ODBC
> > connection and Data Transformation Services to import the data.
> > Dylan
Ok, thanks. I'll look for the batching option, and if that doesn't
work I'll look at bcp. I've never used that but it looks easy enough.|||Hello,
Use the below steps:-
1. In the MySQL Side extract the data from table to a comma seperated text
file
2. Copy the file to SQL Server machine
3. Create the table structure with out indexes and triggers
4. Load the table using BCP IN or BULK INSERT with batch commit options.
5. Create indexes and triggers
6. If you database is in FULL recovery mode, make sure you backup the
transaction log every 2 minutes or so while loading the data.
Thanks
Hari
"dylan" <dylan.roehrig@.gmail.com> wrote in message
news:1180554251.701728.181170@.k79g2000hse.googlegroups.com...
> On May 30, 3:40 pm, mrdenny <mrde...@.discussions.microsoft.com> wrote:
>> You can set the transformation to batch the load, which might release the
>> memory stress.
>> I would probably export the data using a mySQL tool, then BCP the data
>> into
>> the SQL Server.
>> --
>> Denny
>> MCSA (2003) / MCDBA (SQL 2000)
>> MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration
>> /
>> Microsoft Office SharePoint Server 2007: Configuration)
>> MCITP (dbadmin, dbdev)
>> "dylan" wrote:
>> > On May 30, 3:06 pm, mrdenny <mrde...@.discussions.microsoft.com> wrote:
>> > > What method are you using to load the data from the MySQL database to
>> > > the MS
>> > > SQL database?
>> > > --
>> > > Denny
>> > > MCSA (2003) / MCDBA (SQL 2000)
>> > > MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0:
>> > > Configuration /
>> > > Microsoft Office SharePoint Server 2007: Configuration)
>> > > MCITP (dbadmin, dbdev)
>> > > "dylan" wrote:
>> > > > I'm trying to migrate a mysql database to sql server 2000 (then I'm
>> > > > going to upgrade that to 2005). I downloaded myODBC and was able
>> > > > to
>> > > > import most of the tables, however when I try to import the largest
>> > > > table (about 18GB) it fails by running out of memory. The machine
>> > > > I'm
>> > > > using has 4 gigs of memory in it, so I don't think it's the
>> > > > machine,
>> > > > however SQL Server 2000 Standard can only take up to 2 gigs of
>> > > > memory
>> > > > which I think is the problem. How can I work around this? I'm
>> > > > thinking of trying to use mysqldump to dump pieces of the table at
>> > > > a
>> > > > time and import it that way, but i think that will take an
>> > > > unreasonably long time.
>> > > > Thanks for any help!
>> > Hi,
>> > Thanks for the response.
>> > I'm using myODBC to connect to the mysql server through an ODBC
>> > connection and Data Transformation Services to import the data.
>> > Dylan
> Ok, thanks. I'll look for the batching option, and if that doesn't
> work I'll look at bcp. I've never used that but it looks easy enough.
>|||On May 30, 10:03 pm, "Hari Prasad" <hari_prasa...@.hotmail.com> wrote:
> Hello,
> Use the below steps:-
> 1. In the MySQL Side extract the data from table to a comma seperated text
> file
> 2. Copy the file to SQL Server machine
> 3. Create the table structure with out indexes and triggers
> 4. Load the table using BCP IN or BULK INSERT with batch commit options.
> 5. Create indexes and triggers
> 6. If you database is in FULL recovery mode, make sure you backup the
> transaction log every 2 minutes or so while loading the data.
> Thanks
> Hari
> "dylan" <dylan.roeh...@.gmail.com> wrote in message
> news:1180554251.701728.181170@.k79g2000hse.googlegroups.com...
> > On May 30, 3:40 pm, mrdenny <mrde...@.discussions.microsoft.com> wrote:
> >> You can set the transformation to batch the load, which might release the
> >> memory stress.
> >> I would probably export the data using a mySQL tool, then BCP the data
> >> into
> >> the SQL Server.
> >> --
> >> Denny
> >> MCSA (2003) / MCDBA (SQL 2000)
> >> MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration
> >> /
> >> Microsoft Office SharePoint Server 2007: Configuration)
> >> MCITP (dbadmin, dbdev)
> >> "dylan" wrote:
> >> > On May 30, 3:06 pm, mrdenny <mrde...@.discussions.microsoft.com> wrote:
> >> > > What method are you using to load the data from the MySQL database to
> >> > > the MS
> >> > > SQL database?
> >> > > --
> >> > > Denny
> >> > > MCSA (2003) / MCDBA (SQL 2000)
> >> > > MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0:
> >> > > Configuration /
> >> > > Microsoft Office SharePoint Server 2007: Configuration)
> >> > > MCITP (dbadmin, dbdev)
> >> > > "dylan" wrote:
> >> > > > I'm trying to migrate a mysql database to sql server 2000 (then I'm
> >> > > > going to upgrade that to 2005). I downloaded myODBC and was able
> >> > > > to
> >> > > > import most of the tables, however when I try to import the largest
> >> > > > table (about 18GB) it fails by running out of memory. The machine
> >> > > > I'm
> >> > > > using has 4 gigs of memory in it, so I don't think it's the
> >> > > > machine,
> >> > > > however SQL Server 2000 Standard can only take up to 2 gigs of
> >> > > > memory
> >> > > > which I think is the problem. How can I work around this? I'm
> >> > > > thinking of trying to use mysqldump to dump pieces of the table at
> >> > > > a
> >> > > > time and import it that way, but i think that will take an
> >> > > > unreasonably long time.
> >> > > > Thanks for any help!
> >> > Hi,
> >> > Thanks for the response.
> >> > I'm using myODBC to connect to the mysql server through an ODBC
> >> > connection and Data Transformation Services to import the data.
> >> > Dylan
> > Ok, thanks. I'll look for the batching option, and if that doesn't
> > work I'll look at bcp. I've never used that but it looks easy enough.
Thanks Hari, I'm running into a problem dumping the data into a a
comma separated text file though. Is it possible to do that when the
data in the source table is a blob?|||On May 31, 9:19 am, dylan <dylan.roeh...@.gmail.com> wrote:
> On May 30, 10:03 pm, "Hari Prasad" <hari_prasa...@.hotmail.com> wrote:
>
> > Hello,
> > Use the below steps:-
> > 1. In the MySQL Side extract the data from table to a comma seperated text
> > file
> > 2. Copy the file to SQL Server machine
> > 3. Create the table structure with out indexes and triggers
> > 4. Load the table using BCP IN or BULK INSERT with batch commit options.
> > 5. Create indexes and triggers
> > 6. If you database is in FULL recovery mode, make sure you backup the
> > transaction log every 2 minutes or so while loading the data.
> > Thanks
> > Hari
> > "dylan" <dylan.roeh...@.gmail.com> wrote in message
> >news:1180554251.701728.181170@.k79g2000hse.googlegroups.com...
> > > On May 30, 3:40 pm, mrdenny <mrde...@.discussions.microsoft.com> wrote:
> > >> You can set the transformation to batch the load, which might release the
> > >> memory stress.
> > >> I would probably export the data using a mySQL tool, then BCP the data
> > >> into
> > >> the SQL Server.
> > >> --
> > >> Denny
> > >> MCSA (2003) / MCDBA (SQL 2000)
> > >> MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration
> > >> /
> > >> Microsoft Office SharePoint Server 2007: Configuration)
> > >> MCITP (dbadmin, dbdev)
> > >> "dylan" wrote:
> > >> > On May 30, 3:06 pm, mrdenny <mrde...@.discussions.microsoft.com> wrote:
> > >> > > What method are you using to load the data from the MySQL database to
> > >> > > the MS
> > >> > > SQL database?
> > >> > > --
> > >> > > Denny
> > >> > > MCSA (2003) / MCDBA (SQL 2000)
> > >> > > MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0:
> > >> > > Configuration /
> > >> > > Microsoft Office SharePoint Server 2007: Configuration)
> > >> > > MCITP (dbadmin, dbdev)
> > >> > > "dylan" wrote:
> > >> > > > I'm trying to migrate a mysql database to sql server 2000 (then I'm
> > >> > > > going to upgrade that to 2005). I downloaded myODBC and was able
> > >> > > > to
> > >> > > > import most of the tables, however when I try to import the largest
> > >> > > > table (about 18GB) it fails by running out of memory. The machine
> > >> > > > I'm
> > >> > > > using has 4 gigs of memory in it, so I don't think it's the
> > >> > > > machine,
> > >> > > > however SQL Server 2000 Standard can only take up to 2 gigs of
> > >> > > > memory
> > >> > > > which I think is the problem. How can I work around this? I'm
> > >> > > > thinking of trying to use mysqldump to dump pieces of the table at
> > >> > > > a
> > >> > > > time and import it that way, but i think that will take an
> > >> > > > unreasonably long time.
> > >> > > > Thanks for any help!
> > >> > Hi,
> > >> > Thanks for the response.
> > >> > I'm using myODBC to connect to the mysql server through an ODBC
> > >> > connection and Data Transformation Services to import the data.
> > >> > Dylan
> > > Ok, thanks. I'll look for the batching option, and if that doesn't
> > > work I'll look at bcp. I've never used that but it looks easy enough.
> Thanks Hari, I'm running into a problem dumping the data into a a
> comma separated text file though. Is it possible to do that when the
> data in the source table is a blob?
Well, we ended up having to write a small app to import the data - but
it works.
import large table from mysql
I'm trying to migrate a mysql database to sql server 2000 (then I'm
going to upgrade that to 2005). I downloaded myODBC and was able to
import most of the tables, however when I try to import the largest
table (about 18GB) it fails by running out of memory. The machine I'm
using has 4 gigs of memory in it, so I don't think it's the machine,
however SQL Server 2000 Standard can only take up to 2 gigs of memory
which I think is the problem. How can I work around this? I'm
thinking of trying to use mysqldump to dump pieces of the table at a
time and import it that way, but i think that will take an
unreasonably long time.
Thanks for any help!What method are you using to load the data from the mysql database to the MS
SQL database?
--
Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration /
Microsoft Office SharePoint Server 2007: Configuration)
MCITP (dbadmin, dbdev)
"dylan" wrote:
> I'm trying to migrate a mysql database to sql server 2000 (then I'm
> going to upgrade that to 2005). I downloaded myODBC and was able to
> import most of the tables, however when I try to import the largest
> table (about 18GB) it fails by running out of memory. The machine I'm
> using has 4 gigs of memory in it, so I don't think it's the machine,
> however SQL Server 2000 Standard can only take up to 2 gigs of memory
> which I think is the problem. How can I work around this? I'm
> thinking of trying to use mysqldump to dump pieces of the table at a
> time and import it that way, but i think that will take an
> unreasonably long time.
> Thanks for any help!
>|||On May 30, 3:06 pm, mrdenny <mrde...@.discussions.microsoft.com> wrote:[vbcol=seagreen]
> What method are you using to load the data from the mysql database to the
MS
> SQL database?
> --
> Denny
> MCSA (2003) / MCDBA (SQL 2000)
> MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration
/
> Microsoft Office SharePoint Server 2007: Configuration)
> MCITP (dbadmin, dbdev)
> "dylan" wrote:
Hi,
Thanks for the response.
I'm using myODBC to connect to the mysql server through an ODBC
connection and Data Transformation Services to import the data.
Dylan|||You can set the transformation to batch the load, which might release the
memory stress.
I would probably export the data using a mysql tool, then BCP the data into
the SQL Server.
--
Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration /
Microsoft Office SharePoint Server 2007: Configuration)
MCITP (dbadmin, dbdev)
"dylan" wrote:
> On May 30, 3:06 pm, mrdenny <mrde...@.discussions.microsoft.com> wrote:
> Hi,
> Thanks for the response.
> I'm using myODBC to connect to the mysql server through an ODBC
> connection and Data Transformation Services to import the data.
> Dylan
>|||On May 30, 3:40 pm, mrdenny <mrde...@.discussions.microsoft.com> wrote:[vbcol=seagreen]
> You can set the transformation to batch the load, which might release the
> memory stress.
> I would probably export the data using a mysql tool, then BCP the data int
o
> the SQL Server.
> --
> Denny
> MCSA (2003) / MCDBA (SQL 2000)
> MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration
/
> Microsoft Office SharePoint Server 2007: Configuration)
> MCITP (dbadmin, dbdev)
> "dylan" wrote:
>
>
>
Ok, thanks. I'll look for the batching option, and if that doesn't
work I'll look at bcp. I've never used that but it looks easy enough.|||Hello,
Use the below steps:-
1. In the mysql Side extract the data from table to a comma seperated text
file
2. Copy the file to SQL Server machine
3. Create the table structure with out indexes and triggers
4. Load the table using BCP IN or BULK INSERT with batch commit options.
5. Create indexes and triggers
6. If you database is in FULL recovery mode, make sure you backup the
transaction log every 2 minutes or so while loading the data.
Thanks
Hari
"dylan" <dylan.roehrig@.gmail.com> wrote in message
news:1180554251.701728.181170@.k79g2000hse.googlegroups.com...
> On May 30, 3:40 pm, mrdenny <mrde...@.discussions.microsoft.com> wrote:
> Ok, thanks. I'll look for the batching option, and if that doesn't
> work I'll look at bcp. I've never used that but it looks easy enough.
>|||On May 30, 10:03 pm, "Hari Prasad" <hari_prasa...@.hotmail.com> wrote:[vbcol=seagreen]
> Hello,
> Use the below steps:-
> 1. In the mysql Side extract the data from table to a comma seperated text
> file
> 2. Copy the file to SQL Server machine
> 3. Create the table structure with out indexes and triggers
> 4. Load the table using BCP IN or BULK INSERT with batch commit options.
> 5. Create indexes and triggers
> 6. If you database is in FULL recovery mode, make sure you backup the
> transaction log every 2 minutes or so while loading the data.
> Thanks
> Hari
> "dylan" <dylan.roeh...@.gmail.com> wrote in message
> news:1180554251.701728.181170@.k79g2000hse.googlegroups.com...
>
>
>
>
>
>
>
Thanks Hari, I'm running into a problem dumping the data into a a
comma separated text file though. Is it possible to do that when the
data in the source table is a blob?|||On May 31, 9:19 am, dylan <dylan.roeh...@.gmail.com> wrote:
> On May 30, 10:03 pm, "Hari Prasad" <hari_prasa...@.hotmail.com> wrote:
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
> Thanks Hari, I'm running into a problem dumping the data into a a
> comma separated text file though. Is it possible to do that when the
> data in the source table is a blob?
Well, we ended up having to write a small app to import the data - but
it works.
going to upgrade that to 2005). I downloaded myODBC and was able to
import most of the tables, however when I try to import the largest
table (about 18GB) it fails by running out of memory. The machine I'm
using has 4 gigs of memory in it, so I don't think it's the machine,
however SQL Server 2000 Standard can only take up to 2 gigs of memory
which I think is the problem. How can I work around this? I'm
thinking of trying to use mysqldump to dump pieces of the table at a
time and import it that way, but i think that will take an
unreasonably long time.
Thanks for any help!What method are you using to load the data from the mysql database to the MS
SQL database?
--
Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration /
Microsoft Office SharePoint Server 2007: Configuration)
MCITP (dbadmin, dbdev)
"dylan" wrote:
> I'm trying to migrate a mysql database to sql server 2000 (then I'm
> going to upgrade that to 2005). I downloaded myODBC and was able to
> import most of the tables, however when I try to import the largest
> table (about 18GB) it fails by running out of memory. The machine I'm
> using has 4 gigs of memory in it, so I don't think it's the machine,
> however SQL Server 2000 Standard can only take up to 2 gigs of memory
> which I think is the problem. How can I work around this? I'm
> thinking of trying to use mysqldump to dump pieces of the table at a
> time and import it that way, but i think that will take an
> unreasonably long time.
> Thanks for any help!
>|||On May 30, 3:06 pm, mrdenny <mrde...@.discussions.microsoft.com> wrote:[vbcol=seagreen]
> What method are you using to load the data from the mysql database to the
MS
> SQL database?
> --
> Denny
> MCSA (2003) / MCDBA (SQL 2000)
> MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration
/
> Microsoft Office SharePoint Server 2007: Configuration)
> MCITP (dbadmin, dbdev)
> "dylan" wrote:
Hi,
Thanks for the response.
I'm using myODBC to connect to the mysql server through an ODBC
connection and Data Transformation Services to import the data.
Dylan|||You can set the transformation to batch the load, which might release the
memory stress.
I would probably export the data using a mysql tool, then BCP the data into
the SQL Server.
--
Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration /
Microsoft Office SharePoint Server 2007: Configuration)
MCITP (dbadmin, dbdev)
"dylan" wrote:
> On May 30, 3:06 pm, mrdenny <mrde...@.discussions.microsoft.com> wrote:
> Hi,
> Thanks for the response.
> I'm using myODBC to connect to the mysql server through an ODBC
> connection and Data Transformation Services to import the data.
> Dylan
>|||On May 30, 3:40 pm, mrdenny <mrde...@.discussions.microsoft.com> wrote:[vbcol=seagreen]
> You can set the transformation to batch the load, which might release the
> memory stress.
> I would probably export the data using a mysql tool, then BCP the data int
o
> the SQL Server.
> --
> Denny
> MCSA (2003) / MCDBA (SQL 2000)
> MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration
/
> Microsoft Office SharePoint Server 2007: Configuration)
> MCITP (dbadmin, dbdev)
> "dylan" wrote:
>
>
>
Ok, thanks. I'll look for the batching option, and if that doesn't
work I'll look at bcp. I've never used that but it looks easy enough.|||Hello,
Use the below steps:-
1. In the mysql Side extract the data from table to a comma seperated text
file
2. Copy the file to SQL Server machine
3. Create the table structure with out indexes and triggers
4. Load the table using BCP IN or BULK INSERT with batch commit options.
5. Create indexes and triggers
6. If you database is in FULL recovery mode, make sure you backup the
transaction log every 2 minutes or so while loading the data.
Thanks
Hari
"dylan" <dylan.roehrig@.gmail.com> wrote in message
news:1180554251.701728.181170@.k79g2000hse.googlegroups.com...
> On May 30, 3:40 pm, mrdenny <mrde...@.discussions.microsoft.com> wrote:
> Ok, thanks. I'll look for the batching option, and if that doesn't
> work I'll look at bcp. I've never used that but it looks easy enough.
>|||On May 30, 10:03 pm, "Hari Prasad" <hari_prasa...@.hotmail.com> wrote:[vbcol=seagreen]
> Hello,
> Use the below steps:-
> 1. In the mysql Side extract the data from table to a comma seperated text
> file
> 2. Copy the file to SQL Server machine
> 3. Create the table structure with out indexes and triggers
> 4. Load the table using BCP IN or BULK INSERT with batch commit options.
> 5. Create indexes and triggers
> 6. If you database is in FULL recovery mode, make sure you backup the
> transaction log every 2 minutes or so while loading the data.
> Thanks
> Hari
> "dylan" <dylan.roeh...@.gmail.com> wrote in message
> news:1180554251.701728.181170@.k79g2000hse.googlegroups.com...
>
>
>
>
>
>
>
Thanks Hari, I'm running into a problem dumping the data into a a
comma separated text file though. Is it possible to do that when the
data in the source table is a blob?|||On May 31, 9:19 am, dylan <dylan.roeh...@.gmail.com> wrote:
> On May 30, 10:03 pm, "Hari Prasad" <hari_prasa...@.hotmail.com> wrote:
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
> Thanks Hari, I'm running into a problem dumping the data into a a
> comma separated text file though. Is it possible to do that when the
> data in the source table is a blob?
Well, we ended up having to write a small app to import the data - but
it works.
Monday, March 26, 2012
Import From excel
I have an excel file with 11000 lines
I made up a column for numbering the rows, e.g. 1,2,3,4,5,6...11000
I made up a column for numbering the rows, e.g. 1,2,3,4,5,6...11000
I migrate this excel into table for sql 2000
While i use the custom built and specify order by record,
this gives me sth like that : 1,2,3,4,5,6...100..101, 1011,1012, ,120,121....
I see that I parsed them as floats can this be the issue?
Or should I do sth else wrong?Despite the fact in the procedure I created the select with order by record which record is 1,2,3,4,5,6 and I have it as integer,
unless I export the data to excel and re-import from excel, the records from a simple select popup as they like...and not by record order...
I hope someone has dealt with that in the past...help?sql
Subscribe to:
Posts (Atom)