Skip to Content
Author's profile photo Former Member

How to Execute Custom Code via SP_TransactionNotification

if you read theThe SP_TransactionNotification Stored Procedure Stored Procedure  you understood the advantage of procedure: “you can do some validation in SAP Business One and prevent the user from doing an action if the validation condition fails.”

Probably Everybody can have the question:
Is It possible to execute custom code via SP_TransactionNotification procedure?

Basically the answer is Yes.

Let’s investigate the following scenario: Company sales process requires to send delivery notification email to specific customers at time when delivery note has been sucessfully issued.

We can solve this problem via DI API Messages Object using .NET code. There is more elegant another way: Sending email without Addons using the same .NET code from SQL Server.


Write a .NET Class Based DLL which send an email to an address given by parameters. The DLL is using DI API to send an email over SBO mailer service.

I have used C# and Mesasges Object of DI API to send an email.


Sending message via DI API using Messages Object – without GUI

Create a New Windows Class Library Project  in  C#, give name SimpleDeliveryNotification and include the SAP Business One DI API 2007 as a reference.

Import namespaces required to MS SQL 2005 CLR code

Write a Class which can connect to SAP B1 company and send message.

Important notes:
– SAP B1 UserName and Password should provided for access to Company (single sign on is not working here)
– UseTrusted propety can be used (we are inside SQL – the code is running on sql server directly)
– For future flexible usage: Queries can be used to determine Company Name  and Server name for connection parameters of DI API Company Object.

Compile the Class, and Copy the SimpleDeliveryNotification.dll into anywhere on the MS SQL 2005 server.

MS SQL 2005 Settings

Enable CLR on MS SQL Server, connect by Management Studio, and open a new Query. There enter the following commands.

Register the DLL for MS SQL server by CREATE ASSEMBLY command from your copied (compiled path)

Create a Stored Procedure for Executing your managed code (DLL). Procedure name: SP_Send_Delivery_Notification and this will be called when sending the email message.

You can test the Stored procedure by Calling it from MS SQL Query Analizer or from an SAP B1 Query.

The SP_TransactionNotification code

When a delivery note has been issued, the SP_TransactionNotification is executed. In the following example the numatcard field holds the Customer Order Number. E-Mail field from Customers master data can used for populating the email address where the Simple Delivery Notification should be sent.


The CLR comined with SP_TransactionNotification stored procedure is a flexible, fast and elegant possiblity to handle background processes like sending notifications, creating automatic post operations using DI API .

Sample code can be downloaded from here

Background: CLR

The Common Language Runtime (CLR) is a core component of Microsoft’s .NET initiative. It is Microsoft’s implementation of the Common Language Infrastructure (CLI) standard, which defines an execution environment for program code. Developers using the CLR write code in a language such as C# or VB.NET. At compile time, a .NET compiler converts such code into CIL code. At runtime, the CLR’s just-in-time compiler converts the CIL code into code native to the operating system.
Alternatively, the CIL code can be compiled to native code in a separate step prior to runtime. This speeds up all later runs of the software as the CIL-to-native
compilation is no longer necessary.”

definition source: Wikipedia 

Assigned tags

      You must be Logged on to comment or reply to a post.
      Author's profile photo Former Member
      Former Member

      Nice approach. Just a few comments:

      1. All methods for the SP’s are created as static methods, as SQLServer will not instantiate your class.

      2. Return type must always be Void, as all the SP result must be channeled via the SqlContext.Pipe class [Microsoft.SqlServer.Server.SqlProcedure]; and finally

      3. The CREATE ASSEMBLY  command loads the assembly binary file into the database – this is important: the assembly is not linked/referenced from your hard-disk or GAC, its actually loaded into the MDB file, so every time you compile a new version you have to reload it using the ALTER ASSEMBLY command.

      Agian, nice approach.

      Author's profile photo Former Member
      Former Member

      I try your code
      When I run around

      CREATE ASSEMBLY SimpleDeliveryNotification AUTHORIZATION dbo FROM 'C: \ Program Files \ Microsoft SQL Server \ SimpleDeliveryNotification.dll'

      CREATE PROC SP_Send_Delivery_Notification
      @ EmailAddress nvarchar (100)
      @ OrderNumber nvarchar (100)
      @ msg nvarchar (MAX) output
      AS EXTERNAL NAME SimpleDeliveryNotification. [SimpleDeliveryNotification.Class1]. SendNotification

      I have this error

      Msg 10327, Level 14, State 1, Line 1
      Failed to CREATE ASSEMBLY for assembly 'SimpleDeliveryNotification' because assembly 'SimpleDeliveryNotification' is not allowed to PERMISSION_SET = UNSAFE. The assembly is authorized when the following conditions is true: the owner of the database (DBO) has UNSAFE ASSEMBLY permission and the database property database TRUSTWORTHY activated, the assembly is signed with a certificate or asymmetric key that has a corresponding login with UNSAFE ASSEMBLY permission. If you have restored or attached this database, check that the owner of the database is mapped to the correct connection on this server. Otherwise, use sp_changedbowner to solve the problem.
      Msg 6528, Level 16, State 1, Procedure SP_Send_Delivery_Notification, Line 2
      The assembly 'SimpleDeliveryNotification is not found in the SQL catalog of database' master '.

      you have an idea thank you

      Author's profile photo Former Member
      Former Member
      I've implemented the code and it worked the first two times I called the sp directly.

      After implementing the code in sp_notification is now returns this error:

      Login  eror -112 Failed to Extract OBServer.dll from CAB File

      I get this same error now when calling the sp directly as well ..

      I've tried deleting it from c:\temp\sms_obs_dll but to no avail? I can only assume its a user rights issue of some sort?

      Author's profile photo Alvaro Gonzalez
      Alvaro Gonzalez
      Hi Jaco Bayer, i tried the example and got the same error, can you resolve this issue?