sp_OACreate error on SQL Server 2005

Question

hi there,

I have a stored procedure and trying to run "sp_OACreate" but fall over with the following message:

Msg 50000, Level 16, State 1, Procedure HTTP_REQUEST, Line 26 sp_OACreate on MSXML2.XMLHttp.4.0 failed

That occurs because I am setting a variable to get the return value of sp_OACreate and raise the above error if the return value is not 0. Now, the return value I get is "-6" but i don't know why it is happening? Any help on this would be good to help make this work.

Answer 1

Has the DBA disabled the use of stuff like this? Can you execute other sp_OACreate code without issue?

Answer 2

Thoughts:

Is this enabled?

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

Can you use SQL CLR? Even at the best of times, sp_OA% was not recommended. SQL CLR allows things to be done more efficiently and easily.

Are you trying to use CLR via sp_OA%? It's not allowed, KB 32284

Answer 3

Found what was going wrong. Basically was using the wrong version of 'MSXML2.ServerXMLHTTP'. Was calling sp_OACreate 'MSXML2.ServerXMLHTTP.4.0' when'MSXML2.ServerXMLHTTP.5.0' or above were the right ones to use.