Solved

How to call a .NET dll from a Stored Procedure

Posted on 2008-09-29
16
1,217 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

Today I had a very interesting conundrum that had to get solved quickly. Needless to say, it wasn't resolved quickly because when we needed it we were very rushed, but as soon as the conference call was over and I took a step back I saw the correct …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…

726 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