Running a stored procedure in Query Analyzer vs ADO - Execution Di

I am running SQL Server 2000 SP3. I have a stored procedure that I call from
a VB 6.0 Component using ADO/OLEDB. I turn on SQL profiler and see that the
stored proc executes in 22 seconds. I take the stored procedure call straight
out of profiler and paste it into SQL Query Analyzer and run the stored
procedure. It executes in Query Analyzer in 4 seconds. When I look at the
execution plan, Query Analyzer uses a different plan (more efficient) than
the plan used when the stored procedure is executed through ADO. How is this
possible? What am I doing wrong with ADO? How come the stored procedure will
not execute in 4 seconds when I call it through ADO? I can consistently
duplicate this.
Todd.Danner@.wachovia.com wrote:
> I am running SQL Server 2000 SP3. I have a stored procedure that I
> call from a VB 6.0 Component using ADO/OLEDB. I turn on SQL profiler
> and see that the stored proc executes in 22 seconds. I take the
> stored procedure call straight out of profiler and paste it into SQL
> Query Analyzer and run the stored procedure. It executes in Query
> Analyzer in 4 seconds. When I look at the execution plan, Query
> Analyzer uses a different plan (more efficient) than the plan used
> when the stored procedure is executed through ADO. How is this
> possible? What am I doing wrong with ADO? How come the stored
> procedure will not execute in 4 seconds when I call it through ADO? I
> can consistently duplicate this.
How are you executing the procedure from ADO? Post the code you are
testing on ADO (all relevant code) and the SQL you use in QA..
David G.

Running a stored procedure in Query Analyzer vs ADO - Execution Di

I am running SQL Server 2000 SP3. I have a stored procedure that I call from
a VB 6.0 Component using ADO/OLEDB. I turn on SQL profiler and see that the
stored proc executes in 22 seconds. I take the stored procedure call straigh
t
out of profiler and paste it into SQL Query Analyzer and run the stored
procedure. It executes in Query Analyzer in 4 seconds. When I look at the
execution plan, Query Analyzer uses a different plan (more efficient) than
the plan used when the stored procedure is executed through ADO. How is this
possible? What am I doing wrong with ADO? How come the stored procedure will
not execute in 4 seconds when I call it through ADO? I can consistently
duplicate this.Todd.Danner@.wachovia.com wrote:
> I am running SQL Server 2000 SP3. I have a stored procedure that I
> call from a VB 6.0 Component using ADO/OLEDB. I turn on SQL profiler
> and see that the stored proc executes in 22 seconds. I take the
> stored procedure call straight out of profiler and paste it into SQL
> Query Analyzer and run the stored procedure. It executes in Query
> Analyzer in 4 seconds. When I look at the execution plan, Query
> Analyzer uses a different plan (more efficient) than the plan used
> when the stored procedure is executed through ADO. How is this
> possible? What am I doing wrong with ADO? How come the stored
> procedure will not execute in 4 seconds when I call it through ADO? I
> can consistently duplicate this.
How are you executing the procedure from ADO? Post the code you are
testing on ADO (all relevant code) and the SQL you use in QA..
David G.

Running a DLL in the Query Analyzer

Hi,

I have a DLL that I want to run from the Query Analyzer. I tried the
following:

USE master;
EXEC sp_addextendedproc BLAH, '\\Other-Server\Path\To\Blah\Blah.dll'

and I get the error:

ODBC: Msg 0, Level 16, State 1
Cannot load the DLL \\Other-Server\Path\To\Blah\Blah.dll, or one of
the DLLs it references. Reason: 126(The specified module could not be
found.).

The DLL was written in Cobol. The "Other-Server" in the Path above is
the server that Cobol (and the DLL) is located on.

I looked at the dependencies for the DLL and it includes (not
surprisingly) Cobol Dll's. It should find those on the other server.

What can I do?

Thanks!One other thing...

The DLL can be called from Visual Basic. That was installed some time
ago and works great.

On Nov 23, 9:15 am, Jim <jsh...@.datamann.comwrote:

Quote:

Originally Posted by

Hi,
>
I have a DLL that I want to run from the Query Analyzer. I tried the
following:
>
USE master;
EXEC sp_addextendedproc BLAH, '\\Other-Server\Path\To\Blah\Blah.dll'
>
and I get the error:
>
ODBC: Msg 0, Level 16, State 1
Cannot load the DLL \\Other-Server\Path\To\Blah\Blah.dll, or one of
the DLLs it references. Reason: 126(The specified module could not be
found.).
>
The DLL was written in Cobol. The "Other-Server" in the Path above is
the server that Cobol (and the DLL) is located on.
>
I looked at the dependencies for the DLL and it includes (not
surprisingly) Cobol Dll's. It should find those on the other server.
>
What can I do?
>
Thanks!

|||The DLL was written in Cobol. The "Other-Server" in the Path above is

Quote:

Originally Posted by

the server that Cobol (and the DLL) is located on.

Not just any DLL can run as a SQL Server extended stored procedure.
Extended stored procedures are usually written in C/C++, although I've heard
that some have been written using Delphi. See Creating Extended Stored
Procedures in the Books Online for details.

Separately, COM DLLs can be invoked using sp_OA* procs.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Jim" <jshain@.datamann.comwrote in message
news:8d9db5fa-a277-400f-9761-7886f9607c55@.g21g2000hsh.googlegroups.com...

Quote:

Originally Posted by

>
Hi,
>
I have a DLL that I want to run from the Query Analyzer. I tried the
following:
>
USE master;
EXEC sp_addextendedproc BLAH, '\\Other-Server\Path\To\Blah\Blah.dll'
>
and I get the error:
>
ODBC: Msg 0, Level 16, State 1
Cannot load the DLL \\Other-Server\Path\To\Blah\Blah.dll, or one of
the DLLs it references. Reason: 126(The specified module could not be
found.).
>
The DLL was written in Cobol. The "Other-Server" in the Path above is
the server that Cobol (and the DLL) is located on.
>
I looked at the dependencies for the DLL and it includes (not
surprisingly) Cobol Dll's. It should find those on the other server.
>
What can I do?
>
Thanks!
>
>
>
>

|||Since I can call it from a VB program, do you know if I can create a
new DLL using the VB program and use that to access the needed DLL?
If not... If I can call my needed DLL from a C/C++ program, can I
create a new DLL that I could call to get at my needed DLL?

The DLL that I need to call, was bought from a different company, so I
don't have access to the code.

Thanks alot!

Jim

On Nov 24, 9:52 am, "Dan Guzman" <guzma...@.nospam-
online.sbcglobal.netwrote:

Quote:

Originally Posted by

Quote:

Originally Posted by

The DLL was written in Cobol. The "Other-Server" in the Path above is
the server that Cobol (and the DLL) is located on.

>
Not just any DLL can run as a SQL Server extended stored procedure.
Extended stored procedures are usually written in C/C++, although I've heard
that some have been written using Delphi. See Creating Extended Stored
Procedures in the Books Online for details.
>
Separately, COM DLLs can be invoked using sp_OA* procs.
>
--
Hope this helps.
>
Dan Guzman
SQL Server MVP
>
"Jim" <jsh...@.datamann.comwrote in message
>
news:8d9db5fa-a277-400f-9761-7886f9607c55@.g21g2000hsh.googlegroups.com...
>
>
>
>
>

Quote:

Originally Posted by

Hi,

>

Quote:

Originally Posted by

I have a DLL that I want to run from the Query Analyzer. I tried the
following:

>

Quote:

Originally Posted by

USE master;
EXEC sp_addextendedproc BLAH, '\\Other-Server\Path\To\Blah\Blah.dll'

>

Quote:

Originally Posted by

and I get the error:

>

Quote:

Originally Posted by

ODBC: Msg 0, Level 16, State 1
Cannot load the DLL \\Other-Server\Path\To\Blah\Blah.dll, or one of
the DLLs it references. Reason: 126(The specified module could not be
found.).

>

Quote:

Originally Posted by

The DLL was written in Cobol. The "Other-Server" in the Path above is
the server that Cobol (and the DLL) is located on.

>

Quote:

Originally Posted by

I looked at the dependencies for the DLL and it includes (not
surprisingly) Cobol Dll's. It should find those on the other server.

>

Quote:

Originally Posted by

What can I do?

>

Quote:

Originally Posted by

Thanks!- Hide quoted text -

>
- Show quoted text -

|||(jimshain@.gmail.com) writes:

Quote:

Originally Posted by

Since I can call it from a VB program, do you know if I can create a
new DLL using the VB program and use that to access the needed DLL?
If not... If I can call my needed DLL from a C/C++ program, can I
create a new DLL that I could call to get at my needed DLL?
>
The DLL that I need to call, was bought from a different company, so I
don't have access to the code.

Yes, you could write one or more extended stored procedures to access
the DLL. Or implement COM methods.

On SQL 2005, you could to a COM-interop and call it from CLR.

Whatever, it's a bit of work, and and if you have errors in your
code that causes access violation, you could crash SQL Server.

To get information about writing extended stored procedures, please
see in Books Online for details. If you have never done it before,
expect at least 50 hours of development.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thanks for the pointers!

Jim

On Nov 26, 5:35 pm, Erland Sommarskog <esq...@.sommarskog.sewrote:

Quote:

Originally Posted by

(jimsh...@.gmail.com) writes:

Quote:

Originally Posted by

Since I can call it from a VB program, do you know if I can create a
new DLL using the VB program and use that to access the needed DLL?
If not... If I can call my needed DLL from a C/C++ program, can I
create a new DLL that I could call to get at my needed DLL?

>

Quote:

Originally Posted by

The DLL that I need to call, was bought from a different company, so I
don't have access to the code.

>
Yes, you could write one or more extended stored procedures to access
the DLL. Or implement COM methods.
>
On SQL 2005, you could to a COM-interop and call it from CLR.
>
Whatever, it's a bit of work, and and if you have errors in your
code that causes access violation, you could crash SQL Server.
>
To get information about writing extended stored procedures, please
see in Books Online for details. If you have never done it before,
expect at least 50 hours of development.
>
--
Erland Sommarskog, SQL Server MVP, esq...@.sommarskog.se
>
Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Running a Batch File in Query Analyzer

Hi,
I have to run a batch file in query analyzer. How do I do that.
I don't want to use 'xp_cmdshell'
Please advise
(An example would be great)
Regards
IT Dev
File->Open, browse and find the file, and when it is open hit F5...
If you're looking for an non-interactive way to run a file, you might check
out the OSQL command-line tool that ships with SQL Server. It is documented
in Books Online.

Adam Machanic
SQL Server MVP
Author, "Expert SQL Server 2005 Development"
http://www.apress.com/book/bookDisplay.html?bID=10220
"IT Developer" <ITDeveloper@.discussions.microsoft.com> wrote in message
news:7DE905C4-EF0B-45CB-B109-6E63894D38BA@.microsoft.com...
> Hi,
> I have to run a batch file in query analyzer. How do I do that.
> I don't want to use 'xp_cmdshell'
>
> Please advise
> (An example would be great)
> Regards
> IT Dev
|||IT Developer wrote:

> I have to run a batch file in query analyzer. How do I do that.
> I don't want to use 'xp_cmdshell'
Why don't you?
|||Hello,
You will have to use XP_CMDSHELL if you need to execute the .BAT from Query
Analyzer. There is no other alternative.
Thanks
Hari
"IT Developer" <ITDeveloper@.discussions.microsoft.com> wrote in message
news:7DE905C4-EF0B-45CB-B109-6E63894D38BA@.microsoft.com...
> Hi,
> I have to run a batch file in query analyzer. How do I do that.
> I don't want to use 'xp_cmdshell'
>
> Please advise
> (An example would be great)
> Regards
> IT Dev

Running a Batch File in Query Analyzer

Hi,
I have to run a batch file in query analyzer. How do I do that.
I don't want to use 'xp_cmdshell'
Please advise
(An example would be great)
Regards
IT Dev
I answered this question in the microsoft.public.sqlserver.programming.
Please don't post the same question independently to different groups
because that causes duplicate effort among the parties involved. You can
instead post the question once to all relevant groups so that the all
responses are tracked as a single thread.
Hope this helps.
Dan Guzman
SQL Server MVP
"IT Developer" <ITDeveloper@.discussions.microsoft.com> wrote in message
news:5D65C0C7-EABB-4A33-8C24-118C3CAC08F7@.microsoft.com...
> Hi,
> I have to run a batch file in query analyzer. How do I do that.
> I don't want to use 'xp_cmdshell'
>
> Please advise
> (An example would be great)
> Regards
> IT Dev
>

Running a Batch File in Query Analyzer

Hi,
I have to run a batch file in query analyzer. How do I do that.
I don't want to use 'xp_cmdshell'
Please advise
(An example would be great)
Regards
IT DevI answered this question in the microsoft.public.sqlserver.programming.
Please don't post the same question independently to different groups
because that causes duplicate effort among the parties involved. You can
instead post the question once to all relevant groups so that the all
responses are tracked as a single thread.
Hope this helps.
Dan Guzman
SQL Server MVP
"IT Developer" <ITDeveloper@.discussions.microsoft.com> wrote in message
news:5D65C0C7-EABB-4A33-8C24-118C3CAC08F7@.microsoft.com...
> Hi,
> I have to run a batch file in query analyzer. How do I do that.
> I don't want to use 'xp_cmdshell'
>
> Please advise
> (An example would be great)
> Regards
> IT Dev
>

Running a Batch File in Query Analyzer

Hi,
I have to run a batch file in query analyzer. How do I do that.
I don't want to use 'xp_cmdshell'
Please advise
(An example would be great)
Regards
IT DevI answered this question in the microsoft.public.sqlserver.programming.
Please don't post the same question independently to different groups
because that causes duplicate effort among the parties involved. You can
instead post the question once to all relevant groups so that the all
responses are tracked as a single thread.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"IT Developer" <ITDeveloper@.discussions.microsoft.com> wrote in message
news:5D65C0C7-EABB-4A33-8C24-118C3CAC08F7@.microsoft.com...
> Hi,
> I have to run a batch file in query analyzer. How do I do that.
> I don't want to use 'xp_cmdshell'
>
> Please advise
> (An example would be great)
> Regards
> IT Dev
>

Running .Sql Files in a batch

Hi,
I have got multiple .sql files(text files) having T-SQL Statements. I want
to run them all together from query analyzer...how can i do that...
Your help will be appreciatedhI
Let me say you have a file as
Use Pubs
GO
CREATE PROCEDURE spMyProc
AS
SELECT * FROM Titles
--Run this command in QA
EXEC master..xp_cmdshell 'osql.exe -S SERVERNAME -U sa -P pass -i
"D:\ORD1.sql"'
"kailux4" <kailux4@.discussions.microsoft.com> wrote in message
news:067CC39F-41E2-4170-B132-927FDB458C9C@.microsoft.com...
> Hi,
> I have got multiple .sql files(text files) having T-SQL Statements. I want
> to run them all together from query analyzer...how can i do that...
> Your help will be appreciated|||"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:eS%23FL1oaFHA.2900@.TK2MSFTNGP15.phx.gbl...
> hI
> Let me say you have a file as
> Use Pubs
> GO
> CREATE PROCEDURE spMyProc
> AS
> SELECT * FROM Titles
> --Run this command in QA
> EXEC master..xp_cmdshell 'osql.exe -S SERVERNAME -U sa -P pass -i
> "D:\ORD1.sql"'
But bear in mind that "D:\" is a drive on the __server__, not on the client.
Regards
Wojtek|||"kailux4" <kailux4@.discussions.microsoft.com> wrote in message
news:067CC39F-41E2-4170-B132-927FDB458C9C@.microsoft.com...
> Hi,
> I have got multiple .sql files(text files) having T-SQL Statements. I want
> to run them all together from query analyzer...how can i do that...
> Your help will be appreciated
Why from QA? Why not write a batch and use isql.exe within?
Regards
Wojtek|||Hi,
I recommend you to run the .SQL files from command prompt using OSQL
redirecting the output to a log file.
OSQL -SSERVER_NAME -Uuser -Ppassword -d db_name -i file_name.sql >
output.log
OSQL -SSERVER_NAME -Uuser -Ppassword -d db_name -i file_name1.sql >>
output.log
OSQL -SSERVER_NAME -Uuser -Ppassword -d db_name -i file_name2.sql >>
output.log
Thanks
Hari
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:eS%23FL1oaFHA.2900@.TK2MSFTNGP15.phx.gbl...
> hI
> Let me say you have a file as
> Use Pubs
> GO
> CREATE PROCEDURE spMyProc
> AS
> SELECT * FROM Titles
> --Run this command in QA
> EXEC master..xp_cmdshell 'osql.exe -S SERVERNAME -U sa -P pass -i
> "D:\ORD1.sql"'
>
> "kailux4" <kailux4@.discussions.microsoft.com> wrote in message
> news:067CC39F-41E2-4170-B132-927FDB458C9C@.microsoft.com...
>> Hi,
>> I have got multiple .sql files(text files) having T-SQL Statements. I
>> want
>> to run them all together from query analyzer...how can i do that...
>> Your help will be appreciated
>

Running .Sql Files in a batch

Hi,
I have got multiple .sql files(text files) having T-SQL Statements. I want
to run them all together from query analyzer...how can i do that...
Your help will be appreciated
hI
Let me say you have a file as
Use Pubs
GO
CREATE PROCEDURE spMyProc
AS
SELECT * FROM Titles
--Run this command in QA
EXEC master..xp_cmdshell 'osql.exe -S SERVERNAME -U sa -P pass -i
"D:\ORD1.sql"'
"kailux4" <kailux4@.discussions.microsoft.com> wrote in message
news:067CC39F-41E2-4170-B132-927FDB458C9C@.microsoft.com...
> Hi,
> I have got multiple .sql files(text files) having T-SQL Statements. I want
> to run them all together from query analyzer...how can i do that...
> Your help will be appreciated
|||"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:eS%23FL1oaFHA.2900@.TK2MSFTNGP15.phx.gbl...
> hI
> Let me say you have a file as
> Use Pubs
> GO
> CREATE PROCEDURE spMyProc
> AS
> SELECT * FROM Titles
> --Run this command in QA
> EXEC master..xp_cmdshell 'osql.exe -S SERVERNAME -U sa -P pass -i
> "D:\ORD1.sql"'
But bear in mind that "D:\" is a drive on the __server__, not on the client.
Regards
Wojtek
|||"kailux4" <kailux4@.discussions.microsoft.com> wrote in message
news:067CC39F-41E2-4170-B132-927FDB458C9C@.microsoft.com...
> Hi,
> I have got multiple .sql files(text files) having T-SQL Statements. I want
> to run them all together from query analyzer...how can i do that...
> Your help will be appreciated
Why from QA? Why not write a batch and use isql.exe within?
Regards
Wojtek
|||Hi,
I recommend you to run the .SQL files from command prompt using OSQL
redirecting the output to a log file.
OSQL -SSERVER_NAME -Uuser -Ppassword -d db_name -i file_name.sql >
output.log
OSQL -SSERVER_NAME -Uuser -Ppassword -d db_name -i file_name1.sql >>
output.log
OSQL -SSERVER_NAME -Uuser -Ppassword -d db_name -i file_name2.sql >>
output.log
Thanks
Hari
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:eS%23FL1oaFHA.2900@.TK2MSFTNGP15.phx.gbl...
> hI
> Let me say you have a file as
> Use Pubs
> GO
> CREATE PROCEDURE spMyProc
> AS
> SELECT * FROM Titles
> --Run this command in QA
> EXEC master..xp_cmdshell 'osql.exe -S SERVERNAME -U sa -P pass -i
> "D:\ORD1.sql"'
>
> "kailux4" <kailux4@.discussions.microsoft.com> wrote in message
> news:067CC39F-41E2-4170-B132-927FDB458C9C@.microsoft.com...
>
sql

Running .Sql Files in a batch

Hi,
I have got multiple .sql files(text files) having T-SQL Statements. I want
to run them all together from query analyzer...how can i do that...
Your help will be appreciatedhI
Let me say you have a file as
Use Pubs
GO
CREATE PROCEDURE spMyProc
AS
SELECT * FROM Titles
--Run this command in QA
EXEC master..xp_cmdshell 'osql.exe -S SERVERNAME -U sa -P pass -i
"D:\ORD1.sql"'
"kailux4" <kailux4@.discussions.microsoft.com> wrote in message
news:067CC39F-41E2-4170-B132-927FDB458C9C@.microsoft.com...
> Hi,
> I have got multiple .sql files(text files) having T-SQL Statements. I want
> to run them all together from query analyzer...how can i do that...
> Your help will be appreciated|||"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:eS%23FL1oaFHA.2900@.TK2MSFTNGP15.phx.gbl...
> hI
> Let me say you have a file as
> Use Pubs
> GO
> CREATE PROCEDURE spMyProc
> AS
> SELECT * FROM Titles
> --Run this command in QA
> EXEC master..xp_cmdshell 'osql.exe -S SERVERNAME -U sa -P pass -i
> "D:\ORD1.sql"'
But bear in mind that "D:\" is a drive on the __server__, not on the client.
Regards
Wojtek|||"kailux4" <kailux4@.discussions.microsoft.com> wrote in message
news:067CC39F-41E2-4170-B132-927FDB458C9C@.microsoft.com...
> Hi,
> I have got multiple .sql files(text files) having T-SQL Statements. I want
> to run them all together from query analyzer...how can i do that...
> Your help will be appreciated
Why from QA? Why not write a batch and use isql.exe within?
Regards
Wojtek|||Hi,
I recommend you to run the .SQL files from command prompt using OSQL
redirecting the output to a log file.
OSQL -SSERVER_NAME -Uuser -Ppassword -d db_name -i file_name.sql >
output.log
OSQL -SSERVER_NAME -Uuser -Ppassword -d db_name -i file_name1.sql >>
output.log
OSQL -SSERVER_NAME -Uuser -Ppassword -d db_name -i file_name2.sql >>
output.log
Thanks
Hari
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:eS%23FL1oaFHA.2900@.TK2MSFTNGP15.phx.gbl...
> hI
> Let me say you have a file as
> Use Pubs
> GO
> CREATE PROCEDURE spMyProc
> AS
> SELECT * FROM Titles
> --Run this command in QA
> EXEC master..xp_cmdshell 'osql.exe -S SERVERNAME -U sa -P pass -i
> "D:\ORD1.sql"'
>
> "kailux4" <kailux4@.discussions.microsoft.com> wrote in message
> news:067CC39F-41E2-4170-B132-927FDB458C9C@.microsoft.com...
>

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

run an external file

I am using sql server 2000 sp3a.
How do I start (run) an external file. ie, run a vbs or exe fro Query
Analyzer or from within a job step.
Thanks,
YanivWithin a jobstep you can choose that the type of job step is "Operation
System command", put your command in here. Another option would be tu
sue the procedure xp_smdshell which call call the commadn interpreter,
therefore you have to choose TSQL Command execution in the job step.
HTH, jens Suessmeyer.|||Run vbs file
EXEC master..xp_cmdshell 'cscript /nologo c:\file.vbs "stringarg" 5'
> sue the procedure xp_smdshell which call call the commadn interpreter,
xp_cmdshell ? :-)))))))))
"Jens" <Jens@.sqlserver2005.de> wrote in message
news:1138186195.161343.91450@.g49g2000cwa.googlegroups.com...
> Within a jobstep you can choose that the type of job step is "Operation
> System command", put your command in here. Another option would be tu
> sue the procedure xp_smdshell which call call the commadn interpreter,
> therefore you have to choose TSQL Command execution in the job step.
> HTH, jens Suessmeyer.
>|||Thank you both.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23YmvfNaIGHA.1424@.TK2MSFTNGP12.phx.gbl...
> Run vbs file
> EXEC master..xp_cmdshell 'cscript /nologo c:\file.vbs "stringarg" 5'
>> sue the procedure xp_smdshell which call call the commadn interpreter,
> xp_cmdshell ? :-)))))))))
>
> "Jens" <Jens@.sqlserver2005.de> wrote in message
> news:1138186195.161343.91450@.g49g2000cwa.googlegroups.com...
>> Within a jobstep you can choose that the type of job step is "Operation
>> System command", put your command in here. Another option would be tu
>> sue the procedure xp_smdshell which call call the commadn interpreter,
>> therefore you have to choose TSQL Command execution in the job step.
>> HTH, jens Suessmeyer.
>

Run a stored proc thru the index tuning wizard?

Is there any way to run the index tuning wizard on a stored proc, other than
copying the content of the stored proc into sql query analyzer?
I tried highlighting a sql call to the sp, and running itw on that along
with all tables in the db, but it just said it couldn't find anything to
add. And I know that is not the right answer.
Thanks in advance!Hi Derrick
"Derrick" wrote:
> Is there any way to run the index tuning wizard on a stored proc, other than
> copying the content of the stored proc into sql query analyzer?
> I tried highlighting a sql call to the sp, and running itw on that along
> with all tables in the db, but it just said it couldn't find anything to
> add. And I know that is not the right answer.
> Thanks in advance!
>
If your stored procedure uses temporary tables then ITW will not be able to
tune it.
John