• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 258
  • Last Modified:

Write trigger using .NET framework language VB.NET using clr integration

Could anyoine give me a sample trigger or stored procedure for this?

0
caoimhincryan
Asked:
caoimhincryan
  • 17
  • 11
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you cannot write a trigger with clr integration.
you can write a procedure with clr integration, and use that in the trigger.

why do you want to do this?
0
 
caoimhincryanAuthor Commented:
In one my tables, when there is an insert, i want to run an exe of mine.

Are you sayint to write the procedure and then use the trigger to call this?
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>Are you sayint to write the procedure and then use the trigger to call this?
it depends.

>In one my tables, when there is an insert, i want to run an exe of mine.
must that be "immediately"?
usually, I prefer to create a unscheduled sql agent job that starts the .exe, and let the trigger just start that job.
that will be much faster for the end user...

if you need to pass some arguments to the .exe, it might be needed to create the job "on-the-fly", and mark the job also as auto-delete after completion...
0
Prepare for an Exciting Career in Cybersecurity

Help prevent cyber-threats and provide solutions to safeguard our global digital economy. Earn your MS in Cybersecurity. WGU’s MSCSIA degree program curriculum features two internationally recognized certifications from the EC-Council at no additional time or cost.

 
caoimhincryanAuthor Commented:
>In one my tables, when there is an insert, i want to run an exe of mine.
must that be "immediately"?
usually, I prefer to create a unscheduled sql agent job that starts the .exe, and let the trigger just start that job.
that will be much faster for the end user...

I like that idea. It needs to be immediately. Could you tell me how to set up an unscheduled sql agent job??
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>Could you tell me how to set up an unscheduled sql agent job??
just create the sql agent job with no schedules.

then, in your trigger, use sp_start_job stored procedure (in msdb database) to start the job
0
 
caoimhincryanAuthor Commented:
Creating a job is new to me. Would you have the steps? my exe would be

C:\Program Files\Default Company Name\test\HelloWorld.exe

Thanks.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
ms has an document dedicated to that task:
http://msdn.microsoft.com/en-us/library/ms186273.aspx
0
 
caoimhincryanAuthor Commented:
I followed this:
http://msdn.microsoft.com/en-us/library/ms190264.aspx

Is that the right one to follow when trying to execute an exe.

However i got an error saying:

[136] Job StartApp reported: The process could not be created for step 1 of job 0x670D1E74FD7CA646A6F79522CE7ED325 (reason: The system cannot find the file specified)
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
can you show the screenshot of the job step?
0
 
caoimhincryanAuthor Commented:
Here it is:
Capture.JPG
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
is that file in that path on the sql server box?
0
 
caoimhincryanAuthor Commented:
It is yes. Im running it locally on my machine.

The exe is on my machine.
SQL server is on my machine.
0
 
caoimhincryanAuthor Commented:
A weird thing to notice is that when i run the job..it says its executing job....

Then if i go into my task manage, i can see receive.exe in the processes but no form has opened up. The username of that process is SYSTEM. Would that have anything to do with it?
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
running a job should NOT show a form. it is assumed to be a async process, so what likely happens is that your .exe has some problems, and pops up with a error message (which nobody can see).
0
 
caoimhincryanAuthor Commented:
I mean my exe should open a form.when i double click the exe in windows exploer, it opens fine.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>I mean my exe should open a form
started from a service, it won't, as the services are started even before you logged in, so they have their own "desktop".
you won't see the form.

note: you migth try to set the sql server agent service to "allow this service to interact with the desktop" ..
0
 
caoimhincryanAuthor Commented:
When i change the exe path to:
C:\Program Files\Internet Explorer\iexplorer.exe

I'm still getting the same error.
0
 
caoimhincryanAuthor Commented:
>note: you migth try to set the sql server agent service to "allow this service to interact with the desktop" ..
How do i do that?
0
 
caoimhincryanAuthor Commented:
got it..sorry. should have opened my eyes.
0
 
caoimhincryanAuthor Commented:
I did that and am using the path:

C:\Program Files\Internet Explorer\iexplore.exe

It open internet explorer but how come in the status it says it is still executing and status wont change to success until i close internet explorer.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>It open internet explorer but how come in the status it says it is still executing and status wont change to success until i close internet explorer.

that is by design. the sql job will wait for the application exit, to fetch the exit code, to know if the step should be considered successful or not.
0
 
caoimhincryanAuthor Commented:
okay..It works for my exe as well. One more thing..How do i call this job from a trigger? Whats the code?
0
 
caoimhincryanAuthor Commented:
here is my current trigger..
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
ALTER TRIGGER [dbo].[InsertAlertType]
ON [dbo].[AlertTypes]
AFTER INSERT, UPDATE
AS
BEGIN
 
 
DECLARE @col2 INT
DECLARE @cmd VARCHAR(2000)
 
 
set @col2 = (SELECT AlertType FROM inserted)
 
 
IF @col2 = 3
 
--       What do i insert here
 
END

Open in new window

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
as I indicated,you use sp_start_job stored procedure:
http://msdn.microsoft.com/en-us/library/ms186757.aspx


EXEC msdb.dbo.sp_start_job N'your_job_name' ;

Open in new window

0
 
caoimhincryanAuthor Commented:
Thats great angellIII.

One thing..I cant seem to get the below command prompt to go away. it comes up as minimised. When i close it, it keeps opening back up.
Capture.JPG
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
hehe ... that's the "interact with the desktop" setting :)
0
 
caoimhincryanAuthor Commented:
Is there any way of hiding it do you know? If not, it shouldnt be a problem.

Thanks so much for your help. Cant be greatful enough.
0
 
caoimhincryanAuthor Commented:
Thank you so much and Congrats on reaching your new status level!
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 17
  • 11
Tackle projects and never again get stuck behind a technical roadblock.
Join Now