CLR Integration in SQL

Question

CLR Integration in SQL

Viewing 5 posts - 16 through 19 (of 19 total)

You must be logged in to reply to this topic. Login to reply

k4tay Valued Member Points: 74 More actions · Answer 1

binak_86 (4/5/2012)
k4tay (4/5/2012)
I am having some issues deploying my CLR project but after reading this article i suspect it has to do with my project must be using version 4 of the framework where this article specifically states to be sure and change it to 3.5. Can soneone comment as to why this was required? Ill be checking my project once i get into the office.
I am sorry I should have mentioned this in the article.
The answer to your question can be found in the following error, which VS 2010 gives me:
Note: If you do not have the .Net Framework version 3.5 installed on your development computer, you must install it if you want to develop SQL CLR assemblies for SQL Server 2005 or SQL Server 2008. SQL Server 2005 and SQL Server 2008 support only those assemblies that target the 2.0, 3.0, or 3.5 version of the .Net Framework. You specify the .Net Framework version in the project properties.
I am just curious, what exactly are you using CLR integeration for, if you don't mind sharing? 🙂

I am trying to implement this CLR code to process the Facebook Graph JSON object for a users friends list. Take a look at this article:

http://www.aloadofcode.com/csharp-json-sql

When I go to register my assembly on the SQL server, I get the error that the SQL ccannot find system.serialize or something which seems a bit strange since the framework is installed. I might have compild the CLR project with 4 so I am redoing it now. Any help would be appreciated.

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 2

binak_86 (4/5/2012)
@Jeff Moden
How can I do validations of email address through T-SQL?

I'm on my way to work so can't provide much detail just now. I'll do that tonight after work.

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

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 3

Again, apologies for the delay. This is one of the ways you can do it in T-SQL only. The formula isn't quite as pretty as the REGEX example given in the article. Like the CLR, it can be used as a constraint, as is, or in an iTVF.

Of course, neither the CLR code nor the following code covers all possible email address formats. Please see the following article ( http://en.wikipedia.org/wiki/Email_address ) on many more formats and specs that support the formats and then tweak the code to suite your druthers.

DECLARE @EmailAddress VARCHAR(512);

SELECT @EmailAddress = 'Some.One@Company.com';

SELECT CASE

WHEN @EmailAddress NOT LIKE '%[^-a-zA-Z0-9__.@]' ESCAPE '_' COLLATE Latin1_General_BIN

AND @EmailAddress LIKE '%[a-zA-Z0-9]@[a-zA-Z0-9]%.[a-zA-Z0-9][a-zA-Z0-9]%' COLLATE Latin1_General_BIN

THEN 'Good'

ELSE 'Bad'

END

;

The reason for the collation statement is to make it catch accented characters on accent insensitive servers.

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

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Ben Teraberry SSChampion Points: 11845 More actions · Answer 4

I don't think that the argument should be that string evaluation and manipulation cannot be done in t-sql, it's that they cannot be done nearly as efficiently as they can through CLR.

└> bt

Forum Etiquette: How to post data/code on a forum to get the best help[/url]

binak_86 SSC Enthusiast Points: 194 More actions · Answer 5

binak_86

SSC Enthusiast

Points: 194

April 14, 2012 at 10:55 pm

#1473703

Thanks Jeff