Solved

How to call a .NET dll from a Stored Procedure

Posted on 2008-09-29
16
1,192 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
0
Comment
Question by:Ajmal82
  • 7
  • 6
  • 3
16 Comments
 
LVL 69

Expert Comment

by:Éric Moreau
ID: 22596396
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
0
 
LVL 69

Expert Comment

by:Éric Moreau
ID: 22596411
from http://msdn.microsoft.com/en-us/library/ms131052.aspx: "This assembly can be found in the Global Assembly Cache (GAC)"
0
 
LVL 4

Expert Comment

by:Makolyte
ID: 22596619
0
 

Author Comment

by:Ajmal82
ID: 22597820
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


0
 
LVL 69

Expert Comment

by:Éric Moreau
ID: 22597847
>>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
0
 
LVL 4

Expert Comment

by:Makolyte
ID: 22598052
">>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
0
 

Author Comment

by:Ajmal82
ID: 22598203
So what should i do?? If i have both SQL server 2000 and SQL server 2005???
0
 
LVL 4

Expert Comment

by:Makolyte
ID: 22598584
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/
0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 4

Expert Comment

by:Makolyte
ID: 22598612
So use extended stored proc for SQL Server 2000, and CLR integration with 2005.
0
 

Author Comment

by:Ajmal82
ID: 22604977
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
0
 
LVL 4

Accepted Solution

by:
Makolyte earned 250 total points
ID: 22610050
0. Create the class in C#, SEE CODE SNIPPET

1. Enable CLR integration in SQL Server: http://msdn.microsoft.com/en-us/library/ms131048.aspx

2. This gives SQL access to your DLL: CREATE ASSEMBLY <assembly name> from '<location>' WITH PERMISSION_SET = SAFE

3. This creates a function you can execute in SQL from the CLR function:
CREATE PROCEDURE <name>
@i nchar(25) OUTPUT --Or whatever output if you have any
AS
EXTERNAL NAME <dll name>.<class>.<method>

4. This is how you execute the procedure:
DECLARE @J nchar(25) --Or whatever output type you have
EXEC <name> @J out


using System;

using System.Data;

using Microsoft.SqlServer.Server;

using System.Data.SqlTypes;
 

public class <YourClass>

{

    [Microsoft.SqlServer.Server.SqlProcedure]

    public static void HelloWorld(out string text)

    {

        SqlContext.Pipe.Send("Hello world!" + Environment.NewLine);

        text = "Hello world!";

    }

}

Open in new window

0
 

Author Comment

by:Ajmal82
ID: 22617097
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?
0
 
LVL 4

Assisted Solution

by:Makolyte
Makolyte earned 250 total points
ID: 22618404
Hey,

Read this article, specifically the answer: http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=457181&SiteID=17

If you add one DLL that is dependant on others, it will automatically look in the folder which you specified for the other DLLs. Does that help you?
0
 
LVL 4

Assisted Solution

by:Makolyte
Makolyte earned 250 total points
ID: 22618429
Also the article states this:
" CustomMarshalers
Microsoft.VisualBasic
Microsoft.VisualC
mscorlib
System
System.Configuration
System.Data
System.Data.OracleClient
System.Data.SqlXml
System.Deployment
System.Security
System.Transactions
System.Web.Services
System.Xml"

These are the only assemblies that SQL Server allows CLR to load from GAC"

Meaning you're gonna have to stick all your dependencies in one folder and have it automatically detect them, instead of using the GAC.
0
 

Author Comment

by:Ajmal82
ID: 22653916
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]

0
 

Author Closing Comment

by:Ajmal82
ID: 31501164
Check my earlier message for complete solution.
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
crm development 2 37
Need to Modify a Script I found 5 77
Chat Room 1 28
Calling stored proc in EDMX 11 19
This article is for Object-Oriented Programming (OOP) beginners. An Interface contains declarations of events, indexers, methods and/or properties. Any class which implements the Interface should provide the concrete implementation for each Inter…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

705 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now