Moving SAP Business One from SQL Server to HANA: how to convert your queries, reports and formatted searches

Why your queries break on HANA

When a company moves SAP Business One from Microsoft SQL Server to SAP HANA, the data moves over cleanly. The custom SQL does not. Saved queries, alerts, formatted searches, Crystal Reports commands, add-on SQL and the transaction notification all carry T-SQL. HANA does not speak T-SQL.

Most of the changes are small. The trouble is that there are many of them, and one missed quote stops the whole query. This guide lists the changes we make most often, in the order we check them.

1. Quote every column name

This is the change that catches everyone first. HANA turns unquoted names into upper case. SAP Business One column names are mixed case: CardCode, DocTotal, U_MyField. So T0.CardCode becomes T0.CARDCODE, and HANA cannot find it.

The fix is double quotes around the name:

SELECT T0."DocNum", T0."CardCode", T0."DocTotal"
FROM "OINV" T0
WHERE T0."CANCELED" = 'N'

Two rules follow from this:

• Double quotes mean an identifier. Single quotes mean a string. In SQL Server you could sometimes get away with "N". On HANA, "N" is a column called N, and the query fails.
• Aliases are quoted too if you want to keep their case: AS "Customer Name".

2. Swap the common functions

These are the T-SQL functions we see in nearly every SAP B1 database, and their HANA equivalents.

A full before and after, from a typical overdue invoices query:

-- SQL Server
SELECT TOP 50
  T0.DocNum,
  T0.CardCode + ' - ' + T0.CardName AS Customer,
  CONVERT(varchar, T0.DocDueDate, 103) AS DueDate,
  DATEDIFF(day, T0.DocDueDate, GETDATE()) AS DaysLate,
  ISNULL(T0.Comments, '') AS Comments
FROM OINV T0
WHERE T0.DocStatus = 'O'
  AND T0.DocDueDate < DATEADD(day, -30, GETDATE())
ORDER BY DaysLate DESC

-- SAP HANA
SELECT TOP 50
  T0."DocNum",
  T0."CardCode" || ' - ' || T0."CardName" AS "Customer",
  TO_NVARCHAR(T0."DocDueDate", 'DD/MM/YYYY') AS "DueDate",
  DAYS_BETWEEN(T0."DocDueDate", CURRENT_DATE) AS "DaysLate",
  IFNULL(T0."Comments", '') AS "Comments"
FROM "OINV" T0
WHERE T0."DocStatus" = 'O'
  AND T0."DocDueDate" < ADD_DAYS(CURRENT_DATE, -30)
ORDER BY "DaysLate" DESC

You can paste your own T-SQL into our free SQL to HANA converter to get a first draft of these changes.

3. Keep the B1 parameters as they are

Saved queries that ask the user for input use [%0], [%1] and so on. SAP Business One replaces these before the SQL reaches the database, so they stay the same on HANA:

SELECT T0."DocNum", T0."DocDate", T0."DocTotal"
FROM "ORDR" T0
WHERE T0."DocDate" >= [%0]
  AND T0."DocDate" <= [%1]

Formatted searches work the same way. References like $[$38.1.0] or $[ORDR."CardCode"] are filled in by the client. Only the SQL around them needs converting. Watch the quoting inside the field reference: the table and column follow the same HANA rules.

4. Stored procedures and temp tables need SQLScript

Find-and-replace stops working here. T-SQL procedures use @variables, #temp tables, SELECT INTO, WHILE loops and cursors. HANA procedures are written in SQLScript, which has a different shape:

• Variables are declared with DECLARE and read with a colon prefix: :lv_total.
• Intermediate results usually go into table variables (lt_open = SELECT ...;), not temp tables.
• HANA does have local temporary tables (also named with #), but you must create them with explicit columns first. There is no SELECT INTO #temp.
• Each statement ends with a semicolon.

-- SQLScript: open order value per customer
CREATE PROCEDURE "KN_OPEN_ORDERS" (IN p_from DATE)
LANGUAGE SQLSCRIPT AS
BEGIN
  lt_orders = SELECT "CardCode", "DocTotal"
              FROM "ORDR"
              WHERE "DocStatus" = 'O' AND "DocDate" >= :p_from;

  SELECT "CardCode", SUM("DocTotal") AS "OpenValue"
  FROM :lt_orders
  GROUP BY "CardCode";
END;

Cursor-heavy procedures are worth redesigning as set-based queries while you are in there. HANA rewards that.

5. SBO_SP_TransactionNotification

On HANA, SAP Business One ships the transaction notification as a SQLScript procedure. It receives the same inputs as before (object type, transaction type, key columns and values) and returns the error code and message at the end with a SELECT ... FROM DUMMY.

Your T-SQL validation blocks must be rewritten inside that procedure. A typical block looks like this:

IF :object_type = '17' AND (:transaction_type = 'A' OR :transaction_type = 'U') THEN
  SELECT COUNT(*) INTO cnt
  FROM "ORDR"
  WHERE "DocEntry" = :list_of_cols_val_tab_del
    AND IFNULL("NumAtCard", '') = '';
  IF :cnt > 0 THEN
    error := 1;
    error_message := 'Customer reference is required';
  END IF;
END IF;

Declare cnt at the top of the procedure. Keep each rule small and test add and update separately. One syntax error here blocks every document in the company, so deploy it outside working hours.

6. Crystal Reports commands

Crystal Reports built on SQL Server often use a Command (a pasted SQL statement) or call a stored procedure. Both carry T-SQL. For each report:

1. Convert the command SQL or the procedure to HANA syntax.
2. Point the report at the HANA data source with Database > Set Datasource Location, using the HANA connection, credentials and company schema.
3. Check that parameter fields still map to the command parameters.
4. Preview it in the SAP Business One HANA client and compare the output with the SQL Server version.

Reports built on plain tables, without commands, usually only need step 2.

7. A testing approach that catches mistakes

A converted query that runs is not the same as a converted query that is right. String concatenation with + is the classic trap: on HANA it may fail, or it may try to add values as numbers. Date formats are another.

1. Inventory first. Export the list of saved queries (OUQR), formatted searches (CSHS), alerts, Crystal reports, user procedures and the transaction notification. Note who uses each one. Many will be unused and can be dropped.
2. Convert in batches by type: simple queries, formatted searches, reports, then procedures.
3. Run both versions against a copy of the same data, SQL Server and HANA.
4. Compare row counts and totals. If DocTotal sums or counts differ, find out why before moving on.
5. Test edge cases. NULL values, cancelled documents, foreign currency and documents across a year end.
6. Let key users sign off on the reports they use daily.

For starting points, our SAP B1 query library has common queries written in both T-SQL and HANA syntax, and the SQL to HANA converter speeds up the first pass on your own.

What to expect

Simple saved queries take minutes each. Formatted searches are similar. Reports with commands take longer because of testing. Stored procedures and the transaction notification take the most time and carry the most risk, so plan them first, not last.

If you are planning a move to HANA and want your queries, reports and validations converted and tested, contact Kodenova with a list of what you have.

Sources

• SAP (B1SA): Query Migration Tool for Microsoft SQL Server to SAP HANA (GitHub)
• SAP HANA SQL Reference: IFNULL Function
• SAP HANA SQL Reference: DAYS_BETWEEN Function
• SAP HANA SQL Reference: Datetime Functions
• SAP Community: How to convert an SAP B1 MS SQL Crystal Report to HANA
• SAP Community: SBO HANA query with user parameters

Related services

• SAP Business One development