troubleshooting Question

Error from Stored Procedure too few arguments it says.

Avatar of Westside2004
Westside2004Flag for United States of America asked on
Microsoft SQL Server
2 Comments2 Solutions282 ViewsLast Modified:
Hi,

I am trying to figure out this problem with a stored procedure but I am not sure what is wrong.  Basically one stored procedure is called at the application level, but this procedure in turn calls a couple of other stored procs.

I am trying to find out how to fix this problem.  Can anyone review this code and let me know if anything stands out.

The error message is:

Procedure or function sp_displayoaerrorinfo has too many arguments specified.

The error is returned from one of the "nested stored procedures"

From the application level, there are 3 parameters being passed into the main stored procedure.  I am not sure if the problem is there when the stored procedure is initially called or its one of the nested procedures.  I am 80% sure its the nested procedure(s) that are throwing the error, because the main procedure is named differently than what the error message states.  The error messages refers to sp_displayoaerrorinfo and that is not the "main" stored proc that is called, the one in the error is a nested one.

In any case, I am passing in the following parameters.

@Server
@PkgName
@ServerPWD

Here is the main procedure:

CREATE PROC spExecuteDTS
      @Server varchar(255),
      @PkgName varchar(255),                   -- Package Name (Defaults to most recent version)
      @ServerPWD varchar(255) = Null,            -- Server Password if using SQL Security to load Package (UID is SUSER_NAME())
      @IntSecurity bit = 1,                  -- 0 = SQL Server Security, 1 = Integrated Security
      @PkgPWD varchar(255) = ''            -- Package Password
AS

SET NOCOUNT ON
/*
      Return Values
      - 0 Successfull execution of Package
      - 1 OLE Error
      - 9 Failure of Package
*/
DECLARE @hr int, @ret int, @oPKG int, @Cmd varchar(1000)

-- Create a Pkg Object
EXEC @hr = sp_OACreate 'DTS.Package', @oPKG OUTPUT
IF @hr <> 0
BEGIN
      PRINT '***  Create Package object failed'
      EXEC sp_displayoaerrorinfo @oPKG, @hr
      RETURN 1
END

-- Evaluate Security and Build LoadFromSQLServer Statement
IF @IntSecurity = 0
      SET @Cmd = 'LoadFromSQLServer("' + @Server +'", "' + SUSER_SNAME() + '", "' + @ServerPWD + '", 0, "' + @PkgPWD + '", , , "' + @PkgName + '")'
ELSE
      SET @Cmd = 'LoadFromSQLServer("' + @Server +'", "", "", 256, "' + @PkgPWD + '", , , "' + @PkgName + '")'

EXEC @hr = sp_OAMethod @oPKG, @Cmd, NULL

IF @hr <> 0
BEGIN
            PRINT '***  LoadFromSQLServer failed'
            EXEC sp_displayoaerrorinfo @oPKG , @hr
            RETURN 1
END

-- Execute Pkg
EXEC @hr = sp_OAMethod @oPKG, 'Execute'
IF @hr <> 0
BEGIN
            PRINT '***  Execute failed'
            EXEC sp_displayoaerrorinfo @oPKG , @hr
            RETURN 1
END

-- Check Pkg Errors
EXEC @ret=spDisplayPkgErrors @oPKG

-- Unitialize the Pkg
EXEC @hr = sp_OAMethod @oPKG, 'UnInitialize'
IF @hr <> 0
BEGIN
            PRINT '***  UnInitialize failed'
            EXEC sp_displayoaerrorinfo @oPKG , @hr
            RETURN 1
END

-- Clean Up
EXEC @hr = sp_OADestroy @oPKG
IF @hr <> 0
BEGIN
      EXEC sp_displayoaerrorinfo @oPKG , @hr
      RETURN 1
END

RETURN @ret
GO
---------------------------

Here is the nested procedure

CREATE PROC sp_displayoaerrorinfo
      @object as int
AS
DECLARE @hr int
DECLARE @output varchar(255)
DECLARE @source varchar(255)
DECLARE @description varchar(255)
PRINT 'OLE Automation Error Information'
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
      END
ELSE
      BEGIN
            PRINT ' sp_OAGetErrorInfo failed.'
            RETURN
      END

GO
--------------------

The error is with the nested procedure, it has to be.  Can someone please help if at all possible and let me know how to fix this?  It previously worked fine, but now seems to throw this error.  I have spent a couple of days trying to find out and my sql is not that great, so perhaps someone has more experience and can help...

Thanks again!

-ws

Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 2 Answers and 2 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 2 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros