Vadivel's blog

My sister (Sumathi Maraimallai) was working with Citi Shelters for approx 3 years as a Manager for Personal Loans section. After that she moved to another company for better career growth and now she has become a DSA (Direct selling agent) of few MNC banks along with her friend Mr. Roshan (who was also working previously with Citi Shelters for approx 6 years). On the other day she was asking for some referals. I said, I don't want to put people in trouble by giving away their mobile numbers :) Rather I thought I would put a word across here for the benefit of others. If at all you are looking for Personal loans then you might want to make a note of their contact numbers [ Feel free to refer my name so that they would realize that I am also capable of giving them business lol ]: Mobile - 9841283226 Land Line - 664561901 / 02 /03 E–Mail : corporateloans (at) airtelbroadband.in Couple of questions which I thought people would ask by default: 1. What is the duration for getting a loa...

Removing leading and trailling spaces is pretty easy. All you need to do is make use of Ltrim and Rtrim function respectively. But there are times when you want to remove unwanted spaces within a string. Check out the below code snippet to know how to do it. --Declaration and Initialization Declare @strValue varchar(50) Set @strValue = ' I Love you ! ' -- Here between each word leave as many spaces as you want. --Remove the leading and trailing spaces Set @strValue = Rtrim(Ltrim(@strValue)) --Loop through and remove more than one spaces to single space. While CharIndex(' ',@strValue)>0 Select @strValue = Replace(@strValue, ' ', ' ') --Final output :) Select @strValue

Error Description :: An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) Solution 1: Go to, Start >> Programs >> Microsoft SQL Server 2005 >> Configuration Tools >> SQL Server 2005 Surface Area Configuration >> Surface Area Configuration for Services and connections. Within this check whether "Local and remote connections" is choosen. If not choose it :) Solution 2: Check this URL http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=192622&SiteID=1 :) Technorati tags: SQL Server 2005

Microsoft announced a “ BlogStar ” contest last year and the winners were announced in the first week of November 2006. How do I know that I am one among the winners? On November 5 or 6th I got a call from one Ms. Bharathi claiming to be working in Microsoft. The number from which she called is 91-80-65605725. She said that I have won a prize and needed my size and full contact address to ship a jerkin. As the line wasn't clear I couldn't hear properly for what exactly is this gift for? But at that time Blogstar was the only Microsoft competition I was participating so I presumed it to be that. So, you got a call from Microsoft employee and hope you have received your prize as well! What else are you asking for? Excuse me :) As the female's communication was not that professional I thought its some spam caller and asked her to mail me the reason for requesting my contact address so that I can communicate my address back to her official ID. That said, it’s almost two months ...

Due to my enormous presence in the online world, Google has decided to dedicate a separate home page for me. You can check it out in the following url : http://googlemyway.com/Vadivel Technorati tags: Google , GoogleMyWay

This query returns those tables which satisfy the below two conditions: 1. Tables that do not contain any Foreign Key referencing other tables. 2. Tables that are not referenced by other tables using foreign key constraints. Solution: Till SQL Server 2000 days we used to write the below scripts [This still works with SQL Server 2005 also]. Select [name] as "Orphan Tables" from SysObjects where xtype='U' and id not in ( Select fkeyID from SysForeignKeys union Select rkeyID from SysForeignKeys ) Solution which works only with SQL Server 2005: Method 1: Select [name] as "Orphan Tables" from Sys.Tables where object_id not in ( Select parent_object_id from Sys.Foreign_Keys union Select referenced_object_id from Sys.Foreign_Keys ) Method 2: Select ST.[Name] as "Orphan Tables" from Sys.Foreign_Keys as SFK Right Join Sys.Tables as ST On ST.object_id = SFK.parent_object_id Or ST.object_id = SFK.referenced_object_id Where SFK.type is null Technorati tags: SQ...

This seems to be one another frequently asked question in the discussion forums. So thought would write a small post on this today. With the help of built in SQL Server functions we can easily achieve this in one single T-SQL statement as shown below. Select Day(DateAdd(Month, 1, '01/01/2007') - Day(DateAdd(Month, 1, '02/01/2007'))) Generalized Solution: We can generalize it by creating a "User defined Stored Procedure" as shown below: Create Function NumDaysInMonth (@dtDate datetime) returns int as Begin Return(Select Day(DateAdd(Month, 1, @dtDate) - Day(DateAdd(Month, 1, @dtDate)))) End Go Test: Select dbo.NumDaysInMonth('20070201') Go Technorati tags: SQL Server , SQL Server 2005

The below queries would list down the tables which doesn't have Primary Key in it. In SQL Server 2000 : Solution 1: Select Table_name as "Table name" From Information_schema.Tables Where Table_type = 'BASE TABLE' and Objectproperty (Object_id(Table_name), 'IsMsShipped') = 0 and Objectproperty (Object_id(Table_name), 'TableHasPrimaryKey') = 0 Solution 2: SysObjects :: Contains one row for each object that is created within a database, such as a constraint, default, log, rule, and stored procedure. No prizes for guessing 'U' refers to user tables, and 'PK' refers to Primary Keys :) Select [name] as "Table Name without PK" from SysObjects where xtype='U' and id not in ( Select parent_obj from SysObjects where xtype='PK' ) SQL Server 2005: Catalog views return information that is used by the Microsoft SQL Server 2005 Database Engine. We recommend that you use catalog views because they are the most general int...

As you might be knowing the system stored procs would be prefixed with "SP_". If we prefix "sp_" in our user-defined stored procedure it would bring down the performance because SQL Server always looks for a stored procedure beginning with "sp_" in the following order: 1) Master DB, 2) The stored procedure based on the fully qualified name provided, 3) The stored procedure using dbo as the owner, if one is not specified. So, when you have the SP with the prefix "sp_" in the DB other than master, the Master DB is always checked first, and if the user-created SP has the same name as a system stored proc, the user-created stored procedure will never be executed. For example, Let's say that by mistake you have named one of your user defined stored procedure as "sp_help" within the database! Create proc sp_help as Select * from dbo.empdetails Now when you try executing the stored procedure using the below script you would re...

There were few questions regarding "Passing table names as parameters to stored procedures" in Dotnetspider forums. I don't feel this to be a write way of coding. Still many persons are asking similar questions in the forums thought would write a post on "SP_EXECUTESQL()" Vs "EXECUTE()". Sample SP to pass table name as parameter: Create proc SampleSp_UsingDynamicQueries @table sysname As Declare @strQuery nvarchar(4000) Select @strQuery = 'select * from dbo.' + quotename(@table) exec sp_executesql @strQuery -------- (A) --exec (@strQuery) ---------------------- (B) go Test: Execute dbo.samplesp_usingdynamicqueries 'EmpDetails' In the above stored procedure irrespective of whether we use the line which is marked as (A) or (B) it would give us the same result. So what's the difference between them? One basic difference is while using (A) we need to declare the @strQuery as nvarchar or nchar. (i) It would throw the below error if we ...

Let me start by saying, I have never taken a Microsoft exam previously. I was having a "Microsoft Exams 100% Discount Coupon" which was valid till 31-Dec-2006 so thought of utilizing it. I prepared well for 70-431 Microsoft SQL Server 2005 - Implementation and Maintenance paper and today morning at 10.45 AM I took up the online test @ NIIT Adyar. I am glad that I have cleared it with a good score of 982 (out of 1000). Now I am a " Microsoft Certified Technology Specialist " :) There were 52 questions and out of which approximately 15 where simulation questions (that was where I spent most of my time). Though the score might look big, I need to accept that I answered 20 to 30% 0f questions without much of confidence. Hope the choices I made where accidently correct :) I was bit tensed in the morning till the time I answered the first question. Reason being, couple of my friends know that I am going to take up the test today. I was afraid what would they think if I f...

As per RBI regulation I guess all banks should have a "Do Not Distrub Me" or "Do not call me" :) web page inorder to value customers privacy. I get atleast couple of telemarketing calls a day from one bank or other. Its really irritating and i was looking for a way to avoid them totally. Only then i came to know about these "Don't call registeration pages" for various banks. Looooong Live RBI :) :) 1. ICICI Bank - As per the site itseems it would take 15 days for the number to be removed from the telemarketing list! 2. Citibank -- I didn't find the time frame in this site. 3. Deutsche bank -- As per the site itseems it would take 30 days for the number to be removed from the telemarketing list! 4. Standard Chartered Bank -- As per the site itseems it would take 30 days for the number to be removed from the telemarketing list! 5. HDFC Bank -- As per the site itseems it would take 45 days for the number to be removed from the telemarketing list!...

First I suggest you to go through my earlier article on subject "Delete VS Truncate" here . Truncate is also a logged operation, but in a different way. It logs the deallocation of the data pages in which the data exists. Let us create a dummy table for understanding this example: Create Table dbo.TruncateTblDemo (Sno int) Go Insert few records into it: Insert into dbo.TruncateTblDemo values (1) Insert into dbo.TruncateTblDemo values (2) Insert into dbo.TruncateTblDemo values (3) Go You could see that the table has 3 records in it: Select * from dbo.TruncateTblDemo Execute a truncate statement: Truncate Table dbo.TruncateTblDemo After the above statement you can't retrieve the data back because it is an explicit transaction . Unless or until you have set Implicit_Transactions to off . That is, Internally sql would have taken our above truncate statement as follow: Begin Tran Truncate Table dbo.TruncateTblDemo Commit Tran Select * from dbo.truncatetbldemo Getting back data...

Check out this article in computerworld.com . May be after this atleast we need to take our company policies very seriously :) How many of you guys are deleting unwanted mails from your official mailbox as per your company policy? Technorati tags: Microsoft

Read this first: 1. This has no real time usage. So try this out only when you are free :) 2. This script was just done for fun and nothing more. 3. Before executing this script change your result display mode to 'Text' (Ctrl + T) 4. There are easier way of doing the same thing. Just to make it look complex I have done this way :) Code snippet Starts here: Set nocount on Declare @TblLayout table([ID] int Identity, Canvas Char(75)) Insert into @TblLayout Select Replicate (Convert(Varchar, 0x7E), 75) Insert into @TblLayout Select Replicate (Convert(Varchar, 0x7E), 75) Insert into @TblLayout Select Replicate (Convert(Varchar, 0x7E), 75) Insert into @TblLayout Select Replicate (Convert(Varchar, 0x7E), 75) Insert into @TblLayout Select Replicate (Convert(Varchar, 0x7E), 75) Insert into @TblLayout Select Replicate (Convert(Varchar, 0x7E), 75) Insert into @TblLayout Select Replicate (Convert(Varchar, 0x7E), 75) Insert into @TblLayout Select Replicate (Convert(Varchar, 0x7E), 75) Inser...

Offlate, many of my friends where talking about this problem. It seems they ask this question frequently in Microsoft Interviews :) I remember asking this question to freshers in 2005! Just thought I would refresh my knowledge also on this :) So here are few samples which I tried for your reference. Method 1: Using intermediate temp variable int intNumOne = 1, intNumTwo = 2; int intTempVariable; //Swapping of numbers starts here intTempVariable = intNumOne; intNumOne = intNumTwo; intNumTwo = intTempVariable; Response.Write("Value of First Variable :: " + intNumOne.ToString() + "<br>"); Response.Write("Value of Second Variable :: " + intNumTwo.ToString() + "<br>"); Method 2: Without using Temp Variable and by using 8th standard Mathematics :) int intNumOne = 11, intNumTwo = 22; //Swapping of numbers starts here intNumOne = intNumOne + intNumTwo; intNumTwo = intNumOne - intNumTwo; intNumOne = intNumOne - intNumTwo; Response.Write(...

In this post let me explain about the three different types of Operations one can do with Partitions. They are: 1. Split Partition 2. Merge Partition 3. Switch Partition (Important of the lot) Before reading further, make sure that you have read my earlier posts. That is, this and this . Split Partition For splitting a partition we need to make use of “Alter Partition Function” syntax. So in our existing “Partition function” lets create a new range with boundary value “Jan 01, 1970”. Alter Partition Function PF_DOB_Range() Split Range ('01-01-1970') Now if we execute this code snippet it would throw an error something like this: Msg 7707, Level 16, State 1, Line 1 The associated partition function 'PF_DOB_Range' generates more partitions than there are file groups mentioned in the scheme 'PS_DOB_1'. So the way to split a partition is: Step 1: Create a new Filegroup (if at all already you don’t have an extra filegroup) Step 2: Make use of that Filegroup while al...

Lets assume that we have table which contains records of our company transaction starting from the date when our company was started 15 years ago! Hope you would understand that the table would have hell a lot of data as it would be holding 15 years of data. But effectively we might be using only last 2 months or 1 year data at the max (very frequently). For each query, it would be processing through this huge data. Bottomline as the table grows larger the performance would go for a toss, also scalabiity and managing data would also be difficult. Hope I have made the point clear. With the help of partitioning a table we can achieve a great level of performance; also managing tables easier. In our case, one of the way to increase the performance would be to “Partition” the data on a yearly basis and stored on a different filegroup (SQL 2005 allows you to partition tables based on specific data usage patterns using defined ranges or lists). For further theoritical knowledge on this subje...

It’s an interesting find. I got few mails asking me suggestions on the way to handle situations where “the application needs to be updated with the latest code base”. I was preparing a blog post something like this: 1. Normally sites are deployed in Web farm scenarios. If your app is also deployed in a web farm then it’s better to bring one server down update the code base there while all the user request would be served by the other servers in the farm. This way the downtime of the application would be almost zero. 2. If at all your application is deployed on a single server then either you need to face the downtime :) or temporarily create another virtual directory with the old code base. This Virtual directory would be functional till the time you update the actual directory with your latest code base. There would be some negligible amount of downtime here. 3. If you can’t create a new virtual directory for some reason! Then create a static page (“SiteDownForMaintanance.htm”) and ba...

PDF (Portable Document Format) reader is a very important software one needs. As now-a-days most of the product user manual, eBooks, visa application forms etc., are in PDF format. Today’s computers almost always come with Adobe PDF reader installed by default. Till few weeks back I was also using it without much satisfaction!! No doubt Adobe PDF reader is a great product but I hate it for the following points: 1. I feel that Adobe PDF reader software is really bulky. 2. Loading time of PDF document is unnecessary in Adobe reader 3. Installation of Adobe PDF reader takes at least >= 5 minutes. For past few weeks I am fiddling with another PDF reader called “ Foxit Reader 2.0 ”. In my experience with this new reader, I haven’t found any of the above mentioned disadvantages which I have with Adobe PDF reader . Let me explain those 4 points in detail now: i) I feel that Adobe PDF reader software is really bulky. First of all downloading Adobe PDF reader isn’t an easy joke :) it takes ...