SQL Server XML Import - Error calling COM component method - sp_OAMethod

Hello,

I'm migrating servers (going from SQL Server 2005 to 2008).  I have a job that accesses an external XML file and then imports the data into SQL Server 2005.  I've migrated over to a new server but can't get the job to execute.  I've tracked it down to something related to sp_OAMethod I believe.  Not sure if it's a permission issue or an installation setup / version issue or something else.

sp_OACreate and sp_OASetProperty seem to execute without error... but then this seems to return the error:


  exec @err = sp_OAMethod @objHTTP, 'load', @resp_int out, @addr

  if ( @err <> 0 ) begin

    exec sp_OAGetErrorInfo @objHTTP, @err_src out, @err_desc out

    raiserror( 'dbo.xml_handle_get> Error calling COM component method 0x%x, %s, %s', 16, 1, @err, @err_src, @err_desc ) with seterror

    return @err

  end


Thanks in advance!
adrian78Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

adrian78Author Commented:
Hi Everyone,
In the job, one of the stored procedures calls another stored procedure.  When I execute the stored procedure which has the error directly, I think it executes properly... but when I execute the parent stored procedure, it seems as though the child stored procedure results in an error.  Could it be a scope issue difference between SQL Server 2005 and 2008 (or maybe just a setting?).
Thanks!
0
Mark WillsTopic AdvisorCommented:
Well, there is one setting, but would have expected other errors and does not explain why it works directly...

sp_configure 'show advanced options', 1;
go
RECONFIGURE;
go
sp_configure 'Ole Automation Procedures', 1;
go
RECONFIGURE;
go

Open in new window


They are extended stored procedures, and to use the OLE Automation routines, the calling user must be a member of the sysadmin role.

Maybe the owner of the parent stored procedure is someone else ?

Can you provide the actual error message ?
0
adrian78Author Commented:
Thanks for the message. I tried running that command already and no difference:


Configuration option 'show advanced options' changed from 1 to 1. Run the RECONFIGURE statement to install.
Configuration option 'Ole Automation Procedures' changed from 1 to 1. Run the RECONFIGURE statement to install.

I also added the users to the sysadmin but that didn't work.

The error message is:

Message
Executed as user: 3185665\MSSQL_USER. Microsoft (R) SQL Server Execute Package Utility  Version 10.0.5500.0 for 64-bit  Copyright (C) Microsoft Corp 1984-2005. All rights reserved.    Started:  2:47:42 PM  Error: 2013-10-01 14:47:48.16     Code: 0xC002F210     Source: db_update Execute SQL Task     Description: Executing the query "declare @err int    set @err = 0    begin tran    EXEC @..." failed with the following error:

dbo.xml_handle_get> Error calling COM component method 0x80070057, (null), (null).

Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.  End Error  DTExec: The package execution returned DTSER_FAILURE (1).  Started:  2:47:42 PM  Finished: 2:47:48 PM  Elapsed:  5.46 seconds.  The package execution failed.  The step failed.
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

Mark WillsTopic AdvisorCommented:
Right... will need to play a bit...  Could be the params or data have changed.
0
adrian78Author Commented:
It still runs on the old server though.
0
adrian78Author Commented:
I figured it out - jeez, something it's the simplest things that are the issues.  There was some data missing in the DB that it was depending on.  Thanks for all of your help.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Mark WillsTopic AdvisorCommented:
I just finished writing/testing up a procedure and got it working as well :)

Which is a good thing, because I had not used OLE automation in SQL2012 from scratch on my new dev machine. So, thank you for giving me the reason to actually do it !!

Glad to hear you have solved it...

Cheers,
Mark
0
adrian78Author Commented:
Red herring - was simply a data issue with data missing from a table.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.