Stephen Smith's Blog

Musings on Machine Learning…

Fun with SData

with 24 comments


SData is Sage’s new REST based Web Services API used by many of our applications. I’ve blogged about SData a few times already: SData in Sage ERP Accpac 6, More on SData and Sage ERP Accpac 6 and Stateful SData. Calling something a RESTful Web Services API sounds pretty technical and difficult; however, this blog post will try to show that using SData isn’t all that hard and that perhaps it provides and easier to use access method than using SQL or other APIs.

In fact, you might think that you need a complicated SDK with a giant reference manual to use SData. In fact you don’t need an SDK at all. Since SData is based on web standards, many tools already know how to deal with SData. In previous blog posts I showed how SData queries were just URLs that you can just enter in a browser like Chrome and then see the XML response in the browser window. Not very friendly. In this article we’ll show how you can use standard open source tools to play with SData and then how even commercial tools like Microsoft Excel have the capability to deal with SData since Excel knows how to interpret web standards. All the documentation for SData is also freely availalble at


cURL is an open source command line utility and library. You can use the command line utility to execute SData commands to retrieve data and to insert/update/delete records. cURL is a handy utility to anything you like with things that use the HTTP protocol including requests to web sites and web services.

For instance you can issue the command:

curl –user “ADMIN:ADMIN” http://localhost/SDataServlet/sdata/sageERP/accpac/SAMINC/Customers

which will return all the customer records from the Accpac SAMINC database. The –user parameter is used to set the login, note that these must be in upper case for Accpac. Change localhost with your own server name and replace SAMINC with the company id that you want to use.

You can read a specific record by specifying the index as in the following example that reads good old Ronald Black:

curl –user “ADMIN:ADMIN” http://localhost/SDataServlet/sdata/sageERP/accpac/SAMINC/Customers(‘1200’)

Try playing with the query language specified at to retrieve just the records you want. For instance:

curl –user “ADMIN:ADMIN” http://localhost/SDataServlet/sdata/sageERP/accpac/SAMINC/Customers?where=IDCUST gt ‘1500’

to get customers with codes greater than 1500.

You can also insert records with cURL, for instance issue the command:

curl –user “ADMIN:ADMIN” http://localhost/SDataServlet/sdata/sageERP/accpac/SAMINC/Customers -X POST -T cust.xml

where cust.xml contains:

<?xml version=”1.0″ ?>
<entry xmlns:sdata=”
<title>Some title</title>
<TEXTSTRE1>865 W. 14th Ave.</TEXTSTRE1>
<TEXTPHON1>(604) 555-1234</TEXTPHON1>

Note: With these examples, if you try them and have problems, check the double quotes, Word and blogging software tends to change regular double quotes to the slanted ones which then doesn’t work in XML. Also before user its intended to be two dashes not a long dash.

SData from Excel

Excel has a very good ability to query web sites and then parse the data returned. Excel knows about most web standards including the Atom feeds used by SData. You do this by creating a data connect from the web and specify the SData URL as indicated in the dialog below:

It will prompt you for a user id and passwork, enter your Accpac credentials (all in upper case). There are a lot of extra fields and URLs returned in the worksheet that is produced. But in the middle is all the data that you want from Accpac.

Now you can create a more user friendly worksheet based on this one. You can refresh the data anytime you like and you can automate the collection with a VBA macro. So in a new worksheet you can create charts linked to this data, do calculations on it, or manipulate it any other way you like using Excel.

Finding SData URLs

So how do you know what are the SData feed available from an application? You can run Fiddler to spy on the HTTP traffic to see what requests are being made (similar to other Accpac Spy programs).

But SData is actually fairly self-documenting. For instance if you want to get the list of all SData resources for a given dataset then enter:

curl –user “ADMIN:ADMIN” http://localhost/SDataServlet/sdata/sageERP/accpac/SAMINC

We call this a shortened URL and it returns a feed of all the SData resources that you can use at the next level of the URL. In the same way if you enter:

curl –user “ADMIN:ADMIN” http://localhost/SDataServlet/sdata/sageERP/accpac

it will return to you a feed that contains a list of all the Accpac datasets (companies) on that server.

If you want to get information on a feed you can use a $schema call, so if you issue:

curl –user “ADMIN:ADMIN” http://localhost/SDataServlet/sdata/sageERP/accpac/SAMINC/Customers/$schema

you will get back a description of all the fields in the feed.


Many people view SData as a developer technology for ISVs to integrate with Sage products. But I think for partners and integrators that are comfortable performing customizations and integrations using SQL, that SData provides another rich approach to consider. I think that especially for newer graduates entering careers installing, customizing and maintaining ERP systems, that URLs and REST based Web Services will be the much more natural way to go.


Written by smist08

August 6, 2011 at 5:47 pm

Posted in sage 300

Tagged with , , ,

24 Responses

Subscribe to comments with RSS.

  1. I had developed webservice client by using jersey client(java technology), this client is able to connect and down load the data from Sage Accpac System by using Sdata.

    But when I’m calling recursive calls for finding updated records in Accapac System , the Sdata web service failing during middle of the process .

    Example :

    Sage accapac system has configured 10,000 items .

    Integrated system(Webstore Application) downloading first time 10,000 items from Accpac System, Sdata webservice failing middle of the process,if 1000 items then Sdata working fine .

    Failure Case 1 : If more than 2000 items then Sdata webservice failing in middle of the process , each time fetching 50 items by using start index and count in Sdata.

    Failure Case 2 : If downloaded 1000 items to webstore then I’m checking item has updated in Accpac system, this case I’m calling recursive call for each item verification this case after 50 recursive calls it’s failing.

    Once downloaded all the ERP items to Integrated system(webstore) then will check every 10 mins any updates in Accpac system.

    This case I’m calling recursive calls each record has any updated recorded in Accpac System or not. Can you suggest how to get only updated records certain date for item and item pricing by using Sdata.


    • The call that fails should contain an SData diagnostic message, it would be useful to know what the text message is that is embedded in that XML response.


      August 16, 2011 at 3:19 pm

      • Hi Smith,

        I had done Sdata integration with Accpac 6.0 ,Sdata fails after 260 calls, exact the same request .
        I had decompiled java code of Sdata of failed classes then I saw that logic.

        For every request create object this is getting token. if cookie object exist then not creating each request please let me know in java what i needs to set cookie for skiping each request authorization will reuse existing session.

        The object establish every time new sql connection…during Sdata requests observed in sql server, it had reached 512 processes based on this info i’m suspecting every request establishing new sql connection, I think .

        I don’t want authorize every request how can i do setup for this in java jersey client cookie setup …please look following code and suggest me ….

        WebResource service;
        String authentication;
        ClientConfig config = new DefaultClientConfig();
        Client client = Client.create(config);
        service = client.resource(ExternalInterfaceConfig.getBaseUri());
        authentication = “Basic “+ encodeCredentialsBasic(ExternalInterfaceConfig.getUsername(),
        Feed feed = null;
        int sidx = 1, eidx = 100;
        BOBase returnTypeBO = null;
        ArrayList results = new ArrayList();

        List feedResults = new ArrayList();

        synchronized (this)
        feed = service.path(erpResourceName)
        .queryParam(“startIndex”, String.valueOf(sidx))
        .queryParam(“count”, String.valueOf(eidx))
        .header(AUTHENTICATION_HEADER, authentication)

        feed = service.path(erpResourceName)
        .queryParam(“startIndex”, String.valueOf(sidx))
        .queryParam(“count”, String.valueOf(eidx))
        .header(AUTHENTICATION_HEADER, authentication)



        for (Entry entry : feed.getEntries())
        sidx += 100;

        } while (feed.getEntries().size() >= 100);



        December 14, 2011 at 11:17 pm

      • Perhaps check that the JSessionID in the HTTP headers is remaining intact. When you connect with Accpac, our Tomcat server will assign a Jsessionid to the first call from that workstation. If you don’t include this in following calls then you will get a new session. We have a limit of 256 sessions currently. These will timeout after 1/2 hour to be re-used, but you should make sure cookies in the HTTP header are preserved.


        December 16, 2011 at 6:02 pm

      • Hi smith,

        Thanks for your response.

        I have tried as you suggested Keeping JSESSIONID in cookie in http request header but still creating new session in accpac side if JESSIONID exist in the request.

        I have done some trail keeping X-SAGE-SDATA-ACCPAC-SESSION-ID in http request header .

        I have tried to set X-SAGE-SDATA-ACCPAC-SESSION-ID in header and it going to check existing session.
        But Sdata response does’t have X-SAGE-SDATA-ACCPAC-SESSION-ID.
        How to get X-SAGE-SDATA-ACCPAC-SESSION-ID from SData Response for reusing the session.

        Could you please suggest, how to use same session by using java clients to avoid accpac side multiple session creations.



        January 9, 2012 at 2:15 am

      • You might want to try doing an open-session call and using that session id (you create it). If you run Fiddler on the portal you can see how it does an open-session call and then uses the session token after that.


        January 9, 2012 at 11:23 pm

  2. How does one go about creating a reference to the API in a .NET project?

    Kevin Silha

    August 16, 2011 at 4:51 pm

  3. […] the Accpac COM API had in the VB world. I’ve blogged on SData  a few times: here, here, here and here. Basically we want to limit ourselves to at most one server call per user interaction. The VB UIs […]

  4. […] Sage ERP Accpac 6, More on SData and Sage ERP Accpac 6, Stateful SData, On the Sage GCRM Contract, Fun with SData, A Roadmap for SData Integration to Sage Products or Defining SData Feeds for Sage 300 ERP. Jarett […]

  5. Hi Steve, it seems the IDCUST field is required in the SData payload. If IDCUST consists of all customer numbers rather than IDs, then is there a way to retrieve the next incremental customer number/key? It would be nice if it was not required and simply auto-increment key.


    June 18, 2012 at 2:31 am

    • There isn’t a concept of get-next on a single record. Usually if you get a feed of customers, then it will return by default, 20, and then there are URLs included that can get the previous 20 and the next 20. Generally this is the way that you browse data with SData.


      June 18, 2012 at 3:15 pm

  6. If I wanted to return JSON from an SData feed in Sage 300, do I need to change anything on the server side (i.e. JSON version of resource map files)? Whether I add Accept: application/json;vnd.sage=sdata as a cURL request header or append ?format=application/json;vnd.sage=sdata/$diagnosis to a URL, it’s still returning XML.


    December 18, 2015 at 8:01 pm

    • Sorry but the Sage 300 SData feeds only support XML and don’t support JSON.


      December 18, 2015 at 8:23 pm

  7. how would you retrieve a list of transactions based on Nominal code?

    Anthony Philp

    May 18, 2016 at 11:19 am

    • You get lists of anything by just doing a get on that SData resource. So you could do this for G/L Journal Entries or O/E Orders for instance. Exactly the same way you get the list of customers, just a different resource.


      May 18, 2016 at 5:56 pm

  8. For sdata is the only user available to authenticate ADMIN? or can sage users be assigned the ability to use SDATA?

    B Man

    April 24, 2017 at 11:25 pm

    • You can use any user. They will have their regular rights, so if they can post an order via the UI then they can do it via sdata.


      April 24, 2017 at 11:34 pm

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: