We help IT Professionals succeed at work.

How to call a .NET dll from a Stored Procedure

2,022 Views
Last Modified: 2013-11-08
Hi ,

I have a .Net dll which is registered in GAC. I need to call a method from this dll from a stored procedure and pass some value. I have no idea how to do this. A detailed explanation with an example would be really appreciated.

Thanks in advance.
-Ajmal
Comment
Watch Question

Éric MoreauSenior .Net Consultant
CERTIFIED EXPERT
Top Expert 2016

Commented:
I am not sure that you can call any DLLs in the GAC. Have a look at http://msdn.microsoft.com/en-us/library/ms131046.aspx
Éric MoreauSenior .Net Consultant
CERTIFIED EXPERT
Top Expert 2016

Commented:
from http://msdn.microsoft.com/en-us/library/ms131052.aspx: "This assembly can be found in the Global Assembly Cache (GAC)"

Author

Commented:
Hi,

I am a beginner and i am not sure if this would be the right way to do it. We have SQL server 2000 and Server 2005 both.I don't think this CLR integration would work fine with Server 2000. Am i right? Which is the best way to do this? All i need to do is reference a dll from my stored procedure. If this thing couldn't be done could anyone suggest me how we do it through OLE automation stored procedure. I created another thread but no one has replied to that. so i am posting it under this thread as well...

When I try implementing Automation Stored Procedures to Access an Object's Properties. the attached code runs on one of SQL server 2000 but doesn't work on another 2000 Server and any of 2005. The Version is not getting displayed.I tried adding the following code

IF @Hresult <> 0
BEGIN
  EXEC sp_OAGetErrorInfo @Object, @ErrorSource OUT, @ErrorDesc OUT
  PRINT "Error Occurred Calling Object:  " + @ErrorSource + " " + @ErrorDesc
  RETURN
END

and it says
Error Occurred Calling Object:  ODSOLE Extended Procedure sp_OADestroy usage:  ObjPointerToBeDestroyed int IN.


What's wrong with the code? How can i make it work on both Server 2000 and 2005. The TempCommon.dll is registered in GAC. Please help.


CODE-----------------------------

DECLARE @ccObject int
    DECLARE @ccResult int
    DECLARE @ccInt    int
    DECLARE @ccString varchar(100)
    DECLARE @ccVersion varchar(20)

    --Create TempCommon Object
    PRINT ' '
    PRINT 'sp_OACreate TempCommon.DataUtils'
    Exec @ccResult = sp_OACreate 'TempCommon.DataUtils', @ccObject Out

    --Get Component Version Number
    PRINT 'sp_OAMethod (GetProperty) Version'
    EXEC @ccResult = sp_OAMethod @ccObject, 'Version', @ccVersion Out

    -- CLOSE THE CELEBRO COMMON
    PRINT 'sp_OADestroy TempCommon.DataUtils Version '
    EXEC @ccResult = sp_OADestroy @ccObject
    PRINT @ccVersion



So should i be doing this through OLE automation stored procedure or through CLR Integration. Please help, i am really confused and new at it. Any help would be really appreciated.

Thanks
Ajmal


Éric MoreauSenior .Net Consultant
CERTIFIED EXPERT
Top Expert 2016

Commented:
>>I don't think this CLR integration would work fine with Server 2000. Am i right?

It doesn't work at all. It was a new feature of SQL 2005

Commented:
">>I don't think this CLR integration would work fine with Server 2000. Am i right?

It doesn't work at all. It was a new feature of SQL 2005"

That's true. Since this is the SQL Server 2005 zone i assumed it was 2005 :D

Author

Commented:
So what should i do?? If i have both SQL server 2000 and SQL server 2005???

Commented:
I believe what you need is to use extended stored procedure. MSDN reference: http://msdn.microsoft.com/en-us/library/aa215797.aspx

Another article that might help: http://www.devarticles.com/c/a/SQL-Server/Extended-Stored-Procedures-Intro-And-10-Cool-Examples/

Commented:
So use extended stored proc for SQL Server 2000, and CLR integration with 2005.

Author

Commented:
Ok. I don't have to worry about SQL server 2000 anymore. Now please guide me how to do this for SQL server 2005.  I need to access a DLL which gets registered in GAC. I can also provide the dll in case we can't retrieve it from GAC. Please provide detailed step. I am a beginner and having a hard time to follow those MSDN articles. So i have a method called GetInfo in my dll that does some computation and i want to access it from a stored procedure. Also this dll in turns refer to few other dll's. a sample code with steps would be really really appreciated.

Thanks in advance
-Ajmal
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
Thanks Makolyte. I was able to figure out the solution of my problem. The only thing that i haven't been able to figure out is Untill i add my .dll in SQL server 2005 through CREATE ASSEMBLY <assembly name> from '<location>' WITH PERMISSION_SET = SAFE it does not shows up as add refernce in the SQL server project. Now when i try to add my .dll through create assembly it shows me that my .dll is looking for some other dll. I tried copying the .dll from GAC to my dll folder . But since my dll was referencing some 15 other dll's i have to copy all of them in the folder. It did work but i am sure this isn't the right way to do it.  Shouldn't it look for the refernced dll's in the GAC? How to do this?
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
Following steps are required to create a T-SQL stored procedure which references .NET dll's(CLR Integration)
 
1) In SQL Server Management add the assembly to the database. My DLL1.dll references DLL2 and 15 other .NET dll's. I copied all these dll to the folder containing DLL1 as it was trying to locate these dll's.  The following query registers the DLL1 in the database.
 
 
CREATE ASSEMBLY DLL1
FROM 'c:\test\DLL1.dll'
WITH PERMISSION_SET = UNSAFE
 
 
2) In Visual Studio create a new SQL server project and add a stored procedure through new item  to the project. Add reference to DLL1 and DLL2.
 
using
DLL1;
using DLL2;
 
public
partial class StoredProcedures
{
[Microsoft.SqlServer.Server.
SqlProcedure]
public static void GetVersion()
{
SqlPipe p;
p =
SqlContext.Pipe;
p.Send(
UtilsData.Version);
SqlPipe q;
ExportUtils objExports = new ExportUtils();
q =
SqlContext.Pipe;
q.Send(objExports.Version);
}
};
 
 
3)Compile the stored procedure with the following command:
csc.exe /t:library /out:StoredProcedures.dll StoredProcedures.cs
First navigate to directory of your project in command prompt before executing above command.
4) Create an assembly from the .dll and add to the database
 
CREATE ASSEMBLY [SqlServerProject]
 FROM 'StoredProcedures.dll'
 
5) Create a T-SQL Stored Procedure which access the external assembly SqlServerProject
CREATE PROCEDURE TestProcedure
EXTERNAL NAME
[SqlServerProject].[StoredProcedures].[GetVersion]

Author

Commented:
Check my earlier message for complete solution.

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
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.