Failure to fire trigger

Question

Post reply

Failure to fire trigger

drew_walker

SSC Journeyman

Points: 81
More actions
March 18, 2002 at 9:15 am

#78488

I have created a trigger on one of my data tables to execute a DTS job

eg: EXECUTE msdb.dbo.sp_start_job @job_name= 'Output_Data'

If I run this from the SQL Query Analyser it actually works, but when I put it into an INSERT trigger there doesn't seem to be any output ???

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Antares686 SSC Guru Points: 125444 More actions · Answer 1

I have no problems with this on mine. What does the DTS package do exactly. If it is outputing to a file the data then you may need to allow dirty reads as the insert has not occurred possibly by the time the package runs. Also if the package is running and error returns and may stop everything including the insert from happening.

"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)

drew_walker SSC Journeyman Points: 81 More actions · Answer 2

I think your youve hit on something there, about the job failing during execution. (this seems to be a common theme in triggers), I talk some of your previous advice in the forum and have been using SQL Profiler. the job fails with error 14262 which to the best I can fid out means the job doesn't exist. yet when I run it manually it works fine is there any further debugging etc, that can be done ?

drew_walker SSC Journeyman Points: 81 More actions · Answer 3

drew_walker

SSC Journeyman

Points: 81

March 19, 2002 at 1:48 am

#425610

Sorry : the trigger fires a DTS job, which reads a view I have created to get the very last record in the table and then appends it to an ascii file on another server,(bit backward I know but neccessary to keep an old logger system up to date with data).

Antares686 SSC Guru Points: 125444 More actions · Answer 4

Ok try this, could be an internal permission issue with the package so create a Agent job that does an insert only to a table when executed that is not enabled. Then replace the current trigger with one that fires starts this job instead. Now test and see what happens.

"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)

drew_walker SSC Journeyman Points: 81 More actions · Answer 5

This is weird,

I created a new trigger which just outputs a PRINT statement which I could monitor in SQL Profiler. works o-kay, which points to the actual Job or the DTS, tried running the DTS from my PC works great, try calling the job it fails. Tried running the DTS from the server and it failed !. Examined the DTS Package from the Client on the Server and the DataSource connection is saying it is DBase5 ???, when I open this package from my Client on my local PC everything is as it should be ie DataSource connection = OLE DB provider. Any reason why the server is twisting up the DTS Package ????

Antares686 SSC Guru Points: 125444 More actions · Answer 6

Antares686

SSC Guru

Points: 125444

March 19, 2002 at 9:02 am

#425639

Usually only happens when the item being twisted does not exist the say as on your local machine. Try build the package agin on the server (not your local machine) and see what happens.

"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)

drew_walker SSC Journeyman Points: 81 More actions · Answer 7

drew_walker

SSC Journeyman

Points: 81

March 19, 2002 at 9:08 am

#425641

tried that, it goes through all the motions of savin etc, but just never appears. Also when I place a db source onto the worksheet, it will let me configure it correctly, but when you go back and look at the properties it has changed to DBASE 5 ???