InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 22 May 2013 19:44:25 GMT20http://www.sqlservercentral.com/Forums/Topic989884-2743-1.aspx[quote][b]David BAFFALEUF (9/24/2010)[/b][hr]Hi Larry,Thanks for your comments,As blessonblessons says, you'll have to use the fully qualified name to be able to call the function from any other database. It had been a stored procedure instead of a function, you would have been able to create it in the master database and to turn it as a system object using sys.sp_MS_marksystemobject, making the object callable from any database without fully qualifying the name, but it looks like this cannot apply to functions :([/quote]You can make just about anything work from the Master database just by giving it the prefix of sp_. sp_ does not, contrary to popular belief, stand for "Stored Procedure" here. It stands for "SPecial".Also, with the advent of synonymns, I avoid using any naming convention other than 2 part naming conventions so that if the database name changes (for any reason) or I need to move code to another server, I don't have to find all the 3 part naming convention stuff and change it. I only need to modify a handfull of synonyms.Thu, 30 Sep 2010 21:45:35 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic989884-2743-1.aspxHi Larry,Thanks for your comments,As blessonblessons says, you'll have to use the fully qualified name to be able to call the function from any other database. It had been a stored procedure instead of a function, you would have been able to create it in the master database and to turn it as a system object using sys.sp_MS_marksystemobject, making the object callable from any database without fully qualifying the name, but it looks like this cannot apply to functions :(Fri, 24 Sep 2010 02:30:41 GMTDavid BAFFALEUFhttp://www.sqlservercentral.com/Forums/Topic989884-2743-1.aspxYou can have any number of function/procedures in within a DLL.If you need to call the function from another database use the fully qualified name of the functioneg:- [databasename].[schemaname].fn_fixeddrives()Thu, 23 Sep 2010 11:43:35 GMTblessonblessonshttp://www.sqlservercentral.com/Forums/Topic989884-2743-1.aspxDavid,I really appreciate the article. I've always wanted to learn about SQLCLR and your concise (yet useful) example was just the thing to motivate me into trying it. The code compiled cleanly in VS2010, but I've encountered a number of issues with getting this built on my SQL Server 2008 environment.For some reason, I had to SET compatibility_level = 90. I had copy the .DLL to the MSSQL\Binn folder before the CREATE ASSEMBLY command could find it and open it. (Probably folder access permission issue.)I got "stuck" at the CREATE FUNCTION step. I receive a Msg 6505 - Could not find Type 'cFixedDrives' in assembly 'cFixedDrives'. Then I found a KB article on MSDN which advised that I need to include the namespace qualifier as in the following example:[code="other"]CREATE FUNCTION fn_fixeddrives () RETURNS TABLE( "Drive" nvarchar(3), "Size(Mb)" bigint, "Used(Mb)" bigint, "Available(Mb)" bigint, "Use%" bigint )AS EXTERNAL NAME fixeddrives.[cFixedDrives.cFixedDrives].fn_fixeddrivesGO[/code]I have a couple of other questions related to this SQLCLR stuff though. I notice that I can only execute this new function in the database I was in when I created the function. Is there a way that I can cause this to work no matter which database I'm in? Can I include more than a single function in the assembly? (For example, I'd like to code up a bunch of validation routines for various columns in our databases and I was wondering if each one had to be created separately or if I can compile a single .DLL which contained them all.)Again David, I learned quite a bit from this article and it's got me thinking about all of the possibilities I might be able to use this for. Thanks!Thu, 23 Sep 2010 10:31:05 GMTLarry Krusehttp://www.sqlservercentral.com/Forums/Topic989884-2743-1.aspxThanks for the article, it has helped me better understand how to create CLR functions.The discussion is very good as well, so thanks again to everyone!Wed, 22 Sep 2010 22:49:41 GMTUMG Developerhttp://www.sqlservercentral.com/Forums/Topic989884-2743-1.aspxHi Siraj,Yes you can call:[font="Courier New"][System.IO.DriveInfo]::GetDrives() | Format-Table [/font]...from the powershell prompt.Wed, 22 Sep 2010 06:37:38 GMTDavid BAFFALEUFhttp://www.sqlservercentral.com/Forums/Topic989884-2743-1.aspxI wonder if you could point me towards a PowerShell implementation of this function? It may not be an option to compile DLL's or switch on CLR in some environments.Wed, 22 Sep 2010 05:25:41 GMTsiraj.jamdarhttp://www.sqlservercentral.com/Forums/Topic989884-2743-1.aspx[quote][b]David BAFFALEUF (9/22/2010)[/b][hr]The problem is because SQLCLR is not so well understood by the DBAs in general. And for a DBA, who carries the responsability of the corporation's data management and safety, what is not so well understood will wait on the doorway until it is, if not thrown away. I've been a production DBA for the past 10 years, I know how conservative we can be with new *cool* features.[/quote]Truer words rarely spoken. I think the SQL Server community as a whole is very slow to adopt new technology - which can be frustrating. However, it's probably that same characteristic that has made it one of the longest serving job skills in the IT industry as a whole...Wed, 22 Sep 2010 02:17:39 GMTMatt Whitfieldhttp://www.sqlservercentral.com/Forums/Topic989884-2743-1.aspx[quote][b]SQueaLer1 (9/21/2010)[/b][hr]Hi David,I appreciate the concept but I have to disagree with the approach. We programmers can come up with a lot of "nice" solutions, but they don't always work in the corporate world. This is a factor you haven't touched on in your article. Remember, we have total control over our PC with our local SQL, but in large enterprise environments, the situation is completely different.Your solution is not so practical because -* I don't have access to the server's local drive - I need it to be able to copy the DLL.* I am not allowed to even turn on the CLR option.* The function is unsafe and in corporate environments I just don't have the permissions to install it.* DBAs frown upon CLR.I can come up with more reasons, but you get the idea.Now, it so happens that I needed the same feature just last week. I implemented it in PURE SQL using just one short stored procedure. Similar code has been out there for some time. I tweaked it for my needs. I think it is generic enough to be used by anyone.I named it sp_fixeddrives and put it in master. This enables me to call it from any db.I also created a job that executes it hourly, and checks the free space %. If it falls below a threshold, I send out an alert email.[/quote]Hi,Thank you, this was the comment I was hoping to readI understand your position, but because I use this in an operationnal monitoring tool for a while on many business critical environments, I cannot agree on all points. It really depends on your role in the database infrastructure. I am considered as the DBA on the systems I monitor for my clients, so I am pretty much on my own to apply the method I want, but this is not the point. The problem is because SQLCLR is not so well understood by the DBAs in general. And for a DBA, who carries the responsability of the corporation's data management and safety, what is not so well understood will wait on the doorway until it is, if not thrown away. I've been a production DBA for the past 10 years, I know how conservative we can be with new *cool* features.The message I tried to send under the covers was that SQLCLR exists, and to use it you must understand what it can do and what it can't. There is a hazard that comes with practically every tool but it is not a valid reason for me not to learn from it. Same thing applies to COM automation in SQL Server, which is fantastic tool, but because the warnings have always been a square mile big, very few people uses it. Because of all these assumptions, SQLCLR is still in its infancy, like LINQ for example which already turned DBAs against it even before it had touched the market floor. This is why sqlclr.net exists and we try to show the guys in control SQLCLR is not that evil. I know this is a long way to convince DBAs it is better to build bridges across production IT and developpers, but we'll hold. :-DThanks again,PS: Like Jeff, I'd also be interested in seeing how you can achieve this using standard T-SQL.Wed, 22 Sep 2010 01:10:03 GMTDavid BAFFALEUFhttp://www.sqlservercentral.com/Forums/Topic989884-2743-1.aspx[quote][b]SQueaLer1 (9/21/2010)[/b][hr]Hi David,I appreciate the concept but I have to disagree with the approach. We programmers can come up with a lot of "nice" solutions, but they don't always work in the corporate world. This is a factor you haven't touched on in your article. Remember, we have total control over our PC with our local SQL, but in large enterprise environments, the situation is completely different.Your solution is not so practical because -* I don't have access to the server's local drive - I need it to be able to copy the DLL.* I am not allowed to even turn on the CLR option.* The function is unsafe and in corporate environments I just don't have the permissions to install it.* DBAs frown upon CLR.I can come up with more reasons, but you get the idea.Now, it so happens that I needed the same feature just last week. I implemented it in PURE SQL using just one short stored procedure. Similar code has been out there for some time. I tweaked it for my needs. I think it is generic enough to be used by anyone.I named it sp_fixeddrives and put it in master. This enables me to call it from any db.I also created a job that executes it hourly, and checks the free space %. If it falls below a threshold, I send out an alert email.[/quote]Any chance of you posting your tweaked code for this? I'd love to see the T-SQL alternative to such as this. Thanks.Tue, 21 Sep 2010 18:26:15 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic989884-2743-1.aspxHi David,I appreciate the concept but I have to disagree with the approach. We programmers can come up with a lot of "nice" solutions, but they don't always work in the corporate world. This is a factor you haven't touched on in your article. Remember, we have total control over our PC with our local SQL, but in large enterprise environments, the situation is completely different.Your solution is not so practical because -* I don't have access to the server's local drive - I need it to be able to copy the DLL.* I am not allowed to even turn on the CLR option.* The function is unsafe and in corporate environments I just don't have the permissions to install it.* DBAs frown upon CLR.I can come up with more reasons, but you get the idea.Now, it so happens that I needed the same feature just last week. I implemented it in PURE SQL using just one short stored procedure. Similar code has been out there for some time. I tweaked it for my needs. I think it is generic enough to be used by anyone.I named it sp_fixeddrives and put it in master. This enables me to call it from any db.I also created a job that executes it hourly, and checks the free space %. If it falls below a threshold, I send out an alert email.Tue, 21 Sep 2010 14:39:13 GMTSQLiorhttp://www.sqlservercentral.com/Forums/Topic989884-2743-1.aspxThanks - this was a good article. It is nice to see stuff like this in CLR.Tue, 21 Sep 2010 10:15:50 GMTSQLRNNRhttp://www.sqlservercentral.com/Forums/Topic989884-2743-1.aspxHi Harold / Jayant, Very good question. What is clear is that DriveInfo does not consider mount points, so they won't be reported by the sqlclr function. There are ways to deal with volumes and mount points like using the [url=http://msdn.microsoft.com/en-us/library/aa364994%28VS.85%29.aspx]system service API[/url] directly (using a DllImport on kernel32.dll in the source code). You could do it in a separate piece of managed C# console app, but I've not tested it in SQLCLR. You should be very carefully testing this because there are much more potential causes of unhandled exceptions this way. Thanks for your comments,Tue, 21 Sep 2010 09:16:20 GMTDavid BAFFALEUFhttp://www.sqlservercentral.com/Forums/Topic989884-2743-1.aspx[quote][b]David BAFFALEUF (9/21/2010)[/b][hr]And there's always something good to learn in the end.[/quote]If I had a pound for every time I'd learnt something either here or on Ask.SSC then I'd be a lot richer! :-D And please don't get me wrong, I think the article is great - the more people that know about how to use the CLR the better...Tue, 21 Sep 2010 08:20:03 GMTMatt Whitfieldhttp://www.sqlservercentral.com/Forums/Topic989884-2743-1.aspx[quote][b]Matt Whitfield (9/21/2010)[/b][hr]DavidYes, 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[/quote]Hi Matt,You made your point, especially on the GC part, which I hadn't considered in the design. That's what I like the most about comments, the idea of debate. And there's always something good to learn in the end.Thanks again for your comments,Tue, 21 Sep 2010 08:17:44 GMTDavid BAFFALEUFhttp://www.sqlservercentral.com/Forums/Topic989884-2743-1.aspxHey davidWill this also gather mount points? We use a lot of mountpoints here to save on drive letters.Opps, we must have posted at the same time. LOLTue, 21 Sep 2010 08:02:40 GMTHarold Bucknerhttp://www.sqlservercentral.com/Forums/Topic989884-2743-1.aspxHi, Will this report disk/drive space for mounted Volumes?ThanksTue, 21 Sep 2010 07:59:46 GMTjr81http://www.sqlservercentral.com/Forums/Topic989884-2743-1.aspxDavidYes, 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...MTue, 21 Sep 2010 07:39:30 GMTMatt Whitfieldhttp://www.sqlservercentral.com/Forums/Topic989884-2743-1.aspxNicely written. Thanks for the explanationTue, 21 Sep 2010 07:33:51 GMTfhanlonhttp://www.sqlservercentral.com/Forums/Topic989884-2743-1.aspxHi 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 ,Tue, 21 Sep 2010 07:01:37 GMTDavid BAFFALEUFhttp://www.sqlservercentral.com/Forums/Topic989884-2743-1.aspxJackFor 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:[code]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; }[/code]Tue, 21 Sep 2010 06:28:36 GMTMatt Whitfieldhttp://www.sqlservercentral.com/Forums/Topic989884-2743-1.aspxSorry Kevin,Here you go: [url=https://sourceforge.net/projects/fnfixeddrives/]https://sourceforge.net/projects/fnfixeddrives/[/url]Tue, 21 Sep 2010 06:25:56 GMTDavid BAFFALEUFhttp://www.sqlservercentral.com/Forums/Topic989884-2743-1.aspxI 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.Tue, 21 Sep 2010 06:08:29 GMT Jack Corbetthttp://www.sqlservercentral.com/Forums/Topic989884-2743-1.aspxNice stuff. Did I miss the link where I can download the source code and/or compiled object??Tue, 21 Sep 2010 06:06:50 GMTTheSQLGuruhttp://www.sqlservercentral.com/Forums/Topic989884-2743-1.aspxNice idea.thanks for sharingTue, 21 Sep 2010 04:56:21 GMTblessonblessonshttp://www.sqlservercentral.com/Forums/Topic989884-2743-1.aspxWhile 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&lt;&gt; to store your instances, and return the data from there.[strike]That way, you don't need the UNSAFE permission set, and achieve exactly the same thing.[/strike]Edit -&gt; Ok, you still need UNSAFE because of the permissions required by the DriveInfo class, but I still wouldn't use a static :-DTue, 21 Sep 2010 02:00:14 GMTMatt Whitfieldhttp://www.sqlservercentral.com/Forums/Topic989884-2743-1.aspxComments posted to this topic are about the item [B]<A HREF="/articles/SQLCLR/70963/">fn_fixeddrives() : an alternative for xp_fixeddrives</A>[/B]Mon, 20 Sep 2010 21:01:56 GMTDavid BAFFALEUF