Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by David Baffaleuf » fn_fixeddrives() : an alternative for...
Add to briefcase
27 posts, Page 3 of 3
«««123

fn_fixeddrives() : an alternative for xp_fixeddrives Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
siraj.jamdar
Posted Wednesday, September 22, 2010 5:25 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, September 22, 2010 5:21 AM
Points: 1, Visits: 1
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.
Post #991029
David BAFFALEUF
Posted Wednesday, September 22, 2010 6:37 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, January 09, 2013 10:15 AM
Points: 61, Visits: 691
Hi Siraj,

Yes you can call:

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

...from the powershell prompt.

David B.
Post #991086
UMG Developer
Posted Wednesday, September 22, 2010 10:49 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, May 13, 2013 11:21 AM
Points: 2,163, Visits: 2,148
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!
Post #991705
Larry Kruse
Posted Thursday, September 23, 2010 10:31 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, May 08, 2013 11:54 AM
Points: 90, Visits: 584
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!
Post #992176
blessonblessons
Posted Thursday, September 23, 2010 11:43 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, February 06, 2013 4:46 AM
Points: 55, Visits: 391
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()
Post #992241
David BAFFALEUF
Posted Friday, September 24, 2010 2:30 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, January 09, 2013 10:15 AM
Points: 61, Visits: 691
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


David B.
Post #992549
Jeff Moden
Posted Thursday, September 30, 2010 9:45 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 1:51 PM
Points: 32,906, Visits: 26,789
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


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."

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Post #996394
« Prev Topic | Next Topic »

Add to briefcase
27 posts, Page 3 of 3
«««123

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.