Posts Tagged ‘Tips’

Stored procedures to retrieve directory listing – Undocumented XPs

12.05.2011 9 comments

You can use xp_subdirs and xp_dirtree undocumented stored procedures to retrieve a list of child directories under a specified parent directory from file system.

— © 2011 – Vishal (

EXEC master..xp_subdirs 'C:\Inetpub'

xp_subdirs – lists only directories which are direct children of the specified parent. In this case it returns directories which are directly under 'C:Inetpub' as below:










(6 row(s) affected)

If you need to retrieve the children recursively, you will need to use xp_dirtree.

— © 2011 – Vishal (


EXEC master..xp_dirtree 'C:\Program Files\Microsoft.NET'

This returns all the children of 'C:Program FilesMicrosoft.NET' recursively, and their level from parent:

subdirectory                depth

——————–          ———–

ADOMD.NET                         1

100                               2

Resources                         3

1033                              4

110                               2

Resources                         3

1033                              4

Primary Interop Assemblies        1

RedistList                        1


(9 row(s) affected)

You can also restrict the number of level retrieved using:

— © 2011 – Vishal (


EXEC master..xp_dirtree 'C:\Program Files\Microsoft.NET', 2

This will only retrieve children of 'C:Program FilesMicrosoft.NET' whose level <= 2

subdirectory               depth
——————–             ———–
ADOMD.NET                         1
100                               2
110                               2
Primary Interop Assemblies        1
RedistList                        1

(5 row(s) affected)

Note: These procedures are undocumented and unsupported by Microsoft and they may *disappear* from newer SQL Server versions!

Hope This Helps!


Accessing Registry using XPs (contd..) – TSQL

11.05.2011 No comments

We can use the xp_regwrite undocumented extended stored procedure to write new entries to Windows registry.

You need to provide the key path, A new key will be created if it does not exist.

The following code will add a new entry in startup programs list in registry:

— © 2011 – Vishal (

EXEC master..xp_regwrite
     @rootkey     = 'HKEY_LOCAL_MACHINE',
     @key         = 'SoftwareMicrosoftWindowsCurrentVersionRun',
     @value_name  = 'Solitaire',
     @type        = 'REG_SZ',
     @value       = 'C:Program FilesMicrosoft GamesSolitaireSolitaire.exe'


Output returned by the procedure will be '(0) rows affected', unless it encounters an error.


To verify the entry, you need to open Registry Editor and traverse to specified path.




Or you can also use xp_regread or xp_instance_regread to verify the entry.


You can delete an registry entry using xp_regdelete.


— © 2011 – Vishal (

EXEC master..xp_regdeletevalue
     @rootkey     = 'HKEY_LOCAL_MACHINE',
     @key         = 'SoftwareMicrosoftWindowsCurrentVersionRun',
     @value_name  = 'Solitaire'


Output returned by the procedure will be '(0) rows affected', unless it encounters an error.


Hope This Helps!


Accessing Registry using XPs – TSQL

09.05.2011 1 comment

You can use the undocumented extended stored procedure xp_regread to access registry entries using T-SQL.


xp_regread    @rootkey      = N'rootkey',
              @key          = N'key',
              @value_name   = N'value_name',
              @value        = @outputValue OUTPUT


For example, below code returns "C:Program Files" on my system :

DECLARE @returnValue NVARCHAR(100)
EXEC   master.dbo.xp_regread
       @rootkey      = N'HKEY_LOCAL_MACHINE',
       @key          = N'SOFTWARE\MicrosoftWindows\CurrentVersion',
       @value_name   = N'ProgramFilesDir',
       @value        = @returnValue output
SELECT @returnValue


xp_regread ca only be used to retrieve a single value. If you need to retrieve multiple values, you will need to use xp_instance_regenumvalues, which returns all values under a specified key.


For example, To retrieve a list of start-up programs from registry we will use:


EXEC   master..xp_instance_regenumvalues
       @rootkey = N'HKEY_LOCAL_MACHINE',
       @key     = N'SOFTWARE\Microsoft\Windows\CurrentVersion\Run'


this will return all entries under HKLMSoftwareMicrosoftWindowsCurrentVersionRun:




Hope This Helps!


Multiple Server Query Execution – Management Studio

07.05.2011 2 comments

SQL Server Management Studio 2008 introduces a new feature, Multiple Server Query Execution. This feature enables you to execute a query against multiple servers.

To use this feature, you need set up at least one registered server in Management Studio. To learn how to set up a registered server go here. Optionally, you can also create multiple registered servers groups.

For example, I have setup registered servers in my local system as below:


You can bring up the registered servers windows by going to View > Registered Servers.

To create a new query to execute against multiple servers, Right Click on any Server Group in Registered Servers windows and select "New Query".


This will launch a new query window. You can identify the number of servers connected from the Status Bar.


You can execute any query here, the query will be executed against each of the four servers which are connected, and the results will be combined in a single result set.


You are not limited to only SELECT statements, you can also execute other statements. for example, INSERT will work as long as all servers have the Database/Schema/Table you are trying to insert into:


The above statement works fine on my system since I have a TestDB and TestTable  available on all my SQL Instances. The database drop down list displays ONLY the common databases for connected servers.

However, a statement which returns results with different columns cannot be executed.

for example, below SELECT will return error, since the results that are returned by sys.databases has different number of columns for 2005/2008/DENALI.

— © 2011 – Vishal (

SELECT      *
FROM        sys.databases



Sql08(********): (8 row(s) affected)


Denali(********): An error occurred while executing batch.

Error message is: The result set could not be merged because

the result schema did not match the schema from the first responding server.


Sql05.1(********): An error occurred while executing batch.

Error message is: The result set could not be merged because

the result schema did not match the schema from the first responding server.


Sql05(********): An error occurred while executing batch.

Error message is: The result set could not be merged because

the result schema did not match the schema from the first responding server.


Denali(********): (5 row(s) affected)


Sql05.1(********): (8 row(s) affected)


Sql05(********): (5 row(s) affected)


To avoid this you can change the Query Results options for Multi-server by going to Tools > Options in Management Studio.


From the options you can also change the "Server Name" columns that is prefixed to result set.




Setting the "Merge results" to false will return the result sets individually when you execute a multiple server query as below:


MultiServer MultiSet


Hope This Helps!


Recently Executed Queries – Using DMVs/DMFs

06.05.2011 No comments

To find the queries that were executed recently using the sys.dm_exec_query_stats DMV.


SELECT sql_handle, creation_time, last_execution_time
FROM   sys.dm_exec_query_stats
WHERE  last_execution_time > '2011-05-06 06:45:00.000'


The above statement will return the performance statistics for cached query plans. sys.dm_exec_query_stats contains the sql handle of the statement which was executed. To retrieve the actual query text, you need to use a DMF – sys.dm_exec_sql_text as follows:




This functions takes the sql/plan hadle as input and returns the sql text. CROSS APPLY can be utilized add query text to the output:


SELECT        SQLTEXT.text, STATS.last_execution_time
FROM          sys.dm_exec_query_stats STATS
CROSS APPLY   sys.dm_exec_sql_text(STATS.sql_handle) AS SQLTEXT
WHERE         STATS.last_execution_time > '2011-05-06 19:15:00.000'
ORDER BY      STATS.last_execution_time DESC




Hope This Helps!


Create SQL Server Alias – CliConfg.exe

05.05.2011 No comments

If you are working with a number of SQL Server instances (or have really complex server/instance names!), you may already be familiar with aliases and using it to connect to instances. We create aliases for instances using SQL Server Configuration Manager.


However, SQL Server Configuration Manager is not shipped with OS and it will not be available on client machines.

To define an alias on a client machine, you can use the classic SQL Server Client Network Utility. You can launch this by going to Start > Run > CliConfg.exe


CliConfg.exe provides same functionality with a slightly different interface.


If you check "Dynamically determine port", make sure that SQL Server Browser is running on the server.

Hope This Helps!


SQL Server has been running for …

04.05.2011 1 comment

Recently on one of our production servers, which is a two-node Active/Passive cluster.  A failover had occurred. I wanted to find the exact time when this happened. You can get this information from SQL Server Log.

Another quick way is to check the creation time of  “tempdb” database. This will be same as SQL Server Service start time since “tempdb” is recreated each time SQL Server starts.

You can check the creation time using:

SELECT create_date

FROM   sys.databases

WHERE  name = 'tempdb'


You can also format the output using:



       AS [Days],

       ((DATEDIFF(MINUTE, create_date, GETDATE())/60)%24)

       AS [Hours],

       DATEDIFF(MINUTE, create_date, GETDATE())%60

       AS [Minutes]

FROM   sys.databases

WHERE  name = 'tempdb'


This returns below on my local system:




Hope This Helps!


Index was outside the bounds of the array. (Microsoft.SqlServer.Smo) – SQL Server ‘Denali’

03.05.2011 7 comments

Today morning, I read a post on Pinal’s blog. I tried to reproduce the issue, but was unable to do so. Instead, I ran into a different error:


However, this does not stop you from connecting.


You can see from the screen shot that I was able to connect to the Instance, despite the error message. Also, I can access the data without any issues.


I was a bit curious and also tried this on SSMS 2005. And guess what, the same error appears there:

4. Error From SSMS 2005

But the consequences are more “SEVERE” in SSMS 2005. You can access the Database, execute queries against it without any issues.

However, the Object Explorer tree does not expand.

5. Cannot Expand From Object Explorer

Connect Feedback Item :

Hope This Helps!


Inserting to a View – INSTEAD OF TRIGGER – SQL Server

02.05.2011 13 comments

If you have created a View in SQL which is based on a single table – the DML operations you perform on the view are automatically propagated to the base table.

However, when you have joined multiple tables to create a view you will run into below error if you execute a DML statement against the view:

Msg 4405, Level 16, State 1, Line 1
View or function 'ViewName' is not updatable because the modification affects
multiple base tables.

To avoid this error and make a view modifiable you need to create Triggers on the view. These triggers will be used to ‘pass’ the changes to base tables.

You need to create a trigger on a view for all operations you need to perform. For example, if you need to perform INSERT operations on a view you need to create a INSTEAD OF Trigger for ‘passing’ the changes to base tables. If you also need to perform a UPDATE/DELETE operation, you also need to create additional INSTEAD OF Triggers for UPDATE/DELETE.

For example, let’s consider following view definition:
— © 2011 – Vishal (
CREATE TABLE [dbo].[Table1]
(      [ID] [INT] NULL,
       [Name] [VARCHAR](20) NULL

CREATE TABLE [dbo].[Table2]
(      [ID1] [INT] NULL,
       [Name1] [VARCHAR](20) NULL

CREATE View [dbo].[View1]

SELECTTable1.ID, Table1.Name, Table2.Name1
FROM   Table1
INNER JOIN    Table2
              ON Table2.ID1 = Table1.ID

Now, if you try to insert to [View1], you will run into above error. To enable INSERTs on [View1], we need to create INSTEAD OF Trigger as below:

— © 2011 – Vishal (
CREATE TRIGGER [dbo].[Trig_Insert_Employee]
ON [dbo].[View1]


That’s all folks. Now, you can execute INSERT statement against the view and it will INSERT the data to the base tables.

INSERT INTO View1 Values (1,'Gandalf','The Grey')

(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)

Verify the base tables:

Hope This Helps!


TSQL – Select/Skip Top/Bottom N Rows – “Denali” Way

30.04.2011 1 comment

“Denali” introduces Ad-hoc query paging in which you can specify range of rows returned by a SELECT statement. This can be handy when you want to limit number of returned by the statement.

This is implemented in ORDER BY clause. two new keywords are added to ORDER BY clause:

1. OFFSET : Skips top N number of rows from the result set
2. FETCH : Fetch next N number of rows from the result set (after skipping rows specified by OFFSET)

Let’s see it in action:

1. This will remove first 5 rows from the result:
— © 2011 – Vishal (
— Remove First 5 Rows from the Result Set
SELECT ProductID, Name, Color
FROM Production.Product

2. Remove first 5 rows from the result, and return next 10 rows:
— Remove First 5 Rows from the Result Set,
— Return Next 10 Rows
SELECT ProductID, Name, Color
FROM Production.Product

3. Return bottom 5 rows only:
— Return Bottom 5 Rows
SELECT ProductID, Name, Color
FROM Production.Product

For syntax and other options refer BOL : ORDER BY Clause (Transact-SQL)

Hope This Helps!