ERROR: Database Mail is not enabled for agent notifications.

\ by

I missed a step setting up SQL Agent on a new server. So here it is again.

/*
ENABLE SQL Server Agent to use the Database Mail
*/
-- Make sure SQL Server Agent is running.
EXEC [master].[dbo].[xp_servicecontrol]
 'QUERYSTATE',
 'SQLServerAgent'
 GO

USE [msdb]
--1)
EXEC [msdb].[dbo].[sp_set_sqlagent_properties]
 @email_save_in_sent_folder = 1
 
--2) We want to Use DatabaseMail
EXEC [master].[dbo].[xp_instance_regwrite]
 N'HKEY_LOCAL_MACHINE',
 N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent',
 N'UseDatabaseMail',
 N'REG_DWORD',
 1

--3) We want to have "SQLMail Profile" be the DatabaseMail profile SQL Agent uses.
EXEC [master].[dbo].[xp_instance_regwrite]
 N'HKEY_LOCAL_MACHINE',
 N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent',
 N'DatabaseMailProfile',
 N'REG_SZ',
 N'SQLMail Profile'

--
-- STOP/START SQL Server Agent
--
EXEC [master].[dbo].[xp_servicecontrol]
 'STOP',
 'SQLServerAgent'
 GO 
EXEC [master].[dbo].[xp_servicecontrol]
 'START',
 'SQLServerAgent'
 GO

Also see NOTE: Failed to notify ‘operator’ via email.

Posted in: SQLAgent \ Tagged:

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 Server: List Service Accounts’ Owners

\ by

Here’s a couple of ways to get it via code.

/*


*/
DECLARE @DBEngineLogin VARCHAR(100)
DECLARE @AgentLogin VARCHAR(100)

EXECUTE [master].[dbo].[xp_instance_regread]
@rootkey = N'HKEY_LOCAL_MACHINE',
@key = N'SYSTEM\CurrentControlSet\Services\MSSQLServer',
@value_name = N'ObjectName',
@value = @DBEngineLogin OUTPUT

EXECUTE [master].[dbo].[xp_instance_regread]
@rootkey = N'HKEY_LOCAL_MACHINE',
@key = N'SYSTEM\CurrentControlSet\Services\SQLServerAgent',
@value_name = N'ObjectName',
@value = @AgentLogin OUTPUT

SELECT [DBEngineLogin] = @DBEngineLogin,
[AgentLogin] = @AgentLogin
GO

SELECT [servicename],
[service_account]
FROM [sys].[dm_server_services]
GO
Posted in: SQL, SQL Server

SQL ERROR: SSPI handshake failed with error code

\ by 
DESCRIPTION: SSPI handshake failed with error code 0x8009030c, state 14 while establishing a connection with integrated security; the connection has been closed. Reason: AcceptSecurityContext failed. The Windows error code indicates the cause of failure.  [CLIENT: 10.12.23.345].

This can be related to an AD login account expiring and the user leaving SSMS or something running on their PC. The account eventually locks out and SQL sees this message until the user resets their password.

Posted in: ERRORs, Permissions, SQL Server, Windows

SELECT TOP n rows

\ by

I love how top can accept a variable. You could pass it into a procedure.

DECLARE @vcOut VARCHAR(MAX),
    @nTop INT

SELECT  @nTop = 5
SELECT TOP (@nTop)
        @vcOut = COALESCE(@vcOut, '') + NAME + CHAR(10)
FROM    sys.sysdatabases

PRINT @vcOut
--
Posted in: Coding, SQL

SQL Processes, sys.dm_exec_connections and last sql statement

\ by

Last SPIDs, their statement, network packet size.

SELECT  c.session_id,
        s.host_name,
        s.login_name,
        [LastStatement].[[text]],
        c.net_packet_size,
        s.login_time,
        s.status,
        d.name
FROM    sys.dm_exec_connections c
INNER JOIN sys.dm_exec_sessions s
ON      c.session_id = s.session_id
CROSS APPLY sys.dm_exec_sql_text(most_recent_sql_handle) AS LastStatement
LEFT OUTER JOIN
	sys.databases d on d.database_id = [LastStatement].dbid
ORDER BY s.[status],
        s.login_time DESC,
        s.[host_name]
  
--

Source

Posted in: SQL