CODEWITHSUNDEEP
phpsql-serversql-server-2008
Emilio Gort
Emilio Gort

Reputation: 3470

How to debug error 102 severity 15 state 1

How can find a solution to fix a SP that when I run from php I get Error 102 severity 15 state 1 PHP:

$stmt = mssql_init('DBO.TEST_sp_SET_Claim Status');
mssql_bind($stmt, '@ClaimID', $claimID, SQLINT4);
mssql_bind($stmt, '@Status', $status, SQLINT4);
mssql_bind($stmt, '@UserID', $userID, SQLINT4);
$result = mssql_execute($stmt);

SP:

  @ClaimID INT,
  @Status INT,
  @UserID INT
AS
BEGIN
  SET NOCOUNT ON;
  --Start : Update claim Status.
  UPDATE    DBO.Payment
      SET StatusID = @Status, 
        ClosedBy = CASE 
                WHEN @Status = 2 THEN @UserID
                WHEN @Status = 13 THEN @UserID
                WHEN @Status = 14 THEN @UserID
                WHEN @Status = 16 THEN @UserID
                ELSE NULL 
              END ,
        UserID = @UserID, 
        DateClosed = CASE 
                WHEN @Status = 2 THEN GETDATE()
                WHEN @Status = 13 THEN GETDATE()
                WHEN @Status = 14 THEN GETDATE()
                WHEN @Status = 16 THEN GETDATE()
                ELSE NULL 
              END
  FROM  DBO.ClaimHeader P_CH
      INNER JOIN DBO.Payment P_PI ON P_CH.ClientClaimID = P_PI.ClientClaimID
  WHERE P_CH.ClientClaimID  = @ClaimID              
  --End

END

In the SQL Server Profiler I get

Exception           Error 102 severity 15 state 1
SQL:BatchStarting   EXEC DBO.TEST_sp_SET_Claim Status @ClaimID=20,@Status=4,@UserID=22
SQL:BatchCompleted  EXEC DBO.TEST_sp_SET_Claim Status @ClaimID=20,@Status=4,@UserID=22

When I run directly in sql it works fine

DECLARE @return_value int

EXEC    @return_value = [dbo].[TEST_sp_SET_Claim Status]
    @ClaimID = 20,
    @Status = 4,
    @UserID = 22

SELECT  'Return Value' = @return_value

When I run the query directly from php it works fine too like

     $query="
  DECLARE @Status INT, @UserID INT, @ClaimID INT
  SET @Status=$status
  SET @UserID = $userID
  SET @ClaimID = $claimID
  UPDATE    DBO.Payment
      SET StatusID = @Status, 
        ClosedBy = CASE 
                WHEN @Status = 2 THEN @UserID
                WHEN @Status = 13 THEN @UserID
                WHEN @Status = 14 THEN @UserID
                WHEN @Status = 16 THEN @UserID
                ELSE NULL 
              END ,
        UserID = @UserID, 
        DateClosed = CASE 
                WHEN @Status = 2 THEN GETDATE()
                WHEN @Status = 13 THEN GETDATE()
                WHEN @Status = 14 THEN GETDATE()
                WHEN @Status = 16 THEN GETDATE()
                ELSE NULL 
              END
  FROM  DBO.ClaimHeader P_CH
      INNER JOIN DBO.Payment P_PI ON P_CH.ClientClaimID = P_PI.ClientClaimID
  WHERE P_CH.ClientClaimID  = @ClaimID  
       ";
 $result=mssql_query($query);

The only thing I can think is the space in the name TEST_sp_SET_Claim Status, but this Sp is also used by other applications and it works fine, I cant change...could be another thing?

Upvotes: 1

Views: 4213

Answers (1)

Emilio Gort
Emilio Gort

Reputation: 3470

The problem was the SP name DBO.TEST_sp_SET_Claim Status, there is one space just before Status. I fixed just enclosed the SP name in square brackets []

$stmt = mssql_init('[DBO].[TEST_sp_SET_Claim Status]');

It's advisable the use of square brackets to avoid this kind of problem.

If the columns name or Sp name contains spaces or conflicts with a reserved word

List of Reserved Keywords in SQL-SERVER

Upvotes: 1

Related Questions