Vadivel's blog

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 < @intMaxNum Union All Select TD.result + 1 from tempData TD where not exists ( Select 1 from tblFindGaps FGP where FGP.Sno = TD

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.