0

I've tried to run a console application in Server from "after insert trigger" in one of my tables in SQL Server 2000. I used the following line

    Declare @Seq_NO bigint ;

    SELECT @Seq_NO=i.Seq_No from inserted i
    declare @CmdSQL varchar(1000)
    set @CmdSQL = 'C:\temp\SendEmailConsole\SendScheduledEmail.exe' + cast(@Seq_NO as varchar(10))

    exec master..xp_cmdshell  @cmdSQL

which "C:\temp\SendEmailConsole" is a test location for my Application.

It works for other versions of SQL Server newer than 2000, I activated the command on them using this :

EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
EXEC master..sp_configure 'xp_cmdshell', 1
GO
RECONFIGURE

My first question is why master..sp_configure 'xp_cmdshell', 1 is not working in SQL Server 2000? And what's the substitute command?

And second problem is what's the failure in my "after insert" trigger?

And finally, is there any alternative solution for that?

Thanks you for your help.

UPDATE :

Now, the server process is locked when I insert record in that table !!!! WHAT IS MY MISTAKE?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Sara N
  • 1,079
  • 5
  • 17
  • 45
  • Your mistake is attempting ot use xp_cmdshell in a trigger. In fact, triggers should be tiny and only used when absolutely needed. If they are used, they should be kept extremely short and not use code that could block them, at all. –  Jan 27 '15 at 06:05
  • Sending mail from trigger is bad idea. You could use some kind of queue table, where you insert records inside trigger and send mails from queue inside some scheduled task / job. – Arvo Jan 27 '15 at 08:05
  • @Arvo Could you please explain more about that? whats the queue table? and also , it works in sql 2012 without any problem – Sara N Jan 27 '15 at 22:44
  • @SeanGallardy: whats your idea about any alternative solution? I mean, What should I do when I want to execute a Console App after insert in records – Sara N Jan 27 '15 at 22:45
  • Queue table is just another table, where you insert records and another process reads these records and executes needed actions and marks records processed (or deletes them). About xp_cmdshell - is your user sysadmin? Usually only sysadmins are able to use xp_cmdshell anyway. – Arvo Jan 28 '15 at 09:50
  • @SarahN I would re-design the process flow. If that couldn't be done, I would create a wrapper to insert the data and then execute the application from outside of SQL Server by creating a new application as the wrapper/shim. Doing this inside of the database is just asking for it to blow up in a shower of lights and fire. –  Jan 28 '15 at 14:15
  • @Arvo thanks for your help. I did what you suggested. thanks – Sara N Feb 02 '15 at 05:57
  • @Sean Gallardy : also I took your advice and create app out side the SQL. mix this two solutions and it works fine, thanks alot – Sara N Feb 02 '15 at 05:59

0 Answers0