0

I posted this question on the MYOB support forums and their support team responded to say that I'd be better contacting someone familiar with SQL server.

I am trying to get data from our MYOB file into SQL server. I have found the below code however it won't retrieve any data (I think I need to enter username and password as parameters but I'm not sure how).

Following is my code (with the company file information removed)

Declare @Object as Int;
Declare @ResponseText as Varchar(8000);

Exec sp_OACreate  'MSXML2.XMLHTTP'
                , @Object OUT;
Exec sp_OAMethod   @Object
                , 'open'
                , NULL
                , 'get'
                , 'http://localhost:8080/AccountRight/fccce.....6e/GeneralLedger/TaxCode/?api-version=v2'
                , 'false'
Exec sp_OAMethod @Object
                , 'send'
Exec sp_OAMethod  @Object
                , 'responseText'
                , @ResponseText OUTPUT

Select @ResponseText

Exec sp_OADestroy @Object

Can anyone assist me with this?

Thank you.

jakethepeg1
  • 1
  • 1
  • The above code was taken from [Stack Overflow Question 22067593](http://stackoverflow.com/questions/22067593/calling-an-api-from-sql-server-stored-procedure) – jakethepeg1 Feb 26 '15 at 04:12
  • First getting it working with a simpler setup, i.e. create a VBScript (or whatever you are comfortable with) on the server, run and debug it. This will allow you to discover any things you are missing like logins etc. Jumping straight into sp_OA sp's in SQL server is too many layers of complexity for it to work first time or allow you to debug – Nick.Mc Mar 02 '15 at 00:04

1 Answers1

1
  1. You're running this on the server so you ought to be instantiating an 'MSXML2.ServerXMLHTTP' object.
  2. To authenticate to your company file, try Exec sp_OAMethod @Object, 'open', NULL, 'GET', 'http://localhost:8080/[Your URL]', 'false', 'Administrator', '[admin password]'
  3. To get your response you might have to call sp_OAGetProperty rather than sp_OAMethod
  4. The API returns JSON or HTML based on the format query string parameter (default is JSON). Since the API has an OData interface, and OData does support XML responses, you could try requesting an XML response. To do this you add an Accept header: Exec sp_OAMethod @Object, 'setRequestHeader' 'Accept', 'application/xml'.
  5. The reason I'm flailing about a bit is while I understand what you're trying to achieve, the API wasn't designed with the MSXML2 library in mind. I agree that your URL is fine - if it wasn't the API team would have set you straight.
  6. Normally what you'd do is define a DTO that you then initialise using the JSON returned from the API, and then map the DTO into your database. Check out http://developer.myob.com/api/accountright/code-samples-sdks/ They've got .Net, Java, PHP, Node and Ruby. Appreciate you're looking to cut out the code in the middle, so good luck with the Accept header.
asmith1024
  • 315
  • 1
  • 6
  • Still having issues with this. I have made the changes but still no result. My url is fine (if I CTRL + click it it opens and shows me the data). I have tried sp_OAGetErrorInfo to see if that told me anything but no errors... any ideas? – jakethepeg1 Feb 27 '15 at 03:54
  • I think you've hit the nail on the head with the format query string parameter. The API returns JSON or HTML. I've edited the response with one more thing you can try and then a suggestion for moving forward. – asmith1024 Mar 01 '15 at 23:53