In newer Nav versions the tables overview in development environment/database information has gone. So how to get that list back ?
Let’s got to SSMS (Sql Server Management Studio).
There select the database, for which you want that information.
Right click on database -> Reports -> Standard Reports -> Disk usage by Top Tables
There we are ! 😉
If you get following error:
then the database is corrupted. In detail it means sql view “Session” is missing in the database! This can be a result of a crashed backup/restore process or a failed conversion process.
It can be, that View “Database File” is also missing. Although it is possible to restore the missing view e.g. from a (not changed) default cronus database, it won’t help, but you can give it a try. You can restore a new cronus database from the nav 2009 setup, subfolder SQLDemoDatabase\CommonAppData\Microsoft\Microsoft Dynamics NAV\60\Database.
In most cases it’s needed to restore the database from an earlier backup. If you do not have a backup strategy for your databases – but you should have – then export all changed nav objects as text files from the current, crashed database and start with a new database on base of a cronus database. then import/migrate the changed nav objects. after that re-compile all nav objects and restart the nav service.
Helpful Links:
http://saurav-nav.blogspot.co.at/2012/02/metadata-for-object-of-type-table-with.html
cheers
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
There is no possibility to read data from an external database with standard c/al. Best way to do that till NAV version 2009 is using ADO. With Nav vs. 2013 upwards you can use .Net classes.
The following code can be used to read data from 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
ADOParameter Automation 'Microsoft ActiveX Data Objects 2.8 Library'.Parameter
ADORecSet Automation 'Microsoft ActiveX Data Objects 2.8 Library'.Recordset
adoField Automation 'Microsoft ActiveX Data Objects 2.8 Library'.Field
connString Text 1024
activeConnection Variant
salesLineStr Text 1024
idx Integer
adoValue Variant
adoType Integer
// the code
// replace the server ip and the ext. databasename with your values
ReadFromExternalDatabase()
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;
CREATE(ADOCommand);
ADOCommand.ActiveConnection := activeConnection;
ADOCommand.CommandType := 1; // type is sql statement
// sample statement: read first record from table sales line
ADOCommand.CommandText := 'select top 1 * from [CRONUS AG$Sales Line]';
ADOCommand.CommandTimeout := 100;
CREATE(ADORecSet);
ADORecSet := ADOCommand.Execute;
ADORecSet.MoveFirst; // goto first record
FOR idx := 1 TO ADORecSet.Fields.Count - 1 DO BEGIN
adoField := ADORecSet.Fields.Item(idx);
adoType := adoField.Type;
IF adoType 131 THEN // ado type numeric must be converted
adoValue := adoField.Value
ELSE
adoValue := GetFieldValue(adoField);
IF (adoField.Type 13) THEN // ado type 13 = Unknown
// gets the value with index idx of the resulting record
salesLineStr := salesLineStr + FORMAT(adoValue);
salesLineStr := salesLineStr + ';';
END;
MESSAGE(salesLineStr);
ADORecSet.Close;
ADOConnection.Close;
CLEARALL;
GetFieldValue(adoField : 'Microsoft ActiveX Data Objects 2.8 Library'.Field) : Text
// local variables
// ADOStream Automation 'Microsoft ActiveX Data Objects 2.8 Library'.Stream
// RetVal Text
CREATE(ADOStream);
ADOStream.Open;
ADOStream.WriteText(adoField.Value);
ADOStream.Position:= 0;
RetVal:= ADOStream.ReadText;
ADOStream.Close;
exit(RetVal);
helpful links:
http://www.w3schools.com/asp/ado_ref_connection.asp
http://www.w3schools.com/asp/ado_ref_command.asp
http://www.w3schools.com/asp/prop_comm_commandtype.asp#commandtypeenum
DFK's Tech Blog 