Vadivel's blog

This article would explain in detail (with complete code sample) the way to return custom resultsets to the end user using CLR in SQL Server 2005. Things to know before we get started: 1) SqlPipe() -- Its the job of the SqlPipe object to send results back to the stored proc or for that matter UDF etc., 2) SqlDataRecord -- If we want to return resultset (more than one record) make use of this new object which is introduced in ASP.NET 2.0. For that we need to first create a schema using SqlMetaData objects. With this small introduction lets get our hands dirty by writing a small SP which would return more than records. 1. Open VS.NET 2005 2. Create C# based Database project 3. Right click on the solution and choose Add >> Stored proceedure >> name the file as SPReturningResultSet 4. Copy paste the below code into that file using System; using System.Data; using System.Data.Sql; using System.Data.SqlServer; using System.Data.SqlTypes; using System.Data.SqlClient; public part...

This article would explain in detail about the way to rename a physical directory using CLR in SQL Server 2005. The complete source code is attached with this article. 1. Open VS.NET 2005 2. Create C# based Database project 3. Right click on the solution and choose Add >> Stored proceedure 4. Copy paste the below code 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 RenameDirectory(String strOldName, String strNewName) { SqlPipe objPipe; objPipe = SqlContext.GetPipe(); try { //Check for directory existance //Note: Since it is for demo purpose I haven't done the complete validation if (Directory.Exists(strNewName)) { objPipe.Send("Specified directory already exists."); } else // Rename the Directory { //Directory.CreateDirectory(strNewName); Directory.Move(strOldName, strN...

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...

This article is to explain the functionality of Rank() function in SQL Server 2005. In our schooling days if the over all score of two guys are the same in exams then they would be ranked as 1 each and the next guy would get 3rd rank and not 2nd rank. To achieve same kind of result in SQL Server 2005 we can make use of this Rank() function. Extract from MSDN: If two or more rows tie for a rank, each of the tied rows receives the same rank. For example, if the two top sales people have the same SalesYTD value, then they are both ranked one. The sales person with the next highest SalesYTD is ranked number three, since there are two rows that are ranked higher. Therefore, the RANK function does not always return consecutive integers. Table Structure for the sample table: Create table DemoRankingFunction ( Sno int identity, FirstName varchar(25), Marks int ) Sample records for the demo purpose: Insert into DemoRankingFunction Values ('Vadivel',99) Insert into DemoRankingFunction Va...

In this article let us see a very basic example of XML schema and its usage. In order to validate an XML file create a XSD for it. XSD allows you to define a full structure for a XML document that must be followed to be validated. If we enter a different structure it would result in an error. Let us create a sample table structure, schema and test it by inserting couple of records into it. Create a XML Schema Create xml schema collection TestTableSchema as N'<?xml version="1.0" encoding="UTF-16"?> <xsd:schema elementFormDefault="unqualified" attributeFormDefault="unqualified" xmlns:xsd="http://www.w3.org/2001/XMLSchema"> <xsd:element name="TestTable"> <xsd:complexType mixed="false"> <xsd:sequence> <xsd:element name="FirstName" type="xsd:string"/> <xsd:element name="Age" type="xsd:integer"/> <xsd:element name="Sex" type=...

The XML data type has 5 methods namely, 1. exist 2. value 3. modify 4. query and 5. nodes Extract from MSDN: 1. query() : Use this method to query over an XML instance. 2. value() : Use this method to retrieve a value of SQL type from an XML instance. 3. exist() : Use this method to determine if a query returns a nonempty result. 4. modify() : Use this method to specify XML DML statements to perform updates. 5. nodes() : Use this method to shred XML into multiple rows to propagate parts of XML documents into rowsets. Code Snippet -- Part I Declare @xmlString XML Set @xmlString = ' <Customer> <Order id="102"> <ProductName>Rice</ProductName> </Order> </Customer> ' Select testTable.testColumn.query('.') as Records From @xmlString.nodes('/Customer/Order') testTable(testColumn) On executing the above code snippet it would display the result as follows: <Order id="102"><ProductName>Rice</ProductNam...

1. Open a C# -- Database project 2. Create a new 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; public partial class StoredProcedures { [SqlProcedure] public static void SimpleSP() { SqlPipe objPipe; objPipe = SqlContext.GetPipe(); //current month no objPipe.Send(System.DateTime.Now.Month.ToString()); //no of days objPipe.Send(System.DateTime.DaysInMonth(2005,7).ToString()); } }; Just build it and deploy this code. Building and deploying is as simple as just right clicking on the project name and choosing the appropriate option. Open SQL Server Management Studio 1. Create a new query 2. Try and execute the SP written via VS.NET use TestDatabase Exec SimpleSP It would most probabaly throw an error like this: Msg 6263, Level 16, State 1, Line 1 Execution of user code in the .NET Framework is disabled. Use sp_configure 'clr enabled' to enable execution of use...

Time management is a mandatory subject everyone of us needs to learn one day or other. Many times I have had problems taking too many tasks in my head. There is an online guide on effective time management. You can check it here . Source: http://msmvps.com/athif/articles/44653.aspx

Check out slava oaks blog on internals of SQL Server 2005 (Code named: Yukon). Its really extensive and informative. I have decided to go through his blog more frequently :)