October 24, 2001 at 9:05 am
I have a DTS package that sends my table to a precisely formatted text page. I want this package to fire whenever I update the table (insert, update, delete). I know I need a trigger, but I can't seem to find anything in SQL on-line help (am I suprised?) about how to fire the DTS package from a trigger. Thanks for any suggestions or starter code!
October 24, 2001 at 10:25 am
You can shell out (xp_cmdshell) and run dtsrun. You can schedule the DTS package and use sp_start_job. Or you can run the DTS package every minute and check for changes before exporting the table.
I'd be wary of using the trigger. If you get too many updates, it will be tough on the system. You may want to include a flag in the DTS package so only one instance of hte package can run.
Steve Jones
October 24, 2001 at 10:34 am
Right, I've got sp_start_job in my trigger:
EXEC sp_start_job @job_name = 'dts_package'
Current problem:
When I execute the package in SQL EM, it works just fine. So, I delete the contents of the file (to ensure that I know the file has been touched). Then, when I execute an insert or delete from Query Analyzer on the table, I get a message that the job was completed successfully. So, I check the output file, and BAM! it wasn't touched. Did I skip a step or something?
October 24, 2001 at 11:15 am
Is the package scheduled? It runs under the SQLAgent account, so be sure that accoutn has the proper rights. When you execute it, are you clicking in the DTS Packages section in EM or the Jobs section? If you run this from the packages section, it runs under your account (the one you logged in with).
Steve Jones
October 24, 2001 at 1:03 pm
OK. Here's the config:
I'm using Windows authentication, and my login is the DBO account. I have the SQL Agent service starting up in my account, and have it running all script as my account. Essentially, everything in this scenario is in my account. The trigger fires the job, and the package works; but the job won't run the package. I have the job owner set to my account (DBO), and my account is the owner of the package.
I'm about to loose control!
October 24, 2001 at 1:43 pm
bobl - You stated that the "trigger fires the job, and the package works; but the job won't run the package" Have you tried running the job directly from EM or executing the statement in your earlier posting in Query Analyzer? If so, what are the results there? When you say that the "trigger fires the job" are looking in job history to see the job completion or are you basing it strictly on the trigger completion?
Any more information you can provide here would be helpful. Sorry if these questions bring about more frustration. Been there on this issue as well.
David
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
October 24, 2001 at 1:52 pm
<QUOTE>
Have you tried running the job directly from EM or executing the statement in your earlier posting in Query Analyzer? If so, what are the results there?
<SNIP>
Yes on both accounts. The result is that the job hangs indefinately. It doesn't fail, but it doesn't move forward. The package has 1 item, and takes about 1.5 seconds to execute. So there's no reason why the job should hang like that.
</SNIP>
When you say that the "trigger fires the job" are looking in job history to see the job completion or are you basing it strictly on the trigger completion?
<SNIP>
I use query analyzer to insert a row, and when I run the query I get this message:
"Job 'file_update' started successfully."
</SNIP>
Any more information you can provide here would be helpful. Sorry if these questions bring about more frustration. Been there on this issue as well.
</QUOTE>
Nah, questions are good. I'm glad your're trying to help! Thank you.
October 24, 2001 at 2:30 pm
Actually, I've narrowed it down. My package creates a FileSystem object, and that's where my problem lies. When I run the package, I run it as myself and the file is created; but when the job runs the package, it runs into a problem.
Here's some code:
dim filePath
filePath="c:\outputfile.txt"
Set fileSys = CreateObject("Scripting.FileSystemObject")
Set txtFile = fileSys.CreateTextFile(filePath, true)
txtFile.Write("Text")
txtFile.Close
So, what do you think now?
I really do appreciate your help.
October 24, 2001 at 3:04 pm
SOLUTION:
I am running Norton AV. When I attempt to run (manually) the package that creates a file system object, I am prompted by NAV. The prompt informs me that the script contains a dangerous command, and could potentailly create a virus on my machine. It asks if I would like to cancel, run everything EXCEPT the dangerous command, or run the whole script ONE time. I chose the whole script one time.
The problem comes into play when the job attempts to run the package, and receives the prompt. It cannot reply to the prompt, so it will "hang" indefinately, while NAV waits for a response.
I am investigating Norton right now, to find out if there is a prompt catcher, or some other work-around. I will post that solution here (if it ever makes itself available to me).
Thanks again for all of the help. I actually managed to retain MOST of my hair. *g*
October 24, 2001 at 3:06 pm
So this file does not get created?
I'm sorry for continuing to ask questions, but it is confusing in your post.
A couple things:
Are you running this from the local console on the server? Or your workstation?
The SQLAgent service must be restarted if you changed the account.
Create a simple package with a single active X task using the code above. Schedule this and then execute the scheduled package as a job, not the package itself. Does it create the file?
Secondly, does your accoutn have rights to write to the c: drive of the server?
Steve Jones
October 24, 2001 at 6:52 pm
Some of what you are experiencing could be related to your SQL Agent. I had a few days when I was running down a similar situation just getting jobs to start and complete. The enterprise changes I needed to make were to set up a domain account (or at least a local machine) account the BOTH SQL Server and SQL Agent uses for the services. Within Windows 2000 I needed to allow the account to 'run as service'. Once I had this in place the jobs work like champs.
Jody
October 24, 2001 at 9:10 pm
I think you're on the right track, if anything raises a dialog in SQL Agent (or xp_cmdshell) you're done. Super good error handling helps. My preferred solution (I know not everyones) is to compile the code to a dll and mark it for unattended execution, not perfect, but not bad either.
Andy
October 24, 2001 at 10:27 pm
The first time you assign an account to a server, W2K should automatically grant the "run as a service".
Keep in mind that when you execute a package, it runs in the context of your account AND your machine. When SQLAgent does it, it runs as if the SQLAgent account was used to log into the server and that account executed the package.
Steve Jones
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply