mibuso.com • View topic - How to run SQL code in the same transaction as the Nav Página 1 de 2 mibuso.com Microsoft Business Solutions online community Login Register RSS Feed Rules FAQ Search the forum Treemap It is currently Mon Dec 02, 2013 7:05 pm View unanswered posts | View active topics Board index » Microsoft Dynamics NAV (formerly Microsoft Business Solutions-Navision) » NAV Tips & Tricks All times are UTC [ DST ] How to run SQL code in the same transaction as the Nav Page 1 of 1 [ 1 post ] Previous topic | Next topic Author Message rmv_RU Post subject: How to run SQL code in the same transaction as the Nav Posted: Thu Nov 21, 2013 8:41 am Joined: Fri Jun 04, 2010 1:38 pm Posts: 77 Location: Moscow Country: 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 Code: Select all 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 Code: Select all spid int sql text(250) 3. Write a SQL trigger Code: Select all alter trigger run_sql on sql_statement for insert, update as begin if not app_name() IN ('Microsoft Business SolutionsNavision 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') http://www.mibuso.com/forum/viewtopic.php?f=5&t=59746&hilit=ADO 02/12/2013 mibuso.com • View topic - How to run SQL code in the same transaction as the Nav Página 2 de 2 4. Write a test codeunit Code: Select all 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   The main disadvantage of this solution - dynamic sql may cause security problems. Top Display posts from previous: All posts  Sort by Post time  Ascending  Go Page 1 of 1 [ 1 post ] Board index » Microsoft Dynamics NAV (formerly Microsoft Business Solutions-Navision) » NAV Tips & Tricks All times are UTC [ DST ] Who is online Users browsing this forum: No registered users and 1 guest You cannot post new topics You cannot reply to topics You cannot edit your posts You cannot delete your posts You cannot post attachments Search for: Go Jump to: NAV Tips & Tricks http://www.mibuso.com/forum/viewtopic.php?f=5&t=59746&hilit=ADO in in in in in this this this this this forum forum forum forum forum  Go 02/12/2013