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 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?

Import Human-Readable text file into SQL Server 2000

Hello,

I am receiving a text file that is produced from a mainframe that is
out of my control. I am attempting to find a (hopefully clean) way to
import it into a SQL Server database in an automated fashion. I am
not really concerned about how many tables it requires or what the
schema looks like as long as the data remains related and ends up in
its respective fields (I will probably use scratch tables for this).

The data is given to me in a format that is meant to be printed out
and read by human eyes (in a text file). The format looks something
like this:

Begin File:
------------------------
1234 1234 1234 1234 XYZ Company 01/01/2003
......More stuff related to XYZ company for a couple of lines ......
......(this stuff can easily be parsed by position)......

MCARD VISA AMEX DISC
------------------------
TOTAL 11111.11 4444.44 5555.55 30.01
TRANS FEE .20 .20 .15 .15
TRANS AMOUNT 2222.22 888.89 833.33 4.50
DISC .0165 .0165 .0365 .0355
------------------------

ANOTHER HEADER

.........More stuff related to XYZ Company............

End File:

Well, this isn't the exact format, but just an example. The point is
that all of the data in each column is related and should end up in
the same record which is related to the parent record of XYZ Company
(or all in a single record in a single table if that is the closest I
can get).

Also, the rows are not always present. For example, if TRANS FEE
doesn't apply to anything in the row, then the entire row will
collapse and TRANS AMOUNT would be the next line after TOTAL.

I was looking at the bcp utility and dts, but dts doesn't seem to have
the performance capabilities (or reliability for that matter) I am
looking for. Bcp seems like it might work if there is some advanced
formatting commands that I can't find in the documentation - Anyone?

The best I can come up with is to use a high level language such as C#
or VB.NET to parse the text file into another text file that is comma
delimited, and then use the bcp utility (or bulk insert) to import it
into SQL Server where I can then use TSQL to manipulate it how I want.
I am trying to eliminate the high level language parse and just go
straight from file to database. Does anybody know an easier route?

TIAI would probably still go with the high level language. You have multiple
pieces of information in different formats relating to the same bigger
object. It looks as though you have a fixed length part at the top and then
break into a pivot table in the middle. I don't know any format file that
is going to decipher that for you.

--
--

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

"Sven" <sstorhaug@.webuniverse.net> wrote in message
news:67ca584a.0309302103.1eb4b10e@.posting.google.c om...
> Hello,
> I am receiving a text file that is produced from a mainframe that is
> out of my control. I am attempting to find a (hopefully clean) way to
> import it into a SQL Server database in an automated fashion. I am
> not really concerned about how many tables it requires or what the
> schema looks like as long as the data remains related and ends up in
> its respective fields (I will probably use scratch tables for this).
> The data is given to me in a format that is meant to be printed out
> and read by human eyes (in a text file). The format looks something
> like this:
>
> Begin File:
> -----------------------
--
> 1234 1234 1234 1234 XYZ Company 01/01/2003
> .....More stuff related to XYZ company for a couple of lines ......
> .....(this stuff can easily be parsed by position)......
> MCARD VISA AMEX DISC
> -----------------------
--
> TOTAL 11111.11 4444.44 5555.55 30.01
> TRANS FEE .20 .20 .15 .15
> TRANS AMOUNT 2222.22 888.89 833.33 4.50
> DISC .0165 .0165 .0365 .0355
> -----------------------
--
> ANOTHER HEADER
> .........More stuff related to XYZ Company............
> End File:
>
> Well, this isn't the exact format, but just an example. The point is
> that all of the data in each column is related and should end up in
> the same record which is related to the parent record of XYZ Company
> (or all in a single record in a single table if that is the closest I
> can get).
> Also, the rows are not always present. For example, if TRANS FEE
> doesn't apply to anything in the row, then the entire row will
> collapse and TRANS AMOUNT would be the next line after TOTAL.
> I was looking at the bcp utility and dts, but dts doesn't seem to have
> the performance capabilities (or reliability for that matter) I am
> looking for. Bcp seems like it might work if there is some advanced
> formatting commands that I can't find in the documentation - Anyone?
> The best I can come up with is to use a high level language such as C#
> or VB.NET to parse the text file into another text file that is comma
> delimited, and then use the bcp utility (or bulk insert) to import it
> into SQL Server where I can then use TSQL to manipulate it how I want.
> I am trying to eliminate the high level language parse and just go
> straight from file to database. Does anybody know an easier route?
> TIA

Import Excel Spreadsheet

I am using TransferSpreadsheet from an ADP file to import an Excel
spreadsheet into an MSDE database. On one computer, when I run the code to
import the spreadsheet, it prompts for an SQLServer login. The same user ca
n
try it on a different computer and it will work fine. Data in the tables of
the database can be viewed and edited on the bad computer. But the
spreadsheet cannot be imported. I even tried the Get External Data->Import
menu option and it prompts for SQLServer login also.
Any ideas on what to look at on this computer to determine the problem?
Or any suggestions on a better way to import a spreadsheet into an SQLServer
database.?
Thanks!
JerryJerry..
you have to tell us what is the error that you get when you import.
What I usually do (if its only a few thousand rows to import), then I go
forr opendatasource(ODS). Check out in BOL for ODS and they give an example
of select from excel. U can then insert into the table in SQL Server.
But to investigate on why this error is occuring, you need to give us more
info on why its not getting imported.
--
"JerryWendell" wrote:

> I am using TransferSpreadsheet from an ADP file to import an Excel
> spreadsheet into an MSDE database. On one computer, when I run the code t
o
> import the spreadsheet, it prompts for an SQLServer login. The same user
can
> try it on a different computer and it will work fine. Data in the tables
of
> the database can be viewed and edited on the bad computer. But the
> spreadsheet cannot be imported. I even tried the Get External Data->Impor
t
> menu option and it prompts for SQLServer login also.
> Any ideas on what to look at on this computer to determine the problem?
> Or any suggestions on a better way to import a spreadsheet into an SQLServ
er
> database.?
> Thanks!
> Jerry
>|||Thanks for the responds.
This only happens on one particular computer. It works fine on the 8 other
computers that access the database. They are all running Access 2003 and
Windows XP.
When I try to import the spreadsheet, I am prompted to enter an SQL Server
Login. When I enter a valid login (I know it is a valid login because it
works from the other computers) after about a minute I get the following
error messages:
Connection Failed
SQLState: '01S00'
SQLServerError: 0
[Microsoft][ODBC SQLServerDriver][dbnmpntw]Invalid Connection String Attribute
SQLState: '01000'
SQLServerError: 2
[Microsoft][ODBC SQLServerDriver][dbnmpntw]ConnectionOpen
(CreateFile())
SQLState: '08001'
SQLServerError: 6
[Microsoft][ODBC SQLServerDriver][dbnmpntw]Specified SQL Server not found
Thanks for your help.
Jerry
"Omnibuzz" wrote:
> Jerry..
> you have to tell us what is the error that you get when you import.
> What I usually do (if its only a few thousand rows to import), then I go
> forr opendatasource(ODS). Check out in BOL for ODS and they give an exampl
e
> of select from excel. U can then insert into the table in SQL Server.
> But to investigate on why this error is occuring, you need to give us more
> info on why its not getting imported.
> --
>
>
> "JerryWendell" wrote:
>sql

Import data from Active Directory

Is there a way to import data from Active Directory using Data
Transformation Service (or maybe using other way) to SQL Server? I want to
import their username, first/last name, title, phone, etc.. and schedule
the import every night.
Any suggestions?http://www.windowsitpro.com/Article...4328/14328.html
--
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Zean Smith" wrote:

> Is there a way to import data from Active Directory using Data
> Transformation Service (or maybe using other way) to SQL Server? I want t
o
> import their username, first/last name, title, phone, etc.. and schedule
> the import every night.
> Any suggestions?
>
>

Import data from Active Directory

Is there a way to import data from Active Directory using Data
Transformation Service (or maybe using other way) to SQL Server? I want to
import their username, first/last name, title, phone, etc.. and schedule
the import every night.
Any suggestions?
http://www.windowsitpro.com/Article/...328/14328.html
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
"Zean Smith" wrote:

> Is there a way to import data from Active Directory using Data
> Transformation Service (or maybe using other way) to SQL Server? I want to
> import their username, first/last name, title, phone, etc.. and schedule
> the import every night.
> Any suggestions?
>
>