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 |
|
ccbryan
Starting Member
4 Posts |
 Posted - 2009-05-07 : 16:03:46
|
| I am trying to create an INSERT trigger that will call an external .exe, passing the just-inserted values as parameters. I know from searching around that xp_cmdshell is viewed askance by many, but that's all I've got right now. Here's a proper command string to run my executable (that is, the command I want the trigger to execute... if I RUN this in Windows it works):Y:\Executable\InovicePrinter.exe 70001 SI PRINTThe document number and the document type ('70001' and 'SI') are the data that I want to pull from the inserted record. The table DocumentCall is in my SQL database. I'll show you the code then go over my issues...CREATE TRIGGER TrgDocCall ON DocumentCall for InsertAS BEGINprint 'starting trigger' DECLARE @DocNo varchar(12)Declare @DocType varchar(12)Declare @CmdString varchar(12)SELECT @DocNo = (Select Docno from Inserted)select @DocType = (Select DocType from inserted)select @CmdString = 'Y:\Executable\InovicePrinter.exe ' + @DocNo + ' ' + @DocType + ' PRINT' exec master.dbo.xp_cmdshell @command = @CmdString ENDI right-clicked on the DocumentCall table and chose trigger - new, and entered the above code in the window that appeared. It parses without a problem, so I added a record to the DocumentCall table to see if InvoicePrinter.exe was kicked off. That doesn't work. So I've been trying to run it from the design page, figuring it should at least get to the point where it tries to pull the data from Inserted, but it errors out each time saying that ProductCall is an invalid object. I have tried every combination of server.database.tablename I can think of, but with no luck. So that's my first issue: why doesn't it recognize ProductCall table? Is it because I'm running the trigger interactively?I have solved the problem just above in red -- obviously I hadn't tried enough variations on the file name. Still haven't gotten anywhere with issue 2, though.My second issue is that I don't know how the trigger is supposed to be stored in the database and attached to its table. When I hit the save button after writing the query it saved to a .sql file in the My Projects directory and I can't find hide nor hair of it in SQL Server Manager. The result is that I don't know how to fire the trigger... nothing seems to happen when I insert a record into DocumentCall.Thirdly, does the syntax that builds and issues the command look right? I have not been able to run it to see whether it works since I can't get past the tablename...Thanks very much for looking at this. |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2009-05-07 : 16:09:03
|
First look: @CmdString varchar(12) cannot take more than 12 chars
No, you're never too old to Yak'n'Roll if you're too young to die. |
 |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2009-05-07 : 16:14:07
|
Second look: ProductCall / DocumentCall?And to create the trigger you have to execute the statement that you have posted. Then you can find it in Object explorer under that table...
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
ccbryan
Starting Member
4 Posts |
Posted - 2009-05-07 : 16:23:21
|
quote:
Originally posted by webfred
Second look: ProductCall / DocumentCall?And to create the trigger you have to execute the statement that you have posted. Then you can find it in Object explorer under that table...
No, you're never too old to Yak'n'Roll if you're too young to die.
Thanks for the responses webfred..."Productcall" was a figure of my fingers' imagination. Please read it as "DocumentCall". I get the varchar(12) issue, but I haven't even been able to reach that line yet. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-05-08 : 10:34:22
|
| can i ask what is the exe you call doing? also why are you trying it invoke this from trigger? |
|
|
|
|
|
|
|
|
Need help creating Trigger