asked on

Calling a CLR based function from SQL SERVER

Hi,

I am trying to create a function in C# and have it called from SQL Server as a DB Function

This is what i have already done..

1)Created a Class Library Project in VS 2005 Express
2)Created the class with the attached code and compiled to get the .dll file
3) Created new Assembly on SQL Server with dbo as owner and pointing to the .dll as the CLR assembly
4)Called the function with select dbo.Sample()

But i received the following error message

Msg 4121, Level 16, State 1, Line 1
Cannot find either column "dbo" or the user-defined function or aggregate "dbo.Sample or the name is ambiguous.

Am doing anything wrong in the steps?

using System;
using Microsoft.SqlServer.Server;
using System.Text.RegularExpressions;
 
 
 
public class RegExBase
{
    [SqlFunction(IsDeterministic = true, IsPrecise = true)]
    public static bool Sample()
    {
        return true;
    }
};

Open in new window

Jaime Olivares

try with:
select dbo.RegExBase.Sample()

justin_smith

ASKER

That didnt work

ASKER CERTIFIED SOLUTION

Jaime Olivares

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

justin_smith

ASKER

jaime,

that did work..

but i am after functions..

is there a similar think like create proc we need to do to kind of map the the CLR function to SQL function..

thanks

Jaime Olivares

the CREATE PROCEDURE statement is necessary to map the CLR functions to SQL functions.
have you read the link I've posted?

justin_smith

ASKER

well it is create function :-)

This is something similar .. The names are not as same as the example used above

CREATE FUNCTION addtax(@amount int) RETURNS INT
AS EXTERNAL NAME RegExpr.UserDefinedFunctions.addTax
SELECT dbo.addtax(10)

http://msdn.microsoft.com/en-us/library/ms131043.aspx

justin_smith

ASKER

Now this CLR integration looks very promising...

Can i build assemblies that have methods that call other methods to establish functionality?

I am trying to maximise the benefits of the mainstream .net programming and still make it useable as a stored proc of function..

What i am thinking of doing is finally building a wrapper method to interact with the db server..

is this practically done?

and are there any drawbacks to this approach

SOLUTION

Jaime Olivares

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial