?
Solved

Call .NET (2003) function from MS SQL 2000 User Defined Function (UDF)

Posted on 2006-05-09
8
Medium Priority
?
688 Views
Last Modified: 2011-08-18
It seems obvious that SQL-DMO comes into play.  But before I bite off all that for the first time, is this a reasonable idea?

I have a VB.NET function that RC4-encrypts/decrypts values.

I want to call it from a SQL 2000 UDF as in:

Create Function dbo.udfDecodeSSN
(
@ID BigInt
)
Returns VarChar(9)
As
Return
(
Select
   DoRC4 ( dbo.Employee.SSN ) as SSN  /* Note the function call here */
From
   dbo.Employees
Where
   dbo.Employees.ID = @ID
)

/* Something like that */

DoRC4() is a VB.NET (2003) function.  The password/encryption key is embedded in it and the .dll created from it is required to run the UDF.

So, is that going to work, and if so, could I get a clue as to calling my .NET function from the UDF?

THANKS!
0
Comment
Question by:geekboysteves
  • 4
  • 4
8 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 16650720
udf cannot be called like stored procedures, but need to be used in queries:

select * from dbo.udfDecodeSSN(2389238)
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 16650734
please forget that one :-)
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 total points
ID: 16650741
you cannot call a .NET function from a UDF in SQL Server 2000.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

Author Comment

by:geekboysteves
ID: 16651123
I thought that sp_OACreate was designed to allow us to call external DLL/ActiveX [functions/methods/classes] from SQL Server.

As in this example from the EE link below it:

EXEC @hr = sp_OACreate 'DynCrypto.Crypto', @object OUT

http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_21082952.html

Where 'DynCrypto.Crypto' is such and ActiveX component.

>> you cannot call a .NET function from a UDF in SQL Server 2000.

What am I missing here?

Thanks!
0
 

Author Comment

by:geekboysteves
ID: 16651205
This (from SQL Books Online) is disturbing:

"Permissions
Only members of the sysadmin fixed server role can execute sp_OACreate."

Ordinary users need to be able to run it to work in my setup, I would think.
0
 

Author Comment

by:geekboysteves
ID: 16651266
Just so you know, the thing I am trying to accomplish is to have the encrypt/decrypt function outside the SQL server, somewhere.  This is so that a backup of the database does not contain the code to decrypt the encrypted fields.
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 16652518
>I thought that sp_OACreate was designed to allow us to call external DLL/ActiveX [functions/methods/classes] from SQL Server.
yes, but you cannot call those procedures from within functions.

if you really want to use the external functions, you have to use a stored procedure instead of a stored function.
no way around that.
0
 

Author Comment

by:geekboysteves
ID: 16652620
>> you cannot call a .NET function from a UDF in SQL Server 2000.

That is not what I hoped to hear, but after some research, I agree and I am moving on to a new approach.

Thanks for the information!
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It’s quite interesting for me as I worked with Excel using vb.net for some time. Here are some topics which I know want to share with others whom this might help. First of all if you are working with Excel then you need to Download the Following …
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question