Store Procedure for Audit Trial in SQL

Here the code for create procedure to deploy audit trial in sql :

/****** Object: StoredProcedure [dbo].[GenerateAudittrail] Script Date: 01/25/2010 13:30:29 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GenerateAudittrail]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[GenerateAudittrail]
GO

CREATE PROCEDURE [dbo].[GenerateAudittrail]
@TableName varchar(128),
@Owner varchar(128) = 'dbo',
@AuditNameExtention varchar(128) = '_shadow',
@DropAuditTable bit = 0
AS
BEGIN

-- Check if table exists
IF not exists (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[' + @Owner + '].[' + @TableName + ']') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
PRINT 'ERROR: Table does not exist'
RETURN
END

-- Check @AuditNameExtention
IF @AuditNameExtention is null
BEGIN
PRINT 'ERROR: @AuditNameExtention cannot be null'
RETURN
END

-- Drop audit table if it exists and drop should be forced
IF (exists (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[' + @Owner + '].[' + @TableName + @AuditNameExtention + ']') and OBJECTPROPERTY(id, N'IsUserTable') = 1) and @DropAuditTable = 1)
BEGIN
PRINT 'Dropping audit table [' + @Owner + '].[' + @TableName + @AuditNameExtention + ']'
EXEC ('drop table ' + @TableName + @AuditNameExtention)
END

-- Declare cursor to loop over columns
DECLARE TableColumns CURSOR Read_Only
FOR SELECT b.name, c.name as TypeName, b.length, b.isnullable, b.collation, b.xprec, b.xscale
FROM sysobjects a
inner join syscolumns b on a.id = b.id
inner join systypes c on b.xtype = c.xtype and c.name <> 'sysname'
WHERE a.id = object_id(N'[' + @Owner + '].[' + @TableName + ']')
and OBJECTPROPERTY(a.id, N'IsUserTable') = 1
ORDER BY b.colId

OPEN TableColumns

-- Declare temp variable to fetch records into
DECLARE @ColumnName varchar(128)
DECLARE @ColumnType varchar(128)
DECLARE @ColumnLength smallint
DECLARE @ColumnNullable int
DECLARE @ColumnCollation sysname
DECLARE @ColumnPrecision tinyint
DECLARE @ColumnScale tinyint

-- Declare variable to build statements
DECLARE @CreateStatement varchar(8000)
DECLARE @ListOfFields varchar(2000)
SET @ListOfFields = ''


-- Check if audit table exists
IF exists (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[' + @Owner + '].[' + @TableName + @AuditNameExtention + ']') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
-- AuditTable exists, update needed
PRINT 'Table already exists. Only triggers will be updated.'

FETCH Next FROM TableColumns
INTO @ColumnName, @ColumnType, @ColumnLength, @ColumnNullable, @ColumnCollation, @ColumnPrecision, @ColumnScale

WHILE @@FETCH_STATUS = 0
BEGIN
IF (@ColumnType <> 'text' and @ColumnType <> 'ntext' and @ColumnType <> 'image' and @ColumnType <> 'timestamp')
BEGIN
SET @ListOfFields = @ListOfFields + @ColumnName + ','
END

FETCH Next FROM TableColumns
INTO @ColumnName, @ColumnType, @ColumnLength, @ColumnNullable, @ColumnCollation, @ColumnPrecision, @ColumnScale

END
END
ELSE
BEGIN
-- AuditTable does not exist, create new

-- Start of create table
SET @CreateStatement = 'CREATE TABLE [' + @Owner + '].[' + @TableName + @AuditNameExtention + '] ('
SET @CreateStatement = @CreateStatement + '[AuditId] [bigint] IDENTITY (1, 1) NOT NULL,'

FETCH Next FROM TableColumns
INTO @ColumnName, @ColumnType, @ColumnLength, @ColumnNullable, @ColumnCollation, @ColumnPrecision, @ColumnScale

WHILE @@FETCH_STATUS = 0
BEGIN
IF (@ColumnType <> 'text' and @ColumnType <> 'ntext' and @ColumnType <> 'image' and @ColumnType <> 'timestamp')
BEGIN
SET @ListOfFields = @ListOfFields + @ColumnName + ','

SET @CreateStatement = @CreateStatement + '[' + @ColumnName + '] [' + @ColumnType + '] '

IF @ColumnType in ('binary', 'char', 'nchar', 'nvarchar', 'varbinary', 'varchar')
BEGIN
IF (@ColumnLength = -1)
Set @CreateStatement = @CreateStatement + '(max) '
ELSE
SET @CreateStatement = @CreateStatement + '(' + cast(@ColumnLength as varchar(10)) + ') '
END

IF @ColumnType in ('decimal', 'numeric')
SET @CreateStatement = @CreateStatement + '(' + cast(@ColumnPrecision as varchar(10)) + ',' + cast(@ColumnScale as varchar(10)) + ') '

IF @ColumnType in ('char', 'nchar', 'nvarchar', 'varchar', 'text', 'ntext')
SET @CreateStatement = @CreateStatement + 'COLLATE ' + @ColumnCollation + ' '

IF @ColumnNullable = 0
SET @CreateStatement = @CreateStatement + 'NOT '

SET @CreateStatement = @CreateStatement + 'NULL, '
END

FETCH Next FROM TableColumns
INTO @ColumnName, @ColumnType, @ColumnLength, @ColumnNullable, @ColumnCollation, @ColumnPrecision, @ColumnScale
END

-- Add audit trail columns
SET @CreateStatement = @CreateStatement + '[AuditAction] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,'
SET @CreateStatement = @CreateStatement + '[AuditDate] [datetime] NOT NULL ,'
SET @CreateStatement = @CreateStatement + '[AuditUser] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,'
SET @CreateStatement = @CreateStatement + '[AuditApp] [varchar](128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL)'

-- Create audit table
PRINT 'Creating audit table [' + @Owner + '].[' + @TableName + @AuditNameExtention + ']'
EXEC (@CreateStatement)

-- Set primary key and default values
SET @CreateStatement = 'ALTER TABLE [' + @Owner + '].[' + @TableName + @AuditNameExtention + '] ADD '
SET @CreateStatement = @CreateStatement + 'CONSTRAINT [DF_' + @TableName + @AuditNameExtention + '_AuditDate] DEFAULT (getdate()) FOR [AuditDate],'
SET @CreateStatement = @CreateStatement + 'CONSTRAINT [DF_' + @TableName + @AuditNameExtention + '_AuditUser] DEFAULT (suser_sname()) FOR [AuditUser],CONSTRAINT [PK_' + @TableName + @AuditNameExtention + '] PRIMARY KEY CLUSTERED '
SET @CreateStatement = @CreateStatement + '([AuditId]) ON [PRIMARY], '
SET @CreateStatement = @CreateStatement + 'CONSTRAINT [DF_' + @TableName + @AuditNameExtention + '_AuditApp] DEFAULT (''App=('' + rtrim(isnull(app_name(),'''')) + '') '') for [AuditApp]'

EXEC (@CreateStatement)

END

CLOSE TableColumns
DEALLOCATE TableColumns

/* Drop Triggers, if they exist */
PRINT 'Dropping triggers'
IF exists (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[' + @Owner + '].[tr_' + @TableName + '_Insert]') and OBJECTPROPERTY(id, N'IsTrigger') = 1)
EXEC ('drop trigger [' + @Owner + '].[tr_' + @TableName + '_Insert]')

IF exists (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[' + @Owner + '].[tr_' + @TableName + '_Update]') and OBJECTPROPERTY(id, N'IsTrigger') = 1)
EXEC ('drop trigger [' + @Owner + '].[tr_' + @TableName + '_Update]')

IF exists (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[' + @Owner + '].[tr_' + @TableName + '_Delete]') and OBJECTPROPERTY(id, N'IsTrigger') = 1)
EXEC ('drop trigger [' + @Owner + '].[tr_' + @TableName + '_Delete]')

/* Create triggers */
PRINT 'Creating triggers'
EXEC ('CREATE TRIGGER tr_' + @TableName + '_Insert ON ' + @Owner + '.' + @TableName + ' FOR INSERT AS INSERT INTO ' + @TableName + @AuditNameExtention + '(' + @ListOfFields + 'AuditAction) SELECT ' + @ListOfFields + '''I'' FROM Inserted')

EXEC ('CREATE TRIGGER tr_' + @TableName + '_Update ON ' + @Owner + '.' + @TableName + ' FOR UPDATE AS INSERT INTO ' + @TableName + @AuditNameExtention + '(' + @ListOfFields + 'AuditAction) SELECT ' + @ListOfFields + '''U'' FROM Inserted')

EXEC ('CREATE TRIGGER tr_' + @TableName + '_Delete ON ' + @Owner + '.' + @TableName + ' FOR DELETE AS INSERT INTO ' + @TableName + @AuditNameExtention + '(' + @ListOfFields + 'AuditAction) SELECT ' + @ListOfFields + '''D'' FROM Deleted')

END

After you create the procedure, try this :

create table testing1
(
id int identity(1,1) not null,
name varchar(50),
primary key (id)
)
go

exec [GenerateAudittrail] testing1, 'dbo', '_history', 1
go

insert into testing1(name) values('Wihemdra')
insert into testing1(name) values('Halim')
update testing1 set name = 'Wihemdra1' where id = 1
delete from testing1 where id = 2

select * From testing1
select * from testing1_history

Then you can find what exactly the procedure use for.

Happy Code ^_^

Capture Error Message on Procedure

When we want to make a procedure from sql, sometimes we need to capture the error message on procedure. It will allow us to make an history into the physical table on databases. Here the simulation :

---------------------------------
if exists(select 1 from sys.objects where name = 't_test') drop table t_test

create table t_test
(
id int not null,
primary key(id)
)
go
---------------------------------
if exists(select 1 from sys.objects where name = 't_msg') drop table t_msg

create table t_msg
(
id int identity(1,1) not null,
msg varchar(max) not null,
date datetime,
primary key(id)
)
go
---------------------------------
if exists(select 1 from sys.objects where name = 'sp_addTest') drop procedure sp_addTest
---------------------------------
create procedure sp_addTest
@id int
as
begin

begin try
begin tran
insert into t_test values(@id)
commit tran
end try
begin catch
declare @err_msg varchar(max), @err_proc varchar(max)
set @err_msg = ERROR_MESSAGE()
set @err_proc = ERROR_PROCEDURE()
rollback tran

insert t_msg(msg,date) select 'sp_addTest Error : '+ltrim(rtrim(@err_msg))+ 'Error in '+@err_proc,getdate()
end catch

end
go
---------------------------------
exec sp_addTest 2
go

exec sp_addTest 3
go

exec sp_addTest 4
go

exec sp_addTest 2
go

select * From t_test



select * From t_msg




the message is "sp_addTest Error : Violation of PRIMARY KEY constraint 'PK__t_test__1CA7377D'. Cannot insert duplicate key in object 'dbo.t_test'.Error in sp_addTest"

Happy Code ^_^"

Exporting data to a XML file in SQL Server 2005

Overview

In this article, I'll demonstrate how we can combine the new CLR integration and FOR XML enhancements to easily generate xml files from our relational data. For an introduction to .NET Stored procedures have a look at Introducing CLR Stored Procedures in SQL Server 2005. This article will reference the Orders table in the Northwind database. You can download the Northwind and pubs databases here.

.NET Code

The first thing we need to do is write our .Net code for our Stored Procedure and compile it. In this case, we are going to create a procedure that accepts a filename and some content (using a nvarchar(max) parameter) and simply writes that content to the specified file.

[Visual Basic]

Imports System
Imports System.Data
Imports Microsoft.SqlServer.Server
Imports System.Data.SqlTypes
Imports System.IO

Public Class SQLCLRIO
  <> _
  Public Shared Sub WriteToFile(ByVal content As String, _
                                ByVal filename As String)

      Try
         File.WriteAllText(filename, content)

      Catch ex As Exception
          SqlContext.Pipe.Send("Error writing to file " & ex.Message)
      End Try

  End Sub

End Class

[C#]

using System;
using System.Data;
using Microsoft.SqlServer.Server;
using System.Data.SqlTypes;
using System.IO;

public class SQLCLRIO{

  [Microsoft.SqlServer.Server.SqlProcedure]
  public static void WriteToFile(String content,String filename)
  {
      try
      {
         File.WriteAllText(filename, content);
      }
      catch(Exception ex)
      {
         SqlContext.Pipe.Send("Error writing to file " + ex.Message);
      }
  }
}

In order for us to use this code in SQL Server 2005 we must first compile it into an assembly. We can then load it into SQL Server and create our TSQL Stored Procedure wrapper for our .Net code. The command below demonstrates how to compile the code into an assembly. For the purposes of this article we will assume that the code has been saved in a source file called c:\WriteToFile.vb or c:\WriteToFile.cs. To compile this open a command prompt and execute the relavent lines to change to the .NET 2.0 directory and compile the code.

[Visual Basic] cd "%SystemRoot%\Microsoft.NET\Framework\v2.0.50727" vbc /target:library c:\WriteToFile.vb [C#] cd "%SystemRoot%\Microsoft.NET\Framework\v2.0.50727" csc /target:library c:\WriteToFile.cs

TSQL Code

Now that we have created our assembly (c:\WriteToFile.dll) we can load it into SQL Server and create our Stored Procedure. We do this by using the new CREATE ASSEMBLY command as below. We need to specify a permission_set of EXTERNAL_ACCESS because the stored procedure needs to access the file system. We need to ensure that execution of user defined CLR code is enabled using sp_configure and we also need to set the database Trustworthy property ON to allow the loading of the EXTERNAL_ACCESS assembly. NOTE : The external access will be under the security context of the SQL Service account

exec sp_configure 'clr enabled',1 reconfigure go alter database Northwind set trustworthy on go use Northwind go create assembly WriteToFile from 'c:\WriteToFile.dll' with permission_set = external_access

Now that we have loaded our assembly, we can create our Stored Procedure. To do this we simply use the standard TSQL CREATE PROCEDURE syntax but instead of defining a TSQL procedure body, we specify an EXTERNAL NAME that points to our .NET Code. We specify the assembly, class and method name in the form .. as shown below.

create procedure dbo.writetofile ( @content nvarchar(max), @filename nvarchar(255) ) as external name WriteToFile.SQLCLRIO.WriteToFile

Now that the Stored Procedure is created, we can use it to write our content (in this case xml) to a file. The sample query will take the entire contents of the Northwind.dbo.Orders table and create a single, well formed xml document from it which we will then write to a xml file using the WriteToFile stored procedure. The query takes advantage of the ability to declare local variables for LOB types such as varchar(max) and nvarchar(max) - these datatypes supercede the text and ntext datatypes - as well as the new root directive of the FOR XML clause that allows you to specify a single top level root element for you xml document, in the code below the top level element will be . This code will create a new file called AllOrders.xml containing all the order header information from the Northwind database. You can view the contents of the generated file by simply opening it in your web browser.

declare @xml nvarchar(max)

set @xml = (
select * from Orders
for xml auto,elements,root('AllOrders'))

exec writetofile @xml,'c:\AllOrders.xml'

Source : http://www.sqldbatips.com/showarticle.asp?ID=23

Generate System Column ID based on Format Required in SQL

If we want to make a generate id column in table which format is 'TR-091214-001' where,
- tr is define transaction
- 091213 is define yymmdd
- 001 is define increment number of transaction each day

we can make a simple logic and validation for that.

here the steps :

-- step 1 create table
create table testgenid
(
tr_id varchar(30) not null,
primary key(tr_id)
)

-- step 2 generate id
declare @gen_id varchar(30)
set @gen_id = (
select 'TR-'+ right(convert(varchar,getdate(),112),6) + '-' + right('00' + cast((cast(right(isnull(max(tr_id),'000'),3) as int) + 1) as varchar),3)
from testgenid
where tr_id like '%' + right(convert(varchar,getdate(),112),6) + '%')

print @gen_id

insert into testgenid values(@gen_id)

-- step 3 see the result
select * From testgenid

the result is : TR-091214-001

happy coding ^^

Data Dictionary Using Query SQL

When we want to summarize all the tables on database from SQL we need the query to make it simple and fast... here the query :

select distinct b.name, a.name, c.name, a.max_length,c.precision, c.scale
from sys.columns a left join sys.objects b
on a.object_id = b.object_id
left join sys.types c
on c.system_type_id = a.system_type_id
and c.user_type_id = a.user_type_id

Having A Nice Try… ^^

Good Luck

Row Number Function in SQL 2005

A new function in sql server 2005. It's function returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition.

Syntax :

ROW_NUMBER ( ) OVER ( [] )

Example :

create table Testing ( Kd varchar(10) not null, Name varchar(10) )

insert into Testing values('K01','Siji')
insert into Testing values('K02','Loro')
insert into Testing values('K03','Telu')
insert into Testing values('K04','Opat')

select ROW_NUMBER() OVER(ORDER BY Kd) AS 'No.' , Kd , Name from Testing

Output :

Easy Right!!!

Happy Code \^_^/

Find Text In SQL Server

Sometimes we want to check where the certain text could appear on the store procedure or trigger we made... Here is the query provide to maximize the searching :


declare @text varchar(max)
set @text = 'Wihemdra'

select name from sys.objects
where object_id in
(
select id from syscomments
where text like '%'+@text+'%'
)


Regards,
Wihemdra