Syntax in LoadFromSQLServer method

[bajo71]

Hello,
I'm trying to pass a parameter into the above referenced method. But the switching between VBA and T-SQL syntax is vexing me.

CREATE PROCEDURE spRun_DTS (@PackageName char(10))
AS
DECLARE @object int, @hr int
DECLARE @src varchar(255), @desc varchar(255)

EXEC @hr = sp_OAMethod @object, 'LoadFromSQLServer("OWS78002878791", "", "", 256, , , , & @PackageName & )'

If I hard code a value instead of @PackageName, then it works just fine. Any ideas?

Thanks

 

[AlexCuse]

@object is null.

Basically, you need to use the sp_OACreate stored procedure to create your object before you refer to it by its' handle.

Have a look at this article:

http://www.devx.com/getHelpOn/10MinuteSolution/20563

Hope it helps,

Alex

[small]----signature below----[/small]
I don't do any programming whatsoever

Ignorance of certain subjects is a great part of wisdom

 

[bajo71]

I've already used sp_OACreate(just not showing it), I'm only showing the part of the syntax that doesn't work. The code works fine if I hardcode the argument in the LoadFromSQLServer method. What I'm trying to do is pass the @PackageName parameter in the method. But it's going between VBA and T-SQL- I'm not sure it's possible.

Thanks

 

[ESquared]

Maybe this will get you started. I wrote this myself and it involved a LOT of hard work getting these error messages described, so I hope you and everyone else who finds this appreciates that...

These are my descriptions of the actual error message returned so if you don't like them blame me. This function is designed to be used immediately after an OLE call. You have to supply all the parameters. The context is the property or method that you just used, in order to make the error message come out intelligibly. Same for ObjectType and Value. If you were trying an OLE call to do, in effect, "MyObjectOfTypeBlah.DoSomething(2)" then you might choose parameters like so: Context - DoSomething, ObjectType - Blah, Value - 2.

CREATE FUNCTION ObjectErrorFunc (
   @Object int,
   @ReturnCode int,
   @Context varchar(1000), -- Property or Method
   @ObjectType varchar(8000), -- Object Name, when known
   @Value sql_variant -- Parameter value, if known
)
RETURNS varchar(8000)
AS
BEGIN
   DECLARE
      @ErrorMessage varchar(8000),
      @ErrorCode binary(4),
      @Source varchar(8000),
      @Description varchar(8000)

   SET @ErrorCode = convert(binary(4), @ReturnCode)

   EXEC sp_OAGetErrorInfo
      @Object,
      @Source OUTPUT,
      @Description OUTPUT

   IF @Context = '' SET @Context = 'Unknown'
   SELECT
      @ErrorMessage = 
         IsNull(@Source, 'OLE Unknown Source')
         + ' Error ' + IsNull('0x' + dbo.BinaryToHex(@ErrorCode), '0x???????')
         + ' - ' + Coalesce(Descr, LTrim(@Description), 'Unknown Error')
   FROM
      (
         SELECT Error = @ErrorCode
      ) E LEFT JOIN (
         SELECT Error = convert(binary(4), 0x80004005), Descr = convert(varchar(8000), 'Invalid OLE object handle: The specified handle value (' + convert(varchar(8000), @Object) + ') does not refer to a valid OLE object.')
         UNION ALL SELECT 0x80010108, 'The object invoked has disconnected from its clients: The previously valid referenced object has closed or stopped running since the last reference.'
         UNION ALL SELECT 0x80020003, 'Invalid property or method: property or method ''' + @Context + ''' was not found' + IsNull(' on OLE object of type ''' + @ObjectType + '.''', '.')
         UNION ALL SELECT 0x80020005, 'Type mismatch: data type of a Transact-SQL local variable used to store a return value of property or method ''' + @Context + ''' did not match the Visual Basic data type of the property or method return value. Or, the return value of property or method ''' + @Context + ''' was requested, but it does not return a value.'
         UNION ALL SELECT 0x80020006, 'Unknown name: property or method ''' + @Context + ''' was not found for the specified object' + IsNull(' ''' + @ObjectType + '.''', + '.')
         UNION ALL SELECT 0x80020008, 'Bad variable type: data type of a Transact-SQL value passed as a method parameter was NULL or did not match the Microsoft® Visual Basic® method parameter data type.'
         UNION ALL SELECT 0x8002000B, '''Subscript out of range'' on property or method ''' + @Context + IsNull(''' of object type ''' + @ObjectType, '') + '.'''
         UNION ALL SELECT 0x8002000E, 'Invalid number of parameters on property or method ''' + @Context + IsNull(''' of object type ''' + @ObjectType, '') + '.'''
         UNION ALL SELECT 0x80020011, '''Does not support a collection'' on property or method ''' + @Context + IsNull(''' of object type ''' + @ObjectType, '') + '.'''
         UNION ALL SELECT 0x80080005, 'Server execution failed: OLE object ''' + @Context + ''' is registered as a local OLE server (.exe file) but the .exe file could not be found or started.'
         UNION ALL SELECT 0x80040154, 'Class not registered'
         UNION ALL SELECT 0x800401F3, 'Invalid class string: OLE object ProgID/CLSID ''' + @Context + ''' is not registered on Server ''' + @@ServerName + '''. OLE automation servers need to be registered before they can be instantiated using sp_OACreate. This can be done using regsvr32.exe for inprocess (.dll) servers, or the /REGSERVER command-line switch for local (.exe) servers.'
         UNION ALL SELECT 0x8004275B, 'sp_OACreate - data type (' + @Context + ') or value ' + IsNull('(' + Convert(varchar(8000), @Value) + ')', '') + ' of parameter ''context'' is invalid. Valid context parameter values are 1, 4, and 5.'
         UNION ALL SELECT 0x80042727, 'sp_OASetProperty - improper usage: parameters are ObjPointer int IN, PropertyName varchar IN, @setval <any> IN [, additional indexing IN params].'
         UNION ALL SELECT 0x80042731, 'Output values of type Object require output parameters of type int.'
         UNION ALL SELECT 0x80042732, 'Output values of type Object are not allowed in result sets. Use a variable for parameter ''returnvalue'' with the OUTPUT keyword in order to return a handle to an object.'

         UNION ALL SELECT 0x80042742, 'Traversal string:  Bad whitespace: Error parsing method or property specification string ''' + @Context + '.'''
         UNION ALL SELECT 0x8007007E, 'Module could not be found: OLE object ''' + @Context + ''' is registered as an in-process OLE server (.dll file), but the .dll file could not be found or loaded.'
      )  X ON E.Error = X.Error
   RETURN @ErrorMessage
END

If you encounter other errors that I did not (which is why they wouldn't be in th elist) that should be added to the list, please post here.

[COLOR=black #d0d0d0]My alarm clock causes time travel. When I hit snooze, no apparent time passes before the alarm sounds again, but the universe has in fact moved forward through time by ten minutes![/color]