In previous posts I provided VB.NET code to convert names to proper case and provide a “NOT LIKE” expression in reporting services. I recently had a request on how this code could be made re-usable as an assembly in reporting services.

The steps for creating an assembly are pretty straight forward. From Visual Studio create a Visual Basic Class library:

I have renamed the class to ProperCaseClass and included the two functions:


Public Class ProperCaseClass
    Public Shared Function propercase(ByVal str As String) As String
        Return StrConv(str, vbProperCase)
    End Function
    Public Shared Function NotLike(ByVal val As String, ByVal filter As String) As Boolean
        If val.Contains(filter) Then
            Return False
        Else
            Return True
        End If
    End Function
End Class

Once complete then build the project:

After building the project copy the ProperCaseClass.dll to the C:\Program Files\Microsoft Visual Studio 9.0\Common7\IDE\PrivateAssemblies folder, this enables you to use the assemblies within BIDS.

To utilize the assembly open up BIDS and create a new Report Server Project:

After creating the project right click the Reports folder in the solution explorer and select Add>New Item

Select Report and accept the default name, Report1.rdl:

In order to utilize the assembly you must add a reference to it from the Report>Report Properties menu:

From the Report Properties choose References and click the Add button and click the ellipse button to the right of the first row, the button with the “…”:

From the Add Reference window select the Browse tab and navigate to the C:\Program Files\Microsoft Visual Studio 9.0\Common7\IDE\PrivateAssemblies folder and select the ProperCaseClass.dll and click Ok:

Back in the Report Properties window click the Add button to Add or remove classes and in the Class Name column type ProperCaseCass.ProperCaseClass and in the Instance name type propercase:

Since all reports must have a data source and data set create a data source to an instance of SQL, in this case I am using the localhost default instance and AdventureWorks2008:

In the data set I am using the query SELECT GETDATE() since I will be using string literals in my expressions to display using the reference the data set is only necessary to preview the report:

Drag and drop a table on the design surface and in the second data column right click and choose Expression:

In the expression type =ProperCaseClass.ProperCaseClass.propercase("UPPER") which will call the propercase function:

In the details row in the third column right click and again choose Expression. In the Expression builder type =ProperCaseClass.ProperCaseClass.NotLike("UPPER", "UPPR") which will call the NotLike function:

Click OK and preview the report:

The second column in the details has now put “UPPER” into proper case and the third column shows “True” indicating that the values "UPPER" and "UPPR" are not like.

The above process has made the assembly available for BIDS, but not to the report server. In order to assure that the report will render without error the ProperCaseClass.dll must be copied to the C:\Program Files\Microsoft SQL Server\MSRS10.MSSQLSERVER\Reporting Services\ReportServer\bin, or the appropriate folder based on the version of SSRS.

Once the file has been copied then you need to modify the rssrvpolicy.config file to apply the appropriate permissions to the assembly. The rssrvpolicy.config file is located in the C:\Program Files\Microsoft SQL Server\MSRS10.MSSQLSERVER\Reporting Services\ReportServer folder. MSDN goes more in depth into code access security in reporting services here. In this case I added the following code group in the rssrvpolicy.config file:


<CodeGroup
   class="FirstMatchCodeGroup"
   version="1"
   PermissionSetName="FullTrust"
   Name="ProperCaseClass"
   Description="A special code group for my custom assembly.">
   <IMembershipCondition
   class="UrlMembershipCondition"
   version="1"
   Url="C:\Program Files\Microsoft SQL Server\MSRS10_50.SQL08R2\Reporting Services\ReportServer\bin\ProperCaseClass.dll"/>
</CodeGroup>

Once the code group is added you can deploy the report to the report server.

The schedule for SQL Saturday #74 in Jacksonville is posted and I will be presenting Implementing auditing in SQL Server.  I am very excited to be attending this year and hope to see many of you there.

I have just completed my reservations for the SUGA Educational Conference in Las Vegas May 16th – 20th.  I will be presenting 4 sessions,

• Creating a reporting solution
• Introduction to SSRS
• What’s new in SQL Server 11
• Implementing Auditing in SQL Server. 

I am very excited about the conference and the scheduling worked out PERFECTLY as I will be teaching SharePoint 2010 administration the following week at Datanamics in Henderson.
I am fired up to spend most of May in Vegas and look forward to seeing many of you at the conference or Datanamics.

The inaugural SQL Rally is being held in Orlando May 11th through the 13th.  The event sessions are focused on four subject tracks and each track also has a pre-conference seminar available: 

• BI
• DBA
• Developer
• Professional Development

SQL Rally provides both an affordable high-quality training option and awesome justification to visit  Orlando, Fl. 

For Florida residents, like myself, who work with SQL Server in one way or another, this is an incredible opportunity within driving distance.  For any SQL professional living outside of Florida this is an incredible opportunity and great justification to visit the Sunshine State.

Don’t fool yourself and think that this is just a local event.  My friend Mark Broadbent (blog|twitter) will be travelling from the UK to attend so distance isn’t an excuse.

Hope to see you there.

I was recently presented with the task of examining a database, from historic backups, to determine transactions applied over the past 6 years.  Unfortunately, the only background/source data available was a few weeks of full backups and, to make this more of a challenge, many conclusions had already been reached. The most notable conclusion focused on alleged malicious deletions.

After some discussion I found that the primary basis for concluding that deletions had been made were the “gaps” in the IDENTITY seed (I am hoping to get this submitted to ESPN’s “C’MON MAN!” for next season) 

I recently had to create a number of views which forced me to brush up on “view” internals, I’m more of a stored proc. kind of guy.  I had forgotten many of the rules and nuisance's involved in creating and maintaining views so I’ve decided to record them in this post for my reference.
I will use the below two tables for demonstration:
USE tempdb; GO CREATE TABLE People( PersonID   INT PRIMARY KEY, FirstName  NVARCHAR(50), MiddleName NVARCHAR(50), LastName   NVARCHAR(50) NOT NULL
); GO CREATE TABLE Credit( CreditID   INT IDENTITY PRIMARY KEY, PersonID   INT  REFERENCES People(PersonID), Limit      MONEY ); GO INSERT People SELECT DISTINCT BusinessEntityID,
      FirstName,
      MiddleName,
      LastName FROM AdventureWorks2008.Person.Person p JOIN AdventureWorks2008.Sales.SalesOrderHeader s ON p.BusinessEntityID = s.CustomerID; GO INSERT Credit SELECT DISTINCT p.BusinessEntityID,
      MAX(CEILING(TotalDue)) FROM AdventureWorks2008.Person.Person p JOIN AdventureWorks2008.Sales.SalesOrderHeader s ON p.BusinessEntityID = s.CustomerID GROUP BY p.BusinessEntityID;
First rule to point out is the column limitation involved with views.  A view is limited to ONLY 1,024 columns.  In many cases a collection of 1,024 columns would be considered a database, but for those who love to push the upper limit have at it as I am not going to test this, not that I’ll admit.
Another key point to keep in mind is that when creating a view all derived columns must have column names supplied, if the column name is not specified and the column is not derived the column name is the same as the name in the SELECT statement.  For example, the below error occurs when attempting to create a view that uses a derived column and does not supply a column alias:

Column names can be added in the query body, in this case after the derived column concatenating the three name columns, “AS FullName”, or the column names can be specified immediately after the view name, these must be in the same ordinal position as the columns defined in the SELECT statement defined in the view:

Some quick and easy rules to consider:
A view CAN NOT:

• Contain the INTO keyword
• The OPTION clause
• COMPUTE or COMPUTE BY clauses, these are deprecated anyway so STAY AWAY.
• Reference a table variable or temporary table
• Exceed nesting level of 32
• The ORDER BY clause can only be used if the TOP clause is included

I can’t help but mention the rule of using ORDER BY in a view.  Prior to SQL 2005 the “work around” to using ORDER BY in a view was to use TOP as this:

Transactions Using Views

Views can server as a means to INSERT, UPDATE, OR DELETE to one of the underlying base tables.  There are obviously some limitations with this functionality:

1. The INSERT, UPDATE, or DELETE statement can only reference columns from one base table. CREATE VIEW Updateable AS
SELECT p.PersonID,
      FirstName,
      MiddleName,
      LastName,
      FirstName + ' ' +
      ISNULL(MiddleName, '') +
      ' ' + LastName AS FullName,
      Limit FROM People p JOIN Credit c ON p.PersonID = c.PersonID; --This fails as the UPDATE references
--columns from two tables, People(LastName) and Credit(Limit) UPDATE Updateable SET LastName = 'Dye',
    Limit = 1000000 WHERE PersonID = 11000
--This UPDATE statement succeeds since
--only the People(LastName) is referenced UPDATE Updateable SET LastName = 'Dye' WHERE PersonID = 11000
2. Columns being modified must directly reference the underlying table, the column can not be derived, such as the FullName column in the above view
3. Columns being modified are not affected by GROUP BY, HAVING, or DISTINCT
4. The TOP clause is not used in the SELECT statement along with the WITH CHECK OPTION clause
5. The INSERT, UPDATE, or DELETE can not violate any constraint on the affected base table, NULL, primary key, foreign key, etc.

To handle data modifications of a view INSTEAD OF triggers can be placed on a view to handle INSERT, UPDATE, and/or DELETE. 

Indexed Views

1. The column(s) referenced in the index must be unique
2. A view that contains an OUTER JOIN can not be indexed
   
CREATE VIEW IndexedViewOuter WITH SCHEMABINDING AS
SELECT p.PersonID,
      FirstName,
      MiddleName,
      LastName,
      FirstName + ' ' +
      ISNULL(MiddleName, '') +
      ' ' + LastName AS FullName,
      Limit FROM dbo.People p RIGHT OUTER JOIN dbo.Credit c ON p.PersonID = c.PersonID --This will fails as the view 
--uses an outer join, as well as the referenced column are not unique CREATE UNIQUE CLUSTERED INDEX ix_indexedview ON IndexedViewOuter(LastName, FirstName, MiddleName)
3. The query can not use "SELECT *"

A detailed technical article regarding indexed views can be found here.
If the WITH SCHEMABINDING option is used then the ability to create a view using “SELECT * ” is prohibited, which is not necessarily a bad thing.  It is poor practice to use a SELECT all in a view as this can cause issues if the table structure should change:
--Create a table and insert values into it CREATE TABLE tt (
    test1 INT PRIMARY KEY,
    test2 INT
    ) GO INSERT INTO tt (test1, test2) VALUES (1,2) GO --Create the view select all columns with * CREATE VIEW vw_tt AS
SELECT *,
      GETDATE() AS 'My Date' FROM tt
GO --Select * from the view SELECT * FROM vw_tt
GO
--Alter the table adding another column ALTER TABLE tt ADD test3 INT GO --Select from the view the GETDATE() is NULL now SELECT * FROM vw_tt
Since the underlying table has changed and the definition of the view used SELECT * along with a derived column of GETDATE() the result is that the derived column is returning the value of the newly added column.  This can be remedied using the system stored procedure sp_refreshview:
--Refresh the view sp_REFRESHVIEW vw_tt --Select from the view after it has been refreshed
--All columns appear now SELECT * FROM vw_tt
MSDN’s online documentation for views can be found here and was the primary source for this post.

I am truly sorry that I am just now getting to posting these examples.

USE AdventureWorks2008R2
--Using variables wiht control flow in t-sql
DECLARE @var INT
SET @var = (SELECT COUNT (*) FROM Person.Person)
WHILE @var > 0
  IF (SELECT @var-1) >= 1000
BEGIN
SELECT @var - 1
SELECT FirstName,
      MiddleName,
      LastName
FROM Person.Person
WHERE BusinessEntityID = @var
SET @var = @var - 1
END;
GO
SELECT OrderQty + 10,
      OrderQty
FROM Sales.SalesOrderDetail;
GO
--Variables have a limimted scope to the batch
--GO ends the scope
DECLARE @var INT = 10
SELECT @var;
SELECT @var;
GO
--@var is out of scope and an error is raiased
SELECT @var;
GO
--Using a common table expression
WITH CTE
AS
(
SELECT *
FROM Person.Person
)
SELECT FirstName
FROM CTE;
GO
--Structured error handling in t-sql
BEGIN TRY
    -- Generate divide-by-zero error.
    SELECT 1/0;
END TRY
BEGIN CATCH
   PRINT 'Cant do that'
   SELECT ERROR_NUMBER(),
         ERROR_MESSAGE()
END CATCH;

--Unhandled error that occurs during binding
BEGIN TRY
    -- Generate divide-by-zero error.
    SELECT NotHere
   FROM NoSuchThing
END TRY
BEGIN CATCH
   PRINT 'Cant do that'
   SELECT ERROR_NUMBER(),
         ERROR_MESSAGE()
END CATCH;

--Order by clause ordering by the LastName then FirstName
SELECT FirstName,
      LastName
FROM Person.Person
ORDER BY LastName,
        FirstName;
GO

--WHERE using =
SELECT FirstName,
      MiddleName,
      LastName
FROM Person.Person
WHERE MiddleName = ''

--LIKE operator last name beginning with Ab and then any other letters
SELECT *
FROM Person.Person
WHERE LastName LIKE 'Ab%';
GO

--LIKE Operator LastName beginning with Ab and the next letter CAN NOT BE e
SELECT FirstName,
      LastName,
      MiddleName
FROM Person.Person
WHERE LastName LIKE 'Ab[^e]%';

--Using OR and AND in the WHERE clause
--FirstName Like and MiddleName IS NULL are evaluated together 4 rows
SELECT FirstName,
      LastName,
      MiddleName
FROM Person.Person
WHERE LastName LIKE 'Ab%'
AND (FirstName LIKE 'K%'
OR MiddleName IS NULL);

--Using OR and AND in the WHERE clause
--FirstName Like and MiddleName IS NULL are evaluated separately 8,500 rows
SELECT FirstName,
      LastName,
      MiddleName
FROM Person.Person
WHERE LastName LIKE 'Ab%'
AND FirstName LIKE 'K%'
OR MiddleName IS NULL

--USING BETWEEN and the >= and <= equivalent
SELECT *
FROM Sales.SalesOrderDetail
WHERE UnitPrice BETWEEN 1 AND 10;

SELECT *
FROM Sales.SalesOrderDetail
WHERE UnitPrice >= 1
AND UnitPrice <= 10;

--Using BETWEEN with character data types
SELECT FirstName,
      LastName
FROM Person.Person
WHERE LastName BETWEEN 'A' AND 'N';

--Using IN with a sub-query
SELECT *
FROM Person.Address
WHERE City IN (SELECT City FROM Person.Address WHERE City LIKE '%a%');

--NULL values used in expression results in NULL
SELECT 8 + 1 + NULL

--Using ISNULL to replace a NULL value with a specified value
SELECT FirstName,
      ISNULL(MiddleName, 'N/A'),
      LastName
FROM Person.Person

--Using ISNULL in an expression
SELECT 8 + 1 + ISNULL(NULL, 2);

--Using COALESCE in an expression to handle NULL values
SELECT 8 + 1 + COALESCE(NULL, NULL, NULL, 4);

--DISTINCT is applied to the ENTIRE row, ot just the column name immediately following DISTINCT
SELECT DISTINCT LastName,
      FirstName,
      BusinessEntityID
FROM Person.Person;

--Using String literals
SELECT 'Hello my name is ' + FirstName + ' '+ LastName lName,
      'This will NEVER CHANGE' AS never
FROM Person.Person;

--Using ISNULL with string literals
SELECT FirstName + ' '+ ISNULL(SUBSTRING(MiddleName, 1,1) + '.',  ' ') + ' ' + LastName lName,
      'This will NEVER CHANGE' AS never
FROM Person.Person;

--Using expressions
SELECT OrderQty * UnitPrice - UnitPriceDiscount AS SalesPrice
FROM Sales.SalesOrderDetail
WHERE OrderQty > 10
ORDER BY OrderQty

--COUNT aggregation showing the total count of rows
SELECT COUNT(*)
FROM HumanResourc

Voting has opened for the category of enterprise database administration and deployment for SQL Rally that will be held in Orlando May 11th through the 13th.  The session outlines can be found here and you can vote for the presentations you’d like to attend here.  I have submitted a session for “Monitoring/Auditing” that focuses on SQL Audit. 

If you are planning on attending then take the time to vote for sessions that interest you for the inaugural PASS SQL Rally!

I am very sorry that I am just now posting the code samples I used at last weeks SQL Saturday in Tampa.

I am finally getting caught up and will be home for an entire week so there are no excuses for posting!!

Thanks for all who attended and please feel free to contact me with any questions.

--************************************************************
--Create server side trace using t-sql demo
--************************************************************

-- Create a Queue
DECLARE @rc                INT
DECLARE @TraceID        INT
DECLARE @maxfilesize    BIGINT
SET        @maxfilesize = 50


EXEC @rc = sp_trace_create @TraceID OUTPUT, 2, N'C:\Audit', @maxfilesize, NULL, 100 
IF (@rc != 0) GOTO error

-- Set the events
DECLARE @on BIT
SET @on = 1
EXEC sp_trace_setevent @TraceID, 14, 1, @on
EXEC sp_trace_setevent @TraceID, 14, 9, @on
EXEC sp_trace_setevent @TraceID, 14, 6, @on
EXEC sp_trace_setevent @TraceID, 14, 10, @on
EXEC sp_trace_setevent @TraceID, 14, 14, @on
EXEC sp_trace_setevent @TraceID, 14, 11, @on
EXEC sp_trace_setevent @TraceID, 14, 12, @on
EXEC sp_trace_setevent @TraceID, 15, 15, @on
EXEC sp_trace_setevent @TraceID, 15, 16, @on
EXEC sp_trace_setevent @TraceID, 15, 9, @on
EXEC sp_trace_setevent @TraceID, 15, 13, @on
EXEC sp_trace_setevent @TraceID, 15, 17, @on
EXEC sp_trace_setevent @TraceID, 15, 6, @on
EXEC sp_trace_setevent @TraceID, 15, 10, @on
EXEC sp_trace_setevent @TraceID, 15, 14, @on
EXEC sp_trace_setevent @TraceID, 15, 18, @on
EXEC sp_trace_setevent @TraceID, 15, 11, @on
EXEC sp_trace_setevent @TraceID, 15, 12, @on
EXEC sp_trace_setevent @TraceID, 17, 12, @on
EXEC sp_trace_setevent @TraceID, 17, 1, @on
EXEC sp_trace_setevent @TraceID, 17, 9, @on
EXEC sp_trace_setevent @TraceID, 17, 6, @on
EXEC sp_trace_setevent @TraceID, 17, 10, @on
EXEC sp_trace_setevent @TraceID, 17, 14, @on
EXEC sp_trace_setevent @TraceID, 17, 11, @on
EXEC sp_trace_setevent @TraceID, 10, 15, @on
EXEC sp_trace_setevent @TraceID, 10, 16, @on
EXEC sp_trace_setevent @TraceID, 10, 9, @on
EXEC sp_trace_setevent @TraceID, 10, 17, @on
EXEC sp_trace_setevent @TraceID, 10, 2, @on
EXEC sp_trace_setevent @TraceID, 10, 10, @on
EXEC sp_trace_setevent @TraceID, 10, 18, @on
EXEC sp_trace_setevent @TraceID, 10, 11, @on
EXEC sp_trace_setevent @TraceID, 10, 12, @on
EXEC sp_trace_setevent @TraceID, 10, 13, @on
EXEC sp_trace_setevent @TraceID, 10, 6, @on
EXEC sp_trace_setevent @TraceID, 10, 14, @on
EXEC sp_trace_setevent @TraceID, 12, 15, @on
EXEC sp_trace_setevent @TraceID, 12, 16, @on
EXEC sp_trace_setevent @TraceID, 12, 1, @on
EXEC sp_trace_setevent @TraceID, 12, 9, @on
EXEC sp_trace_setevent @TraceID, 12, 17, @on
EXEC sp_trace_setevent @TraceID, 12, 6, @on
EXEC sp_trace_setevent @TraceID, 12, 10, @on
EXEC sp_trace_setevent @TraceID, 12, 14, @on
EXEC sp_trace_setevent @TraceID, 12, 18, @on
EXEC sp_trace_setevent @TraceID, 12, 11, @on
EXEC sp_trace_setevent @TraceID, 12, 12, @on
EXEC sp_trace_setevent @TraceID, 12, 13, @on
EXEC sp_trace_setevent @TraceID, 13, 12, @on
EXEC sp_trace_setevent @TraceID, 13, 1, @on
EXEC sp_trace_setevent @TraceID, 13, 9, @on
EXEC sp_trace_setevent @TraceID, 13, 6, @on
EXEC sp_trace_setevent @TraceID, 13, 10, @on
EXEC sp_trace_setevent @TraceID, 13, 14, @on
EXEC sp_trace_setevent @TraceID, 13, 11, @on


-- Set the Filters
DECLARE @intfilter INT
DECLARE @bigintfilter BIGINT
DECLARE @databaseid    INT

--Set the databaseid to filter only on the fuel database
SELECT @databaseid =  DB_ID(N'AdventureWorks2008R2')

EXEC sp_trace_SETfilter @TraceID, 3, 0, 0, @databaseid
-- Set the trace status to start
EXEC sp_trace_SETstatus @TraceID, 1

-- Display trace id for future references
SELECT TraceID=@TraceID
GOTO finish

ERROR: 
SELECT ErrorCode=@rc

FINISH: 
GO


/*
-- This commented code can be used to stop and delete the trace
-- Stop and remove the trace when done
EXEC sp_trace_setstatus 2, 0
EXEC sp_trace_setstatus @traceid = 2 , @status = 2
*/ 


--************************************************************
--Create DDL audit
--************************************************************

--Create table to hold DDL events
USE AdventureWorks
GO
CREATE TABLE AuditLog
(ID           INT PRIMARY KEY IDENTITY(1,1),
Command    NVARCHAR(1000),
PostTime   NVARCHAR(24),
HostName   NVARCHAR(100),
LoginName  NVARCHAR(100)
)
GO

-- create DDL trigger
CREATE TRIGGER Audit
ON DATABASE
FOR DDL_DATABASE_LEVEL_EVENTS
AS
DECLARE @data XML
DECLARE @cmd NVARCHAR(1000)
DECLARE @posttime NVARCHAR(24)
DECLARE @spid NVARCHAR(6)
DECLARE @loginname NVARCHAR(100)
DECLARE @hostname NVARCHAR(100)

SET @data = EVENTDATA()
SET @cmd = @data.value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]', 
'NVARCHAR(1000)')
SET @cmd = LTRIM(RTRIM(REPLACE(@cmd, '
', '')))
SET @posttime = @data.value('(/EVENT_INSTANCE/PostTime)[1]',
'NVARCHAR(24)')
SET @spid = @data.value('(/EVENT_INSTANCE/SPID)[1]', 'nvarchar(6)')
SET @loginname = @data.value('(/EVENT_INSTANCE/LoginName)[1]',
'NVARCHAR(100)')
SET @hostname = HOST_NAME()

INSERT INTO dbo.AuditLog(Command, PostTime,HostName,LoginName)
VALUES(@cmd, @posttime, @hostname, @loginname)
SELECT @data

GO

-- perform DDL operations
UPDATE STATISTICS Production.Product
GO
CREATE TABLE dbo.Test(col INT)
GO
DROP TABLE dbo.Test
GO

-- View log table
SELECT *
FROM dbo.AuditLog
GO

DELETE [AuditLog]
WHERE ID = 4
-- clean up
DROP TRIGGER Audit
ON DATABASE

DROP TABLE dbo.AuditLog
GO

--************************************************************
--SQL Audit Demo
--************************************************************

USE master;
GO 

--Create a server audit for the application.  Since the QUEUE_DELAY is not specified
--The default is 1000ms or one minute and if failure occurs the SQL serveice will 
--continue to run as the default parameter for ON_FAILURE is continue
CREATE SERVER AUDIT Log_Audit
TO APPLICATION_LOG;
GO

--Create a server audit for a file.  QUEUE_DELAY 0 so the audit is synchronous
--ON_FAILURE is set to fail so if a failure occurs the sql service will shutdown
CREATE SERVER AUDIT File_Audit
TO FILE(FILEPATH='C:\AUDIT\')
WITH(ON_FAILURE=SHUTDOWN,
     QUEUE_DELAY = 0);
GO

--Create a server audit for the application that will be used to record audit level events.  
--Since the QUEUE_DELAY is not specified the default is 1000ms or one minute 
--and if failure occurs the SQL service will 
--continue to run as the default parameter for ON_FAILURE is continue
--CREATE SERVER AUDIT Audit_Audit
--TO APPLICATION_LOG;
--GO

--The audits are available in the sys.server_audits catalog view
SELECT * 
FROM sys.server_audits;
GO

--The aduits are not enabled/on so not visible in the extended events DMV sys.dm_xe_session_targets view
SELECT *
FROM sys.dm_xe_session_targets

SELECT *
FROM sys.dm_xe_packages

--Enable the server audits
ALTER SERVER AUDIT Log_Audit
WITH (STATE=ON)
GO

ALTER SERVER AUDIT File_Audit
WITH (STATE=ON)
GO

--ALTER SERVER AUDIT Audit_Audit
--WITH (STATE=ON)
--GO

--************************************************************
--View the application log and two entries show the audit states
--were enabled since this is intrinsically audited
--************************************************************

--The audits are now visible in the    extended events DMV    
SELECT *
FROM sys.dm_xe_session_targets dt

--Create a database audit specification for any SELECT or INSERT 
--statement on the Person.Person by a member of dbo and any SELECT 
--statement on HumanResources.Employee by anyone 
USE AdventureWorks2008;
GO 

CREATE DATABASE AUDIT SPECIFICATION Person_Employee 
FOR SERVER AUDIT File_Audit  
ADD(SELECT, INSERT ON Person.Person BY dbo),
ADD(SELECT ON HumanResources.Employee BY public)
WITH (STATE = ON)
GO

--Run SELECT queries against the objects that are being audited
SELECT *
FROM Person.Person;
GO

SELECT *
FROM Person.Person
WHERE LastName = 'Duffy';
GO

SELECT JobTitle,
'David Rocks!!'
FROM HumanResources.Employee;
GO

--Using the sys.fn_get_audit_file function review the audit file
SELECT event_time,
      action_id,
      session_server_principal_name,
statement
FROM sys.fn_get_audit_file('C:\Audit\*', DEFAULT, DEFAULT);
GO

SELECT *
FROM sys.fn_get_audit_file('C:\Audit\*', DEFAULT, DEFAULT);
GO

--Since the server audit is being used by another audit specification 
--this will fail
CREATE DATABASE AUDIT SPECIFICATION Wont_Work_Dude 
FOR SERVER AUDIT File_Audit  
ADD(SELECT, INSERT ON Person.Person BY dbo),
ADD(SELECT ON HumanResources.Employee BY public)
WITH (STATE = ON)
GO

--******************************************************************************************
--Stop the SQL service and delete the C:\Audit folder and attempt restart.  The service
--Will fail to restart since the log can not be written to
--******************************************************************************************

--CREATE SERVER AUDIT SPECIFICATION Audit_Specification
--FOR SERVER AUDIT Audit_Audit
--    ADD (ALTER_AUDIT_GROUP);
--GO

--******************************************************************************************
--Stop the Database audit specification and then the server audit
--Veiw the c:\Audit folder and note that the audit files still exist
--******************************************************************************************

USE AdventureWorks2008;
GO

ALTER DATABASE AUDIT SPECIFICATION Person_Employee
WITH (STATE=OFF);
GO

DROP DATABASE AUDIT SPECIFICATION Person_Employee;
GO

USE master;
GO

ALTER SERVER AUDIT Log_Audit
WITH (STATE=OFF)
GO

ALTER SERVER AUDIT File_Audit
WITH (STATE=OFF)
GO

DROP SERVER AUDIT Log_Audit;
GO

DROP SERVER AUDIT File_Audit;
GO

--Alter the AuditLogging specification turning the state off and drop it 
USE [master]

GO

ALTER SERVER AUDIT SPECIFICATION AuditLogging
WITH (STATE=OFF);
GO

DROP SERVER AUDIT SPECIFICATION AuditLogging;
GO

ALTER SERVER AUDIT Audit_Audit
WITH (STATE=OFF)
GO

DROP SERVER AUDIT Audit_Audit;
GO

T-SQL Tuesday #14 Resolutions

Time for T-SQL Tuesday!

This months event is hosted by Jen McCown who has chosen the topic of “techie resolutions”. 

I hate to make overly general statements, but I can wrap this up in two words….  Catch Up!!!

I have spent much of the past year putting out fires with existing implementations and projects, and as such have not had adequate time to catch up with new technology.  A new project is promising to begin in the near future will utilize SQL 08R2 and will take full advantage of many of its new features and functionality.

SSRS and Mapping

One of the more interesting facets of this project is to utilize SSRS to create reports that will take full advantage of the geo spatial data that we have collected.  I have limited experience working with geo spatial data types in SQL, limited being lab and classroom only, and am excited to begin putting out this data to use in distributed reports. 

PowerPivot

I am very excited about this technology and providing our data in a familiar tool, excel, to the masses.

PowerShell

Working with PowerShell has been on my “Some day” list.  Over the past few years the movement and reliance on this technology is obvious and I can’t afford to procrastinate any longer.

Denali

During the PASS conference I had the time to install and work with SQL 11, Denali.  Unfortunately I was dragged back into the cruel reality of work upon my return home and have had little time to explore since.  I am committed to tinkering with the CTP before it blossoms into a release to market and I am once again struggling to catch up.

I have so many more goals, Ohio State winning a national championship, the TB Bucs making it back to the playoffs, the Magic topping the Southeastern and surpassing the Heat, but I’ll save these for later.