How to run SQL code in the same transaction as the Nav

rmv_RU

Sometimes we have to use SQL in Nav for different actions including data modification. For example, we have to call a stored procedure to update a web-order status after release a sales order. Unfortunately, there are no way to run the stored procedure in a same transaction as a sales order released by using ADO. Different transactions may cause data integrity problems.

The solution is - Write a SQL statetment into special table from NAV and use a sql trigger on the table to run the statement
1. Create a view in SQL and then link the view in Nav

create view
[dbo].[sys_ConnectionProperties]
as 
select cast(serverproperty('servername') as varchar(100)) as server_name, DB_NAME() as db_name, @@SPID as spid
	, system_USER as [system_user]

2. Create a table sql_statement in Nav

spid int
sql text(250)

3. Write a SQL trigger

alter trigger run_sql on sql_statement for insert, update
as begin
	if 	not app_name() IN ('Microsoft Business Solutions-Navision client', 'Microsoft Dynamics NAV client', 'Microsoft Business Solutions-Navision', 'Microsoft Dynamics NAV','C/ODBC','C/FRONT','Navision Application Server', 'Microsoft Dynamics NAV Application Server','Navision VIP Application Server')
		raiserror ('forbidden', 16, 1)

	declare @spid int
	declare @sql varchar(250)
	select @sql=[sql] from inserted where spid=@@SPID
	
	--set @sql='raiserror (''zuzu'', 16, 1)'
	exec (@sql)
	

end

4. Write a test codeunit

SysProperties.FIND('-');
SQL.spid:=SysProperties.spid;
//make sure that sql update works
SQL.sql:='update [your_company_name$item] set description=''changes made by sql'' where No_=''your_item_no''';
IF NOT SQL.INSERT THEN
  SQL.MODIFY;
commit;
//make sure that sql update rollback when Nav error occurs
SQL.sql:='update [your_company_name$item] set description=''changes made by sql'' where No_=''your_item_no2''';
IF NOT SQL.INSERT THEN
  SQL.MODIFY;
ERROR('error');

The main disadvantage of this solution - dynamic sql may cause security problems.