In the last issue of the SHAREPoint Kompendium Magazin (Band 6, 2014) I published an article in German about the brand new ECS Web Service Designer for SharePoint 2010/2013 from Theobald Software. The application is a desktop windows application allowing you to visually design SAP driven Web Services for SharePoint.

Theobald Software just released the ECS Web Service Designer for SharePoint 2010/2013. The designer is part of the product suite ERPConnect Services, a suite which allow you the complete range of data integration between SharePoint and your SAP system.

The Web Service Designer is a fantastic way to visually design and deploy no-code SOAP or OData web services for SharePoint. Each service operation can be designed using a workflow designer (see screenshots). The designer provides a couple of base, flow and SAP-related activities the user can choose from to build it’s operation logic.

The base and flow group of activities consist of the following:

Check Condition Activity (IfElse)

Switch Cases Activity

For Each Activity

Do While Activity

Custom Code Activity

Assign Value Activity

Add Item (to collection) Activity

The SAP-related group of activities consist of the following:

Execute Table Activity (reads data from a SAP table)

Execute Function (execute a SAP BAPI function module)

A key feature of the ECS Web Service Designer is the option to use C# expressions for field/property mappings.

Other feature highlights are:

One-Click and direct solution deployment on SharePoint servers

One-Click SharePoint solution generation

One-Click Visual Studio exporting (the visually designed web service can be exported as VS solution)

For more information about this brand new product, see Theoabld Software product site.

I have developed a new version of the popular ‚MSN-like Stock Quotes Add-In For Excel 2007‘ for Excel 2013. The user defined function PSQ is exactly the same as in previous version, but it will retrieve stock data asynchronously. This makes the Excel user interface more responsive to user actions, especially if you have to query for a lot of stock symbols.

A couple of weeks ago I have implemeted two HTML5 demos in C# using ASP.NET MVC, Web API and jQuery. The demos are displaying SAP customer and stock quantity data. The SAP/.NET connectivity is handled by the ERPConnect library from Theobald Software. The primary goal was to show how easily you can integrate good looking visual effects with jQuery.

The customer demo (CIS) adds hidden table rows with a horizontal silder showing an order quantity diagram. The stock quantity demo (SIS) displays mutliple table rows once the user clicks on the top row. In addition it will add an extra row showing a chart.

The samples are part of the ERPConnect library. You can download a trail version from Theobald Software. ERPConnect is a lean .NET assembly that lets you develop robust SAP interfaces without a great degree of effort and most of all, without an elaborate infrastructure or any additional middleware.

A few days ago I had a real strange problem while using HttpClient in combination with ASP.NET Web API and integrated windows authentication (IIS Express). To keep it simple, I always got a 401 (Unauthorized) error returned from the localhost website, even though I was setting up the HttpClient correctly (see screenshots) and the HttpClient was sending the authorization header (negotiate).

After researching a while I found out, that exactly the .fiddler URI addition is part of the problem for the 401 error I always received. Microsoft changed the logic of the NTLM authentication for some of the security classes in .NET version 3.5 SP1 (see link below) in order to avoid relefection attacks. A reflection attack is a method of attacking a challenge-response authentication system. The HttpClient uses those changed classes internally to connect to the website.

So, the solution is quite simple. We just need to add the registry key BackConnectionHostNames (important: Multi-Value String) to HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\ Control\Lsa\MSV1_0 section in the registry with the value localhost.fiddler. A full explanation about the security concepts in WIA you will find at Microsoft’s site: Changes to NTLM authentication for HTTPWebRequest in Version 3.5 SP1

You can easily reproduce this error by creating a standard MVC4 web application with a Web API controller using windows authentication (see screenshot for settings). Then you add a console application to the project from which access the REST API (ValuesController).

Theobald Software released a new product suite called ERPConnect Services for SharePoint 2010. The product contains a new kind of query language named XtractQL to query SAP business data in a SQL-like manner. XtractQL or XQL is a combination of SQL and ABAP language syntax statements to simplify querying SAP systems.

XtractQL allows querying SAP objects like tables, BW-Cubes and SAP Queries. In addition XtractQL executes function modules (BAPIs) and returns metadata for objects. It can even execute MDX statements. Each XQL query is returning a data table object as result set. In case of the execution function modules the caller must define the returning table explicitly. XtractQL is very useful in situations where you want or need to handle dynamic statements.

ERPConnect Services for SharePoint 2010 (ECS) provides an additional tool, the XtractQL Explorer (see screenshot below), to learn more about the query language and to test XQL queries. You can use this tool independent of SharePoint 2010, but you need access to a SAP system.

The screenshot shows different samples of XQL queries. The following list shows a couple of other samples of XQL queries you can use in your SharePoint applications:

SELECT TOP 5 * FROM T001W WHERE FABKL = ‚US‘

This query selects the top 5 records of the SAP table T001W where the field FABKL equals the value US.

This query selects all records and fields of the SAP table MARA using a custom SAP function module to retrieve the data called Z_XTRACT_IS_TABLE.

SELECT MAKTX AS [ShortDesc], MANDT, SPRAS AS Language FROM MAKT

This query selects all records of the SAP table MAKT. The result set will contains three fields named ShortDesc, MANDT and Language.

SELECT TOP 30 LIPS-LFIMG, LIPS-MATNR, TEXT_LIKP_KUNNR AS CustomerID

FROM QUERY ‚S|ZTHEO02|ZLIKP‘

WHERE SP$00002 BT ‚0080011000‘AND ‚0080011999‘

This statement executes the SAP Query „S|ZTHEO02|ZLIKP“ (name includes the workspace, user group and the query name). As you can see XtractQL extends the SQL syntax with ABAP or SAP specific syntax elements. This way you can define fields using the LIPS-MATNR format and SAP-like where clauses like „SP$00002 BT ‚0080011000‘AND ‚0080011999‘“.

SELECT * FROM BWQUERY ‚0D_DECU/VARDEMO01‘

WHERE MAT03 EQ ‚M03‘, SALESORG BT ‚1000‘ AND ‚3000

This query executes a query against a specific SAP BW cube using a where clause.

DESCRIBE FUNCTION ‚SD_RFC_CUSTOMER_GET‘ GET EXPORTS

This query returns metadata about the export parameters of the SAP function module.

DESCRIBE QUERY ‚G|ZTHEO1|ZTHEOSQUERY‘ GET FIELDS

This query returns metadata about the defined SQP Query.

EXECUTE FUNCTION ‚SD_RFC_CUSTOMER_GET‘

EXPORTS KUNNR=’0000003340′

TABLES CUSTOMER_T INTO @RETVAL;

This query executes the SAP function module SD_RFC_CUSTOMER_GET and returns as result the table CUSTOMER_T (defined as @RETVAL).

ERPConnect Services runtime provides three kind of libraries to integrate with your application: Silverlight, Desktop and SharePoint client library. See product documentation for more information.

Here an example of how to use the libraries in code, e.g. within a web part project:

using ERPConnectServices;

…

ERPConnectServiceClient client = new ERPConnectServiceClient();

DataTable dt = client.ExecuteXQL(„SELECT TOP 50 * FROM MAKT“);

The ExecuteXQL method call of the ERPConnect Service Application in SharePoint will execute the XtractQL command against the defined SAP system.

There is also another way to use XtractQL. ERPConnect Services for SharePoint 2010 includes a Visual Studio 2010 Designer plugin. Similar to the LINQ to SQL designer, the ECS designer creates automatically code for you, see screenshot below:

You will find more information about the XtractQL syntax and ERPConnect Services for SharePoint 2010 product itself at the website of Theobald Software. There is also an evaluation version available.