New answers tagged

2

You need to turn quoted identifiers on in the T-SQL job step. SET QUOTED_IDENTIFIER ON; By default, SQL Server Agent treats quotation marks as if QUOTED_IDENTIFIER is OFF, which means items in quotes are treated as literals. Turning QUOTED_IDENTIFIER ON will allow those items to be treated as column names. See this MSDN blog entry about the issue.


0

You haven't done anything wrong or gone crazy - it's a known bug and we've experienced the same thing. Our process was changed that we would script out job status and reapply after any maintenance plan work - until we could move off of maintenance plans entirely (which we did). Continuing on from the comments on the main question, there is an owner ...


3

If you direct output for the step into a "table", like this: You can see all output from the last run of the job, including PRINT, RAISERROR, and results from queries, by looking in the msdb database at the dbo.sysjobstepslogs table: SELECT JobName = sj.name , StepName = sjs.step_name , DateModified = sjsl.date_modified , LogText = sjsl.log ...


1

The account that you use to run the job WILL need permissions to delete those files. To test your process, login as that specific user and test your job--specifically the deletion of files-- with that user. That being said, I've had issues purging SQL Server backups the way I'd like so I created this CmdExec script do so. To delete files and get error ...


4

For T-SQL Job Steps, the "output" refers to "messages" -- notices sent via PRINT and RAISERROR. Result sets are also included as "output", but only if there are no PRINT / RAISERROR messages, else it is only the PRINT / RAISERROR messages that are included. Try this test: Job Step 1 Definition: PRINT ' ** Line 1 ** '; SELECT ' ** Line 2 ** ' AS [Line ...


0

Since I don't actually see any transactions created within your SP I can only guess. But I can tell you that transactions inside a stored procedure are .. tricky. If you have nested transactions, for example one created outside a stored procedure and one created inside the stored procedure, SQL may have a transaction count of two but it's still really only ...


0

If you don't want your long running job (Job 2) to mess with the fast running job (Job 1), you can add a step in the first job (Job 1) which will check if Job 2 is running and wait for it to finish. With this, your Job 1 runs will be decreased but the duration of each run might be as you expected. And are you using WITH(NOLOCK) in your select statements ?


3

There is no way to directly modify the subject and body fields of the email alerts generated by SQL Server Agent. Having the alert run a job instead of directly generating an alert email would be the correct way of getting to your desired state. Having said that, it's not necessary to use PowerShell for that if you don't want to. Unfortunately, there does ...


6

Maybe the wording is unclear, but least privilege applies to all activities and should never be ignored. The least privilege you need to perform database maintenance is sysadmin. Lower privileged users could perform those activities too (I'm thinking of db_owners), but the least privilege needed to perform any activity on a SQL Server instance is sysadmin, ...



Top 50 recent answers are included