Skip to content

Instantly share code, notes, and snippets.

View matt40k's full-sized avatar

Matt Smith matt40k

View GitHub Profile
@matt40k
matt40k / reduce_ssisdb.sql
Created August 27, 2015 09:49
Reduce the retention and max project versions kept to help reduce the SSISDB size. Check the settings
-- Make a note of the before
-- Tempted to add into to preserve the before
select * from catalog.catalog_properties
-- Change the settings
exec catalog.configure_catalog retention_window, 30
exec catalog.configure_catalog max_project_versions, 2
-- Check the settings have stuck
select * from catalog.catalog_properties
@matt40k
matt40k / unblock.txt
Created August 28, 2015 13:45
Rows were detected. The schema update is terminating because data loss might occur - unblock
Error:
Rows were detected. The schema update is terminating because data loss might occur
uncheck the "Block incremental deployment if data loss might occur"
In publish database - advanced publish settings > general tab
@matt40k
matt40k / RenameFiles_ChangeSchema
Created May 27, 2015 21:26
Create SQL objects with the file name of NNNN_SCHEMA_OBJECTNAME.sql - this script allows you to bulk change the schema name on the files
$dir = 'C:\svn\SQL'
$files = Get-ChildItem $dir -Filter *.sql
Foreach ($file in $files) {
$old = $file.FullName
$new = $old.Replace("_OldSchema_", "_NewSchema_")
Rename-Item -path $old -NewName $new
}
([xml](svn log -v --xml)).log.logentry | % {
$entry = $_;
$_.paths.path | foreach {
$obj = 1 | select -Property Revision,Author,Date,Message,Action,FilePath;
$obj.Revision = [int]$entry.Revision;
$obj.Author = $entry.Author;
$obj.Date = Get-Date $entry.Date;
$obj.Message = $entry.msg;
$obj.Action = $_.action;
$obj.FilePath = $_.InnerText;
@matt40k
matt40k / ClearCache
Created May 27, 2015 21:34
Clears the Jasper / iTrent cache on the web tier
$webserver = "localhost"
$environment = "dev"
$username = "user"
$password = "pass"
$url = 'http://' + $webserver + '/' + $enviroment + '_web/report/meta'
Function ClearCache($type)
{
return Invoke-WebRequest -Uri $url/$type -Method GET -Headers @{"Authorization" = "Basic "+[System.Convert]::ToBase64String([System.Text.Encoding]::UTF8.GetBytes($username+":"+$password ))}
@matt40k
matt40k / UpdateToSmallInt
Created May 28, 2015 10:09
Loop through folder and update the files - changes from int to smallint - save a few bits ;)
$dir = "C:\SVN\SQL"
$files = Get-ChildItem $dir -Filter *.sql
Foreach ($file in $files) {
(Get-Content $file.FullName).replace('[ETL_Run_ID] [int]','[ETL_Run_ID] [smallint]') | Set-Content $file.FullName
}
@matt40k
matt40k / CognosReports_fromDb
Created May 28, 2015 13:15
Queries the Cognos BI database for the report and packages
select
names.NAME as 'ReportName'
,packageNames.NAME as 'PackageName'
,convert(nvarchar(512),reportpath.PATHS) as 'ReportPath'
from
CMOBJNAMES names
inner join cmrefnoord1 ref on
names.CMID = ref.CMID
inner join CMOBJNAMES packageNames on
packageNames.CMID = ref.REFCMID
@matt40k
matt40k / CognosBI_Audit_Reports
Created May 28, 2015 13:17
Queries the Cognos BI Audit DB for the reports executed
SELECT
CASE
WHEN rr.[COGIPF_REPORTNAME] = 'null' THEN 'Unsaved report'
WHEN rr.[COGIPF_REPORTNAME] = '' THEN 'Unsaved report'
WHEN rr.[COGIPF_REPORTNAME] IS NULL THEN 'Unsaved report'
WHEN rr.COGIPF_REPORTNAME = 'adHocReport' THEN 'Ad-Hoc'
WHEN SUBSTRING(rr.[COGIPF_REPORTNAME], 1, 10) = 'reportView' then (SUBSTRING (rr.[COGIPF_REPORTNAME], 19, LEN (rr.[COGIPF_REPORTNAME]) - 20))
WHEN SUBSTRING(rr.[COGIPF_REPORTNAME], 1, 6) = 'report' then (SUBSTRING (rr.[COGIPF_REPORTNAME], 15, LEN (rr.[COGIPF_REPORTNAME]) - 16))
WHEN (SUBSTRING(rr.[COGIPF_REPORTNAME], 1, 5) = 'query') then (SUBSTRING (rr.[COGIPF_REPORTNAME], 14, LEN (rr.[COGIPF_REPORTNAME]) - 15 ))
ELSE 'Unsaved report'
@matt40k
matt40k / CognosBI_Audit_QueryStudio
Created May 28, 2015 13:18
Queries the Cognos BI Audit DB for the QueryStudio executions
SELECT
CASE
WHEN eq.[COGIPF_PACKAGE] = '' THEN ' '
WHEN eq.[COGIPF_PACKAGE] = 'null' THEN ' '
WHEN CHARINDEX('package', eq.[COGIPF_PACKAGE]) >0 THEN
(SUBSTRING (eq.[COGIPF_PACKAGE], (CHARINDEX('package', eq.[COGIPF_PACKAGE]) +15), (CHARINDEX(']', (SUBSTRING (eq.[COGIPF_PACKAGE], (CHARINDEX('package', eq.[COGIPF_PACKAGE]) +17), 500))))))
ELSE (SUBSTRING (eq.[COGIPF_PACKAGE], 1, LEN(eq.[COGIPF_PACKAGE])))
END AS [PACKAGE]
,eq.[COGIPF_TARGET_TYPE]
,ul.[COGIPF_USERID]
@matt40k
matt40k / CensusExportRename
Created May 28, 2015 13:57
Rename the census export files so it can be loaded into Capita One
using System;
using System.IO;
namespace BulkRename
{
class Program
{
static void Main(string[] args)
{
string inputDir = Properties.Settings.Default.InputDirectory;