July 15, 2009 at 4:15 am
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
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
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