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 Oracle Stored Procedure via SQL Server 2000 Linked Serve

All,
I am trying to execute a simple Oracle stored procedure from SQL Server
2000. The SP has no input or output parameters. I have created the linked
server correctly as I am able to execute Oracle functions just fine. I have
tried the following two methods to execute the SP, SKELETON:
{Call ORADB..MARCO.SKELETON()}
Execute ORADB..MARCO.SKELETON
Both methods result in the same error listed below:
Server: Msg 7212, Level 17, State 1, Line 1
Could not execute procedure 'SKELETON' on remote server 'ORADB'.
[OLE/DB provider returned message: One or more errors occurred during
processing of command.]
[OLE/DB provider returned message: Syntax error in {call...} ODBC Escape.]
Any ideas on how I can get this working properly?
Thanks,
MarcoHere is an old post by Umachandar. See if it helps:
http://tinyurl.com/7dxrr
-oj
"marco" <marco@.discussions.microsoft.com> wrote in message
news:792C4015-008A-420A-B307-EAFC6BAC49EE@.microsoft.com...
> All,
> I am trying to execute a simple Oracle stored procedure from SQL Server
> 2000. The SP has no input or output parameters. I have created the
> linked
> server correctly as I am able to execute Oracle functions just fine. I
> have
> tried the following two methods to execute the SP, SKELETON:
> {Call ORADB..MARCO.SKELETON()}
> Execute ORADB..MARCO.SKELETON
> Both methods result in the same error listed below:
> Server: Msg 7212, Level 17, State 1, Line 1
> Could not execute procedure 'SKELETON' on remote server 'ORADB'.
> [OLE/DB provider returned message: One or more errors occurred during
> processing of command.]
> [OLE/DB provider returned message: Syntax error in {call...} ODBC Escape.]
>
> Any ideas on how I can get this working properly?
> Thanks,
> Marco
>