Jack

For going out to the OS, you generally can use EXTERNAL_ACCESS. In this instance, you actually do need UNSAFE - because of the permissions that are required to use DriveInfo under CAS. I usually check these things before I open my big mouth! 🙂

But I still wouldn't use a static field even so, seeing as the static field's life time will extend beyond the execution of the function (for example, if you use a singleton under UNSAFE, you can observe that it's lifetime is longer than that of the call into the CLR - subsequent calls to the same function would use the same instance).

Anyway, the code I would use would look like this:

sealed class _driveInfo

{

public readonly string DriveName;

public readonly long TotalSizeMB;

public readonly long UsedSizeMB;

public readonly long FreeSizeMB;

public readonly long PercentUsed;

public _driveInfo(string driveName, long totalSizeMB, long usedSizeMB, long freeSizeMB, long percentUsed)

{

DriveName = driveName;

TotalSizeMB = totalSizeMB;

UsedSizeMB = usedSizeMB;

FreeSizeMB = freeSizeMB;

PercentUsed = percentUsed;

}

}

[Microsoft.SqlServer.Server.SqlFunction

(

FillRowMethodName = "_f_fill",

TableDefinition = "Drive nvarchar(3), SizeMb bigint, UsedMb bigint, AvailableMb bigint, UsePct bigint"

)

]

public static IEnumerable fn_fixeddrives()

{

foreach (DriveInfo d in DriveInfo.GetDrives())

{

if (d.DriveType == DriveType.Fixed && d.IsReady)

{

yield return new _driveInfo(d.Name,

(d.TotalSize) / 1048576,

(d.TotalSize - d.TotalFreeSpace) / 1048576,

(d.TotalFreeSpace) / 1048576,

(d.TotalFreeSpace * 100) / d.TotalSize);

}

}

}

private static void _f_fill(Object o, out string drvName, out long totalsizeMb, out long usedsizeMb, out long freesizeMb, out long pctused)

{

_driveInfo dI = (_driveInfo)o;

drvName = dI.DriveName;

totalsizeMb = dI.TotalSizeMB;

usedsizeMb = dI.UsedSizeMB;

freesizeMb = dI.FreeSizeMB;

pctused = dI.PercentUsed;

}