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 ,

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

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

Running a job via osql?

Hello All,
A VERY green SQL Server DBA here looking for some help. Our main production environment is Oracle, which utilizes Control-M as a scheduler. At the end of the Oracle batch process, we would like to automate a process to kick off a sql server job (perhaps via osql??) Is this possible?

Thanks in advance,
TonyYes, just create a bat file with the osql commands in it and execute the bat file..I would imagine you would have to have osql on the oracle box...so if it's unix, then I'm not sure...|||Thanks Brad!|||ummmm...That's Brett

Or "x002548"

Or "HEY YOU"

Or "You look like a man who needs a drink"

Or "Coach" (As in hey coach..ummm...can I play forward? 13 voices at once)

Don't forget to redirect the output to a log so you can track what happened...that would be from a second bat file calling the one with the osql commands...

I like this approach, because it kind reminds me of my old JCL home

Weep no more, my lady,
Oh weep no more today!
We will sing one song for the old JCL home,
For the old JCL home far away.|||Brett,
My sincerest apologies!! That's what I get for multi-tasking & not paying attention when I type.

Our scheduling software has a windows client version that would allow us to excute the .bat file on the SQL Server box.

In order to call the job, do I just refer to the Job Name in the /Q [query] portion of the osql "statement"?

Server- PROD01
User- Tony
Password- Test
Job Name- Get1|||See the attachments

The txt file should be the bat, but it didn't let me upload a bat file|||ok, it only allows 1 upload per post..

This is a sample sql that osql executes

Well maybe it would allow more than 1, it didn't like the *.sql extension which is what it was|||Very Cool!! Once again, many thanks. I know that yours was executing a .sql script. How do I get it to recognize a pre-exisiting SQL Server Job?|||Place something like this in the *.sql file

EXECUTE msdb..sp_start_job 'Load_Ledger_Init_sp'|||The job and subsequently the script that calls the job need to have an error trapping mechanism. With current syntax of both batch file and SQL script Control-M will report SUCCESS even if OSQL part fails. At a minimum add -b to OSQL to force it to fail if error occurs.|||The osql failure will show uo in the log.

I usually load the log to a table to interogate what happened.

Whats -b?

yeah, yeah, BOL|||The osql failure will show uo in the log.

I usually load the log to a table to interogate what happened.

Whats -b?

yeah, yeah, BOL

EDIT:

-b

Specifies that osql exits and returns a DOS ERRORLEVEL value when an error occurs. The value returned to the DOS ERRORLEVEL variable is 1 when the SQL Server error message has a severity of 10 or greater; otherwise, the value returned is 0. Microsoft MS-DOS batch files can test the value of DOS ERRORLEVEL and handle the error appropriately.|||The error WILL show up in the log, but Control-M will not know that the error occurred, unless you use -b.|||Good point.

But I still would interogate the log anyway...but thanks for the info...

Anything with a severity error of less than 10 though still returns a zero...

EXINFO 1 Informational, nonerror.
EXUSER 2 User error.
EXNONFATAL 3 Nonfatal error.
EXCONVERSION 4 Error in DB-Library data conversion.
5 The server has returned an error flag.
EXTIME 6 Time-out period exceeded while waiting for
a response from the server; the DBPROCESS is still alive.
EXPROGRAM 7 Coding error in user program.
EXRESOURCE 8 Running out of resources; the DBPROCESS may be dead.
EXCOMM 9 Failure in communication with server; the DBPROCESS is dead.
EXFATAL 10 Fatal error; the DBPROCESS is dead.
EXCONSISTENCY 11 Internal software error; notify your primary support provider|||Sample fool-proof batch for Control-M:

OSQL...
if errorlevel 1 goto ErrorHandler
goto end

:ErrorHandler
echo OSQL ended with code %ERRORLEVEL%!
exit /B 1

:end|||Fine, but what about the severity errors below 10?

You're not suggesting that they completed. And since the return code will be 0, you still have to check.

No?|||Sample fool-proof SQL script (pseudo):

<sql_statement>
if @.@.error != 0 begin
raiserror ('The last statement failed (<WHAT STATEMENT? BE SPECIFIC!>)', 15, 1)
return
end
...|||are you sure it's not brad?

Run large sql script via osql takes loooong time

I have a large sql script file. If I open it in Query Analyzer and run it,
it takes about 15 seconds to run. If i run the same file via osql with the
-i argument to specify the sql file, it takes several minutes to run.
Any idea why? Is there a better way to do this?
What i'm really trying to do is execute the sql in a file, from a stored
procedure. So in this instance i'm calling xp_cmdshell to kick off the osql.
like this:
EXEC ('xp_cmdshell ''osql -E -n -S myServer -d ' + @.newDbName + ' -i
C:\Dev\Current\SQL\DBSchema.sql''')
Is there a better way to execute a sql file in called by a stored procedure?
Thanks,
John
> Any idea why?
It can possibly be because the two environments has different default settings for some SET options,
resulting in different execution plans, or even things like index on views not being used from OSQL.
I'd start by comparing execution plans.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"john conwell" <johnconwell@.discussions.microsoft.com> wrote in message
news:D3232F62-F547-4C96-B9C8-3DE5DAC2FC68@.microsoft.com...
>I have a large sql script file. If I open it in Query Analyzer and run it,
> it takes about 15 seconds to run. If i run the same file via osql with the
> -i argument to specify the sql file, it takes several minutes to run.
> Any idea why? Is there a better way to do this?
> What i'm really trying to do is execute the sql in a file, from a stored
> procedure. So in this instance i'm calling xp_cmdshell to kick off the osql.
> like this:
> EXEC ('xp_cmdshell ''osql -E -n -S myServer -d ' + @.newDbName + ' -i
> C:\Dev\Current\SQL\DBSchema.sql''')
> Is there a better way to execute a sql file in called by a stored procedure?
> Thanks,
> John

Run large sql script via osql takes loooong time

I have a large sql script file. If I open it in Query Analyzer and run it,
it takes about 15 seconds to run. If i run the same file via osql with the
-i argument to specify the sql file, it takes several minutes to run.
Any idea why? Is there a better way to do this?
What i'm really trying to do is execute the sql in a file, from a stored
procedure. So in this instance i'm calling xp_cmdshell to kick off the osql.
like this:
EXEC ('xp_cmdshell ''osql -E -n -S myServer -d ' + @.newDbName + ' -i
C:\Dev\Current\SQL\DBSchema.sql''')
Is there a better way to execute a sql file in called by a stored procedure?
Thanks,
John> Any idea why?
It can possibly be because the two environments has different default settings for some SET options,
resulting in different execution plans, or even things like index on views not being used from OSQL.
I'd start by comparing execution plans.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"john conwell" <johnconwell@.discussions.microsoft.com> wrote in message
news:D3232F62-F547-4C96-B9C8-3DE5DAC2FC68@.microsoft.com...
>I have a large sql script file. If I open it in Query Analyzer and run it,
> it takes about 15 seconds to run. If i run the same file via osql with the
> -i argument to specify the sql file, it takes several minutes to run.
> Any idea why? Is there a better way to do this?
> What i'm really trying to do is execute the sql in a file, from a stored
> procedure. So in this instance i'm calling xp_cmdshell to kick off the osql.
> like this:
> EXEC ('xp_cmdshell ''osql -E -n -S myServer -d ' + @.newDbName + ' -i
> C:\Dev\Current\SQL\DBSchema.sql''')
> Is there a better way to execute a sql file in called by a stored procedure?
> Thanks,
> John

Run large sql script via osql takes loooong time

I have a large sql script file. If I open it in Query Analyzer and run it,
it takes about 15 seconds to run. If i run the same file via osql with the
-i argument to specify the sql file, it takes several minutes to run.
Any idea why? Is there a better way to do this?
What i'm really trying to do is execute the sql in a file, from a stored
procedure. So in this instance i'm calling xp_cmdshell to kick off the osql
.
like this:
EXEC ('xp_cmdshell ''osql -E -n -S myServer -d ' + @.newDbName + ' -i
C:\Dev\Current\SQL\DBSchema.sql''')
Is there a better way to execute a sql file in called by a stored procedure?
Thanks,
John> Any idea why?
It can possibly be because the two environments has different default settin
gs for some SET options,
resulting in different execution plans, or even things like index on views n
ot being used from OSQL.
I'd start by comparing execution plans.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"john conwell" <johnconwell@.discussions.microsoft.com> wrote in message
news:D3232F62-F547-4C96-B9C8-3DE5DAC2FC68@.microsoft.com...
>I have a large sql script file. If I open it in Query Analyzer and run it,
> it takes about 15 seconds to run. If i run the same file via osql with th
e
> -i argument to specify the sql file, it takes several minutes to run.
> Any idea why? Is there a better way to do this?
> What i'm really trying to do is execute the sql in a file, from a stored
> procedure. So in this instance i'm calling xp_cmdshell to kick off the os
ql.
> like this:
> EXEC ('xp_cmdshell ''osql -E -n -S myServer -d ' + @.newDbName + ' -i
> C:\Dev\Current\SQL\DBSchema.sql''')
> Is there a better way to execute a sql file in called by a stored procedur
e?
> Thanks,
> John