Axapta มีคำสั่ง SQL รวมอยู่ใน X++ อยู่แล้ว
แต่ถ้าอยากใช้คำสั่ง SQL อื่น ๆ เช่น ใช้ประโยชน์จาก NewID() ของ MS SQL Server เพื่อ random ก็ทำได้ ดังตัวอย่าง
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
| static void Job3(Args _args)
{
LogInProperty Lp = new LogInProperty();
OdbcConnection myConnection;
Statement myStatement;
ResultSet myResult;
str sqlQuery;
;
sqlQuery = 'SELECT TOP 10 * FROM CustTable ORDER BY NewID()';
LP.setServer("Server");
LP.setDatabase("db");
Lp.setUsername("user");
Lp.setPassword("password");
try{
myConnection = new OdbcConnection(LP);
}
catch{
info("Check username/password.");
return;
}
myStatement = myConnection.createStatement();
myResult = myStatement.executeQuery(sqlQuery);
while (myResult.next()){
box::info(myResult.getString(1));
}
} |
Written by Komkid on October 20th, 2010 with no comments.
Read more articles on Axapta and Database and Programming.
Example 1 (SQL Server table)
Run the following SQL statement from Teratrax Database Manager, Query Analyzer, or SQL Server Management Studio. Replace the names in bold with your own:
USE db1
GO
EXEC sp_spaceused N'dbo.orders'
GO
Results
* name: Table name for which space usage information was requested
* rows: Number of rows existing in the table
* reserved: Total amount of reserved space for table data and indexes
* data: Amount of space used by table data
* index_size: Amount of space used by table indexes
* unused: Total amount of space reserved for table but no yet used
Example 2 (SQL Server database)
You can also run sp_spaceused without any parameters to display information about the whole database. Replace the names in bold with your own:
USE db1
GO
EXEC sp_spaceused
GO
Results
First Recordset:
* database_name: Name of the current database
* database_size: Size of the current database in megabytes. database_size includes both data and log files
* unallocated space: Space in the database that has not been reserved for database objects
Second Recordset:
* reserved: Total amount of space allocated by objects in the database
* data: Total amount of space used by data
* index_size: Total amount of space used by indexes
* unused: Total amount of space reserved for objects in the database, but not yet used
From : http://www.teratrax.com/articles/table_size_sp_spaceused.html
Written by Komkid on September 1st, 2009 with no comments.
Read more articles on Admin and Database.
TRUNCATE TABLE name
vs
DELETE FROM TABLE name
TRUNCATE TABLE is functionally identical to DELETE statement with no WHERE clause: both remove all rows in the table. But TRUNCATE TABLE is faster and uses fewer system and transaction log resources than DELETE.
The DELETE statement removes rows one at a time and records an entry in the transaction log for each deleted row. TRUNCATE TABLE removes the data by deallocating the data pages used to store the table’s data, and only the page deallocations are recorded in the transaction log.
TRUNCATE TABLE removes all rows from a table, but the table structure and its columns, constraints, indexes and so on remain. The counter used by an identity for new rows is reset to the seed for the column. If you want to retain the identity counter, use DELETE instead. If you want to remove table definition and its data, use the DROP TABLE statement.
You cannot use TRUNCATE TABLE on a table referenced by a FOREIGN KEY constraint; instead, use DELETE statement without a WHERE clause. Because TRUNCATE TABLE is not logged, it cannot activate a trigger.
TRUNCATE TABLE may not be used on tables participating in an indexed view.
Written by Komkid on August 18th, 2009 with no comments.
Read more articles on Database.