The following code can be used to create a sql view in a given, external database.
// local variables ADOConnection Automation 'Microsoft ActiveX Data Objects 2.8 Library'.Connection ADOCommand Automation 'Microsoft ActiveX Data Objects 2.8 Library'.Command ADORecSet Automation 'Microsoft ActiveX Data Objects 2.8 Library'.Recordset connString Text 1024 activeConnection Variant viewName Text 100 // the code // replace the server ip and the ext. databasename with your values connString := 'Driver={SQL Server};Server=127.0.0.1;Database=Cronus600'; CREATE(ADOConnection); ADOConnection.ConnectionString(connString); ADOConnection.Open; // convert ADO Connection to Variant activeConnection := ADOConnection; viewName := 'CRONUS$Item View'; CREATE(ADOCommand); ADOCommand.ActiveConnection := activeConnection; ADOCommand.CommandType := 1; ADOCommand.CommandText := 'SELECT * FROM INFORMATION_SCHEMA.TABLES where ' + '(TABLE_TYPE = ''VIEW'') and (TABLE_NAME =''' + viewName + ''')'; ADORecSet := ADOCommand.Execute; IF ADORecSet.EOF = TRUE THEN BEGIN ADOCommand.CommandText := 'CREATE VIEW [' + viewName + ']' + 'AS SELECT No_, Description FROM dbo.[CRONUS$Item]' + 'WHERE No_ like ''100%'''; ADOCommand.Execute; message('view created'); END ELSE MESSAGE('view already exists'); ADORecSet.Close; ADOConnection.Close; CLEARALL;
follow also posting “Sql Statements in C/AL – Part 1 : Select”.
cheers