Executing SP in MSSQL takes forever via JDBC?!

Hi all,
I need to execute a stored procedure in our database server, MS SQL Server
2000 and it takes forever...
I'm using Microsoft SQL Server 2000 Driver for JDBC Version 2.2.0037
My code:
Class.forName("com.microsoft.jdbc.sqlserver.SQLSer verDriver");
Connection conn = DriverManager.getConnection
("jdbc:microsoft:sqlserver://myhost:1433;DatabaseName=MYDB;User=me;Password=sec ret;SendStringParametersAsUnicode=false");
CallableStatement cs = conn.prepareCall("{call my_sp(?,?)}");
cs.setString(1,"param1");
cs.setString(2,"param2");
long start = System.currentTimeMillis();
ResultSet rs = cs.executeQuery();
System.out.println("exec time: " + (System.currentTimeMillis() - start) + "
ms");
...
Every time I execute this piece of code, it takes between 45-50 seconds...
I have tried to execute my SP from DBVisualizer(an app using the same JDBC
driver) and it is the same result. But, when I execute my SP from Query
Analyzer it takes less than a second?! I have also tried executing the SP
from TOAD for SQL Server, and then the execution time also is less than a
second. Why? What have I missed? Does anybody recognize this problem?
It is not me personally that has wriiten the SP, so I do not really know
what it does and how it looks, but I know that the SP I'm calling is calling
another SP that is creating a couple of temporary tables while it is
executing...
Greatfull for any suggestions...
Cheers
//Anders =)
anders.hedstrom wrote:

> Hi all,
> I need to execute a stored procedure in our database server, MS SQL Server
> 2000 and it takes forever...
> I'm using Microsoft SQL Server 2000 Driver for JDBC Version 2.2.0037
> My code:
> Class.forName("com.microsoft.jdbc.sqlserver.SQLSer verDriver");
> Connection conn = DriverManager.getConnection
> ("jdbc:microsoft:sqlserver://myhost:1433;DatabaseName=MYDB;User=me;Password=sec ret;SendStringParametersAsUnicode=false");
>
> CallableStatement cs = conn.prepareCall("{call my_sp(?,?)}");
> cs.setString(1,"param1");
> cs.setString(2,"param2");
Try these two things:
1 - Change the URL to "sendStringParametersAsUnicode". Note the initial lowercase 's'.
2 -
Statement s = conn.createStatement();
ResultSet r = s.executeQuery("exec my_sp " + param1 + ", " + param2 );
Joe Weinstein at BEA

> long start = System.currentTimeMillis();
> ResultSet rs = cs.executeQuery();
> System.out.println("exec time: " + (System.currentTimeMillis() - start) + "
> ms");
> ...
>
> Every time I execute this piece of code, it takes between 45-50 seconds...
> I have tried to execute my SP from DBVisualizer(an app using the same JDBC
> driver) and it is the same result. But, when I execute my SP from Query
> Analyzer it takes less than a second?! I have also tried executing the SP
> from TOAD for SQL Server, and then the execution time also is less than a
> second. Why? What have I missed? Does anybody recognize this problem?
> It is not me personally that has wriiten the SP, so I do not really know
> what it does and how it looks, but I know that the SP I'm calling is calling
> another SP that is creating a couple of temporary tables while it is
> executing...
> Greatfull for any suggestions...
> Cheers
> //Anders =)
|||
> Try these two things:
> 1 - Change the URL to "sendStringParametersAsUnicode". Note the initial lowercase 's'.
> 2 -
> Statement s = conn.createStatement();
> ResultSet r = s.executeQuery("exec my_sp " + param1 + ", " + param2 );
> Joe Weinstein at BEA
Hi Joe,
thanx alot for your tip! This fixed my problem;
> Statement s = conn.createStatement();
> ResultSet r = s.executeQuery("exec my_sp " + param1 + ", " + param2 );
It would be quite interesting to know why it takes so long to execute the SP
when using CallableStatement and PreparedStatement...
By the way, the lowercase 's' on sendStringParametersAsUnicode made no
difference.
Once again Joe, thanx!!!
Cheers
//Anders =)

Executing batch script from MSSQL

If I have an application running on MSSQL is it possible that if an action occurs on the application side that a stored procedure can execute a batch script?xp_cmdshell. requires certains permissions. it is likely whatever programming language you are using, also has a command for command line and it would probably be better to do it from the application instead of the database.|||OK, so probably since the application uses asp I could call a script from this side, right?|||OK, so probably since the application uses asp I could call a script from this side, right?

ASP has it's own little peculiar security issue. The package has to be executed from the security context of the IUSR_XXXX NT account that executes the pages on the web server. If this is'nt done right you can create a security problem.

Do a search on this forum using ASP and DTS as terms.|||I suggest avoiding xp_CmdShell - outta process can mean inna trouble.

What you want to do is a bit quirky, and there may be a better solution if you were to provide more details (for example, why are you seeking a synchronous solution? and what exactly is that batch script doing?).

Outta band is outta sight!

execute proc on stopping mssql server

How execute extended proc on stopping ms sql server (it mean before
stoping).
Is it possible to handle this?
Or is in system proc a'la sp_on_stop?

ThxIndrek Mgi (mkkk@.hotmail.com) writes:
> How execute extended proc on stopping ms sql server (it mean before
> stoping).
> Is it possible to handle this?
> Or is in system proc a'la sp_on_stop?

KILL on the spid in question is the only way I can think of. Short of
rebooting the server.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Try this,

--Email when database is started

CREATE procedure email

as

exec sp_sendSMTPmail 'test@.test.com.au', 'Database Rebooted',
@.cc='', @.BCC = '',
@.Importance=1,
@.Attachments='', @.HTMLFormat = 0,@.From =
'test@.test.com.au'

GO
exec sp_procoption N'email', N'startup', N'true'
GO

Thanks,

"Indrek Mgi" <mkkk@.hotmail.com> wrote in message
news:4215cb43_2@.news.estpak.ee...
> How execute extended proc on stopping ms sql server (it mean before
> stoping).
> Is it possible to handle this?
> Or is in system proc a'la sp_on_stop?
> Thx