mbourgon

--2013/06/17 1.10 mdb thebakingdba.blogspot.com 
--			added filter for "last_distsync is null", which should only be running or never-run.

DECLARE @min INT, @max INT, @sql NVARCHAR(4000)
DECLARE @repl_server_list TABLE(id INT IDENTITY, srvname sysname)

SET NOCOUNT ON 
--build a stripped down temp table; the OPENROWSET allows us to skip fields we don't care about. 
IF OBJECT_ID('tempdb..#tmp_subscriptiondata') IS NOT NULL
    DROP TABLE #tmp_subscriptiondata

mbourgon

#######################  
<#  
Purpose: Take a list of servers from a table.  Run a series of scripts in a folder against each server, saving to a table.
Requirements: invoke-sqlcmd2, split-job (1.2.1), write-datatable, and add-sqltable.  
                See link below for those scripts; split-job is from Poshcode
Installation: 
                Create a folder called c:\sql_tools.  
                Put each of the 4 scripts in it.  
                Create a folder called c:\sql_tools\repository_scripts.
                Put whatever SQL code you want to run against each server in the repository_scripts folder.

mbourgon

-- If the Event Session Exists, drop it first
IF EXISTS (SELECT 1 
			FROM sys.server_event_sessions 
			WHERE name = 'UnknownAppHosts')
	DROP EVENT SESSION UnknownAppHosts 
	ON SERVER;

-- Create the Event Session

mbourgon

/*
SQL Server Agent Job: Failure
Execute SQL:
exec yourdbname.dbo.ssis_job_failure_info_fromjobname @job_name = '<%ObjectName%>'

If performance is lacking, add the following indexes to SSISDB, as these tables
do cascading deletes and are FKs and also aren't indexed.  Index script found
on a MS connect item

CREATE NONCLUSTERED INDEX [NCIX_operation_id]

mbourgon

DECLARE	@DTS_Packages TABLE
	(
	PackageName SYSNAME,
	PackageDescription SYSNAME,
	Creator SYSNAME,
	CreateDate DATETIME,
	PackageXML XML
	)

INSERT	INTO @DTS_Packages

mbourgon

/*
Version 1.01 - 2013/06/27 mbourgon thebakingdba.blogspot.com
Purpose: Look through the RDLs stored in your SSRS server (ReportServer DB), returning info like the Datasource and Query. 
  		This version doesn't contain the DataSource.  1.1 or 1.2 will, since it can be stored in 2 different places.

Getting the RDL cribbed from http://markvsql.com/2009/11/find-all-deployed-ssrs-reports-that-reference-a-particular-field/
XML query from Mikael Eriksson on StackOverflow http://stackoverflow.com/questions/17332257/sql-query-xml-while-ignoring-namespace
(the trick is ignoring the namespace, which led me to this non-normal [for me anyway] syntax) Thanks Mikael!

1.00 - first revision - it's alive!

mbourgon

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,

mbourgon

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

mbourgon

--mdb 2013/06/01  Replication Monitor Part 3 - checking for delayed subscribers--      This script specifically looks for subscribers that haven't had changes applied recently
--        I believe this uses the same code that replication itself does for the "72 hour" inactivation
--2013/06/17 1.10 mdb thebakingdba.blogspot.com
--            added filter for "last_distsync is null", which should only be running or never-run.
--2013/08/08 2.00 mdb Version 2 - now works on SQL Server 2012 (via RESULT SETS).  2012 has a bug with sp_describe_first_result_set (please vote!)
--http://connect.microsoft.com/SQLServer/feedback/details/737341/sql-server-2012-openrowset-on-msdb-dbo-sp-help-job-throws-error

DECLARE @min INT, @max INT, @sql NVARCHAR(4000)
DECLARE @repl_server_list TABLE(id INT IDENTITY, srvname sysname)

mbourgon

<#
.SYNOPSIS
  Will script an object from SQL Server and CHECKIN/ADD to TFS.
.EXAMPLE 
  sql_script_to_TFS.ps1 -server yourservername -Database yourdatabasname -ScriptType "FULL" -Author yourTFSname -Comment "full checkin of database"

#>
# Purpose - given parameters, script out an object from a SQL Server, using SMO, then check it into TFS.
#Scripting code gleefully copied from Phil Factor - the great parts that work are his, the errors are mine
# https://www.simple-talk.com/sql/database-administration/automated-script-generation-with-powershell-and-smo/