Total Pageviews

Search This Blog

Tuesday, January 29, 2008

How to invoke .NET Assembly from SQL Server 2005


1. Write a C# assembly which will have a simple method called "HelloWorld" which will take a string as argument and will return the string concatenated with "Hello".

2. Build the assembly

3. Open SQL Query Analyzer and

CREATE ASSEMBLY asmHelloWorldSQL FROM 'c:\HelloWorldSQLAssembly.dll'

Note: asmHelloWorldSQL is name of assembly which can be anything and we have to pass the path where the assembly resides

4. Create a function which will reference the assembly we created

CREATE FUNCTION dbo.clrHelloWorld
@name as nvarchar(200)
RETURNS nvarchar(200)
AS EXTERNAL NAME [asmHelloWorldSQL].[HelloWorldSQLAssembly.Class1].[HelloWorld]

Note: This function will invoke the HelloWorld method from the assembly and return the string.

5. SELECT dbo.clrHelloWorld('Invoke from SQL 2005')
this will return

"Hello Invoke from SQL 2005"

Happy assembling :-)

No comments: