Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums  SQL Server 2000 Forums  SQL Server Development (2000)  sp_OACreate call causing 10077 Error?

Author  Topic  socnob Starting Member 10 Posts Posted - 2007-04-20 : 17:28:07 Issue: I am getting the following error message during creation of a COM class object when creating it via SQL Server's "sp_OACreate" extended stored procedure: ================= OLE Automation Error Information HRESULT: 0x0000275d Source: ODSOLE Extended Procedure Description: NULL ================= (Technically, the "Description" is blank/NULL, but I did an "isnull(@description, '')" to it, otherwise, this line doesn't print out at all.) The stored procedure that does this creation is called (indirectly) within another stored procedure's WHILE loop. For the 1st bunch of times this stored proc. is called, the COM class object is created with no problems (I cannot define "bunch" as an exact number because I did not check how many "creations" it works until failing, but seems to be at least 100 or so). The HRESULT value of "0x0000275d" is "10077" in decimal. I have not found much of anything about this error message online. About the only useful info I did find online dealing with this error number was @ http://www.webservertalk.com/archive132-2004-6-255191.html Based on that site's entry, it sounds like there is a maximum number of ODSOLE objects that can exist (MAX_ODSOLE_OBJECTS) and once that number is hit, any future attempts to create an ODSOLE object results in this "10077" error (with a NULL description - you think MS could have provided some description here). Question: 1.) Can anyone here verify that this error is directly related attempting to exceed the MAX_ODSOLE_OBJECTS? If so, why would this still be the case even when the COM class object is only known within executing stored procedure and according to Books Online: ========== Remarks If sp_OADestroy is not called, the created OLE object is automatically destroyed at the end of the batch. ========== 2.) Is the "automaic" destruction of OLE object's that SQL perform not done immediately upon end of a batch and thus eventhough my stored proc has been exited, the object could still in memory until SQL Server decides its a good time to do some clean up? Here is the code for my stored proc that does the creation of the COM class object and calls a method on that newly created object: =========================== CREATE PROCEDURE My_COMobj_UpdateTbl @xFatal bit OUT, @xServ varchar(100), @xDB varchar(100), @xSqlStmt varchar(1000) AS /* Input: @xServ - Name of the server which should be passed along as a parameter to the COM Class object's UpdateTable method. @xDB - Name of the database which should be passed along as a parameter to the COM Class object's UpdateTable method. @xSqlStmt - The SQL statement to be executed via the @object's UpdateTable method. Output: @xFatal - Return value indicating if an error occurred that should cause the whole process to halt. 0=Continue; 1=Stop processing This calls the "UpdateTable" method of the passed-in COM Class object (@object). This is done because in SQL 2000 stored procedures there is no way to trap/ignore certain fatal errors that occur during processing; thus, had to use another way to execute the SQL statement, trap it if something happened, and ignore those fatal errors that were raised by triggers. */ DECLARE @object int DECLARE @output varchar(255) DECLARE @hrhex char(10) DECLARE @hr int DECLARE @hr2 int DECLARE @source varchar(255) DECLARE @description varchar(255) DECLARE @errAt varchar(30) set @xFatal = 1 SET @errAt = '' set @source = '' set @description = '' EXEC @hr = sp_OACreate 'MyCOMDLL.MyClass', @object OUT, 4 IF @hr <> 0 BEGIN SET @errAt = '***obj Creation Err***' GOTO ObjectError END EXEC @hr = sp_OAMethod @object, 'UpdateTable', NULL, @xServ, @xDB, @xSqlStmt IF @hr <> 0 BEGIN GOTO ObjectError END set @xFatal = 0 return ObjectError: BEGIN print '@errAt = ' + @errAr EXEC @hr2 = sp_OAGetErrorInfo @object, @source OUT, @description OUT select @description = ltrim(isnull(@description, 'NULL')) IF @hr2 = 0 BEGIN IF ( --See if any of these are errors that we can ignore, i.e. Trigger Errors (CHARINDEX('CANNOT UPDATE', UPPER(@description)) = 1) OR (CHARINDEX('CANNOT INSERT', UPPER(@description)) = 1) OR (CHARINDEX('CHILDREN EXIST', UPPER(@description)) = 1) ) BEGIN goto IgnoreErrors END ELSE BEGIN print 'Error trying to run sql statement: ' + @xSqlStmt PRINT 'OLE Automation Error Information' EXEC sp_hexadecimal @hr, @hrhex OUT SELECT @output = ' HRESULT: ' + @hrhex PRINT @output SELECT @output = ' Source: ' + @source PRINT @output SELECT @output = ' Description: ' + @description PRINT @output return -1 END END ELSE BEGIN PRINT ' sp_OAGetErrorInfo failed in Echo_MP_UpdateTbl.' RETURN END RETURN END IgnoreErrors: set @xFatal = 0 return =========================== Also, here is the code for the "sp_hexadecimal" stored procedure which displays the error info (this is working fine and I just added it in case someone wants to look at it): =========================== ALTER PROCEDURE sp_hexadecimal (@binvalue varbinary(255), @hexvalue varchar(255) OUTPUT) AS DECLARE @charvalue varchar(255) DECLARE @i int DECLARE @length int DECLARE @hexstring char(16) SELECT @charvalue = '0x' SELECT @i = 1 SELECT @length = DATALENGTH(@binvalue) SELECT @hexstring = '0123456789abcdef' WHILE (@i <= @length) BEGIN DECLARE @tempint int DECLARE @firstint int DECLARE @secondint int SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1)) SELECT @firstint = FLOOR(@tempint/16) SELECT @secondint = @tempint - (@firstint*16) SELECT @charvalue = @charvalue + SUBSTRING(@hexstring, @firstint+1, 1) + SUBSTRING(@hexstring, @secondint+1, 1) SELECT @i = @i + 1 END SELECT @hexvalue = @charvalue ========================== Thanks for any info! P.S. This issue came about out of another issue I posted here called "Catch and Ignore existing RaiseError Message" (http://www.sqlteam.com/Forums/topic.asp?TOPIC_ID=81983).