RSS

Category Archives: SQL

Reading Data through SQL Data Reader in AX 2009


Reading Data through SQL Data Reader in AX 2009

static void CheckDatabaseConThroughDotNet(Args _args)
{
System.Data.SqlClient.SqlConnection con;
System.Data.SqlClient.SqlCommand com;
System.Data.SqlClient.SqlDataAdapter da;
System.Data.SqlClient.SqlDataReader dr;
System.Data.DataSet ds;
str _vendName;
;

try
{
con = new System.Data.SqlClient.SqlConnection(strfmt(“Data Source=Ibrahim; Initial Catalog=DynamicsAx; Integrated Security=true”));
com = new System.Data.SqlClient.SqlCommand(“Select * from VendTable”,con);

con.Open();
dr = com.ExecuteReader();

while(dr.Read())
{
// _vendName = dr(0);

}
con.Close();

}
Catch(Exception::Error)
{
info(“error in Error Class”);
}
Catch(Exception::CLRError)
{
info(CLRInterop::getLastException().ToString());
}
}

Advertisements
 
Leave a comment

Posted by on June 20, 2012 in SQL, X++

 

Tags: ,

SQL – Scheduled Date wise database backup


Newer posts →SQL – Scheduled Date wise database backup
Posted on January 15, 2010 by shekhardiptiman
The scenario here is that there should be backup folders for each day of the week i.e. Monday,Tuesday,…..SUNDAY ( 7 in total). The SQL backup at disc should be performed daily and it should get copied in the respective day’s folder. The folders should retain the backup for one week. The backup performed on each day should overwrite the last week’s backup for that day. This way there will be at the most 7 backups available on the disc.

Following are the steps to achieve the above :

1. Create a Stored Procedure in SQL server with the following code.

The code is given below for reference

==================================================================

USE [master]
GO
/****** Object: StoredProcedure [dbo].[sp_BackupDatabase] Script Date: 01/15/2010 00:25:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

– =============================================
– Author:
– Create date:
– Description: Backup Database
– Parameter1: databaseName
– Parameter2: backupType F=full, D=differential, L=log
– =============================================
ALTER PROCEDURE [dbo].[sp_BackupDatabase]
@databaseName sysname, @backupType CHAR(1)
AS
BEGIN
SET NOCOUNT ON;

DECLARE @sqlCommand NVARCHAR(1000)
DECLARE @dateTime NVARCHAR(20)

SELECT @dateTime = REPLACE(CONVERT(VARCHAR, GETDATE(),111),’/’,”) +
REPLACE(CONVERT(VARCHAR, GETDATE(),108),’:’,”)

IF @backupType = ‘F’
SET @sqlCommand = ‘BACKUP DATABASE ‘ + @databaseName +
‘ TO DISK = ”F:\SQLBackup\’ + @databaseName + ‘_Full_’ + @dateTime + ‘.BAK”’
IF @backupType = ‘D’
SET @sqlCommand = ‘BACKUP DATABASE ‘ + @databaseName +
‘ TO DISK = ”F:\SQlBackup\’ + @databaseName + ‘_Diff_’ + @dateTime + ‘.BAK” WITH DIFFERENTIAL’
IF @backupType = ‘L’
SET @sqlCommand = ‘BACKUP LOG ‘ + @databaseName +
‘ TO DISK = ”F:\SQlBackup\’ + @databaseName + ‘_Log_’ + @dateTime + ‘.TRN”’
EXECUTE sp_executesql @sqlCommand
END

========================================================

2. Create a file to provide the parameter values regarding the name of the database and type of the backup to be performed to the above procedure as shown below: Save it as .sql file e.g. backup.sql

3. Create a scheduled task in windows to process the above in the following screenshot ######-SVR is the server name on which SQL is installed.

■Configure Run – ‘sqlcmd -S XXXXX-SVR -E -i F:\DYNAMICSAX\Backup.sql’ . ( highlighted is the path to the backup.sql file)
■Configure the “Start in” as shown .
■Configure “Run as” as a valid user in SQL database who has admin rights for the server and the database to be backed up. Save teh user name and password for that user id.
■Set the schedule for the above task to ‘Daily’

The above would create an SQL backup daily at 8.05 pm and save at “F:\Sqlbackup” folder as setup in the stored procedure.

4. Inside F:\SQLBACKUP folder there would 7 subfolders ( one for each weekday excluded). Now the rest of the job is very simple . Create a batch file which will delete the files from the weekday folders and move the backup file from the root of the F:\SQLBackup to the subfolder of the day.

We created 7 batch files (one for each day.). In the following screenshot there are 5 batch files show. ( excluded saturday and sunday) . For example if A.bat is processed then it will delete the files in the folder named “Monday” and move the file from the root of the folder F:\SQLBACKUP as explained above into “Monday”

5. Create windows scheduled tasks which would access these batch files once in a week i.e A.bat would be processed on Monday, B.bat on Tuesday …

Configure the schedule for each task as explained and shown in the screenshot.

Important : The schedule should set such that it processes the batch files after the SQL backup has been finished and the backup file is saved .

Now the SQL backup would be performed daily with a date stamp in its name and saved in a folder for each weekday.

**************You have achieved your objective*****************

The above is an illustration for SQL 2005. I hope it would work for SQL 2008 also.

 
Leave a comment

Posted by on February 20, 2012 in SQL

 

Tags:

Connecting to Databases through X++


Connecting to Databases through X++

In this article, different ways through which one can connect to different databases for data manipulation operations.
In AX, the methods that I know are following. Please feel free to add more methods that you feel are also available.
  • ODBC Connection
  • ADO Connection
  • OleDB Connection
  • Connection class

Let us go through each of these options one by one.

ODBC Connection:

ODBC stands for Open Data Base Connectivity. It is a connection that is created to define a connection between a computer and a database stored on another system. The ODBC connection contains information needed to allow a computer user to access the information stored in a database that is not local to that computer. In Dynamics AX, we have ODBCConnection class to carry out this type of database connection need. This class uses LoginProperty class for login information and uses Statement and ResultSet classes for carrying out DML operations. Below is an example of how to use this class.

static void dbODBCConnection(Args _args)
{
LoginProperty   loginProp;
ODBCConnection  conn;
Resultset       resultSet, resultSetCount;
Statement       statement1, statement2;
;

    loginProp = new LoginProperty();

    loginProp.setServer(‘Arun’);
loginProp.setDatabase(‘AX2009′);

    conn = new ODBCConnection(loginProp);

    statement1  = conn.createStatement();
resultSet   = statement1.executeQuery(“SELECT * from CustTable where DATAAREAID = ‘CEU’”);

    while (resultSet.next())
{
info(resultSet.getString(1));
}
}

The above sample code is a job. Note that you may have to use respective permission classes like SQLStatementExecutePermission etc. while using in classes and any other place. Note that if you need to invoke a stored procedure then just type exec in the executeQuery instead of the select statement.

ADO Connection:

ADO is an abbreviation for ActiveX Data Objects. ADO is a set of COM objects for accessing databases or data stores. In AX we have following objects making a collection for implementing ADO concept.

  • CCADOConnection – Helps in establishing a connection to the target database.
  • CCADOCommand – Helps in executing a command (a Text type or a Stored procedure)
  • CCADORecordSet – Stores the data
  • CCADOFields – A collection of all fields in CCADORecordSet
  • CCADOField – A single field from the collection of fields
  • CCADOParameter – A class that helps in passing parameters that a command needs or demands

The example below demonstrates the working of some of these classes:

static void dbCCADOConnection(Args _args)
{
CCADOConnection connection = new CCADOConnection();
CCADOCommand    ccADOCommand;
CCADORecordSet  record;
str connectStr = “Provider=SQLNCLI.1;Integrated Security=SSPI;”+
“Persist Security Info=False;Initial Catalog=AX2009;Data Source=Arun”;

    COM     recordSet;  /*This is required to call moveNext method to parse the record set. In AX 4.0 this method was there in the CCADORecordSet class but in AX 2009 this has been deleted*/
    ;

    // Executing a SQL Statement
try
{
connection.open(connectStr);
ccADOCommand = new CCADOCommand();
ccADOCommand.commandText(“Select * from CustTable where DataAreaId = ‘CEU’”);
ccADOCommand.activeConnection(connection);
record = ccADOCommand.execute();
recordSet = record.recordSet();
while (!record.EOF())
{
info(any2str(record.fields().itemIdx(0).value()));
recordSet.moveNext();
}
}
catch
{
error(“An Exception has occurred”);
}

    connection.close();
}

The above sample code is a job. Note that you may have to use respective permission classes like SQLStatementExecutePermission etc. while using in classes and any other place.

OLEDB Connection:

OLEDB stands for Object Linking and Embedding, DataBase. It is a set of APIs designed by Microsoft and used for accessing different types of data stored in a uniform manner. Dynamics AX as such doesn’t have any specific classes built for this purpose. But one can make use of .Net Framework’s System.Data.OleDb namespace through AX’s COM Interoperability feature and use it in AX.

Below is an example code that depicts this scenario:

static void dbOLEDBConnection(Args _args)
{
System.Exception                    e;
System.Data.OleDb.OleDbConnection   objConn;
System.Data.OleDb.OleDbCommand      cmdSelect;
System.Data.OleDb.OleDbDataReader   reader;
InteropPermission                   perm;
str connectStr = “Provider=SQLNCLI.1;Integrated Security=SSPI;”+
“Persist Security Info=False;Initial Catalog=AX2009;Data Source=Arun”;
str exceptionStr;
;

    try
{
perm = new InteropPermission(InteropKind::ClrInterop);
if (perm == null)
{
throw error(“Error with file permissions”);
}
perm.assert();

        objConn = new System.Data.OleDb.OleDbConnection(connectStr);
objConn.Open();

        cmdSelect   = objConn.CreateCommand();
cmdSelect.set_CommandText(“SELECT * FROM CustTable where DATAAREAID = ‘CEU’”);
reader      = cmdSelect.ExecuteReader();

        while (reader.Read())
{
info(reader.GetString(0));
}
}
catch(Exception::CLRError)
{
CodeAccessPermission::revertAssert();

        perm = new InteropPermission(InteropKind::ClrInterop);
if (perm == null)
{
return;
}
perm.assert();

        e = ClrInterop::getLastException();

        CodeAccessPermission::revertAssert();

        while( e )
{
exceptionStr += e.get_Message();
e = e.get_InnerException();
}
info(exceptionStr);
}
catch
{
error(“An Exception has occurred”);
}

    if(objConn)
objConn.Close();
}

Connection Class:

Connection class is mainly used for accessing the database in which a user has logged into AX i.e. Current Database and carry out the operations. This class is exetensively used in ReleaseUpdateDB classes, the classes used in data upgrades. This class cannot be run on client and should always be run on server. One more unique thing that I noticed is that the statements that you want to execute should be asserted first for permissions and then passed on to other method where they are executed. Create a class with following methods and set its RunOn property to Server.

class TestSQLExecuteClass
{
}

//This method tests the permissions for statement and then calls the method that will execute the statement
static void dbConnectionClass()
{
ResultSet   rs;
SqlStatementExecutePermission perm;
;

    perm = new SQLStatementExecutePermission(“select * from CustTable where DATAAREAID = ‘CEU’”);
perm.assert();

    rs = TestSQLExecuteClass::statementExeQuery(“select * from CustTable where DATAAREAID = ‘CEU’”);

    while (rs.next())
{
info(rs.getString(1));
}
CodeAccessPermission::revertAssert();
}

//Executes the passed statement
private static ResultSet statementExeQuery(str _sql, Connection _con = null)
{
ResultSet   resultSet;
Statement   statement;
;

    try
{
if(!_con)
{
_con = new Connection();
}

        statement = _con.createStatement();

        // Do not call assert() here, do it in the caller
// BP deviation documented

resultSet = statement.executeQuery(_sql);
}
catch (Exception::Error)
{
throw error(“@SYS99562”);
}

    return resultSet;
}

Now you can call the method in a job as shown below:

static void dbConnectionClass(Args _args)
{
;

    TestSQLExecuteClass::dbConnectionClass();
}

 
Leave a comment

Posted by on December 13, 2011 in SQL, X++

 

Tags: ,

 
All About Dynamics 365

Dynamics 365, D365, Implementor

Syed Rafay Ali

This blog contains information about Functional techniques and guidelines in Microsoft Dynamics AX, including tips, tricks, tutorials, tools and upcoming news enhancement in Microsoft Dynamics Ax

Philippsen's Blog

Everyday findings in my world of .net and related stuff

Microsoft Dynamics AX

A great WordPress.com site

Finite Minds

Adventures in IoT

Dynamics Ax

Technical Knowledge

timsaxblog

A blog about implementing Microsoft Dynamics AX and Dynamics 365 for Operations

Microsoft Dynamics 365 Blog

Strat your organization's digital translation with Dynamics 365, A Blog by Sandeep Chaudhury

DEVSerra - Dynamics AX development blog

Your official Microsoft Dynamics AX blog.

OrganicAX

Discovering Dynamics

AX

A blog by Hai Nguyen

Learn Dynamics Ax with Johnkrish

Live as if you were to die tomorrow. Learn as if you were to live forever - Mahatma Gandhi ****** The more I learn, the less I know - Albert Einstein

Twisted Untwirled

Just another WordPress.com site

ramdynamicsax

Just another WordPress.com site

guyterry's Dynamics AX blog

Just another Dynamics AX blog