?
Solved

Executing sp_OAMethod and getting an error

Posted on 2009-04-15
1
Medium Priority
?
620 Views
Last Modified: 2012-05-06
Running SQL server 2000 as a member server attached to an Small Business Server 2003 domain.
All is fully patched.

See the code I have supplied.

When the code:
EXEC @intPkgErr      = sp_OAMethod @oPKG      ,
          'LoadFromSqlServer("ML150", "", "", 256, , , "IN_pGainLossSpread_110")' , NULL

is executed, error 2147467259 is returned.

What does this mean?

/*
  \\sbs2003server\E-drive\Investing - Programming\Scripts\Stored Procedures\Spreads Calc dev\pSpreadCalc_050
 
   Run the GainLoss Spread programs.
*/
 
-- Must feed UserID from some mechanism.
 
  DECLARE @strUserID		varchar(20)
  
  DECLARE @strFunctionID	varchar(200)
  DECLARE @strSymbol		varchar(7)
  DECLARE @strComments		varchar(3000)
  DECLARE @intPatternID		int
  DECLARE @intPkgErr		int
  DECLARE @oPKG			int
  DECLARE @intError		bigint
 
  SET     @intError 	= 0
  SET	  @strUserID	=	 'System'
  
  EXEC pSpreadCalc_100 @strUserID
 
  EXEC ptblGainLossSpread_maintain 'System'
 
  EXEC @intPkgErr	= sp_OACreate 'DTS.Package', @oPKG OUT
  
  IF (@intPkgErr <> 0)
  	BEGIN
    		SET  @intError = @intPkgErr
    		SET  @strComments = 'In Procedure pSpreadCalc_050 Label 100 '
    				  + 'Error ' 
                	          + CAST(@intError AS CHAR(9)) 
                        	  + ' occurred'
    		EXEC ptblErrorLog
      		  @strUserID		= @strUserID		,
      		  @strFunctionID	= @strFunctionID	,
      		  @intPatternID		= @intPatternID		,
      		  @strSymbol		= @strSymbol		,
      		  @strComments		= @strComments		,
      		  @strCallingProgram	= 'pSpreadCalc_050'
   	END
 
-- Load the DTS pkg
 
  If (@intPkgErr = 0 )
    BEGIN
      EXEC @intPkgErr	= sp_OAMethod @oPKG	,
	    'LoadFromSqlServer("ML150", "", "", 256, , , "IN_pGainLossSpread_110")' , NULL
    END
 
-- Test
SELECT @intPkgErr as '@intPkgErr'
--
 
  IF (@intPkgErr <> 0)
  	BEGIN
    		SET  @intError = @intPkgErr
    		SET  @strComments = 'In Procedure pSpreadCalc_050 Label 200 '
    				  + 'Error ' 
                	          + CAST(@intError AS CHAR(900)) 
                        	  + ' occurred'
    		EXEC ptblErrorLog
      		  @strUserID		= @strUserID		,
      		  @strFunctionID	= @strFunctionID	,
      		  @intPatternID		= @intPatternID		,
      		  @strSymbol		= @strSymbol		,
      		  @strComments		= @strComments		,
      		  @strCallingProgram	= 'pSpreadCalc_050'
   	END
                          
  Return
-- Return @intError

Open in new window

0
Comment
Question by:donpick
1 Comment
 
LVL 13

Accepted Solution

by:
St3veMax earned 1500 total points
ID: 24156006
Try adding SELECT ERROR_MESSAGE(), ERROR_Number() and see if that gives you any further information.
0

Featured Post

Microsoft Certification Exam 74-409

VeeamĀ® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question