We help IT Professionals succeed at work.

Calling a CLR based function from SQL SERVER

1,397 Views
Last Modified: 2012-08-13
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

Comment
Watch Question

Jaime OlivaresSoftware Architect
CERTIFIED EXPERT
Top Expert 2008

Commented:
try with:
select dbo.RegExBase.Sample()

Author

Commented:
That didnt work
Software Architect
CERTIFIED EXPERT
Top Expert 2008
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
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 OlivaresSoftware Architect
CERTIFIED EXPERT
Top Expert 2008

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

Author

Commented:
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

Author

Commented:
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

Jaime OlivaresSoftware Architect
CERTIFIED EXPERT
Top Expert 2008
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.