Click here to monitor SSC
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
siraj.jamdar
siraj.jamdar
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 2
I 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.
David BAFFALEUF
David BAFFALEUF
Valued Member
Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)

Group: General Forum Members
Points: 65 Visits: 712
Hi Siraj,

Yes you can call:

[System.IO.DriveInfo]::GetDrives() | Format-Table

...from the powershell prompt.

David B.
UMG Developer
UMG Developer
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2280 Visits: 2204
Thanks 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!
Larry Kruse
Larry Kruse
SSC-Enthusiastic
SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)

Group: General Forum Members
Points: 104 Visits: 746
David,

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:
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_fixeddrives
GO



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!
blessonblessons
blessonblessons
Valued Member
Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)

Group: General Forum Members
Points: 63 Visits: 420
You 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 function
eg:- [databasename].[schemaname].fn_fixeddrives()
David BAFFALEUF
David BAFFALEUF
Valued Member
Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)

Group: General Forum Members
Points: 65 Visits: 712
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 Sad

David B.
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45223 Visits: 39927
David BAFFALEUF (9/24/2010)
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 Sad


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.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
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