xp_cmdshell TRIGGER - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

xp_cmdshell TRIGGER

Author

Topic

Lucky
Starting Member

10 Posts

Posted - 2008-08-01 : 02:24:12

Hi Dears,

can u plz help me out urgently, Actually I am passing @empno to > c:\Output.vbs' but its not creating, can u plz help me out.
there is no error in Trigger.

many thanks,

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[t_Term]

ON [dbo].[EMTER]

FOR UPDATE

AS

DECLARE @tdate1 DATETIME

DECLARE @tdate2 DATETIME

DECLARE @empno INT

SET @tdate1=(select ter_dated from INSERTED)

SET @tdate2=(select ter_dated from DELETED)

IF (@tdate1 <> @tdate2)

BEGIN TRY

SET @empno=(SELECT det_numbera FROM Inserted)

DECLARE @cmd sysname, @var sysname
SET @var = @empno
SET @cmd = 'echo ' + @var + ' > c:\Output.vbs'
EXEC master..xp_cmdshell @cmd

--INSERT INTO testEmp VALUES(@empno)
-- select * from testemp

END TRY

BEGIN CATCH

SELECT ERROR_NUMBER() as ErrorNumber,

ERROR_MESSAGE() as ErrorMessage;

END CATCH

______________________________________________________________________________________

"We can be Knowledgeable with other mens Knowledge, but we can't be wise with other mens wisdom"

Lucky
Starting Member

10 Posts

Posted - 2008-08-01 : 02:33:13

HI, I am running this TSQl and its working fine, but I don't know why its not creating file through TRIGGER,

Declare @empno int
set @empno = '001234'
DECLARE @cmd sysname, @var sysname
SET @var = cast(@empno as varchar(255))
SET @cmd = 'echo ' + @var + ' > c:\Output.vbs'
EXEC master..xp_cmdshell @cmd

______________________________________________________________________________________

"We can be Knowledgeable with other mens Knowledge, but we can't be wise with other mens wisdom"

pootle_flump

1064 Posts

Posted - 2008-08-01 : 04:00:53

1) Your error handling is wrong - a trigger cannot return a result set. Stick this in your catch:

RAISERROR(ERROR_MESSAGE(), 16, 1)

2) Also - your code assumes that only one row at a time will be affected - it is not coded correctly to deal with set based changes. This is a common trigger error.

If 1 does not change anything,try inserting the return from EXEC master..xp_cmdshell @cmd into a table and interrogate that. I would include the value of @cmd too to help your debugging.

pootle_flump

1064 Posts

Posted - 2008-08-01 : 04:45:07

Actually - sorry - that is not valid syntax - you can't reference ERROR_MESSAGE() directly with RAISERROR. Just comment out the TRY...CATCH for now instead - that way you will receive the errors.

Lucky
Starting Member

10 Posts

Posted - 2008-08-04 : 23:36:03

HI,

SET @cmd = 'echo ' + CAST(@empno AS VARCHAR) + '\\abc000vfs002\IT$\\Output.vbs'

I am just creating OUTPUT file on the network with the above command, I can create on Local drive but can't get OUTPUT.vbs on the network can u hlep me please ?????

______________________________________________________________________________________

"We can be Knowledgeable with other mens Knowledge, but we can't be wise with other mens wisdom"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources