March 18, 2019 at 11:43 am
I have created the following sproc for calling a URL to return XML. However, we dont have access to run sp_OAMethod. The call is below. My question is there an alternative way to call a URL to return XML?
Call:
DECLARE @ResponseXML XML
EXEC IntLDMCallSAPURL '' , @ResponseXML OUTPUT
CREATE PROCEDURE [dbo].[IntCallURL]
-- Add the parameters for the stored procedure here
@URL as varchar(250)
, @ResponseXML XML OUTPUT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @Object as Int;
DECLARE @Response TABLE (ResponseXML XML)
Exec sp_OACreate 'MSXML2.XMLHTTP', @Object OUT;
Exec sp_OAMethod @Object, 'open', NULL, 'get', @SAP_URL, 'false'
Exec sp_OAMethod @Object, 'send'
INSERT INTO @Response ( ResponseXML )
Exec sp_OAMethod @Object, 'responseXML.xml'
Exec sp_OADestroy @Object
SELECT @ResponseXML = ResponseXML
FROM @Response
END
March 18, 2019 at 12:06 pm
Here is the error I get in our QA enviroment:
Msg 229, Level 14, State 5, Procedure sp_OACreate, Line 1
The EXECUTE permission was denied on the object'sp_OACreate', database 'mssqlsystemresource', schema 'sys'.
Msg 229, Level 14, State 5, Procedure sp_OAMethod, Line 1
The EXECUTE permission was denied on the object'sp_OAMethod', database 'mssqlsystemresource', schema 'sys'.
Msg 229, Level 14, State 5, Procedure sp_OAMethod, Line 1
The EXECUTE permission was denied on the object'sp_OAMethod', database 'mssqlsystemresource', schema 'sys'.
Msg 229, Level 14, State 5, Procedure sp_OAMethod, Line 1
The EXECUTE permission was denied on the object'sp_OAMethod', database 'mssqlsystemresource', schema 'sys'.
Msg 229, Level 14, State 5, Procedure sp_OADestroy, Line1
The EXECUTE permission was denied on the object'sp_OADestroy', database 'mssqlsystemresource', schema 'sys'.
March 21, 2019 at 3:52 am
Never done it, but if you have CLR enabled, you might be able to use that instead of the old VBScript functions.
https://sqlsunday.com/2013/03/03/web-requests-using-clr-proc/
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply