How can i read data from Sql Server Axapta Database tables from X++ without ODBC connection?I don't want to create the ODBC in all the clients , so I want to access the Sql Server database from X++ without any other dependencies.
Please can anyone help me on this ?
//N by Arun Garg for Connecting SQL SERVER static void DatabaseConnection(Args _args) { #CCADO CCADOConnection connection; CCADOCommand command; CCADORecordset recordset ; CCADOFields fields; str Database; str connectionStr; str statement; str CurrentCompanyCode; sqlSystem sq = new sqlSystem(); CustTable custTable; ;
CurrentCompanyCode=curext(); connectionStr =" Driver={SQL Native Client};Trusted_Connection=no;SERVER= {" + sq.loginServer() + "};DATABASE={master};User Id='';Password=''; statement = "select AccountNum,Name"; statement+=" from CustomerTable where AxaptaProcessDateTime = 0"; info(strfmt("%1",connectionStr)); info(strfmt("%1",statement)); connection = new CCADOConnection(); connection.open(connectionStr,#adConnectUnspecified); command = new CCADOCommand(); command.activeConnection(connection); command.commandText(statement); try { recordset = command.execute(); while(!recordset.EOF()) { fields = recordset.fields(); CustTable.AccountNum = fields.itemIdx(0).value(); //entry in PartyTable CustTable.Name = fields.itemIdx(1).value(); CustTable.insert(); recordset.recordSet().movenext(); } //Update Table statement = "update CustomerTable set AxaptaProcessDateTime = getdate() where AxaptaProcessDateTime = 0 "; command.commandText(statement); command.execute(); info("Records Inserted"); connection.close(); } catch { connection.close(); throw error ( "Error "); } }
See How to connect SQL SERVER with Dynamics Ax 2009
you got ur answer
Hi,
You may use Connection class. By default this will establish a connection to the current database session. Code snippet from Developers guide below. Also search in this forum as this subject was already discussed.
server static void main(Args args) { Connection con = new Connection(); Statement stmt = con.createStatement(); ResultSet r; str sql; SqlStatementExecutePermission perm; ; sql = strfmt('SELECT VALUE FROM SQLSYSTEMVARIABLES'); // Set code access permission to help protect the use of // Statement.executeUpdate. perm = new SqlStatementExecutePermission(sql); perm.assert(); try { r = stmt.executeQuery(sql); while (r.next()) { print r.getString(1); pause; } } catch (exception::Error) { print "An error occured in the query."; pause; } // Code access permission scope ends here. CodeAccessPermission::revertAssert(); }
Harish Mohanbabu Long way to go before I sleep ..
Arun Garg: //N by Arun Garg for Connecting SQL SERVER static void DatabaseConnection(Args _args) { #CCADO CCADOConnection connection; CCADOCommand command; CCADORecordset recordset ; CCADOFields fields; str Database; str connectionStr; str statement; str CurrentCompanyCode; sqlSystem sq = new sqlSystem(); CustTable custTable; ; CurrentCompanyCode=curext(); connectionStr =" Driver={SQL Native Client};Trusted_Connection=no;SERVER= {" + sq.loginServer() + "};DATABASE={master};User Id='';Password=''; statement = "select AccountNum,Name"; statement+=" from CustomerTable where AxaptaProcessDateTime = 0"; info(strfmt("%1",connectionStr)); info(strfmt("%1",statement)); connection = new CCADOConnection(); connection.open(connectionStr,#adConnectUnspecified); command = new CCADOCommand(); command.activeConnection(connection); command.commandText(statement); try { recordset = command.execute(); while(!recordset.EOF()) { fields = recordset.fields(); CustTable.AccountNum = fields.itemIdx(0).value(); //entry in PartyTable CustTable.Name = fields.itemIdx(1).value(); CustTable.insert(); recordset.recordSet().movenext(); } //Update Table statement = "update CustomerTable set AxaptaProcessDateTime = getdate() where AxaptaProcessDateTime = 0 "; command.commandText(statement); command.execute(); info("Records Inserted"); connection.close(); } catch { connection.close(); throw error ( "Error "); } }
When I try this code I get the following error message "[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified."
Can anyone help me with this ?