|
|
|
SSChampion
Group: General Forum Members
Last Login: Today @ 8:24 AM
Points: 20,164,
Visits: 13,698
|
|
Phil Factor (2/7/2009)
I see CLR more as a way of providing extensions to the SQL Server system to handle complex data. Now that is really exciting.......
For what it's worth, that's more of what I saw CLRs being used for instead of a convenient method for people who don't know T-SQL to still write code against a database. Regex, the things you wrote about, and, if you want to skip SSIS, file handling are a couple of great places for properly written CLRs.
--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, 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/
|
|
|
|
|
SSChampion
Group: General Forum Members
Last Login: Today @ 8:24 AM
Points: 20,164,
Visits: 13,698
|
|
Richard M (2/7/2009)
Boy, am I glad to be "someone" now.....
More than that, I've got a pretty healthy respect for you because of the way you just responded and have responded in the past. It wasn't my intent to make it personal and, thank goodness, you didn't take it personal. Thank you, Sir!
--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, 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/
|
|
|
|
|
SSChampion
Group: General Forum Members
Last Login: Today @ 8:24 AM
Points: 20,164,
Visits: 13,698
|
|
Jonathan Kehayias (2/6/2009)
I've got some more coming that it will be fun to see your comments on. All along this same line of processing, but its always interesting to see what you come up with for TSQL equivalents to the SQLCLR. As you say, the places I can write CLR to beat TSQL has been limited, and some of your tricks like appending a '/' at the end of a URL in TSQL to make domain name extraction lightening fast are very interesting and something to learn from.
Knowing your abilities, there will undoubtedly be things where T-SQL and I just don't stand a chance, but I sure do look forward to trying. I always look forward to anything written by Jonathan Kehayias. And, like Phil, I'm tickled that, if our group at work does come up with the need for a viable CLR, we now have a great template to follow that considers the right kind of security that the Systems DBA's are likely to buy. Thank you, Sir, and very well done!
--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, 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/
|
|
|
|
|
Grasshopper
Group: General Forum Members
Last Login: Monday, February 08, 2010 10:36 AM
Points: 10,
Visits: 15
|
|
Hey there - thanks for the article; it was just what I was looking for.
I've decided to deploy my CLR via VS2010, using a VB SQL CLR project. However, my code requires system.directoryservices. My directoryservices code works fine in a normal vb module where I can add the reference to system.directory services, however, in my VB SQL CLR module, I don't have the ability to do so. I'm suing the CLR project for the "auto deployment" scenario you speak of.
Do you have any recommendation on how to 1) import system.directoryservices into my CLR project, or 2) compile my code and "manually" add it as a clr?
Thanks man.
Thor (Hammer of God)
|
|
|
|
|
SSC Rookie
Group: General Forum Members
Last Login: Friday, March 12, 2010 3:36 PM
Points: 28,
Visits: 27
|
|
Thank you for the article, i was able to implement a simple SQLCLR assebly, It just does basic file operations , the function receives the Source path and Destination path from the calling procedure and the SQLCLR function ( written in C#) only copies the file to the destination path.
Everything was excelled when deployed, the apps showed a big boost in performance..
Suddenly after about 3hr, the SQLCLR function start throwing .NET exceptions. :
" An error occurred in the Microsoft .NET Framework while trying to load assembly id 65539. The server may be running out of resources, or the assembly may not be trusted with PERMISSION_SET = EXTERNAL_ACCESS or UNSAFE. Run the query again, or check documentation to see how to solve the assembly trust issues.
System.IO.FileLoadException: Could not load file or assembly 'FileCopy' "
The fuction was called extensively ( about 10 call evey 10 secs ).
Could someone please comment on this, about why this happened. I thought .NET takes care of memory leaks on its own, and this does not need to be handeled in the code.
Any comments on this will be greatly appreiciated.
Thanks in Advance!!
|
|
|
|
|
Ten Centuries
Group: General Forum Members
Last Login: Tuesday, March 16, 2010 10:13 AM
Points: 1,310,
Visits: 1,228
|
|
What is your C# code for the function? I am guessing that this is a 32 bit SQL Server? It may not have anything to do with a memory leak, or failure to deallocate the memory used, but instead with VAS fragmentation over time when coupled with other VAS consumers in your environment, something very specific to 32 bit servers where the VAS is limited and SQL Server only takes a default reservation of 384MB (which is very small) for running VAS consumers like SQL CLR. We need more information like the C# code, whether SQL is 32 or 64 bit, and the amount of physical ram installed on the server.
Jonathan Kehayias
http://www.sqlclr.net
http://sqlblog.com/blogs/jonathan_kehayias
http://www.twitter.com/SQLSarg
|
|
|
|
|
SSC Rookie
Group: General Forum Members
Last Login: Friday, March 12, 2010 3:36 PM
Points: 28,
Visits: 27
|
|
Hi Jonathan
Thanks for replying back..
Server is Win Server 2003 R2 , 64 bit edition,
32 GB RAM installed,
Microsoft SQL Server 2005 - 9.00.4229.00 (X64)
Jul 29 2009 14:59:51
Standard Edition (64-bit) on Windows NT 5.2
(Build 3790: Service Pack 2)
C# code:
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.IO;
public partial class UserDefinedFunctions
{
[Microsoft.SqlServer.Server.SqlFunction]
public static SqlInt32 udf_doFilecopy(SqlString sSrc, SqlString sDest)
{
try
{
if (File.Exists(sDest.Value) == false && File.Exists(sSrc.Value))
{
FileInfo fileInfo = new FileInfo(sDest.Value)
if (Directory.Exists(fileInfo.DirectoryName))
{
File.Copy(sSrc.Value, sDest.Value);
return (0);
}
else
{
Directory.CreateDirectory(fileInfo.DirectoryName);
File.Copy(sSrc.Value, sDest.Value);
return (0);
}
}
else if (File.Exists(sDest.Value) == true)
return (1);
else if (File.Exists(sSrc.Value) == false)
return (2);
else
return (3);
}
catch (Exception e)
{
return (-1);
}
}
|
|
|
|
|
Ten Centuries
Group: General Forum Members
Last Login: Tuesday, March 16, 2010 10:13 AM
Points: 1,310,
Visits: 1,228
|
|
|
|
|
|
SSC Rookie
Group: General Forum Members
Last Login: Friday, March 12, 2010 3:36 PM
Points: 28,
Visits: 27
|
|
Max Memory for SQL: 20GB
Lock pages in memory is enabled in for the user with which the SQL runs
|
|
|
|
|
Ten Centuries
Group: General Forum Members
Last Login: Today @ 7:32 AM
Points: 1,102,
Visits: 3,618
|
|
just a sanity check, have you verified in the sql server logs that SQL server is using locked pages. Check the instance startup text in the log to verify lock pages is in force
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 
|
|
|
|
