Hi.
How can I get the messages sent by the server while I'm executing a stored procedure via the JDBC driver?
I need to get my own debug messages (done through the print() function)
and also standard server messages (such as "x row(s) affected" or
results from SET STATISTICS TIME ON). Is this possible?
Many thanks.
Carlos
Hi,
I'm getting an error when I make two calls to a stored procedure (which I didn't use to).
This is an example:
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
public class Main {
public Main() {
Connection sproc_con;
try {
sproc_con = getConnection();
CallableStatement sproc_stmt = sproc_con.prepareCall("{call getID(?)}");
for (int i = 0; i < 2; i++) {
int id = getID("Test", sproc_stmt);
System.out.println(id);
}
sproc_stmt.close();
sproc_con.close();
} catch (ClassNotFoundException ex) {
ex.printStackTrace();
} catch (SQLException ex) {
ex.printStackTrace();
}
}
private int getID(String tableName, CallableStatement sproc_stmt) {
ResultSet rs = null;
int ID = 0;
try {
sproc_stmt.setString(1, tableName);
rs = sproc_stmt.executeQuery();
if (rs.next())
ID = rs.getInt(1);
} catch (SQLException sqle) {
sqle.printStackTrace();
System.exit(1);
} finally {
try {
if (rs != null)
rs.close();
} catch (SQLException sqle) {
sqle.printStackTrace();
}
return ID;
}
}
private Connection getConnection() throws ClassNotFoundException, SQLException {
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
return DriverManager.getConnection("jdbc:sqlserver://myserver:1433;DatabaseName=TestDB", "sa", "");
}
public static void main(String[] args) {
new Main();
}
}
The first call is ok, but the second throws an exception at sproc_stmt.executeQuery(); which says:
com.microsoft.sqlserver.jdbc.SQLServerException: Server failed to resume the transaction, desc: 3600000001.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(Unknown Source)
at com.microsoft.sqlserver.jdbc.IOBuffer.processPackets(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.getPrepExecResponse(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PreparedStatementExecutionRequest.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.SQLServerPreparedStatement.executeQuery(Unknown Source)
at javaapplication2.Main.getID(Main.java:53)
at javaapplication2.Main.<init>(Main.java:33)
at javaapplication2.Main.main(Main.java:85)
Am I doing something wrong or is this a bug?
I'm running on Win2003 with SQL Server 2005 Developer edition.
Thanks
/Niclas
I am unable to send null values through the Microsoft JDBC 1.1 driver to a stored procedure. Please look at the thread already started on the SQL Server Transact SQL Forum at http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1772215&SiteID=1
View 1 Replies View RelatedI have a procedure that uses varchar(max) as output parameter, when I tried to retrieve
the result of calling the procedure, the result is truncated to 4000 character. Is this a driver bug?
Any workaround?
Here is the pseudo code:
create Procedure foo(@output varchar(max))
{
set @foo = 'string that has more than 4000 characters...';
return;
}
Java code:
CallableStatement cs = connection.prepareCall("{call foo ?}");
cs.registerOutputParameter(1, Types.longvarchar); // also tried Types.CLOB.
cs.execute();
String result = cs.getString(1); // The result is truncated to 4000 char.
-- Also tried
CLOB clob = cs.getClob(1);
long len = clob.length(); // The result is 4000.
Thanks,
Eric Wang
Hi,
I am using SQL server 2005 stored procedures being called from my java application using the CallableStatement. As long as my stored procedure is a simple and direct Select statement things are moving nicely.
But my stored procedures are a little bit more complicated and this causes problems for me to parse the data in a ResultSet.
a sample stored procedure:
Create procedure sp_Get
@cat int,
@itemId bigint
as
declare @results table (tableId bigint, label varchar(200), typeId int)
if @cat = 1
begin
insert @results (tableId, label, typeId)
select
tableId = personId,
label = fname + lname,
typeId = 1
from Person where catId = @itemId
insert @results (tableId, label, typeId)
select
tableId = prospectId,
label = prospect,
typeId = 2
from Prospects where catId = @itemId
end
else if @cat = 2
begin
insert @results (tableId, label, typeId)
select
tableId = companyId,
label = Company,
typeId = 1
from Company where regionId = @itemId
end
/* result set*/
select
tableId,
label,
typeId
from @results
GO
my java code:
Connection conn = this.getConnection() //opens connection to db
CallableStatement cmd = conn.prepareCall(" { call sp_Get (?,?) }");
cmd.setInteger("cat", 1);
cmd.setLong("itemId", 2);
//this returns false?
boolean hasResults = cmd.execute();
//this also returns false?
boolean moreResults = cmd.getMoreResults();
The strange part of this is that when you execute this query in the SQL Server Managent Studio it returns 1 result set.
Why is my java code not able to see this result?
Thanks for any help,
- Rogier
Using the MSSQL JDBC 1.2 driver (Oct 2007 release), we don't seem to be able to call stored procedures which internally uses app_name() function to fill into database tables .
This driver fails to access such stored procedures in both SQL Server 2000 and SQL Server 2005 databases.
The previous 1.1 driver (2006), suceeds in both cases.
Here is a test case which demonstrates this problem.
create table ICS_ConsraintTest (PrimaryIdentifier varchar(64),
ServiceProvider varchar(64),
SecondaryID varchar(64),
SecondaryServiceProvider varchar(64),
Description varchar(64),
PanoramaObject varchar(64),
Operation int,
ScalingFactor int,
DisplayAs float,
FeedType int,
InputValue int,
OutputValue float,
salary float,
birthdate datetime,
CONSTRAINT SimpleTestConstraint CHECK((len(ltrim([PrimaryIdentifier])) > 0
and len(ltrim([ServiceProvider])) > 0
and len(ltrim([SecondaryID])) > 0
and len(ltrim([PanoramaObject])) > 0
and [salary] <> 0.0)))
Stored procedure id defined as follows:
CREATE PROCEDURE SP_ICS_TestWithConstraints1(@PrimaryIdentifier varchar(64),
@PrimaryServiceProvider varchar(64),
@ServiceProvider varchar(64),
@SecondaryID varchar(64),
@SecondaryServiceProvider varchar(64),
@Description varchar(64),
@PanoramaObject varchar(64),
@Operation int,
@ScalingFactor int,
@DisplayAs float,
@FeedType int,
@InputValue int,
@OutputValue float,
@salary float,
@birthdate datetime) AS
BEGIN
BEGIN TRANSACTION
BEGIN
/* Insert */
INSERT INTO ICS_ConsraintTest ( PrimaryIdentifier,
ServiceProvider,
SecondaryID,
SecondaryServiceProvider,
Description,
PanoramaObject,
Operation,
ScalingFactor,
DisplayAs,
FeedType,
InputValue,
OutputValue,
salary,
birthdate)
VALUES ( @PrimaryIdentifier,
@ServiceProvider,
app_name(),
@SecondaryServiceProvider,
@Description,
@PanoramaObject,
@Operation,
@ScalingFactor,
@DisplayAs,
@FeedType,
@InputValue,
@OutputValue,
@salary,
@birthdate)
END
COMMIT TRANSACTION
END
Check out the app_name() is passed as the SecondaryID which causes the failure.
I have read similar posts to this, but I am still having problems.
I am trying to use connection pooling to connect to a local SQL Server 2005 database. I am running my application using
MyEclipse Enterprise Workbench. I have verified that sqljdbc.jar resides in "WebRoot/WEB-INF/lib/"
"WebRoot/WEB-INF/web.xml":
<?xml version="1.0" encoding="UTF-8"?>
<web-app version="2.4" xmlns="http://java.sun.com/xml/ns/j2ee"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsichemaLocation="http://java.sun.com/xml/ns/j2ee
http://java.sun.com/xml/ns/j2ee/web-app_2_4.xsd">
<resource-ref>
<res-ref-name>jdbc/DefaultDS</res-ref-name>
<res-type>javax.sql.DataSource</res-type>
<res-auth>Container</res-auth>
</resource-ref>
</web-app>
"WebRoot/META-INFcontext.xml":
<?xml version="1.0" encoding="UTF-8"?>
<Context>
<Resource name="jdbc/DefaultDS"
auth="Container"
type="javax.sql.DataSource"
username="tec"
password="tec"
driverClassName="com.microsoft.sqlserver.jdbc.SQLServerDrive"
url="jdbcqlserver://localhost:1433/tec;databaseName=tec;user=tec;password=test;"
validationQuery="select 1"
maxActive="10"
maxIdle="2"/>
</Context>
Classpath:
<?xml version="1.0" encoding="UTF-8"?>
<classpath>
<classpathentry kind="src" path="src"/>
<classpathentry kind="con" path="org.eclipse.jdt.launching.JRE_CONTAINER"/>
<classpathentry kind="con" path="com.genuitec.eclipse.j2eedt.core.J2EE14_CONTAINER"/>
<classpathentry kind="lib" path="WebRoot/WEB-INF/lib/dom.jar"/>
<classpathentry kind="lib" path="WebRoot/WEB-INF/lib/jaxen-full.jar"/>
<classpathentry kind="lib" path="WebRoot/WEB-INF/lib/jaxp-api.jar"/>
<classpathentry kind="lib" path="WebRoot/WEB-INF/lib/jdbc2_0-stdext.jar"/>
<classpathentry kind="lib" path="WebRoot/WEB-INF/lib/sqljdbc.jar"/>
<classpathentry kind="lib" path="WebRoot/WEB-INF/lib/jstl.jar"/>
<classpathentry kind="lib" path="WebRoot/WEB-INF/lib/mail.jar"/>
<classpathentry kind="lib" path="WebRoot/WEB-INF/lib/sax.jar"/>
<classpathentry kind="lib" path="WebRoot/WEB-INF/lib/saxpath.jar"/>
<classpathentry kind="lib" path="WebRoot/WEB-INF/lib/standard.jar"/>
<classpathentry kind="lib" path="WebRoot/WEB-INF/lib/xalan.jar"/>
<classpathentry kind="lib" path="WebRoot/WEB-INF/lib/xercesImpl.jar"/>
<classpathentry kind="output" path="WebRoot/WEB-INF/classes"/>
</classpath>
Code to connect:
import java.io.Serializable;
import java.sql.*;
import javax.sql.*;
import javax.naming.*;
public int testConnect(){
Connection conn = null;
InitialContext ctx = null;
java.sql.Statement stmt = null;
try {
ctx = new InitialContext();
Context envCtx = (Context) ctx.lookup("java:comp/env");
DataSource ds = (DataSource) envCtx.lookup("jdbc/DefaultDS");/*This is generating the Cannot load JDBC driver class... error*/
conn = ds.getConnection();
stmt = conn.createStatement();
return CONSTANT.SUCCESS;
} catch (Exception e) {
return CONSTANT.FAILURE;
}finally {
try {
if (stmt != null)
stmt.close();
if (conn != null)
conn.close();
if (ctx != null)
ctx.close();
} catch (Exception ex) {
// do nothing
return CONSTANT.FAILURE;
}
}
}
Any ideas would be greatly appreciated.
I need to develope a stored procedure (eventually called by a trigger) that creates a record in an event table for all the descendants of a drawing. There are 3 tables involved as example tables shown below:
DwgTable
DwgID (integer)--drawing record identifier
PrntDwgID (integer)--parent drawing record identifier (a previously defined DwgID from this table)
DwgEventTable
DwgEventID (integer)--record identifier
DwgID (integer)--value from DwgTable
EventID (integer)--value from EventTable
EventTable
EventID
There are other fields in two of the tables and only the fields shown in the DwgEventTable, but only the fields shown are required for adding a record in the DwgEventTable for a new event in the EventTable. The problem is identifying all the DwgID's of the descendant of the DwgID where the EventID occurred. There may be 0 to N descendants in 0 to N generations. I need to add a record for the original DwgID and all the descendant DwgID's in the DwgEventTable for the event identified by EventID.
I could do this from the client side, but a better place would be from the server side. I need some clue(s) on how to start coding a recursive stored procedure in SQL Server 2005. From what I have read, you cannot create a managed code procedure that appends or updates records--if managed code can add/modify records then I can do the above with managed code procedure.
Any Suggestions?
Hi all,
We've just stumbled on a 1.0 version incompatibility with the JDBC specs.
Problem: A table with SMALLINT column. According to JDBC specs version 3.0
(p.B-179) and 4.0 (p.221)), the value should be converted to Integer type.
Unfortunatelly we get a Short object :(
Now, I remember, this case was also affecting old JSQLConnect driver from
DataDirect. Could that problem sneak to new MS driver too?
Please let me know any resolution to this problem if exists.
The issue has not been fixed in CTP 1.1 version. Any ideas if it can be fixed??
Cheers,
Piotr
I didn't want to maintain similar/identical tables in a legacy FoxPro system and another system with SQL Server back end. Both systems are active, but some tables are shared.
Initially I was going to use a Linked Server to the FoxPro to pull the FP data when needed. This works. But, I've come up with what I believe is a better solution. Keep in mind that these tables are largely static - occassional changes, edits.
I will do a 1 time DTS from FP into SQL Server tables.
I then create INSERT and UPDATE triggers within FoxPro.
These triggers fire a stored procedure in FoxPro that establishes a connection to the SQL Server and fire the appropriate stored procedure on SQL Server to CREATE and/or UPDATE the corresponding table there.
In the end - the tables are local to both apps.
If the UPDATES or TRIGGERS fail I write to an error log - and in that rare case - I can manually fix. I could set it up to email me from within FoxPro as well if needed.
Here's the FoxPro and SQL Server code for reference for the Record Insert:
FOXPRO employee.dbf InsertTrigger:
employee_insert_trigger(VAL(Employee.ep_pk),Employ ee.fname,Employee.lname,Employee.email,Employee.us er_login,Employee.phone)
FOXPRO corresponding Stored Procedure:
FUNCTION EMPLOYEE_INSERT_TRIGGER
PARAMETERS wepk,wefname,welname,weemail,WEUSERID,WEPHONE
nhandle=SQLCONNECT('SS_PDITHP3','userid','password ')
IF nhandle<0
m.errclose=.f.
IF !USED("errorlog")
USE tisdata!errorlog IN SELECT(1)
m.errclose=.t.
ENDIF
SELECT errorlog
INSERT INTO errorlog (date, time, program,source,user) ;
values (DATE(), TIME(), 'EMPLOYEE_INSERT_TRIGGER','nhandle<0 PARAMS: '+STR(wepk)+wefname+welname+weemail+WEUSERID+WEPHO NE,GETENV("username"))
IF m.errclose
USE IN errorlog
ENDIF
RETURN
ENDIF
nquery="exec ewo_sp_insertNewEmployee @WEPK ="+STR(wepk)+",@WEFNAME ='"+wefname+"',@WELNAME ='"+welname+"',@WEEMAIL ='"+weemail+"',@WEUSERID ='"+weuserid+"',@WEPHONE='"+wephone+"',@RETCODE =0"
nsucc=SQLEXEC(nhandle,nquery)
SQLDISCONNECT(nhandle)
IF nSucc<0
m.errclose=.f.
IF !USED("errorlog")
USE tisdata!errorlog IN SELECT(1)
m.errclose=.t.
ENDIF
SELECT errorlog
INSERT INTO errorlog (date, time, program,source,user) ;
values (DATE(), TIME(), 'EMPLOYEE_INSERT_TRIGGER','nSucc<0 PARAMS: '+STR(wepk)+wefname+welname+weemail+WEUSERID+WEPHO NE,GETENV("username"))
IF m.errclose
USE IN errorlog
ENDIF
ENDIF
RETURN
SQL SERVER Stored Procedure called from FOXPRO Stored Procedure
CREATE procedure ewo_sp_insertNewEmployee (
@WEPK int,
@WEFNAME char(20),
@WELNAME char(20),
@WEEMAIL char(50),
@WEUSERID char(15),
@WEPHONE char(25),
@RETCODE int OUTPUT
)
AS
insert into WO_EMP (
WE_PK,
WE_FNAME,
WE_LNAME,
WE_EMAIL,
WE_USERID,
WE_PHONE
)
VALUES (
@WEPK,
@WEFNAME,
@WELNAME,
@WEEMAIL,
@WEUSERID,
@WEPHONE
)
IF @@ERROR <> 0
BEGIN
SET @RETCODE=@@ERROR
END
ELSE
BEGIN
-- SUCCESS!!
SET @RETCODE=0
END
return @RETCODE
GO
Hi all,
I have a problem while i create a proxy account.The situation is like this...There is a user who has an login in to the server.He has a stored procedure which calls some on the SSIS packages and XP_cmdshell...so this stored procedure basically load some data in to the tables .So for the login in order to execute the stored procedure as he is not a Sys admin I have created a proxy account in my account as Iam an SA and then in the proxies and in principals I selected his login name and this way I have created a credential and a proxy account.
Now the problem is if he logins with his id and password and try to execute the stored procedure it gives an error message
Server: Msg 15153, Level 16, State 1, Procedure xp_cmdshell, Line 1
The xp_cmdshell proxy account information cannot be retrieved or is invalid. Verify that the '##xp_cmdshell_proxy_account##' credential exists and contains valid information.
....so this mean the login is not able to see the proxy account.So what I did is I created a job and then in the job owner tab I have selected his login and then created a step with the type operating system (CmdExec) as I need to just execute the stored procedure and used the proxy account that I have created.
so I gave the command -- exec <stored procedure> --.
But this job fails and gives the error message as
[298] SQLServer Error: 536, Invalid length parameter passed to the SUBSTRING function. [SQLSTATE 42000]....
So now ....first My question is am I doing in a right way....if its right then why Iam not able to execute the stored procedure.
If there is any other way through which I can execute the stored procedure using a proxy account for the logins who are not sys admins....please do let me know.....
Thanks
Raja.V
Hi,Could some please tell me whether Microsoft provides Type 2 and Type 4jdbc driver ? For Type 4 MS providescom.microsoft.jdbc.sqlserver.SQLServerDriver driver. What is thecofiguration required for type 2 driver and what driver class filesrequired ?Its very urgent to me please reply.Ajay
View 1 Replies View RelatedHi,
I do not know if this is the right place, but do guide me if possible.
I am using JDBC driver to send data from SAP XI to MS SQL 2000.
The data is passed as an XML file, as below
<?xml version="1.0" encoding="UTF-8" ?>
- <ns0:MT_UPDATEPERSONACUSTOMERS xmlns:ns0="urndalesapcustomersupdatetopersona">
- <CallProc>
- <UPDATE_EndCustomerFromSAP action="EXECUTE">
<table>mk_INSERT_ConsultantFromSAP_proc</table>
<SAPID type="CHAR">0000206320</SAPID>
<DateCreated hasQuot="No" type="DATE">2007-04-24</DateCreated>
<FirstName type="CHAR">CAROL J</FirstName>
<LastName type="CHAR">VERITY</LastName>
<AccountGroup type="CHAR">Z001</AccountGroup>
<CustomerStatus type="INTEGER">1</CustomerStatus>
<Active type="INTEGER">1</Active>
<Blocked type="INTEGER">1</Blocked>
<AssocConsultant type="CHAR">NULL</AssocConsultant>
<Street1 type="CHAR">112 EVERGREEN SQUARE SW</Street1>
<Street2 type="CHAR">CHANGED ADDRESS LINE 1</Street2>
<City type="CHAR">Saint John</City>
<District type="CHAR">NULL</District>
<Region type="CHAR">NB</Region>
<PostalCode type="CHAR">E2L 4L1</PostalCode>
<DayPhone type="CHAR">306-781-4923</DayPhone>
<EveningPhone type="CHAR">306-761-2121</EveningPhone>
<Email type="CHAR">test@lllll.com</Email>
<CommunicationMethod type="CHAR">Day Phone</CommunicationMethod>
</UPDATE_EndCustomerFromSAP>
</CallProc>
</ns0:MT_UPDATEPERSONACUSTOMERS>
I know there is a way to pass paramters by name and hence sequence of parameters should not matter. So I was shocked and I still do not believe that when you call a stored procedure through JDBC adapter, the SEQUENCE of parameters have to be the same as defined in the stored procedure. I do not know if this is true for Oracle as well as MS SQL Server 2000, but I faced this problem on MS SQL 2k.
example - if stored proc 1 has params a, b, c and stored proc 2 has param d, c, a . My destination data type looks like a, b, c, d. Now if i decide to call proc 2, the JDBC adapter in SAP generates XML like a,c,d. I did a trace on SQL server and found that the JDBC driver uses sp_execute command, which my DBA told uses positional mapping and not named mapping.
I believe that SAP or the MS JDBC driver should support this.
So does anyone know, if there is an additional parameter which will make the JDBC driver to work with name and not position of paramters.
I have some long running stored procedures that I invoke from ADO.NET (using Typed Datasets).
Inside the stored procedure, I like to keep track of the execution by using the PRINT command.
Is there a way of extracting and displaying this PRINT information in .NET during the stored procedure execution?
Is oracle and Microsoft JDBC drivers are same? Oracle 9i comes with JDBC, can I use the same driver to access the Microsoft SQL Server?
View 1 Replies View RelatedHi
I just downloaded the Microsoft JDBC Driver for SQL Server 2000. Now it tells me SQL Server 7 is not supported. :o I already checked the JDBC Driver list on suns site. Its quite long and only states MS SQL Server without any version number. Can you recommend a particular driver? Preferrably without any cost. :D
Or am I better of with the jdbc/odbc bridge?
Thanks
Shabassa
Hi,
Does SQL Server Express ship with a JDBC driver? ... Hardly so, but is it available somewhere?
Rgds and thanks, PP
Hi guys,
Where can I download the JDBC Driver for our MS SQL Server 7 ?
Thank you.
Hello all,I have a question about the SQL Server JDBC driver. I was wondering ifanyone knows what the default prefetch size is (in number of rows).Also, does anyone know if an entire packet is sent (i.e. padded withnull values) if there is not enough data to fill it?Thanks for your help!ty
View 2 Replies View RelatedI need a free, redistributable JDBC driverfrom MS SQL Server. (MSDE2000a)
View 2 Replies View Relatedwhat is the jdbc driver for sql express? is it "sqljdbc"? then what is its "complete" name in the class path:"com.microsoft.sqlexpress.sqljdbc"? and for the conection url is it "jdbc:microsoft:sqlexpress://localhost/sqlexpress"?
View 3 Replies View RelatedHi Mixxo,
I also am having this problem! if you get an solution,
send me an answer or add my msn: griciolli@hotmail.com
When running a stored procedure, how can i retrieve the warnings that are issued within the stored procedure?
the code used is below,
the jdbc is connecting fine, it is running the stored procedure, but when an error is raised in the stored procedure, it is not coming back into s.getwarnings()
warning raised in stored proc with
Code Block
RAISERROR ('Error', 16, 1)with nowait;
there are no results for the stored procedure, I am just wanting to get the warnings
Code Block
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
Connection con = DriverManager.getConnection("jdbc:sqlserver://localh...........);
CallableStatement s = con.prepareCall("{call procedure_name}");
s.execute();
//running in seprate thread
SQLWarning warn = s.getWarnings();
while (m.running) {
if(warn == null) {
warn = s.getWarnings();
}
safeOut(warn);
if(warn != null)
warn = warn.getNextWarning();
m.wait(100);
}the code is not complete, but should show what is happening
it is continually outputting null for the warning, though the strored proc is definately raising errors, which is proven by running it in a query window in sql server.
it is retreiving warning if the statement is a raiseerror instead of a call to the proc
Code Block
CallableStatement s = con.prepareCall("RAISERROR ('Error', 1, 1)with nowait;");
this thread is quite related, but doesnt offer a working solution
Getting messages sent while JDBC Driver calls stored procedure
any help much appreciated,
thankyou
Simon
I keep getting this error on one of my pages when trying to do an update to the table via a stored procedure:
*System.Data.SqlClient.SqlException: Procedure or function 'sp_u_contractor_references' expects parameter '@p_contractorrfid', which was not supplied
I have verified that I am adding this parameter and that the spelling is correct. I have also made sure that the value is not NULL.
I'm confused as to what else the problem could be. Any ideas?
Hello, I am getting many of these messages in my server's event log (approximately 13 every 5 seconds or so). I have tried clearing the queue with "END CONVERSATION @ConvHandle WITH CLEANUP;" but the event log keeps getting messages. I have attached an example below.
Type: Information
Event Source: MSSQLSERVER
Event Category: (2)
Event ID: 9724
Date: 7/10/2007
Time: 3:52:37 PM
Description:
The activated proc [dbo].[SqlQueryNotificationStoredProcedure-32e779eb-edcb-44d1-ba30-93f46ef9d9f8] running on queue HoudiniPlatform.dbo.SqlQueryNotificationService-32e779eb-edcb-44d1-ba30-93f46ef9d9f8 output the following: 'Could not find stored procedure 'dbo.SqlQueryNotificationStoredProcedure-32e779eb-edcb-44d1-ba30-93f46ef9d9f8'.'
please help it's urgent i badly need the jdbc driver for mssql for my project
thank u
Hi at all, i'm new here, your communitiy seems too much preparated.
My question is simple...
i must to connect via jdbc to a ms sql server 2000 database, but i'm in doubt to select which jdbc driver to use. your suggest over microsoft'drivers? i found on the internet the open source drivers jdts...what about them?
thank you and excuse for by bad english! :p
I appear to have an issue here with WebLogic and SQL Server. The WebLogic server is set up to create a pool of connections (max 15, min 4) to the SQL server. The idea is that connections are "leased" to the application for the duration of a transaction and then returned to the pool at the end of the lease. The pool can grow to up to 15 simultaneous connections -- after that the applications must wait for the next available connection in the pool.
As activity subsides, connections are supposed to be dropped down to the minimum number of 4 connections (which are maintained open continuously).
The problem that I can see from SQL is that there are tens of connections that are open (should not be more than 15, right?) and some of these connections have been held open for several days (going back to the last time we rebooted the server 2 weeks ago). I am told that this is a relatively new phenomenon; new within the last 3 months, anyway. I have tried killing some of these processes manually and now they show up in "Current Activity" as being in a "Killed/Rollback" state.
Does anyone have any experience with JDBC/Weblogic and SQL? Any experiences similar to this?
More information:
SQL Server:
SQL Server 2000 (SP3)
Windows NT (SP6)
WebLogic Server:
Weblogic 6.1
Windows 2000 (SP4)
Weblogic JDBC Driver (6.1)
Any input would be welcome.
Regards,
hmscott
Hi, All!
I'm using Borland's ProcedureDataSet to retrieve data from a SQL 2005 database using the JDBC 1.2 Driver.
My problem is with the money data type. The Borland's component translate money into BigDecimal and if the value
in the database in NULL then I get a NullPointerException.
On MSDN I saw money was referred as Double which is wrong!
Any Ideas?
Hi;
I used SQLServerDatabaseMetaData (which requires a connection to return this data which seems unnecessary) to get:
name: Microsoft SQL Server 2005 JDBC Driver
version: 1.1.1501.101
Passing a url of: jdbc:sqlserver://localhost:1433;DatabaseName=Northwind;IntegratedSecurity=true
I get the error:
Oct 7, 2006 3:46:09 PM com.microsoft.sqlserver.jdbc.AuthenticationJNI <clinit>
WARNING: Failed to load the sqljdbc_auth.dll
Exception in thread "main" com.microsoft.sqlserver.jdbc.SQLServerException: This driver is not configured for integrated authentication.
I copied over x86sqljdbc_auth.dll and then it worked. I assume this is required with the jar file. This leads to a couple of questions:
What are the files xax86sqljdbc_xa.dll & x64 files for? Are they needed?
The jar file can just travel with my java code. Should the dll do the same or does it need to be in the path and/or the default directory?
Are there any other files needed or just these two?
thanks - dave
Hello,
We are deploying our application with the v1.1 JDBC driver, but are getting a little worried with some of the results of our recent stress tests. I wanted to see if anyone could offer any insight into this problem. Also, do you have any expectation for a release date for the 1.2 driver (not CTP).
Under heavy load with a number of concurrent processes loading to a few tables in our database through batched INSERT statements (called executeBatch against a prepared statement), we see these exceptions coming back from the driver:
com.vectorsgi.tw.services.exception.LoaderException: java.lang.NullPointerException
at com.vectorsgi.tw.loader.X937HibernateLoader.loadToDatabase(X937HibernateLoader.java:813)
at com.vectorsgi.tw.loader.X937HibernateLoader.loadToDatabase(X937HibernateLoader.java:482)
at com.vectorsgi.tw.loader.X937HibernateLoader.loadToDatabase(X937HibernateLoader.java:466)
at com.vectorsgi.viex.workflow.capture.LoadListener.execute(LoadListener.java:91)
at com.vectorsgi.viex.workflow.capture.LoadListener.actionFired(LoadListener.java:55)
at fluximpl.JavaActionImpl.execute(JavaActionImpl.java:6)
at fluximpl.gl.a(gl.java:348)
at fluximpl.gl.b(gl.java:417)
at fluximpl.gl.a(gl.java:236)
at fluximpl.dm.d(dm.java:58)
at fluximpl.tlb.run(tlb.java:10)
Caused by: java.lang.NullPointerException
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecuteBatchPreparedStatement(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PreparedStatementBatchExecutionRequest.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.SQLServerPreparedStatement.executeBatch(Unknown Source)
at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeBatch(NewProxyPreparedStatement.java:1723)
at com.vectorsgi.tw.loader.X937HibernateLoader.loadItems(X937HibernateLoader.java:1326)
at com.vectorsgi.tw.loader.X937HibernateLoader.loadToDatabase(X937HibernateLoader.java:637)
... 10 more
In a test of 20 "jobs" 2 failed with this error, so its certainly not frequent, but not fleeting either. What is also strange about this is that after the two failures (not immediately back to back but within a few minutes of each other) we got a primary key constraint error:
com.vectorsgi.tw.services.exception.LoaderException: java.sql.BatchUpdateException: com.microsoft.sqlserver.jdbc.SQLServerException: Violation of PRIMARY KEY constraint 'PK__X937_ITEM__0E7B75BC'. Cannot insert duplicate key in object 'X937_ITEM'.
at com.vectorsgi.tw.loader.X937HibernateLoader.loadToDatabase(X937HibernateLoader.java:813)
at com.vectorsgi.tw.loader.X937HibernateLoader.loadToDatabase(X937HibernateLoader.java:482)
at com.vectorsgi.tw.loader.X937HibernateLoader.loadToDatabase(X937HibernateLoader.java:466)
at com.vectorsgi.viex.workflow.capture.LoadListener.execute(LoadListener.java:91)
at com.vectorsgi.viex.workflow.capture.LoadListener.actionFired(LoadListener.java:55)
at fluximpl.JavaActionImpl.execute(JavaActionImpl.java:6)
at fluximpl.gl.a(gl.java:348)
at fluximpl.gl.b(gl.java:417)
at fluximpl.gl.a(gl.java:236)
at fluximpl.dm.d(dm.java:58)
at fluximpl.tlb.run(tlb.java:10)
Caused by: java.sql.BatchUpdateException: com.microsoft.sqlserver.jdbc.SQLServerException: Violation of PRIMARY KEY constraint 'PK__X937_ITEM__0E7B75BC'. Cannot insert duplicate key in object 'X937_ITEM'.
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeBatch(Unknown Source)
at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeBatch(NewProxyPreparedStatement.java:1723)
at com.vectorsgi.tw.loader.X937HibernateLoader.loadItems(X937HibernateLoader.java:1333)
at com.vectorsgi.tw.loader.X937HibernateLoader.loadToDatabase(X937HibernateLoader.java:637)
... 10 more
Now- I realize that this is very straight forward, but we are almost certain that our key generation code cannot generate a duplicate key. We have been running it 24x7 for three years at a number of customer sites without ever getting a duplicate (under the open-source jTDS driver). Now that we are trying to move to a better driver that is actively supported, we see these primary key violations every once in a while. Its very hard to consistently replicate, but we have received it from 5 of the past 20 stress tests.
I mainly want insight into the NullPointerException, but if there is any possibility that a driver issue could be contributing to my primary key problem--that would sure make a lot of sense. (I realize its a long shot!)
Thanks,
Steve Ash
Software Developer
Metavante Image Solutions
Hi;
I am having problem figuring out if the JDBC Driver for MS SQL Server 2005 is faster or setting up a data source from Administrative tools/Data Sources. I have run several tests and in all tests, the odbc datasource is faster. In that case, why having the trouble of downloading a driver if I am not wrong. I have understood that the native driver is always faster than the odbc bridge.
Thanks in advance
Hi all, I am building a program using Java and SQL express and I need the driver in order to connect to the database. I have downloaded I think a version for sql server 2005 but it did not work. Can somebody tell me if such driver exists?
Thank you all