Call a DLL or EXE file from SQL Trigger

_{Thursday, March 22, 2007 2:44 PM}

I need some help calling a DLL or EXE from a SQL Trigger.  I have the trigger set up, except I have no clue how to call a DLL or EXE or if it is even possible.  Here is what I have for the Trigger so far: 

Another question I have is can a trigger be created programmatically using ASPX pages written in VB.NET in VS2003?

Thank you in advance

_{Friday, March 23, 2007 9:09 AM ✅Answered}

Hi,

for debugging and better handling purposes, I would suggest first putting evverything in a varaible and executing this afterwards:

IF ((select ins.[ParentID] FROM inserted ins) = '57750')

DECLARE @MyTocId varchar(12)

SELECT @MyTocId = '"C:\Documents and Settings\michael\My Documents\Visual Studio Projects\Test\bin\Test.exe" ' + TocId FROM inserted

EXEC master ..xp_cmdshell @command = @MyTocId

Warning: Triggers are fired per statement not per row, you will in addition make sure that your trigger is able to handle multiple rows affected in a trigger. In further addition you will have to make sure that the command is not executed as no row is affected as the trigger is fired (as already said) on a statement basis (even if the affected rowcount is 0).

Jens K. Suessmeyer.

http://www.sqlserver2005.de

_{Thursday, March 22, 2007 7:10 PM}

I have to warn you first: although calling external exes is possible, it is not recommended as there are at least a couple obvious drawbacks:

1. You would possibly lose data integrity because those external processes are NOT bound to the SQL transaction that triggers run in. For example, if the INSERT statement that fires the insert trigger is rolled back, regular trigger actions would be rolled back too, but those external processes would not. Similarly, triggers might not be able to detect errors that happen to those external processes and it ends up with the external exe failed but the trigger (as well as the firing statement) succeeded.
2. These processes will run outside of SQL Server, so you would lose total control of them. E.g. they might come back and compete with SQL Server for resources like CPU and memory.

I am wondering what kind of scenarios you have, but there got to be a better way to do it :)

Anyway, if you still decide to go with this route, you could call external exes from a trigger by:

- xp_cmdshell '<some exe>'

• For a *unsafe* CLR trigger, you could practically do anything, including calling external processes (e.g. Process class)

For you other question, yes, you can create triggers programmatically from wherever you can connect to the server.

_{Thursday, March 22, 2007 7:35 PM}

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

GO

CREATE TRIGGER SendMailIfParentID

ON Toc

AFTER INSERT

AS

IF ((select ins.[ParentID] FROM inserted ins) = '57660')

EXEC master ..xp_cmdshell 'C:\Documents and Settings\michael\My Documents\Visual Studio Projects\Test\bin\Test.exe 57660'

Thats what I have for my trigger now (The 57660 is a test ID number and isn't of much relavence), either the trigger isnt being called or the EXE isn't being called.  I can't figure out why it's not working. 

_{Thursday, March 22, 2007 8:50 PM}

I figured out what wasn't working and fixed it.  Now I can't get a variable to append to the end of the command line.  It keeps telling me the + isn't valid.  Does anyone have any ideas?

ALTER TRIGGER SendMailIfParentID

ON Toc

AFTER INSERT

AS

IF ((select ins.[ParentID] FROM inserted ins) = '57750')

DECLARE @MyTocId varchar(12)

SELECT @MyTocId = (SELECT TocId FROM inserted)

EXEC master ..xp_cmdshell '"C:\Documents and Settings\michael\My Documents\Visual Studio Projects\Test\bin\Test.exe" ' + @MyTocId

GO

_{Thursday, November 1, 2007 1:34 PM}

Hello jens,

      I created a trigger on table for insert. I also calling an EXE from my trigger. When i try to insert data in table, query runs for infinite time (till i didn't shut down PC)

 I also enabled "xp_cmdShell" from "SQL Server Surface Area Configuration"

Here is code my Trigger...

CREATE TRIGGER t_TempTriger

ON TempTable

FOR INSERT

AS

DECLARE @newString VARCHAR(100)

SELECT @newString = (SELECT Cust_Name + ' ' + Address FROM Inserted)

DECLARE @MyTocId varchar(200)

SELECT @MyTocId = '"c:\temp.exe" '  + @newString

PRINT @MyTocId

EXEC master ..xp_cmdshell @command = @MyTocId

When I comment last line, everything works fine. It prints @MyTocId.

Please tell me where i'm wrong.

Thanks.

_{Thursday, November 1, 2007 2:11 PM | 1 vote}

Well, you have to be careful, triggers behave synchronously, meaning that the whole Insert process will be stopped till the external process returns. E.g. you start the application which does a readline --> the DML operation will never return. Better do something like writing the task in a table and let a job pickup the to be processed commands.

Jens K. Suessmeyer

http://www.sqlserver2005.de