Useful SQL Query (Part 3) – Remove database log from SQL

There are certain needs when you want to remove the database logs. Below SQL query will help you in that.

USE [master]

GO
ALTER DATABASE [TestDb] SET RECOVERY SIMPLE WITH NO_WAIT

DBCC SHRINKFILE(TestDbLog, 1)

ALTER DATABASE [TestDb] SET RECOVERY FULL WITH NO_WAIT

GO

Useful SQL Query (Part 1) – History of Database Restore

History of Database Restore

Sometime in administration work you would like to see history of database restore, like when we have restored this version of data. Usually helps in development and test system.

SELECT [rs].[destination_database_name],
[rs].[restore_date],
[bs].[backup_start_date],
[bs].[backup_finish_date],
[bs].[database_name] as [source_database_name],
[bmf].[physical_device_name] as [backup_file_used_for_restore]
FROM msdb..restorehistory rs
INNER JOIN msdb..backupset bs
ON [rs].[backup_set_id] = [bs].[backup_set_id]
INNER JOIN msdb..backupmediafamily bmf
ON [bs].[media_set_id] = [bmf].[media_set_id]
ORDER BY [rs].[restore_date] DESC

Screen-Shot-2015-01-20-at-12_03_02-PM

Executing SQL directly from X++

The X++ language supports a number of ways to execute native SQL against any SQL data source. Below are two samples for retrieving data as well as manipulating data. Please be aware, that SQL can be really powerful, and it should be used as such.

Example #1: Retrieve data:

Since this example uses a Connecion class, data is retrieved from the database where Axapta is currently connected.

void Sample_1(void)

{
Connection Con = new Connection();
Statement Stmt = Con.createStatement();
ResultSet R =Stmt.executeQuery(‘SELECT VALUE FROM SQLSYSTEMVARIABLES’);

while ( R.next() )
{
print R.getString(1);
}
}

Example #2: Manipulating data (deletion):

void Sample_2(void)
{
str sql;
Connection conn;
SqlStatementExecutePermission permission;
;

sql = ‘delete from custTable’;
permission = new SqlStatementExecutePermission(sql);
conn = new Connection();
permission = new SqlStatementExecutePermission(sql);
permission.assert();
conn.createStatement().executeUpdate(sql);
// the permissions needs to be reverted back to original condition.
CodeAccessPermission::revertAssert();
}

Happing Daxing J Enjoy the power of SQL using x++

%d bloggers like this: