How to capture sp_OAMethod Messages?

• jc64_62

  SSC Enthusiast

  Points: 181

  More actions

  February 14, 2008 at 9:42 am

  #122237

  I have a test DTS package that has two connections. Connection one takes a flat file as a data source and connection two is the destination database table.

  I am setting the flat file name dynamically using sp_OASetProperty.

  When I run the DTS with existing file I see the data in the table and sp_OAMethod 'Execute' runs successfully. That is OK.

  When I run it with a file that doesn't exist I am getting no error message and sp_OAMethod 'Execute' runs successfully.

  My question is can I get a message saying that the file doesn't exist so I can report that fact to the user.

  My code is

  EXEC @ret = sp_OACreate 'DTS.Package', @dtsObject OUTPUT

  EXEC @ret = sp_OAMethod @dtsObject, 'LoadFromSQLServer', NULL,@servername,'','','256','','','','TestPakage'

  EXEC @ret = sp_OASetProperty @dtsObject, @connectionOneDataSource, @connectionOneDataSourceValue

  EXEC @ret = sp_OAMethod @dtsObject, 'Execute'

  IF @ret <> 0

  EXEC sp_OAGetErrorInfo @dtsObject, @src OUT, @desc OUT

  Thank you

• Vijaya Kadiyala

  SSCrazy Eights

  Points: 9119

  More actions

  February 20, 2008 at 1:16 pm

  #780374

  Hi,

  check out the below code this give you the exact error.

  EXEC @hr = sp_OAMethod @object, 'Connect', NULL, 'my_server',

  'my_login', 'my_password'

  IF @hr <> 0

  BEGIN

  EXEC sp_OAGetErrorInfo @object

  RETURN

  END

  for more information you can check out the belowlink

  http://technet.microsoft.com/en-us/library/ms174984.aspx

  Thanks -- Vj

  http://dotnetvj.blogspot.com

  Thanks -- Vijaya Kadiyala
  www.dotnetvj.com
  SQL Server Articles For Beginers