adrian78
asked on
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!
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!
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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.
Right... will need to play a bit... Could be the params or data have changed.
ASKER
It still runs on the old server though.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
ASKER
Red herring - was simply a data issue with data missing from a table.
ASKER
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!