1

I am using a FileTable in SQL Server 2014 and need to run an executable that parses the file name of any inserted/updated/deleted file and then in turn the executable inserts into other tables on the database the information that was parsed from the name. I do not expect the .exe to run long at all but if it runs into issues, I do not want to lock it for an extended period of time.

For instance:

CREATE PROCEDURE filename_parser
    @name nvarchar(255)
AS
BEGIN

    DECLARE @exe nvarchar(255)
    SET @exe = 'c:\test\my.exe "' + @name + '"'
    EXEC master..xp_cmdshell @exe

END
GO

If I run the stored procedure from an INSERT or UPDATE trigger, for instance:

USE [db_1]
GO
CREATE TRIGGER [dbo].[i_table_a]

    ON 
        [dbo].[table_a]
    AFTER
        INSERT

AS

    DECLARE @file nvarchar(255)

    SELECT TOP 1
        @file = name
    FROM 
        inserted

    EXEC filename_parser @name = @file

will I end up locking table_a until the executable completes? Sorry, if the answer is obvious. I have not found a straight forward answer. Any help/pointing in the appropriate direction is appreciated.

Related links:

Do stored procedures lock tables/rows?

SQL Server - How to lock a table until a stored procedure finishes

Community
  • 1
  • 1
interesting-name-here
  • 1,851
  • 1
  • 20
  • 33
  • Do you have the source code for your .exe? Maybe a try/catch would gracefully handle any issues, log them (so you can analyze, debug later), and quickly exit. – tgolisch Jun 07 '16 at 14:39
  • Indeed, I have try/catches. It does logging quick enough I assume. I am starting to add many triggers to databases though as they are necessary for me to upgrade from older systems to newer ones while older systems data is still being created and the newer systems need the data. While I see your point, the more I think about all that needs done, the more the core question pertains to my situation, which is, whether or not the trigger locks the table until the stored procedure completes. – interesting-name-here Jun 07 '16 at 15:03
  • Microsoft docs say xp_cmdshell will run synchronously. Triggers run synchronously too. So, if your exe gets stuck, it will hang the trigger, which will hang the insert, and other stuff. https://msdn.microsoft.com/en-us/library/ms175046.aspx#remarks – tgolisch Jun 07 '16 at 21:19
  • Post as answer and I'll accept – interesting-name-here Jun 08 '16 at 12:25

1 Answers1

1

Microsoft docs say xp_cmdshell will run synchronously. Triggers run synchronously too. So, if your exe gets stuck, it will hang the trigger, which will hang the insert, and other stuff. msdn.microsoft.com/en-us/library/ms175046.aspx#remarks

tgolisch
  • 6,549
  • 3
  • 24
  • 42