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  Transact-SQL (2000)  Problem with sp_OAMethod ... 'LoadFromSQLServer'

Author  Topic  AlanS Starting Member 28 Posts Posted - 2007-10-05 : 12:21:48 I've developed several hundred lines of T-SQL to extract all the text from the stored procedures, UDFs and DTS packages on any given instance of SQL Server. I'm having a problem with a part of the process (shown in CODE SECTION A, below), which is designed to (a) get a list of all DTS packages, (b) for each package in the list create a new package object (first "EXEC @rc" statement), (c) load the package from SQL Server into the newly created package object (second "EXEC @rc" statement), and (d) pass the package's object token and name to the _SearchProcessDTS stored procedure, which extracts all the text from the package's various collections. This code runs flawlessly against my local instance of SQL Server, correctly and accurately extracting all the desired information. When I run it against a remote instance (installed on another machine on our network), step (c) consistently fails for every DTS package. The error reporting routines (shown in CODE SECTION B, below) always show an error code of -2147024888 (0x80070008), and that the stored procedure sp_OAGetErrorInfo itself fails when trying to obtain the usual information on that error code. I am using Windows authentication, and am assigned to the System Administrators server role on the remote instance of SQL Server. One other quirk here is that yesterday, while experimenting with enlarging the size of certain variables and table fields, this code worked ONCE against the remote server. Since then, however, it has reverted to the error status described above, even though I've left those sizes at their increased values (and they appear to have no connection with the code shown here). What am I doing wrong? -- CODE SECTION A: -- DECLARE variables DECLARE @DTSPackages TABLE (PackageName varchar(1000)) DECLARE @CurrentPkg varchar(1000) DECLARE @rc int DECLARE @ObjectToken int DECLARE @location varchar(100) DECLARE @SvrName varchar(1000) -- populate @DTSPackages table INSERT INTO @DTSPackages (PackageName) SELECT [name] FROM msdb..sysdtspackages WHERE [name] LIKE dbo._GetSearchConfig('DTSPackageMask') -- process DTS packages SET @SvrName = CAST(SERVERPROPERTY('ServerName') AS varchar) PRINT '** Server Name: ' + @SvrName WHILE (SELECT COUNT(*) FROM @DTSPackages) > 0 BEGIN SET @CurrentPkg = (SELECT TOP 1 PackageName FROM @DTSPackages ORDER BY PackageName) DELETE FROM @DTSPackages WHERE PackageName = @CurrentPkg SET @location = '_SearchPopulate:1:DTS Packages:' + @CurrentPkg EXEC @rc = sp_OACreate 'DTS.Package', @ObjectToken OUTPUT IF @rc <> 0 EXEC sp_displayoaerrorinfo @ObjectToken, @rc, @location SET @location = '_SearchPopulate:2:DTS Packages:' + @CurrentPkg EXEC @rc = sp_OAMethod @ObjectToken, 'LoadFromSQLServer', NULL, @SvrName, '', '', '256', '', '', '', @CurrentPkg IF @rc <> 0 EXEC sp_displayoaerrorinfo @ObjectToken, @rc, @location PRINT '** DTS Package: ' + @CurrentPkg EXEC _SearchProcessDTS @ObjectToken, @CurrentPkg END -- CODE SECTION B: -- CREATE sp_hexadecimal stored procedure IF EXISTS (SELECT [id] FROM sysobjects WHERE ([name] = 'sp_hexadecimal') AND (xtype = 'P')) DROP PROCEDURE sp_hexadecimal GO CREATE 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 GO -- CREATE sp_displayoaerrorinfo stored procedure IF EXISTS (SELECT [id] FROM sysobjects WHERE ([name] = 'sp_displayoaerrorinfo') AND (xtype = 'P')) DROP PROCEDURE sp_displayoaerrorinfo GO CREATE PROCEDURE sp_displayoaerrorinfo @object int, @hresult int, @locationcode varchar(100) = null AS DECLARE @output varchar(255) DECLARE @hrhex char(10) DECLARE @hr int DECLARE @source varchar(255) DECLARE @description varchar(255) DECLARE @hrhex2 char(10) --PRINT 'OLE Automation Error Information' EXEC sp_hexadecimal @hresult, @hrhex OUT --SELECT @output = ' HRESULT: ' + @hrhex --PRINT @output EXEC @hr = sp_OAGetErrorInfo @object, @source OUT, @description OUT IF @hr = 0 BEGIN --SELECT @output = ' Source: ' + @source --PRINT @output --SELECT @output = ' Description: ' + @description --PRINT @output PRINT '[' + ISNULL(@locationcode, '#') + '] OLE Automation Error; Code: ' + CAST(@hresult AS varchar) + ' (' + @hrhex + '); Src: ' + @source + '; Desc: ' + @description END ELSE BEGIN --PRINT ' sp_OAGetErrorInfo failed.' EXEC sp_hexadecimal @hr, @hrhex2 OUT PRINT '[' + ISNULL(@locationcode, '#') + '] sp_OAGetErrorInfo failed with error code ' + CAST(@hr AS varchar) + ' (' + @hrhex2 + ') on call for error code ' + CAST(@hresult AS varchar) + ' (' + @hrhex + ')' RETURN END GO