SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


fn_fixeddrives() : an alternative for xp_fixeddrives


fn_fixeddrives() : an alternative for xp_fixeddrives

Author
Message
David BAFFALEUF
David BAFFALEUF
SSC Eights!
SSC Eights! (947 reputation)SSC Eights! (947 reputation)SSC Eights! (947 reputation)SSC Eights! (947 reputation)SSC Eights! (947 reputation)SSC Eights! (947 reputation)SSC Eights! (947 reputation)SSC Eights! (947 reputation)

Group: General Forum Members
Points: 947 Visits: 712
Comments posted to this topic are about the item fn_fixeddrives() : an alternative for xp_fixeddrives

David B.
Matt Whitfield
Matt Whitfield
Hall of Fame
Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)

Group: General Forum Members
Points: 3405 Visits: 719
While I like the idea, I'm not sure about storing the drive information in a static field - you would be much better off declaring a class or struct in which to return results, using a generic List<> to store your instances, and return the data from there.

That way, you don't need the UNSAFE permission set, and achieve exactly the same thing.

Edit -> Ok, you still need UNSAFE because of the permissions required by the DriveInfo class, but I still wouldn't use a static :-D

Atlantis Interactive - SQL Server Tools
My blog
Why I wrote a sql query analyzer clone
blessonblessons
blessonblessons
Old Hand
Old Hand (371 reputation)Old Hand (371 reputation)Old Hand (371 reputation)Old Hand (371 reputation)Old Hand (371 reputation)Old Hand (371 reputation)Old Hand (371 reputation)Old Hand (371 reputation)

Group: General Forum Members
Points: 371 Visits: 420
Nice idea.thanks for sharing
TheSQLGuru
TheSQLGuru
SSC Guru
SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)

Group: General Forum Members
Points: 51950 Visits: 8793
Nice stuff. Did I miss the link where I can download the source code and/or compiled object??

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Jack Corbett
  Jack Corbett
SSC Guru
SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)

Group: General Forum Members
Points: 70882 Visits: 14948
I thought this was a well-written and clear article which helped me better understand working with the CLR. I still haven't found a need to do any CLR programming and I'm not really looking for any, but it is good to understand.

Matt,

Why would using a class or struct make it unnecessary to use UNSAFE? I had just assumed that going out to the OS to get the drive information would necessitate UNSAFE. I'm a total newbie with CLR.



Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
David BAFFALEUF
David BAFFALEUF
SSC Eights!
SSC Eights! (947 reputation)SSC Eights! (947 reputation)SSC Eights! (947 reputation)SSC Eights! (947 reputation)SSC Eights! (947 reputation)SSC Eights! (947 reputation)SSC Eights! (947 reputation)SSC Eights! (947 reputation)

Group: General Forum Members
Points: 947 Visits: 712
Sorry Kevin,

Here you go: https://sourceforge.net/projects/fnfixeddrives/

David B.
Matt Whitfield
Matt Whitfield
Hall of Fame
Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)

Group: General Forum Members
Points: 3405 Visits: 719
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! Smile

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;
}



Atlantis Interactive - SQL Server Tools
My blog
Why I wrote a sql query analyzer clone
David BAFFALEUF
David BAFFALEUF
SSC Eights!
SSC Eights! (947 reputation)SSC Eights! (947 reputation)SSC Eights! (947 reputation)SSC Eights! (947 reputation)SSC Eights! (947 reputation)SSC Eights! (947 reputation)SSC Eights! (947 reputation)SSC Eights! (947 reputation)

Group: General Forum Members
Points: 947 Visits: 712
Hi Matt,

I agree using a static field will force the assembly to be considered as UNSAFE, but diving into the IO system would lead to the same path anyway.

I use a static field because I need the list to be available outside of the main scope, especially in the fill method. What you will usually see in sqlclr developments is to pass the full object to the fill method, because it is more convenient, but you'll have to call a DriveInfo constructor to get the job done. The idea in this example was to pass a simple array pointing to fixed and ready drives, so you save a call to the object's ctor. And remember a call to the object's ctor will be called every time a row is fetched from the list.

Appreciate for the comments ,

David B.
fhanlon
fhanlon
SSCertifiable
SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)

Group: General Forum Members
Points: 5456 Visits: 2328
Nicely written. Thanks for the explanation



Francis
Matt Whitfield
Matt Whitfield
Hall of Fame
Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)

Group: General Forum Members
Points: 3405 Visits: 719
David

Yes, the DriveInfo class does require UNSAFE because of the permissions, I said that - my bad.

However, I can't agree with the rest of what you've said. I don't see that the constructor of a simple class would outweigh the cost of using an ArrayList instead of yield as well as the cost of boxing and unboxing all of the values stored in that ArrayList...

The other worry would be garbage collection. By putting everything in a static, the DriveInfo instances that are created are not naturally garbage collected because the reference to them is held by the static array. By doing away with that, everything is up for garbage collection as soon as the function terminates...

M

Atlantis Interactive - SQL Server Tools
My blog
Why I wrote a sql query analyzer clone
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search