title: Unable to execute stored Procedure using Java and JDBC on SQL server
body: <p>I have been trying to execute a MS SQL Server stored procedure via JDBC today and have been unsuccessful thus far.  The stored procedure has 1 input and 1 output parameter.  With every combination I use when setting up the stored procedure call in code I get an error stating that the stored procedure couldn't be found.  I have provided the stored procedure I'm executing below (NOTE:  this is vendor code, so I cannot change it).</p>
  
  <pre><code>set ANSI_NULLS ON
  set QUOTED_IDENTIFIER ON
  GO
  
  
  ALTER PROC [dbo].[spWCoTaskIdGen] 
  @OutIdentifier int OUTPUT
  AS
  
  BEGIN
  DECLARE @HoldPolicyId int
  DECLARE @PolicyId char(14)
  
  IF NOT EXISTS
  (
  SELECT *
  FROM UniqueIdentifierGen (UPDLOCK)
  )
  INSERT INTO UniqueIdentifierGen VALUES (0)
  
  UPDATE UniqueIdentifierGen 
  SET 
      CurIdentifier = CurIdentifier + 1
  
  SELECT @OutIdentifier = 
      (SELECT CurIdentifier
      FROM UniqueIdentifierGen)
  END
  </code></pre>
  
  <p>The code looks like:</p>
  
  <pre><code> CallableStatement statement = connection
              .prepareCall("{call dbo.spWCoTaskIdGen(?)}");
      statement.setInt(1, 0);
      ResultSet result = statement.executeQuery();
  </code></pre>
  
  <p>I get the following error:  SEVERE: Could not find stored procedure 'dbo.spWCoTaskIdGen'.</p>
  
  <p>I have also tried</p>
  
  <pre><code>    CallableStatement statement = connection
              .prepareCall("{? = call dbo.spWCoTaskIdGen(?)}");
      statement.registerOutParameter(1, java.sql.Types.INTEGER);
      statement.registerOutParameter(2, java.sql.Types.INTEGER);
      statement.executeQuery();
  </code></pre>
  
  <p>The above results in:  SEVERE: Could not find stored procedure 'dbo.spWCoTaskIdGen'.</p>
  
  <p>I have also tried:</p>
  
  <pre><code>    CallableStatement statement = connection
              .prepareCall("{? = call spWCoTaskIdGen(?)}");
      statement.registerOutParameter(1, java.sql.Types.INTEGER);
      statement.registerOutParameter(2, java.sql.Types.INTEGER);
      statement.executeQuery();
  </code></pre>
  
  <p>The code above resulted in the following error:  Could not find stored procedure 'spWCoTaskIdGen'.</p>
  
  <p>Finally, I should also point out the following:  </p>
  
  <ol>
  <li><p>I have used the MS SQL Server Management Studio tool and have been able to successfully run the stored procedure.  The sql generated to execute the stored procedure is provided below:</p>
  
  <pre><code>GO
  DECLARE @return_value int;
  DECLARE @OutIdentifier int;
  EXEC @return_value = [dbo].[spWCoTaskIdGen] @OutIdentifier = @OutIdentifier OUTPUT;
  SELECT
      @OutIdentifier [@OutIdentifier],
      @return_value [Return Value];
  GO
  </code></pre></li>
  <li><p>The code being executed runs with the same user id that was used in point #1 above.</p></li>
  <li>In the code that creates the Connection object I log which database I'm connecting to and the code is connecting to the correct database.</li>
  </ol>
  
  <p>Any ideas?</p>
  
  <p>Thank you very much in advance.</p>