title: JDBC SQLException for SQL Server
body: <p>I have this piece of code which works well on DB2 and Oracle, but fails on MSSQL(both 2005 and 2008).</p>
  
  <pre><code>c = SessionMgr.getInstance().getTOPLinkManager().getDataSource().getConnection();
  String sql = "";
  sql = "{CALL " + TOPLinkManager.getSchemaPrefix() + "CONTACT_REFERENCES (?, ?, ?)}";
  cs = c.prepareCall(sql);
  cs.setInt(1, dateType);
  cs.setDate(2, date);
  cs.setInt(3, operator);
  cs.execute();
  cs.close();
  sql= "SELECT FIRST_NAME,LAST_NAME,CREATED,LASTMODIFIED FROM CONTACT WHERE EXISTS(SELECT * FROM CONTACT_REF WHERE CONTACT.SEQID = CONTACT_REF.SEQID AND CONTACT.SITEID = CONTACT_REF.SITEID)";
  ps = c.prepareStatement(sql);
  ResultSet rs = ps.executeQuery();
  </code></pre>
  
  <p>The stored procedure called populates the table <code>CONTACT_REF</code>, which is used in the query to get data from the server.<br/></p>
  
  <p>The exception thrown is:<br/></p>
  
  <pre><code>com.microsoft.sqlserver.jdbc.SQLServerException: The server failed to resume the transaction. Desc:3c00000002.
  at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:196)
  at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1454)
  at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:388)
  at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:338)
  at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:4026)
  at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:1416)
  at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:185)
  at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:160)
  at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeQuery(SQLServerPreparedStatement.java:281)
  at weblogic.jdbc.wrapper.PreparedStatement.executeQuery(PreparedStatement.java:100)
  at marketsoft.tools.purge.actions.PurgeContactAction.checkReferences(PurgeContactAction.java:126)
  </code></pre>
  
  <p>This is the stored procedure being called:</p>
  
  <pre><code>IF OBJECT_ID ('CONTACT_REFERENCES', 'P') IS NOT NULL
     DROP PROCEDURE CONTACT_REFERENCES
  go
  
  CREATE PROCEDURE CONTACT_REFERENCES
  @datetype int,
  @created DATETIME,
  @operator int
  AS
  BEGIN
  BEGIN TRANSACTION
  
  CREATE TABLE #DUP_REF
  (
  SEQID NUMERIC(19,0),
  SITEID NUMERIC(10,0)
  )
  
  DELETE FROM PURGE_CONTACT;
  DELETE FROM CONTACT_REF;
  IF @datetype = 0 BEGIN
  IF @operator = 0 BEGIN
  insert into PURGE_CONTACT SELECT SEQID,SITEID,DETAILID,DETAILSITE from CONTACT WHERE CREATED &lt; @created;
  END
  
  ELSE IF @operator = 1 BEGIN
  insert into PURGE_CONTACT SELECT SEQID,SITEID,DETAILID,DETAILSITE from CONTACT WHERE DATEPART(yy,CREATED) = DATEPART(yy,@created)
                                                              AND DATEPART(mm,CREATED) = DATEPART(mm,@created)
                                                              AND DATEPART(dd,CREATED) = DATEPART(dd,@created);
  END
  
  ELSE IF @operator = 2 BEGIN
  insert into PURGE_CONTACT SELECT SEQID,SITEID,DETAILID,DETAILSITE from CONTACT WHERE CREATED &gt; @created;
  END
  END
  ELSE IF @datetype = 1 BEGIN
  IF @operator = 0 BEGIN
  insert into PURGE_CONTACT SELECT SEQID,SITEID,DETAILID,DETAILSITE from CONTACT WHERE LASTMODIFIED &lt; @created;
  END
  
  ELSE IF @operator = 1 BEGIN
  insert into PURGE_CONTACT SELECT SEQID,SITEID,DETAILID,DETAILSITE from CONTACT WHERE DATEPART(yy,LASTMODIFIED) = DATEPART(yy,@created)
                                                              AND DATEPART(mm,LASTMODIFIED) = DATEPART(mm,@created)
                                                              AND DATEPART(dd,LASTMODIFIED) = DATEPART(dd,@created);
  END
  
  ELSE IF @operator = 2 BEGIN
  insert into PURGE_CONTACT SELECT SEQID,SITEID,DETAILID,DETAILSITE from CONTACT WHERE LASTMODIFIED &gt; @created;
  END
  END
  
  
  INSERT INTO #DUP_REF SELECT SEQID,SITEID
  FROM PURGE_CONTACT
  WHERE EXISTS (SELECT *
                FROM LEAD
                WHERE LEAD.CONTACTID = PURGE_CONTACT.SEQID
                      AND LEAD.CONTACTSITE = PURGE_CONTACT.SITEID);
  
  INSERT INTO #DUP_REF SELECT SEQID,SITEID
  FROM PURGE_CONTACT
  WHERE EXISTS (SELECT *
                FROM MAPRESULT
                WHERE MAPRESULT.CONTACTSEQ = PURGE_CONTACT.SEQID
                      AND MAPRESULT.CONTACTSITE = PURGE_CONTACT.SITEID);
  
  INSERT INTO #DUP_REF SELECT SEQID,SITEID
  FROM PURGE_CONTACT
  WHERE EXISTS (SELECT *
                FROM USERS
                WHERE USERS.CONTACTID = PURGE_CONTACT.SEQID
                      AND USERS.CONTACTSITE = PURGE_CONTACT.SITEID);
  
  INSERT INTO #DUP_REF SELECT SEQID,SITEID
  FROM PURGE_CONTACT
  WHERE EXISTS (SELECT *
                FROM PLAYER
                WHERE PLAYER.CONTACTID = PURGE_CONTACT.SEQID
                      AND PLAYER.CONTACTSITE = PURGE_CONTACT.SITEID);
  
  INSERT INTO #DUP_REF SELECT SEQID,SITEID
  FROM PURGE_CONTACT
  WHERE EXISTS (SELECT *
                FROM SHOPPINGCART
                WHERE SHOPPINGCART.CONTACT_SEQID = PURGE_CONTACT.SEQID
                      AND SHOPPINGCART.CONTACT_SITEID = PURGE_CONTACT.SITEID);
  
  INSERT INTO CONTACT_REF SELECT DISTINCT SEQID,SITEID FROM #DUP_REF;
  
  DROP TABLE #DUP_REF;
  
  DELETE FROM PURGE_CONTACT
  WHERE EXISTS (SELECT *
                FROM CONTACT_REF
                WHERE CONTACT_REF.SEQID = PURGE_CONTACT.SEQID
                      AND CONTACT_REF.SITEID = PURGE_CONTACT.SITEID);
  
  COMMIT
  
  END
  go
  </code></pre>