Solved

launch client application from database trigger on MS SQL Server

Posted on 2004-08-28
8
2,234 Views
Last Modified: 2008-01-09
I want to launch a client application from a database trigger on MS SQL Server. When a table is updated the rigger should launch an application that checks various things. Any Ideas how to do this? I tried by creating a DTS to launch a exe-file on the client computer. It did not work. Could not find path. I wonder if u can use DTS to launch an app remotely or should I use MSMQ. How is this done? I have never used MSMQ before.
0
Comment
Question by:Peter_Ankarlou
  • 5
  • 3
8 Comments
 
LVL 37

Expert Comment

by:gregoryyoung
ID: 11923456
directly from sql help ... its 1 way of doing this ...

xp_cmdshell (T-SQL)

Executes a given command string as an operating-system command shell and returns any output as rows of text. Grants nonadministrative users permissions to execute xp_cmdshell.



Note When executing xp_cmdshell with the Microsoft® Windows® 95/98 operating system, the return code from xp_cmdshell will not be set to the process exit code of the invoked executable. The return code will always be 0.



Syntax

xp_cmdshell {'command_string'} [, no_output]

Arguments

'command_string'

Is the command string to execute at the operating-system command shell. command_string is varchar(255) or nvarchar(4000), with no default.

no_output

Is an optional parameter executing the given command_string, and does not return any output to the client.



Return Code Values

0 (success) or 1 (failure)

Result Sets

Executing this xp_cmdshell statement returns a directory listing of the current directory.

xp_cmdshell 'dir *.exe'



The rows are returned in an nvarchar(255) column.
Executing this xp_cmdshell statement returns the following result set:

xp_cmdshell 'dir *.exe', NO_OUTPUT



Here is the result:

The command(s) completed successfully.



Remarks

xp_cmdshell operates synchronously. Control is not returned until the command shell command completes.
When you grant execute permissions to users, the users can execute any operating-system command at the Microsoft Windows NT® command shell that the account running Microsoft SQL Server™ has the needed privileges to execute.
By default, only members of the sysadmin fixed server role can execute this extended stored procedure. You may, however, grant other users permission to execute this stored procedure. Users who are not members of the sysadmin fixed server role will always run their Windows NT commands in the context of the SQLAgentCmdExec user account, which is the same account used by SQL Server Agent for scheduled tasks entered by users who are not members of sysadmin. When members of the sysadmin fixed server role execute xp_cmdshell, their Windows NT commands execute in the MSSQLServer service's security context, which by default is a user account with local administrator authority.



Note In previous versions, a user who was granted execute permissions for xp_cmdshell ran the command in the context of the MSSQLServer service's user account. SQL Server could be configured (through a configuration option) so that users who did not have sa access to SQL Server could run xp_cmdshell in the context of the SQLExecutiveCmdExec Windows NT account. In SQL Server 7.0, the account is called SQLAgentCmdExec. Users who are not members of the sysadmin fixed server role now run commands in the context of this account without specifying a configuration change.



Permissions

Execute permissions for xp_cmdshell default to members of the sysadmin fixed server role but can be granted to other users.



Important If you choose to use a Windows NT account that is not a member of the local administrator's group for the MSSQLServer service, users who are not members of the sysadmin fixed server role cannot execute xp_cmdshell.



Examples
A. Return a list of executable files

This example shows the xp_cmdshell extended stored procedure executing a directory command.

EXEC master..xp_cmdshell 'dir *.exe'



B. Use Windows NT net commands

This example shows the use of xp_cmdshell in a stored procedure. This example notifies users (with net send) that SQL Server is about to be shut down, pauses the server (with net pause), and then shuts the server down (with net stop).

CREATE PROC shutdown10

AS

EXEC xp_cmdshell 'net send /domain:SQL_USERS ''SQL Server shutting down
in 10 minutes. No more connections allowed.', no_output
EXEC xp_cmdshell 'net pause sqlserver'
WAITFOR DELAY '00:05:00'
EXEC xp_cmdshell 'net send /domain: SQL_USERS ''SQL Server shutting down
in 5 minutes.', no_output
WAITFOR DELAY '00:04:00'
EXEC xp_cmdshell 'net send /domain:SQL_USERS ''SQL Server shutting down
in 1 minute. Log off now.', no_output
WAITFOR DELAY '00:01:00'
EXEC xp_cmdshell 'net stop sqlserver', no_output



C. Return no output

This example uses xp_cmdshell to execute a command string without returning the output to the client.

USE master

EXEC xp_cmdshell 'copy c:\sqldumps\pubs.dmp \\server2\backups\sqldumps',
NO_OUTPUT


D. Use return status

In this example, the xp_cmdshell extended stored procedure also suggests return status. The return code value is stored in the variable @result.

DECLARE @result int

EXEC @result = xp_cmdshell 'dir *.exe'

IF (@result = 0)

PRINT 'Success'
ELSE
PRINT 'Failure'


E. Write variable contents out to file

This example writes the contents of the current directory to a file named dir_out.txt in the current server directory.

DECLARE @cmd sysname, @var sysname

SET @var = 'dir /p'

SET @cmd = 'echo ' + @var + ' > dir_out.txt'

EXEC master..xp_cmdshell @cmd
0
 
LVL 37

Expert Comment

by:gregoryyoung
ID: 11923457
but msmq is probably a better way of going (leave the other program running and send it a message)
0
 

Author Comment

by:Peter_Ankarlou
ID: 11925753
Gregory,
thanks indeed a lot for all your efforts. The way you have described using XP_CMD is the way I have solved it so far. And it works fine. The problem I have is when my XP_CMD is opening up and running a DTS that launches a MSMQ message. When I try to open the DTS and run it from the SQL Server Enterprise manager it works fine and the program on my computer starts due to the triggers and the rule of the message queue I have set up on my computer but ...
When I insert a row in the table that through a trigger fires a XP_CMD command that in itself fires of the DTS that launches a MSMQ message I get the reply that a message could not be created. I suspect this means that in order to execute the DTS on the server and get the disered result you need to have a MSMQ queue installed on the SQL server and also have sufficient rights to actually send message through the MSMQ server installed on the SQL Server right?
 
0
 
LVL 37

Expert Comment

by:gregoryyoung
ID: 11929615
well that would be a good set of axioms.
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:Peter_Ankarlou
ID: 11929800
Gregory, maybe I have stated something obvious, I just would very much like a confirmation that it is true?
0
 
LVL 37

Accepted Solution

by:
gregoryyoung earned 500 total points
ID: 11929814
remember that xp_cmd runs as the user that sql server is running as ...
0
 
LVL 37

Expert Comment

by:gregoryyoung
ID: 11946611
was that the issue (command be running as the user of the sqlserver service?)
0
 

Author Comment

by:Peter_Ankarlou
ID: 12043100
No. I wanted help with launching a MSMQ Message from a database trigger that in itself launched an application on another server. I could not solve it due to various security issues regarding lack of AD for the SQL Server forinstance. I gave you the 500 points for your great effort. Thanks. Peter
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Suggested Solutions

This article will show, step by step, how to integrate R code into a R Sweave document
Whether you’re a college noob or a soon-to-be pro, these tips are sure to help you in your journey to becoming a programming ninja and stand out from the crowd.
Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …
In this fourth video of the Xpdf series, we discuss and demonstrate the PDFinfo utility, which retrieves the contents of a PDF's Info Dictionary, as well as some other information, including the page count. We show how to isolate the page count in a…

744 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

10 Experts available now in Live!

Get 1:1 Help Now