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.
| Author |
Topic |
|
hedemann
Starting Member
3 Posts |
 Posted - 2005-09-28 : 08:59:34
|
Hi, I have this strangest problem with this trigger.==================================================================CREATE TRIGGER SendSMSTriggerON dbo.SendSMSAFTER INSERT ASDeclare @id nvarchar(250)Declare @cmdline nvarchar(250)SELECT @id=ins.id FROM inserted insSet @cmdline = '"C:\Program Files\SendSMSI\SendSMS.exe " ' + @idEXEC master..xp_cmdshell @cmdline-- Check to see if trigger is called-- UPDATE SendSMS Set text2 = @cmdline where id = @id==================================================================When a row is inserted into the table, the trigger should call the program with @id as parameter. Id is an auto increment field in the table. When the program is executed, it should select the inserted row, and create a text file. But it doesn’t work that way – the select in the program can’t retrieve the row. I know that the program works, if I call it directly ex. ‘SendSMSI 47’ it retrieves the right information a build the text file. And I know that the trigger is executed, because the text2 field is updated with the contents of @cmd.So can anyone tell me what is going wrong?Thanks |
|
|
DonAtWork
Master Smack Fu Yak Hacker
2167 Posts |
Posted - 2005-09-28 : 09:50:06
|
Does SendSMS.exe have the proper permissions to exec xp_cmdshell in the context of the trigger? When you execute it from the command line, does the program use YOUR credentials and permissions on the DB? Or does it have built in permissions?This is purely a stab in the dark.  Help us help YOU!Read this blog entry for more details: http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx*need more coffee*SELECT * FROM Users WHERE CLUE > 0(0 row(s) affected) |
 |
|
|
hedemann
Starting Member
3 Posts |
Posted - 2005-09-29 : 08:12:16
|
| Yes, SendSMS is executed; it writes an entry into the event lock, containing the row id. I don’t know, but I have a suspicion to a lock of some kind. The trigger is called when a row is inserted into the table, and a select is performed by a program called from the same trigger, on the same row before the trigger is ended. Is it possible to force the trigger to write the row and release it before the program is called? |
|
|
|
hedemann
Starting Member
3 Posts |
Posted - 2005-09-30 : 06:41:25
|
| Case closed – solutions found. I missed a “Commit” just before executing the “master..xp_cmdshell”. |
|
|
|
|
|
|
|
|
Problem with insert trigger....