CODEWITHSUNDEEP
sqlsql-serverbatch-filesql-server-2014-express
user4356
user4356

Reputation: 11

SQL Server trigger is stuck on executing

I am trying to run a batch file in a SQL Server trigger to get information from a query and put it in a text file. I am trying to do this because I want to do stuff with the information in that same batch file later on. The problem is that when the trigger is called, it stays stuck on executing.

I have tested the query and batch file call in Microsoft SQL Server Management Studio and they both work but when I call the batch file in the trigger is stays stuck at executing.

Here is my code. First the batch file, then trigger, query the batch file is calling, and my query to test the trigger

@echo off
echo start
sqlcmd -S AZ7GH2\SQLEXPRESS -h -1 -i C:\Users\user1\Documents\test3.sql -o C:\Users\user1\Documents\test.txt
echo end
exit

SQL Server trigger

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER TRIGGER [dbo].[ffupdate] 
ON [dbo].[feat] 
AFTER UPDATE
AS
BEGIN
    IF UPDATE (act)
    BEGIN
        EXEC xp_CMDShell 'C:\Users\user1\Documents\ffscript.bat'
    END
END

test3.sql (query being called by batch file)

:setvar SQLCMDERRORLEVEL 1
SET NOCOUNT ON

USE [dev]

DECLARE @ver INT
SET @ver = CHANGE_TRACKING_CURRENT_VERSION() - 1

CREATE TABLE #ctb(fuid INT)  

INSERT INTO #ctb
    SELECT featid
    FROM CHANGETABLE(CHANGES feat, @ver) AS tb

SELECT fl.flg
FROM fl, #ctb
WHERE fl.fid = #ctb.fuid
GO

:setvar SQLCMDERRORLEVEL 0
SET NOCOUNT OFF

Query to test trigger

USE [dev]

UPDATE feat
SET act = 0
WHERE featid = 1;

I don't know what is wrong. I have looked for an answer and can't find one. Like I said, everything works fine by itself but when put together it stays stuck at executing. Any help would be greatly appreciated.

Upvotes: 1

Views: 1045

Answers (2)

Toto
Toto

Reputation: 1

For future reference... you might want to consider adding "COMMIT" at the end of your input file to make sure no tables are being locked for change.

Upvotes: 0

EchoMike444
EchoMike444

Reputation: 1692

You are locking yourself !

You have a trigger on the table FEAT , and when you update one row ( session A ) , you create a new session ( session B via the batch ) that try to read some capture information on the table FEAT .

Upvotes: 0

Related Questions