Tag Info

New answers tagged

0

Try simply restarting the SQL Server Agent and testing again. Your results from sysjobhistory lead me to believe that isn't because of a PowerShell error. You have a success record but you also have an in progress record. I've seen the SQL Agent act very odd sometimes and restarting it was the only thing I've figured out to fix it. For example, I've had jobs ...


0

I totally agree with that TRUSTWORTHY is not all that trustworthy of an approach. You should choose another way to get the result that you need. I recently posted a way to grant the starting of a SQL Agent job to another user or group of users. You can see it at: Allow non-sys admin, non-owner of a job to execute it The third option, a security table and ...


1

Even though you can use xp_cmdshell to copy the directory, I would not recommend to do it using SQL Server. You were right in thinking of using PowerShell and/or robocopy to copy files or directory to wherever you want. Powershell will even allow you to send email once the task is completed. Currently, in my company, we use native sql server backups with ...


4

I don't like the TRUSTWORTHY option because it significantly increases your exposure to a variety of things. As Remus explains in this answer, it essentially elevates any db_owner to sysadmin. Some other things worth reading are a series on TRUSTWORTHY by Sebastian Meine, the BOL topic, and a KB article (even though the assembly portions may not be relevant ...


1

Can not comment as i have not enough points so writing answer. Seems like this is cross database ownership chaining issue. If this procedure is created outside of msdb then is has no access to objects in the referenced database, even if "EXECUTE AS" impersonates user as a member of sysadmin role. So probable solution is: Make your database TRUSWORTHY: ...


0

A simple approach would be to split Job A's step 8 (let's call them A8a and A8b). A8a checks B's status from the msdb tables and does a waitfor delay if B has not completed, or stops if B has errored. This gets a bit tricky in restart scenarios, or if history gets purged from the tables before A8a runs. You can avoid the busy wait by using a flag. This ...


0

Are you allowed to modify the Jobs? Can you remove Step 8 through 10 in 'JOB A' create a new JOB C. Insert step 11 in 'JOB B' then call JOB C from the Job you can run 'sp_start_job' http://msdn.microsoft.com/en-us/library/ms186757.aspx If you are looking for something more robust there's a product called Tidal enterprise Job scheduler from Cisco. It has ...


5

You are using jobs incorrectly, the steps from job B should simply be in job A. Use stored procedures to group and reuse common code not jobs. However it is possible to do this, in which case this is a duplicate of Good way to call multiple SQL Server Agent jobs sequentially from one main job?


1

Here is script to get running jobs status along with Schedule information SELECT sj.name JobName, sj.start_step_id StartStepID, s.name JobScheduleName FROM msdb.dbo.sysjobactivity AS sja INNER JOIN msdb.dbo.sysjobs AS sj ON sja.job_id = sj.job_id INNER JOIN msdb.dbo.sysjobschedules AS js ON js.job_id = sj.job_id ...


1

I had the same exact issue. My SQL job would hang and say "Executing..." and never complete. Add "--trust-model always" to your .bat file. Basically your .bat file runs when you run it manually because you're the owner. It trusts you. When you run the .bat as a different user, the cmd will actually display a warning and ask you if you trust it. ...


0

Another option, if you expect to be "playing" around with the jobs order very often, you can follow the following steps. We use it extensively and it works flawlessly. create a dummy step that just let it run tsql code like: WAITFOR DELAY '00:00:01' select Quit the job reporting success behavior for On success action on Advanced tab of step ...


3

You can't disable a particular job step. You have two options, the way I see it: Change the job to have the "start step" as your second step, and have the second step's "on success action" to quit the job (if there are subsequent steps) Create a separate job with the step's logic and then only use the new job that does the work



Top 50 recent answers are included