Vadivel's blog

One of the sql newsgroup member asked this question: Guys, I have a table by name "TblRecursive" which has following data ID, Name, ParentID 1, A, 0 2, B, 1 3, C, 2 4, D, 2 5, E, 1 Using the above data I just want to generate a result as below A A\B A\B\C A\B\D A\E Can you help in writing a query for this? My Solution: We can achieve this by calling a "User Defined Function (UDF) recursively". Let me show how to do that with a working example. --Table creation Create table tblEmployeeInfo ( EmpId int primary key, EmpName varchar(30), MgrId int ) --Insert test data into it Insert into tblEmployeeInfo values(1, 'Director', null) Go Insert into tblEmployeeInfo values(2, 'Joint Director', 1) Go Insert into tblEmployeeInfo values(3, 'Secretary', 2) Go Insert into tblEmployeeInfo values(4, 'Joint Secr.,', 3) Go Insert into tblEmployeeInfo values(5, 'Legal Advisor', 1) Go -- User defined function for your requirement Create function

I wrote a small program for screen scraping any sites using XmlHttp object and VBScript. I know I haven't done any rocket science :) still I thought of sharing the code with you all. XmlHttp -- E x tensible M arkup L anguage H ypertext T ransfer P rotocol An advantage is that - the XmlHttp object queries the server and retrieve the latest information without reloading the page. Source code: < html > < head > < script language ="vbscript"> Dim objXmlHttp Set objXmlHttp = CreateObject("Msxml2.XMLHttp") Function ScreenScrapping() URL == "UR site URL comes here" objXmlHttp.Open "POST", url, False objXmlHttp.onreadystatechange = getref("HandleStateChange") objXmlHttp.Send End Function Function HandleStateChange() If (ObjXmlHttp.readyState = 4) Then msgbox "Screenscrapping completed .." divShowContent.innerHtml = objXmlHttp.responseText End If End Function </ script > < head > < body > &l

Last Updated on October 10, 2007 * Latest articles are added at the end of this post. Articles relating to SQL Server 7.0 / 2000 1. [MSDN] Database documentation 2. Returning comma seperated details from a table ... 3. Quick search within ALL stored procedures ... 4. Find whether a column is identiy or not 5. Encryption in SQL Server 7.0 6. About sp_readerrorlog 7. Useful TSQL code snippets for beginners 8. Copying database diagrams ... 9. Query to display Null values at the bottom ... 10. Alternate rows ... 11. Running number !! 12. Doing case sensitive searches 13. Easiest way to add comments to your SQL 2k code ... 14. Listing records from 10 to 15 (for ex) without using where clause 15. Is sorting possible in Views? 16. Creating thumbnails from binary content 17. Saving an image as binary data into SQL Server 18. Reclaim Unused Table Space 19. Encrypting ALL SP's ... 20. Database Compatibility ... 21. About TimeStamp datatype of SQL Server ... 22. Grouping Store

I saw an question in one of the SQL newsgroup which I visit frequently (offlate). That person is having a problem with retriving data from a table. Let me explain it in detail. Sample table structure: Create table empTest ( [Id] int identity, Contact varchar(100), Employee_Id int ) Go Let us populate few records into the table: Insert into empTest (Contact, Employee_Id) values ( 'vmvadivel@gmail.com', 101) Insert into empTest (Contact, Employee_Id) values ( '04452014353', 101) Insert into empTest (Contact, Employee_Id) values ( 'vmvadivel@yahoo.com', 102) Insert into empTest (Contact, Employee_Id) values ( '9104452015000', 102) Go And now, as you could see each employee has more than one contact details. So if you query the table as Select * from EmpTest it would list couple of records for each employee. Instead of this won't it be nice if we could generate comma seperated contact details for each employee. i.e., There would be only one record for an

This article would explain in detail the methods involved in searching strings within ALL stored procedures. I am sure there might have been situation where you want to find out a stored procedure where you remember writing some complex logic. Won't it be nice if we can find out that stored procedure where we have already written that important piece of code .. so that we can reuse? If your answer is "yes" read on. Points to note before executing this SP: 1. I have written 2 methods for this purpose. If we want this SP to be in the MASTER database then set @method =1. If not set it to 2 2. If @method is set to 2 then it is advisable to change the SP name. As you know only SP's which exist in MASTER database needs to be prefixed with "SP_" (for performance reason). The Stored Procedure: Create Procedure sp_searchForStoredProc ( @searchString varchar(100) ) As /************************************************** Stored Procedure: sp_searchForStoredProc CreatiOn

In one of the newgroup somebody was asking the way to find whether a column is identity or not. I thought I would write my answer there as an article for the benefit of those who have the same doubt. I know of two ways of finding whether a given column is an identity column or not. Let me try and explain it ... Sample Table structure: Create a sample table with an identity column in it. Create table [order_details] ( OrderId int identity, OrderName varchar(10), UnitPrice int ) Method 1: [Easiest way] Select ColumnProperty(Object_id('order_details'), 'OrderId', 'IsIdentity') Method 2: For some reasons if you don't want the above method!! then try this one Declare @colName varchar(100) Declare @RetColName varchar(100) Set @colName = 'OrderId' -- Specify the column name for which you want to check --Status column = 128 means its an identity column Select @RetColName=[name] from syscolumns where (status & 128) = 128 and id=(select id from sysobjects

Often people ask me "I have a table and there are few views based on that table. When I make a structural change to my table it invalidates all those views. So we are left out with no other option than to drop those views and recreate it. But is there any alternate way for this?". For all those people who have this doubt in mind .. read on. i) Create this sample table for demo purpose Create table tstTestingUpdateView ( Sno int identity, [Name] varchar(10), Mail varchar(50) ) ii)Insert some dummy records Insert into tstTestingUpdateView Values('Vadivel','smart3a@yahoo.com') iii) Create a view based on that table Create view tstView1 As Select * from tstTestingUpdateView iv) Execute the newly created view and have a look at the output Select * from tstView1 v) Now add a new column to the table Alter table tstTestingUpdateView add ContactNumber Varchar(20) --Now if you execute the view it won't list the newly added column in it Select * from tstView1 vi) So

Try double clicking any word within my blogspace. For example, if you double click on a word "response" then you would get an definition of that word in detail. Try it out for yourself and do let me know whether this would be helpful for you guys.

Estonia has sucessfully conducted an national election with online voting option. A tiny Baltic nation last week became what appears to be the first country to open its local elections to Internet voting on a nationwide level--although only about 1 percent of the votes were cast online. Check out the full article here Estonia pulls off nationwide Net voting Needless to say, internet voting would save lot of time and energy for almost everybody. But I seriously donno whether in near future it would be possible in India! I personally feel that we need to improve a lot in the following fields "Security", "Infrastructure" and "Computer awareness". I don't think this would be possible here in India or Tamil nadu for atleast next 10 years.

After a long time I visited CNUG (Chennai .NET User Group) . When I was going through the questions I saw a question posted by "Nitin" asking Is it possible to encrypt data within data server (SQL Server 7.0) URL of that post can be seen here >> http://groups.msn.com/ChennaiNetUserGroup/general.msnw?action=get_message&mview=0&ID_Message=9327&all_topics=0 My response to that post: There are two undocumented functions in SQL Server (since SQL Server 6.5). They are: 1. Pwdencrypt and 2. Pwdcompare. Pwdencrypt -- It uses one way encryption. That is, it takes a string and returns a encrypted version of that string. Pls note that in one way encryption you can't get back the actual string (i.e., you can decrypt the encrypted data). Pwdcompare -- It compares an unencrypted string to its encrypted representation to see whether they match. Since it is undocumented functions there is a possibility that MS can remove or change it at anytime without prior notice. So

In this article we would look into the easiest way of importing an image as BLOB content into a SQL table. 1. Openrowset has new bulk features introduced in SQL Server 2005. 2. Openrowset supports bulk operations through a built-in bulk provider that allows data from a file to be read and returned as a rowset. 3. Using the BULK rowset provider you can load a file into a table's column using regular DML. 4. Unlike SQL Server 2000, instead of being limited to Text, NText and Image datatypes for large objects, in SQL Server 2005 we can also use Varchar(max), nvarchar(max) and Varbinary(max) datatypes. The new MAX option allows you to manipulate large objects the same way you manipulate regular datatypes 5. With OPENROWSET you'll be able to return a rowset from a file as a single varbinary(max), varchar(max) or nvarchar(max) data type value. We'll use "SINGLE_BLOB", "SINGLE_CLOB" or "SINGLE_NCLOB" to diffentiate what kind of single-row,

I came to know about "Notorious B.G on MTV" from my favourite bloggers blogspace >> http://scobleizer.wordpress.com/2005/10/29/why-do-i-work-at-microsoft/ I went through the complete transcript @ http://www.mtv.com/thinkmtv/features/education/gates_forum/ and the below QA is what I liked the most. Yago: Before we reach that day, certainly I know a lot of people in high school and college are hearing a lot about how India and China will take over a lot of American jobs. What do you say to that generation of young people now that's in college, that's now in high school or approaching high school? Gates: India and China advancing and getting rich is fantastic news. What that means is that people who have been living in poverty, had ill health and illiteracy, are now getting jobs that allow them to be educated and realize their potential. If we had a choice today where India and China would be as rich as the United States, we should all want that, because not on

In this article we would look into basics of paging records in SQL Server 2005. I have provided couple of methods with correspondng code snippets for our better understanding. Code snippet for the sample table: Create Table tstSQLPaging ( Sno int, FirstName varchar(50), LastName varchar(50), EmailId varchar(100), Salary int ) Go Enter sample data into that table: Insert into tstSQLPaging values (1, 'Vadivel','M','vmvadivel@yahoo.com',10000) Insert into tstSQLPaging values (2, 'Sailakshmi','L','abc@yahoo.com',9000) Insert into tstSQLPaging values (3, 'Raj','A','aRaj@yahoo.com',11000) Insert into tstSQLPaging values (4, 'Dhina','B','bDhina@yahoo.com',25000) Insert into tstSQLPaging values (5, 'Siddharth','s','itissiddhu@yahoo.com',6000) Insert into tstSQLPaging values (6, 'Vicky','L','vicky@yahoo.com',19000) Insert into tstSQLPaging values (7, &#

In this article we would see how SQL Server 2005 has been powered to create Webservices with ease. Implementing real webserices in SQL Server 2005 is officially called HTTP Endpoints. Ok without fasting any further time lets start to understand HTTP Endpoint by getting our hands dirty. Open SQL Server 2005 Use Adventureworks go --Lets create a sample table populate it with few test data Create Table WSDemo (Sno int, EmpName varchar(50), EmpMailId varchar(100)) Insert into WSDemo Values (1, 'Vadivel','vmvadivel@yahoo.com') Insert into WSDemo Values (1, 'Velias','smart3a@yahoo.com') --- Stored procedure which would return all employee name with their corresponding email address. Create Procedure dbo.FetchWSDemo As Select EmpName, EmpMailId From WSDemo Go --The actual WS work happens here Create EndPoint FetchWSDemo State = Started As HTTP ( Path = '/SQLWSDemo', Authentication = (INTEGRATED), Ports = (Clear), Site = 'localhost') For Soap ( W

Many a times in the last couple of months I got mails or rather spams :) saying we can't create folder in the name of "CON", "PRN" etc., Instead of responding to them individually (that is what I was doing all this while) I have created this blog post. Check out http://msdn.microsoft.com/library/default.asp?url=/library/en-us/fileio/fs/naming_a_file.asp to understand what are all the reserved words using which you can't create folders in Windows. That said, go to dos prompt and try out md\\.\c:\con ... to your surprise a folder by name "con" would have been created in your c drive!!

In this article I would talk about the New APPLY operator in SQL Server 2005. As usual I would provide a working sample / code snippet for better understanding. Extract from BOL: The APPLY operator allows you to invoke a table-valued function for each row returned by an outer table expression of a query. The table-valued function acts as the right input and the outer table expression acts as the left input. The right input is evaluated for each row from the left input and the rows produced are combined for the final output. The list of columns produced by the APPLY operator is the set of columns in the left input followed by the list of columns returned by the right input. There are two forms of APPLY: CROSS APPLY and OUTER APPLY. CROSS APPLY returns only rows from the outer table that produce a result set from the table-valued function. OUTER APPLY returns both rows that produce a result set, and rows that do not, with NULL values in the columns produced by the table-valued function.

In this article let us understand the system stored procedure Sp_readerrorlog in detail. Sp_readerrorlog is a system stored procedure which can be used to read the SQL Server error logs from within T-SQL. Syntax of sp_readerrorlog Parameter 1 : Non zero integer value Parameter 2 : File name Parameter 3 : Line number in the file Parameter 4 : Search string 1. Code snippet to display the content of the current event log Execute sp_readerrorlog 2. Code snippet to display the content of eventlog.1 EXEC sp_readerrorlog 1, 'C:\Program Files\Microsoft SQL Server\MSSQL$Velias\log\ERRORLOG.1' Or EXEC sp_readerrorlog 1 3. Code snippet to search a string --Would search the second row of the specified text file for the number 2000. EXEC sp_readerrorlog 1, 'C:\SendToTextFile.txt',2, '2000' Yep you got it right ... this system stored proc can be used not only on log file but on any file (as shown in the above code snippet). Script to import the SQL Error Logs, format the res

In this article we would see an interesting feature which was introduced in SQL Server 2005. Do you know that you can execute a T-SQL batch "any number" of times just by providing an integer value along with GO. As we all know we used to end a command or a script block by typing a command terminator. i.e, GO. GO :: Signals the end of a batch of Transact-SQL statements to the MS SQL Server utilities. Extract from BOL: GO is not a Transact-SQL statement; it is a command recognized by the sqlcmd and osql utilities and SQL Server Management Studio Code editor. SQL Server utilities interpret GO as a signal that they should send the current batch of Transact-SQL statements to SQL Server. The current batch of statements is composed of all statements entered since the last GO, or since the start of the ad hoc session or script if this is the first GO. In SQL Server 2005, we can follow the command terminator (GO) with an integer value to specify how many times the command should be r

Find below the script which would help you all in searching contents within "msdn", "dictionary", "msn", "yahoo", "netscape", "Google" and within my blogspace with ease. Script: Windows Registry Editor Version 5.00 [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Internet Explorer\SearchUrl] [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Internet Explorer\SearchUrl\dic] @=" http://dictionary.reference.com/search?q=%s " [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Internet Explorer\SearchUrl\google] @=" http://www.google.com/search?hl=en&lr =&q=%s" [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Internet Explorer\SearchUrl\msdn] @=" http://search.microsoft.com/search/results.aspx?qu=%s " [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Internet Explorer\SearchUrl\net] @=" http://search.netscape.com/ns/search?fromPage=NSCPTop&query=%s " [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Internet Explorer\SearchUrl\msn] @=" htt

For quite sometime I am reading Tejas blogs and I should say it is really informative. Keep up the good work Tejas. Wanna know more about him .. check out "Who is Tejas Patel?"

Rajesh Shetty author of Beyond Code gives us pointers of what to do when you start a blog. This is one of my favorite questions. A few months ago, the question used to be "How do I start a blog?" That's no longer the popular question. Almost everyone knows how to start a blog. In fact, one of the easiest things about the blog is starting one. With so many free blogging services available, it should be a snap to setup one. Of course, if it's easy for you to start a blog, it's easy for several million people out there. So, for obvious reasons, starting a blog is no longer a differentiator. You got to do something more. So, what are those additional things that you can do? Over the last few months I have a learnt a thing or two about blogging from several of my friends who are my role models in the blogging world. Here's my $.02 for those of you who are starting to blog (partial list) 1. Focus on providing high ROII (Return on Investment for an Interaction) Ever

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 argu

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&q

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