calling dll's / com's from SQL 2005

Question

calling dll's / com's from SQL 2005

MarkBrendsEmail

Grasshopper

Points: 11
More actions
July 7, 2010 at 4:15 am

#226598

Hello,
I am out of ideas!
I just keep getting:-
Error Source Description
ODSOLE Extended Procedure Invalid class string
When I run the code below
DECLARE @comHandle INT
DECLARE @errorSource VARCHAR(8000)
DECLARE @errorDescription VARCHAR(8000)
DECLARE @retString VARCHAR(100)
-- Initialize the COM component.
EXEC @retVal = sp_OACreate '[SQL-2-XStreamBridge]' , @comHandle OUTPUT
IF (@retVal <> 0)
BEGIN
-- Trap errors if any
EXEC sp_OAGetErrorInfo @comHandle, @errorSource OUTPUT, @errorDescription OUTPUT
SELECT [Error Source] = @errorSource, [Description] = @errorDescription
RETURN
END
I have kept notes on how I have config SQL to try and get this working, see below
-----------------------------
GETTING SQL SETUP AND WORKING
-----------------------------
In order for SQL to trust this dll it has been signed with a strong name. This option is not available because the SQL-2-XStreamBridge.dll uses TPXStreamAccess.dll which is a 3rd party dll and does not have a strong name.
You will need to setup a separate database ie SQL-2-XStreamBridgeDB
Add a users to this DB SQL-2-XStreamBrdige and make them the dbo
next open the properties window of the root of all DB's ie SQL-01\INFOCENTRE
select permissions and ensure that this user has UnSafe Assembly granted.
all done.
click on the new SQL-2-XStreamBridge DB and then open a new query window
copy and paste the code below in to that windows and execute each section
sp_configure 'clr enable', 1
GO
RECONFIGURE
GO
ALTER DATABASE [SQL-2-XStreambridge] SET TRUSTWORTHY ON
GO
CREATE ASSEMBLY TPXStreamAccess
AUTHORIZATION dbo
FROM 'C:\Program Files\SQL-2-XStreamBridge\TPXStreamAccess.dll'
WITH PERMISSION_SET = UNSAFE
GO
CREATE ASSEMBLY [SQL-2-XStreamBridge]
AUTHORIZATION dbo
FROM 'C:\Program Files\SQL-2-XStreamBridge\SQL-2-XStreamBridge.dll'
WITH PERMISSION_SET = UNSAFE
GO

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

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

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 1

Ummm... you only use sp_OACreate for OLE Automation not SQLCLR. If you registered the SQLCLR assembly in the database, you then have to create the necessary Function/Stored Procedure object stubs for the objects that exist in your assembly. I don't see where you issued any CREATE FUNCTION/CREATE PROCEDURE statements in the setup code. For example if you have:

SQL-2-XStreamBridge.SomeClass.InSomeNameSpace.SomeFunctionName

You would need to create the TSQL Wrapper function around that after creating the assembly by doing something like:

CREATE FUNCTION SomeFunctionName(@x int, @y int)

RETURNS int

EXTERNAL NAME SQL-2-XStreamBridge.[SQL-2-XStreamBridge.SomeClass.InSomeNameSpace].SomeFunctionName