Please Help! How I can run appl from trigger SQL?

  • Thread starter Thread starter Guest
  • Start date Start date
Pola said:
I am using VC++.net
How I can run appl from the trigger SQL?
Click to expand...

Generally speaking it is a bad idea to have SQL call an external
application in a trigger. It takes a lot of resources. You may want to
look at setting up a worker queue where you have a job runs every minute
and processes the appropriate records.
 
Hello Pola,

Based on my understanding, you want to run some functions in VC++.NET in
TSQL, right?

Currently SQL Server 2000 doesn't support running a .NET framework class
library directly. However, in the next version of SQL Server, Yukon, it is
supported. We can register a class library in SQL Yukon and run it in TSQL
directly. I think that should be what you want. Running it in SQL Server is
good for some work like math calculation. However, for running query which
returns a lot of records. It is better to still use TSQL.

For the time being, I agree with Aaron that we can use job to accomlish it.

Thanks.

Best regards,
Yanhong Huang
Microsoft Community Support

Get Secure! ¨C www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.
 
Hi Pola,

After discussing with our SQL engineer, we think you could embed the
xp_cmdshell (extended stored procedure) in the trigger to run the
application within the trigger like:

Exec xp_cmdshell 'dos_cmd'

Transact-SQL Reference - xp_cmdshell
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_
xp_aa-sz_4jxo.asp

However, for such application, it can't have GUI or need user interaction.

Thanks.

Best regards,
Yanhong Huang
Microsoft Community Support

Get Secure! ¨C www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.
 
Aaron Weiker said:
Generally speaking it is a bad idea to have SQL call an external
application in a trigger. It takes a lot of resources. You may want to
look at setting up a worker queue where you have a job runs every minute
and processes the appropriate records.
Click to expand...

OpenLink Virtuoso is a full blown database engine that already hosts
the .NET CLR and associated frameworks.

You can see a live demo at:
http://kingsleydemo.openlinksw.com:8890/tutorial/ (just follow the
"Runtime Hosting" link)

You can download a free evaluation copy from:
http://www.openlinksw.com/virtuoso

You can see my Virtuoso vs Yukon blog post at:
http://www.openlinksw.com/blog/~kidehen/index.vspx?id=138

Kingsley Idehen
OpenLink Software
http://www.openlinksw.com/blog/~kidehen
 
Hi Pola,

With further researching, no-gui, no-interactive/unattached and no
output/echo application is OK for xp_cmdshell.

As xp_cmdshell is run under the SQL Server Service with its startup
account, something will be lost in the security context of permissions
passed to the application outside of SQL Server layers. If your application
is associated with this security context, it will not work fine without the
proper permission(s). Even though we can start SQL Server Service from a
command prompt with the ¨Cc parameter to bypass this security context,
that might not be your expected startup behavior (not start as a Windows
Service) on SQL Server.

Using Startup Options:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad
_1_start_8m43.asp

I think that¡¯s one of the reasons that Aaron suggested running the
executable file out of the SQL Server layer. In addition, application
outside of SQL Server can check the trigger inside SQL Server and execute
another executable file gracefully, which can be another way.

Thanks very much.

Best regards,
Yanhong Huang
Microsoft Community Support

Get Secure! ¨C www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.
 
Back
Top