Running a SQL file from OSQL command line utility

Hi,
I have dumped a very large database from mysql (using mysqldump program)
as a raw sql file. The reason was, convert this database to a MSSQL
database. Since mysqldump creates the file as raw sql file with the
database-table structures and the data in it, I thought using OSQL command
line utilities should work to out this whole database in MSSQL server.
I have run this command from command line:
osql -u sa -i mysqldump.sql
It is going since yesterday. It has been almost 36 hours that it's
started. And in the mssql server, I see no database created yet. On the
screen of the command line, I see bunch of numbers are going in order. I
assume they are row numbers of the tables processed. But, if it is doing it,
then where is it saving all this data ? I have checked the tempdb, pub db,
other dbs, and I see no tables related to the database I am inserting. Will
it populate it at the and of the job ? Or, am I doing something wrong here
?
Regards.
Murtix.Hi Murtix
You cannot load into SQL Server anything other than a SQL Server backup.
Is mysqldump the actual backup file? If so, then what you are seeing in the
line number is osql thinking it is receiving a command, and it keep
receiving them, waiting to receive a 'go' to tell it to execute what it has
received. It will not even try to process whatever bits are in the mysqldump
file until it finally encounters a 'go'.
The contents of an input file passed to osql (with -i) needs to be a SQL
script, containing valid Transact-SQL statements. Even if you had a valid
SQL Server backup file, the input file to osql would have to be script in
the TSQL language telling SQL Server to restore a specified backup file, you
could not just pass in the backup file itself.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Murtix Van Basten" <nospam@.nospam.org> wrote in message
news:3fee42a7$1_3@.athenanews.com...
> Hi,
> I have dumped a very large database from mysql (using mysqldump
program)
> as a raw sql file. The reason was, convert this database to a MSSQL
> database. Since mysqldump creates the file as raw sql file with the
> database-table structures and the data in it, I thought using OSQL command
> line utilities should work to out this whole database in MSSQL server.
> I have run this command from command line:
> osql -u sa -i mysqldump.sql
> It is going since yesterday. It has been almost 36 hours that it's
> started. And in the mssql server, I see no database created yet. On the
> screen of the command line, I see bunch of numbers are going in order. I
> assume they are row numbers of the tables processed. But, if it is doing
it,
> then where is it saving all this data ? I have checked the tempdb, pub db,
> other dbs, and I see no tables related to the database I am inserting.
Will
> it populate it at the and of the job ? Or, am I doing something wrong
here
> ?
> Regards.
> Murtix.
>|||Hi ,
You cant use that file (generated using MYSQLDUMP) directly in MSSQL using
OSQL. This raw file can be used only in MYSQL incase you need a recovary.
OSQL program can take only TSQL commands.
The solution for you is:
1. Create a database in MSSQL server Manually
2. Use DTS to transfer Tables to MSSQL Server
If your table size is very huge , generate a text file in MYSQL for each
tables and use BCP IN to load inside MSSQL.
Thanks
Hari
MCDBA
"Murtix Van Basten" <nospam@.nospam.org> wrote in message
news:3fee42a7$1_3@.athenanews.com...
> Hi,
> I have dumped a very large database from mysql (using mysqldump
program)
> as a raw sql file. The reason was, convert this database to a MSSQL
> database. Since mysqldump creates the file as raw sql file with the
> database-table structures and the data in it, I thought using OSQL command
> line utilities should work to out this whole database in MSSQL server.
> I have run this command from command line:
> osql -u sa -i mysqldump.sql
> It is going since yesterday. It has been almost 36 hours that it's
> started. And in the mssql server, I see no database created yet. On the
> screen of the command line, I see bunch of numbers are going in order. I
> assume they are row numbers of the tables processed. But, if it is doing
it,
> then where is it saving all this data ? I have checked the tempdb, pub db,
> other dbs, and I see no tables related to the database I am inserting.
Will
> it populate it at the and of the job ? Or, am I doing something wrong
here
> ?
> Regards.
> Murtix.
>|||I see.
Thanks.
It is a mysqldump file so, there is not any t-sql scripts in it. (AFAIK
T-SQL is only being used by MSSQL, and I dont think mysql could create a
raw-sql file in that format) That means I have been doing nothing :-(.
But just of a curiosity. what is the numbers I am seeing on the screen
though ? I mean, could you tell me what is the computer processing now ?
Murtix.
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:eqe$00OzDHA.1676@.TK2MSFTNGP12.phx.gbl...
> Hi Murtix
> You cannot load into SQL Server anything other than a SQL Server backup.
> Is mysqldump the actual backup file? If so, then what you are seeing in
the
> line number is osql thinking it is receiving a command, and it keep
> receiving them, waiting to receive a 'go' to tell it to execute what it
has
> received. It will not even try to process whatever bits are in the
mysqldump
> file until it finally encounters a 'go'.
> The contents of an input file passed to osql (with -i) needs to be a SQL
> script, containing valid Transact-SQL statements. Even if you had a valid
> SQL Server backup file, the input file to osql would have to be script in
> the TSQL language telling SQL Server to restore a specified backup file,
you
> could not just pass in the backup file itself.
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "Murtix Van Basten" <nospam@.nospam.org> wrote in message
> news:3fee42a7$1_3@.athenanews.com...
> > Hi,
> >
> > I have dumped a very large database from mysql (using mysqldump
> program)
> > as a raw sql file. The reason was, convert this database to a MSSQL
> > database. Since mysqldump creates the file as raw sql file with the
> > database-table structures and the data in it, I thought using OSQL
command
> > line utilities should work to out this whole database in MSSQL server.
> >
> > I have run this command from command line:
> >
> > osql -u sa -i mysqldump.sql
> >
> > It is going since yesterday. It has been almost 36 hours that it's
> > started. And in the mssql server, I see no database created yet. On the
> > screen of the command line, I see bunch of numbers are going in order. I
> > assume they are row numbers of the tables processed. But, if it is doing
> it,
> > then where is it saving all this data ? I have checked the tempdb, pub
db,
> > other dbs, and I see no tables related to the database I am inserting.
> Will
> > it populate it at the and of the job ? Or, am I doing something wrong
> here
> > ?
> >
> > Regards.
> >
> > Murtix.
> >
> >
>|||Hi Hari,
all the pain I suffered to do this, was because of not to use BCP method.
I guess I will have to use it after finding out osql methid would not work.
Thank you clearifying this issue for me.
Best Regards.
Murtix.
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:Oaj1x2OzDHA.1412@.TK2MSFTNGP11.phx.gbl...
> Hi ,
> You cant use that file (generated using MYSQLDUMP) directly in MSSQL using
> OSQL. This raw file can be used only in MYSQL incase you need a recovary.
> OSQL program can take only TSQL commands.
> The solution for you is:
> 1. Create a database in MSSQL server Manually
> 2. Use DTS to transfer Tables to MSSQL Server
> If your table size is very huge , generate a text file in MYSQL for each
> tables and use BCP IN to load inside MSSQL.
> Thanks
> Hari
> MCDBA
>
>
>
> "Murtix Van Basten" <nospam@.nospam.org> wrote in message
> news:3fee42a7$1_3@.athenanews.com...
> > Hi,
> >
> > I have dumped a very large database from mysql (using mysqldump
> program)
> > as a raw sql file. The reason was, convert this database to a MSSQL
> > database. Since mysqldump creates the file as raw sql file with the
> > database-table structures and the data in it, I thought using OSQL
command
> > line utilities should work to out this whole database in MSSQL server.
> >
> > I have run this command from command line:
> >
> > osql -u sa -i mysqldump.sql
> >
> > It is going since yesterday. It has been almost 36 hours that it's
> > started. And in the mssql server, I see no database created yet. On the
> > screen of the command line, I see bunch of numbers are going in order. I
> > assume they are row numbers of the tables processed. But, if it is doing
> it,
> > then where is it saving all this data ? I have checked the tempdb, pub
db,
> > other dbs, and I see no tables related to the database I am inserting.
> Will
> > it populate it at the and of the job ? Or, am I doing something wrong
> here
> > ?
> >
> > Regards.
> >
> > Murtix.
> >
> >
>|||Murtix,
> It is a mysqldump file so, there is not any t-sql scripts in it.
> (AFAIK T-SQL is only being used by MSSQL, and I dont think mysql
> could create a raw-sql file in that format) That means I have
> been doing nothing :-(.
Please take a look at this product. It is probably the best solution
for your problem. You can download the evaluation version.
http://www.ispirer.com/products/
> But just of a curiosity. what is the numbers I am seeing on the
> screen though ? I mean, could you tell me what is the computer
> processing now ?
As Kalen said, osql is trying to interpret the file as a series of
T-SQL statements. The numbers you see are line numbers of "lines"
that osql has parsed. They can be suppressed wih the -n command line
switch.
Linda|||Hi Murtix
What exactly do you mean by 'raw' sql file? SQL is a language, so the only
thing I can think of 'raw' SQL meaning is a file of ANSI standard SQL
commands.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Murtix Van Basten" <nospam@.nospam.org> wrote in message
news:3fee4677$1_1@.athenanews.com...
> I see.
> Thanks.
> It is a mysqldump file so, there is not any t-sql scripts in it. (AFAIK
> T-SQL is only being used by MSSQL, and I dont think mysql could create a
> raw-sql file in that format) That means I have been doing nothing :-(.
> But just of a curiosity. what is the numbers I am seeing on the screen
> though ? I mean, could you tell me what is the computer processing now ?
> Murtix.
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:eqe$00OzDHA.1676@.TK2MSFTNGP12.phx.gbl...
> > Hi Murtix
> >
> > You cannot load into SQL Server anything other than a SQL Server backup.
> >
> > Is mysqldump the actual backup file? If so, then what you are seeing in
> the
> > line number is osql thinking it is receiving a command, and it keep
> > receiving them, waiting to receive a 'go' to tell it to execute what it
> has
> > received. It will not even try to process whatever bits are in the
> mysqldump
> > file until it finally encounters a 'go'.
> >
> > The contents of an input file passed to osql (with -i) needs to be a SQL
> > script, containing valid Transact-SQL statements. Even if you had a
valid
> > SQL Server backup file, the input file to osql would have to be script
in
> > the TSQL language telling SQL Server to restore a specified backup file,
> you
> > could not just pass in the backup file itself.
> >
> > --
> > HTH
> > --
> > Kalen Delaney
> > SQL Server MVP
> > www.SolidQualityLearning.com
> >
> >
> > "Murtix Van Basten" <nospam@.nospam.org> wrote in message
> > news:3fee42a7$1_3@.athenanews.com...
> > > Hi,
> > >
> > > I have dumped a very large database from mysql (using mysqldump
> > program)
> > > as a raw sql file. The reason was, convert this database to a MSSQL
> > > database. Since mysqldump creates the file as raw sql file with the
> > > database-table structures and the data in it, I thought using OSQL
> command
> > > line utilities should work to out this whole database in MSSQL server.
> > >
> > > I have run this command from command line:
> > >
> > > osql -u sa -i mysqldump.sql
> > >
> > > It is going since yesterday. It has been almost 36 hours that it's
> > > started. And in the mssql server, I see no database created yet. On
the
> > > screen of the command line, I see bunch of numbers are going in order.
I
> > > assume they are row numbers of the tables processed. But, if it is
doing
> > it,
> > > then where is it saving all this data ? I have checked the tempdb, pub
> db,
> > > other dbs, and I see no tables related to the database I am inserting.
> > Will
> > > it populate it at the and of the job ? Or, am I doing something wrong
> > here
> > > ?
> > >
> > > Regards.
> > >
> > > Murtix.
> > >
> > >
> >
> >
>|||Murtix Van Basten (nospam@.nospam.org) writes:
> It is a mysqldump file so, there is not any t-sql scripts in it. (AFAIK
> T-SQL is only being used by MSSQL, and I dont think mysql could create a
> raw-sql file in that format) That means I have been doing nothing :-(.
> But just of a curiosity. what is the numbers I am seeing on the screen
> though ? I mean, could you tell me what is the computer processing now ?
OSQL is also intended to be a interactive utility, so when you start
OSQL without the -i switch, you can enter commands. The 1>, 2> etc are
the command prompts, one per line. As Linda said, you can use -n to
suppress these. You can also use -o to redirect output to a file, so
you can look at the error messages.
However, since MySQL is not like to generate SQL suitable for MS SQL Server,
the whole operation appears to be a fruitless one. You could possibly use
the file, but you would have to edit it manually to address changes
between the SQL dialects and to insert GO statements to separate
batches.
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp