Troubleshooting Connectivity with UDL files

I had a new SSIS package that was ready to deploy to production. It was pulling data from Oracle and this was the first package being placed into production that was using Oracle as a source. Arrangements had been made to have the Oracle OLE DB drivers installed on the production SQL Server as the performance was determined to be better that the OLE DB driver Microsoft provided at the time. On the first run of the package in production, the Oracle connection failed validation with a very useless error message, Connection failed validation. Why? The production server did not have any client tools to test connectivity as the system administrator had merely installed the drivers. As this was financial institution and the server was locked down, the SQL Server service account was setup with minimal rights. I sat down with the production DBA to troubleshoot the issue. Fortunately we were able to login interactively using the SQL Server service account. We created a UDL file to test whether a connection could be made to the production Oracle server. The first thing we checked was the Provider tab which verified that the Oracle OLE DB driver was indeed installed. We then attempted to create a connection and an error was raised. The error showed that the SQL Server service account did not have rights to the directory where the Oracle driver was installed and could not read the tnsnames.ora file. We contacted the system administrator to grant the directory rights to the SQL server service account and the connection was able to be made on the next test. The SSIS package ran successfully as well.

Let’s take a closer look at UDL files.

UDL files are useful to:

  • troubleshoot connectivity issues.
  • prove that a computer can connect to a database server.
  • determine if the OLE DB driver you need is installed on the machine.
  • generate a connection string.

 

A UDL file can be created two different ways. If you have the “Hide extensions for known file types” option turned off in explorer, the UDL file can be created directly from the Desktop. Right click on the Destop and select New->Text Document. Rename the new file test.udl. Windows will pop the warning below. Click Yes.

If you haven’t seen it before, here is the “Hide extensions for known file types” option.

The second way is to open Notepad and save an empty file to the Desktop named test.udl. The file icon should look like this on new versions of Windows.

Here is the older version of the icon that would appear on Windows XP/2003.

Double click the newly created icon and the Data Link Properties dialog will be displayed.

The Connection tab is ready to create a connection to a SQL Server by default. In order to create a connection using a different driver, click on the Provider tab and select the driver.

Now when the Connection tab is selected, notice how the UI has changed to provide the appropriate options for the new driver.

The Advanced tab allows additional connection properties to be set.

The All tab shows all of the properties that can be set for the chosen provider. There are properties on this tab that may not be in the documentation provide by the vendor.

Now that we have seen all this fancy UI magic the question is “What is a UDL file”? It is just a test file with a connection string. Windows launches the UI based on the file extension.

Let’s create a UDL using the SQL Server OLE DB driver with windows authentication, pointing to the AdventureWorks database and with an application name of “Test UDL”. Here are the results.

; Everything after this line is an OLE DB initstring

Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=AdventureWorks;Data Source=.\SQL2005;Application Name=Test UDL

The UDL been around for a while, is available on any windows box and is a quick light weight way to troubleshoot your connections.

Getting Drive Info, Part 4, the SSIS Script Component

In this installment of the Getting Drive Info series (Part 1, Part 2, Part 3), SSIS will be used to collect and save the drive information. SSIS provides multiple ways to accomplish this task. This installment will focus on using the Script Component to collect the drive information.

The Script component will be used as the source of a Data Flow where the DriveInfo class of the System.IO namespace will be used to collect the drive information. The DBA_Tools database and DriveInfo table from the earlier installments will also be needed.

Fire up BIDS and create a new project. Name the package something appropriate.

Drag a Data Flow task onto the Control Flow designer and name it DriveInfo.

Create a new Connection Manager that points to the DBA_Tools database on your SQL Server.

Bring the Data Flow designer into focus. Grab the Script Component from the Data Flow Transformations of the Toolbox and drag it onto the Data Flow designer. The Select Script Component Type dialog will appear. The Script Component can be a source of data, a transformer of data or a consumer of data. Choose Source, since this Script Component is going to provide drive information and click OK. Rename the Script Component Get DriveInfo.

Double click on the Script Component to open the editor. Now let’s add some output columns. Rename Output 0 to DriveInfo and add the columns:

  • Drive, data type string [DT_STR]
  • DriveSize, data type float [DT_R4]
  • FreeSpace, data type float [DT_R4]

Here are the column property details for the Drive column.

It is important to define the output columns first so they show up OutputBuffer object while the coding the script. The output buffer will be named DriveInfoBuffer in this case.

Now it’s time to add the code to get the drive information and send ti to the output buffer. Here is the code in VB.Net for SSIS 2005.

Imports System
Imports System.Data
Imports System.IO
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper

Public Class ScriptMain
    Inherits UserComponent

    Public Overrides Sub CreateNewOutputRows()

        For Each di As DriveInfo In DriveInfo.GetDrives()
            If di.IsReady And di.DriveType = DriveType.Fixed Then
                Me.DriveInfoBuffer.AddRow()
                Me.DriveInfoBuffer.Drive = di.Name
                Me.DriveInfoBuffer.DriveSize = di.TotalSize
                Me.DriveInfoBuffer.FreeSpace = di.TotalFreeSpace
            End If
        Next

    End Sub

End Class

Here is the code in C# for SSIS 2008.

Make sure to set the ScriptLanguage property to Microsoft Visual C# 2008 in BIDS 2008 before clicking the Edit Script… button and pasting in the code below.

using System;
using System.Data;
using System.IO;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;

[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
public class ScriptMain : UserComponent
{
    public override void PreExecute()
    {
        base.PreExecute();
        /*
          Add your code here for preprocessing or remove if not needed
        */
    }

    public override void PostExecute()
    {
        base.PostExecute();
        /*
          Add your code here for postprocessing or remove if not needed
          You can set read/write variables here, for example:
          Variables.MyIntVar = 100
        */
    }

    public override void CreateNewOutputRows()
    {
        /*
          Add rows by calling the AddRow method on the member variable named "<Output Name>Buffer".
          For example, call MyOutputBuffer.AddRow() if your output was named "MyOutput".
        */
        foreach (DriveInfo di in DriveInfo.GetDrives())
        {
            if (di.IsReady && di.DriveType == DriveType.Fixed)
            {
                DriveInfoBuffer.AddRow();
                DriveInfoBuffer.Drive = di.Name;
                DriveInfoBuffer.DriveSize = di.TotalSize;
                DriveInfoBuffer.FreeSpace = di.TotalFreeSpace;
            }
        }
    }
}

Now we need to add the destination to store the drive information. Grab the OLE DB Destination from the Data Flow Destinations of the Toolbox and drag it onto the Data Flow designer and name it Put DriveInfo. Connect the output of the Get DriveInfo Script Component to Put DriveInfo.

In the OLE DB Destination Editor, set the OLE DB connection manager to the DBA_Tools connection created earlier and pick the DriveInfo table from the table or view drop down.

Here is the create table statement for the DriveInfo table used in the earlier parts of the series.

CREATE TABLE dbo.DriveInfo
( Drive VARCHAR(255)
, DriveSize FLOAT
, FreeSpace FLOAT)

Now you are ready to start collecting and saving the drive information on your SQL Server. I will leave it to you to explore the DriveInfo object to see what other bits of information that may be useful to collect and store for the drives. The only limitation of this technique is that the package can only get the drive information on the server that the package is running on. The package would need to be deployed and run on every SQL Server where it is desried to collect the drive information. The last part of this series will address this limitation by implementing the collection of drive information using the WMI Task in SSIS.

Getting Drive Info Part 3, CLR

In the first two parts, (Part 1, Part 2), of the Getting Drive Info series the techniques to gather drive info with methods that will work on SQL Server 2000 were presented. Now it is time to move on to look at the options that the newer versions of SQL Server can use. In this article the drive info will be retrieved via the CLR. Andy Novick (Blog) wrote the code to accomplish this in his article, CLR Function to return free space for all drives on a server, on MSSQLTips.com. Security and environment set up were not covered in this article. Rather than reinvent the wheel, the code from the referenced article will be used. I will discuss security and the setup I created to run it .

The first attempt was to build and deploy to the master database. This resulted in the following error:

Msg 6522, Level 16, State 2, Line 1
A .NET Framework error occurred during execution of user-defined routine or aggregate “drive_info”:
System.Security.SecurityException: Request for the permission of type ‘System.Security.Permissions.SecurityPermission, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089′ failed.
System.Security.SecurityException:
at System.Security.CodeAccessSecurityEngine.Check(Object demand, StackCrawlMark& stackMark, Boolean isPermSet)
at System.Security.CodeAccessPermission.Demand()
at System.IO.Directory.GetLogicalDrives()
at System.IO.DriveInfo.GetDrives()
at UserDefinedFunctions.drive_info()

After getting the above error and not really understanding the ramifications of an unsafe assembly, I decide it was time to do some reading. I found that the book, A Developers Guide to SQL Server 2005, had an excellent discussion of the CLR and the different assembly security types. Now I was ready to create an environment for this function.

Since this was a function for DBAs to use for system monitoring, I created a new database named DBA_Tools and set the owner to sa. In order to let an Unsafe assembly execute, the TRUSTWORTHY option needs to be enabled. Turn on Trustworthy with this command.

ALTER DATABASE DBA_Tools SET TRUSTWORTHY ON

Make sure that the CLR server option is enabled, otherwise you can’t execute the CLR function. This can be accomplished with this script.

EXECUTE sp_configure 'show advanced options', 1
RECONFIGURE WITH OVERRIDE

EXECUTE sp_configure 'clr enabled', 1
RECONFIGURE WITH OVERRIDE

One thing that I did notice is having the CLR server option enabled or disabled had no impact on deploying the function. This is a great feature if you typically don’t want the CLR enabled all of the time. The CLR can be enabled by the script that runs drive_info() function and then be disabled immediately after.

Moving on to the Visual Studio setup, make sure the connection in the CLR database project points to the DBA_Tools database. Set the Assembly name property on the Application tab of the Solution property page to something more appropriate.

Set the Assembly Name

The CLR function performed as advertised after setting everything up correctly. The function was built in both Visual Studio 2005 and 2008 and deployed to the corresponding versions of SQL Server. The setup worked on both SQL Server 2005 and 2008. Here is a sample output.

Results of the drive_info function

Utilizing the table, DriveInfo, that was used in the previous installments, the script to populate it would be:

INSERT INTO DriveInfo
SELECT letter   AS Drive
     , total_mb AS DriveSize
     , free_mb  AS FreeSpace 
  FROM drive_info()
 WHERE type = 'Fixed'

Although this solution works well enough, there is some risk with this approach. Based on how an Unsafe assembly is handled with in the SQLOS, there is a risk for memory leaks when an unhandled exception occurs. If a solution can be created that uses alternative SQL Server features rather than an Unsafe CLR assembly, I would choose the alternative. Speaking of alternatives, that where the next installments come in. They will cover using SSIS to get the drive info via the Script Component and WMI.

Getting Drive Info Part 2, DTS

In this installment of the drive info series we will gather the drive information via a DTS package. Part 1 of this series used the File System Object from the Windows Scripting Host via the sp_OA suite of stored procedures to gather the drive information. The File System Object will be used again to accomplish this task, but this time from an ActiveXScript task. Using this method can only collect drive information from the server where this DTS package is run. The drive information can be stored on any SQL Server.

Open a new DTS package in the DTS Designer and create a new Connection object that points to a SQL Server where the drive information is going to be stored. Set the database to tempdb or the database that will hold the DriveInfo table which will be created in the package.

Create a new string global variable named DriveInfoXml.

The next thing that needs to be done is create the table to hold the data. Drag an Execute SQL task into the DTS Designer and add the following SQL statement.

CREATE TABLE DriveInfo

IF NOT EXISTS(SELECT 1
                FROM INFORMATION_SCHEMA.TABLES
               WHERE TABLE_NAME = 'DriveInfo')
CREATE TABLE dbo.DriveInfo
( Drive VARCHAR(255)
, DriveSize FLOAT
, FreeSpace FLOAT)

Here is a screen shot of the Execute SQL task set up.

CREATE TABLE DriveInfo

Now we need to collect the drive information. Collection of the drive information is accomplished using the File System Object from an ActiveX Script task. The script accomplishes the same thing as the sp_OA sql script, but looks less like dynamic sql and doesn’t use xp_fixeddrives. The script stores the drive information in XML formatted data to be used in the later steps which insert the data in the table. Drag an ActiveX Script task into the DTS Designer, name it “Get Drive Info” and copy the code below into the code area of the ActiveX Script task. Connect an On Success precedence constraint from the Create DriveInfo Table Execute SQL task to the Get Drive Info ActiveX Script task.

'**********************************************************************
'  Get Drive Size
'
'**********************************************************************

Function Main()

	Dim FSO
	Dim Drives
	Dim Drive
	Dim XML

	' Set up global data.
	Const DriveTypeRemovable = 1
	Const DriveTypeFixed = 2
	Const DriveTypeNetwork = 3
	Const DriveTypeCDROM = 4
	Const DriveTypeRAMDisk = 5
	NewLine = Chr(10)
   
	Set FSO = CreateObject("Scripting.FileSystemObject")

 	Set Drives = FSO.Drives

  	'Create an XML doc to use in SQL
	XML = "<drives>"
	For Each Drive In Drives
		If Drive.DriveType = DriveTypeFixed Then
			XML = XML & "<drive DriveLetter=""" & Drive.DriveLetter & """ TotalSize=""" & Drive.TotalSize & """ FreeSpace=""" & Drive.FreeSpace & """/>"
		End If
	Next
	XML = XML & "</drives>"
	DTSGlobalVariables("DriveInfoXml").Value = XML
	MsgBox(XML)
	Main = DTSTaskExecResult_Success

End Function

Here is a screen shot of the Get Drive Size ActiveX Script Task.

Get Drive Size

We use an ActiveX Script task for the next step that builds the sql command for the Execute SQL task to follow. The XML data created previous step is used in a sql script that utilizes the XML features of SQL Server. There are many ways to get this data into a table in a DTS package, but I choose XML just to show another example that didn’t fall back on Ado. I also think it’s kind of silly to use Ado in DTS when there are already database connections to use. It’s probably a little easier to debug as the output of this task can be copied into a query tool and tested.

Drag another ActiveX Script task into the DTS Designer, name it “Build Put DriveInfo” and copy the code below into the code area of the ActiveX Script task. Connect an On Success precedence constraint from the Get Drive Info ActiveX Script task to the Build Put DriveInfo ActiveX Script task.

'**********************************************************************
'  Build Put DriveInfo
'************************************************************************

Function Main()

Dim SQL
Dim oPkg
Set oPkg = DTSGlobalVariables.Parent

SQL = "DECLARE @hdoc int"  & VbCrLf &_
"DECLARE @doc varchar(8000) -- use varchar(max) on 2005 and higher"  & VbCrLf &_
"SET @doc ='" & DTSGlobalVariables("DriveInfoXml").Value & "'"  & VbCrLf &_
"--Create an internal representation of the XML document."  & VbCrLf &_
"EXECUTE sp_xml_preparedocument @hdoc OUTPUT, @doc"  & VbCrLf &_
"-- Execute a SELECT statement that uses the OPENXML rowset provider."  & VbCrLf &_
"INSERT INTO dbo.DriveInfo"  & VbCrLf &_
"SELECT DriveLetter"  & VbCrLf &_
"     , TotalSize / POWER(1024, 2) AS DriveSize  -- in MB for GB make it POWER(1024, 3)"  & VbCrLf &_
"     , FreeSpace / POWER(1024, 2)"  & VbCrLf &_
"  FROM OPENXML (@hdoc, '/drives/drive',1)"  & VbCrLf &_
"          WITH (DriveLetter varchar(255)"  & VbCrLf &_
"               ,TotalSize float"  & VbCrLf &_
"               ,FreeSpace float)"

oPkg.Tasks("DTSTask_DTSExecuteSQLTask_2").CustomTask.SQLStatement = SQL
	Main = DTSTaskExecResult_Success
End Function

Here is a screen shot of the Build Put DriveInfo ActiveX Script task.

Put Drive Info

The final step in the DTS package is to add the Put DriveInfo Execute SQL Task. The initial sql command is SELECT GETDATE() just to make the parser happy. The actual sql command to execute was generated by the previous step. Here is a sample of the sql command generated by a test run.

DECLARE @hdoc int
DECLARE @doc varchar(8000) -- use varchar(max) on 2005 and higher
SET @doc ='<drives><drive DriveLetter="C" TotalSize="500000878592" FreeSpace="50790670336"/></drives>'
--Create an internal representation of the XML document.
EXECUTE sp_xml_preparedocument @hdoc OUTPUT, @doc
-- Execute a SELECT statement that uses the OPENXML rowset provider.
INSERT INTO dbo.DriveInfo
SELECT DriveLetter
     , TotalSize / POWER(1024, 2) AS DriveSize  -- in MB for GB make it POWER(1024, 3)
     , FreeSpace / POWER(1024, 2)
  FROM OPENXML (@hdoc, '/drives/drive',1)
          WITH (DriveLetter varchar(255)
               ,TotalSize float
               ,FreeSpace float)

Here is a screen shot of the completed package.

DriveInfo DTS Package

sp_help_partition

If you are using partitioned tables, this may come in handy. I’ll keep it short and just post the code. Let me know if there are errors or you have improvements.

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_help_partition]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_help_partition]
GO

CREATE PROCEDURE dbo.sp_help_partition @OBJECT_NAME sysname = NULL
AS
-------------------------------------------------------------------------------
-- 
-- Proc: sp_help_partition
-- Author: Norman Kelm, Gerasus Software, (C) 2010
--
-- Like sp_help, but for partitioned table partition info.
--
-- Usage:
-- Return a list of table that are partitioned
-- EXECUTE sp_help_partition
--
-- Return the partitioning information for a table
-- EXECUTE sp_help_partition 'Production.TransactionHistory'
--
-- Revision History:
-- 08/06/2010 - 1.0 - Original version
--
-------------------------------------------------------------------------------
DECLARE @schema sysname
      , @TABLE_NAME sysname
      
SELECT @schema = CASE WHEN PARSENAME(@OBJECT_NAME,2) IS NULL THEN 'dbo' ELSE PARSENAME(@OBJECT_NAME,2) END
     , @TABLE_NAME = PARSENAME(@OBJECT_NAME,1)

IF @OBJECT_NAME IS NULL
BEGIN
  -- List all paritioned tables when no paramters
  SELECT DISTINCT SCHEMA_NAME(OBJECTPROPERTY(object_id, 'SchemaId')) + '.' + OBJECT_NAME(si.object_id) AS [Name]
    FROM sys.partition_schemes AS ps
   INNER JOIN
         sys.indexes AS si
      ON ps.data_space_id = si.data_space_id
END
ELSE
BEGIN
  IF EXISTS(SELECT 1 
              FROM sys.partition_schemes AS ps
             INNER JOIN
                   sys.indexes AS si
                ON ps.data_space_id = si.data_space_id
               AND OBJECT_ID(@OBJECT_NAME) = si.[object_id])
  BEGIN
  
    -- Borrowed from sp_help
    SELECT [Name]  = o.name
         , [Owner] = USER_NAME(OBJECTPROPERTY(object_id, 'ownerid'))
         , [Schema] = SCHEMA_NAME(OBJECTPROPERTY(object_id, 'SchemaId'))
         , [Type]  = substring(v.name,5,31)
         , [Created_datetime] = o.create_date  
     FROM sys.all_objects o
        , master.dbo.spt_values v  
    WHERE o.object_id = OBJECT_ID(@OBJECT_NAME)
      AND o.type = substring(v.name,1,2) collate database_default
      AND v.type = 'O9T'  
 
   SELECT COUNT(*) AS NumberOfPartitions
     FROM sys.partitions  p
    INNER JOIN
          sys.indexes i
       ON p.object_id = i.object_id
      AND p.index_id = i.index_id
    WHERE OBJECT_ID(@OBJECT_NAME) = p.[object_id]  
      AND i.type in (0,1) --0 = heap, 1 = clustered, skip the nonclustered for the count
       
    SELECT [Scheme] = ps.name 
      FROM sys.partition_schemes AS ps
     INNER JOIN
           sys.indexes AS si
        ON ps.data_space_id = si.data_space_id
       AND OBJECT_ID(@OBJECT_NAME) = si.[object_id]  
       AND si.type in (0,1) --0 = heap, 1 = clustered, skip the nonclustered for the count
      
    SELECT [Function] = pf.name
         , [Type]     = pf.type_desc
         , [fanout]   = pf.fanout 
         , [boundary_value_on_right] = pf.boundary_value_on_right
         , [create_date]             = pf.create_date
         , [modify_date]             = pf.modify_date
      FROM sys.partition_functions AS pf
     INNER JOIN
           sys.partition_schemes AS ps
        ON ps.function_id = pf.function_id
     INNER JOIN
           sys.indexes AS si
        ON ps.data_space_id = si.data_space_id
       AND OBJECT_ID(@OBJECT_NAME) = si.[object_id]  
       AND si.type in (0,1) --0 = heap, 1 = clustered, skip the nonclustered for the count

    SELECT [Function Parameters] = pf.name
         , [parameter_id] = pp.parameter_id
         , [Type] = st.name
         , pp.max_length
         , pp.precision
         , pp.scale
         , pp.collation_name
      FROM sys.partition_parameters AS pp
     INNER JOIN
           sys.partition_functions AS pf
        ON pf.function_id = pp.function_id
     INNER JOIN
           sys.partition_schemes AS ps
        ON ps.function_id = pf.function_id
     INNER JOIN
           sys.indexes AS si
        ON ps.data_space_id = si.data_space_id
     INNER JOIN
           sys.types AS st
        ON pp.system_type_id = st.system_type_id
       AND OBJECT_ID(@OBJECT_NAME) = si.[object_id]  
       AND si.type in (0,1) --0 = heap, 1 = clustered, skip the nonclustered for the count

    SELECT [Function Range Values] = pf.name
         , prv.boundary_id
         , prv.value         
      FROM sys.partition_range_values AS prv
     INNER JOIN
           sys.partition_parameters AS pp
        ON prv.function_id = pp.function_id
       ANd prv.parameter_id = pp.parameter_id
     INNER JOIN
           sys.partition_functions AS pf
        ON pf.function_id = pp.function_id
     INNER JOIN
           sys.partition_schemes AS ps
        ON ps.function_id = pf.function_id
     INNER JOIN
           sys.indexes AS si
        ON ps.data_space_id = si.data_space_id
     INNER JOIN
           sys.types AS st
        ON pp.system_type_id = st.system_type_id
       AND OBJECT_ID(@OBJECT_NAME) = si.[object_id]  
       AND si.type in (0,1) --0 = heap, 1 = clustered, skip the nonclustered for the count
     ORDER BY
           prv.boundary_id
           
     SELECT tc.CONSTRAINT_NAME
          , cc.CHECK_CLAUSE
       FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS tc
       JOIN INFORMATION_SCHEMA.CHECK_CONSTRAINTS AS cc
         ON tc.CONSTRAINT_NAME = cc.CONSTRAINT_NAME
        AND tc.CONSTRAINT_SCHEMA = cc.CONSTRAINT_SCHEMA
      WHERE tc.TABLE_SCHEMA = @schema
        AND tc.TABLE_NAME   = @TABLE_NAME
        AND tc.CONSTRAINT_TYPE = 'CHECK'
   END
   ELSE
   BEGIN
     DECLARE @error NVARCHAR(255)
     SELECT @error = @OBJECT_NAME + ' is not partitioned!!!'
     RAISERROR (@error, 16, 1)
   END
END
GO


Getting Drive Info Part 1, sp_OA

This blog series will cover several different methods to collect drive information from a Windows server that is running SQL Server. I’ve answered several requests in forums, news groups and twitter in the past about how to get the drive size of all of the hard drives on the server that houses a SQL Server instance. These people found the undocumented system stored procedure xp_fixeddrives which returns a result set of the drives on the server along with free space. They were looking for a way to get the drive size as well. This would be a useful addition to xp_fixeddrives to create a disk growth monitoring system. This blog series will also show methods that are specific to SQL Server 2000 as well as methods that utilize the newer features available in versions 2005 and 2008 in future posts. The techniques presented can be modified to get just about any information about the Windows server that houses the SQL Server instance.

Retrieving Drive Size Using sp_OA

The first method discussed is old to say the least as I have been using it since SQL Server 7.0 and there are still plenty of SQL Server 2000 instances running that can make use of this method. It utilizes the sp_OA suite of stored procedures. I have used this method to gather data for an automated SQL Server status report daily for years on SQL Server 7.0, 2000 and 2005. Although there are reports that the sp_OA stored procedures can have memory problems and are a security risk, I have yet to have an issue. They are also set to be deprecated at some point. These points are something that needs to be considered before using this solution. The sp_OA suite of stored procedures allows the use of COM objects from within Transact-SQL. Yes, in order to get the drive size you need to do a little bit of object oriented programming. Much like xp_cmdshell, this suite of store procedures has security risks. Microsoft recognized this and disabled them by default in SQL Server 2005 and 2008. They need to be enabled by running the script (the following is not required on SQL Server 2000):

-- For SQL Server 2005 and 2008 only
EXECUTE sp_configure 'show advanced options', 1
RECONFIGURE WITH OVERRIDE

EXECUTE sp_configure 'Ole Automation Procedures', 1
RECONFIGURE WITH OVERRIDE

If you are really worried about the security aspects of having these stored procedures enabled continuously, the above script could be made part of the entire drive info script. This would enable the store procedures for only as long as necessary to get the drive information. The Ole Automation Procedures configuration option could be disabled at the end of the drive size script.

In order to get the drive size a new instance of the Windows Scripting Host File System Object has to be created. The GetDrive method has to be called and then the property TotalSize is retrieved. Here is the script for the C: drive.

DECLARE @rs INTEGER
      , @fso INTEGER
      , @drv INTEGER
      , @drivesize VARCHAR(20)

-- Create a new Windows Scripting Host File System Object
EXEC @rs = sp_OACreate 'Scripting.FileSystemObject', @fso OUTPUT

-- Get the drive information for the C: drive
IF @rs = 0
  EXEC @rs = sp_OAMethod @fso, 'GetDrive("C:\")', @drv OUTPUT

-- Get the size of this drive
IF @rs = 0
  EXEC @rs = sp_OAGetProperty @drv,'TotalSize', @drivesize OUTPUT
IF @rs<> 0
  SET @drivesize = '0'

-- Clean up
EXEC sp_OADestroy @drv
EXEC sp_OADestroy @fso

SELECT CAST( @drivesize AS FLOAT ) / 1024.0 / 1024.0

The next step in the process is to include xp_fixeddrives to get a list of all the drives on a server and iterate through the result set of drives to get the drive size. A table is populated with all the fixed drive info. This data is iterated and the drive size for each drive is collected from the file system object. Here is the script.

DECLARE @rs INTEGER
      , @fso INTEGER
      , @drv INTEGER
      , @drivesize VARCHAR(20)
      , @drive VARCHAR(255)
      , @GetDrive VARCHAR(255)

DECLARE @DriveInfo TABLE(Drive VARCHAR(255), DriveSize FLOAT, FreeSpace FLOAT)

-- Get all the drives and free space in MB
INSERT INTO @DriveInfo(Drive, FreeSpace) EXECUTE xp_fixeddrives
-- Convert Free Space to GB if applicable to the environment
UPDATE @DriveInfo SET FreeSpace = FreeSpace / 1024.

SELECT TOP 1 @drive = Drive FROM @DriveInfo WHERE DriveSize IS NULL

WHILE @drive IS NOT NULL
BEGIN

  -- Create a new Windows Scripting Host File System Object
  EXEC @rs = sp_OACreate 'Scripting.FileSystemObject', @fso OUTPUT

  -- Get the drive information for the drive
  SELECT @GetDrive = 'GetDrive("'+ @drive + ':\")'
  IF @rs = 0
    EXEC @rs = sp_OAMethod @fso, @GetDrive, @drv OUTPUT

  -- Get the size of this drive
  IF @rs = 0
    EXEC @rs = sp_OAGetProperty @drv,'TotalSize', @drivesize OUTPUT
  IF @rs<> 0
    SET @drivesize = '0'

  UPDATE @DriveInfo SET DriveSize = CAST(@drivesize AS FLOAT) / 1024.0 / 1024.0 WHERE Drive = @drive
  -- Convert Drive Size to GB if applicable to the environment
  UPDATE @DriveInfo SET DriveSize = DriveSize / 1024. WHERE Drive = @drive

  SELECT @drive = NULL
  SELECT TOP 1 @drive = Drive FROM @DriveInfo WHERE DriveSize IS NULL

END

SELECT * FROM @DriveInfo

-- Clean up
EXEC sp_OADestroy @drv
EXEC sp_OADestroy @fso

The data in the @DriveInfo table can be stored permanently in a persisted table for size monitoring and historical reporting.

This concludes the first part of Drive Info series. The sp_OA stored procedures can be used to gather all kinds of information. There are many more properties to look at in the Drive object, go here <add link to Drive docs> for more info. Other COM objects could be instantiated via sp_OA and used to gather server information.

In the next installment of the drive info series, I will show another method supported by SQL Server 2000 and collect the same information using DTS.

SSIS-DTS Package Search 2.0.18 released

SSIS-DTS Package Search 2.0.18 has been released. This is a bug fix release that addresses a problem with the search of the parameter and ResultSet bindings in the Execute SQL task.

Follow

Get every new post delivered to your Inbox.