Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

CLR TYPES AND SQL TYPES Expand / Collapse
Author
Message
Posted Saturday, October 18, 2008 3:57 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Yesterday @ 10:56 AM
Points: 70, Visits: 316
Here's my class {ref:msdn}:

public static long DirSize(DirectoryInfo d)
{
long Size = 0;

FileInfo[] fis = d.GetFiles();
foreach (FileInfo fi in fis)
{
Size += fi.Length;
}

DirectoryInfo[] dis = d.GetDirectories();
foreach (DirectoryInfo di in dis)
{
Size += DirSize(di);
}

return (Size);
}

So I created the assembly (successful). But when I'm trying to create a function from the assembly...

CREATE FUNCTION FileSize (@dir VARCHAR(max))
RETURNS BIGINT
EXTERNAL NAME MyAssembly.MyClass.DirSize
GO

...I am getting this error:

Msg 6552, Level 16, State 3, Procedure FileSize, Line 1
CREATE FUNCTION for "FileSize" failed because T-SQL and CLR types for parameter "@dir" do not match.



Marlon Ribunal
http://marlonribunal.com
Twitter @MarlonRibunal
Post #588105
Posted Saturday, October 18, 2008 4:28 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 1:14 AM
Points: 42,412, Visits: 35,479
The error message is fairly clear. The data types don't match. A VARCHAR(max) is not the same as a DirectoryInfo. What are you expecting to be passing to this function?


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #588113
Posted Saturday, October 18, 2008 2:20 PM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Yesterday @ 10:56 AM
Points: 70, Visits: 316
GilaMonster (10/18/2008)
The error message is fairly clear. The data types don't match. A VARCHAR(max) is not the same as a DirectoryInfo. What are you expecting to be passing to this function?


Well, I did not ask the *obvious* question. What is the compatible SQL Server Data Type for C# Type DirectoryInfo? I want to pass well, again it's quite *obvious*, a directory path. Any other suggestion rather than repeating my own question?


Marlon Ribunal
http://marlonribunal.com
Twitter @MarlonRibunal
Post #588158
Posted Saturday, October 18, 2008 5:46 PM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Yesterday @ 11:30 PM
Points: 36,706, Visits: 31,156
MarlonRibunal (10/18/2008)
GilaMonster (10/18/2008)
The error message is fairly clear. The data types don't match. A VARCHAR(max) is not the same as a DirectoryInfo. What are you expecting to be passing to this function?


Well, I did not ask the *obvious* question. What is the compatible SQL Server Data Type for C# Type DirectoryInfo? I want to pass well, again it's quite *obvious*, a directory path. Any other suggestion rather than repeating my own question?


Hey... smart guy... settle down. You want help, be nice. ;) If it's so bloody obvious, look it up in Books Online.


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

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #588188
Posted Saturday, October 18, 2008 7:54 PM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Yesterday @ 10:56 AM
Points: 70, Visits: 316
What I was trying to say is..please do not reply to the post if you do not have the answer. Thanks.

Marlon Ribunal
http://marlonribunal.com
Twitter @MarlonRibunal
Post #588200
Posted Saturday, October 18, 2008 9:27 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Thursday, June 5, 2014 10:54 AM
Points: 9,902, Visits: 9,480
MarlonRibunal (10/18/2008)
What I was trying to say is..please do not reply to the post if you do not have the answer. Thanks.

I can only imagine that you are trying to control us with "rules" like this because it makes you feel better about having to ask for help, however, you should realize that trying to control how we help you is much more likely to result in your not finding the solution that you need.

For instance, in this case, it is not possible for us to both help you with your technical problem and at the same time comply with the rules that you are trying to impose (that is, "do not reply to the post if you do not have the answer" to your question). This is because you have asked the wrong question and it has no answer.

And this puts me in a bit of a quandary. Normally, I would just ignore the fact that you are asking the wrong question and just tell you the solution to your problem anyway. However, I am a great believer in mutual respect and even though you seem to be treating us (and in particular, the people trying to help you) with disrespect, I would still not want to disrespect you in turn by ignoring your request.

So instead, I will leave it up to you. I believe that I can assist you with your technical problem. Please let me know if you would like me to provide that assistance.


-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Post #588205
Posted Sunday, October 19, 2008 2:28 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 1:14 AM
Points: 42,412, Visits: 35,479
MarlonRibunal (10/18/2008)


Well, I did not ask the *obvious* question. What is the compatible SQL Server Data Type for C# Type DirectoryInfo? I want to pass well, again it's quite *obvious*, a directory path. Any other suggestion rather than repeating my own question?


Really? I would never have guessed! </sarcasm> Perhaps I should have been a little more clear. What's an example of value that you want to pass to the function from SQL? Where does that value come from?

SQL doesn't have anything compatible with DirectoryInfo. SQL does not use the .Net data types. What you are probably going to have to do (and I say probably, because I don't know what exactly you're passing to the function) is to have the function accept a SQLString and then convert that into a directoryInfo before operating on it.



Just to add to what the others have said, please remember that we're posting and answering questions in our free time. None of us get paid to do this. We like helping people but in general, but if you post with attitude, people may just ignore your posts as others are easier to answer.

Oh, and as for repeating your own question. In the initial post, you didn't actually ask a question.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #588224
Posted Sunday, October 19, 2008 3:10 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Yesterday @ 10:56 AM
Points: 70, Visits: 316
Mea Culpa....

My Bad, did not mean to be sarcastic.

Let's go back to business guys...let's forgive and forget....


I am trying to do something like the extended procedure xp_dirscan. Is there an equivalent procedure in SQL Server 2005?

That is what I am trying to do on my first ever CLR build (xp_dirscan-like function). But CLR brought me bad luck. Let's be friends...

I apologize.


Marlon Ribunal
http://marlonribunal.com
Twitter @MarlonRibunal
Post #588227
Posted Sunday, October 19, 2008 3:40 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 1:14 AM
Points: 42,412, Visits: 35,479
No problem. :)

Ok, so what exactly will you be passing to the function? Something like 'C:\Somedir\SomeOtherDir\'? What do you want returning from the function?
Also, what do you need it for?

What might work (and I say might, because I'm not a C# expert) is to change the function you gave to something like this.

public static long DirSize(SQLString dir)
{
DirectoryInfo d = new DirectoryInfo((String) dir);
long Size = 0;

Now you can create that function in SQL with a nvarchar(4000) parameter (I don't think it'll take varchar(MAX), though if you have directory trees more than 4000 characters wide, I'd worry for other reasons).
The cast to String may or maynot be necessary. Try both ways, see what works.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #588229
Posted Sunday, October 19, 2008 10:48 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, June 23, 2014 11:55 AM
Points: 1,708, Visits: 1,792
GilaMonster (10/19/2008)

Now you can create that function in SQL with a nvarchar(4000) parameter (I don't think it'll take varchar(MAX), though if you have directory trees more than 4000 characters wide, I'd worry for other reasons).
The cast to String may or maynot be necessary. Try both ways, see what works.


An nvarchar(max) could be used, but the recommended equivalent type is SqlChars, which can stream data to the function. Both have a .Value method which returns a String datatype that can be used as:

            DirectoryInfo d = new DirectoryInfo(dir.Value);

You can also declare the function method as:

public static long DirSize(string dir)
{

and the conversion occurs inline. I am not a big fan of allowing the conversion to be implicitly made, but it does work.


Jonathan Kehayias | Principal Consultant | MCM: SQL Server 2008
My Blog | Twitter | MVP Profile
Training | Consulting | Become a SQLskills Insider
Troubleshooting SQL Server: A Guide for Accidental DBAs
Post #588264
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse