http://www.sqlclr.netRSS feeds for SQLCLR.net60http://www.sqlclr.net/Articles/tabid/54/articleType/ArticleView/articleId/40/QA-with-Adam-Machanic--Whos-Afraid-of-SQLCLR.aspx#Comments0http://www.sqlclr.net/DesktopModules/DnnForge%20-%20NewsArticles/RssComments.aspx?TabID=54&ModuleID=371&ArticleID=40http://www.sqlclr.net/DesktopModules/DnnForge%20-%20NewsArticles/Tracking/Trackback.aspx?ArticleID=40&PortalID=0&TabID=54http://summit2009.sqlpass.org/Agenda/PrePostConferenceSessions/MachanicQA.aspxYou could almost hear the gasps of horror when Microsoft integrated the .NET Common Language Runtime (CLR) into SQL Server 2005. Run C# or VB.NET code inside SQL Server? For many database developers and administrators, it was like an alien had just burst through SQL Server’s midsection. But SQL Server MVP Adam Machanic says that more and more developers and DBAs are discovering that far from destroying SQL Server performance and security, SQLCLR is actually a powerful ally in solving complex business and technical problems. Read More on the PASS website..Jonathan KehayiasFri, 24 Jul 2009 15:24:00 GMTf1397696-738c-4295-afcd-943feb885714:40http://www.sqlclr.net/Articles/tabid/54/articleType/ArticleView/articleId/39/Default.aspx#Comments0http://www.sqlclr.net/DesktopModules/DnnForge%20-%20NewsArticles/RssComments.aspx?TabID=54&ModuleID=371&ArticleID=39http://www.sqlclr.net/DesktopModules/DnnForge%20-%20NewsArticles/Tracking/Trackback.aspx?ArticleID=39&PortalID=0&TabID=54http://sqlblog.com/blogs/adam_machanic/archive/2009/04/28/sqlclr-string-splitting-part-2-even-faster-even-more-scalable.aspxSQL Server MVP Adam Machanic shows a new way of parsing strings in SQL Server using SQLCLR that outperforms all conventional TSQL methods, as well as the most common SQLCLR implemenations, using a custom split function that allows streaming of the results.Site AdministratorFri, 01 May 2009 15:56:00 GMTf1397696-738c-4295-afcd-943feb885714:39http://www.sqlclr.net/Articles/tabid/54/articleType/ArticleView/articleId/38/Default.aspx#Comments0http://www.sqlclr.net/DesktopModules/DnnForge%20-%20NewsArticles/RssComments.aspx?TabID=54&ModuleID=371&ArticleID=38http://www.sqlclr.net/DesktopModules/DnnForge%20-%20NewsArticles/Tracking/Trackback.aspx?ArticleID=38&PortalID=0&TabID=54http://www.sqlservercentral.com/articles/SQLCLR/65656/Learn how to use SQLCLR to get file system information instead of using xp_cmdshell on your SQL Servers.Jonathan KehayiasFri, 06 Feb 2009 17:21:00 GMTf1397696-738c-4295-afcd-943feb885714:38http://www.sqlclr.net/Articles/tabid/54/articleType/ArticleView/articleId/37/Free-Tools-to-make-SQLCLR-development-easier.aspx#Comments0http://www.sqlclr.net/DesktopModules/DnnForge%20-%20NewsArticles/RssComments.aspx?TabID=54&ModuleID=371&ArticleID=37http://www.sqlclr.net/DesktopModules/DnnForge%20-%20NewsArticles/Tracking/Trackback.aspx?ArticleID=37&PortalID=0&TabID=54http://www.sqlclr.net/Articles/tabid/54/articleType/ArticleView/articleId/37/Free-Tools-to-make-SQLCLR-development-easier.aspxIf you develop SQLCLR objects with a version of Visual Studio other than Professional Edition, these free tools can help speed up your development and deployment times.Jonathan KehayiasSun, 25 Jan 2009 22:58:00 GMTf1397696-738c-4295-afcd-943feb885714:37http://www.sqlclr.net/Articles/tabid/54/articleType/ArticleView/articleId/36/Vote-For-It--Server-Side-Compiles-in-SQL-CLR.aspx#Comments0http://www.sqlclr.net/DesktopModules/DnnForge%20-%20NewsArticles/RssComments.aspx?TabID=54&ModuleID=371&ArticleID=36http://www.sqlclr.net/DesktopModules/DnnForge%20-%20NewsArticles/Tracking/Trackback.aspx?ArticleID=36&PortalID=0&TabID=54http://www.sqlclr.net/Articles/tabid/54/articleType/ArticleView/articleId/36/Vote-For-It--Server-Side-Compiles-in-SQL-CLR.aspxGreg Low a SQL Server MVP, has submitted a very compelling Connect item 265266, Add server-side compilation ability to SQL CLR which aims to remove the requirement to develop SQLCLR assemblies external to SQL Server.  Read about this suggestion here, and vote for it by visiting the Connect site to let Microsoft know you think it is important.Jonathan KehayiasSun, 05 Oct 2008 01:47:00 GMTf1397696-738c-4295-afcd-943feb885714:36http://www.sqlclr.net/Articles/tabid/54/articleType/ArticleView/articleId/34/Default.aspx#Comments1http://www.sqlclr.net/DesktopModules/DnnForge%20-%20NewsArticles/RssComments.aspx?TabID=54&ModuleID=371&ArticleID=34http://www.sqlclr.net/DesktopModules/DnnForge%20-%20NewsArticles/Tracking/Trackback.aspx?ArticleID=34&PortalID=0&TabID=54http://www.sqlclr.net/Articles/tabid/54/articleType/ArticleView/articleId/34/Default.aspxTraditionally if you had a need to access a file or other resource outside of SQL Server, the SQL Server service account was required to have appropriate file system access to the folder or path containing the file.  With CLR integration, this is no longer an absolute requirement.  Identity Impersonation will allow you to implicitly or explicitly change the execution context inside of a SQLCLR Function, Procedure, or Trigger. Jonathan KehayiasSat, 02 Aug 2008 01:35:00 GMTf1397696-738c-4295-afcd-943feb885714:34http://www.sqlclr.net/Articles/tabid/54/articleType/ArticleView/articleId/33/Default.aspx#Comments1http://www.sqlclr.net/DesktopModules/DnnForge%20-%20NewsArticles/RssComments.aspx?TabID=54&ModuleID=371&ArticleID=33http://www.sqlclr.net/DesktopModules/DnnForge%20-%20NewsArticles/Tracking/Trackback.aspx?ArticleID=33&PortalID=0&TabID=54http://www.sqlclr.net/Articles/tabid/54/articleType/ArticleView/articleId/33/Default.aspxA common part of programming in .NET is to use an configuration file to store configuration information in an easily modifiable location.  The app.config or web.config file is an invaluable inclusion in most .NET projects and developers may need to maintain this functionality as a part of logic sharing between objects in the database and the application as well.  This article will demonstrate how to configure your SQLCLR project to use Configuration Files in SQL.Jonathan KehayiasFri, 25 Jul 2008 19:43:00 GMTf1397696-738c-4295-afcd-943feb885714:33http://www.sqlclr.net/Articles/tabid/54/articleType/ArticleView/articleId/32/Building-a-SAFE-HttpUtility-Class-for-SQL-CLR.aspx#Comments0http://www.sqlclr.net/DesktopModules/DnnForge%20-%20NewsArticles/RssComments.aspx?TabID=54&ModuleID=371&ArticleID=32http://www.sqlclr.net/DesktopModules/DnnForge%20-%20NewsArticles/Tracking/Trackback.aspx?ArticleID=32&PortalID=0&TabID=54http://www.sqlclr.net/Articles/tabid/54/articleType/ArticleView/articleId/32/Building-a-SAFE-HttpUtility-Class-for-SQL-CLR.aspxThis Article covers how to build a SQL Safe version of the System.Web.HttpUtility class methods HtmlEncode and HtmlDecode.  Source code is provided in the Examples area for these specific methods to be used in SQL CLR as User-Defined Functions with a SAFE Permission Set.Jonathan KehayiasTue, 22 Jul 2008 02:05:00 GMTf1397696-738c-4295-afcd-943feb885714:32http://www.sqlclr.net/Articles/tabid/54/articleType/ArticleView/articleId/27/Default.aspx#Comments0http://www.sqlclr.net/DesktopModules/DnnForge%20-%20NewsArticles/RssComments.aspx?TabID=54&ModuleID=371&ArticleID=27http://www.sqlclr.net/DesktopModules/DnnForge%20-%20NewsArticles/Tracking/Trackback.aspx?ArticleID=27&PortalID=0&TabID=54http://www.sqlclr.net/Articles/tabid/54/articleType/ArticleView/articleId/27/Default.aspxNo matter how hard you try at some point it might become necessary for you to add a method to an existing CLR User-Defined Type to support a new business rule, or if created a single assembly with all of your objects including your CLR UDT, you may need to modify an existing function or stored procedure to meet new requirements.  This article will demonstrate how to use ALTER ASSEMBLY to perform these updates.Jonathan KehayiasTue, 08 Jul 2008 03:51:00 GMTf1397696-738c-4295-afcd-943feb885714:27http://www.sqlclr.net/Articles/tabid/54/articleType/ArticleView/articleId/26/Default.aspx#Comments1http://www.sqlclr.net/DesktopModules/DnnForge%20-%20NewsArticles/RssComments.aspx?TabID=54&ModuleID=371&ArticleID=26http://www.sqlclr.net/DesktopModules/DnnForge%20-%20NewsArticles/Tracking/Trackback.aspx?ArticleID=26&PortalID=0&TabID=54http://www.sqlclr.net/Articles/tabid/54/articleType/ArticleView/articleId/26/Default.aspxThis article is part one of a two part series to try and show some of the versatility that CLR provides us. Part one will focus on creating a user-defined CLR function to use a XSLT file to transform a XML string and e-mail it using sp_send_dbMail.Reuben ShafferThu, 03 Jul 2008 23:20:00 GMTf1397696-738c-4295-afcd-943feb885714:26http://www.sqlclr.net/Articles/tabid/54/articleType/ArticleView/articleId/25/Default.aspx#Comments0http://www.sqlclr.net/DesktopModules/DnnForge%20-%20NewsArticles/RssComments.aspx?TabID=54&ModuleID=371&ArticleID=25http://www.sqlclr.net/DesktopModules/DnnForge%20-%20NewsArticles/Tracking/Trackback.aspx?ArticleID=25&PortalID=0&TabID=54http://www.sqlclr.net/Articles/tabid/54/articleType/ArticleView/articleId/25/Default.aspxAn important part of building CLR objects for use in SQL Server is good exception management.  Part of this is raising the exception back to the calling operation so that it can be handled appropriately.  In .NET this is handled using try/catch blocks in the code, and throwing a new instance of the Exception class with a message. This article will show how to cleanup the Exception message in TSQL to show only the needed information cleanly.Jonathan KehayiasThu, 03 Jul 2008 15:58:00 GMTf1397696-738c-4295-afcd-943feb885714:25http://www.sqlclr.net/Articles/tabid/54/articleType/ArticleView/articleId/23/Default.aspx#Comments0http://www.sqlclr.net/DesktopModules/DnnForge%20-%20NewsArticles/RssComments.aspx?TabID=54&ModuleID=371&ArticleID=23http://www.sqlclr.net/DesktopModules/DnnForge%20-%20NewsArticles/Tracking/Trackback.aspx?ArticleID=23&PortalID=0&TabID=54http://www.sqlclr.net/Articles/tabid/54/articleType/ArticleView/articleId/23/Default.aspxThis article is to demonstrate how easy it is to write a primitive CLR regular expression function with C# and SQL Server 2005.  We will solve the problem of separating numeric from nonnumeric characters. Reuben ShafferMon, 23 Jun 2008 17:21:00 GMTf1397696-738c-4295-afcd-943feb885714:23http://www.sqlclr.net/Articles/tabid/54/articleType/ArticleView/articleId/12/Default.aspx#Comments0http://www.sqlclr.net/DesktopModules/DnnForge%20-%20NewsArticles/RssComments.aspx?TabID=54&ModuleID=371&ArticleID=12http://www.sqlclr.net/DesktopModules/DnnForge%20-%20NewsArticles/Tracking/Trackback.aspx?ArticleID=12&PortalID=0&TabID=54http://www.sqlclr.net/Articles/tabid/54/articleType/ArticleView/articleId/12/Default.aspxSample SQLCLR user-defined aggregate for the product of a column of numbers.  There is related discussion about the SQL  alternatives to SQLCLR aggregates.  Andrew NovickMon, 26 May 2008 07:00:00 GMTf1397696-738c-4295-afcd-943feb885714:12http://www.sqlclr.net/Articles/tabid/54/articleType/ArticleView/articleId/18/Default.aspx#Comments0http://www.sqlclr.net/DesktopModules/DnnForge%20-%20NewsArticles/RssComments.aspx?TabID=54&ModuleID=371&ArticleID=18http://www.sqlclr.net/DesktopModules/DnnForge%20-%20NewsArticles/Tracking/Trackback.aspx?ArticleID=18&PortalID=0&TabID=54http://www.sqlclr.net/Articles/tabid/54/articleType/ArticleView/articleId/18/Default.aspxIf you choose to implement CLR User Defined Types in SQL Server, there are certain considerations that must be understood to minimize problems later on.  This article covers these considerations and how to plan for them when you are designing the UDT.Jonathan KehayiasMon, 12 May 2008 00:47:00 GMTf1397696-738c-4295-afcd-943feb885714:18http://www.sqlclr.net/Articles/tabid/54/articleType/ArticleView/articleId/10/Using-SQLBulkCopy-in-SQL-CLR.aspx#Comments0http://www.sqlclr.net/DesktopModules/DnnForge%20-%20NewsArticles/RssComments.aspx?TabID=54&ModuleID=371&ArticleID=10http://www.sqlclr.net/DesktopModules/DnnForge%20-%20NewsArticles/Tracking/Trackback.aspx?ArticleID=10&PortalID=0&TabID=54http://www.sqlclr.net/Articles/tabid/54/articleType/ArticleView/articleId/10/Using-SQLBulkCopy-in-SQL-CLR.aspxAt some point you might want to Bulk Copy data from one database to another, either for archiving, or for reporting.  Whatever your reason, if you choose to implement this with CLR, there is an important gotcha that you need to be aware of.  You can not use SqlBulkCopy with the context connection.Jonathan KehayiasFri, 18 Apr 2008 03:37:00 GMTf1397696-738c-4295-afcd-943feb885714:10http://www.sqlclr.net/Articles/tabid/54/articleType/ArticleView/articleId/9/Default.aspx#Comments1http://www.sqlclr.net/DesktopModules/DnnForge%20-%20NewsArticles/RssComments.aspx?TabID=54&ModuleID=371&ArticleID=9http://www.sqlclr.net/DesktopModules/DnnForge%20-%20NewsArticles/Tracking/Trackback.aspx?ArticleID=9&PortalID=0&TabID=54http://www.sqlclr.net/Articles/tabid/54/articleType/ArticleView/articleId/9/Default.aspxWhen deploying a CLR Assembly that requires EXTERNAL_ACCESS or UNSAFE as the Permission Set you have the option of either Signing the Assembly with a strong key or setting the database to TRUSTWORTHY ON.  Microsoft's recommendation is that you sign the assembly with a strong key as a best practice.Jonathan KehayiasFri, 11 Apr 2008 04:44:00 GMTf1397696-738c-4295-afcd-943feb885714:9http://www.sqlclr.net/Articles/tabid/54/articleType/ArticleView/articleId/8/Default.aspx#Comments0http://www.sqlclr.net/DesktopModules/DnnForge%20-%20NewsArticles/RssComments.aspx?TabID=54&ModuleID=371&ArticleID=8http://www.sqlclr.net/DesktopModules/DnnForge%20-%20NewsArticles/Tracking/Trackback.aspx?ArticleID=8&PortalID=0&TabID=54http://www.sqlclr.net/Articles/tabid/54/articleType/ArticleView/articleId/8/Default.aspxDetermining whether to set the Trustworthy Property of a database to ON or to sign your assembly with a certificate might be a confusing thing.  Simply searching online for how to run UNSAFE or EXTERNAL permission Assemblies in SQL Server makes it seem as if setting the Trustworthy Property to ON is the right way to go.  This however is not the Microsoft recommended practice...Jonathan KehayiasFri, 11 Apr 2008 03:35:00 GMTf1397696-738c-4295-afcd-943feb885714:8http://www.sqlclr.net/Articles/tabid/54/articleType/ArticleView/articleId/5/Determining-When-to-Use-CLR.aspx#Comments1http://www.sqlclr.net/DesktopModules/DnnForge%20-%20NewsArticles/RssComments.aspx?TabID=54&ModuleID=371&ArticleID=5http://www.sqlclr.net/DesktopModules/DnnForge%20-%20NewsArticles/Tracking/Trackback.aspx?ArticleID=5&PortalID=0&TabID=54http://www.sqlclr.net/Articles/tabid/54/articleType/ArticleView/articleId/5/Determining-When-to-Use-CLR.aspxOne of the most commonly asked questions I get from developers is, should this be done with a CLR function/procedure or in TSQL?  The addition of CLR in SQL Server brought a very powerful extension to the Database Engine.  This article will focus on providing information on how to best make the decision to use CLR or TSQL to solve a problem.Jonathan KehayiasSat, 05 Apr 2008 17:35:00 GMTf1397696-738c-4295-afcd-943feb885714:5http://www.sqlclr.net/Articles/tabid/54/articleType/ArticleView/articleId/4/Single-Assembly-versus-Multiple-Assemblies.aspx#Comments0http://www.sqlclr.net/DesktopModules/DnnForge%20-%20NewsArticles/RssComments.aspx?TabID=54&ModuleID=371&ArticleID=4http://www.sqlclr.net/DesktopModules/DnnForge%20-%20NewsArticles/Tracking/Trackback.aspx?ArticleID=4&PortalID=0&TabID=54http://www.sqlclr.net/Articles/tabid/54/articleType/ArticleView/articleId/4/Single-Assembly-versus-Multiple-Assemblies.aspxWhen implementing CLR in SQL Server, often times it easier for developers to use a singe assembly.  This allows them to open a single Visual Studio Solution to add to or change existing CLR.  To a DBA, this can lead to a problem with security in the database server.  Rather than having a single assembly, it is instead better to separate the code out by safety level, placing all Safe Code in a common assembly.  This helps to minimize the volume of code that has to be reviewed by the DBA to know how safe the Assembly is.Jonathan KehayiasSat, 05 Apr 2008 17:20:00 GMTf1397696-738c-4295-afcd-943feb885714:4http://www.sqlclr.net/Articles/tabid/54/articleType/ArticleView/articleId/1/Enabling-CLR-in-SQL-Server.aspx#Comments0http://www.sqlclr.net/DesktopModules/DnnForge%20-%20NewsArticles/RssComments.aspx?TabID=54&ModuleID=371&ArticleID=1http://www.sqlclr.net/DesktopModules/DnnForge%20-%20NewsArticles/Tracking/Trackback.aspx?ArticleID=1&PortalID=0&TabID=54http://www.sqlclr.net/Articles/tabid/54/articleType/ArticleView/articleId/1/Enabling-CLR-in-SQL-Server.aspxLearn how to enable CLR for the SQL Instance and set your database to the correct compatibility level for running CLR.Jonathan KehayiasFri, 04 Apr 2008 03:01:00 GMTf1397696-738c-4295-afcd-943feb885714:1