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
ENDThis 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]
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.
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..
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.
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.
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?