Vadivel's blog

There are quite a lot of DMV's which have been introduced along with SQL Server 2005. One such is sys.dm_db_missing_index_details. Using this DMV we can identify what all indexes SQL optimizer is expecing in our tables. Query: select * from sys.dm_db_missing_index_details Fields to note: equality_columns, inequality_columns, included_columns, statement So by querying this DMV and having a look at these 4 columns we can decide on the types of indexes which needs to be created for our tables. Each of this column has a meaning. Either find it yourself :) or drop a line in the comment section. One important point to keep in mind is the result you see out of running the above DMV is just a 'suggestion' on the way to improve the performance of your query. But its not 100% guaranteed that on creating all these indexes would help. We cannot access the DMV using accounts which doesn't have permission for 'View Server State'. I would write more on these in upcoming post...

I am glad to know that I have been renominated as a MVP in SQL Server category this year (2007 - 2008). Thanks everyone for your support and encouragement!

When queries / SP's hits those tables where appropriate indexes are not there then the database engine would automatically create indexes to be used by its execution plan. The hitch is those dynamic indexes would not be reused by the engine the next time when it hits the same table / column. i.e., it would create another dynamic index each time when it hits the same table / column. That said, normally till SQL Server 2000 I used to execute the below statement to list down those dynamically created indexes. So that i can analyze and create indexes appropriately. Select [name] from sysindexes where [name] like '_WA_%' I would write more on this shortly.

If you are connected to a remote desktop (Using Remote Desktop Connection) and for some reason you want to use "Ctrl" + "Alt" + "Del" it would open up the local machines dialog box only. In order to initiate the dialog box of the remote machine to which you are connected to make use of "Ctrl" + "Alt" + "End" Hope this helps!

Prior to SQL Server 2005 if we want to find out the list of recently run queries we need to depend on SQL Profiler. Now in SQL Server 2005 the life has become more easier(!). With the help of of an Dynamic Management Views (DMV) and a table valued function we can list the required result. Points to note: 1. sys.dm_exec_query_stats -- This DMV returns aggregate performance statistics for cached query plans. The view contains one row per query statement within the cached plan, and the lifetime of the rows are tied to the plan itself. When a plan is removed from the cache, the corresponding rows are eliminated from this view. 2. sys.dm_exec_sql_text -- This table valued function returns the text of the SQL batch that is identified by the specified sql_handle Solution: Select dmStats.last_execution_time as 'Last Executed Time', dmText.text as 'Executed Query' from sys.dm_exec_query_stats as dmStats Cross apply sys.dm_exec_sql_text(dmStats.sql_handle) as dmTex...

This is one of the other very common question which I get from many of my blog readers / dotnetspider users. Hi Vadivel, I have created a fresh login in my SQL Server 2005 database. Now I want to grant that newly created login permission to execute any / all stored procedure within that database. Can you tell me how to do this in one shot? As of now, I am writting GRANT statement for all individual Stored procedures name manually :( Regards xxxxxx Find above one of the recent mail which I received from one of dotnetspider user. I thought I would write a sample and blog it for benefit of all those people who are having similar requirement. So is this post :) Solution: Declare @strUserName sysname Set @strUserName = 'Support' Select 'Grant exec on [' + Routine_Schema + '].[' + Routine_Name + '] TO [' + @strUserName + ']' from Information_Schema.Routines Where Routine_Type = 'Procedure' Now in the result pane the GRANT statement for all stor...

In this post I have given one of the way to find out the missing numbers within a table. Please note the you need SQL Server 2005 to execute this example and test it yourself. Sample table creation Create table tblFindGaps ( Sno int not null ) Go Populate dummy records in the table: Insert tblFindGaps values (1) Insert tblFindGaps values (10) Insert tblFindGaps values (3) Insert tblFindGaps values (5) Insert tblFindGaps values (9) Insert tblFindGaps values (11) Insert tblFindGaps values (15) Insert tblFindGaps values (18) Insert tblFindGaps values (22) Insert tblFindGaps values (100) Go Solution to find the missed out numbers: Declare @intMaxNum int Select @intMaxNum = max(Sno) from tblFindGaps; With tempData (result) as ( Select distinct FG.Sno + 1 from tblFindGaps FG where not exists ( Select 1 from tblFindGaps FGP where FGP.Sno = FG.Sno + 1 ) and FG.Sno Union All Select TD.result + 1 from tempData TD where not exists ( Select 1 from tblFindGaps FGP where FGP.Sno = TD.result + 1 ) an...

I am working on SQL Server 2005 for quite sometime now. For past couple of weeks I am facing a strange error often but not always!! Refer the screenshot below to know the actual error: Database 'msdb' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details. (Microsoft SQL Server, Error: 945) My system configuration: I am using Windows XP Media center edition with SP2 and 1 GB RAM. I have SQL Server 2005 (version 9.00.1399.00) What's the solution? I came across this KB article - http://support.microsoft.com/kb/899436 . As per the KB article it looks like this error occurs because of a ACL issue here. If it's an ACL issue I presume that SQL 2005 should not work for me always. I am wondering how it works for me once I restart my laptop couple of times. Bottomline is I haven't yet found a solution for this. If at all you have run into this issu,e and have solved it, do write back to me.

Today I just want to write a sample explaining the workaround for 'Divide by zero error encountered.' error in SQL Server. Sample Table Structure for demo purpose Create Table dbo.TestDivideByZero ( WebSite varchar(50), NumOfHits int, Income int ) Go Insert dummy records Insert into dbo.TestDivideByZero values ('a.com', 100, 20) Insert into dbo.TestDivideByZero values ('b.com', 10, 0) Insert into dbo.TestDivideByZero values ('c.com', 300, 25) Insert into dbo.TestDivideByZero values ('d.com', 1300, 225) Go Query to produce 'Divide by zero error encountered.' error Select WebSite, NumOfHits / Income from dbo.TestDivideByZero Go This would throw the below error: Msg 8134, Level 16, State 1, Line 1 Divide by zero error encountered. Workaround Make use of NULLIF function. Like, if the value is 0 then return NULL. For better understanding execute the below query and see it for yourself. Select WebSite, NumOfHits / NullIf(Income,0) as ColumnNam...

Today I was trying to join two table each having approximately 5 Lakhs records in one of my SQL Server 2005 database. I was trying to find the count of some field when this arithmetic overflow error was thrown. Arithmetic overflow error converting expression to data type int. I understood that the calculation has exceeded the maximum INT range. For better understand on this error check out this SQL Books online article and/or this KB article . The work around which did the trick for me is, instead of "Count" I changed it as "Count_big".

I was going through the "Top 20 things Programmers says to Testers!" . Its really funny and brought back lot of memories. Out of the 20, i need to admit I use these excuses very often :) 18. "It worked yesterday!" 12. "You must have a wrong version" 1. "It works on my machine" The other excuses which I use or heard people using it are: 1. The issue isn't reproducable. 2. It's designed to work that way! 3. You know we have done it as a value add ourself! there is no spec for this. 4. It's been fixed long time back. May be you are looking into a wrong version! 5. You know its not because of our layer! It's an error in the "Service Layer" which another team needs to look at. 6. I don't think its a show stopper. How about fixing it in the next release? Do you have a say on this?

Today I thought of trying my luck with Zoho Writer and write my feedback about that. So is this post :) Just a thought: Actually I am surprised that almost all of zoho's product have different UI's. Won't it look good if they create a uniform look and feel across their products? Most of the Products have Single Sign On facility: It's really nice to see that they have done SSO (single sign on) authentication for their products. It means, previously I have registered with Zoho Sheets and now to work with Zoho Writer I can make use of the same login. How to enable SSO for a zoho product? 1. First login to any of their product (zoho sheet, zoho writer, zoho show, zoho project, zoho creator, zoho planner, zoho wiki and zoho chat). Yes you need to register once in any of their product. 2. In the header of the application you can see "My Account" link. Click on it to see a screenshot as shown below for your account information. 3. Now lets assume that you need to en...

<%@ Page language="c#" Codebehind="WebForm1.aspx.cs" AutoEventWireup="true" Inherits="Sample2003Application.WebForm1" %> AutoEventWireup -- It's a boolean field. By default in VS.NET 2003 it would be "false". More over this attribute is applicable only for applications which are created via VS.NET 2003. If it's set to "True", the ASP.NET runtime does not require events to specify event handlers like Page_Load etc., Once you create a new webform in VS.NET the 'AutoEventWireup' attribute of that page would be false. Open the code behind file and check out the 'InitializeComponent' it would be something like this: private void InitializeComponent() { this.Load += new System.EventHandler(this.Page_Load); } Within Page_Load even print a message into the screen. private void Page_Load(object sender, System.EventArgs e) { Response.Write("We are in Page Load event."); } This message "We ar...

Normally for exporting data from SQL Server to Excel one would use DTS (SQL 2k) or SSIS (SQL Server 2005). For some reason if at all you want to do it via query read on: Step 1: Execute the code snippet Exec sp_configure 'show advanced options', 1; Go Reconfigure; Go Exec sp_configure 'Ad Hoc Distributed Queries', 1; Go Reconfigure; Go Step 2: Create the excel file and then add the headings in the .xls file. [Important] The heading should be the same as that of the table columns. Insert into Openrowset ('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=c:\VadivelTesting.xls;' , 'Select * from [ProductInfo$]') Select ProductID, ProductName from dbo.tblProducts Points which might interest you: 1. As long as the file is within your C: drive this sample would work. If at all your database is in a different machine from that .xls file you need to change Database=c:\VadivelTesting.xls; to UNC path. For example, Database=\\Servername\shareName (And need ...

I came to know about ToonDoo from D. Rajendran (who is a Product Manager in Jambav) and I am glad he introduced this to me. I created couple of toons and have blogged about it here . I really liked this toonDoo concept and i am playing with it for past 2 days like a kid :) Actually I wanted to write about "Jambav" in June '06 itself. But as I was bit busy couldn't do that (believe me i was busy only lol). Now somebody has beaten me and has interviewed "Rajendran Dandapani" about him and Jambav . He has spoken about "What is Jambav and why that name?", "about the motivation to start Jambav", "About him and his responsibilities", "Key technologies used", "their business model" etc., I strongly recommend everybody to go through it without fail. That said, there are few things which I am not sure about this tool ... 1. How much load can their server take? Because very often I find that the server is down (more t...

Jambav (backed by Adventnet ) has launched another cool tool few days back by name " ToonDoo " (Cartoon Strip Creator). You can check my really exciting works below :) [ Updated the copy: As usual there were lots of typo in my Indian Cricket toon which I have changed it now]. BTW, at present there is a contest at ToonDoo . Create any number of toon and show the world what you are thinkinng abt it. What are you waiting for? Go get registered and start playing with it. Techcrunch has a good write up on ToonDoo here Technorati tags: Jambav , Cricket , ToonDoo , Kids

Most of us would be very much familiar with "Autocomplete" feature of various browsers. After turning on that feature if you try filling any textboxes the browser provides you with the values which you have already typed in that field before. What if the "autocomplete" feature is turned ON in the end users browser but you don't want the data to be catched by the browser? Its quite simple. All we need to do is make use of the attribute "autocomplete" within the pages form tag. Example 1: Disabling Autocomplete for the whole form 1. Create a sample web form and replace that default form tag with the code snippet below <form id="form1" runat="server" autocomplete="off"> <asp:TextBox ID="txtOne" runat=server></asp:TextBox> <asp:TextBox id="txtTwo" runat="server" ></asp:TextBox> <asp:Button ID="btnGo" runat="server" Text="Submit" /> ...

If you want to restart a computer which you have connected via Windows Remote Desktop do this. Start >> Run >> shutdown -r -f The machine would be restarted after 30 seconds automatically There are lots of other options available with shutdown command. You can view all the available options by typing " shutdown /? " in the command window.

Error Message: The web application you are attempting to access on this web server is currently unavailable. Please hit the "Refresh" button in your web browser to retry your request. Administrator Note: An error message detailing the cause of this specific request failure can be found in the system event log of the web server. Please review this log entry to discover what caused this error to occur. Error in Event Viewer: aspnet_wp.exe could not be started. The error code for the failure is 80004005. This error can be caused when the worker process account has insufficient rights to read the .NET Framework files. Please ensure that the .NET Framework is correctly installed and that the ACLs on the installation directory allow access to the configured account. OR aspnet_wp.exe could not be launched because the username and/or password supplied in the processModel section of the config file are invalid. Solution: Check out this microsoft support article >> http://suppor...

Fed up with Bread - Jam and Curd Rice, today i wanted to eat Omelette. Interesting part is I wanted to cook it myself :) So in the first picture you see all the items which are needed for preparing an Omelette. When I had a closer look at the eggs I see that almost all the eggs are broken. But believe me when I bought it couple of days back it was in perfect condition! I was wondering whether the eggs have become rotten or pretty old to consume! I tried taking an egg and break it but couldn't break it at all :) Since I have kept in the freezer all the eggs have frozen and looked like a iron ball :) After trying for few minutes of trying i removed the shell of the egg and then kept that iron ball :) into a bowl and placed it within Oven. I heated it for 1 minute and checked. It melted only to a limit. So i just set it for another 2 minutes and checked it later. It has melted but the part of the egg white has become a Omelette :( I didn't leave it there. I took the bowl out of ...