Gedanken zu SQL, Reports, ETL und CRM
Set col = datagrid.Columns.Add(8) ' "8" indicates a Combo column
With col
.Caption = "Some Field"
.FieldName = "SOMEFIELD"
.Visible = true
.Width = 50
.ReadOnly = false
.DisableEditor = false
With .Items
.Add("Value 1")
.Add("Value 2")
.Add("Value 3")
End With
End With
/*
Hierarchie von Firmen
---------------------
Egal wo man steht, erst den obersten ParentAccount suchen
und von dort aus alle Kinder feststellen
--http://www.bidn.com/blogs/KeithHyer/bidn-blog/2293/hierarchy-with-recursive-cte-and-a-little-borrowed-sorting-trickery
*/
/*Muster MSCRM BusinessUnit:*/
--Select Name, * from Team order by 1
With hBU(Name, BusinessUnitId, ParentBusinessUnitIdName, ParentBusinessUnitId, Levl, sortBU)
As (
/*Anker*/
Select Name, BusinessUnitId, ParentBusinessUnitIdName, ParentBusinessUnitId , 0 Levl , CONVERT( VARchar(MAX) , ( BusinessUnit.Name ) ) as sortBU
From BusinessUnit
Where ParentBusinessUnitId IS NULL /*and Name = 'dkacrm'*/
union all
/*Recursion*/
Select BusinessUnit.Name, BusinessUnit.BusinessUnitId, BusinessUnit.ParentBusinessUnitIdName, BusinessUnit.ParentBusinessUnitId , Levl + 1 as Levl, CONVERT( VARchar(MAX) , ( SortBU + BusinessUnit.Name ) ) as sortBU
From BusinessUnit
inner join hBU on hBU.BusinessUnitId = BusinessUnit.ParentBusinessUnitId
Where BusinessUnit.ParentBusinessUnitId IS NOT NULL
)
Select coalesce(replicate(char(124)+' ',Levl-1)+replicate(char(43)+' ',Levl-Levl+1) ,'') + hBU.Name , *
from hBU
order by sortBU
/*Muster MSCRM Account:
Wobei die Felder CustomerTypeCode und ImportSequenceNumber nur Firmen/Kunden-spezifisch sind
*/
With hAcc(Name, Accountid, ParentAccountIdName, ParentAccountid, CustomerTypeCode, ImportSequenceNumber, Levl, SortAcc)
As (
/*Anker*/
Select Name, AccountId, ParentAccountIdName, ParentAccountId, CustomerTypeCode, ImportSequenceNumber, 0 Levl, CONVERT( Varchar(MAX) , Name ) as SortAcc
From Account
Where 1=1
and CustomerTypeCode in (200002 /*Debitoren*/, 200000 /*Filialen*/)
and ParentAccountId IS NULL
union all
/*Recursion in CTE*/
Select Account.Name, Account.AccountId, Account.ParentAccountIdName, Account.ParentAccountId, Account.CustomerTypeCode, Account.ImportSequenceNumber, Levl + 1, CONVERT( Varchar(MAX) , SortAcc + Account.Name ) as SortAcc
From Account
inner join hAcc on hAcc.Accountid=Account.ParentAccountId
Where 1=1
and Account.CustomerTypeCode in (200002 /*Debitoren*/, 200000 /*Filialen*/)
and Account.ParentAccountId IS NOT NULL
)
Select coalesce(replicate(char(124)+' ',Levl-1)+replicate(char(43)+' ',Levl-Levl+1) ,'') + Name as Name,
CustomerTypeCode, StringMap.Value as CustomerTypeCodeReadable,
/*Case when CustomerTypeCode = 200000 then 'Filiale' when CustomerTypeCode = 200002 Then 'Debitor' else '???' end CustomerTypeCodeReadable,*/
AccountId, ParentAccountId, ParentAccountIdName, CustomerTypeCode, ImportSequenceNumber, Levl
From hAcc
left outer join StringMap on StringMap.AttributeName = 'CustomerTypeCode' and StringMap.LangId = 1031 and StringMap.AttributeValue = hAcc.CustomerTypeCode
Order by SortAcc
/*Muster SLX Account:*/
Declare @Ankerid char(12);
SET @Ankerid = 'A6UNMA000DQO'; --z.B. Firma Muster GmbH & Co. KG (ist ein Kind weil es ein Parent hat)
--SET @Ankerid = 'A6UJ9A00050W'; --z.B. Muster Holding AG (ist OberAccount)
--Von hier aus den Obervater finden:
DECLARE @ToplevelAccountid char(12);
WITH hAcc(Account, Accountid, ParentAccountid, Levl)
AS (
--Anker:
Select Account, Accountid, Parentid AccountParentid, 0 Levl
from Account
where Accountid=@Ankerid
union all
--Rekursion in CTE:
Select Account.Account Account, Account.Accountid Accountid, Account.Parentid ParentAccountid, Levl + 1
from Account
inner join hAcc on hAcc.ParentAccountid = Account.Accountid
--where Parentid IS NOT NULL
)
--Select Account, Accountid, ParentAccountid, Levl from hAcc where ParentAccountid IS NULL
Select @ToplevelAccountid = Accountid from hAcc where ParentAccountid IS NULL;
--Print @ToplevelAccountid
--Von hier aus alle Kinder:
WITH hAcc(Account, Accountid, ParentAccount, ParentAccountid, Levl, SortAcc)
AS (
--Anker (also alle Sätze, die keine Kinder mehr haben):
Select Account, Accountid, cast(NULL AS Varchar(128)) ParentAccount, Parentid ParentAccountid, 0 Levl
,CONVERT( VARchar(MAX) , Account ) as SortAcc
from Account
where Parentid IS NULL
and Accountid=@ToplevelAccountid
union all
--Rekursion in CTE:
Select Account.Account Account, Account.Accountid Accountid, hAcc.Account ParentAccount, Account.Parentid ParentAccountid, Levl + 1
, CONVERT( VARchar(MAX) , ( SortAcc + Account.Account ) ) as SortAcc
from Account
inner join hAcc on hAcc.Accountid=Account.Parentid
where Parentid IS NOT NULL
)
Select
Account Account,
coalesce(replicate(char(124)+' ',Levl-1)+replicate(char(43)+' ',Levl-Levl+1) ,'') Leveling,
'' FirstChar,
Accountid, ParentAccount, ParentAccountid, Levl,
(Case when @Ankerid = Accountid Then 1 else 0 end) Markierung
,SortAcc
--,Row_Number() Over(order by SortAcc asc) RowNum
from hAcc
order by SortAcc
/*
--Beispiel:
Select parentid, Account, Accountid from Account where Accountid='A6UJ9A000F01'
Select parentid, Account, Accountid from Account where Accountid='A6UJ9A00050W'
*/
Select * from account
Select * from gc_account_ext
Select * from ACC_CORPORATE
Select a.ACCOUNT, a.ACCOUNTID, ac.ACCOUNTID, ac.ACC_PARENTID
from account a
inner join ACC_CORPORATE ac on a.ACCOUNTID=ac.ACCOUNTID
;WITH LoopIT( PARENT, CHILD )
AS (
--Anker (also alle Sätze, die keine Kinder mehr haben):
Select ac.ACC_PARENTID, ac.ACCOUNTID
From ACC_CORPORATE ac
where ac.ACC_PARENTID NOT IN (Select ac2.ACCOUNTID From ACC_CORPORATE ac2)
union all
--Rekursion in CTE:
Select r.ACC_PARENTID, r.ACCOUNTID
From LoopIT r
inner join ACC_CORPORATE acr on r.ACCOUNTID = acr.ACC_PARENTID
)
Select PARENT, CHILD FROM LoopIT
Hinweis: Standardmäßig sind jedoch nur 100 Rekursionen erlaubt und werden mit einem Fehler abgerochen. Lösung: Ganz an das Ende des T-SQL Code, also auch hinter „WHERE“-Bedingung/en und „Order BY“:
OPTION (MAXRECURSION 32767)
Sehr gute Zusammenfassung unter:
Installation guide to Kingswaysoft:
http://www.tpein.dk/?page_id=262
Software download list:
http://www.tpein.dk/?page_id=274
/**************************************************************
Wieviel Platz ist auf dem Filesystem des SQL Servers
und wie wird der RAM benutzt?
***************************************************************/
DECLARE @DB varchar(255)
SET @DB = --'FOD_Test_DB'
--Hier ist bewusst ein Fehler eingebaut, damit die DB korrekt festgelegt ist
/*
Physikalische Größe und freien Speicher in den Datenbanken
*/
exec ('use '+@DB+';
select
name
, filename
, convert(decimal(12,2),round(a.size/128.000,2)) as FileSizeMB
, convert(decimal(12,2),round(fileproperty(a.name,''SpaceUsed'')/128.000,2)) as SpaceUsedMB
, convert(decimal(12,2),round((a.size-fileproperty(a.name,''SpaceUsed''))/128.000,2)) as FreeSpaceMB
from dbo.sysfiles a;'+
'use '+'tempdb'+';
select
name
, filename
, convert(decimal(12,2),round(a.size/128.000,2)) as FileSizeMB
, convert(decimal(12,2),round(fileproperty(a.name,''SpaceUsed'')/128.000,2)) as SpaceUsedMB
, convert(decimal(12,2),round((a.size-fileproperty(a.name,''SpaceUsed''))/128.000,2)) as FreeSpaceMB
from dbo.sysfiles a')
/*
Freier Speicher im Filesystem des SQL-Servers:
*/
exec xp_fixeddrives
/*
Aufteilung des Arbeitspeicher des Systems
*/
/*
Select * from sys.dm_os_memory_pools
Select * from sys.dm_os_memory_allocations
Select * from sys.dm_os_memory_clerks
Select * from sys.dm_os_memory_objects
Select * from sys.dm_os_memory_nodes
--Ab SQL Server 2008 möglich:
Select cast(round(total_physical_memory_kb/1024.0/1024.0,2) as decimal(10,2)) PhysicalRAM_GB
,cast(round(available_physical_memory_kb/1024.0/1024.0,2) as decimal(10,2)) AvailableRAM_GB
,*
from sys.dm_os_sys_memory
*/
/*
Binary to Decimal
Zwei Teile:
1) Stored Function
2) CTE, ohne Function!
--MUSTER:
--select sysdba.fod_Int2Binary(5242881)
select sysdba.fod_Binary2Int('00000000010100000000000000000001', 17, 32)
*/
IF EXISTS (select name, * from sysobjects where name = 'fod_Binary2Int' and Type = 'FN') Drop function fod_Binary2Int
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:
-- Create date: <2008-01-22>
-- Description:
-- =============================================
CREATE FUNCTION fod_Binary2Int (@Folge as char(32), @Von as Int, @Bis as Int)
RETURNS Int
AS
BEGIN
-- Declare the return variable here
DECLARE @Result int
DECLARE @Zeichen char(1)
Declare @i int
SET @Result = 0
SET @i = @Von
WHILE (@i <= @Bis)
Begin
SET @Result = @Result * 2
SET @Zeichen = Substring(@Folge, @i, 1)
IF @Zeichen = '1'
SET @Result = @Result + 1
SET @i = @i + 1
End
-- Return the result of the function
RETURN @Result
END
GO
------------------------------------------------
/*
Binary to Decimal
Link: http://improve.dk/converting-between-base-2-10-and-16-in-t-sql/ ( robert matthew cook )
*/
;with table_64_bit as
(
select CONVERT(varchar(64), '0') as bit_check, CONVERT(bigint, 0) as decimal_value
union all
select CONVERT(varchar(64), '1%'), CONVERT(bigint, 1)
union all
select CONVERT(varchar(64), REPLACE(SPACE(LEN(bit_check)-1), ' ', '_') + '1%'), CONVERT(bigint, POWER(CONVERT(bigint, 2), LEN(bit_check)-1)) from table_64_bit where LEN(bit_check) between 2 and 63
)
select
from_binary, sum(decimal_value) as to_decimal
from table_64_bit
join (select '11101011001011010101111010000' as from_binary) as fake_table on reverse(from_binary) like bit_check
group by
from_binary;
/*
Integer => Binär
Zwei Varianten:
1) Stored Function
2) T-SQL, ohne Function
--MUSTER:
select sysdba.fod_Int2Binary(5242881)
--select sysdba.fod_Binary2Int('00000000010100000000000000000001', 17, 32)
*/
IF EXISTS (select name, * from sysobjects where name = 'fod_Int2Binary' and Type = 'FN') Drop function fod_Int2Binary
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:
-- Create date: <2008-01-22>
-- Description:
-- =============================================
CREATE FUNCTION fod_Int2Binary (@Zahl as Int)
--, @PreNulls as char(1))
RETURNS char(32)
AS
BEGIN
-- Declare the return variable here
DECLARE @Result char(32)
Declare @Rest int
Declare @Anteil int
SET @Result = ''
SET @Anteil = @Zahl
WHILE (@Anteil > 0)
Begin
Set @Rest = @Anteil % 2
SET @Anteil = @Anteil / 2
Set @Result = ltrim(str(@Rest)) + @Result
End
--If (@PreNulls = 'T')
Set @Result = right('0000000000' + '0000000000' + '0000000000' + '00' + rtrim(ltrim(@Result)) , 32)
-- Return the result of the function
RETURN @Result
END
GO
---------------------------------------------------------
Declare @i as bigint = 5242881 --> 00000000010100000000000000000001
Select
CASE WHEN CONVERT(VARCHAR(16), @i & Cast(2147483648 as bigint) ) > 0 THEN '1' ELSE '0' END +
CASE WHEN CONVERT(VARCHAR(16), @i & Cast(1073741824 as bigint) ) > 0 THEN '1' ELSE '0' END +
CASE WHEN CONVERT(VARCHAR(16), @i & 536870912 ) > 0 THEN '1' ELSE '0' END +
CASE WHEN CONVERT(VARCHAR(16), @i & 268435456 ) > 0 THEN '1' ELSE '0' END +
CASE WHEN CONVERT(VARCHAR(16), @i & 134217728 ) > 0 THEN '1' ELSE '0' END +
CASE WHEN CONVERT(VARCHAR(16), @i & 67108864 ) > 0 THEN '1' ELSE '0' END +
CASE WHEN CONVERT(VARCHAR(16), @i & 33554432 ) > 0 THEN '1' ELSE '0' END +
CASE WHEN CONVERT(VARCHAR(16), @i & 16777216 ) > 0 THEN '1' ELSE '0' END +
CASE WHEN CONVERT(VARCHAR(16), @i & 8388608 ) > 0 THEN '1' ELSE '0' END +
CASE WHEN CONVERT(VARCHAR(16), @i & 4194304 ) > 0 THEN '1' ELSE '0' END +
CASE WHEN CONVERT(VARCHAR(16), @i & 2097152 ) > 0 THEN '1' ELSE '0' END +
CASE WHEN CONVERT(VARCHAR(16), @i & 1048576 ) > 0 THEN '1' ELSE '0' END +
CASE WHEN CONVERT(VARCHAR(16), @i & 524288 ) > 0 THEN '1' ELSE '0' END +
CASE WHEN CONVERT(VARCHAR(16), @i & 262144 ) > 0 THEN '1' ELSE '0' END +
CASE WHEN CONVERT(VARCHAR(16), @i & 131072 ) > 0 THEN '1' ELSE '0' END +
CASE WHEN CONVERT(VARCHAR(16), @i & 65536 ) > 0 THEN '1' ELSE '0' END +
CASE WHEN CONVERT(VARCHAR(16), @i & 32768 ) > 0 THEN '1' ELSE '0' END +
CASE WHEN CONVERT(VARCHAR(16), @i & 16384 ) > 0 THEN '1' ELSE '0' END +
CASE WHEN CONVERT(VARCHAR(16), @i & 8192 ) > 0 THEN '1' ELSE '0' END +
CASE WHEN CONVERT(VARCHAR(16), @i & 4096 ) > 0 THEN '1' ELSE '0' END +
CASE WHEN CONVERT(VARCHAR(16), @i & 2048 ) > 0 THEN '1' ELSE '0' END +
CASE WHEN CONVERT(VARCHAR(16), @i & 1024 ) > 0 THEN '1' ELSE '0' END +
CASE WHEN CONVERT(VARCHAR(16), @i & 512 ) > 0 THEN '1' ELSE '0' END +
CASE WHEN CONVERT(VARCHAR(16), @i & 256 ) > 0 THEN '1' ELSE '0' END +
CASE WHEN CONVERT(VARCHAR(16), @i & 128 ) > 0 THEN '1' ELSE '0' END +
CASE WHEN CONVERT(VARCHAR(16), @i & 64 ) > 0 THEN '1' ELSE '0' END +
CASE WHEN CONVERT(VARCHAR(16), @i & 32 ) > 0 THEN '1' ELSE '0' END +
CASE WHEN CONVERT(VARCHAR(16), @i & 16 ) > 0 THEN '1' ELSE '0' END +
CASE WHEN CONVERT(VARCHAR(16), @i & 8 ) > 0 THEN '1' ELSE '0' END +
CASE WHEN CONVERT(VARCHAR(16), @i & 4 ) > 0 THEN '1' ELSE '0' END +
CASE WHEN CONVERT(VARCHAR(16), @i & 2 ) > 0 THEN '1' ELSE '0' END +
CASE WHEN CONVERT(VARCHAR(16), @i & 1 ) > 0 THEN '1' ELSE '0' END
GILT NIE ALS AUGETESTET!!!!
Eine SalesLogix Tabelle umbennen openCRMS schrieb am 4. Oktober 2009 15:45 Es passiert jedem Entwickler einmal, dass er einen Tipp- oder Schreibfehler beim Erstellen einer SalesLogix-Tabelle im Architect macht. Da der Architect jedoch leider keine Option bietet eine Tabelle umzubenennen, muss man den Umweg über SQL gehen. Dies wiederum führt dazu, dass eine auf SQL Basis umbenannten Tabelle in SalesLogix nicht mehr korrekt nutzbar ist. Der Grund hierfür ist die Tatsache, dass beim Anlegen einer SalesLogix Tabelle im Databasemanager des Architects, der Tabellenname zugleich in mehreren Tabellen als Systeminformation gespeichert wird: in der Tabelle SECTABLEDEFS, RESYNCTABLEDEFS und JOINDATA.
Um diese Probleme zu umschiffen, habe ich den folgenden SQL-Script geschrieben, dass es mir erlaubt, eine SalesLogix-Tabelle, die ich im SQL Server Management Studio umbenannt habe, in SalesLogix wieder benutzen kann.
ACHTUNG: Machen Sie das nicht so einfach, falls auch Remote-Clients in dem System vorkommen könnten!
/*
GILT NIE ALS AUGETESTET!!!!
Eine SalesLogix Tabelle umbennen
openCRMS schrieb am 4. Oktober 2009 15:45
Es passiert jedem Entwickler einmal, dass er einen Tipp- oder Schreibfehler
beim Erstellen einer SalesLogix-Tabelle im Architect macht. Da der Architect
jedoch leider keine Option bietet eine Tabelle umzubenennen, muss man hierfÜr
den Umweg über SQL gehen. Dies wiederum fÜhrt dazu, dass eine auf SQL Basis
umbenannten Tabelle in SalesLogix nicht mehr korrekt nutzbar ist. Der Grund
hierf¸r ist die Tatsache, dass beim Anlegen einer SalesLogix Tabelle im
Databasemanager des Architects, der Tabellenname zugleich in mehreren Tabellen
als Systeminformation gespeichert wird:
in der Tabelle SECTABLEDEFS, RESYNCTABLEDEFS und JOINDATA.
Um diese Probleme zu umschiffen, habe ich den folgenden SQL-Script geschrieben, dass es mir erlaubt, eine SalesLogix-Tabelle, die ich im SQL Server Management Studio umbenannt habe, in SalesLogix wieder benutzen kann:
*/
Declare @myTable varchar(128)
Declare @newTableName varchar(128)
set @myTable = 'ALTER_NAME'
set @newTableName = 'NEUER_NAME'
select * from sysdba.SECTABLEDEFS where TABLENAME = @myTable
update sysdba.SECTABLEDEFS
set TABLENAME = @newTableName
where TABLENAME = @myTable
update sysdba.RESYNCTABLEDEFS
set TABLENAME = @newTableName
where TABLENAME = @myTable
update sysdba.JOINDATA
set FROMTABLE = @newTableName
where FROMTABLE = @myTable
update sysdba.JOINDATA
set TOTABLE = @newTableName
where TOTABLE = @myTable
/*
Jede strukturelle Aenderungen an einer oder allen Datenbanken soll (voellig automatisch) nachvollziehbar sein.
Trigger on a CREATE, ALTER, DROP, GRANT, DENY, REVOKE, or UPDATE STATISTICS statement (DDL Trigger)
Um alle technische Aenderungen nachvollziehen zu koennen.
Also werden alle Veraenderungen an Struktruen von allen Datenbanken dokumentiert.
Dies gilt auch fuer Trigger und neue Datenbanken u.ae.
Es werden somit saemtliche "DDL" Befehle dokumentiert, aber keine DML Befehle und deren Wirkungen!
DDL = Data Definition Language
DML = Data Manipulation Language
Moeglicherweise sollte die getriggerte ƒnderungen
an die Ziel-Datenbank (bsp. master.dbo.FOD_ddl_log)
eines zweitem SQL-Server gesendet werden.
Hinweis: sa-Rechte (oder vergleichbare) sind notwendig.
Hinweis: Dieser Trigger ist auf dem SQL-Server unter ServerObjects/Triggers zu finden
Change:
20160126 FOD: Diese Beschreibung und JEGLICHER "DELETE-Code" wurde auskommentiert + with execute as 'sa'
*/
USE master;
GO
--Logging-Tabelle anlegen:
CREATE TABLE FOD_ddl_log (
id bigint identity(1,1),
xmlEVENTDATA xml
);
GO
--Logging-View anlegen (nur wg. dem Auslesen von XML):
Create View dbo.vFOD_ddl_log
as
Select id,
xmlEVENTDATA.value('(/EVENT_INSTANCE/PostTime)[1]', 'datetime') as PostTime,
xmlEVENTDATA.value('(/EVENT_INSTANCE/ServerName)[1]', 'varchar(256)') as ServerName,
xmlEVENTDATA.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'varchar(256)') as DatabaseName,
xmlEVENTDATA.value('(/EVENT_INSTANCE/UserName)[1]', 'varchar(256)') as UserName,
xmlEVENTDATA.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)') as EventType,
xmlEVENTDATA.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(2000)') as TSQLCommand,
xmlEVENTDATA
from FOD_ddl_log
--TRIGGER anlegen:
CREATE TRIGGER FOD_ddl_log
/*ON DATABASE*/
ON ALL SERVER
WITH EXECUTE AS 'sa'
FOR DDL_DATABASE_LEVEL_EVENTS
AS
DECLARE @data XML;
SET @data = EVENTDATA();
INSERT FOD_ddl_log ( [xmlEVENTDATA] )
VALUES ( @data ) ;
GO
--- DER CODE SOLL ERST NUR BIS HIER GESTARTET WERDEN!!!
/*
--Test the trigger:
--------------------
Begin Transaction
CREATE TABLE TestTable (a int)
Rollback
CREATE TABLE TestTable (a int)
GO
DROP TABLE TestTable ;
GO
SELECT * FROM master.dbo.vFOD_ddl_log order by PostTime desc;
GO
*/
/*
--ALLES WIEDER ENTFERNEN, WENN ES NICHTGEWUENSCHT IST:
-----------------------------------------------------
--Drop the trigger.
DROP TRIGGER master.dbo.FOD_ddl_log
--ON DATABASE
ON ALL SERVER
GO
--Drop View vFOD_ddl_log:
use master
go
DROP VIEW dbo.vFOD_ddl_log;
GO
--Drop table FOD_ddl_log:
use master
go
DROP TABLE dbo.FOD_ddl_log;
GO
*/
/*
--Aufwaendiges Logging:
http://www.mssqltips.com/sqlservertip/2085/sql-server-ddl-triggers-to-track-all-database-changes/
--Einfaches Logging:
http://msdn.microsoft.com/en-us/library/ms173781.aspx
--Bedeutung von ÑEvent_Instanceì
http://www.sqlteam.com/article/using-ddl-triggers-in-sql-server-2005-to-capture-schema-changes
--Die Beschreibung der Events nach Microsoft Technet:
http://technet.microsoft.com/en-us/library/ms186438(SQL.90).aspx
*/
Aufwändiges Logging:
http://www.mssqltips.com/sqlservertip/2085/sql-server-ddl-triggers-to-track-all-database-changes/
Einfaches Logging:
http://msdn.microsoft.com/en-us/library/ms173781.aspx
Bedeutung von „Event_Instance“
http://www.sqlteam.com/article/using-ddl-triggers-in-sql-server-2005-to-capture-schema-changes
Die Beschreibung der Events nach Microsoft Technet:
http://technet.microsoft.com/en-us/library/ms186438(SQL.90).aspx
/* Darstellen von SQL Code von Views. Trigger, Prozeduren und Funktionen */ Select s.name, m.definition from sys.sql_modules m inner join sys.views s ON s.object_id = m.object_id where 1=1 Select s.name, m.definition from sys.sql_modules m inner join sys.triggers s ON s.object_id = m.object_id where 1=1 Select s.name, m.definition from sys.sql_modules m inner join sys.procedures s ON s.object_id = m.object_id where 1=1 order by 1 Select s.name, m.definition from sys.sql_modules m inner join sys.objects s on s.object_id = m.object_id and s.Type='FN' where 1=1 order by 1
Problem 1: Nicht immer sind die CRLF verblieben
Problem 2: Code ist möglw. länger als 4000/8000 Zeichen
Problem 3: Code ist möglw. verschlüsselt
Falls ein Backup sehr viel Zeit braucht, wäre es schön, wenn man einmal feststellen kann, wie weit das Backup ist:
/*
Wenn ein Backup länger lauft, kann man feststellen wie weit das Backup ist.
Ggf. hilft diese Recht zum Lesen dieses Code auch anderen geben:
GRANT VIEW SERVER STATE TO [sqluser_loginname]
*/
Select Session_id asSPID, r.database_id, r.command, a.text as Query, start_time, percent_complete,
DATEADD(Second, estimated_completion_time/1000, GetDate()) as estimated_completion_time
From sys.dm_exec_requests r cross Apply sys.dm_exec_sql_text(r.sql_handle) a
Where r.command in ('Backup Database', 'Restore Database', 'Backup Log')
Übrigens: Ich habe die Dauer von Backups gebraucht, als Probleme beim Zugriff auf ein RAID System einmal bestand…
Falls die Wiederherstellung einer Datenbank immer noch nicht fertig ist, wäre es schön, wenn man einmal feststellen kann, wie weit die Wiederherstellung zumindest ist…
/*
http://timlaqua.com/2009/09/determining-how-long-a-database-will-be-in-recovery-sql-server-2008/
ACHTUNG: CODE UNTERSCHIEDLICH VOR SQL SERVER 2012:
sys.xp_readerrorlog
sys.sp_readerrorlog
*/
DECLARE @Serverversion integer = 2012 --BITTE CODE VORHER LESEN!!!!
DECLARE @DBName VARCHAR(64) = 'databasename'
If @Serverversion > 2012
BEGIN
/*
----------------------------
SQL Server 2008
----------------------------
So, your MSSQL service crashed in the middle of a big transaction? Or you bumped the service while it was rolling back some gigantic schema change (like say a column add on a 800 million row table)? Well, as you prepare your resume in preparation for the fallout from this debockle, you can use the following query to see how much time you have left. Or, I should say, how much time it thinks you have left... which seems to swing wildly up and down... microsoft math ftw.
*/
DECLARE @ErrorLog AS TABLE([LogDate] CHAR(24), [ProcessInfo] VARCHAR(64), [TEXT] VARCHAR(MAX))
INSERT INTO @ErrorLog
EXEC sys.xp_readerrorlog 0, 1, 'Recovery of database', @DBName
SELECT TOP 5
[LogDate]
,SUBSTRING([TEXT], CHARINDEX(') is ', [TEXT]) + 4,CHARINDEX(' complete (', [TEXT]) - CHARINDEX(') is ', [TEXT]) - 4) AS PercentComplete
,CAST(SUBSTRING([TEXT], CHARINDEX('approximately', [TEXT]) + 13,CHARINDEX(' seconds remain', [TEXT]) - CHARINDEX('approximately', [TEXT]) - 13) AS FLOAT)/60.0 AS MinutesRemaining
,CAST(SUBSTRING([TEXT], CHARINDEX('approximately', [TEXT]) + 13,CHARINDEX(' seconds remain', [TEXT]) - CHARINDEX('approximately', [TEXT]) - 13) AS FLOAT)/60.0/60.0 AS HoursRemaining
,[TEXT]
FROM @ErrorLog
ORDER BY 1 DESC
END
Else
BEGIN
/*
----------------------------
Sql Server 2012
----------------------------
uses a different stored procedure to read the error log:
*/
DECLARE @ErrorLog AS TABLE([LogDate] CHAR(24), [ProcessInfo] VARCHAR(64), [TEXT] VARCHAR(MAX))
INSERT INTO @ErrorLog
EXEC master..sp_readerrorlog 0, 1, 'Recovery of database', @DBName
SELECT TOP 5
[LogDate]
,SUBSTRING([TEXT], CHARINDEX(') is ', [TEXT]) + 4,CHARINDEX(' complete (', [TEXT]) - CHARINDEX(') is ', [TEXT]) - 4) AS PercentComplete
,CAST(SUBSTRING([TEXT], CHARINDEX('approximately', [TEXT]) + 13,CHARINDEX(' seconds remain', [TEXT]) - CHARINDEX('approximately', [TEXT]) - 13) AS FLOAT)/60.0 AS MinutesRemaining
,CAST(SUBSTRING([TEXT], CHARINDEX('approximately', [TEXT]) + 13,CHARINDEX(' seconds remain', [TEXT]) - CHARINDEX('approximately', [TEXT]) - 13) AS FLOAT)/60.0/60.0 AS HoursRemaining
,[TEXT]
FROM @ErrorLog
ORDER BY 1 DESC, 2 DESC, 3 DESC
END
Quelle: http://timlaqua.com/2009/09/determining-how-long-a-database-will-be-in-recovery-sql-server-2008/
Eine Windows-Maschine die man nicht über die grafische Oberfläche bedienen kann kann in einer CMD-Shell herunter gefahren werden, oder mit dem Parameter „/R“ neustarten:
shutdown /R oder shutdown /R /T0
Das kann per SQL festgestellt werden:
/*per SQL die "laufende SSIS package executions"
bestimmen:*/
/*Select status, start_time as start_time2,
folder_name, project_name ,*
from SSISDB.catalog.executions
where end_time is null
order by start_time desc
*/
Select top 5 status, start_time as start_time2,
folder_name, project_name ,*
from SSISDB.catalog.executions
order by start_time desc
/*
Die richtige execution_id auswählen und als
@operation_id eintragen
(typisch MS - muss ja auch keiner Logik folgen):
Exec catalog.stop_operation @operation_id = 12210
Exec catalog.stop_operation @operation_id = 12211
*/
…oder im SSMS (Management Studio) herausfinden:
per SSMS-Dialog:
Rechte Maus auf „Integration Services Catalogs \ SSISDB“ und „Active Operations“ auswählen…Dort die gewünschte Package auswählen und auf „STOP“ klicken.
Links:
https://sabigdata.wordpress.com/2015/02/12/stop-ssis-package-execution-in-ssis-catalog/
Gelegentlich kommt es vor, dass das MSCRM hängt und die Anwender auch nicht benutzten können und asynchrone Jobs sich selber sperren.
Es hilft meist, den „Async Job“ einfach Neustarten…
/*Anzahl der Sätze Total*/
Select Count(1) from [dbo].[AsyncOperationBase] with (nolock)
/*Benutzung*/
Select Sum(Coalesce(Len([Data]),0)) as [Data]
,Sum(Coalesce(Len([WorkflowState]),0)) as [WorkflowState]
,Sum(Coalesce(Len([Message]),0)) as [Message]
,Sum(Coalesce(Len([RegardingObjectIdName]),0)) as [RegardingObjectIdName]
,Sum(Coalesce(Len([RegardingObjectIdYomiName]),0)) as [RegardingObjectIdName]
,Sum(Coalesce(Len([FriendlyMessage]),0)) as [FriendlyMessage]
,Sum(Coalesce(Len([OwningExtensionIdName]),0)) as [OwningExtensionIdName]
from [dbo].[AsyncOperationBase] with (nolock)
/*Interner Zustand der Tabelle... z.B. falls diese riesig ist*/
SELECT 'AsyncOperationBase' Tablename, type_desc, total_pages * 8 as total_pages_in_KB, used_pages * 8 as used_pages_in_KB, total_pages, used_pages, data_pages,container_id, allocation_unit_id
FROM sys.allocation_units
WHERE container_id in (SELECT partition_id FROM sys.partitions WHERE OBJECT_ID = OBJECT_ID('AsyncOperationBase') )
/*Verteilung bezüglich des StatusCode*/
Select StatusCode, COUNT(1) Anzahl, Case when StatusCode=0 Then 'Ready/WaitingForResources'
when StatusCode=10 Then 'Suspended/Waiting'
when StatusCode=20 Then 'Locked/InProgress'
when StatusCode=21 Then 'Locked/Pausing'
when StatusCode=22 Then 'Locked/Canceling'
when StatusCode=30 Then 'Completed/Succeeded'
when StatusCode=31 Then 'Completed/Failed'
when StatusCode=32 Then 'Completed/Canceled'
Else 'unbekannter Status'
END StatusCode_Readable
FROM dbo.[AsyncOperationBase] with (nolock)
Group by StatusCode
Wenn es mehrere Millionen Sätze gibt, dann lohnt sich die Missing Indizees einmal anzuschauen…
Es gibt normalerweise eine regelmäßigen Bereinigungs-Job, der alte Sätze löscht.
Sprachnachrichten des Anrufbeantworter der Fritz!Box kann man sich per E-Mail senden lassen.
In dem Fritz!Box Menü über Telefonie / Anrufbeantworter diesen aktivieren und dann passend Einstellen:
Ganz unten im Dialog in den „Weitere Einstellungen“ kann die E-Mail Adresse eintragen werden, an die Nachricht gesendet werden soll.
Wichtig ist, dann noch im Bereich „Push Service“ den Dienst aktivieren für „Anrufbeantworter“.
Gefunden habe ich den Tipp unter:
https://www.youtube.com/watch?v=Vg-4sRVIeqs
Wenn erst später ein neuer SQL Server installiert wird, und dann z.B. als Ziel des Visual Studio Projektes jetzt auf „SQL Server 2016“ wechselt wird, dann sind die Kingswaysoft Controls nicht mehr vorhanden!
Versuche:
Repair oder Change des „SSIS Integration Toolkit for Microsoft Dynamics 365“ scheidet wenn das Toolkit weiterhin im Visual Studio sichtbar ist.
Lösung:
Deinstallieren des „SSIS Integration Toolkit for Microsoft Dynamics 365“ und einfach dann wieder installieren. Eine ggf. installierte Lizenz ist dann weiterhin gültig (soweit diese vorher auch gültig war;-)
Während einer Installation eines SQL Servers 2016 und/oder SQL Server 2017:
Erste Hinweise: https://blog.sqlauthority.com/2017/12/21/sql-server-fix-error-vs-shell-installation-failed-exit-code-1638/
Die notwendige „Redistributable Version“ installieren. Die gesuchte Version sollte im Installations Log zu finden sein!
Sollte sich diese „Redistributable Version“ jedoch nicht installieren lässt…
…müssen aktuellere Versionen deinstalliert werden, bis dann die verlangte Version installiert werden kann:
Dann die gewünschte Software installieren – in diesem Fall SQL Server 2016 und/oder SQL Server 2017.
Und dann müssen auch alle gerade deinstallierte Versionen wieder installiert werden. Wobei mit den älteren Versionen anfangen!
https://support.microsoft.com/de-de/help/2977003/the-latest-supported-visual-c-downloads
Voraussetzungen für Importe per SSIS: 1) DataTools installieren oder VisualStudio + DataTools for VS 2) Kingswaysoft installieren: „SSIS Integration Toolkit for Microsoft Dynamics 365“ Automatisierung: Nur wenn Lizenz installiert ist 3) Zugriff auf den „Quell-SQL Server“ und/oder Sonstige Quellen 4) Zugriff auf das Ziel „MS Dynamics 365“ bzw. MSCRM 5) Zugriff auf einen „Import-SQL Server“ Versionen? Express: Automatisierung als BATCH Sonst (Standard, Enterprise, ..): Automatisierung per Agent Jobs und/oder SSIS Integration Service und/oder BATCH 6) Datenbank „FOD_StagingDB“ (Angelegt von Importen für Logging, Crossreference, Errorhandling, …) (Benutzt bei Importen per SSIS/Kingswaysoft) Hinweise: • Abhängigkeiten bei Zugriffen von „SELECT – Zugriffe mit Daten mit zwei oder mehreren unterschiedlichen Server kann bestehen z.B. Unterschiede von Daten vom Quell- und Ziel-Server zu bestimmen… • Wie sollen bei Fehlern reagiert werden? Gar kein Errorhandling oder sollen alle Error „wieder vorgelegt werden“ bis Fehler gelöst ist? • Migration oder Integration? ODFR hat bisher keine Migration erlebt! • Unterschied zwischen Full-Import und Delta-Import? Delta-Importe können so geschrieben werden, dass z.B. am jedem 1. Samstag auch Full-Importe erlauben! • Können wir E-Mails an Kd. und uns senden bezüglich bestimmter Fehler/Abbrüche? ODFR empfiehlt dies einzurichten
Bei „Product“, „Quote“ und „Opportunity“ (und weiteren Entitäten) kommen Pflicht-Felder vor. Z.B. sind „Einheiten“ verlangt. Bei einem Import stößt man auf dieses ER-Model:
Beispiel: UOMGroup: „Weight“
UOM: „Gramm“, „Kilogramm“, „Tonne“
Prinzipiell werden die Daten im MSCRM in den folgenden Tabellen
Es gibt neben der Grafik weitere Activities, die das MSCRM unterstützt.
Um einen Zugriff auf das NAV zu erlangen muss Information vom NAV geliefert werden.
Es ist die korrekte Schreibweise und daher mglw. muss mit einem NAV-Spezialist gesprochen werden und dem die richtige „Available Companies“ zu bekommen:
DECLARE @Table Table (Id int Not NULL identity(1,1), Wert nvarchar(10))
Insert into @Table Values('A'),('B'),('C')
Select * from @Table
Select STRING_AGG(Wert,', ') WITHIN GROUP ( ORDER BY Wert ASC) as Liste from @Table
Seit SQL Server 2016 gibt es einige neue Funktionen. Ein Beispiel mit dem Code wesentlich kürzer ist die table based function „string_split()“.
Diese Funktion wird als Tabelle aufgerufen und hat nur die Spalte „value“:
/*Test Tabelle und Daten bereitstellen:*/
DECLARE @Test TABLE (id int identity(1,1) NOT NULL, wert nvarchar(MAX) NULL)
INSERT INTO @test VALUES ('a,b,c,d,e,f'), ('1,2,3,4,5,6,7,8,9'), ('aa,bb,cc,dd,ee,ff,gg')
--Select * from @Test
/*Daten aus einem Feld als Tabelle bereitstellen:*/
Select value
from @Test
outer apply string_split(wert,',')
where id = 3
SELECT
a.AccountID
,STUFF(
(SELECT '; ' + Coalesce(Lastname,'')+Coalesce(', '+Firstname,'')
FROM Contact c
Where c.AccountID = a.AccountID
FOR XML PATH('')
)
, 1, 2, '')
As concatenated_string
FROM Account a
order by a.accountid
Hinweis: Falls der Ergebnis-String nicht größer wird als 4000 bzw. 8000 Zeichen, dann könnte auch „STRING_AGG“ benutzt werden!
Beispiel Code um aus mehreren Sätze eine „Kommaseparierte Liste“ zu haben:
IF EXISTS (select name from sysobjects where name = 'fod_implode' and Type = 'FN') Drop function fod_implode
CREATE FUNCTION sysdba.fod_implode ( @id AS char(12))
RETURNS varchar(4000)
BEGIN
DECLARE @implode varchar(3000)
Set @implode = ''
DECLARE Name_Cursor CURSOR FOR
SELECT lastname FROM sysdba.Contact WHERE accountid = @id
DECLARE @Value varchar(255)
OPEN Name_Cursor
FETCH NEXT FROM Name_Cursor into @Value
WHILE (@@FETCH_STATUS = 0)
BEGIN
if @implode = ''
Set @implode = @Value
else
Set @implode = @implode + ', ' + @Value
FETCH NEXT FROM Name_Cursor into @Value
END
CLOSE Name_Cursor
DEALLOCATE Name_Cursor
--Rueckgabewert:
RETURN @implode
END
Beispiel Code um die Werte aus einem einzelnem Feld als Tabelle bereitzustellen:
/*
Felder die Semikolon separierte Felder enthalten, sollen diese in Tabelle mit entsprechend vielen Zeilen sein
*/
Create Table #t (Id int unique, value varchar(254))
Insert into #t Values(1, 'a;bb;ccc;')
Insert into #t Values(2, 'x;yy')
Insert into #t Values(3, NULL)
/*Erst an das Ende Semikolons anhängen*/
Update #t set value=rtrim(value)+';' Where right(rtrim(value),1)<>';' and (value<>'')
--Select * from #t
;With ht(Id, v, Pos)
As (
/*Anker*/
Select id, Substring(value,1,CHARINDEX(';',value,1)-1), CHARINDEX(';',value,1)-1 as Pos
From #t
Where CHARINDEX(';',value,3)>0
union all
/*Recursion*/
Select #t.Id, Substring(#t.value,ht.Pos+2,CHARINDEX(';',value,ht.Pos+2)-1 - ht.pos - 1), CHARINDEX(';',value,ht.Pos+2)-1 as Pos
From #t
inner join ht on #t.Id = ht.Id
Where CHARINDEX(';',#t.value, (ht.Pos+2)) > 0
)
Select *
from ht
Drop Table #t
Select y.y Monat , Ceiling(y.y/3.0) Quartal , y.y - 3*(Ceiling(y.y/3.0) - 1) PositionImQuartal from ( SELECT number FROM master.dbo.spt_values WHERE type='P' AND number>=1 AND number <=12 ) y(y)
/*
NOCH *NICHT* AUGETESTET!!!!
Eine SalesLogix Tabelle umbennen
openCRMS schrieb am 4. Oktober 2009 15:45
Es passiert jedem Entwickler einmal, dass er einen Tipp- oder Schreibfehler
beim Erstellen einer SalesLogix-Tabelle im Architect macht. Da der Architect
jedoch leider keine Option bietet eine Tabelle umzubenennen, muss man hierfür
den Umweg über SQL gehen. Dies wiederum führt dazu, dass eine auf SQL Basis
umbenannten Tabelle in SalesLogix nicht mehr korrekt nutzbar ist. Der Grund
hierfür ist die Tatsache, dass beim Anlegen einer SalesLogix Tabelle im
Databasemanager des Architects, der Tabellenname zugleich in mehreren Tabellen
als Systeminformation gespeichert wird:
in der Tabelle SECTABLEDEFS, RESYNCTABLEDEFS und JOINDATA.
Um diese Probleme zu umschiffen, habe ich das folgenden SQL-Script geschrieben, dass es mir erlaubt, eine SalesLogix-Tabelle, die ich im SQL Server Management Studio umbenannt habe, in SalesLogix wieder benutzen kann:
*/
Declare @myTable varchar(128)
Declare @newTableName varchar(128)
set @myTable = 'ALTER_NAME'
set @newTableName = 'NEUER_NAME'
select * from sysdba.SECTABLEDEFS where TABLENAME = @myTable
update sysdba.SECTABLEDEFS
set TABLENAME = @newTableName
where TABLENAME = @myTable
update sysdba.RESYNCTABLEDEFS
set TABLENAME = @newTableName
where TABLENAME = @myTable
update sysdba.JOINDATA
set FROMTABLE = @newTableName
where FROMTABLE = @myTable
update sysdba.JOINDATA
set TOTABLE = @newTableName
where TOTABLE = @myTable
Quelle:
https://github.com/evilbaschdi/PowerShellScripts/blob/master/PowerShellProject/GetDotNetVersions.ps1
Write-Host (Get-ChildItem 'HKLM:\SOFTWARE\Microsoft\NET Framework Setup\NDP' -recurse |
Get-ItemProperty -name Version,Release -EA 0 |
Where-Object { $_.PSChildName -match '^(?!S)\p{L}'} |
Select-Object PSChildName, Version, Release, @{
name="Product"
expression={
switch($_.Release) {
378389 { [Version]"4.5" }
378675 { [Version]"4.5.1" }
378758 { [Version]"4.5.1" }
379893 { [Version]"4.5.2" }
393295 { [Version]"4.6" }
393297 { [Version]"4.6" }
394254 { [Version]"4.6.1" }
394271 { [Version]"4.6.1" }
394802 { [Version]"4.6.2" }
394806 { [Version]"4.6.2" }
460798 { [Version]"4.7" }
460805 { [Version]"4.7" }
461308 { [Version]"4.7.1" }
461310 { [Version]"4.7.1" }
}
}
} | Format-Table | Out-String)
Write-Host "Press any key to continue..."
#$Host.UI.RawUI.ReadKey("NoEcho,IncludeKeyUp") > $null
Aufwaendiges Logging:
http://www.mssqltips.com/sqlservertip/2085/sql-server-ddl-triggers-to-track-all-database-changes/
–Einfaches Logging:
http://msdn.microsoft.com/en-us/library/ms173781.aspx
–Bedeutung von „Event_Instance“
http://www.sqlteam.com/article/using-ddl-triggers-in-sql-server-2005-to-capture-schema-changes
Es muss eine Tabelle geben, in der die Änderungen gesammelt werden. Diese Tabelle wurde im u. g. Code in der Datenbank „master“ abgelegt.
/*
Jede strukturelle Änderungen an einer oder allen Datenbanken soll (völlig automatisch) nachvollziehbar sein.
Trigger on a CREATE, ALTER, DROP, GRANT, DENY, REVOKE, or UPDATE STATISTICS statement (DDL Trigger)
Um alle technische Änderungen nachvollziehen zu können.
Also werden alle Veränderungen an Struktruen von allen Datenbanken dokumentiert.
Dies gilt auch für Trigger und neue Datenbanken u.ä.
Es werden somit sämtliche "DDL" Befehle dokumentiert, aber keine DML Befehle und deren Wirkungen!
DDL = Data Definition Language
DML = Data Manipulation Language
Möglicherweise sollte die getriggerte Änderungen
an die Ziel-Datenbank (bsp. master.dbo.fod_ddl_log)
eines zweitem SQL-Server gesendet werden.
Hinweis: sa-Rechte (oder vergleichbare) sind notwendig.
Hinweis: Dieser Trigger ist auf dem SQL-Server unter ServerObjects/Triggers zu finden
Change:
20160126 ODFR: Dieser Kopf und JEGLICHER "DELETE-Code" wurde auskommentiert + with execute as 'sa'
*/
USE master;
GO
--Logging-Tabelle anlegen:
CREATE TABLE fod_ddl_log (
id bigint identity(1,1),
xmlEVENTDATA xml
);
GO
--Logging-View anlegen (nur wg. dem Auslesen von XML):
Create View dbo.vfod_ddl_log
as
Select id,
xmlEVENTDATA.value('(/EVENT_INSTANCE/PostTime)[1]', 'datetime') as PostTime,
xmlEVENTDATA.value('(/EVENT_INSTANCE/ServerName)[1]', 'varchar(256)') as ServerName,
xmlEVENTDATA.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'varchar(256)') as DatabaseName,
xmlEVENTDATA.value('(/EVENT_INSTANCE/UserName)[1]', 'varchar(256)') as UserName,
xmlEVENTDATA.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)') as EventType,
xmlEVENTDATA.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(2000)') as TSQLCommand,
xmlEVENTDATA
from GC_ddl_log
--TRIGGER anlegen:
CREATE TRIGGER GC_ddl_log
/*ON DATABASE*/
ON ALL SERVER
WITH EXECUTE AS 'sa'
FOR DDL_DATABASE_LEVEL_EVENTS
AS
DECLARE @data XML;
SET @data = EVENTDATA();
INSERT GC_ddl_log ( [xmlEVENTDATA] )
VALUES ( @data ) ;
GO
--- DER CODE SOLL ERST NUR BIS HIER GESTARTET WERDEN!!!
/*
--Test the trigger:
--------------------
Begin Transaction
CREATE TABLE TestTable (a int)
Rollback
CREATE TABLE TestTable (a int)
GO
DROP TABLE TestTable ;
GO
SELECT * FROM master.dbo.vGC_ddl_log order by PostTime desc;
GO
*/
/*
--ALLES WIEDER ENTFERNEN, WENN ES NICHTGEWÜNSCHT IST:
-----------------------------------------------------
--Drop the trigger.
DROP TRIGGER master.dbo.fod_ddl_log
--ON DATABASE
ON ALL SERVER
GO
--Drop View vfod_ddl_log:
use master
go
DROP VIEW dbo.vfod_ddl_log;
GO
--Drop table fod_ddl_log:
use master
go
DROP TABLE dbo.fod_ddl_log;
GO
*/
/*
--Aufwaendiges Logging:
http://www.mssqltips.com/sqlservertip/2085/sql-server-ddl-triggers-to-track-all-database-changes/
--Einfaches Logging:
http://msdn.microsoft.com/en-us/library/ms173781.aspx
--Bedeutung von „Event_Instance“
http://www.sqlteam.com/article/using-ddl-triggers-in-sql-server-2005-to-capture-schema-changes
*/
IF EXISTS (select name, * from sysobjects where name = 'fod_YMD2Datetime' and Type = 'FN') Drop function fod_YMD2Datetime GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE FUNCTION fod_YMD2Datetime(@Year int, @Month int, @Day int) RETURNS datetime AS -- ============================================= -- Author: FOd -- Create date: <2009-01-30> -- Description: Aus Jahr, Monat und Tag ein Datetime liefern -- ============================================= BEGIN DECLARE @d datetime; SET @d = dateadd(year,(@Year - 1753),'1/1/1753'); SET @d = dateadd(month,@Month - 1,@d); SET @d = dateadd(day, @Day - 1, @d); RETURN @d END go --Select dbo.gc_YMD2Datetime(2009, 1, 30)
Quelle stammt IMHO von NovickSoftware.com
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF EXISTS (select name, * from sysobjects where name = 'fod_IsEmailValid' and Type = 'FN') Drop function fod_IsEmailValid
GO
CREATE FUNCTION [dbo].[fod_IsEmailValid] ( @EmailAddr varchar(255))
RETURNS BIT
/*
* Check a Emailaddress
* Checks the text string to be sure it's a valid e-mail address.
* Returns 1 when it is, otherwise 0.
* 1 if @EmailAddr is a valid email address
* Example:
SELECT CASE WHEN 1=dbo.gc_IsEmailValid('anovick@NovickSoftware.com')
THEN 'Is an e-mail address' ELSE 'Not an e-mail address' END
*
* Test:
print case when 1=dbo.udf_txt_isEmail('anovick@novicksoftware.com')
then 'Passes' else 'Fails' end + ' test for good addr'
print case when 0=dbo.udf_txt_isEmail('@novicksoftware.com')
then 'Passes' else 'Fails' end + ' test for no user'
print case when 0=dbo.udf_txt_isEmail('anovick@n.com')
then 'Passes' else 'Fails' end + ' test for 1 char domain'
print case when 1=dbo.udf_txt_isEmail('anovick@no.com')
then 'Passes' else 'Fails' end + ' test for 2 char domain'
print case when 0=dbo.udf_txt_isEmail('anovick@.com')
then 'Passes' else 'Fails' end + ' test for no domain'
print case when 0=dbo.udf_txt_isEmail('anov ick@novicksoftware.com')
then 'Passes' else 'Fails' end + ' test for space in name'
print case when 0=dbo.udf_txt_isEmail('ano#vick@novicksoftware.com')
then 'Passes' else 'Fails' end + ' test for # in user'
print case when 0=dbo.udf_txt_isEmail('anovick@novick*software.com')
then 'Passes' else 'Fails' end + ' test for * asterisk in domain'
****************************************************************/
AS BEGIN
DECLARE @AlphabetPlus VARCHAR(255)
, @Max INT -- Length of the address
, @Pos INT -- Position in @EmailAddr
, @OK BIT -- Is @EmailAddr OK
-- Check basic conditions
IF @EmailAddr IS NULL
OR NOT @EmailAddr LIKE '_%@__%.__%'
OR CHARINDEX(' ',LTRIM(RTRIM(@EmailAddr))) > 0
RETURN(0)
SELECT @AlphabetPlus = 'abcdefghijklmnopqrstuvwxyz01234567890_-.@'
, @Max = LEN(@EmailAddr)
, @Pos = 0
, @OK = 1
WHILE @Pos < @Max AND @OK = 1 BEGIN
SET @Pos = @Pos + 1
IF NOT @AlphabetPlus LIKE '%'
+ SUBSTRING(@EmailAddr, @Pos, 1)
+ '%'
SET @OK = 0
END -- WHILE
RETURN @OK
END
go
--?????
--GRANT EXEC on dbo.fod_IsEmailValid to PUBLIC
go
--Collation der DB/DBs feststellen: Select name, compatibility_level, collation_name from sys.databases db --where db.database_id = DB_ID() --Collation aller Spalten feststellen (Nur für: char, varchar, ... Nicht für: int, long, datetime, ...) SELECT TABLE_NAME, COLUMN_NAME, COLLATION_NAME , DATA_TYPE, * FROM INFORMATION_SCHEMA.COLUMNS where COLLATION_NAME is NOT NULL order by TABLE_NAME asc, ORDINAL_POSITION asc --3440 Sätze (hier noch die Systemtabellen und u.a.) Select o.name, c.name, c.collation_name, t.name from sys.all_objects o INNER JOIN sys.columns c ON o.object_id = c.object_id inner join sys.types t ON t.user_type_id = c.user_type_id where type = 'U' and c.COLLATION_NAME is NOT NULL order by o.name asc, c.column_id asc --2770
Link: https://www.sqlservercentral.com/Forums/Topic1156549-391-1.aspx
/* Erkennen ob ein Agent Job noch läuft und diesen ggf. auch abbrechen.... Link: https://www.sqlservercentral.com/Forums/Topic1156549-391-1.aspx */ SELECT [sjv].[name], [sjv].[description], [sja].[run_requested_date], [sja].[last_executed_step_id] FROM [msdb].[dbo].[sysjobs_view] sjv JOIN [msdb].[dbo].[sysjobactivity] sja ON [sjv].[job_id] = [sja].[job_id] WHERE [sja].[run_requested_date] is not null AND [sja].[stop_execution_date] is null USE msdb GO --EXEC dbo.sp_stop_job N'....hier den namen des Jobs...'
Der Code stammt nicht von mir und weiß leider nicht mehr wer die Abfrage geschrieben hat. Aber er/sie hat sehr gutes SELECT über dem „NULL Problem“ geschrieben:
DECLARE @Var int; SET @Var = NULL; IF @Var = 8 OR @Var <> 8 PRINT 'It either is or isn''t 8'; ELSE PRINT 'Huh? It''s not 8, but it''s not not 8 either!'; IF @Var = @Var PRINT 'Duh, of course it''s equal to itself'; ELSE PRINT 'What the hey, it''s not even equal to itself!!';
/*
-----------------------------------------------------
-- Infos zu den langsamsten SQL-Statement
-- use master
-- Es muss gewisse Rechte bestehen (z.B. "SA") "sysdba" reicht nicht aus"
Hinweis: Durations können z.B. aus dem Einlesen von vielen Millionen von Sätzen stammen
Ebenso bitte beachten, dass AvgDuration[s] meist aus "Warten" von gesperrten Objekten stammen.
Daher sollte die AvgCpuTime und die Executions besonders bewertet werden!
-----------------------------------------------------
Änderungen:
2016-02-19 ORDER BY "AvgCpuTime[s]"
*/
use master
go
SELECT TOP(200)
qs.execution_count AS "Executions",
--CAST(CAST(qs.total_elapsed_time AS NUMERIC(20,4))/1000/qs.execution_count AS NUMERIC(20,4))
--CAST(CAST(qs.total_worker_time AS NUMERIC(20,4))/1000/qs.execution_count AS NUMERIC(20,4))
--AS "AvgCpuTime[ms]", /*Umrechung_in_Millisekunden*/
cast(CAST(CAST(qs.total_worker_time AS NUMERIC(20,4))/1000/qs.execution_count AS NUMERIC(20,4)) / 1000 as Numeric (20,1))
AS "AvgCpuTime[s]", /*Umrechung_in_Sekunden*/
SUBSTRING(st.text,(qs.statement_start_offset+2)/2, /*Offset wird in Bytes angegeben*/
(CASE WHEN qs.statement_end_offset = -1
THEN LEN(CAST(st.text AS NVARCHAR(MAX)))*2
ELSE qs.statement_end_offset
END - qs.statement_start_offset)/2)
AS "SqlStatement",
--AS "AvgDuration[ms]", /*Umrechung_in_Millisekunden*/
CAST(CAST(CAST(qs.total_elapsed_time AS NUMERIC(20,4))/1000/qs.execution_count AS NUMERIC(20,4)) / 1000 as Numeric (20,1))
AS "AvgDuration[s]", /*Umrechung_in_Sekunden*/
CAST (CAST(CAST(qs.last_elapsed_time AS NUMERIC(20,4))/1000 AS NUMERIC(20,4)) / 1000 as Numeric (20,1))
AS "LastDuration[s]", /*Umrechung in Sekunden*/
CAST( CAST(CAST(qs.last_worker_time AS NUMERIC(20,4))/1000 AS NUMERIC(20,4)) / 1000 as Numeric (20,1))
AS "LastCpuTime[s]", /*Umrechung in Sekunden*/
CAST( CAST(CAST(qs.total_elapsed_time AS NUMERIC(20,4))/1000 AS NUMERIC(20,4)) / 1000 as Numeric (20,1))
AS "TotalDuration[s]", /*Umrechung in Sekunden*/
CAST ( CAST(CAST(qs.total_worker_time AS NUMERIC(20,4))/1000 AS NUMERIC(20,4)) / 1000 as Numeric (20,1))
AS "TotalCpuTime[s]", /*Umrechung in Sekunden*/
qs.total_logical_reads AS "TotalLogicalReads",
qs.total_physical_reads AS "TotalPhysicalReads",
qs.total_logical_writes AS "TotalLogicalWrites",
qs.creation_time AS "FirstExecution",
qs.last_execution_time AS "LastExecution",
db_name(st.dbid) AS "Database"
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS st
WHERE qs.total_elapsed_time > 0
-- and qs.last_execution_time > '2016-02-18 19:00:00'
-- and (qs.last_execution_time < '2016-02-18 19:00:00' or qs.creation_time < '2016-02-18 19:00:00')
--ORDER BY "AvgDuration[ms]" DESC, qs.execution_count DESC
--ORDER BY "AvgDuration[s]" DESC, qs.execution_count DESC
ORDER BY "AvgCpuTime[s]" DESC, qs.execution_count DESC
/*
Select * FROM sys.dm_exec_query_stats AS qs
*/
/*
Bitte als "sa" anmelden (möglicherweise reicht die Windows-Authentification)
Hinweis: wenn z.B. ein Satz verändert wird, werden neben der Tabelle auch alle Indizes gesperrt.
Das ist der Grund für die vielen gesperrten Zeilen
*/
/*
KILL 141
KILL 141 WITH STATUSONLY --dies ist nur bei komplexen Rollback's sinnvoll möglich
*/
USE master
go
-- Locks indentifizieren --------------------------------------------------------------------------------
Select request_session_id, blocking_session_id, DatabaseName,dbid, LockedObjectName, LockedObjectId, LockedResource, LockType, LoginName, HostName
, IsUserTransaction, TransactionName, TransactionStartTime
, replace(replace(replace(replace( ST.text ,CHAR(9),' ') ,CHAR(13),' ') ,CHAR(10),' '),';',':') AS SqlStatementText /* SQL Statement */
from (
SELECT DISTINCT L.request_session_id AS request_session_id, -- Anfragender System Process Identifier
t2.blocking_session_id as blocking_session_id, -- Blockierender System Process Identifier
--request_owner_guid AS UOW, -- Identifiziert die Arbeitseinheits-ID (Unit of Work, UOW) verteilter Transaktionen
DB_NAME(L.resource_database_id) AS DatabaseName, -- Databasename
O.Name AS LockedObjectName, -- Object name
P.object_id AS LockedObjectId, -- Eindeutige Object identification number
L.resource_type AS LockedResource, -- Resource which is locked: DATABASE, FILE, OBJECT, PAGE, KEY, EXTENT, RID, APPLICATION, METADATA, HOBT, ALLOCATION_UNIT.
L.request_mode AS LockType, -- Lock Type: Exclusive locks (X), Intent locks (I), Shared locks (S), Update locks (U), Schema locks (Sch)
--L.resource_description AS LockDescription, -- LockDescription
--ST.text AS SqlStatementText, -- SQL Statement
--replace(replace(replace( ST.text ,CHAR(9),' ') ,CHAR(13),'/*CR*/') ,CHAR(10),'/*LF*/') AS SqlStatementText, /* SQL Statement */
CN.most_recent_sql_handle,
ES.login_name AS LoginName, -- Loginname mit dem der User an der DB angemeldet ist
ES.host_name AS HostName, -- Hostname des verbunden clients
TST.is_user_transaction as IsUserTransaction, -- 1 = Die Transaktion wurde von einer Benutzeranforderung initiiert. 0 = Systemtransaktion.
AT.name as TransactionName, -- Transaktionsname
AT.transaction_begin_time as TransactionStartTime,-- Uhrzeit des Transaktionsbeginns.
CN.auth_scheme as AuthenticationMethod -- Login Methode SQL / Windows authentifizierung
FROM sys.dm_tran_locks L
left JOIN sys.partitions P ON P.hobt_id = L.resource_associated_entity_id
left JOIN sys.objects O ON O.object_id = P.object_id
left JOIN sys.dm_exec_sessions ES ON ES.session_id = L.request_session_id
left JOIN sys.dm_tran_session_transactions TST ON ES.session_id = TST.session_id
left JOIN sys.dm_tran_active_transactions AT ON TST.transaction_id = AT.transaction_id
left JOIN sys.dm_exec_connections CN ON CN.session_id = ES.session_id
left JOIN sys.dm_os_waiting_tasks as t2
ON L.lock_owner_address = t2.resource_address
) as temp
CROSS APPLY sys.dm_exec_sql_text(temp.most_recent_sql_handle) AS ST
WHERE 1=1
--and resource_database_id = db_id('SalesLogix_Prod') --nur Locks einer bestimmten Datenbank betrachten
--and L.request_mode in ('X', 'IX') -- Exklusive Locks
--and L.request_mode NOT in ('S')
--and (t2.blocking_session_id IS NOT NULL OR L.request_mode = 'X') --klare Störer und Opfer darstellen
ORDER BY temp.request_session_id
/*
In welchem View kommt ein bestimmter Code vor?
Hinweis: in verschluesselten Views funktioniert dies nicht
Hinweis: Möglicherweise funktioniert der Code nur ab SQL Server 2005
Hinweis: optional ginge auch: exec sp_helptext 'schema.viewname'
*/Select v.name, m.definition
from sys.sql_modules m
inner join sys.views v ON v.object_id = m.object_id
where 1=1
--and v.object_id = 2815072
and m.definition like '%sysdba.%'
and m.definition like '%dbo.%'
and m.definition like '%Contact%'
order by v.name, v.object_id
/*
SQL um die Daten in einem "Blobdata" lesbar zu machen...
Grundsätzlich macht dies nur Sinn, wenn der Inhalt vom Typ ASCII ist
*/
--select name, * from sysobjects order by name
IF EXISTS (select name, * from sysobjects where name = 'fx_blob2varchar' and Type = 'FN') Drop function fx_blob2varchar
go
CREATE FUNCTION fx_blob2varchar ( @blob datablob )
RETURNS varchar(max)
AS
BEGIN
DECLARE @i bigint
DECLARE @end bigint
DECLARE @ergebnis varchar(max)
SELECT @i = 1, @ergebnis = '', @end=DATALENGTH(@blob)
WHILE @i < @end
BEGIN
If ascii(substring(@blob,@i,1)) <= 31 or ascii(substring(@blob,@i,1)) >= 127
Set @ergebnis = @ergebnis + '.'
ELSE
Set @ergebnis = @ergebnis + char(substring(@blob,@i,1))
SET @i = @i + 1
END
RETURN @ergebnis
END
go
/*
--Im Beispiel wird auch noch das "SELECT" herausgeschnitten:
Select sysdba.fx_blob2varchar (data) as Rohdaten,
substring(
sysdba.fx_blob2varchar (data),
charindex('SELECT ', sysdba.fx_blob2varchar (data)),
len(sysdba.fx_blob2varchar (data))
) as Herausgeschnitten
,name
,displayname
,*
from Plugin
where type = 23 --also nur 'GRUPPEN'
and sysdba.fx_blob2varchar (data) like '%GC_MANUFACTURERPROD%'
ORDER BY displayname ASC
*/
Quelle: Quelle: http://stackoverflow.com/questions/16491/restore-database-backup-over-the-network
/* Wiederherstellen einer Datenbank von einem Netzwerklaufwerk Problem: Der SQL-Server erlaubt nur lokale Laufwerke Lösung: "NET USE ..." in Verbindung von "xp_cmdshell" Hinweis: Ein benutzes Passwort kann/wird bei Passwort-Wechsel Problemen führen Quelle: http://stackoverflow.com/questions/16491/restore-database-backup-over-the-network */ -- zuvor prüfen, wie die werte in der master vor dem setzen der werte eingerichtet sind, -- um sie hinterher wieder auf die ursprünglichen werte zurücksetzen zu können EXEC sp_configure -- prüfen und dokumentieren, zum zudrückstellen auf die ursprungswerte -- werte unter !!!show advanced options!!! -- JETZT muss die advanced options eingeschaltet werden, um danach zu prüfen, ob die xp_cmdshell bereits aktiviert ist oder nicht. -- Setzen der werte + aktivieren der xp_cmdshell -- STEP 1: -- allow changes to advanced options EXEC sp_configure 'show advanced options', 1 GO -- Update currently configured values for advanced options. RECONFIGURE GO -- To enable xp_cmdshell EXEC sp_configure 'xp_cmdshell', 1 GO -- Update currently configured values for advanced options. RECONFIGURE GO -- nutzen der xp_cmdshell zum mounten des netzwerkpfades direkt im SMSS -- STEP 2: EXEC xp_cmdshell 'NET USE Z: \\Srv\Path password1 /USER:Domain\UserName' -- wiederherstellen der datenbank mit dem nun sichtbaren laufwerk -- STEP 3: RESTORE DATABASE DataBaseNameHere FROM DISK = 'Z:\BackNameHere.BAK' GO /* ggf. EXEC xp_cmdshell 'NET USE DELETE Z:' ggf. EXEC sp_configure 'xp_cmdshell', 0 ggf. EXEC sp_configure 'show advanced options', 0 */
Di laufende SSIS package executions in dem Integration Server Catalog finden und ggf. beenden.
Quelle: https://sabigdata.wordpress.com/2015/02/12/stop-ssis-package-execution-in-ssis-catalog/
/*
Stop SSIS package executions in "SSISDB" (Integration Service Catalogs)
Links:
Stop SSIS Package execution in SSIS.Catalog
*/
--per SSMS-Dialog:
--Rechte Maus auf "SSISDB" und "Active Operations" auswählen...Dort den gewünschten Paackage auswählen und auf "STOP" klicken.
--per SQL die "laufende SSIS package executions" bestimmen:
Select status, start_time as start_time2, folder_name, project_name ,* from SSISDB.catalog.executions where end_time is null order by start_time desc
/*
--Die richtige execution_id auswählen und als @operation_id eintragen (typisch MS - muss ja auch keiner Logik folgen):
Exec catalog.stop_operation @operation_id = ...
*/
What is a system-versioned temporal table?
A system-versioned temporal table is a type of user table designed to keep a full history of data changes and allow easy point in time analysis. This type of temporal table is referred to as a system-versioned temporal table because the period of validity for each row is managed by the system
(Quelle: https://docs.microsoft.com/en-us/sql/relational-databases/tables/temporal-tables)
Beispiel:
SELECT * FROM Employee FOR SYSTEM_TIME BETWEEN '2014-01-01 00:00:00.0000000' AND '2015-01-01 00:00:00.0000000'
Video: https://channel9.msdn.com/Shows/Data-Exposed/Temporal-in-SQL-Server-2016
Weitere sehr gute Beschreibung: http://sqlhints.com/tag/for-system_time-as-of/
Quelle:
Garantiestatus von Festplatten prüfen
Quelle: heise.de: Vorsicht-vor-OEM-Festplatten
Das Kingswaysoft verlangt eine bestimmte Datenstruktur um diese in MS Dynamics 365 zu bringen. Gleiches gilt für…
Sperren automatisch erkennen und melden als E-Mail:
/*
Erkennen von Sperren und Emails wenn Session dabei warten muessen
- -
Changes:
2015-09-24 11:47 ODFR: Die CSV-Datei kleiner gemacht + den echten Zeitpunkt einer Sperre die behindert!
2015-09-29 12:34 ODFR: Kommentare sollen nur Slash+Stern sein, sonst Gefahr auf fehlerhafter Funktionalit‰t
2015-09-30 13:22 ODFR: Auch Backup-Jobs m¸ssen ggf. auch dargestellt werden (diese sind keine Transactions)
*/
/*Vorbereitungen:*/
DECLARE @iMinuten int;
SET @iMinuten = 2;
DECLARE @iMinutenSeitSperre int;
DECLARE @cQuery_attachment_filename varchar(64);
SET @cQuery_attachment_filename='Sperren_'+replace(replace(replace(convert(char(16), Getdate(), 121),' ','_'),'-',''),':','')+'.csv';
DECLARE @cQuery_result_separator char(1);
SET @cQuery_result_separator=CHAR(9);
/*Die Query vorbereiten, die sowohl zum Bewerten von Sperren, als auch ggf. in einer Email benutzt wird:*/
DECLARE @cQuery varchar(8000);
SET @cQuery = 'SELECT DISTINCT L.request_session_id AS request_session_id, /* Anfragender System Process Identifier */
t2.blocking_session_id as blocking_session_id, /*Blockierender System Process Identifier */
/*request_owner_guid AS UOW,*/ /* Identifiziert die Arbeitseinheits-ID (Unit of Work, UOW) verteilter Transaktionen */
DB_NAME(L.resource_database_id) AS DatabaseName, /* Databasename */
O.Name AS LockedObjectName, /* Object name */
P.object_id AS LockedObjectId, /* Eindeutige Object identification number */
L.resource_type AS LockedResource, /* Resource which is locked: DATABASE, FILE, OBJECT, PAGE, KEY, EXTENT, RID, APPLICATION, METADATA, HOBT, ALLOCATION_UNIT. */
L.request_mode AS LockType, /* Lock Type: Exclusive locks (X), Intent locks (I), Shared locks (S), Update locks (U), Schema locks (Sch) */
/*L.resource_description AS LockDescription,*/ /* LockDescription */
replace(replace(replace( ST.text ,CHAR(9),'' '') ,CHAR(13),''/*CR*/'') ,CHAR(10),''/LF*/'') AS SqlStatementText, /* SQL Statement */
ES.login_name AS LoginName, /* Loginname mit dem der User an der DB angemeldet ist */
ES.host_name AS HostName, /* Hostname des verbunden clients */
TST.is_user_transaction as IsUserTransaction, /* 1 = Die Transaktion wurde von einer Benutzeranforderung initiiert. 0 = Systemtransaktion. */
AT.name as TransactionName, /* Transaktionsname */
AT.transaction_begin_time as TransactionStartTime,/* Uhrzeit des Transaktionsbeginns. */
coalesce(AT.transaction_begin_time, ES.last_request_start_time) as SQLJobStartTime, /* Uhrzeit des Beginns des laufenden SQLs. */
CN.auth_scheme as AuthenticationMethod /* Login Methode SQL / Windows Authentifizierung */
FROM sys.dm_tran_locks L
left JOIN sys.partitions P ON P.hobt_id = L.resource_associated_entity_id
left JOIN sys.objects O ON O.object_id = P.object_id
left JOIN sys.dm_exec_sessions ES ON ES.session_id = L.request_session_id
left JOIN sys.dm_tran_session_transactions TST ON ES.session_id = TST.session_id
left JOIN sys.dm_tran_active_transactions AT ON TST.transaction_id = AT.transaction_id
left JOIN sys.dm_exec_connections CN ON CN.session_id = ES.session_id
CROSS APPLY sys.dm_exec_sql_text(CN.most_recent_sql_handle) AS ST
left JOIN sys.dm_os_waiting_tasks as t2
ON L.lock_owner_address = t2.resource_address
WHERE 1=1
/*and resource_database_id = db_id(''MYDATABASE'')*/ /*nur Locks einer bestimmten Datenbank betrachten*/
/*and L.request_mode in (''X'', ''IX'') */ /* nur Exklusive Locks */
and (t2.blocking_session_id IS NOT NULL OR L.request_mode like ''%X%'' or L.request_mode = ''NULL'' or L.request_mode is NULL) /*mˆgliche Stˆrer und klare Opfer darstellen*/
ORDER BY L.request_session_id';
/*Code zum Bewerten von Sperren:*/
Declare @dTransactionStartTime Datetime;
Declare @tTable Table (
request_session_id int, /* Anfragender System Process Identifier */
blocking_session_id int, /*Blockierender System Process Identifier */
DatabaseName nvarchar(255), /* Databasename */
LockedObjectName nvarchar(255), /* Object name */
LockedObjectId int, /* Eindeutige Object identification number */
LockedResource nvarchar(255), /* Resource which is locked: DATABASE, FILE, OBJECT, PAGE, KEY, EXTENT, RID, APPLICATION, METADATA, HOBT, ALLOCATION_UNIT. */
LockType nvarchar(10), /* Lock Type: Exclusive locks (X), Intent locks (I), Shared locks (S), Update locks (U), Schema locks (Sch) */
SqlStatementText nvarchar(max), /* SQL Statement */
LoginName nvarchar(255), /* Loginname mit dem der User an der DB angemeldet ist */
HostName nvarchar(255), /* Hostname des verbunden clients */
IsUserTransaction int, /* 1 = Die Transaktion wurde von einer Benutzeranforderung initiiert. 0 = Systemtransaktion. */
TransactionName nvarchar(255), /* Transaktionsname */
TransactionStartTime datetime,/* Uhrzeit des Transaktionsbeginns. */
SQLJobStartTime datetime,/* Uhrzeit des Transaktionsbeginns. */
AuthenticationMethod nvarchar(255) /* Login Methode SQL / Windows Authentifizierung */
)
/*Einmal die Eckwerte aller aktuellen Session einlesen, die entweder sperren oder selber warten muessen*/
Insert into @tTable
exec ( @cQuery )
/* Aelteste Sperre, egal ob ein echtes Warten vorliegt oder nicht */
/*
SELECT TOP 1 @dTransactionStartTime=AT.transaction_begin_time
FROM sys.dm_tran_locks L
left JOIN sys.partitions P ON P.hobt_id = L.resource_associated_entity_id
left JOIN sys.objects O ON O.object_id = P.object_id
left JOIN sys.dm_exec_sessions ES ON ES.session_id = L.request_session_id
left JOIN sys.dm_tran_session_transactions TST ON ES.session_id = TST.session_id
left JOIN sys.dm_tran_active_transactions AT ON TST.transaction_id = AT.transaction_id
WHERE AT.transaction_begin_time IS NOT NULL
and L.request_mode NOT in ('S','IS')
ORDER BY AT.transaction_begin_time ASC;
*/
/* Nur Sperren beachten, wenn ein andere Session wartet */
/*
Select @dTransactionStartTime=Min(AT.transaction_begin_time)
FROM sys.dm_tran_locks L
left JOIN sys.dm_exec_sessions ES ON ES.session_id = L.request_session_id
left JOIN sys.dm_tran_session_transactions TST ON ES.session_id = TST.session_id
left JOIN sys.dm_tran_active_transactions AT ON TST.transaction_id = AT.transaction_id
left JOIN sys.dm_os_waiting_tasks as t2 ON L.lock_owner_address = t2.resource_address
having count(t2.blocking_session_id) > 0
*/
/*Tabelle definieren nur für sperrende und abhaengige Sessions:*/
Declare @tTable2 Table (
request_session_id int, /* Anfragender System Process Identifier */
blocking_session_id int, /*Blockierender System Process Identifier */
TransactionStartTime datetime /* Uhrzeit des Transaktionsbeginns. */
)
/*Und nun die sperrende und abhaengige Sessions aus vorhin befuellten Tabelle bestimmen:*/
;With hLocks (request_session_id, blocking_session_id, TransactionStartTime)
as (
Select request_session_id, blocking_session_id, SQLJobStartTime
From @tTable t1
where blocking_session_id is NOT NULL
union all
Select t2.request_session_id, t2.blocking_session_id, t2.SQLJobStartTime
from (
Select request_session_id, blocking_session_id, SQLJobStartTime
From @tTable
) t2 inner join hLocks on t2.request_session_id = hLocks.blocking_session_id
)
Insert into @tTable2
Select * from hLocks
Select @dTransactionStartTime = Min(TransactionStartTime) from @tTable2
/*
Select * from @tTable
Select @dTransactionStartTime
*/
/*Nun die Ergbnisse bewerten:*/
SET @iMinutenSeitSperre=coalesce(datediff(mi,@dTransactionStartTime,GetDate()),0);
DECLARE @cSubject varchar(128);
SET @cSubject = 'SPERRE SEIT MEHR ALS '+cast(@iMinutenSeitSperre as varchar(10))+' Minuten';
DECLARE @cBody varchar(512);
SET @cBody = 'Die Sperre fing an um '+convert(char(16), @dTransactionStartTime, 121)+' und somit vor '+cast(@iMinutenSeitSperre as varchar(10))+' Minuten.'
+CHAR(13)+'Es könnte in '+cast((60 - (@iMinutenSeitSperre % 60) ) as varchar(10))+' Minuten eine neue Email versendet werden,'
+CHAR(13)+'wenn das Problem bis dahin nicht gelöst ist!';
/*Die erste Email darf natürlich kommen und erst dann nur alle volle Stunden*/
/*es können derzeit (leider) bis 3 Email versendet werden */
If @iMinutenSeitSperre > 0
If (@iMinutenSeitSperre % 60) between @iMinuten and (@iMinuten * 2)
BEGIN
/*print 'SPERRE SEIT MEHR ALS '+cast(@iMinuten as varchar(10))+' Minuten'*/
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'SQLService'
,@recipients = 'admins@emailserver.local'
,@copy_recipients = 'user1@emailserver.local'
/*,@blind_copy_recipients = 'private1@emailserver.local'*/
,@subject = @cSubject
,@body = @cBody
,@query = @cQuery
,@query_result_header = 1
,@query_result_separator = @cQuery_result_separator
,@query_no_truncate = 1
,@append_query_error = 1
,@query_result_width = 32767
,@query_attachment_filename = @cQuery_attachment_filename /*'Sperren.csv'*/
,@attach_query_result_as_file = 1 ;
END
/*
ELSE
BEGIN
print @cBody;
END
Else
print 'Keine Sperren.'
*/
Voraussetzungen: Database Mail ist aktiviert und eingerichtet. Es muss das „Profil“ und natürlich die Ziel Emailadressen angepasst werden.
Ebenso kann es sein, dass im Database Mail die Beschränkung der Attachment-Größe etwas gelockert wird (Systemparameter in Database Mail).
Link: https://docs.microsoft.com/de-de/sql/relational-databases/database-mail/configure-database-mail#SystemParameters
Sperren erkennen
Sonstiges