paschott

Import-Module dbatools

$scripts = get-childitem "C:\ScriptsToRun\" -Filter *.sql | sort-object Name
$servers = "servername"
$database = "databasename"
    
foreach ($script in $scripts) {
    $OutputFile = $script.directoryname + "\" + $script.basename + ".txt"
    Write-Host $OutputFile
    Invoke-DbaQuery -SqlInstance $servers -File $script.FullName -Database $database -MessagesToOutput | Out-File -FilePath $OutputFile

paschott

#PowerShell script
################################
########## PARAMETERS ##########
################################ 
$projectFolder = "C:\SSIS\myProject\myProject"
$dtutilPath = "C:\Program Files\Microsoft SQL Server\130\DTS\Binn\dtutil.exe"
# The number changes per SQL Server version
# 130=2016, 120=2014, 110=2012
# Also check the drive where SQL Server is
# installed

paschott

DECLARE @NewDataFileGrowth varchar(100) = '500MB'
DECLARE @NewLogGrowth varchar(100) = '100MB'

select 
  CASE WHEN physical_name like '%.ldf'
    THEN 'ALTER DATABASE [' + DB_NAME(mf.database_id) + '] MODIFY FILE (NAME = [' + mf.name + '], FILEGROWTH = ' + @NewLogGrowth + ')'
    ELSE 'ALTER DATABASE [' + DB_NAME(mf.database_id) + '] MODIFY FILE (NAME = [' + mf.name + '], FILEGROWTH = ' + @NewDataFileGrowth + ')'
  END as ChangeAutoGrowSettings, 
  DB_NAME(mf.database_id) database_name, 
  mf.name logical_name, 

paschott

--replace the URL in the function with the appropriate URL for your file
SELECT TOP 1000 *
FROM sys.fn_get_audit_file('https://subscription.blob.core.windows.net/paths/auditfile.xel', default, default)
WHERE (event_time <= '2019-02-20T21:56:36.631Z')
/* additional WHERE clause conditions/filters can be added here */
and action_id IN ('DBAF') --look specifically for auth failures
ORDER BY event_time DESC

paschott

--Create table to store posts
CREATE TABLE [dbo].[Posts](
	[Username] [nvarchar](255) NULL,
	[Type] [nvarchar](255) NULL,
	[Subtype] [nvarchar](255) NULL,
	[TS] [nvarchar](255) NULL,
	[Channel] [nvarchar](255) NOT NULL,
	[MessageText] [nvarchar](max) NULL,
	[MessageDate] [datetime2](7) NULL,
	[PostID] [bigint] IDENTITY(1,1) NOT NULL,

paschott

# lets just bucket all the comments from all users and then print it out
# for each file, read the file sequentially and add it to a hash table count

$database = "SQLSlack"
$server = "localhost"
$parent_folder = "C:\SlackExport"
Import-Module SqlServer


function Invoke-Sql {

paschott

--Note that this uses a DB Mail Profile named "Default".
CREATE PROC dbo.pr_SendSSISErrors
	@FolderName sysname = NULL,
	@ProjectName sysname = NULL,
	@PackageName sysname = NULL,
	@ToAddress NVARCHAR(500)
AS
BEGIN --Proc
SET NOCOUNT ON

paschott

--Create test table to represent data we want to import.
CREATE TABLE #ProductsToImport (
    OriginalSystemID INT,
    ProductName NVARCHAR(50),
    ProductNumber NVARCHAR(25),
    SafetyStockLevel SMALLINT,
    ReorderPoint SMALLINT,
    StandardCost MONEY,
    ListPrice MONEY,
    DaysToManufacture INT,

paschott

CREATE TABLE TriggerStatus (
TriggerStatusID INT IDENTITY(1,1) NOT NULL,
TriggerName varchar(100) NOT NULL,
IsEnabled BIT NOT NULL CONSTRAINT DF_TriggerStatus_IsEnabled DEFAULT 1,
CONSTRAINT PK_TriggerStatus PRIMARY KEY CLUSTERED (TriggerStatusID)
)
GO

CREATE TABLE MyAuditTable (
MyAuditTableID INT NOT NULL IDENTITY(1,1),

paschott

--With thanks to Bill Fellows for the script from here:
-- http://stackoverflow.com/questions/21781351/how-can-i-clean-up-the-ssisdb
USE SSISDB;

SET nocount ON;

IF Object_id('tempdb..#DELETE_CANDIDATES') IS NOT NULL
  BEGIN
      DROP TABLE #delete_candidates;
  END;