xp_cmdshell from insert trigger

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

xp_cmdshell from insert trigger

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-01-17 : 09:51:36

Hannelien writes "I'm having problems using xp_cmdshell from a trigger. When running the following from query analyser it is working fine:

declare @cmdString varchar(1024),
@cmd sysname

set @cmdString = 'test1,test2'
SET @cmd = 'cmd /c "C:\TestEXE.exe" test1,test2'
EXEC master..xp_cmdshell @cmd

The TestExe.exe creates a text file in c:/.

When running from a trigger:

IF EXISTS(SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[TRI_TelephonyParticipation]') AND OBJECTPROPERTY(id, N'IsTrigger') = 1)
DROP TRIGGER [dbo].[TRI_TelephonyParticipation]
GO
CREATE TRIGGER [dbo].[TRI_TelephonyParticipation] ON [TelephonyParticipation]
FOR INSERT
AS
BEGIN
declare @cmdString varchar(1024),
@cmd sysname

set @cmdString = 'test1,test2'
SET @cmd = 'cmd /c "C:\TestEXE.exe" test1,test2'
EXEC master..xp_cmdshell @cmd

END

GO

When running from the above it seems as though xp_cmdshell does not execute as TestEXE.exe does not create a text file on c:/. Please if anyone can give me any idea on what is wrong here."

clarkbaker1964
Constraint Violating Yak Guru

428 Posts

Posted - 2005-01-18 : 19:35:02

The path and file for TextEXE.exe must reside on the server for one and not on your local machine... Does this code run from an sp? mabe you have permission issues with the cmd.exe

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2005-01-19 : 05:52:30

it runs fine because you're using your login which has the privilege to run the xp_cmdshell,

running it in a trigger would mean it has to run under the login that "triggered the trigger", and if this login does not have the necessary permission to run xp_cmdshell, the transaction fails.

--------------------
keeping it simple...

X002548
Not Just a Number

15586 Posts

Posted - 2005-01-19 : 15:04:04

quote:
Originally posted by AskSQLTeam

Please if anyone can give me any idea on what is wrong here."

Sure, running xp_cmdshell in a trigger is not a good idea...

Everytime the trigger fires, it'll create a new spid...and what's the executable doing anyway?

Creating a file?

Based on what?

Sounds like you should rethink this process...

Oh wait! Is this a cert question? Sounds like one....

Brett

8-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources