sp_OACreate in SQL Server 2005, Fails with 'Not enough storage is available to complete this op

Hi all,

I have created a small COM in C# so that I can programatically create and execute stored procedures with SMO. At this point the COM has nothing in it but just a test prototype.
But when I tried to create the object as follows, I get the error indicated below.
It is not a memory issue because I have adequate storage and RAM.

Please Help!

DECLARE @objectint
DECLARE
@hrint
DECLARE
@propertyvarchar(255)
DECLARE @returnvarchar(255)
DECLARE @srcvarchar(255), @descvarchar(255)

-- Create an object.
EXEC @hr= sp_OACreate'SQLInterop.CsharpHelper', @object OUT
IF @hr<> 0
BEGIN
EXEC
sp_OAGetErrorInfo @object, @src OUT, @desc OUT
SELECT hr=convert(varbinary(4),@hr), Source=@src,Description=@desc
RETURN
END

This is the error I am getting:

Error Code: 0x8007000E
Description: Not enough storage is available to complete this operation.
Source: ODSOLE Extended Procedure


This is the C# code for the COM:

using System;
using System.Runtime.InteropServices;
using System.Reflection;
using System.Runtime.CompilerServices;
using System.EnterpriseServices;

// General Information about an assembly is controlled through the following
// set of attributes. Change these attribute values to modify the information
// associated with an assembly.
[assembly:AssemblyTitle("CSServer")]
[assembly:
AssemblyDescription("Test SQL .NET interop")]
[assembly:
AssemblyDelaySign(false)]
[assembly:
AssemblyKeyFile("MyKey.snk")]

// Setting ComVisible to false makes the types in this assembly not visible
// to COM components. If you need to access a type in this assembly from
// COM, set the ComVisible attribute to true on that type.
[assembly:ComVisible(true)]

// The following GUID is for the ID of the typelib if this project is exposed to COM
[assembly:Guid("ff35c6b4-81bf-47dd-9290-fcbbb49008d9")]

// Version information for an assembly consists of the following four values:
//
// Major Version
// Minor Version
// Build Number
// Revision
//
// You can specify all the values or you can default the Revision and Build Numbers
// by using the '*' as shown below:
[assembly:AssemblyVersion("1.0.0.1")]
[assembly:
AssemblyFileVersion("1.0.0.1")]

// the ApplicationName attribute specifies the name of the
// COM+ Application which will hold assembly components
[assembly:ApplicationName("SQLInterop")]

// the ApplicationActivation.ActivationOption attribute specifies
// where assembly components are loaded on activation
// Library : components run in the creator's process
// Server : components run in a system process, dllhost.exe
[assembly:ApplicationActivation(ActivationOption.Server)]
namespace SQLInterop
{
publicinterfaceITest
{
string SayHello();
string SayIt(String strMessage);
}

//[SecurityRole("RBSecurityDemoRole", SetEveryoneAccess = true)]
[ComVisible(true)]
[
CLSCompliant(false)]
[
ClassInterface(ClassInterfaceType.None)]
publicclassCSharpHelper :ITest
{
publicstring SayHello()
{
return"Hello from CSharp";
}

publicstring SayIt(String strMessage)
{
return strMessage +": from CSharp";
}
}
}

[8994 byte] By [sobo1] at [2007-12-30]
# 1
OA_Create will have problems when you are call .net com. better option is to use CLR
DineshAsanka at 2007-9-5 > top of Msdn Tech,SQL Server,Transact-SQL...
# 2

Hi Dinesh,

Thank you for the quick response and the suggestion to use CLR. The problem is that I have already tried that, incorporating SMO. But when I issue the following command, I get the error shown below.

CREATE ASSEMBLY ACDataBridge
FROM
'H:\User\Development\Projects\Applications\ACDataBridge\bin\Debug\Projects.Applications.ACDataBridge.dll'
WITH PERMISSION_SET = UNSAFE

Msg 6544, Level 16, State 1, Line 1
CREATE ASSEMBLY for assembly 'Projects.Applications.ACDataBridge' failed because assembly 'microsoft.sqlserver.batchparser' is malformed or not a pure .NET assembly.
Unverifiable PE Header/native stub.

sobo1 at 2007-9-5 > top of Msdn Tech,SQL Server,Transact-SQL...
# 3

sp_OACreate is use full for SQL Server 2000 & calling the COM components.

here you have option to call your C# code directly using the CLR code.. use those features instead of converting your C# code to COM. It will be faster & safer & robust than SP_OACreate..

ManiD at 2007-9-5 > top of Msdn Tech,SQL Server,Transact-SQL...
# 4

Hi ManID,

Thanks for your suggestion, but that does not solve my problem because I need to use SMO in the C# code contained in SQL CLR Assembly, but I get this error when I tried to create assembly as follows:

CREATE ASSEMBLY ACDataBridge
FROM
'H:\User\Development\Projects\Applications\ACDataBridge\bin\Debug\Projects.Applications.ACDataBridge.dll'
WITH PERMISSION_SET = UNSAFE

Msg 6544, Level 16, State 1, Line 1
CREATE ASSEMBLY for assembly 'Projects.Applications.ACDataBridge' failed because assembly 'microsoft.sqlserver.batchparser' is malformed or not a pure .NET assembly.
Unverifiable PE Header/native stub.

This is very sad. What good is SQL CLR if you cannot even employ such commonly used API as SMO. If this functionality does not meet my needs, I may have to resort to using Extended Stored Procedure or Native C++ COM.

Thanks.

sobo1 at 2007-9-5 > top of Msdn Tech,SQL Server,Transact-SQL...
# 5
Hi ManiD,

Thank you for your suggestion to use CLR. The problem is that I have already tried that, incorporating SMO. But when I issue the following command, I get the error shown below. I don't know what good SQL CLR is if I cannot use it to meet my needs. Do I have to use Extended Stored Procedure or Native COM+ to make this work?

CREATE ASSEMBLY ACDataBridge
FROM
'H:\User\Development\Projects\Applications\ACDataBridge\bin\Debug\Projects.Applications.ACDataBridge.dll'
WITH PERMISSION_SET = UNSAFE

Msg 6544, Level 16, State 1, Line 1
CREATE ASSEMBLY for assembly 'Projects.Applications.ACDataBridge' failed because assembly 'microsoft.sqlserver.batchparser' is malformed or not a pure .NET assembly.
Unverifiable PE Header/native stub.

sobo1 at 2007-9-5 > top of Msdn Tech,SQL Server,Transact-SQL...
# 6

I am having same problem.

CLR inside SQL server does not support creation of assembly if i am using something else there. Over that now I am not even able to make COM objects.

How am I going to work with this?

AnEaglesEyes at 2007-9-5 > top of Msdn Tech,SQL Server,Transact-SQL...

SQL Server

Site Classified