Skip to Content
0

Get OpportunityCollection and ServiceRequestCollection from SAP Cloud for Customer in SQL

Nov 09, 2017 at 11:46 AM

82

avatar image
Former Member

Hi all,

We have SAP Cloud for Customer installed in our system. We would like to access the tickets and opportunities to be imported in SQL Server. We know it can be obtained using the following URL:

https://mynnnnnn.crm.ondemand.com/sap/c4c/odata/v1/c4codata//OpportunityCollection?$format=xml

Same for the tickets, but using ServiceRequestCollection, however, we could not be able to connect via SQL. We tryed using the following SQL code, but we get errors regarding 'A certificate is required to complete client authentication'.

I am new in the use of Sap Cloud for Customer. Can you please tell me what I am doing wrong in the following code?

PS: We are using SQL Server 2012

Thanks.

USE tempdb
GO

IF OBJECT_ID('tempdb..#xml') IS NOT NULL DROP TABLE #xml
CREATE TABLE #xml ( yourXML XML )
GO

DECLARE @URL VARCHAR(8000) 

SELECT @URL = 'https://mynnnnnn.crm.ondemand.com/sap/c4c/odata/v1/c4codata//OpportunityCollection?$format=xml'

DECLARE @Response nvarchar(max)
DECLARE @XML xml
DECLARE @Obj int 
DECLARE @Result int 
DECLARE @HTTPStatus int 
DECLARE @ErrorMsg varchar(MAX)
DECLARE @UserName nvarchar(100)
DECLARE @Password nvarchar(100)

SET @UserName = 'myuser'
SET @Password = 'mypassword'

EXEC @Result = sp_OACreate 'MSXML2.ServerXMLHttp', @Obj OUT 

EXEC @Result = sp_OAMethod @Obj, 'open', NULL, 'GET', @URL, false, @UserName, @Password

EXEC @Result = sp_OAMethod @Obj, 'setRequestHeader', NULL, 'Content-Type', 'application/x-www-form-urlencoded'

EXEC @Result = sp_OAMethod @Obj, send, NULL, ''

EXEC @Result = sp_OAGetProperty @Obj, 'status', @HTTPStatus OUT 

INSERT #xml ( yourXML )
EXEC @Result = sp_OAGetProperty @Obj, 'responseXML.xml' 

SELECT *
FROM #xml
10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

1 Answer

Benny Huang May 17 at 06:22 AM
0

Hi M.C,

I have the same issue. Have you solved the problem? How do you solve it?

Thanks.

Benny Huang

Share
10 |10000 characters needed characters left characters exceeded