I have a SQL script (.sql file) residing on my local drive.The script contains T-SQL commands to create views in Navision database which are defined from respective tables of a third party database (say, WMDATA is the name of that database).
Eg:- “WMERP Views.sql” file contains the following
USE [NAVDATA]
CREATE VIEW [dbo].[CRONUS International Ltd_$WMCustomer]
AS SELECT * FROM WMDATA.dbo.WMCustomer
I want to execute the above sql script stored in “WMERP Views.sql” file by writing C/AL code such that when the code executes it automatically creates WMCustomer view in NAVDATA db.
Please let me know if this is possible and how it can be achieved.
The most easy way to execute a “*.sql” file from NAV is usung the C/AL SHELL command (check “C/SIDE Reference Guide” for deatils") executing the sqlcmd.exe (2005) or osql.exe (2000) feature (check “Books Online” for details).
But have in mind that you also could fire TSQL from NAV using MS ADO as “Automation Server” …
Infact, i came to know about sqlcmd utility just after i wrote this query and started searching through google. I have now decided to do the following-
I will use a batch file which contains the sqlcmd command to run the sql script. Since creation of the views in NAV db. is a one time setup, i will run the batch file to execute the sqlcmd command and get the required views created.