?
Solved

How to call a .NET dll from a Stored Procedure

Posted on 2008-09-29
16
Medium Priority
?
1,255 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 6
  • 3
16 Comments
 
LVL 70

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 70

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
Command Line Tips and Tricks

The command line is a powerful tool at the disposal of every Linux user. Although Linux distros come with beautiful user interfaces, it's worthwhile to learn the command line because it allows you to do a number of things that you otherwise cannot do from the GUI.  

 

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 70

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
 
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 750 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 750 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 750 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

Amazon Web Services EC2 Cheat Sheet

AWS EC2 is a core part of AWS’s cloud platform, allowing users to spin up virtual machines for a variety of tasks; however, EC2’s offerings can be overwhelming. Learn the basics with our new AWS cheat sheet – this time on EC2!

Question has a verified solution.

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

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…
Performance in games development is paramount: every microsecond counts to be able to do everything in less than 33ms (aiming at 16ms). C# foreach statement is one of the worst performance killers, and here I explain why.
This course is ideal for IT System Administrators working with VMware vSphere and its associated products in their company infrastructure. This course teaches you how to install and maintain this virtualization technology to store data, prevent vuln…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…

719 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