Using COM DLLs from SQL
in [Programming SQL]
|
From: Mark on 7 Jul 2010 05:50 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 avalible 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 seperate 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 permisions 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 Roman Rehak wrote: You have to add the assembly to the database. 03-Aug-07 You have to add the assembly to the database. Once it's there, you should be able to add reference to it from your SQL CLR project in Visual Studio. It should show a list of assemblies considered "safe" by Microsoft, plus a list of assemblies loaded into the database. This example loads System.Drawing: CREATE ASSEMBLY [System.Drawing.dll] FROM 'C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\System.Drawing.dll' WITH PERMISSION_SET = UNSAFE Roman -- Roman Rehak http://sqlblog.com/blogs/roman_rehak <sacha.stanton(a)gmail.com> wrote in message news:1186173769.687924.90870(a)l70g2000hse.googlegroups.com... Previous Posts In This Thread: On Friday, August 03, 2007 4:42 PM sacha.stanto wrote: Can I add a reference to an external DLL in a managed CLR C# stored procedure? Hi, my managed CLR stored procedures are working fine, but I need to import a .NET C# DLL that contains specialized functions I need to call. Is this possible? I can't seem to find any way to add the reference. Thank you. On Friday, August 03, 2007 5:45 PM Roman Rehak wrote: You have to add the assembly to the database. You have to add the assembly to the database. Once it's there, you should be able to add reference to it from your SQL CLR project in Visual Studio. It should show a list of assemblies considered "safe" by Microsoft, plus a list of assemblies loaded into the database. This example loads System.Drawing: CREATE ASSEMBLY [System.Drawing.dll] FROM 'C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\System.Drawing.dll' WITH PERMISSION_SET = UNSAFE Roman -- Roman Rehak http://sqlblog.com/blogs/roman_rehak <sacha.stanton(a)gmail.com> wrote in message news:1186173769.687924.90870(a)l70g2000hse.googlegroups.com... Submitted via EggHeadCafe - Software Developer Portal of Choice Overriding Deserialization of Persisted Workflow Instances http://www.eggheadcafe.com/tutorials/aspnet/40fe2b27-ef79-45d0-82e1-6eca3ed67df8/overriding-deserialization-of-persisted-workflow-instances.aspx
From: Bob Barrows on 7 Jul 2010 07:32 Your subject line says it's a COM dll, which means that all the CLR stuff is irrelevant (CLR is only for .Net dlls unless you are using the .Net Interop library to instantiate and use the COM dll). Have you used regsrvr32 to register the dll on your SQL Server machine? Are you using the correct class string in your call to sp_OACreate? Check the documentation for the class. It doesn't strictly have to be, but typically, the class string is a two-part name, such as "adodb.connection" or "scripting.filesystemobject". If your subject line is in error, and this is actually a .Net dll rather than a COM dll, then you cannot use sp_OACreate to use it. You have to create a CLR procedure or function. Mark Brend wrote: > 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 avalible 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 seperate 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 permisions 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 > > > > Roman Rehak wrote: > > You have to add the assembly to the database. > 03-Aug-07 > > You have to add the assembly to the database. Once it's there, you > should be > able to add reference to it from your SQL CLR project in Visual > Studio. It > should show a list of assemblies considered "safe" by Microsoft, plus > a list > of assemblies loaded into the database. > > This example loads System.Drawing: > > CREATE ASSEMBLY [System.Drawing.dll] > FROM > 'C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\System.Drawing.dll' > WITH PERMISSION_SET = UNSAFE > > Roman > > -- > Roman Rehak > http://sqlblog.com/blogs/roman_rehak > > <sacha.stanton(a)gmail.com> wrote in message > news:1186173769.687924.90870(a)l70g2000hse.googlegroups.com... > > Previous Posts In This Thread: > > On Friday, August 03, 2007 4:42 PM > sacha.stanto wrote: > > Can I add a reference to an external DLL in a managed CLR C# stored > procedure? > Hi, > > my managed CLR stored procedures are working fine, but I need to > import a .NET C# DLL that contains specialized functions I need to > call. > > Is this possible? I can't seem to find any way to add the reference. > > Thank you. > > On Friday, August 03, 2007 5:45 PM > Roman Rehak wrote: > > You have to add the assembly to the database. > You have to add the assembly to the database. Once it's there, you > should be > able to add reference to it from your SQL CLR project in Visual > Studio. It > should show a list of assemblies considered "safe" by Microsoft, plus > a list > of assemblies loaded into the database. > > This example loads System.Drawing: > > CREATE ASSEMBLY [System.Drawing.dll] > FROM > 'C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\System.Drawing.dll' > WITH PERMISSION_SET = UNSAFE > > Roman
|
Pages: 1 Prev: Need Help for a Join Query Next: Cannot read data from server easily? |