Solved

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

Posted on 2008-10-10
28
249 Views
Last Modified: 2011-10-19
Could anyoine give me a sample trigger or stored procedure for this?

0
Comment
Question by:caoimhincryan
  • 17
  • 11
28 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22685896
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
 

Author Comment

by:caoimhincryan
ID: 22685910
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
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22685934
>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
 

Author Comment

by:caoimhincryan
ID: 22685942
>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
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22685957
>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
 

Author Comment

by:caoimhincryan
ID: 22686273
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
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22686385
ms has an document dedicated to that task:
http://msdn.microsoft.com/en-us/library/ms186273.aspx
0
 

Author Comment

by:caoimhincryan
ID: 22686466
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
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22686527
can you show the screenshot of the job step?
0
 

Author Comment

by:caoimhincryan
ID: 22686596
Here it is:
Capture.JPG
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22686647
is that file in that path on the sql server box?
0
 

Author Comment

by:caoimhincryan
ID: 22686668
It is yes. Im running it locally on my machine.

The exe is on my machine.
SQL server is on my machine.
0
 

Author Comment

by:caoimhincryan
ID: 22686699
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
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22686781
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:caoimhincryan
ID: 22686804
I mean my exe should open a form.when i double click the exe in windows exploer, it opens fine.
0
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 22686843
>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
 

Author Comment

by:caoimhincryan
ID: 22686854
When i change the exe path to:
C:\Program Files\Internet Explorer\iexplorer.exe

I'm still getting the same error.
0
 

Author Comment

by:caoimhincryan
ID: 22686869
>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
 

Author Comment

by:caoimhincryan
ID: 22686877
got it..sorry. should have opened my eyes.
0
 

Author Comment

by:caoimhincryan
ID: 22686918
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
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22686949
>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
 

Author Comment

by:caoimhincryan
ID: 22686970
okay..It works for my exe as well. One more thing..How do i call this job from a trigger? Whats the code?
0
 

Author Comment

by:caoimhincryan
ID: 22687033
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
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22687137
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
 

Author Comment

by:caoimhincryan
ID: 22687240
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
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22687260
hehe ... that's the "interact with the desktop" setting :)
0
 

Author Comment

by:caoimhincryan
ID: 22687282
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
 

Author Closing Comment

by:caoimhincryan
ID: 31504967
Thank you so much and Congrats on reaching your new status level!
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
Both in life and business – not all partnerships are created equal. As the demand for cloud services increases, so do the number of self-proclaimed cloud partners. Asking the right questions up front in the partnership, will enable both parties …

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now