JDBC Driver issue: driver

Showing posts with label driver. Show all posts

Friday, February 24, 2012

Jet ODBC Driver for Text files - Permissions Issue

Hi,
I have setup a linked server to a text file uing the MS Jet ODBC driver.
Users that are not sysadmins and local admins on the server that the file
resides get an Jet Initialize error message.
What are the permissions required for SQL Server users to gain access to
this file?
Thanks.
Arun,
What happens if you run OPENDATASOURCE (See Books Online) against that
provider for the non-admin user account in Query Analyzer? You could also
enable trace flag 7300.
In Query Analyzer you would type something like:
DBCC TRACEON(7300)
go
SELECT * FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',...
Please provide exact error message.
Regards,
James

Jet ODBC Driver for Text files - Permissions Issue

Hi,
I have setup a linked server to a text file uing the MS Jet ODBC driver.
Users that are not sysadmins and local admins on the server that the file
resides get an Jet Initialize error message.
What are the permissions required for SQL Server users to gain access to
this file?
Thanks.Arun,
What happens if you run OPENDATASOURCE (See Books Online) against that
provider for the non-admin user account in Query Analyzer? You could also
enable trace flag 7300.
In Query Analyzer you would type something like:
DBCC TRACEON(7300)
go
SELECT * FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',...
Please provide exact error message.
Regards,
James

JDBC: Retrieving stored procedures column information returns "-9" data type for nvarc

Hello,

I'm using the latest JDBC driver for SQL server 2005 (1.0.809.102).

Retrieving stored procedures column information returns "-9" data type for nvarchar:

public class driver {
public static void main(String[] args) throws java.lang.Throwable {
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver").newInstance();

java.util.Properties properties = new java.util.Properties ();
properties.put("username", "sa");
properties.put("password", "sa");
properties.put("database", "GHTDB");

Connection con = DriverManager.getConnection("jdbc:sqlserver://POWERGH", properties);

ResultSet rs = con.getMetaData().getProcedureColumns("GHTDB", null, "SalesByCategory", null);

while (rs.next()) {
int i = rs.getInt("DATA_TYPE");
String s = rs.getString("TYPE_NAME");

System.out.println(i); // prints -9 for nvarchar
System.out.println(s);
}

}
}

Is this behavior documented anywhere and is by design?

Any help will be appreciated,
Regards,
Kosta

Kosta:

java.sql.Types has no value for any of the unicode text types so we return the SQL Server value for nvarchar (-9) rather than mis-reporting the type as a non-unicode varchar (12).

Both options here are bad, although, returning 12 in this case has the advantage of being wrong in the company of most other JDBC drivers, including our SQL Server 2000 JDBC driver.

If you think we should fix this, please file a bug at the product feedback center (http://lab.msdn.microsoft.com/productfeedback/) and we'll revisit the issue and make a change or doc it.

-shelby

Shelby Goerlitz

SQL Server Data Programmability -- JDBC

JDBC: DatabaseMetaData.getURL() returns jdbc:sqljdbc://

Hi,

I'm using:

Microsoft SQL Server 9.00.1399
Microsoft SQL Server 2005 JDBC Driver 1.0.809.102

Executing the DatabaseMetaData.getURL() method when database is connected returns "jdbc:sqljdbc://". The documentation for getURL() specifies that this URL is returned only when accessing pre SQL Server 2005 versions while when connected to 2005 it should return the correct URL
(ex: jdbc:sqlserver://192.168.1.105:1223;databaseName=test)

My question is why I don't get the correct URL as I connect to a 2005 database?

(Another related question is why a proper URL is not returned even when connected to a pre 2005 database?)

Regards

Roger

Minq Software
Author of DbVisualizer (http://www.dbvis.com/products/dbvis)

I don't know where did you get the documentation saying that getURL() will return more than "jdbc:sqljdbc://" for SQL Server 2005. According to our spec, it is the correct behavior.

Thanks

Bei

|||I read the following:

http://msdn2.microsoft.com/en-us/library/ms379009.aspx

But you're right and I was wrong that this behavior includes SQL Server 2005 as well.

Big question then is why it returns "jdbc:sqljdbc://"? Why does it return jdbc:sqljdbc to start with and secondly why doesn't it return the complete URL as specified at connect? (Beta versions of the driver returned the URL correctly (as I see it) but using the latest driver it works as above).

Regards

Roger
|||

It is for security reason. We don't want to leak sensitive information.

Thanks
Bei

|||Bei,

> It is for security reason. We don't want to leak sensitive information.

Can you please explain what security reasons there might be? We've tried JDBC drivers from 20+ vendors for all major databases and all of them returns the URL as it was specified during connect.

Second question is why jdbc:sqljdbc:// was choosed over the more intuitive jdbc:sqlserver://?

Regards

Roger

|||

Roger,

The fact that it is returning "jdbc:sqljdbc://" is obviously a bug, I have filed this internally but if you want to you can file it directly under http://lab.msdn.microsoft.com/productfeedback/default.aspx

The reasoning behind returning something other than the URL that the user passed in boils down to two issues.

1) We can't return password information with this method. This is considered a security bug and would not pass internal Microsoft security reviews. This may sound strange but there are good reasons behind it and it is consistent with all the drivers microsoft has shipped recently.

2) Here are some of the ways in which you can create a connection:

DriverManager.getConnection(url)
DriverManager.getConnection(url, info)
DriverManager.getConnection(url, user, password)
DataSource.getConnection()
DataSource.getConnection(username, password)

What would you expect getUrl to return in each of these cases? How usefull will what you are returning be without password information?

When we asked ourselves this questions it became clear that doing the work to return all values minus the password did not make a lot of sense.

If you have a scenario where having this information is important I would be happy to work with you to get this integrated with our next driver release. Sorry there is nothing I can do about the password information.

Hope this makes sense,

Angel

|||Angel,

Thanks for getting back to me.

Good to hear that you agree that the jdbc:sqljdbc:// is a bug.

When it comes to the URL in general it is supposed to return the URL, not URL + userid/password, just only the URL. If user or password is passed as arguments in the URL these should be removed. I strongly recommend that you check drivers from other vendors.

Thanks!

Regards

Roger

JDBC XA in a cluster

I want to know how to install the JDBC XA driver in a cluster and make i
cluster aware.
Thanks,
Padron
You don't. You just have to cluster MS DTC, and then Authorize XA and/or
TIP transaction types in the security dialogs that come with Win2K3 SP1.
http://support.microsoft.com/kb/899191
http://msdn2.microsoft.com/en-us/library/ms679479.aspx
http://support.microsoft.com/kb/908620
http://support.microsoft.com/kb/817066/en-us
There also seems to have been a bug in earlier 2005 releases:
http://support.microsoft.com/kb/919006/en-us
Hope this helps.
Sincerely,
Anthony Thomas

"Padron" <Padron@.discussions.microsoft.com> wrote in message
news:301CBA6C-5D1E-4C0D-8D8C-12085109E751@.microsoft.com...
> I want to know how to install the JDBC XA driver in a cluster and make i
> cluster aware.
> Thanks,
> Padron

JDBC und SQL Server

Hi,
Im very new in this topic.
And now I want to read some data from a SQL Server with Java.
But I dont know how.
I have now downloaded the new Driver form Microsoft and now I want to read
data from the SQL Server with Java.
Can someone give me a little example.
Sincerly Patrick Hennig
Hi Patrick,
Here's some sample code:
Connection connection = null;
try {
String driverName = "jdbc:microsoft:sqlserver://";
String serverName = "127.0.0.1";
String portNumber = "1433";
String myDatabaseUrl = serverName + ":" + portNumber;
String myDatabaseName = "DatabaseName=Northwind";
String url = "jdbc:microsoft:sqlserver://" + myDatabaseUrl + ";" +
myDatabaseName;
String username = "username";
String password = "password";
// Load the JDBC driver
Class.forName(driverName);
// Create a connection to the database
connection = DriverManager.getConnection(url, username, password);
Statement stmt = connection.createStatement();
ResultSet rs = stmt.executeQuery("SELECT CustomerId, CompanyName FROM
Customers");
while (rs.next()) {
System.out.println(rs.getString("CustomerId"));
System.out.println(rs.getString("CompanyName"));
}
rs.close();
} catch (ClassNotFoundException e) {
// Could not find the database driver
e.printStackTrace();
} catch (SQLException e) {
// Could not connect to the database
e.printStackTrace();
}
finally {
try {
connection.close();
} catch (SQLException e1) {
e1.printStackTrace();
}
}
A great place to find all kinds of Java sample code is here:
http://javaalmanac.com/cgi-bin/search/find.pl?words=sql
- Tim
"Patrick Hennig" <patrick_hennig@.licht-concept.de> wrote in message
news:%23mXDLUsgEHA.3944@.tk2msftngp13.phx.gbl...
> Hi,
> Im very new in this topic.
> And now I want to read some data from a SQL Server with Java.
> But I dont know how.
> I have now downloaded the new Driver form Microsoft and now I want to read
> data from the SQL Server with Java.
> Can someone give me a little example.
> Sincerly Patrick Hennig
>

JDBC Type 4 Driver for MS SQL Server 7.0

Hi everyone,

Does Websphere 4 /WSAD 5.1 has builtin Type 4 JDBC driver for Microsoft SQL server 7.0? If not, is there any *free* jdbc driver for MS SQL server 7.0, By the way, I need to use the driver to access MS SQL server 7.0 and I can't find the sutiable Type 4 JDBC driver.

DhanaCheck this out:
http://www.programming-x.com/programming-x/jdbc-driver.html

Also, Microsoft has one for 2000 that might work with 7. Not sure though.

Originally posted by vdstmx
Hi everyone,

Does Websphere 4 /WSAD 5.1 has builtin Type 4 JDBC driver for Microsoft SQL server 7.0? If not, is there any *free* jdbc driver for MS SQL server 7.0, By the way, I need to use the driver to access MS SQL server 7.0 and I can't find the sutiable Type 4 JDBC driver.

Dhana

jdbc type 4 driver error

Mr Mugundan.......

My web application is running in jdk1.4, tomcat4, ms sql 2005 in win 2003 environment. I am using jdbc type 4 driver for sql server 2005 (com.microsoft.sqlserver.jdbc.SQLServerDriver, sqljdbc_1.2)database connection this gives an error even gives error in both sqljdbc1.1 and 1.2 versions...

please see the error message...

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

tomcat server error message begin

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

1SELECT costct from costctmain where costct not in (select distinct mainct from costcts) 1
pageName : GENERAL_ERROR_PAGE
select count(*) from ( select P1.type, SUM(CASE P1.costct WHEN '11234578' THEN P1.total ELSE 0 END) AS a11234578, sum(P1.total)
w.project as type, e.costct, sum(t.tshours) as total from tswork t right outer join emp e on t.eid = e.eid join workmap w on t
ct pp on w.project = pp.pno where tsstatus in ('appr','subt') and tsupload = 'y' and e.costct in (11234578) and tsdate >= (sel
od where period = 'APR' and periodyear = 2007) group by e.costct, w.project union select PP.* from ( select t.jno as type, e.c
s total from tsjob t right outer join emp e on t.eid = e.eid where tsstatus in ('appr','subt') and tsupload = 'y' and e.costct
te >= (select startdate from period where period = 'APR' and periodyear = 2007) group by e.costct, t.jno ) as PP where type !=
y P1.type ) as P3
Error in executing query
com.microsoft.sqlserver.jdbc.SQLServerException: The IOBuffer.process operation returned an unknown packet type:-120. Index:70.
DS_DONE(-3) TDS_DONEPROC(-2) TDS_DONEINPROC(-1)
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(Unknown Source)
at com.microsoft.sqlserver.jdbc.IOBuffer.processPackets(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.buildNextRowset(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.doExecuteStatement(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerStatement$StatementExecutionRequest.executeStatement(Unknown Source)
at com.microsoft.sqlserver.jdbc.CancelableRequest.execute(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeRequest(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeQuery(Unknown Source)
at eTime.DBConnectManager.execQuery(DBConnectManager.java:198)
at reports.UtilisationReport.getData(UtilisationReport.java:333)
at reports.UtilisationReport.generateReport(UtilisationReport.java:109)
at reports.DivUtilisationReportServlet.getReportData(DivUtilisationReportServlet.java:230)
at reports.DivUtilisationReportServlet.doGet(DivUtilisationReportServlet.java:62)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:696)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:809)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:198)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:144)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:209)
at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:595)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:432)
at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:954)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:138)
at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:595)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:432)
at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:954)
at org.apache.catalina.core.StandardContext.invoke(StandardContext.java:2459)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:132)
at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:595)
at org.apache.catalina.valves.ErrorDispatcherValve.invoke(ErrorDispatcherValve.java:118)
at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:593)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:116)
at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:593)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:432)
at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:954)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:126)
at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:595)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:432)
at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:954)
at org.apache.coyote.tomcat4.CoyoteAdapter.service(CoyoteAdapter.java:152)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:869)
at org.apache.coyote.http11.Http11BaseProtocol$Http11ConnectionHandler.processConnection(Http11BaseProtocol.java:664)
at org.apache.tomcat.util.net.PoolTcpEndpoint.processSocket(PoolTcpEndpoint.java:527)
at org.apache.tomcat.util.net.LeaderFollowerWorkerThread.runIt(LeaderFollowerWorkerThread.java:80)
at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:684)
at java.lang.Thread.run(Thread.java:534)
java.lang.NullPointerException
at reports.UtilisationReport.getData(UtilisationReport.java:335)
at reports.UtilisationReport.generateReport(UtilisationReport.java:109)
at reports.DivUtilisationReportServlet.getReportData(DivUtilisationReportServlet.java:230)
at reports.DivUtilisationReportServlet.doGet(DivUtilisationReportServlet.java:62)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:696)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:809)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:198)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:144)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:209)
at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:595)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:432)
at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:954)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:138)
at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:595)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:432)
at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:954)
at org.apache.catalina.core.StandardContext.invoke(StandardContext.java:2459)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:132)
at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:595)
at org.apache.catalina.valves.ErrorDispatcherValve.invoke(ErrorDispatcherValve.java:118)
at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:593)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:116)
at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:593)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:432)
at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:954)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:126)
at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:595)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:432)
at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:954)
at org.apache.coyote.tomcat4.CoyoteAdapter.service(CoyoteAdapter.java:152)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:869)
at org.apache.coyote.http11.Http11BaseProtocol$Http11ConnectionHandler.processConnection(Http11BaseProtocol.java:664)
at org.apache.tomcat.util.net.PoolTcpEndpoint.processSocket(PoolTcpEndpoint.java:527)
at org.apache.tomcat.util.net.LeaderFollowerWorkerThread.runIt(LeaderFollowerWorkerThread.java:80)
at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:684)
at java.lang.Thread.run(Thread.java:534)
select count(*) from ( select aatype as type, SUM(CASE P2.costct WHEN '11234578'THEN P2.total ELSE 0 END) AS a11234578, SUM(P2.t
select aatype, costct, sum(tshours) as total from tsnr t join emp e on t.eid = e.eid where tsstatus in ('appr','subt') and tsup
in ('11234578') and tsdate >= (select startdate from period where period = 'APR' and periodyear = 2007) group by aatype, costct
) as P3
Error in executing query
com.microsoft.sqlserver.jdbc.SQLServerException: The IOBuffer.process operation returned an unknown packet type:-120. Index:70.
DS_DONE(-3) TDS_DONEPROC(-2) TDS_DONEINPROC(-1)
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(Unknown Source)
at com.microsoft.sqlserver.jdbc.IOBuffer.processPackets(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.buildNextRowset(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.doExecuteStatement(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerStatement$StatementExecutionRequest.executeStatement(Unknown Source)
at com.microsoft.sqlserver.jdbc.CancelableRequest.execute(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeRequest(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeQuery(Unknown Source)
at eTime.DBConnectManager.execQuery(DBConnectManager.java:198)
at reports.UtilisationReport.getData(UtilisationReport.java:333)
at reports.UtilisationReport.generateReport(UtilisationReport.java:133)
at reports.DivUtilisationReportServlet.getReportData(DivUtilisationReportServlet.java:230)
at reports.DivUtilisationReportServlet.doGet(DivUtilisationReportServlet.java:62)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:696)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:809)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:198)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:144)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:209)
at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:595)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:432)
at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:954)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:138)
at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:595)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:432)
at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:954)
at org.apache.catalina.core.StandardContext.invoke(StandardContext.java:2459)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:132)
at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:595)
at org.apache.catalina.valves.ErrorDispatcherValve.invoke(ErrorDispatcherValve.java:118)
at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:593)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:116)
at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:593)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:432)
at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:954)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:126)
at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:595)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:432)
at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:954)
at org.apache.coyote.tomcat4.CoyoteAdapter.service(CoyoteAdapter.java:152)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:869)
at org.apache.coyote.http11.Http11BaseProtocol$Http11ConnectionHandler.processConnection(Http11BaseProtocol.java:664)
at org.apache.tomcat.util.net.PoolTcpEndpoint.processSocket(PoolTcpEndpoint.java:527)
at org.apache.tomcat.util.net.LeaderFollowerWorkerThread.runIt(LeaderFollowerWorkerThread.java:80)
at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:684)
at java.lang.Thread.run(Thread.java:534)
java.lang.NullPointerException
at reports.UtilisationReport.getData(UtilisationReport.java:335)
at reports.UtilisationReport.generateReport(UtilisationReport.java:133)
at reports.DivUtilisationReportServlet.getReportData(DivUtilisationReportServlet.java:230)
at reports.DivUtilisationReportServlet.doGet(DivUtilisationReportServlet.java:62)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:696)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:809)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:198)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:144)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:209)
at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:595)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:432)
at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:954)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:138)
at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:595)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:432)
at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:954)
at org.apache.catalina.core.StandardContext.invoke(StandardContext.java:2459)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:132)
at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:595)
at org.apache.catalina.valves.ErrorDispatcherValve.invoke(ErrorDispatcherValve.java:118)
at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:593)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:116)
at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:593)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:432)
at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:954)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:126)
at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:595)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:432)
at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:954)
at org.apache.coyote.tomcat4.CoyoteAdapter.service(CoyoteAdapter.java:152)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:869)
at org.apache.coyote.http11.Http11BaseProtocol$Http11ConnectionHandler.processConnection(Http11BaseProtocol.java:664)
at org.apache.tomcat.util.net.PoolTcpEndpoint.processSocket(PoolTcpEndpoint.java:527)
at org.apache.tomcat.util.net.LeaderFollowerWorkerThread.runIt(LeaderFollowerWorkerThread.java:80)
at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:684)
at java.lang.Thread.run(Thread.java:534)

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

end of tomcat server error ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

but when i use sql server 2000 driver jdbc ("sun.jdbc.odbc.JdbcOdbcDriver"), It works fine but i need to use type 4 driver please help me to fix this issue.

advance many thanks.....

Could you provide me with the call stack for our 1.2 driver CTP? Is there any possibility for a finest level trace or an isolated repro we can debug this.|||

Dear Sir,

Thanks for your reply,

My observation about this issue is ..........the following query is ok with "sun.jdbc.odbc.JdbcOdbcDriver" driver but no ok with "com.microsoft.sqlserver.jdbc.SQLServerDriver" (ms sql 2005 server).

The error is compute clause. If i remove the compute from the query it works fine, no issue. But if i use compute clause it gives error. I dnt know why. please help me.

"select P3.* from ( select P1.type, SUM(CASE P1.costct WHEN
'1015168' THEN P1.total ELSE 0 END) AS a1015168, sum(P1.total) as total from ( select w.project as t
ype, e.costct, sum(t.tshours) as total from tswork t right outer join emp e on t.eid = e.eid join w
orkmap w on t.wno = w.wbs join project pp on w.project = pp.pno where tsstatus in ('appr','subt') an
d tsupload = 'y' and e.costct in ('1015168') and tsdate >= (select startdate from period where perio
d = 'JAN' and periodyear = 2006) and tsdate <= (select enddate from period where period = 'JAN' and
periodyear = 2006) group by e.costct, w.project union select PP.* from ( select t.jno as type, e.co
stct, sum(t.tshours) as total from tsjob t right outer join emp e on t.eid = e.eid where tsstatus in
('appr','subt') and tsupload = 'y' and e.costct in ('1015168') and tsdate >= (select startdate from
period where period = 'JAN' and periodyear = 2006) and tsdate <= (select enddate from period where
period = 'JAN' and periodyear = 2006) group by e.costct, t.jno ) as PP where type != 'NULL' ) as P1
group by P1.type ) as P3 order by P3.type compute sum(a1015168), sum(Total)"

But feel that "sun.jdbc.odbc.JdbcOdbcDriver" is better than type 4 driver. Is any way to modify this.

thanks,

Baker07

|||

Thanks a lot for reporting this and your detailed information. I can repro this locally, we will aim to fix the issue in our next release. It looks like we are not handling the token coming from a compute call. We will aim to add this in our next release. Again thank for your efforts to report this issue.

|||

Mr Mugund,

Is it possible to provide me a batch file to handle this error.

If possible... you can provide me...else i can wait for the next release.

thanks

|||

One thing we are interested knowning is that have you been using some other driver or technology that supports the compute clause. How do they expose the result from the compute clause?

Thanks

Mugunthan

jdbc type 4 driver error

Mr Mugundan.......

please see the error message...

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

tomcat server error message begin

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

end of tomcat server error ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

but when i use sql server 2000 driver jdbc ("sun.jdbc.odbc.JdbcOdbcDriver"), It works fine but i need to use type 4 driver please help me to fix this issue.

advance many thanks.....

Could you provide me with the call stack for our 1.2 driver CTP? Is there any possibility for a finest level trace or an isolated repro we can debug this.|||

Dear Sir,

Thanks for your reply,

My observation about this issue is ..........the following query is ok with "sun.jdbc.odbc.JdbcOdbcDriver" driver but no ok with "com.microsoft.sqlserver.jdbc.SQLServerDriver" (ms sql 2005 server).

The error is compute clause. If i remove the compute from the query it works fine, no issue. But if i use compute clause it gives error. I dnt know why. please help me.

But feel that "sun.jdbc.odbc.JdbcOdbcDriver" is better than type 4 driver. Is any way to modify this.

thanks,

Baker07

|||

Mr Mugund,

Is it possible to provide me a batch file to handle this error.

If possible... you can provide me...else i can wait for the next release.

thanks

|||

One thing we are interested knowning is that have you been using some other driver or technology that supports the compute clause. How do they expose the result from the compute clause?

Thanks

Mugunthan

JDBC slow in the morning?!

Hello,
I have a strange problem...
I manage a web application on Tomcat, Struts, SQLServer with the last
jdbc driver from Microsoft.
So, each morning one stored proc is very slow (around 20 sec to
execute) and around 10:00 AM, the stored proc accelerate (around 500
ms)!!
This stored proc is executing one query, and if necessary, insert
records for caching in a cache table.
Here is my code...
/*******************************************************************
* DocumentsOfEDMChanelForUser -
EXEC DocumentsOfEDMChanelForUser 10, 4, 576, 3
TRUNCATE TABLE cache_EDMRights
********************************************************************/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[DocumentsOfEDMChanelForUser]') and
OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[DocumentsOfEDMChanelForUser]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE DocumentsOfEDMChanelForUser
@.TopCount int
,@.LanguageOID int
,@.SecurityUserOID int
,@.ChanelOID int
AS
SET NOCOUNT ON
SELECT DISTINCT
Document.DOCUMENT_PK AS OID,
dbo.getLexiconText(Document.DisplayName, @.LanguageOID, '') AS
DocumentName,
dbo.getLexiconText(Document.Description, @.LanguageOID, '') AS
Description,
Document.LastModify AS CreationDate,
dbo.getFolderPath(Document.Folder, @.LanguageOID, '/') AS
FolderPath,
dbo.getIsDocumentReadedByUser(Document.DOCUMENT_PK,
@.SecurityUserOID) AS IsReaded,
dbo.getDocumentRights(@.SecurityUserOID, Document.DOCUMENT_PK,
0, 1) AS Rights
INTO
#ResultSet
FROM
Document Document INNER JOIN
EDMChanel EDMChanel ON Document.EDMChanel = EDMChanel.EDMCHANEL_CHANEL_IPK INNER JOIN
CodeBasis CodeBasis ON EDMChanel.PeriodType = CodeBasis.CODEBASIS_PK
WHERE
Document.EDMChanel = @.ChanelOID AND
Document.Active = 1 AND
CASE
WHEN CodeBasis.Code = 'PeriodType.Day' THEN DATEADD(day,
EDMChanel.Period, Document.LastModify)
WHEN CodeBasis.Code = 'PeriodType.Week' THEN DATEADD(week,
EDMChanel.Period, Document.LastModify)
WHEN CodeBasis.Code = 'PeriodType.Month' THEN
DATEADD(month, EDMChanel.Period, Document.LastModify)
WHEN CodeBasis.Code = 'PeriodType.Year' THEN DATEADD(year,
EDMChanel.Period, Document.LastModify)
END >= getdate()
--ORDER BY
-- CreationDate DESC
IF EXISTS(SELECT 1 FROM #ResultSet WHERE (Rights & 65536) > 0)
BEGIN
-- LEFT JOIN with cache because there are index violation with
insert
INSERT INTO cache_EDMRights
SELECT
0, @.SecurityUserOID, R.OID, R.Rights ^ 65536
FROM
#ResultSet R LEFT OUTER JOIN
cache_EDMRights C ON IsFolder = 0 AND C.SecurityUser = @.SecurityUserOID AND C.OID = R.OID
WHERE
C.OID IS NULL AND
(R.Rights & 65536) > 0
UPDATE #ResultSet SET Rights = Rights ^ 65536 WHERE (Rights &
65536) > 0
END
DECLARE @.SQL varchar(5000)
SET @.SQL = 'SELECT TOP ' + cast(@.TopCount as varchar(10)) + ' *
FROM #ResultSet WHERE Rights > 0 ORDER BY CreationDate DESC'
EXEC(@.SQL)
DROP TABLE #ResultSet
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
I'm not understanding?!
Anyone has idea?
Thanks for help.Hi
How do you know that it's JDBC issue?
Have you ran SQL Server Profiler to track down what is going on during the
execution?
Also ,I'd create a temporary table by CREATE TABLE #Temp and not by SELECT *
INTO ...
Another point, it might be a bad idea to use UDF with a large set data,
because it performs row-by-row process similar how cursors work.
"crabouif" <pascal_fluck@.hotmail.com> wrote in message
news:109014db.0411240054.4fc04476@.posting.google.com...
> Hello,
> I have a strange problem...
> I manage a web application on Tomcat, Struts, SQLServer with the last
> jdbc driver from Microsoft.
> So, each morning one stored proc is very slow (around 20 sec to
> execute) and around 10:00 AM, the stored proc accelerate (around 500
> ms)!!
> This stored proc is executing one query, and if necessary, insert
> records for caching in a cache table.
> Here is my code...
> /*******************************************************************
> * DocumentsOfEDMChanelForUser -
> EXEC DocumentsOfEDMChanelForUser 10, 4, 576, 3
> TRUNCATE TABLE cache_EDMRights
> ********************************************************************/
> if exists (select * from dbo.sysobjects where id => object_id(N'[dbo].[DocumentsOfEDMChanelForUser]') and
> OBJECTPROPERTY(id, N'IsProcedure') = 1)
> drop procedure [dbo].[DocumentsOfEDMChanelForUser]
> GO
> SET QUOTED_IDENTIFIER ON
> GO
> SET ANSI_NULLS ON
> GO
>
> CREATE PROCEDURE DocumentsOfEDMChanelForUser
> @.TopCount int
> ,@.LanguageOID int
> ,@.SecurityUserOID int
> ,@.ChanelOID int
> AS
> SET NOCOUNT ON
> SELECT DISTINCT
> Document.DOCUMENT_PK AS OID,
> dbo.getLexiconText(Document.DisplayName, @.LanguageOID, '') AS
> DocumentName,
> dbo.getLexiconText(Document.Description, @.LanguageOID, '') AS
> Description,
> Document.LastModify AS CreationDate,
> dbo.getFolderPath(Document.Folder, @.LanguageOID, '/') AS
> FolderPath,
> dbo.getIsDocumentReadedByUser(Document.DOCUMENT_PK,
> @.SecurityUserOID) AS IsReaded,
> dbo.getDocumentRights(@.SecurityUserOID, Document.DOCUMENT_PK,
> 0, 1) AS Rights
> INTO
> #ResultSet
> FROM
> Document Document INNER JOIN
> EDMChanel EDMChanel ON Document.EDMChanel => EDMChanel.EDMCHANEL_CHANEL_IPK INNER JOIN
> CodeBasis CodeBasis ON EDMChanel.PeriodType => CodeBasis.CODEBASIS_PK
> WHERE
> Document.EDMChanel = @.ChanelOID AND
> Document.Active = 1 AND
> CASE
> WHEN CodeBasis.Code = 'PeriodType.Day' THEN DATEADD(day,
> EDMChanel.Period, Document.LastModify)
> WHEN CodeBasis.Code = 'PeriodType.Week' THEN DATEADD(week,
> EDMChanel.Period, Document.LastModify)
> WHEN CodeBasis.Code = 'PeriodType.Month' THEN
> DATEADD(month, EDMChanel.Period, Document.LastModify)
> WHEN CodeBasis.Code = 'PeriodType.Year' THEN DATEADD(year,
> EDMChanel.Period, Document.LastModify)
> END >= getdate()
> --ORDER BY
> -- CreationDate DESC
> IF EXISTS(SELECT 1 FROM #ResultSet WHERE (Rights & 65536) > 0)
> BEGIN
> -- LEFT JOIN with cache because there are index violation with
> insert
> INSERT INTO cache_EDMRights
> SELECT
> 0, @.SecurityUserOID, R.OID, R.Rights ^ 65536
> FROM
> #ResultSet R LEFT OUTER JOIN
> cache_EDMRights C ON IsFolder = 0 AND C.SecurityUser => @.SecurityUserOID AND C.OID = R.OID
> WHERE
> C.OID IS NULL AND
> (R.Rights & 65536) > 0
> UPDATE #ResultSet SET Rights = Rights ^ 65536 WHERE (Rights &
> 65536) > 0
> END
> DECLARE @.SQL varchar(5000)
> SET @.SQL = 'SELECT TOP ' + cast(@.TopCount as varchar(10)) + ' *
> FROM #ResultSet WHERE Rights > 0 ORDER BY CreationDate DESC'
> EXEC(@.SQL)
> DROP TABLE #ResultSet
> GO
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS ON
> GO
> I'm not understanding?!
> Anyone has idea?
> Thanks for help.|||As Uri already indicated, you may not want to assume it's
just a JDBC issue. In addition to profiler, you would want
to check the activity on the server (using PerfMon) as well
as the activity in SQL Server. When something runs slow at
particular times, it's often due to other activities on the
server or in SQL Server that are using a lot of resources.
-Sue
On 24 Nov 2004 00:54:00 -0800, pascal_fluck@.hotmail.com
(crabouif) wrote:
>Hello,
>I have a strange problem...
>I manage a web application on Tomcat, Struts, SQLServer with the last
>jdbc driver from Microsoft.
>So, each morning one stored proc is very slow (around 20 sec to
>execute) and around 10:00 AM, the stored proc accelerate (around 500
>ms)!!
>This stored proc is executing one query, and if necessary, insert
>records for caching in a cache table.
>Here is my code...
>/*******************************************************************
>* DocumentsOfEDMChanelForUser -
>EXEC DocumentsOfEDMChanelForUser 10, 4, 576, 3
>TRUNCATE TABLE cache_EDMRights
>********************************************************************/
>if exists (select * from dbo.sysobjects where id =>object_id(N'[dbo].[DocumentsOfEDMChanelForUser]') and
>OBJECTPROPERTY(id, N'IsProcedure') = 1)
>drop procedure [dbo].[DocumentsOfEDMChanelForUser]
>GO
>SET QUOTED_IDENTIFIER ON
>GO
>SET ANSI_NULLS ON
>GO
>
>CREATE PROCEDURE DocumentsOfEDMChanelForUser
> @.TopCount int
> ,@.LanguageOID int
> ,@.SecurityUserOID int
> ,@.ChanelOID int
>AS
> SET NOCOUNT ON
> SELECT DISTINCT
> Document.DOCUMENT_PK AS OID,
> dbo.getLexiconText(Document.DisplayName, @.LanguageOID, '') AS
>DocumentName,
> dbo.getLexiconText(Document.Description, @.LanguageOID, '') AS
>Description,
> Document.LastModify AS CreationDate,
> dbo.getFolderPath(Document.Folder, @.LanguageOID, '/') AS
>FolderPath,
> dbo.getIsDocumentReadedByUser(Document.DOCUMENT_PK,
>@.SecurityUserOID) AS IsReaded,
> dbo.getDocumentRights(@.SecurityUserOID, Document.DOCUMENT_PK,
>0, 1) AS Rights
> INTO
> #ResultSet
> FROM
> Document Document INNER JOIN
> EDMChanel EDMChanel ON Document.EDMChanel =>EDMChanel.EDMCHANEL_CHANEL_IPK INNER JOIN
> CodeBasis CodeBasis ON EDMChanel.PeriodType =>CodeBasis.CODEBASIS_PK
> WHERE
> Document.EDMChanel = @.ChanelOID AND
> Document.Active = 1 AND
> CASE
> WHEN CodeBasis.Code = 'PeriodType.Day' THEN DATEADD(day,
>EDMChanel.Period, Document.LastModify)
> WHEN CodeBasis.Code = 'PeriodType.Week' THEN DATEADD(week,
>EDMChanel.Period, Document.LastModify)
> WHEN CodeBasis.Code = 'PeriodType.Month' THEN
>DATEADD(month, EDMChanel.Period, Document.LastModify)
> WHEN CodeBasis.Code = 'PeriodType.Year' THEN DATEADD(year,
>EDMChanel.Period, Document.LastModify)
> END >= getdate()
> --ORDER BY
> -- CreationDate DESC
> IF EXISTS(SELECT 1 FROM #ResultSet WHERE (Rights & 65536) > 0)
> BEGIN
> -- LEFT JOIN with cache because there are index violation with
>insert
> INSERT INTO cache_EDMRights
> SELECT
> 0, @.SecurityUserOID, R.OID, R.Rights ^ 65536
> FROM
> #ResultSet R LEFT OUTER JOIN
> cache_EDMRights C ON IsFolder = 0 AND C.SecurityUser =>@.SecurityUserOID AND C.OID = R.OID
> WHERE
> C.OID IS NULL AND
> (R.Rights & 65536) > 0
> UPDATE #ResultSet SET Rights = Rights ^ 65536 WHERE (Rights &
>65536) > 0
> END
> DECLARE @.SQL varchar(5000)
> SET @.SQL = 'SELECT TOP ' + cast(@.TopCount as varchar(10)) + ' *
>FROM #ResultSet WHERE Rights > 0 ORDER BY CreationDate DESC'
> EXEC(@.SQL)
> DROP TABLE #ResultSet
>GO
>SET QUOTED_IDENTIFIER OFF
>GO
>SET ANSI_NULLS ON
>GO
>I'm not understanding?!
>Anyone has idea?
>Thanks for help.|||Well, I endly solve my problem.
So I check that I had an index is breaked. And in 10:00 AM I had an
agent who reindex it.
Thanks for your idees, I solve that problem with profiler...
But what I dont explain is why with JDBC (respectively my application
and Aqua Data Studio) it was slow, and with MS Query Analyser it was
quick...
Thanks one more for you help.
Pascal
Sue Hoegemeier <Sue_H@.nomail.please> wrote in message news:<sn99q0p7d6ii61m735s2mdt4eed5h23q18@.4ax.com>...
> As Uri already indicated, you may not want to assume it's
> just a JDBC issue. In addition to profiler, you would want
> to check the activity on the server (using PerfMon) as well
> as the activity in SQL Server. When something runs slow at
> particular times, it's often due to other activities on the
> server or in SQL Server that are using a lot of resources.
> -Sue
> On 24 Nov 2004 00:54:00 -0800, pascal_fluck@.hotmail.com
> (crabouif) wrote:
> >Hello,
> >
> >I have a strange problem...
> >
> >I manage a web application on Tomcat, Struts, SQLServer with the last
> >jdbc driver from Microsoft.
> >
> >So, each morning one stored proc is very slow (around 20 sec to
> >execute) and around 10:00 AM, the stored proc accelerate (around 500
> >ms)!!
> >
> >This stored proc is executing one query, and if necessary, insert
> >records for caching in a cache table.
> >
> >Here is my code...
> >
> >/*******************************************************************
> >* DocumentsOfEDMChanelForUser -
> >EXEC DocumentsOfEDMChanelForUser 10, 4, 576, 3
> >TRUNCATE TABLE cache_EDMRights
> >********************************************************************/
> >if exists (select * from dbo.sysobjects where id => >object_id(N'[dbo].[DocumentsOfEDMChanelForUser]') and
> >OBJECTPROPERTY(id, N'IsProcedure') = 1)
> >drop procedure [dbo].[DocumentsOfEDMChanelForUser]
> >GO
> >
> >SET QUOTED_IDENTIFIER ON
> >GO
> >SET ANSI_NULLS ON
> >GO
> >
> >
> >CREATE PROCEDURE DocumentsOfEDMChanelForUser
> > @.TopCount int
> > ,@.LanguageOID int
> > ,@.SecurityUserOID int
> > ,@.ChanelOID int
> >AS
> > SET NOCOUNT ON
> >
> > SELECT DISTINCT
> > Document.DOCUMENT_PK AS OID,
> > dbo.getLexiconText(Document.DisplayName, @.LanguageOID, '') AS
> >DocumentName,
> > dbo.getLexiconText(Document.Description, @.LanguageOID, '') AS
> >Description,
> > Document.LastModify AS CreationDate,
> > dbo.getFolderPath(Document.Folder, @.LanguageOID, '/') AS
> >FolderPath,
> > dbo.getIsDocumentReadedByUser(Document.DOCUMENT_PK,
> >@.SecurityUserOID) AS IsReaded,
> > dbo.getDocumentRights(@.SecurityUserOID, Document.DOCUMENT_PK,
> >0, 1) AS Rights
> > INTO
> > #ResultSet
> > FROM
> > Document Document INNER JOIN
> > EDMChanel EDMChanel ON Document.EDMChanel => >EDMChanel.EDMCHANEL_CHANEL_IPK INNER JOIN
> > CodeBasis CodeBasis ON EDMChanel.PeriodType => >CodeBasis.CODEBASIS_PK
> > WHERE
> > Document.EDMChanel = @.ChanelOID AND
> > Document.Active = 1 AND
> > CASE
> > WHEN CodeBasis.Code = 'PeriodType.Day' THEN DATEADD(day,
> >EDMChanel.Period, Document.LastModify)
> > WHEN CodeBasis.Code = 'PeriodType.Week' THEN DATEADD(week,
> >EDMChanel.Period, Document.LastModify)
> > WHEN CodeBasis.Code = 'PeriodType.Month' THEN
> >DATEADD(month, EDMChanel.Period, Document.LastModify)
> > WHEN CodeBasis.Code = 'PeriodType.Year' THEN DATEADD(year,
> >EDMChanel.Period, Document.LastModify)
> > END >= getdate()
> > --ORDER BY
> > -- CreationDate DESC
> >
> > IF EXISTS(SELECT 1 FROM #ResultSet WHERE (Rights & 65536) > 0)
> > BEGIN
> > -- LEFT JOIN with cache because there are index violation with
> >insert
> > INSERT INTO cache_EDMRights
> > SELECT
> > 0, @.SecurityUserOID, R.OID, R.Rights ^ 65536
> > FROM
> > #ResultSet R LEFT OUTER JOIN
> > cache_EDMRights C ON IsFolder = 0 AND C.SecurityUser => >@.SecurityUserOID AND C.OID = R.OID
> > WHERE
> > C.OID IS NULL AND
> > (R.Rights & 65536) > 0
> > UPDATE #ResultSet SET Rights = Rights ^ 65536 WHERE (Rights &
> >65536) > 0
> > END
> >
> > DECLARE @.SQL varchar(5000)
> > SET @.SQL = 'SELECT TOP ' + cast(@.TopCount as varchar(10)) + ' *
> >FROM #ResultSet WHERE Rights > 0 ORDER BY CreationDate DESC'
> > EXEC(@.SQL)
> > DROP TABLE #ResultSet
> >GO
> >SET QUOTED_IDENTIFIER OFF
> >GO
> >SET ANSI_NULLS ON
> >GO
> >
> >I'm not understanding?!
> >
> >Anyone has idea?
> >
> >Thanks for help.|||One thing is that you could have been seeing the effects of
caching and in combination with calling the stored procedure
differently in the two scenarios. You can monitor the
caching - cache hits, cache misses, etc. using Profiler.
-Sue
On 25 Nov 2004 22:58:40 -0800, pascal_fluck@.hotmail.com
(crabouif) wrote:
>Well, I endly solve my problem.
>So I check that I had an index is breaked. And in 10:00 AM I had an
>agent who reindex it.
>Thanks for your idees, I solve that problem with profiler...
>But what I dont explain is why with JDBC (respectively my application
>and Aqua Data Studio) it was slow, and with MS Query Analyser it was
>quick...
>Thanks one more for you help.
> Pascal
>
>Sue Hoegemeier <Sue_H@.nomail.please> wrote in message news:<sn99q0p7d6ii61m735s2mdt4eed5h23q18@.4ax.com>...
>> As Uri already indicated, you may not want to assume it's
>> just a JDBC issue. In addition to profiler, you would want
>> to check the activity on the server (using PerfMon) as well
>> as the activity in SQL Server. When something runs slow at
>> particular times, it's often due to other activities on the
>> server or in SQL Server that are using a lot of resources.
>> -Sue
>> On 24 Nov 2004 00:54:00 -0800, pascal_fluck@.hotmail.com
>> (crabouif) wrote:
>> >Hello,
>> >
>> >I have a strange problem...
>> >
>> >I manage a web application on Tomcat, Struts, SQLServer with the last
>> >jdbc driver from Microsoft.
>> >
>> >So, each morning one stored proc is very slow (around 20 sec to
>> >execute) and around 10:00 AM, the stored proc accelerate (around 500
>> >ms)!!
>> >
>> >This stored proc is executing one query, and if necessary, insert
>> >records for caching in a cache table.
>> >
>> >Here is my code...
>> >
>> >/*******************************************************************
>> >* DocumentsOfEDMChanelForUser -
>> >EXEC DocumentsOfEDMChanelForUser 10, 4, 576, 3
>> >TRUNCATE TABLE cache_EDMRights
>> >********************************************************************/
>> >if exists (select * from dbo.sysobjects where id =>> >object_id(N'[dbo].[DocumentsOfEDMChanelForUser]') and
>> >OBJECTPROPERTY(id, N'IsProcedure') = 1)
>> >drop procedure [dbo].[DocumentsOfEDMChanelForUser]
>> >GO
>> >
>> >SET QUOTED_IDENTIFIER ON
>> >GO
>> >SET ANSI_NULLS ON
>> >GO
>> >
>> >
>> >CREATE PROCEDURE DocumentsOfEDMChanelForUser
>> > @.TopCount int
>> > ,@.LanguageOID int
>> > ,@.SecurityUserOID int
>> > ,@.ChanelOID int
>> >AS
>> > SET NOCOUNT ON
>> >
>> > SELECT DISTINCT
>> > Document.DOCUMENT_PK AS OID,
>> > dbo.getLexiconText(Document.DisplayName, @.LanguageOID, '') AS
>> >DocumentName,
>> > dbo.getLexiconText(Document.Description, @.LanguageOID, '') AS
>> >Description,
>> > Document.LastModify AS CreationDate,
>> > dbo.getFolderPath(Document.Folder, @.LanguageOID, '/') AS
>> >FolderPath,
>> > dbo.getIsDocumentReadedByUser(Document.DOCUMENT_PK,
>> >@.SecurityUserOID) AS IsReaded,
>> > dbo.getDocumentRights(@.SecurityUserOID, Document.DOCUMENT_PK,
>> >0, 1) AS Rights
>> > INTO
>> > #ResultSet
>> > FROM
>> > Document Document INNER JOIN
>> > EDMChanel EDMChanel ON Document.EDMChanel =>> >EDMChanel.EDMCHANEL_CHANEL_IPK INNER JOIN
>> > CodeBasis CodeBasis ON EDMChanel.PeriodType =>> >CodeBasis.CODEBASIS_PK
>> > WHERE
>> > Document.EDMChanel = @.ChanelOID AND
>> > Document.Active = 1 AND
>> > CASE
>> > WHEN CodeBasis.Code = 'PeriodType.Day' THEN DATEADD(day,
>> >EDMChanel.Period, Document.LastModify)
>> > WHEN CodeBasis.Code = 'PeriodType.Week' THEN DATEADD(week,
>> >EDMChanel.Period, Document.LastModify)
>> > WHEN CodeBasis.Code = 'PeriodType.Month' THEN
>> >DATEADD(month, EDMChanel.Period, Document.LastModify)
>> > WHEN CodeBasis.Code = 'PeriodType.Year' THEN DATEADD(year,
>> >EDMChanel.Period, Document.LastModify)
>> > END >= getdate()
>> > --ORDER BY
>> > -- CreationDate DESC
>> >
>> > IF EXISTS(SELECT 1 FROM #ResultSet WHERE (Rights & 65536) > 0)
>> > BEGIN
>> > -- LEFT JOIN with cache because there are index violation with
>> >insert
>> > INSERT INTO cache_EDMRights
>> > SELECT
>> > 0, @.SecurityUserOID, R.OID, R.Rights ^ 65536
>> > FROM
>> > #ResultSet R LEFT OUTER JOIN
>> > cache_EDMRights C ON IsFolder = 0 AND C.SecurityUser =>> >@.SecurityUserOID AND C.OID = R.OID
>> > WHERE
>> > C.OID IS NULL AND
>> > (R.Rights & 65536) > 0
>> > UPDATE #ResultSet SET Rights = Rights ^ 65536 WHERE (Rights &
>> >65536) > 0
>> > END
>> >
>> > DECLARE @.SQL varchar(5000)
>> > SET @.SQL = 'SELECT TOP ' + cast(@.TopCount as varchar(10)) + ' *
>> >FROM #ResultSet WHERE Rights > 0 ORDER BY CreationDate DESC'
>> > EXEC(@.SQL)
>> > DROP TABLE #ResultSet
>> >GO
>> >SET QUOTED_IDENTIFIER OFF
>> >GO
>> >SET ANSI_NULLS ON
>> >GO
>> >
>> >I'm not understanding?!
>> >
>> >Anyone has idea?
>> >
>> >Thanks for help.

Friday, February 24, 2012

Jet ODBC Driver for Text files - Permissions Issue

Jet ODBC Driver for Text files - Permissions Issue

JDBC: Retrieving stored procedures column information returns "-9" data type for nvarc

JDBC: DatabaseMetaData.getURL() returns jdbc:sqljdbc://

JDBC XA in a cluster

JDBC und SQL Server

JDBC Type 4 Driver for MS SQL Server 7.0

jdbc type 4 driver error

jdbc type 4 driver error

JDBC slow in the morning?!

JDBC slow in the morning?!

JDBC slow in the morning?!

JDBC Driver issue

Blog Archive

About Me