Category Archives: TSQL

MS SQL TSQL PadLeft

6th February 2014 andre Leave a comment

Ever wanted a PadLeft for SQL server
Here’s the next best thing, a scalar function we wrote and implemented into all our databases
Enjoy

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Andre Pageot | Michael Grabsky
-- Create date: 06/02/2014
-- Description:	Pads a string out
-- USAGE: SELECT dbo.fnPadLeft('123456',9,'0')
--	returns: 000123456
-- =============================================
CREATE FUNCTION [dbo].[fnPadLeft]
(
	-- Parameters taken from the .NET implementation of PadLeft
	@StringToPad varchar(250)
	,@totalWidth int
	,@paddingChar varchar(1)

)
RETURNS varchar(250)
AS
BEGIN
	/*	DECLARE	@StringToPad varchar(250)
			,@totalWidth int
			,@paddingChar varchar(1)
			SELECT @StringToPad = '123456',@totalWidth = 9,@paddingChar='0'
	*/

	DECLARE @returnString varchar(5)
	DECLARE @PadString varchar(max) SET @PadString =''

	WHILE LEN(@Padstring) < @totalWidth
		BEGIN
			SELECT @PadString = @PadString + @paddingChar
		END		

	--SELECT RIGHT(@PadString + RTRIM(@StringToPad), @totalWidth)

	RETURN RIGHT(@PadString + RTRIM(@StringToPad), @totalWidth)
END

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

-- =============================================

-- Author: Andre Pageot | Michael Grabsky

-- Create date: 06/02/2014

-- Description: Pads a string out

-- USAGE: SELECT dbo.fnPadLeft('123456',9,'0')

-- returns: 000123456

-- =============================================

CREATE FUNCTION [dbo].[fnPadLeft]

(

-- Parameters taken from the .NET implementation of PadLeft

@StringToPad varchar(250)

,@totalWidth int

,@paddingChar varchar(1)

)

RETURNS varchar(250)

BEGIN

/* DECLARE @StringToPad varchar(250)

,@totalWidth int

,@paddingChar varchar(1)

SELECT @StringToPad = '123456',@totalWidth = 9,@paddingChar='0'

DECLARE @returnString varchar(5)

DECLARE @PadString varchar(max) SET @PadString =''

WHILE LEN(@Padstring) < @totalWidth

BEGIN

SELECT @PadString = @PadString + @paddingChar

END

--SELECT RIGHT(@PadString + RTRIM(@StringToPad), @totalWidth)

RETURN RIGHT(@PadString + RTRIM(@StringToPad), @totalWidth)

END

Infrastructure, SQL, Tech, TSQL

SQL Server Monitor Changes to SCHEMA

4th June 2013 andre Leave a comment

If you need to prevent changes to your SQL Server Schema, or at least monitor them, then this post is worth reading

I needed to monitor/control changes made to SQL server databases at server instance level. There are plenty of ways of doing this but there is nothing as simple as a DDL level trigger. It’s clean, can be disabled quickly for rapid change implementation and its a central location which means deploying to sever SQL Servers or a server farm is that much easier.

So the basic requirements
1. We must be able to monitor CREATE,DROP and ALTER on Tables, Views, Stored Procedures, Triggers and Functions
2. We must be able to filter based on user, applying the rule or not
3. We must have a log of each schema change regardless of success or failure
4. We need to send an email when a change has been attempted and was disallowed

So how are we going to do this. Well we can use the ROLLBACK inside our trigger to prevent the changes from occurring, and we can use the RAISERROR to display the appropriate message we want to user/program to receive when the attempt fails. We can also take advantage of the code SQL Server query execution sequence such that after a rollback has been called in a trigger, the parent transaction has been dumped, but any further transactions will continue to be executed and committed. See here http://msdn.microsoft.com/en-us/library/ms187844(v=sql.105).aspx

Some Key fundamentals we are going to be using
First off the

CREATE TRIGGER [TriggerName] ON ALL SERVER

1	CREATE TRIGGER [TriggerName] ON ALL SERVER

, this will allow us to create the trigger at server level, so it affects all databases. It will hence be located in the Server Objects >> Triggers within SQL Server Management Studio
Next comes

EventData()

1	EventData()

which contains all the details we want about what is changing and who is trying to change it
We will be using “

SYSTEM_USER

1	SYSTEM_USER

” to identify the current logged in connection that is behind the event and also filter on this to make a decision to reject or approve the change.
Finally we use the

ROLLBACK

ROLLBACK

and

RAISERROR

RAISERROR

commands to initiate a rejection of the event, followed with our logging insert and in my case an email send method, I won’t get into the details of the mail sending in this article, however its worth mentioning that I have a fully fledged SSIS package driven mail sending engine integrated with MS Exchange, its possible to send from any mailbox to any mail group or address in the active directory or individual mail address book, you can attach files from the network or from binary within the database, you can send with importance in text format or html as this example demonstrates, Its a very powerful and very handy engine to have in the systems topology, took 3 days to write and about a month to fine tune, suddenly sending mail is very easy from anywhere in your infrastructure. Anyway I digress

Enough background, here’s the Code. You will obviously need to adjust bits to suite your needs before you use this
This should be executed against the master

/*
IF  EXISTS (SELECT * FROM master.sys.server_triggers 
WHERE parent_class_desc = 'SERVER' AND name = N'ddl_SQL_Server_Schema_Lock')
DROP TRIGGER [ddl_SQL_Server_Schema_Lock] ON ALL SERVER;
*/

CREATE TRIGGER [ddl_SQL_Server_Schema_Lock]
    ON ALL SERVER
    FOR ALTER_PROCEDURE, DROP_PROCEDURE, 
        ALTER_TABLE, DROP_TABLE, 
        ALTER_FUNCTION, DROP_FUNCTION, 
        ALTER_INDEX, DROP_INDEX, 
        ALTER_VIEW, DROP_VIEW,
        ALTER_TRIGGER, DROP_TRIGGER
    AS 
    BEGIN

		/*Record the change and who made it, we must do this before our ROLLBACK  otherwise we lose access to the EventData*/
		DECLARE @EventData XML,
				@DatabaseName varchar(800),
				@Schema SYSNAME,
				@Object SYSNAME,
				@EventType SYSNAME,
				@SQL VARCHAR(max)
				
		SET @EventData = EventData()
		SET @DatabaseName = '' + @EventData.value('data(/EVENT_INSTANCE/DatabaseName)[1]', 'VARCHAR(50)')
		SET @Schema = @EventData.value('data(/EVENT_INSTANCE/SchemaName)[1]', 'VARCHAR(50)')
		SET @Object = @EventData.value('data(/EVENT_INSTANCE/ObjectName)[1]', 'VARCHAR(50)')
		SET @EventType = @EventData.value('data(/EVENT_INSTANCE/EventType)[1]', 'VARCHAR(50)')
		SET @SQL = @EventData.value('data(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]', 'VARCHAR(max)')
		
		/*Check user before allowing changes*/
		DECLARE @Msg varchar(MAX),@Blocked bit=0;
		IF (SELECT count(*) WHERE SYSTEM_USER IN ('NT AUTHORITY\SYSTEM','Domain\AllowUserName')) = 0
			BEGIN
				SET @Msg = 'Direct changes to LIVE are prohibited, Changes made to LIVE need to go through the roll out process';
				SET @Blocked = 1
				ROLLBACK;
				RAISERROR (@Msg,16,1);
				--print @Msg;				
				--print'blocked'
			END			
			
	
		--Continue to log this event	to our logging table	
		INSERT INTO [SFCLWareHouse].dbo.SQL_Server_SchemaAudit 
			(AuditDate, UserName, DataBaseName, [Event], [Schema], Object, TSQL, [XMLEventData],Blocked)
		SELECT 
			GetDate(),
			SYSTEM_USER,--@EventData.value('data(/EVENT_INSTANCE/UserName)[1]', 'SYSNAME')
			@DatabaseName,
			@EventType, @Schema, @Object,
			@SQL,
			@EventData,
			@Blocked
		
		/*Notify of blocked changes if the event was not allowed*/
		IF @Blocked = 1
			BEGIN
				DECLARE @MailID int, @HTMLBody varchar(max)
				SET @HTMLBody ='An attempt has been made at changing the SQL Schema for sqlprod.swisscantouk.local &lt;br&gt;&lt;br&gt;&#39; +
								&#39;&lt;TABLE style=&quot;border:none&quot;&gt;&lt;TD&gt;Time:&lt;/TD&gt;&lt;TD&gt;&#39; + CAST(GETDATE() as varchar(MAX)) + &#39;&lt;/TD&gt;&lt;/TR&gt;&#39; +
								&#39;&lt;TD&gt;User:&lt;/TD&gt;&lt;TD&gt;&#39; + SYSTEM_USER + &#39;&lt;/TD&gt;&lt;/TR&gt;&#39; +
								&#39;&lt;TD&gt;Database:&lt;/TD&gt;&lt;TD&gt;&#39; + @DatabaseName + &#39;&lt;/TD&gt;&lt;/TR&gt;&#39; +
								&#39;&lt;TD&gt;Event:&lt;/TD&gt;&lt;TD&gt;&#39; + @EventType + &#39;&lt;/TD&gt;&lt;/TR&gt;&#39; +
								&#39;&lt;TD&gt;ObjectName:&lt;/TD&gt;&lt;TD&gt;&#39; + @Object + &#39;&lt;/TD&gt;&lt;/TR&gt;&#39; +
								&#39;&lt;TD&gt;TSQL:&lt;/TD&gt;&lt;TD&gt;&#39; + @SQL + &#39;&lt;/TD&gt;&lt;/TR&gt;&#39; 
								
				
				EXECUTE [DataWareHouse].[dbo].[Mail_INSERT] @MailID = @MailID OUTPUT
							,@To = 'monitoredmailbox@domain.com' 
							,@Subject = 'SQL Prod Schema change attmept' 
							,@BodyHTML = @HTMLBody
							,@Importance = 1
			END

    END

IF EXISTS (SELECT * FROM master.sys.server_triggers

WHERE parent_class_desc = 'SERVER' AND name = N'ddl_SQL_Server_Schema_Lock')

DROP TRIGGER [ddl_SQL_Server_Schema_Lock] ON ALL SERVER;

CREATE TRIGGER [ddl_SQL_Server_Schema_Lock]

ON ALL SERVER

FOR ALTER_PROCEDURE, DROP_PROCEDURE,

ALTER_TABLE, DROP_TABLE,

ALTER_FUNCTION, DROP_FUNCTION,

ALTER_INDEX, DROP_INDEX,

ALTER_VIEW, DROP_VIEW,

ALTER_TRIGGER, DROP_TRIGGER

BEGIN

/*Record the change and who made it, we must do this before our ROLLBACK otherwise we lose access to the EventData*/

DECLARE @EventData XML,

@DatabaseName varchar(800),

@Schema SYSNAME,

@Object SYSNAME,

@EventType SYSNAME,

@SQL VARCHAR(max)

SET @EventData = EventData()

SET @DatabaseName = '' + @EventData.value('data(/EVENT_INSTANCE/DatabaseName)[1]', 'VARCHAR(50)')

SET @Schema = @EventData.value('data(/EVENT_INSTANCE/SchemaName)[1]', 'VARCHAR(50)')

SET @Object = @EventData.value('data(/EVENT_INSTANCE/ObjectName)[1]', 'VARCHAR(50)')

SET @EventType = @EventData.value('data(/EVENT_INSTANCE/EventType)[1]', 'VARCHAR(50)')

SET @SQL = @EventData.value('data(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]', 'VARCHAR(max)')

/*Check user before allowing changes*/

DECLARE @Msg varchar(MAX),@Blocked bit=0;

IF (SELECT count(*) WHERE SYSTEM_USER IN ('NT AUTHORITY\SYSTEM','Domain\AllowUserName')) = 0

BEGIN

SET @Msg = 'Direct changes to LIVE are prohibited, Changes made to LIVE need to go through the roll out process';

SET @Blocked = 1

ROLLBACK;

RAISERROR (@Msg,16,1);

--print @Msg;

--print'blocked'

END

--Continue to log this event to our logging table

INSERT INTO [SFCLWareHouse].dbo.SQL_Server_SchemaAudit

(AuditDate, UserName, DataBaseName, [Event], [Schema], Object, TSQL, [XMLEventData],Blocked)

SELECT

GetDate(),

SYSTEM_USER,--@EventData.value('data(/EVENT_INSTANCE/UserName)[1]', 'SYSNAME')

@DatabaseName,

@EventType, @Schema, @Object,

@SQL,

@EventData,

@Blocked

/*Notify of blocked changes if the event was not allowed*/

IF @Blocked = 1

BEGIN

DECLARE @MailID int, @HTMLBody varchar(max)

SET @HTMLBody ='An attempt has been made at changing the SQL Schema for sqlprod.swisscantouk.local <br><br>' +

'<TABLE style="border:none"><TD>Time:</TD><TD>' + CAST(GETDATE() as varchar(MAX)) + '</TD></TR>' +

'<TD>User:</TD><TD>' + SYSTEM_USER + '</TD></TR>' +

'<TD>Database:</TD><TD>' + @DatabaseName + '</TD></TR>' +

'<TD>Event:</TD><TD>' + @EventType + '</TD></TR>' +

'<TD>ObjectName:</TD><TD>' + @Object + '</TD></TR>' +

'<TD>TSQL:</TD><TD>' + @SQL + '</TD></TR>'

EXECUTE [DataWareHouse].[dbo].[Mail_INSERT] @MailID = @MailID OUTPUT

,@To = 'monitoredmailbox@domain.com'

,@Subject = 'SQL Prod Schema change attmept'

,@BodyHTML = @HTMLBody

,@Importance = 1

END

SQL, TSQL

SQL Handling Null Values

22nd January 2011 andre Leave a comment

Having Null Values in your database is a good thing despite those that might tell you otherwsie. Not only does it save space but it is a definitive “im empty” signature. There are of course proper and improper ways to handle SQL Nulls. Just remember never ever try to compare to a null using “=”.

1. A NULL value represents an UNKNOWN value

2. Under ANSI_NULLS ON, two NULL values will never be equal to each other because the two separate values are unknown. With ANSI_NULLS OFF, two separate NULL values will evaluate to equal values.

IF OBJECT_ID('SalesHistory')>0
DROP TABLE SalesHistory;
GO
CREATE TABLE [dbo].[SalesHistory]
(
[SaleID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
[Product] [varchar](10) NULL,
[SaleDate] [datetime] NULL,
[SalePrice] [money] NULL
)
GO
DECLARE @i SMALLINT, @Product VARCHAR(10)
SET @i = 1
WHILE (@i <=100)
BEGIN
SET @Product = CASE WHEN @i%2 = 0 THEN 'Computer' ELSE NULL END
INSERT INTO SalesHistory(Product, SaleDate, SalePrice)
VALUES (@Product, DATEADD(mm, @i, '3/11/1919'), DATEPART(ms, GETDATE()) + (@i + 57))
INSERT INTO SalesHistory(Product, SaleDate, SalePrice)
VALUES('BigScreen', DATEADD(mm, @i, '3/11/1927'), DATEPART(ms, GETDATE()) + (@i + 13))
INSERT INTO SalesHistory(Product, SaleDate, SalePrice)
VALUES('PoolTable', DATEADD(mm, @i, '3/11/1908'), DATEPART(ms, GETDATE()) + (@i + 29))
SET @i = @i + 1
END
GO

SELECT * FROM SalesHistory WHERE Product IS NULL
/*returns all the null values which is NULL*/

SELECT * FROM SalesHistory WHERE Product = NULL
/*returns no rows, since the NULL value cannot be equal to anyother //value.*/

SELECT * FROM SalesHistory WHERE Product IS NOT NULL

SELECT ISNULL(Product, 'Missing') FROM SalesHistory
/*For every null value in Product it is replaced by ‘Missing’*/

SELECT Product, COUNT(*) AS ProductCount, SUM(SalePrice) AS ProductSales
FROM SalesHistory
GROUP BY Product

IF OBJECT_ID('SalesHistory')>0

DROP TABLE SalesHistory;

CREATE TABLE [dbo].[SalesHistory]

(

[SaleID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,

[Product] [varchar](10) NULL,

[SaleDate] [datetime] NULL,

[SalePrice] [money] NULL

)

DECLARE @i SMALLINT, @Product VARCHAR(10)

SET @i = 1

WHILE (@i <=100)

BEGIN

SET @Product = CASE WHEN @i%2 = 0 THEN 'Computer' ELSE NULL END

INSERT INTO SalesHistory(Product, SaleDate, SalePrice)

VALUES (@Product, DATEADD(mm, @i, '3/11/1919'), DATEPART(ms, GETDATE()) + (@i + 57))

INSERT INTO SalesHistory(Product, SaleDate, SalePrice)

VALUES('BigScreen', DATEADD(mm, @i, '3/11/1927'), DATEPART(ms, GETDATE()) + (@i + 13))

INSERT INTO SalesHistory(Product, SaleDate, SalePrice)

VALUES('PoolTable', DATEADD(mm, @i, '3/11/1908'), DATEPART(ms, GETDATE()) + (@i + 29))

SET @i = @i + 1

END

SELECT * FROM SalesHistory WHERE Product IS NULL

/*returns all the null values which is NULL*/

SELECT * FROM SalesHistory WHERE Product = NULL

/*returns no rows, since the NULL value cannot be equal to anyother //value.*/

SELECT * FROM SalesHistory WHERE Product IS NOT NULL

SELECT ISNULL(Product, 'Missing') FROM SalesHistory

/*For every null value in Product it is replaced by ‘Missing’*/

SELECT Product, COUNT(*) AS ProductCount, SUM(SalePrice) AS ProductSales

FROM SalesHistory

GROUP BY Product

Any time a NULL value is present in a column that an aggregation is performed on, such as a COUNT, AVG, or SUM function, those values will be ignored and therefore not included in the functions result.

SELECT COALESCE(Product, ProductDescription, Product, 'Missing Info')
FROM SalesHistory

1 2	SELECT COALESCE(Product, ProductDescription, Product, 'Missing Info') FROM SalesHistory

The COALESCE function returns the first non-NULL value in the field list that it accepts. The COALESCE function is very useful in a join operation or in queries where you are comparing a single value from a list of possible fields to a single value.

Sometimes Interesting, Always Useful, Mostly Technical

Category Archives: TSQL

MS SQL TSQL PadLeft

SQL Server Monitor Changes to SCHEMA

SQL Handling Null Values

Information Technology, Life, Interesting Stumbles, Servers, Configuration, Topology, Security, Best Practices, Developing, Fire Fighting, Problem solving, Visual Studio 2005 – 2015 .NET 1.1 – 4.5, jQuery, JSON & much much more