Hello all,
This forum has been invaluable in helping us get started using QODBC, and I would like to give a special thanks to Tom for all his hard work in answering our many questions :-)
I've read just about every posting in this forum on QODBC running under a Windows service, but I am still having a heck of a time getting the synchronization of data to work successfully, and on a timely basis.
This is going to be a long post, so you may want to get some Red Bull...
Summary of Project
We are responsible for synchronizing a SQL Server 2005 database with payment and customer information from QuickBooks, and synchronizing payment information entered by our users in the same SQL Server 2005 database, into QuickBooks.
We have written an .NET Web service that a Windows service may call to both send and retrieve the payment and customer information from and to the SQL Server 2005 instance.
We have written a Windows service that handles the communications with QuickBooks and the Web service.
Software Demographics for QODBC and QuickBooks
QuickBooks: Premier 2007 with all the latest patches
QuickBooks Data File: Located on a mapped network drive
Data File Size: 90 MB
QODBC: v 7.00.00.214 evaluation Server Edition
OS: Windows XP Professional with all the latest service packs
DCOM: DCOM has been enabled and I ran the DCOM configuration script from QODBC
QODBC Configuration
DCOM Configuration
Note... I'm just showing the launch and activation screen shot, but the access permissions and Configuration permissions are similar. I used the DCOM configuration program supplied by QODBC to configure this.
Problem
The problem we are having, which seems to be typical of a lot of users is that synchronizing the data from QuickBooks using QODBC is extremely slow.
I have tried running the Optomizer with all the different settings, including turning it off, and it doesn't seem to make any difference what so ever on the speed of extracting data from Quick Books. I have also followed the directions on resetting the Optomizer file, but to no avail.
When I say it takes a long time, I have yet to see it finish, and this is after 48 hours. I usually get an error in the log file such as this:
2007-04-18 09:34:12 QODBC Ver: 7.00.00.214 *********************************************************************************************************************
IsAService: True
SQLOptimize_OpenOptimizeDBHandle prepare:
SELECT "type", "tbl_name" FROM "SQLite_MASTER"
file is encrypted or is not a database
From this error, I would assume that QODBC was either having trouble accessing the file, or the file was corrupt. I'm running the service as myself for now, and I certainly have access to the local file. As stated previously, I have followed the directions on resetting the Optimizer file, and re-generating it, so it shouldn't be corrupted.
We have made sure we are not using "SELECT * from tablename", and have used the JumpIn columns where we could. The following are the SQL statements we are using. All of them take hours to complete, and we are talking about a few thousand records is all.
Get Customers
SELECT ListID, TimeCreated, TimeModified, EditSequence, Name, FullName,
AccountNumber, FirstName, LastName, BillAddressAddr1, BillAddressAddr2,
BillAddressAddr3, BillAddressAddr4, BillAddressCity, BillAddressState,
BillAddressPostalCode, BillAddressCountry, Email, Phone, AltPhone,
CustomerTypeRefFullName, CustomerTypeRefListID, isActive
FROM Customer NOSYNC
WHERE CustomerTypeRefFullName IN ('Van Pooler')
Get Payments for a particular TxnID (Two separate queries)
SELECT CustomerRefFullname, CustomerRefListID, EditSequence, Memo, PaymentMethodRefFullName,
PaymentMethodRefListID, RefNumber, TimeCreated, TimeModified, TotalAmount, TxnDate, TxnID, TxnNumber, UnusedCredits, UnusedPayment
FROM ReceivePayment NOSYNC
WHERE TxnID = '?'
SELECT AppliedToTxnPaymentAmount, AppliedToTxnBalanceRemaining, AppliedToTxnDiscountAmount,
AppliedToTxnTxnID, TxnID, RefNumber, TxnDate "
FROM ReceivePaymentLine NOSYNC
WHERE TxnID = '?'
Insert a payment (Dynamically built Values list)
INSERT INTO ReceivePayment (CustomerRefListID, ARAccountRefListID, RefNumber, PaymentMethodRefFullName,
DepositToAccountRefListID, TotalAmount, IsAutoApply, TxnDate)
Values...
Get Invoices (two separate queries)
SELECT DISTINCT invoice.TxnID,invoice.TimeCreated,invoice.TimeModified, "
invoice.EditSequence, invoice.TxnNumber, invoice.CustomerRefListID CustomerListID,
invoice.CustomerRefFullName CustomerFullName, invoice.TxnDate, invoice.RefNumber,
invoice.IsPending, invoice.IsFinanceCharge, invoice.PONumber, invoice.DueDate,
invoice.Subtotal, invoice.SalesTaxTotal, invoice.AppliedAmount, invoice.BalanceRemaining
FROM invoice NOSYNC
INNER JOIN customer NOSYNC ON (invoice.CustomerRefListID = customer.ListID)
WHERE customer.CustomerTypeRefFullName IN ('Van Pooler')
AND invoice.TimeModified > {ts 'yyyy-MM-dd HH:mm:ss.fff'}
SELECT txnID, InvoiceLineTxnLineID, InvoiceLineItemRefListID, InvoiceLineItemRefFullName,
InvoiceLineDesc, InvoiceLineQuantity, InvoiceLineRate, InvoiceLineRatePercent, InvoiceLineAmount
FROM invoiceLine NOSYNC
WHERE txnID = '?'
Note: Everywhere you see the NOSYNC statement being used, we are calling the following Stored Procedures to make sure the data is optomized. This is not done in a loop, but is done once per feature (Get Invoices, Get Customers, etc)
// open a connection to the QB database
conn = new System.Data.Odbc.OdbcConnection(QBAPISettings.Current.QbDatabaseConnection);
conn.ConnectionTimeout = 300; // 5 minutes
conn.Open();
command = conn.CreateCommand();
command.CommandTimeout = 10800; // 3 hours
command.CommandType = CommandType.Text;
// ************** OPTIMIZE tables ******************
command.CommandText = "sp_optimizeupdatesync Customer";
//execute the command
command.ExecuteNonQuery();
Obviously, "Customer" is replaced for each table we need to update
We are also careful not to open and close a connection to QODBC in a loop. The connection is opened and closed once per feature.
Here is an example of the complete code for getting all the customers (Note, this will take about 2 hours to run under .NET, but only a few mintues under the VB Demo program. I'm thinking this may be the case because we are using DCOM in the .NET environment due to it running as a service:
/// <summary>
/// Gets a list of customers modified since lastSyncDateTime.
/// </summary>
/// <param name="lastSyncDateTime">Last time a syncronization occured.</param>
/// <returns>ArrayList that contains a collection of QBCustomers.</returns>
private ArrayList GetCustomers(DateTime lastSyncDateTime)
{
ArrayList customerList = new ArrayList();
OdbcConnection conn = null;
OdbcCommand command = null;
OdbcDataReader dr = null;
string sql = "SELECT ListID, TimeCreated, TimeModified, EditSequence, Name, FullName," +
"AccountNumber, FirstName, LastName, BillAddressAddr1, BillAddressAddr2, " +
"BillAddressAddr3, BillAddressAddr4, BillAddressCity, BillAddressState, " +
"BillAddressPostalCode, BillAddressCountry, Email, Phone, AltPhone, " +
"CustomerTypeRefFullName, CustomerTypeRefListID, isActive " +
"FROM Customer NOSYNC " +
"WHERE CustomerTypeRefFullName IN (" + QBAPISettings.Current.QbCustomerTypes + ")";
try
{
//// filter the list of customer records by the last sync date
if (lastSyncDateTime != DateTime.MinValue)
{
// add the time modified filter using UTC time
sql += " AND TimeModified > {ts '" + lastSyncDateTime.ToString("yyyy-MM-dd HH:mm:ss.fff") + "'}";
}
// open a connection to the QB database
conn = new System.Data.Odbc.OdbcConnection(QBAPISettings.Current.QbDatabaseConnection);
conn.ConnectionTimeout = 300; // 5 minutes
conn.Open();
command = conn.CreateCommand();
command.CommandType = CommandType.Text;
command.CommandTimeout = 10800; // 3 hours
//// ************** OPTIMIZE tables ******************
command.CommandText = "sp_optimizeupdatesync Customer";
// execute the command
command.ExecuteNonQuery();
// Create a command to get the customers
command.CommandText = sql;
// exectute the command
dr = command.ExecuteReader(CommandBehavior.Default);
// process the results
while (dr.Read())
{
QBCustomer cust = new QBCustomer();
cust.AccountNumber = (dr.IsDBNull(dr.GetOrdinal("AccountNumber")) ? "" : dr.GetString(dr.GetOrdinal("AccountNumber")));
cust.BillAddr1 = (dr.IsDBNull(dr.GetOrdinal("BillAddressAddr1")) ? "" : dr.GetString(dr.GetOrdinal("BillAddressAddr1")));
cust.BillAddr2 = (dr.IsDBNull(dr.GetOrdinal("BillAddressAddr2")) ? "" : dr.GetString(dr.GetOrdinal("BillAddressAddr2")));
cust.BillAddr3 = (dr.IsDBNull(dr.GetOrdinal("BillAddressAddr3")) ? "" : dr.GetString(dr.GetOrdinal("BillAddressAddr3")));
cust.BillAddr4 = (dr.IsDBNull(dr.GetOrdinal("BillAddressAddr4")) ? "" : dr.GetString(dr.GetOrdinal("BillAddressAddr4")));
cust.BillCity = (dr.IsDBNull(dr.GetOrdinal("BillAddressCity")) ? "" : dr.GetString(dr.GetOrdinal("BillAddressCity")));
cust.BillCountry = (dr.IsDBNull(dr.GetOrdinal("BillAddressCountry")) ? "" : dr.GetString(dr.GetOrdinal("BillAddressCountry")));
cust.BillPostalCode = (dr.IsDBNull(dr.GetOrdinal("BillAddressPostalCode")) ? "" : dr.GetString(dr.GetOrdinal("BillAddressPostalCode")));
cust.BillState = (dr.IsDBNull(dr.GetOrdinal("BillAddressState")) ? "" : dr.GetString(dr.GetOrdinal("BillAddressState")));
cust.CustomerTypeFullName = (dr.IsDBNull(dr.GetOrdinal("CustomerTypeRefFullName")) ? "" : dr.GetString(dr.GetOrdinal("CustomerTypeRefFullName")));
cust.CustomerTypeListID = (dr.IsDBNull(dr.GetOrdinal("CustomerTypeRefListID")) ? "" : dr.GetString(dr.GetOrdinal("CustomerTypeRefListID")));
cust.EditSequence = (dr.IsDBNull(dr.GetOrdinal("EditSequence")) ? "" : dr.GetString(dr.GetOrdinal("EditSequence")));
cust.Email = (dr.IsDBNull(dr.GetOrdinal("Email")) ? "" : dr.GetString(dr.GetOrdinal("Email")));
cust.FirstName = (dr.IsDBNull(dr.GetOrdinal("FirstName")) ? "" : dr.GetString(dr.GetOrdinal("FirstName")));
cust.FullName = (dr.IsDBNull(dr.GetOrdinal("FullName")) ? "" : dr.GetString(dr.GetOrdinal("FullName")));
cust.IsActive = (dr.IsDBNull(dr.GetOrdinal("isActive")) ? false : dr.GetBoolean(dr.GetOrdinal("isActive")));
cust.LastName = (dr.IsDBNull(dr.GetOrdinal("LastName")) ? "" : dr.GetString(dr.GetOrdinal("LastName")));
cust.ListID = (dr.IsDBNull(dr.GetOrdinal("ListID")) ? "" : dr.GetString(dr.GetOrdinal("ListID")));
cust.Name = (dr.IsDBNull(dr.GetOrdinal("Name")) ? "" : dr.GetString(dr.GetOrdinal("Name")));
cust.Phone = (dr.IsDBNull(dr.GetOrdinal("Phone")) ? "" : dr.GetString(dr.GetOrdinal("Phone")));
cust.SecondPhone = (dr.IsDBNull(dr.GetOrdinal("AltPhone")) ? "" : dr.GetString(dr.GetOrdinal("AltPhone")));
cust.TimeCreated = (dr.IsDBNull(dr.GetOrdinal("TimeCreated")) ? DateTime.MinValue : dr.GetDateTime(dr.GetOrdinal("TimeCreated")));
cust.TimeModified = (dr.IsDBNull(dr.GetOrdinal("TimeModified")) ? DateTime.MinValue : dr.GetDateTime(dr.GetOrdinal("TimeModified")));
customerList.Add(cust);
}
}
catch (Exception ex)
{
QBSyncLogger.Write(m_QBSyncStatus.SyncObject, "Error", ex.ToString());
}
finally
{
if (dr != null) dr.Close();
if (conn != null) conn.Close();
}
return customerList;
}
I have monitored the service in Windows Task Manager, and have seen the following:
When the application first starts, I see that QuickBooks is loaded, as well as the DCOM wrapper. QuickBooks starts using up to 20% CPU for the first few minutes, then my application goes to 100% CPU usage for the next two hours. If I monitor this with FileMon.exe, I can see that the msxml4.dll is being accessed over and over again with about 10ms between access usage. I'm assuming that this is QODBC cycling through the XML file that QuickBooks produced.
Can anyone see where I might have a configuration wrong, or maybe doing something incorrectly in the code?
Any help on this would be greatly appreciated.
Regards (or should I say G'day Mate)
| 
