[Event Notifications] Emailed report based off collected ERRORLOG messages

One of the cool side effects of Event Notifications is the easy collection of SQL Errorlog.  While it doesn't collect all messages, it collects enough for me to be aware of problems.  Alas, it does not collect enough for me to get an email when a server decides to shut down... the skipped messages are usually around startup/shutdown and error dumps.  See https://connect.microsoft.com/SQLServer/feedback/details/782901/event-notification-not-all-errorlog-notices-are-sent-to-service-or-queue#details for more info.


BUT... since it's EN, you get them in real-time, so you could theoretically do other things with them.  This report runs once a day, but I'm sure someone in the comments will come up with a new use I hadn't considered.  I use Alerts on the SQL Server, but I could think of other triggers (what if you suddenly started getting failed logins?)

Another advantage: this has more than the sql ERRORLOG file has - it's closer to what the Event Logs have.  Which means that you don't just got "query ran out of memory", you get the name of the login as well.  REALLY handy, that.

For this report, I've just built a simple 3-way email.  If there are severity errors, you get a block of those.  If there are non-severity errors, they go in a different block And finally, login emails get their own. 

What to do: 
 1) Set up EN to capture ERRORLOG.  See my "Master of All I Survey" post for more.  If you already have EN set up, you'll need to DROP/CREATE a new EN that includes it.  (No alters, sorry)
2) Create a table with the exclusion table.  This allows you to skip records you know about, without changing code.

 CREATE TABLE [dbo].[EN_sql_errors_exclusion_email]
(
[id] [int] NOT NULL IDENTITY(1, 1),
[Exclusions] [varchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[EN_sql_errors_exclusion_email] ADD CONSTRAINT [PK__EN_sql_errors__ID] PRIMARY KEY CLUSTERED  ([id]) ON [PRIMARY]
GO


3) Create a job.  Here's my description:
This job uses EventNotificationRec..ENAudit_Events and looks for all SQL errors since 1 day ago , split up by those that have 'severity' in the text data, those that don't, and failed logins.  You can exclude data by adding rows to table EN_sql_errors_exclusion_email (uses LIKE, so 'blah%blah' will work; code already includes % at beginning & end)

Enjoy.  Let me know if you have any questions or concerns.

	SET NOCOUNT ON
	SET ANSI_NULLS ON
	SET QUOTED_IDENTIFIER ON
	IF OBJECT_ID('tempdb.dbo.#errors_mail') IS NOT NULL
	DROP TABLE #errors_mail;
	WITH cte
	AS (SELECT servername
	,loginname
	,PostTime
	,C.value('ApplicationName[1]', 'varchar(200)') AS ApplicationName
	,DatabaseName
	,C.value('TextData[1]', 'varchar(max)') AS TextData
	,C.value('HostName[1]', 'varchar(200)') AS HostName
	FROM EventNotificationRec..ENAudit_Events
	OUTER APPLY message_body_xml.nodes('EVENT_INSTANCE') AS T (C)
	WHERE EventType = 'ERRORLOG'
	)
	SELECT cte.*
	INTO #errors_mail
	FROM cte
	LEFT JOIN EventNotificationRec.dbo.EN_sql_errors_exclusion_email e WITH (NOLOCK)
	ON cte.textdata LIKE '%' + e.exclusions + '%'
	WHERE --textdata LIKE '%severity%' --AND textdata NOT LIKE '%Login failed for user%'
	[PostTime] > getdate()-1 --DATEADD(dd, DATEDIFF(dd, 1, GETDATE()), 0)
	AND e.exclusions IS NULL
	ORDER BY posttime DESC
	DECLARE @tableHTML NVARCHAR(MAX)
	,@MailSubject VARCHAR(100)
	SELECT @MailSubject = 'Event Notifications - ' + CONVERT(VARCHAR,COUNT(*)) + ' SQL Errors since ' + CONVERT(VARCHAR(20),GETDATE()-1)
	FROM #errors_mail
	SET @tableHTML = N'<H3>Severity Errors since ' + CONVERT(VARCHAR(20), GETDATE()-1) + '</H3>'
	+ N'<table border="1">' + N'<tr><th> ServerName </th>' + N'<th> LoginName </th>'
	+ N'<th> PostTime </th>' + N'<th>ApplicationName</th>' + N'<th>DatabaseName</th>' + N'<th>TextData</th>'
	+ N'<th> HostName </th></tr>' + ISNULL(CAST((SELECT td = RTRIM(LTRIM(ISNULL(T.ServerName,'')))
	,''
	,td = RTRIM(LTRIM(ISNULL(T.LoginName,'')))
	,''
	,td = ISNULL(CONVERT(VARCHAR(16), T.PostTime, 120),'')
	,''
	,td = ISNULL(RTRIM(LTRIM(T.ApplicationName)),'')
	,''
	,td = ISNULL(RTRIM(LTRIM(T.DatabaseName)),'')
	,''
	,td = ISNULL(RTRIM(LTRIM(T.TextData)),'')
	,''
	,td = ISNULL(RTRIM(LTRIM(T.HostName)),'')
	FROM #errors_mail T
	WHERE textdata NOT LIKE '%Login failed for%'
	AND textdata LIKE '%Severity%'
	ORDER BY T.ServerName, T.PostTime DESC
	FOR
	XML PATH('tr')
	,TYPE
	) AS NVARCHAR(MAX)),'') + N'</table>';
	--now add nonseverity errors
	SET @tableHTML = ISNULL(@tableHTML,'') + '<br><br>' + N'<H3>non-Severity Errors since ' + CONVERT(VARCHAR(20), GETDATE()-1) + '</H3>'
	+ N'<table border="1">' + N'<tr><th> ServerName </th>' + N'<th> LoginName </th>'
	+ N'<th> PostTime </th>' + N'<th>ApplicationName</th>' + N'<th>DatabaseName</th>' + N'<th>TextData</th>'
	+ N'<th> HostName </th></tr>' + ISNULL(CAST((SELECT td = RTRIM(LTRIM(ISNULL(T.ServerName,'')))
	,''
	,td = RTRIM(LTRIM(ISNULL(T.LoginName,'')))
	,''
	,td = ISNULL(CONVERT(VARCHAR(16), T.PostTime, 120),'')
	,''
	,td = ISNULL(RTRIM(LTRIM(T.ApplicationName)),'')
	,''
	,td = ISNULL(RTRIM(LTRIM(T.DatabaseName)),'')
	,''
	,td = ISNULL(RTRIM(LTRIM(T.TextData)),'')
	,''
	,td = ISNULL(RTRIM(LTRIM(T.HostName)),'')
	FROM #errors_mail T
	WHERE textdata NOT LIKE '%Severity%'
	ORDER BY T.ServerName, T.PostTime DESC
	FOR
	XML PATH('tr')
	,TYPE
	) AS NVARCHAR(MAX)),'') + N'</table>';
	SET @tableHTML = ISNULL(@tableHTML,'') + '<br><br>' + N'<H3>Login Errors since ' + CONVERT(VARCHAR(20), GETDATE()-1) + '</H3>'
	+ N'<table border="1">' + N'<tr><th> ServerName </th>' + N'<th> TextData </th>'
	+ N'<th> Count(*) </th>' + N'<th>Started</th>'
	+ N'<th> Ended </th></tr>' + ISNULL(CAST((SELECT td = RTRIM(LTRIM(ISNULL(T.ServerName,'')))
	,''
	,td = ISNULL(RTRIM(LTRIM(SUBSTRING(textdata,CHARINDEX('Login failed for', textdata), 200))),'')
	,''
	,td = isnull(CONVERT(VARCHAR(25), NULLIF(COUNT(*),0)),'')
	,''
	,td = isnull(CONVERT(VARCHAR(16), MIN(posttime), 120),'')
	,''
	,td = isnull(CONVERT(VARCHAR(16), Max(posttime), 120),'')
	FROM #errors_mail T
	WHERE textdata LIKE '%Login failed for%'
	GROUP BY t.servername, SUBSTRING(textdata,CHARINDEX('Login failed for', textdata), 200)
	ORDER BY T.ServerName, SUBSTRING(textdata,CHARINDEX('Login failed for', textdata), 200) DESC
	FOR
	XML PATH('tr')
	,TYPE
	) AS NVARCHAR(MAX)),'') + N'</table>';
	EXEC msdb.dbo.sp_send_dbmail @profile_name = 'yournamegoeshere', @recipients = 'tbd@dev.null',
	@subject = @MailSubject, @body = @tableHTML, @body_format = 'HTML';

view raw EN_sql_ERRORLOG_email hosted with ❤ by GitHub

[SSIS] Finding your connection strings in SQL Server 2012

(update 2013/07/18: hey, grabbing the data source doesn't actually get the default value.  That's stored in the same tables, but I need to figure out how to pull those out (ObjectType, maybe).  Give me a few days.)

(update 2013/07/22 here's what I've cobbled together, though it needs to be integrated with the below

SELECT  projects.name, ConnStr.object_name, ConnStr.parameter_name, ConnStr.description,
        ConnStr.design_default_value, ConnStr.default_value,
        SrvName.parameter_name, SrvName.description, SrvName.design_default_value,
        SrvName.default_value,
        --the catalog name
        DBName.parameter_name, DBName.design_default_value, DBName.default_value
    FROM ssisdb.catalog.projects
    INNER JOIN     [SSISDB].[catalog].[object_parameters] ConnStr
    ON SSISDB.catalog.projects.project_id = ConnStr.project_id
--get the server name.  Left outer join as it doesn't appear in the table otherwise
    LEFT OUTER JOIN [SSISDB].[catalog].[object_parameters] SrvName
    ON connstr.[object_name] = SrvName.[object_name]
    AND connstr.parameter_name LIKE 'CM%ConnectionString'
    AND srvname.parameter_name LIKE 'CM%ServerName'
--get the database name as well
    LEFT OUTER JOIN [SSISDB].[catalog].[object_parameters] DBName
    ON connstr.[object_name] = DBName.[object_name]
    AND connstr.parameter_name LIKE 'CM%ConnectionString'
    AND DBName.parameter_name LIKE 'CM%InitialCatalog'
WHERE ConnStr.parameter_name LIKE 'CM%ConnectionString'
    AND LEFT(connstr.parameter_name, LEN(connstr.parameter_name) - CHARINDEX('.',REVERSE(connstr.parameter_name)))
     = LEFT(SrvName.parameter_name, LEN(SrvName.parameter_name) - CHARINDEX('.',REVERSE(SrvName.parameter_name)))
    AND LEFT(connstr.parameter_name, LEN(connstr.parameter_name) - CHARINDEX('.',REVERSE(connstr.parameter_name)))
     = LEFT(DBName.parameter_name, LEN(DBName.parameter_name) - CHARINDEX('.',REVERSE(DBName.parameter_name)))
ORDER BY projects.name, ConnStr.object_name, connstr.parameter_name
)


Part of the "fun" with SSIS has always been the myriad of ways a parameter can be set.  Variables, Configuration Files, Properties, Jobs.... and probably 1 or 2 others, just to confuse things.

I've already posted (http://thebakingdba.blogspot.com/2013/05/ssis-finding-your-connection-strings-in.html) about the connection strings in 2008/R2.  This is the version for 2012... 

In theory, it should be simple - if you're using the new methodology, you're developing with SSISDB, and using it to hold your configuration values.   Thus, it has all the connection string details within.

However, it can be overridden at the job level... so you potentially need both.  What I do is grab the most recent run of the package, as well as what SSISDB says it has for the package, and present both.

Let me know if there are any questions.    I see on mine, for instance, dupes where the connection string is the same, but the parameter name is different.  Not sure why yet.

Let me know if you see any problems or places to improve, or heck, maybe I'm just doing it wrong.

	USE ssisdb
	--thebakingdba.blogspot.com 2013/07/18 1.00
	--purpose - grab the connection strings from SSISDB for the package and the job runs.
	SELECT ISNULL(project_based_values.project_name,
	job_based_values.project_name) AS project_name,
	ISNULL(project_based_values.package_name,
	job_based_values.package_name) AS package_name,
	ISNULL(project_based_values.parameter_name,
	job_based_values.parameter_name) AS parameter_name,
	project_based_values.Connection_String AS Project_Connection_String,
	job_based_values.connection_string AS Job_Connection_String,
	CASE WHEN project_based_values.Connection_String = job_based_values.connection_string
	THEN 'SAME'
	WHEN project_based_values.Connection_String = job_based_values.connection_string
	THEN 'DIFFERENT'
	WHEN project_based_values.Connection_String IS NULL
	THEN 'PROJECT'
	WHEN job_based_values.connection_string IS NULL THEN 'JOB'
	END AS Values_From
	FROM (
	SELECT ip.name AS project_name, [a].[object_name] AS package_name,
	[a].[parameter_name],
	[design_default_value] AS Connection_String
	FROM (
	SELECT [object_name], [parameter_name],
	MAX([project_version_lsn]) AS max_project_version_lsn
	FROM [internal].[object_parameters]
	WHERE [parameter_name] LIKE '%connectionstring%'
	GROUP BY [object_name], [parameter_name]
	) a
	INNER JOIN [internal].[object_parameters] a2
	ON [a].[object_name] = [a2].[object_name]
	AND [a].[parameter_name] = [a2].[parameter_name]
	AND a.[max_project_version_lsn] = [a2].[project_version_lsn]
	INNER JOIN internal.projects ip
	ON a2.project_id = ip.project_id
	) project_based_values
	FULL OUTER JOIN (
	SELECT project_name, package_name, parameter_name,
	parameter_value AS connection_string
	FROM internal.[execution_parameter_values]
	INNER JOIN internal.executions
	ON internal.execution_parameter_values.execution_id = internal.executions.execution_id
	INNER JOIN (
	SELECT MAX(execution_id) AS max_execution_id
	FROM internal.executions
	GROUP BY project_name, package_name
	) most_recent_run
	ON internal.executions.execution_id = most_recent_run.max_execution_id
	WHERE parameter_name LIKE '%.connectionstring'
	) job_based_values
	ON project_based_values.project_name = job_based_values.project_name
	AND REPLACE(project_based_values.package_name, '.dtsx', '') = REPLACE(job_based_values.package_name,
	'.dtsx', '')
	AND project_based_values.parameter_name = job_based_values.parameter_name
	AND project_based_values.Connection_String = job_based_values.connection_string
	ORDER BY 1, 2, 3, 6

view raw gistfile1.sql hosted with ❤ by GitHub

[Replication] Fun little gotcha with tables that have a UNIQUE index/constraint.

So I've been bitching to one of our devs about how his updates to a replicated table suck.  Today I went looking.  What the.... it issues a delete, then inserts the row?  Why are you doing it that way?  Well, he's not.  SQL is.  : - \

Turns out, if you have a unique index or constraint (as opposed to the default PK index, I suppose...), then it deletes then inserts the row.  Which sucks when you have a massive wide table with a ton of records.

Live and learn.  Hopefully this helps somebody else.

https://blogs.msdn.com/b/repltalk/archive/2011/10/04/sql-server-transaction-replication-bounded-update-or-why-my-update-was-transformed-into-an-delete-insert-pair.aspx?Redirected=true