February 27, 2007 at 5:35 pm
Dear all
I have the following issue:
I have a sql 2000 database. What i have to do is update another database when a table in my source database is updated. I cannot directly talk to the destination database but i have to go through an api.
So what i did i created a small program that expects parrameters that i would need to pass through the API to the destination database.
Is there any way to create a trigger for my source database that when triggered will pass the parameters that are being updated in the source database to the program which will talk through the api to the destination database?
Would there be any examples of something like this done before?
Thank you all so much for your help.
Sincerely
February 27, 2007 at 6:29 pm
I am not 100 % sure what your requirements are but I will suggest you try this:
FOR INSERT
AS
Exec Master..xp_cmdshell 'C:\aaa.bat abcd,efgh'
What I am showing you is how to use Master..xp_cmdshell command to execute external DOS command or EXE or Batch file to pass parameters which I believe what you are looking for.
February 27, 2007 at 7:05 pm
Dear Terry
That was exactly what i was interested in. I will try that approach now.
Only one addition to the post:
Can the trigger receive a return value from the batch file or the exe program. Like for example if the exe program succesfully completes the task and inserts the stuff in the destination then it will all be good. But if the programm for some reason fails to do so then i would like to use the trigger in my source database to ROLLBACK the UPDATE or INSERTION and the user would have to try and do the task again.
Regards
February 27, 2007 at 7:35 pm
You may try something like this:
CREATE TRIGGER TriggerTest ON [dbo].[TransactionLineItems]
FOR INSERT
AS
Exec Master..xp_cmdshell 'C:\aaa.bat abcd, efgh'
-- Wait for 3 seconds for the bach file execution
Waitfor Delay '000:00:03'
-- Check if any failure reported in the output file
-- Assuming the output file is aaa.txt
IF Exists(Select Name From tempdb..Sysobjects Where Name Like '%TempResult%')
Drop Table #TempResult
Create Table #TempResult (txtResult varchar(200))
Insert Into #TempResult
Exec Master..xp_cmdshell 'Type C:\aaa.txt'
If exists (Select txtResult From #TempResult Where txtResult like '%Fail%')
Begin
Print 'Failed'
--Rollback Tran
End
Else
Print 'Success'
Actually I never tried this before. So please leave some log if this works.
March 4, 2007 at 5:23 pm
I have been trying to get the trigger to execute my external program but it does not seem to work...I will paste my whole script here.....I cant figure out why it would not run it:
CREATE TRIGGER MyTrigger ON [dbo].[TheTable]
FOR INSERT
AS
BEGIN
DECLARE @var sysname,@cmd sysname,@Client sysname,@Job sysname,@Type sysname
SET @Client = 'Client'
SET @Job = 'Job1'
SET @Type = 'test'
SET @cmd = 'C:\programDir\SubdirExe\program.exe ' + @Client + ' ' + @Job + ' ' + @Type
SET @var = 'echo ' + @cmd + ' output.txt'
Print ('AFTER Trigger [] – Trigger executed !!')
EXEC Master..xp_cmdshell @cmd
EXEC Master..xp_cmdshell @var
END
I cant see why this would not run. The echo command runs without no problems but the running of the program runs. I have tried running the program with the same parameters from the command line and it runs with no problems.But from inside the trigger doesnt want to run..
Any help would be appreciated.
Sincerely
March 4, 2007 at 9:02 pm
Do some check in your trigger as follows:
Create Table [dbo].[ErrorCapture]
( [ErrDesc] Varchar(2000) Null)
Use the table inside the trigger as follows:
CREATE TRIGGER MyTrigger ON [dbo].[TheTable]
FOR INSERT
AS
BEGIN
set nocount on
DECLARE @var sysname,@cmd sysname,@Client sysname,@Job sysname,@Type sysname
SET @Client = 'Client'
SET @Job = 'Job1'
SET @Type = 'test'
SET @cmd = 'C:\programDir\SubdirExe\program.exe ' + @Client + ' ' + @Job + ' ' + @Type
SET @var = 'echo ' + @cmd + '> output.txt'
Print ('AFTER Trigger [] – Trigger executed !!')
-- clear the ErrorCapture buffer
Truncate Table [dbo].[ErrorCapture]
Insert Into [dbo].[ErrorCapture]
EXEC Master..xp_cmdshell @cmd
-- display error
Select * From [dbo].[ErrorCapture]
EXEC Master..xp_cmdshell @var
END
One other trivial check is, is the 'C:\programDir\SubdirExe\program.exe' on the SQL server or your PC? This exe should be on your SQL server. Once you capture the error message, then you are on better position to debug.
March 4, 2007 at 9:54 pm
yup i have the application that i would like to run on the same machine as the sql server.
just something really dodgy that is happeneing now is:
i modified the trigger and then clicked apply so i can save the changes but that process appear to take too long and my sql enterprise manager does not respond. Could i have stuffed up the database?
I would probably have to restard the SQL server later on.....I still have few users using the other databases so i cant perform the process now. My test database is on the same server as the production one.
Cheers
March 4, 2007 at 11:27 pm
The error that i am getting is:
| C:\WINDOWS\system32>application.exe Client Job1 Type1 |
'application.exe' is not recognized as an internal or external command,
operable program or batch file.
March 5, 2007 at 12:02 am
now i just ran it again and now error was produced but no result aswell
just to do a test i tried to do the operation from the comand line and it ran without any problems
that seemd interesting and unpredictable
March 29, 2007 at 2:42 am
Hi, I am not a trained SQL person, but would the fact that you are using variables, the values of which might lose the quote (') characters, be causing your problems?
Try hardcoding the values on your commandline (without using variables) to test.
I am interested to see if this works, as I would like to send emails from the external program.
Regards
Michael
March 29, 2007 at 2:44 am
The path to the application is RELATIVE to the SQL Server machine.
N 56°04'39.16"
E 12°55'05.25"
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply