You’re offline. This is a read only version of the page.
155
Hi everyone,
I use OdbcConnection and below code is only piece of all code. I copy my query from SQL and paste it in this x++ code. When I execute the all code, everything is perfect , but it takes 1 hour. The colum are 9 and rows are 50 000. The query is 20 lines and is not complicated. Give me advice to suit you in another way. I want to transfer the dates from SQL to EXCEL by X++.
Share
Report OdbcConnection odbcConnection;
Statement statement;
//Variables to connection
#define.ServerDefault('ServerName')
#define.DatabaseDefault("Database Name")
//Sentence to execute in SQLServer-database
str sql;
//Result of excecution
ResultSet resultSet;
LoginProperty loginProperty;
SqlStatementExecutePermission perm;
Dimension dimension;
SysExcelApplication excel;
SysExcelWorkbooks workbooks;
SysExcelWorkbook workbook;
SysExcelWorksheets worksheets;
SysExcelWorksheet worksheet;
SysExcelCells cells;
SysExcelCell cell;
int row;
;
excel = SysExcelApplication::construct();
workbooks = excel.workbooks();
workbook = workbooks.add();
worksheets = workbook.worksheets();
worksheet = worksheets.itemFromNum(1);
cells = worksheet.cells();
cells.range('A:A').numberFormat('@');
-------------------------------------------------------------
//Get ODBC connection
loginProperty = new LoginProperty();
loginProperty.setServer(#ServerDefault);
loginProperty.setDatabase(#DatabaseDefault);
//Create a connection to external database
try
{
odbcConnection = new OdbcConnection(loginProperty);
}
catch
{
throw error("Error creating ODBC Connection");
}
//Exec sentence
if(odbcConnection)
{
if(sql == "")
{
throw error("No sql sentence to execute");
}
//Assert permission for executing the sql string
perm = new SqlStatementExecutePermission(sql);
//Check for permission to use the statement
perm.assert();
//Prepare the sql statement
statement = odbcConnection.createStatement();
resultSet = statement.executeQuery(sql);
}
*This post is locked for comments
ExcelWorkbooks writing is little bit slow with X++ if you are comparing with C#... kindly use select statement instead of access via ODBC... another thing check execution plan on SQL where your query taking time..
ExcelWorkbooks writing is little bit slow with X++ if you are comparing with excel... kindly use select statement instead of access via ODBC... another thing check execution plan on SQL where your query taking time..
First of all you should write your select statement in x++ instead of executing pure SQL statements.
Is your query executing slowly also if you execute it directly in SQL Server?
Do you have criteria fields in your query that are not covered by any table index?
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
#1
André Arnaud de Cal...
291,253
Super User 2024 Season 2
#2
Martin Dráb
230,188
Most Valuable Professional
#3
nmaenpaa
101,156
All responses (
Answers (