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


CLR TYPES AND SQL TYPES


CLR TYPES AND SQL TYPES

Author
Message
MarlonRibunal
MarlonRibunal
SSC-Enthusiastic
SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)

Group: General Forum Members
Points: 113 Visits: 363
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
GilaMonster
GilaMonster
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86480 Visits: 45239
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, MVP, M.Sc (Comp Sci)
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


MarlonRibunal
MarlonRibunal
SSC-Enthusiastic
SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)

Group: General Forum Members
Points: 113 Visits: 363
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
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)

Group: General Forum Members
Points: 84943 Visits: 41071
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. Wink 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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
MarlonRibunal
MarlonRibunal
SSC-Enthusiastic
SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)

Group: General Forum Members
Points: 113 Visits: 363
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
RBarryYoung
RBarryYoung
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14614 Visits: 9518
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."
GilaMonster
GilaMonster
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86480 Visits: 45239
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, MVP, M.Sc (Comp Sci)
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


MarlonRibunal
MarlonRibunal
SSC-Enthusiastic
SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)

Group: General Forum Members
Points: 113 Visits: 363
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
GilaMonster
GilaMonster
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86480 Visits: 45239
No problem. Smile

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, MVP, M.Sc (Comp Sci)
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


Jonathan Kehayias
Jonathan Kehayias
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2876 Visits: 1807
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
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