What causes SQL Server to return the message 'The statement has been terminated'?

Question

I have a very simple INSERT statement being executed from a PHP script running on a Linux Apache web server. I can run the query fine from within SQL Management Studio and it normally runs fine from PHP as well. However, every once in awhile I get an error message from my PHP script that the query failed and the mssql_get_last_message() function returns 'The statement has been terminated'.

What sources can cause this message to be returned from SQL Server?

Laxative

Answer 1

You have found one of the most annoying parts of SQL Server. There are situations where an error can be raised, and SQL will generated two error messages: the first to explain what the error was, and the second to say something useful like "The statement has been terminated" (which, technically, is error number 3621). The thing is that SQL, and most everything else that touches it--such as PHP--can only see/pick up/process/or otherwise utilize that last unlcear error message. The one that's actually useful gets lost.

The quick way to figure out what's going on is to run the sequence of commands leading up to the error from SSMS. This, apparently, will not work for you.

A fussier way to figure it out is to fire up SQL Profiler to track the Exception event, and then run your process. This should show all errors that occured. Tossing in relevant other events (SP:Starting, SP:StmtStarting, SQL:BatchStarting, whatever is applicable to the code your submitting to the database) will show which command is raising the error.

Intertidal

Answer 2

To get a numeric error code from mssql you can do a select that looks something like

SELECT @@ERROR AS ErrorCode
Which SHOULD return the correct error code.

You can also try this code which is posted on PHP.NET.

function query($sQuery, $hDb_conn, $sError, $bDebug)
{
    if(!$rQuery = @mssql_query($sQuery, $hDb_conn))
    {
        $sMssql_get_last_message = mssql_get_last_message();
        $sQuery_added  = "BEGIN TRY\n";
        $sQuery_added .= "\t".$sQuery."\n";
        $sQuery_added .= "END TRY\n";
        $sQuery_added .= "BEGIN CATCH\n";
        $sQuery_added .= "\tSELECT 'Error: '  + ERROR_MESSAGE()\n";
        $sQuery_added .= "END CATCH";
        $rRun2= @mssql_query($sQuery_added, $hDb_conn);
        $aReturn = @mssql_fetch_assoc($rRun2);
        if(empty($aReturn))
        {
            echo $sError.'. MSSQL returned: '.$sMssql_get_last_message.'.<br>Executed query: '.nl2br($sQuery);
        }
        elseif(isset($aReturn['computed']))
        {
            echo $sError.'. MSSQL returned: '.$aReturn['computed'].'.<br>Executed query: '.nl2br($sQuery);
        }
        return FALSE;
    }
    else
    {
        return $rQuery;
    }
}

Postglacial

So with your example, if I detect my 'The statement has been terminated' message I should be able to submit the query you provided to get the error code? – Laxative

Nevermind, your solution in theory sounds good. However, the @@ERROR variable will get overwritten if another query hits the database before you have a chance to query it. On a busy server this is highly likely. – Laxative

The issue you are having is a known problem with the mssql driver apparently. Look at php.net/manual/en/function.mssql-get-last-message.php, especially the comments at the bottom. Folks have been struggling with this issue for a long time. – Postglacial

The try/catch block retains the scope for things like @@error or error_message() in sql server so you would be catching the correct error. However, since mssql_query is called twice (once in the IF, the other in the body of the IF) those might actually be two different errors because they were called at different times. I would suggest just putting the try/catch around the query before running it at all, give the error message a distinct alias and after running the query, check specifically for that alias to see if an error was produced. – Millardmillboard

this is a reference on the behavior of a try/catch in sql server: msdn.microsoft.com/en-us/library/ms175976.aspx – Millardmillboard

Answer 3

You can use the code in the message to know which is the error. For example:

[2627: The statement has been terminated.]

In this case, the error code is 2627, so if you execute the sql below you'll know the message

SELECT msg.text
  FROM sys.messages msg 
 INNER JOIN sys.syslanguages lng ON lng.msglangid = msg.language_id
 WHERE msg.message_id = 2627
   AND lng.alias = 'English'

Violation of %ls constraint '%.*ls'. Cannot insert duplicate key in object '%.*ls'. The duplicate key value is %ls.

This is a way to know the right message error. In my example the error is violation of primary key

Wideranging