MSCRM: Unit/UnitGroup

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“

MSCRM: Activity

Prinzipiell werden die Daten im MSCRM in den folgenden Tabellen 


Es gibt neben der Grafik weitere Activities, die das MSCRM unterstützt.

 

Scribe Insight 7.9.2 Zugriff auf NAV

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:

Kommaseparierte Liste – Explode (string_split)

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

Kommaseparierte Liste – Implode (XML)

 

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!

Kommaseparierte Liste – Implode (Funktion)

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

Kommaseparierte Liste – Explode (Rekursiv)

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


Eine SalesLogix Tabelle umbennen


/*

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 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

Powershell: Welche .NET Versionen sind aktuell installiert?

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

Änderungen von Datenbanken dokumentieren per Trigger…

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
*/

Funktion: Jahr, Monat und Tag als „Datetime“…

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:		
-- Create date:	<2009-01-30>
-- Description:	 
-- =============================================
BEGIN

  DECLARE @d datetime;

  SET @d = dateadd(year,(@Year - 1753),'1/1/1753');
  SET @d = dateadd(month,@Month - 1,@d);

  RETURN dateadd(day, @Day - 1, @d)
END
go

--Select dbo.gc_YMD2Datetime(2009, 1, 30)

Funktion: fod_IsEmailValid

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 Informationen feststellen…

--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


Erkennen ob ein Agent Job noch läuft und diesen ggf. auch abbrechen….

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...' 




Ist „NULL = NULL“? Oder ist es doch nicht?

Der Code stammt nicht von mir und weiß leider nicht mehr wer die Abfrage geschrieben hat. Aber er/sie hat sehr gutes SELECT mit 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!!';

Die langsamsten SQL Statements

/*
-----------------------------------------------------
-- 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
*/

Sperrende SQL Befehle indentifizieren

/*
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 

Alle Views finden, in dem bestimmter Code vorkommt

/*
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

Daten von einem „Blobdata“ lesbar machen…


/*
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
*/

Wiederherstellen einer Datenbank von einem Netzwerklaufwerk

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
*/

SSIS package executions in „SSISDB“ (Integration Service Catalogs)

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 =  ...
*/

SYSTEM_VERSIONING (Temporal Tables)

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:

http://sqlhints.com/2015/12/31/temporal-tables-in-sql-server-2016-part-2-querying-system-versioned-temporal-table/

 

Garantiestatus von Festplatten prüfen

Quelle: heise.de: Vorsicht-vor-OEM-Festplatten

 

MS SQL: Sperren melden als Agentjob

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