SQL ERROR: ‘JOBID’ scripting variable not defined.

\ by

I often run scripts, especially SQL Agent job scripts using SQLCMD. Moving to SQL 2014 I ran into this error: ‘JOBID’ scripting variable not defined.

I see it is because I also use a token variable in the last job step. A quick solution for me was to add the -x parameter to SQLCMD ( -x (disable variable substitution) ) as suggested as one of the workarounds here.

 

Posted in: Files, SQL, SQLAgent

SQL Agent Jobs – Listing job owners even those in AD Groups

\ by

I sometimes find job owners are from an Active Directory group. Here’s a report on how to find these. (The example code also shows one way to delete the jobs.)

USE msdb
go

SELECT 'EXEC sp_delete_job @job_name = ''' + sj.name + ''';' AS RunSQL,
 sj.name AS 'job',
 SUSER_SNAME(owner_sid) AS 'owner',
 spr.NAME AS 'proxy'
 --,sj.*
FROM msdb.dbo.sysjobs sj
LEFT JOIN msdb.dbo.sysjobsteps sjt
ON sj.job_id = sjt.job_id
LEFT JOIN msdb.dbo.sysproxies spr
ON spr.proxy_id = sjt.proxy_id
ORDER BY sj.name
Posted in: SQL, SQLAgent

Msg 1934, Sev 16: UPDATE STATISTICS failed because the following SET options have incorrect settings: ‘ARITHABORT’.

\ by

Got this when I ran my sp__updatestats procedure in a SQL Agent jobstep, but it worked in Query Analyzer. The reason? The default settings are probably different in QA rather than external connections similar to SQL Agent.

Msg 1934, Sev 16: UPDATE STATISTICS failed because the following SET options have incorrect settings: 'ARITHABORT'. [SQLSTATE 42000]

The solution was to recreate the procedure that ran UPDATE STATISTICS using a SET command within the procedure. 

CREATE PROCEDURE dbo.sp__UpdateStats
AS
SET ARITHABORT ON 
SET QUOTED_IDENTIFIER ON 
...

We started to get this problem after I added a computed column to a table. 

Posted in: ERRORs, SQL, SQLAgent

NOTE: Failed to notify ‘operator’ via email.

\ by

On SQL 2005 you have to tell SQL Server Agent / Properties / Alert System, which profile that you'd like to enable. Here's what it does.

e.g. and don't forget to restart SQL Agent.

USE [msdb]
GO
EXEC master.dbo.xp_instance_regwrite 
   N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent', 
   N'DatabaseMailProfile', 
   N'REG_SZ', 
   N'MyDatabaseProfile'
GO

Please refer MSDN

To set up SQL Server Agent Mail to use Database Mail

  1. In Object Explorer, expand a server.

  2. Right-click SQL Server Agent, and then click Properties.

  3. Click Alert System.

  4. Select Enable Mail Profile.

  5. In the Mail system list, choose Database Mail.

  6. In the Mail profile list, select a mail profile for Database Mail.

 

Posted in: SQL Server, SQLAgent

Installing SQL 2005 Performance Dashboard Reports

\ by

I'm a little obtuse sometimes so when following the install instructions I didn't see where this was installed on my PC.

SQL Server 2005 Performance Dashboard Reports

So searching my entire PC here is the script that now needs to be run on each instance of SQL 2005 you want to monitor. 
      C :\Program Files\SQL Server DBA Dashboard

Here are some highlights from the script that may concern you.


CREATE ASSEMBLY DBA_Dashboard
FROM 'C:\Program Files\SQL Server DBA Dashboard\DBA_Dashboard.dll'
WITH PERMISSION_SET = SAFE

GO

and

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'SS_DBA_DASHBOARD_GATHER_STATS', 
... So I guess we'll be using SQL Agent, or not with SQLEXPRESS on my PC …

Configuration option 'show advanced options' changed from 1 to 1. Run the RECONFIGURE statement to install.
Configuration option 'clr enabled' changed from 1 to 1. Run the RECONFIGURE statement to install.
Configuration option 'Ole Automation Procedures' changed from 1 to 1. Run the RECONFIGURE statement to install.
SQLServerAgent is not currently running so it cannot be notified of this action.
SQLServerAgent is not currently running so it cannot be notified of this action.

 

Posted in: SQLAgent

Using SQLAgent Tokens – Automatically add a schedule

\ by

Automatically add a schedule to a SQL Agent job. This example adds one that will run two hours from now, once. Left to do: what if no longer today?

More tokens.

DECLARE @vcSchedule VARCHAR(128),
   @nJOBID uniqueidentifier,
   @nDATE INT,
   @nTIME INT,
   @nHours INT
SELECT @vcSchedule = 'Reschedule',
   @nJOBID = CONVERT(uniqueidentifier, [JOBID]),
   @nDATE = [DATE],
   @nTIME = [TIME],
   @nHours = '020000' + @nTIME
--SELECT @nJOBID, @nDATE, @nTIME, @nHours 

IF EXISTS (SELECT [name] FROM [msdb].[dbo].[sysjobschedules] WHERE job_id = @nJOBID AND name= @vcSchedule)
  EXEC msdb..sp_delete_jobschedule @job_id=@nJOBID, @name=@vcSchedule

EXECUTE msdb.dbo.sp_add_jobschedule 
   @job_id = @nJOBID, 
   @name = @vcSchedule, @enabled = 1, 
   @freq_type = 1, 
   @active_start_date = @nDATE, 
   @active_start_time = @nHours, 
   @freq_interval = 1, --once
   @freq_subday_type = 1, --At the specified time
   @freq_subday_interval = 1, 
   @freq_relative_interval = 0, 
   @freq_recurrence_factor = 0, 
   @active_end_date = 99991231, 
   @active_end_time = 235959

Posted in: SQL, SQLAgent