SQL Server Query not working when executed from PHP

Question

I've got a set of triggers in my table in SQL Server and when I execute the queries in SQL Server Management Studio they work fine. But when they're executed from my php files they doesn't take effect. I used SQL Server Profiler and the trigger gets to the last line of execution that is the insert in my log table but then after this I get an Attention error. Here's my trigger for a delete statement:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- Batch submitted through debugger: SQLQuery25.sql|7|0|C:\Users\ADMINI~1\AppData\Local\Temp\3\~vsB4EE.sql

ALTER TRIGGER [dbo].[OperationStructureFields_delete]
ON [dbo].[OperationStructureFields]
FOR DELETE
AS
BEGIN
    DECLARE
        @id INT,
        @result varchar(MAX),
        @user varchar(MAX),
        @LoopCounter INT = 1, 
        @MAX INT, 
        @Column NVARCHAR(100),
        @Type NVARCHAR(100),
        @Value NVARCHAR(100),
        @ValueXML xml,
        @Sql NVARCHAR(MAX),
        @Tmp NVARCHAR(MAX),
        @LoopCounter2 INT = 1,
        @MAX2 INT,
        @Message nvarchar(2048)
    SELECT @user = system_user
    SELECT @MAX = MAX(ORDINAL_POSITION) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'OperationStructureFields'
    SELECT @MAX2 = MAX(ID) FROM deleted
    SELECT @LoopCounter2 = MIN(ID) FROM deleted
    Select * into #deleted from deleted
    WHILE(@LoopCounter2 <= @MAX2)
    BEGIN
        SET @LoopCounter = 1
        SET @result = '{'
        WHILE(@LoopCounter <= @MAX)
        BEGIN
           SELECT @Column = COLUMN_NAME, @Type = DATA_TYPE
           FROM INFORMATION_SCHEMA.COLUMNS WHERE ORDINAL_POSITION = @LoopCounter and TABLE_NAME = 'OperationStructureFields'
           if (@Type = 'xml')
           BEGIN
            SET @Sql = 'SELECT @ValueXML = ' + @Column + ' FROM #deleted where ID=' + CONVERT(varchar(200),@LoopCounter2,0)
            exec sp_executesql @Sql, N'@ValueXML xml out', @ValueXML out
            SET @Value = CONVERT(VARCHAR(MAX),@ValueXML,0)
           END
           ELSE
           BEGIN
             SET @Sql = 'SELECT @Value = ' + @Column + ' FROM #deleted where ID=' + CONVERT(varchar(200),@LoopCounter2,0)
             exec sp_executesql @Sql, N'@Value varchar(MAX) out', @Value out 
           END    
           IF (@Value is not null or @Value != '') and  (@Type = 'datetime2' or @Type = 'datetime' or @Type = 'date')
           BEGIN
                IF @result = '{'
                BEGIN
                    SET @result = @result + ' "' + @Column + '":"' + CONVERT(VARCHAR(20),@Value,120) + '"'
                END
                ELSE
                BEGIN
                    SET @result = @result + ', "' + @Column + '":"' + CONVERT(VARCHAR(20),@Value,120) + '"'
                END
           END
           ELSE IF (@Value is not null or @Value != '')
           BEGIN
                IF @result = '{'
                BEGIN
                    SET @result = @result + ' "' + @Column + '":"' + @Value + '"'
                END
                ELSE
                BEGIN
                    SET @result = @result + ', "' + @Column + '":"' + @Value + '"'
                END
           END
           SET @LoopCounter  = @LoopCounter + 1 
        END
        SET @result = @result + '}'
        INSERT INTO sys_logs (username,datahora,tabela,[object_id],[action],oldvalue) values (@user,GETDATE(),'OperationStructureFields',@LoopCounter2,'DELETE',@result)
        delete from #deleted where ID = @LoopCounter2
        select @LoopCounter2 = MIN(ID) from #deleted where ID > @LoopCounter2  
    END
END

And a print of my SQL Server Profiler:

The PHP code is:

$res = sqlsrv_query($connection, $_sql, array(), array('Scrollable' => 'buffered'));

and the variable $_sql has the following value:

DELETE FROM OperationStructureFields WHERE ID= '66817'

And it doesn't return any errors.

The PHP version is 5.5.16.

I executed the SELECT @@OPTIONS inside my php file and got the following options:

ANSI_WARNINGS
ANSI_PADDING
ANSI_NULLS
QUOTED_IDENTIFIER
ANSI_NULL_DFLT_ON
CONCAT_NULL_YIELDS_NULL

The only difference to the SQL Server Management Studio is the option ARITHABORT that is ON in the SQL Server Management Studio.

Do you have any ideia what may be causing this?

UPDATES:

I believe it may be something on my PHP settings. In my phpinfo() I've got the following settings for sqlsrv:

Also in SQL Server Profiler I've got the following definitions in Audit Login:

-- network protocol: LPC
set quoted_identifier on
set arithabort off
set numeric_roundabort off
set ansi_warnings on
set ansi_padding on
set ansi_nulls on
set concat_null_yields_null on
set cursor_close_on_commit off
set implicit_transactions off
set language us_english
set dateformat mdy
set datefirst 7
set transaction isolation level read committed

Anton · Accepted Answer

Messages

Changed database context to XXXXXXX

Changed language setting to YYYYYYY

are not error messages. They are informational messages and should be ignored by applications. PHP can be configured to ignore it.

Severity Levels: https://msdn.microsoft.com/en-us/library/ms164086.aspx

PHP Settings:

1) php.ini changes:

mssql.min_error_severity = 11
mssql.min_message_severity = 11

or

2) by executing

mssql_min_error_severity(11);

just before execution of mssql_query()

http://php.net/manual/en/function.mssql-min-error-severity.php

UPDATE:

3) The trigger should have

set nocount on;

at the top of code, just after:

AS
BEGIN

This will prevent sending multiple informational messages (like "xx rows affected") to the application.

SQL Server Query not working when executed from PHP

Answers (2)

Related Questions