3

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:

Prt Screen do 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:

phpinfo();

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
6
  • I don't have much experience with SQL Server via PHP, but it looks like your options array may not be right. 'buffered' is not one of the values listed in the documentation. I don't know if that would cause this problem, though. Commented Aug 25, 2016 at 15:32
  • @Don'tPanic it isn't that option that's causing the errors. :s Commented Aug 25, 2016 at 15:42
  • is it possible that you are dealing with permissions issues across databases? Is sys_logs a local table in your DB or other? Commented Aug 30, 2016 at 14:41
  • @Dave I'm using the same user/password in PHP and SQL Server Management Studio. Commented Aug 30, 2016 at 14:52
  • @Dave I've updated my question. Commented Aug 31, 2016 at 17:17

2 Answers 2

1
+50

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.

Sign up to request clarification or add additional context in comments.

7 Comments

I was more concerned with the Attention error. Something isn't right because I can run the statement in SQL Server Management Studio without problems but with PHP it fails without error messages. :S
I've changed those settings just in case but the error persists.
Could be other type of setting?
Do you use transaction when you make a call to mssql? Is there a change that transaction is not committed? Also could you add 'set nocount on;' to the top of your trigger - it's good practice to have it for SPs and triggers.
I tried the set nocount on in the beginning of my trigger and it seems to work now. I'm still doing some tests but seems like that was the problem. Can you update your answer so I can then give you the +50 ?
|
0

Each of the application SQL drivers (including SSMS) have standard SET Options. Those can be overridden, but by default, the driver is going to pass certain setting values. (If you don't know what I am talking about take a look at this article to get an idea of what the options are and to find out how to determine which options are being used.

What I would suggest would be to determine what SET Options your application is using, then mimic those in SSMS and then troubleshoot from there. Your other option would be to go ahead and perform set statements from your app to be consistent with SSMS's default. We did something similar to that in a Node.js application a few years because we were getting a number of errors returned from procs that ran fine in .Net. Once we added statements in Node to parrot .Net everything worked fine.

I am not positive that your issue is due to SET Options, but I think it is likely.

7 Comments

The only difference was the ARITHABORT option and I tried turning that on but the error persists.
Have you tried executing in SSMS with the same login that your PHP application is using?
I'm using the same login as the PHP application :s
This is a long shot and it's kind of hard to set up a test for it on my end, but are you connecting to the same database through both SSMS and PHP? I am not sure that would affect anything, but I am wondering if that could possible change the database context on anything in the trigger. Does your app connect to that same database? This is a shot in the dark but I cannot come up with anything else if your SET options are the same and your login is the same.
It's the same database, I've checked that.
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.