DDL Triggers with practical example

DDL triggers let you watch what users do, but instead of watching what they do to data, you can watch what they do to the system. They let us protect and monitor changes to the server or database structure by firing when a user executes any DDL statement. The list of DDL statements you can monitor is quite long. (There are server-level events, such as creating and altering logins, as well as for the database, including creating and modifying tables, indexes, views, procedures, and so on.

For a full list, check SQL Server 2008 Books Online in the “DDL Events Groups” topic.)

DDL triggers are of no value in protecting data values, because they don’t fire for operations

where data is changed or manipulated. They are, however, good for monitoring and preventing

changes to the system, even by users who have the rights to do so. For example, consider the all too-frequent case where the manager of the IT group has system administration powers on the database, though he can barely spell SQL (if this power wasn’t granted, it would seem like a slight to the abilities/power of this manager). Now, let’s assume that this manager is just pointing and clicking his way around the UI, and one click is to the wrong place and all of a sudden, your customer table joins the choir invisible. Now you have to restore from a backup and waste a day cleaning up the mess, while trying to figure out who dropped the table. (OK, so if you have constraints on your table, you can’t actually drop it that easily. And yes, to be honest most every DBA has dropped some object in a production database. That ends the honest part of this section.

1) Preventing a DDL Action:

With a simple DDL trigger, we can prevent the accidental drop of the table by trapping for the event and stopping it or we can log who it was that dropped the table. In the first example, I will create a DDL trigger that will prevent any alterations to the schema without the user going in and manually disabling this trigger. It is a great safeguard to secure your objects from accidental change.

CREATE TRIGGER tr_server$allTableDDL_prevent –note, not a schema owned object

ON DATABASE

AFTER CREATE_TABLE, DROP_TABLE, ALTER_TABLE

AS

BEGIN

BEGIN TRY –note the following line will not wrap

RAISERROR (‘The trigger: tr_server$allTableDDL_prevent must be disabled before making any Modifications to Database’,16,1)

END TRY

BEGIN CATCH

DECLARE @ERROR_MESSAGE varchar(8000)

SET @ERROR_MESSAGE = ERROR_MESSAGE()

RAISERROR (@ERROR_MESSAGE,16,1)

END CATCH

END

Now we try to create a simple table:

CREATE TABLE dbo.test –dbo for simplicity of example

(

testId int identity CONSTRAINT PKtest PRIMARY KEY

)

We get the following error message:

Msg 50000, Level 16, State 1, Procedure tr_server$allTableDDL_prevent, Line 19

The trigger: tr_server$allTableDDL_prevent must be disabled before making any

Modifications to Database

1) Recording a DDL Action:

The second case, and just as useful, is to log DDL that is executed in a database so you can see what has been done. Although stopping DDL is something I usually do in a production database, logging changes is something I often do in a development environment. Not that logging is never useful in a production environment; it is just that it shouldn’t be as necessary, since tables in the production system should be very stable and changed only in an organized manner, not just randomly by a user or a DBA. Sometimes I will use DDL logging to catch things that are routine such as index changes so I will know to watch the new indexes especially closely for a while to see whether they are valuable.

Let’s look at creating a trigger similar to the one created . The difference is that this time we will have the trigger monitor DDL changes, not prevent them. First, let’s drop the trigger created previously:

DROP TRIGGER tr_server$allTableDDL_prevent ON DATABASE

Now, we create a table to contain the history of changes to our table:

CREATE TABLE dbo.TableChangeLog

(

TableChangeLogId int identity

CONSTRAINT pkTableChangeLog PRIMARY KEY (TableChangeLogId),

ChangeTime datetime,

UserName sysname,

Ddl varchar(max)–so we can get as much of the batch as possible

)

And we build another trigger to fire when a user creates, alters, or drops a table:

–not a schema bound object

CREATE TRIGGER tr_server$allTableDDL

ON DATABASE

AFTER CREATE_TABLE, DROP_TABLE, ALTER_TABLE

AS

BEGIN

SET NOCOUNT ON –to avoid the rowcount messages

SET ROWCOUNT 0 –in case the client has modified the rowcount

BEGIN TRY

–we get our data from the EVENT_INSTANCE XML stream

INSERT INTO dbo.TableChangeLog (ChangeTime, userName, Ddl)

SELECT getdate(), user,

EVENTDATA().value(‘(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]’,

‘nvarchar(max)’)

END TRY

BEGIN CATCH

DECLARE @ERROR_MESSAGE varchar(8000)

SET @ERROR_MESSAGE = ERROR_MESSAGE()

RAISERROR (@ERROR_MESSAGE,16,1)

END CATCH

END

Now we run this to create the dbo.test table:

CREATE TABLE dbo.test

(

id int

)

GO

DROP TABLE dbo.test

We check out the TableChangeLog data to see what has changed:

SELECT * FROM dbo.TableChangeLog

This shows us our commands:

TableChangeLogId

ChangeTime

UserName

Ddl

1

57:24.0

dbo

CREATE TABLE dbo.test ( id int )

2

57:24.0

dbo

DROP TABLE dbo.test

Now we can see what users have been up to in the database without them having to do anything special to cause it to happen (or without them even knowing, either).

Advertisements

Stored Procedure Error Tracking in MS SQL Server 2008 Best Practices

You can easily trace all errors of Stored Procedures in MS SQL Server. To do this, first create a table called Error.

CREATE TABLE [dbo].[Error](
    [iAutoID] [int] IDENTITY(1,1) NOT NULL,
    [dErrorDate] [datetime] NOT NULL,
    [vErrorNumber] [nvarchar](max) NULL,
    [vErrorSeverity] [nvarchar](max) NULL,
    [vErrorState] [nvarchar](max) NULL,
    [vErrorProcedure] [nvarchar](max) NULL,
    [vErrorLine] [nvarchar](max) NULL,
    [vErrorMessage] [nvarchar](max) NULL
) ON [SECONDARY]

GO

ALTER TABLE [dbo].[Error] ADD  CONSTRAINT [DF_Error_dErrorDate]  DEFAULT (getdate()) FOR [dErrorDate]
GO
Now create a Stored Procedure by writing the following code:   
CREATE PROCEDURE [dbo].[prcDailyAttendanceGeneration]
WITH
EXECUTE AS CALLER
AS
BEGIN
    SET XACT_ABORT, NOCOUNT ON
    DECLARE @starttrancount INT
    Begin TRY
        SELECT @starttrancount = @@TRANCOUNT
        IF @starttrancount = 0
        BEGIN TRANSACTION

	-- Your own code start
	Execute prcInsertShiftDateWiseInfo
        -- Your own code end

	IF @starttrancount = 0
        COMMIT TRANSACTION
	End Try
    Begin Catch
        -- Test if the transaction is uncommittable.
        IF  XACT_STATE() <> 0 AND @starttrancount = 0
        BEGIN
            ROLLBACK TRANSACTION;
        END;
	-- This is the main tricks to store all the errors in error table.
	insert into Error(vErrorNumber,vErrorSeverity,vErrorState,vErrorProcedure,vErrorLine,vErrorMessage)
        SELECT ERROR_NUMBER(),ERROR_SEVERITY(),ERROR_STATE(),ERROR_PROCEDURE(),ERROR_LINE(),ERROR_MESSAGE()
	End Catch
END
 Now if the procedure gives any error, the error details will be saved into the Error table. 
By this way you can easily get all error details from the Error table and can take the necessary steps.

SQL Server Isolation Levels By Example

Isolation levels is the way locking works between transactions in SQL Server.

SQL Server 2008 supports the following isolation levels

  • Read Uncommitted
  • Read Committed (The default)
  • Repeatable Read
  • Serializable
  • Snapshot

Before I run through each of these in detail you may want to create a new database to run the examples,

run the following script on the new database to create the sample data.

IF (EXISTS (SELECT *
                 FROM INFORMATION_SCHEMA.TABLES
                 WHERE TABLE_SCHEMA = ‘dbo’
                 AND  TABLE_NAME = ‘IsolationTests’))
                
Begin
Drop table IsolationTests
CREATE TABLE IsolationTests
(
    Id INT IDENTITY,
    Col1 INT,
    Col2 INT,
    Col3 INT
)
INSERT INTO IsolationTests(Col1,Col2,Col3)
SELECT 1,2,3
UNION ALL SELECT 1,2,3
UNION ALL SELECT 1,2,3
UNION ALL SELECT 1,2,3
UNION ALL SELECT 1,2,3
UNION ALL SELECT 1,2,3
UNION ALL SELECT 1,2,3
End

image

Fig.Sample Data

Also before we go any further it is important to understand these two terms….

  1. Dirty Reads – This is when you read uncommitted data, when doing this there is no guarantee that data read will ever be committed meaning the data could well be bad.
  2. Phantom Reads – This is when data that you are working with has been changed by another transaction since you first read it in. This means subsequent reads of this data in the same transaction could well be different.
 
1. Read Uncommitted

This is the lowest isolation level there is. Read uncommitted causes no shared locks to be requested which allows you to read data that is currently being modified in other transactions. It also allows other transactions to modify data that you are reading.

As you can probably imagine this can cause some unexpected results in a variety of different ways. For example data returned by the select could be in a half way state if an update was running in another transaction causing some of your rows to come back with the updated values and some not to.

To see read uncommitted in action lets run Query1 in one tab of Management Studio and then quickly run Query2 in another tab before Query1 completes.

Query1

BEGIN TRAN

UPDATE IsolationTests SET Col1 = 2

--Simulate having some intensive processing here with a wait

WAITFOR DELAY '00:00:10'

ROLLBACK

Query2

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

SELECT * FROM IsolationTests

Notice that Query2 will not wait for Query1 to finish, also more importantly Query2 returns dirty data. Remember Query1 rolls back all its changes however Query2 has returned the data anyway, this is because it didn’t wait for all the other transactions with exclusive locks on this data it just returned what was there at the time.

There is a syntactic shortcut for querying data using the read uncommitted isolation level by using the NOLOCK table hint. You could change the above Query2 to look like this and it would do the exact same thing.

SELECT * FROM IsolationTests WITH(NOLOCK)

 

2.Read Committed

This is the default isolation level and means selects will only return committed data. Select statements will issue shared lock requests against data you’re querying this causes you to wait if another transaction already has an exclusive lock on that data. Once you have your shared lock any other transactions trying to modify that data will request an exclusive lock and be made to wait until your Read Committed transaction finishes.

You can see an example of a read transaction waiting for a modify transaction to complete before returning the data by running the following Queries in separate tabs as you did with Read Uncommitted.

Query1

BEGIN TRAN

UPDATE IsolationTests SET Col1 = 2

--Simulate having some intensive processing here with a wait

WAITFOR DELAY '00:00:10'

ROLLBACK

Query2

SELECT * FROM IsolationTests

Notice how Query2 waited for the first transaction to complete before returning and also how the data returned is the data we started off with as Query1 did a rollback. The reason no isolation level was specified is because Read Committed is the default isolation level for SQL Server. If you want to check what isolation level you are running under you can run “DBCC useroptions”. Remember isolation levels are Connection/Transaction specific so different queries on the same database are often run under different isolation levels.

 

3.Repeatable Read

This is similar to Read Committed but with the additional guarantee that if you issue the same select twice in a transaction you will get the same results both times. It does this by holding on to the shared locks it obtains on the records it reads until the end of the transaction, This means any transactions that try to modify these records are forced to wait for the read transaction to complete.

As before run Query1 then while its running run Query2

Query1

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ

BEGIN TRAN

SELECT * FROM IsolationTests

WAITFOR DELAY '00:00:10'

SELECT * FROM IsolationTests

ROLLBACK

Query2

UPDATE IsolationTests SET Col1 = –1

Notice that Query1 returns the same data for both selects even though you ran a query to modify the data before the second select ran. This is because the Update query was forced to wait for Query1 to finish due to the exclusive locks that were opened as you specified Repeatable Read.

If you rerun the above Queries but change Query1 to Read Committed you will notice the two selects return different data and that Query2 does not wait for Query1 to finish.

One last thing to know about Repeatable Read is that the data can change between 2 queries if more records are added. Repeatable Read guarantees records queried by a previous select will not be changed or deleted, it does not stop new records being inserted so it is still very possible to get Phantom Reads at this isolation level.

 

4.Serializable

This isolation level takes Repeatable Read and adds the guarantee that no new data will be added eradicating the chance of getting Phantom Reads. It does this by placing range locks on the queried data. This causes any other transactions trying to modify or insert data touched on by this transaction to wait until it has finished.

You know the drill by now run these queries side by side…

Query1

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

BEGIN TRAN

SELECT * FROM IsolationTests

WAITFOR DELAY '00:00:10'

SELECT * FROM IsolationTests

ROLLBACK

Query2

INSERT INTO IsolationTests(Col1,Col2,Col3)

VALUES (100,100,100)

You’ll see that the insert in Query2 waits for Query1 to complete before it runs eradicating the chance of a phantom read. If you change the isolation level in Query1 to repeatable read, you’ll see the insert no longer gets blocked and the two select statements in Query1 return a different amount of rows.

 

5.  Snapshot

This provides the same guarantees as serializable. So what’s the difference? Well it’s more in the way it works, using snapshot doesn’t block other queries from inserting or updating the data touched by the snapshot transaction. Instead row versioning is used so when data is changed the old version is kept in tempdb so existing transactions will see the version without the change. When all transactions that started before the changes are complete the previous row version is removed from tempdb. This means that even if another transaction has made changes you will always get the same results as you did the first time in that transaction.

So on the plus side your not blocking anyone else from modifying the data whilst you run your transaction but…. You’re using extra resources on the SQL Server to hold multiple versions of your changes.

To use the snapshot isolation level you need to enable it on the database by running the following command,

ALTER DATABASE IsolationTests

SET ALLOW_SNAPSHOT_ISOLATION ON

If you rerun the examples from serializable but change the isolation level to snapshot you will notice that you still get the same data returned but Query2 no longer waits for Query1 to complete.

In a nutshell,You can see how the higher the level you use the less concurrency you are offering and the more blocking you bring to the table. You should always try to use the lowest isolation level you can which is usually read committed.

Loading XML Using SSIS

SQL Server Integration Services can read XML files, that’s known by every BI developer.

Basic Example

his first example is a really simple XML file containing a list of colors with their corresponding RGB code.

<colors>
  <color RGB=”FF0000″>Red</color>
  <color RGB=”00FF00″>Green</color>
  <color RGB=”0000FF”>Blue</color>
  <color RGB=”FFFFFF”>White</color>
  <color RGB=”000000″>Black</color>
</colors>

Let’s import this into a database.  Open up the BIDS, create an SSIS project and throw a Data Flow Task into the package and open it up.

The component that we’re now most interested in is the XML Source, one of the components in the Data Flow Sources category in the Toolbox.

image

Add one of those to your Data Flow and double-click it to open up the XML Source Editor.

The Data Access Mode should be set to XML file location, which is the default setting.  The other options are XML file from variable – useful if you’ve got the file path and name of the XML file in a variable

As XML Location, select the .xml file.  Our XML sample does not have an inline schema, so we can’t use that checkbox.  And we can’t click the OK button either, it’s grayed out.  The source component really expects a description of the XML structure before the editor can be closed.

The bottom of the screen even shows a warning with the following message:

XML Schema (XSD) is not specified. Select an existing XSD or click Generate XSD to create an XSD from the XML file.

So, what are you waiting for,  Click the Generate XSD button to let the XML Source Editor generate the XSD schema for us.  Real easy, right?

Remember where you save the file, and when it’s generated, select the .xsd file in the XSD location textbox.  As you can see, the OK button will become available.  But don’t click it just yet.

Here’s what the XML Source Editor now looks like:

image

 

Let’s now move on to the second page of the XML Source Editor, called Columns.  When you open it, you’ll receive the following popup with a couple of warnings:

Warning gets displayed when opening the Columns page

The editor is letting us know that the columns that are being generated do not have a maximum length specified.  So it’s setting them to Unicode (DT_WSTR) with a length of 255.  Click the OK button to get rid of that message and to be able to see the generated columns.

image

As you can see, our only attribute – RGB, is nicely put in a column with the same name.  The value of each <color> node however is not put in a column called Color.  By default, this value is put into a column called “text”.  Which is a weird name for a column in an SSIS data flow if you ask me.  The good thing is that you can just rename it by changing the Output Column value.

Let’s test this out.  My favorite way is to add a Multicast component to the Data Flow, then add a Data Viewer on the connector (right-click the green arrow, select Data Viewers, click Add > OK > OK).  Now execute the package to get this result:

image

Mission accomplished, we’ve retrieved data from a very basic XML file!