Post reply

Executing vb.net dll in sql server 2005 stored procedure

  • July 15, 2009 at 4:15 am

    #132965

    Hi,

    I've created a dll in visual basic.net 2005. i want to call the dll in my sql server 2005 stored procedure. (note sql server 2005 is upgraded from sql server 2000) so its not supporting creating assembly in sql server 2005.

    my vb.net code,

    Imports System

    Imports System.Reflection

    Imports System.IO

    Namespace nscreatetxt

    Public Class clscreatetxt

    Public Sub New()

    End Sub 'New

    Public Sub createtxt()

    Dim SW As StreamWriter

    SW = File.CreateText("\\file1\softusers\ps_prakash\MyTextFile.txt")

    SW.WriteLine("Hello Mom")

    SW.WriteLine("Hello GrandMom")

    SW.Close()

    End Sub 'createtxt

    End Class 'clscreatetxt

    End Namespace 'nscreatetxt

    my stored procedure code:

    -- Scratch variables used in the script

    DECLARE @retVal INT

    DECLARE @comHandle INT

    DECLARE @errorSource VARCHAR(8000)

    DECLARE @errorDescription VARCHAR(8000)

    DECLARE @retString VARCHAR(100)

    ---2146232576

    -- Initialize the COM component.

    EXEC @retVal = sp_OACreate '{3045481F-66C0-3EE5-BE5C-2DA49E90AFCA}', @comHandle OUTPUT

    print @retVal

    IF (@retVal <> 0)

    BEGIN

    -- Trap errors if any

    EXEC sp_OAGetErrorInfo @comHandle, @errorSource OUTPUT, @errorDescription OUTPUT

    SELECT [Error Source] = @errorSource, [Description] = @errorDescription

    RETURN

    END

    -- Release the reference to the COM object

    EXEC sp_OADestroy @comHandle

    i created the dll, generated strong name, registered in gac and using regasm i registered in registry after that i executed the above sql statements its returning some negative value.

    my questions is

    1) can i call a dll (which is created in visual studio 2005) inside a stored procedure of sql server 2005

    2) can i use sp_oacreate procedures in sql server 2005

    3) send me detail code how to dll in sql server 2005 stored procedures

    Thanks & Regards,

    P.Prakash

  • July 15, 2009 at 2:27 pm

    #1025359

    I can't send you examples, but the proper terminology for calling external code from SQL is either "EXTENDED Stored Procedure" or "CLR (Common Language Runtime)". I suggest you research those terms in BOL (books online) and the forums/articles here.

    __________________________________________________

    Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
    Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

  • July 15, 2009 at 6:15 pm

    #1025427

    Thanks you very much for your reply i'll look in to it

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply