Call vb.Net developed dll in SQL Server 2005 with configuration level 80 then gets error "I

Hi,

I want to call a dll from Stored procedure developed in SQL Server 2005 at configuration level 80. but when I execute the stored procedure I get the following error.

Error Source: ODSOLE Extended Procedure

Description: Invalid class string

Code of stored procedure and vb.net class is given below:

VB.Net

Imports System

Imports System.IO

Imports System.Data

Imports System.Data.SqlClient

Imports System.Data.SqlTypes

Imports Microsoft.SqlServer.Server

Imports Microsoft.VisualBasic

Imports System.Diagnostics

Public Class PositivePay

Public Shared Sub LogToTextFile(ByVal LogName As String, ByVal newMessage As String)

' impersonate the calling user

Dim newContext As System.Security.Principal.WindowsImpersonationContext

newContext = SqlContext.WindowsIdentity.Impersonate()

Try

Dim w As StreamWriter = File.AppendText(LogName)

LogIt(newMessage, w)

w.Close()

Catch Ex As Exception

Finally

newContext.Undo()

End Try

End Sub

End Class

===============================================================

STORED PROCEDURE

Create PROCEDURE [dbo].[PPGenerateFile]

AS

BEGIN

Declare @.retVal INT

Declare @.comHandler INT

declare @.errorSource nvarchar(500)

declare @.errorDescription nvarchar(500)

declare @.retString nvarchar(100)

-- Intialize the COM component

EXEC @.retVal= sp_OACreate 'PositivePay.class', @.comHandler OUTPUT

IF(@.retVal <> 0)

BEGIN

--Trap errors if any

EXEC sp_OAGetErrorInfo @.comHandler,@.errorSource OUTPUT, @.errorDescription OUTPUT

SELECT [error source] = @.errorsource, [Description] = @.errordescription

Return

END

-- Call a method into the component

EXEC @.retVal = sp_OAMethod @.comHandler,'LogToTextFile',@.retString OUTPUT, @.LogName = 'D:\text.txt',@.newMessage='Hello'

IF (@.retVal <>0 )

BEGIN

EXEC sp_OAGetErrorInfo @.comHandler,@.errorSource OUTPUT, @.errorDescription OUTPUT

SELECT [error source] = @.errorsource, [Description] = @.errordescription

Return

END

select @.retString

END

sp_OACreate is used to invoke a OLE object. Assuming you are wanting to leverage SQLCLR since this is the forum you are posting the question in to do this you would create a class in .Net, reference it in a CLR procedure, and deploy both the class and the procedure.

If you are wanting to leverage the older (.80/2000) methods you can try creating a Service Component (assuming this is still supported in .Net 2.0, this exploses .Net assemblies via COM+) and then use sp_OACreate to invoke the type.

Derek

|||

Hi Derek,

Basicaly our application is using SQL Server 2005 with configuration level 80. So i have to use older method to call com component from SQL Server using stored procedure as shown in already posted code.
Please review my code and help me what should i do to perfom my task successfuly. I give you again some description about the task.
Basicaly a job will call the stored procedure at some settled time. That stored procedure will innvoke the component (whose developed in VB.Net using Framework 2.0). That component will get some record from database and will write them in simple .txt file.

So please tell me by revewing my above code that what should i do?
Best Regards,
Jawad Naeem

|||

Try this:

use the type library export, TlbExp.exe via the .Net Framework SDK command prompt, you can use the "/?" to show it's full syntax.

Then Register your new type via RegSvr32.exe

Then test invoking it from say a VBScript using Dim oTest oTest = CreateObject()

If the test succeeds, then use the sp_OACreate in a TSQL script to invoke the type successfuly.

The end goal here is to create a basic COM component from a managed assembly. TSQL's consumption of the object is a mute point. Any automation-aware environment could consume it.

HTH,

Derek

|||

Hi,

Please tell me the steps with more detail, like using code example

Thanks

|||I am assuming your new thread was the same issue as this one, thus please see your other thread for my response...and give me two answers if it solves your problem lol :)