Update external database with X++

Finance and Operations/AX Developer Forum
1

Hi everyone!!!

I’m trying to update an external database through code but I’m getting this error:

“Exception has been thrown by the target of an invocation. Invalid authorization specification.”

I have a code to get all the information I need from the external database. I put all the information in a table called parnetInterface.

str exceptionStr;
str connectStr = “Provider=sqloledb;Data Source=”+parnetInterface.IpServer+";Network Library=DBMSSOCN;Initial Catalog="+parnetInterface.DataBase+";User ID="+parnetInterface.User+";Password="+parnetInterface.Pswd;
try
{
perm = new InteropPermission(InteropKind::ClrInterop);
if (perm == null)
throw error(“Need permission”);

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

cmdSelect = objConn.CreateCommand();
cmdSelect.set_CommandText(sqlGlobalStatement);
reader = cmdSelect.ExecuteReader();
while(reader.Read())
{}
catch(Exception::CLRError)
{
CodeAccessPermission::revertAssert();
perm = new InteropPermission(InteropKind::ClrInterop);
if (perm == null)
{
throw error (“Error”);
}
perm.assert();
e = ClrInterop::getLastException();
CodeAccessPermission::revertAssert();
while( e )
{
exceptionStr += e.get_Message();
e = e.get_InnerException();
}
info(exceptionStr);
}
catch
{
error(“Error”);
}
if(objConn)
objConn.Close();

Everything here goes ok, I have no problems here. But when I try to do the same to update my database, I got the error I mentioned. Here is my code:

str connectStr = “Provider=sqloledb;Data Source=”+parnetInterface.IpServer+";Network Library=DBMSSOCN;Initial Catalog="+parnetInterface.DataBase+";User ID="+parnetInterface.User+";Password="+parnetInterface.Pswd;
str exceptionStr;
;
try
{
perm = new InteropPermission(InteropKind::ClrInterop);
if(perm == null)
throw error(“Need permission”);

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

objConn.Open();

//When I try to go to the next line (the while select) it goes directly to the catch

while select tmpLedgerJournalTrans
{ /* do the update here */ }
objConn.Close();
}

catch(Exception::CLRError)
{
CodeAccessPermission::revertAssert();
perm = new InteropPermission(InteropKind::ClrInterop);

if (perm == null)
{
throw error (“Error”);
}

perm.assert();
e = ClrInterop::getLastException();
CodeAccessPermission::revertAssert();

while( e )
{
exceptionStr += e.get_Message();
e = e.get_InnerException();
}

info(exceptionStr);
}
catch
{
error(“Error”);
}

I don’t know if I’m doing something wrong. I have all the permissions in the database, I can update/create/delete/insert whatever I want in the sql table.

I really appreciate if someone can help me!!! =)

2

If your connection definition is exactly the same (which would be much clearer if you didn’t duplicate the code) and the connection can be open once and it fails another time, then the problem doesn’t seem to be in code.

It might be in the state of the object (e.g. limit of connections) or some context (e.g. you run it once from client and once from AOS).

Also don’t satisfy yourself with the error message - explore all properties of the exception, such as type and error code. This is much easier from Visual Studio, so you might want to tune your code there and then translate it to X++, or simply to create a library and add it to AOT. And if you choose the latter way, you’ll realize that you can also use better frameworks, such as LINQ to SQL.

3

I put the method in a different class and put the property RunOn: Server.

I still face the problem, here is my new code:

void writeKeysToParnet()//LedgerJournalId _journalNum)
{
connectStr = “Provider=sqloledb;Data Source=”+parnetInterface.IpServidor+";Network Library=DBMSSOCN;Initial Catalog="+parnetInterface.BaseDeDatos+";User ID="+parnetInterface.Usuario+";Password="+parnetInterface.Contrasena;

try
{
perm = new InteropPermission(InteropKind::ClrInterop);
if (perm == null)
throw error(“No tiene permisos para acceder a la base de datos”);

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

cmdSelect = objConn.CreateCommand();
cmdSelect.set_Connection(objConn);
objConn.Open();
cmdSelect.set_CommandText(strFmt(“UPDATE ParnetAX SET JournalNum = ‘%1’, Voucher = ‘%2’ WHERE ParnetAX.OperacionDoc = ‘%3’ AND ParnetAX.DocumentoDoc = ‘%4’ AND ParnetAx.FolioParnet = ‘%5’ AND ParnetAx.AsientoParnet = ‘%6’”,
tmpLedgerJournalTrans.JournalNum, tmpLedgerJournalTrans.Voucher,
tmpLedgerJournalTrans.OperacionDoc, tmpLedgerJournalTrans.DocumentoDoc,
tmpLedgerJournalTrans.FolioParnet, tmpLedgerJournalTrans.AsientoParnet));
reader = cmdSelect.ExecuteNonQuery();

// Closing the connection
objConn.Close();
if(transUpdated==0)
throw error(‘Se han actualizado CERO registros en PARNET!.’);

}

catch(Exception::CLRError)
{
CodeAccessPermission::revertAssert();
perm = new InteropPermission(InteropKind::ClrInterop);

if (perm == null)
{
throw error (“Error en permisos”);
}

perm.assert();
e = ClrInterop::getLastException();
CodeAccessPermission::revertAssert();

while( e )
{
exceptionStr += e.get_Message();
e = e.get_InnerException();
}

info(exceptionStr);
}
catch
{
error(“Ha ocurrido una excepción”);
}
}