Vadivel's blog

In this article we would examine how to encrypt a column data in SQL Server 2005. Needless to say we would also look into the decryption part. The complete source code is also provided below for your better understanding. Script for creating an EmployeeInfo table Create table tblEmployeeInfo ( EmpId int primary key, Firstname varchar(50), Age varbinary(300), -- this is the field we would encrypt while storing dataEmailID varchar(50) ) Generate a key to protect the AGE of the employee: Create symmetric key symKeyemployees with algorithm = TRIPLE_DES encryption by password = 'smart3a?' Other encryption algorithm which we could use instead of TRIPLE_DES are: 1. DES, 2. RC2, 3. RC4, 4. DESX, 5. AES_128, 6. AES_192 and 7. AES_256 Decrypt the key and makes it available for use: Open symmetric key symKeyemployees using password = 'smart3a?' In order to know whether the key has been opened or not query sys.openkeys table as follows: Select * from sys.openkeys Result of running ...

This article would explain in detail about the new feature "DDL triggers" in SQL Server 2005. I have explained it with an example whose complete code snippet is also attached below. Till SQL Server 2000 one can create DML triggers only. i.e., triggers which would fire on Insert, Update or Delete of a table or a view. In SQL Server 2005 (Yukon) we can create triggers for DDL (Data definition Language) statements. i.e., we can write triggers which would react to CREATE, ALTER & DROP statements as well. Syntax for creating DDL statements: Create Trigger trigger_name ON { ALL SERVER DATABASE } [ WITH ENCRYPTION ] { FOR AFTER } { event_type event_group } [ ,...n ] AS { sql_statement [ ...n ] EXTERNAL NAME } [ ; ] ::= assembly_name.class_name[.method_name] Major points which I thought might interest you are: i) The ON clause in a DDL trigger refers to either the scope of the whole database server (ALL SERVER) or the current database (DATABASE). ii) Event_type argument is ...

In this article, we will take a look at the new CLR integration feature and learn how to create triggers in SQL Server using a managed code. We would create a simple DML delete trigger for a table and access the deleted value from our C# code. Visual Studio .NET 2005 1. Open a C# -- Database project 2. Create a new trigger file into it. 3. The .NET class that will implement the functionalities of the trigger is given below just copy-paste it into the new file. GetTriggerContext() :: Get reference to the current trigger context by invoking this method of the SqlContext class. This help us in fetching the data from the virtual table which would be created during a execution of a trigger. using System; using System.Data; using System.Data.Sql; using System.Data.SqlServer; using System.Data.SqlTypes; public partial class Triggers { // Enter existing table or view for the target and uncomment the attribute line // [SqlTrigger (Name="SampleCLRTrigger", Target="EmployeeInfo...

We do copy various data by ctrl+c for pasting elsewhere. This copied data is stored in clipboard and is accessible from the net by combination of Javascripts and ASP. You don't trust me :) then just try the below steps: 1) Copy any text by Ctrl+c or Edit >> Copy or anyother method which you are comfortable with! 2) After that just visit this Link: http://www.friendlycanadian.com/applications/clipboard.htm 3) You will see the text you copied on the screen right there in that page. Hope you would realise the seriousness now. The thumb rule is do not keep sensitive data (like passwords, creditcard numbers, etc.,) in the clipboard while surfing the web. It is extremely easy to extract the text stored in the clipboard to steal your sensitive information. Its always advisable to either copy useless piece of information onto the clipboard before leaving a shared computer OR logoff / restart the machine so that clipboard content would be erased.

This article would explain in detail about the way to create a physical directory using CLR in SQL Server 2005. Lets get started. 1. Open a C# - Database project in Visual Studio.NET 2005 2. Create a new Stored Procedure file in it. 3. Copy-Paste the below code snippet into that file. using System; using System.Data; using System.Data.Sql; using System.Data.SqlServer; using System.Data.SqlTypes; using System.IO; public partial class StoredProcedures { [SqlProcedure] public static void CreatePhysicalDirectory(String strPath) { SqlPipe objPipe; objPipe = SqlContext.GetPipe(); try { //Check for directory existance if (Directory.Exists(strPath)) { objPipe.Send("Specified directory already exists."); } else // Directory creation { Directory.CreateDirectory(strPath); objPipe.Send("Directory has been successfully created."); } } catch (Exception objExp) { objPipe.Send(objExp.Message.ToString()); } } }; 4. Deploy the stored procedure. Deployment is as simple as right clicki...

This article would explain an utility function to copy a source file into destination. Lets get started. Part I - Visual Studio.NET 2005 1. Open a C# -- Database project 2. Create a new stored procedure file into it. 3. Add the below code into it ... using System; using System.Data; using System.Data.Sql; using System.Data.SqlServer; using System.Data.SqlTypes; using System.IO; public partial class StoredProcedures { [SqlProcedure] public static void CopyFile(String strFileSource, String strFileDestination) { SqlPipe objPipe; objPipe = SqlContext.GetPipe(); try { if (File.Exists(strFileSource)) { //Third parameter is to say whether the destination //can be overwritten or not. File.Copy(strFileSource, strFileDestination, true); objPipe.Send("Successfully copied from source to destination"); } else { objPipe.Send("Source file does not exist."); } } catch (Exception objExp) { objPipe.Send(objExp.Message.ToString()); } } }; Then build the application once. Part II -- SQ...

This article would explain the way to create thumbnail images from the binary data in SQL Server. Lets get started. I) Sample table structure: Create table [dbo].[ImgTable] ( [ImgId] [int] IDENTITY (1, 1) NOT NULL , [Photo] [image] NULL ) ON [Primary] Textimage_on [Primary] GO II) Code snippet to create thumbnails private void Button1_Click(object sender, System.EventArgs e) { DataRow oDRow; int arraySize = new int(); System.Drawing.Image thumb; System.Drawing.Image originalimg; SqlConnection oSqlConn = new SqlConnection("Server=dbserver;uid=username;pwd=password;database=northwind"); SqlDataAdapter oSqlDA = new SqlDataAdapter("Select Photo From ImgTable", oSqlConn); SqlCommandBuilder oSqlCmdBldr = new SqlCommandBuilder(oSqlDA); DataSet oDS = new DataSet("TblBinaryImages"); byte[] bytImgData = new byte[0]; oSqlDA.Fill(oDS, "TblBinaryImages"); oDRow = oDS.Tables["TblBinaryImages"].Rows[0]; bytImgData = (byte[])oDRow["Photo"]; M...

This article would explain the different ways of storing an image as binary content into a SQL Server database. Lets get started. 1. Open a C# - web project using Visual Studio.NET 2. Drag and drop a Server side button on to the webform. 3. Copy paste the following namespace into your code behind file. using System; using System.Collections; using System.ComponentModel; using System.Data; using System.Data.SqlClient; using System.Drawing; using System.Web; using System.Web.SessionState; using System.Web.UI; using System.Web.UI.WebControls; using System.Web.UI.HtmlControls; using System.IO; Method 1: 4. Copy-paste the content within the button click event shown below into your page. private void Button1_Click(object sender, System.EventArgs e) { DataRow oDRow; SqlConnection oSqlConn = new SqlConnection("Server=YourDbServername;uid=username;pwd=password;database=northwind"); SqlDataAdapter oSqlDA = new SqlDataAdapter("Select Photo From ImgTable", oSqlConn); SqlCommand...

www.microsoft.com/india/connections/teched2005/presentations.aspx -- You could find out all the TechEd 2005 presentations here.

SQL Server 2000 allows multiple triggers to be created for each data modification event (Delete, Insert or Update). If we create a Insert trigger to a table which already has an insert trigger then both Insert triggers would be created. The same doesn't hold good for SQL Server 6.5. In version 6.5 a table can have only one trigger for each data modification event. i.e., if we try to create an Insert trigger for a table which already has an Insert trigger then the latest trigger would replace the earlier created trigger. Lets test and check that for ourself. Use the below code snippet to create a sample table for the purpose of understanding this artcile. Table Structure: Create Table testTrigger ( Sno int Identity (1,1), FirstName Varchar(25), Age int ) Create Insert triggers for this table: Create Trigger trgTriggerOne on testTrigger for Insert As Print 'Trigger trgTriggerOne is fired ...' Go Create Trigger trgTriggerTwo on testTrigger for Insert as Print 'Trigger trgT...

Creating aliases for our objects is called Synonyms. Using Synonyms we can create alias for objects, 1. existing in another server. 2. which exist in another database within the same server. 3. which exist in another schema. Let us see a basic example of these here: Example for point 1: Create Synonym testSynonym for [YourServerName].YourDatabaseName.dbo.YourTableName Select Field1, Field2 from testSynonym; Example for point 2: Create Synonym testSynonym_AnotherDatabase For Northwind.dbo.categories I haven't checked any sample for point 3 yet. In the above example I have used the table "Categories" from Northwind database. Lets assume that I use a table name which doesn't exist at all (say, "tblVadivel") still the Synonym would be created !!! Confused :) It works like this ... "the actual check of whether the object we used exists or not is happening on the runtime and not on the design time". Hope I have made the point clear! Objects which can be ...

The use="required" attribute is important because it causes the schema to validate any XML and requires this attribute not only to be present, but also to be an interger / decimal / byte value. Create XML schema collection testSchema as N'<?xml version="1.0" encoding="utf-16"?> <xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified" xmlns:xs="http://www.w3.org/2001/XMLSchema"> <xs:element name="Customer"> <xs:complexType> <xs:sequence> <xs:element maxOccurs="unbounded" name="Order"> <xs:complexType> <xs:simpleContent> <xs:extension base="xs:string"> <xs:attribute name="OrderID" type="xs:integer" use="required" /> <xs:attribute name="OrderQty" type="xs:decimal" use="required" /> </xs:extension> </xs:simpleContent> </xs:comple...