http://www.geekzilla.co.uk/Innovation Team's dumping grounden-usTue, 10 Jun 2003 04:00:00 GMTTue, 10 Jun 2003 09:41:01 GMTGeekZilla.co.ukeditor@GeekZilla.co.ukwebmaster@GeekZilla.co.uk http://www.geekzilla.co.uk/view4E325877-BACA-4E37-A5C8-FA4AC0B8428D.htm CLR Stored Procedure for searching files I was recently asked to write a CLR stored procedure which would process a text file and return a row for each line in the file that contained text matching our search criteria. I'd never written a CLR stored proc before so it was an interesting learning excersise. This article details the steps I went through to get to the end result. Pre-requesits *Visual Studio 2005 *SQL Server 2005 The SQL project From Visual studio, create a new C# database project. The new project wizard will ask you to specify a database for your project. In this instance, this is only used when you deploy the stored procedure. Once the project is created, add a new stored procedure to the project by right clicking the project and choosing '''Add New Item..'''. Specify the input parameters for the stored procedure in the auto generated function header. For this example: The file processor The following code processes a text file and builds up a list of occurances. Returning the results The SqlContext.Pipe object is the key to communicating with SQL. There are four methods on the Pipe object which can be used for returning data. ||Send()||Multiple rows from a reader, one record or some text|| ||SendResultsStart()||Sends one row (SqlDataRecord) and configures the pipe for sending more rows|| ||SendResultsRow()||Sends one row|| ||SendResultsEnd()||Tells the Pipe we've finished sending data|| To return a single row you need to create a SqlDataRecord. Below is an example of creating a SqlDataRecord with one varchar column. If we wanted to only return one row we'd use send.. as below The finised stored procedure Deploying the Stored Procedure Deploying the stored procedure is easy, or so it seemed. Right click the project in Solution Explorer, choose Deploy. This worked, unfortunately this stored procedure would like to access a file, this requires the EXTERNAL_ACCESS permission. EXTERNAL_ACCESS To mark the stored proc as requiring EXTERNAL_ACCESS you need to bring up the properties window for the project. On the Database tab, change the '''Permission Level''' to '''"External"'''. TRUSTWORTHY One last thing, before you can deploy an EXTERNAL_ACCESS stored procedure, you will need to ensure that the target database is marked as [TRUSTWORTHY]. Use the following T-Sql to do this. Lets deploy Once you've ensured the EXTERNAL_ACCESS and TRUSTWORTH settings are correct, try deploying the stored proc. It should deploy with no problems.. '''(I was connected to the database as SA when I tried)''' Calling the Stored Procedure Summary Creating CLR stored procedures is easy. Creating CLR stored procedures which need access to external objects like files, web services etc is also pretty easy once you know what to do about security issues. Further Reading http://www.devx.com/codemag/Article/31193/0/page/5 http://blogs.msdn.com/lcris/archive/2005/09/26/474202.aspx 16/6/2006 http://www.geekzilla.co.uk/view4E325877-BACA-4E37-A5C8-FA4AC0B8428D.htm http://www.geekzilla.co.uk/viewC84A92DA-2856-490A-AE97-406E1B868F99.htm Enabling TRUSTWORTHY on a SQL 2005 Database If your .NET stored procedure wants to have external access, the targer database will have to have TRUSTWORTHY set on.. this is how you do it. #c#GO 15/6/2006 http://www.geekzilla.co.uk/viewC84A92DA-2856-490A-AE97-406E1B868F99.htm http://www.geekzilla.co.uk/view26F31AD9-FA7D-4470-BFCD-77F5D6BC8EF4.htm Enabling CLR execution in SQL Server If you plan to run stored procedures you have written in .NET you will probably need to enable CLR execution on your server. To do this you'll need to run the following T-SQL 15/6/2006 http://www.geekzilla.co.uk/view26F31AD9-FA7D-4470-BFCD-77F5D6BC8EF4.htm http://www.geekzilla.co.uk/view6C0ECA41-80B9-4EA9-A232-FF9C5AEE68C3.htm Writing CLR Stored Procedures in C# As SQL Server 2005 rolls out DBA's are going to be forced to learn either C# or Visual Basic or both. Until now these were client side languages and not knowing them had little impact on your job. And if you write code in these languages your going to have to learn to use Visual Studio. This article covers the basics of C# and Visual Studio using a basic stored procedure as an example. It's written using the April CTP of SQL Server 2005. ''This article shows you how http://www.sqlteam.com/item.asp?ItemID=21927 '' 15/6/2006 http://www.geekzilla.co.uk/view6C0ECA41-80B9-4EA9-A232-FF9C5AEE68C3.htm