Troubleshooting - QODBC Driver dropping a few transactions on insert to InvoiceLines
Problem Description:
We seem to be having an issue with an insert into the InvoiceLines table dropping a few transactions.
We need to create about 1000 invoices per month, with an average of about 6 - 7 lines per invoice. As QuickBooks cannot really handle a set-based insert, we have used a cursor to insert the invoice lines and generate an invoice customer by customer (using the FQSaveToCache flag as the trigger. The code is as follows:
SET NOCOUNT ON
DECLARE curOutput CURSOR FOR SELECT CustomerRefListID,TemplateRefListID,TxnDate,TermsRefListID,CustomerMsgRefListID,InvoiceLineItemRefListID,InvoiceLineQuantity,InvoiceLineRate,InvoiceLineAmount,FQSavetoCache FROM dbo.QBQODBC_INVOICEPOSTINGDATA WHERE CustomerRefListID IS NOT NULL ORDER BY CustomerRefListID, FQSavetoCache DESC DECLARE @CustomerID VARCHAR(100) DECLARE @TemplateID VARCHAR(100) DECLARE @TransactionDate DATETIME DECLARE @CustomerTermsID VARCHAR(100) DECLARE @CustomerMsgID VARCHAR(100) DECLARE @ItemID VARCHAR(100) DECLARE @Quantity DECIMAL(18,2) DECLARE @Rate DECIMAL(18,2) DECLARE @Amount DECIMAL(18,2) DECLARE @QBFlag INT DECLARE @i INT SET @i = 0
OPEN curOutput FETCH NEXT FROM curOutput
INTO @CustomerID,@TemplateID,@TransactionDate,@CustomerTermsID,@CustomerMsgID,@ItemID,@Quantity,@Rate,@Amount,@QBFlag; --Subroutine for QB insert WHILE @@FETCH_STATUS = 0
BEGIN INSERT INTO QREMOTE...INVOICELINE (CustomerRefListID,TemplateRefListID,TxnDate,TermsRefListID,CustomerMsgRefListID,InvoiceLineItemRefListID, InvoiceLineQuantity,InvoiceLineRate,InvoiceLineAmount,FQSaveToCache) VALUES( @CustomerID, @TemplateID, @TransactionDate, @CustomerTermsID, @CustomerMsgID, @ItemID, @Quantity, @Rate, @Amount, @QBFlag ) SET @i = @i + 1 IF @QBFlag = 0 and @i > 500 BEGIN WAITFOR DELAY '00:05:00' set @i = 0 END
PRINT 'Transaction quantity of ' + CAST(@Quantity AS VARCHAR(12)) + ' for item ID ' + @ItemID + ' for customer ID ' + @CustomerID + ' is now posted.' FETCH NEXT FROM curOutput INTO @CustomerID, @TemplateID, @TransactionDate, @CustomerTermsID, @CustomerMsgID, @ItemID, @Quantity, @Rate, @Amount, @QBFlag ; END --End subroutine for QB insert
CLOSE curOutput DEALLOCATE curOutput
Our original code was identical to the above, with the exception of it did not include the WAITFOR DELAY code that is triggered every time the FQSavetoCache flag is set to zero. The code works beautifully and has successfully created invoices in QuickBooks for hundreds of customers.
Unfortunately, we are only at about a 97 - 98% success rate in the accuracy of the invoices being generated, and the issue seems to be the inability of QuickBooks (or the QODBC driver) to keep up with the data being fed from our SQL Server table (which we have verified is 100% accurate). I thought that adding the 10-second delay when the FQSavetoCache flag was set to zero would resolve the issue, but it has not. If the 10-second delay doesn't seem to work when the flag is zero, I could, of course, set the delay to wait for a second or two after each row of data.

Do you have any suggestions? Please advice.
Solutions:
We did troubleshooting and found SQL Server is sending the Disconnect command every 5 minutes. The disconnect command is not expected from SQL Server when there is activity on the linked server. This is not a QODBC or QRemote issue.
[forums.sqlteam.com]SQL Server Linked Server to ODBC Connection Drops connection every 4-5 minutes
[social.technet.microsoft.com]SQL Server Linked Server to ODBC Connection Drops connection every 4-5 minutes
[forums.sqlteam.com]Is there a way to Disconnect Linked Server connection - Not delete and re-add
[social.technet.microsoft.com]Is there a way to Disconnect Linked Server connection - Not delete and re-add
There seems to be no option in SQL Server or do not cause why SQL Server is sending Disconnect command.
We can't use MS Access as middleware because MS Access as a middleware has the same issue.
Workaround 1:
As a workaround, You can add some timing logic to the looping like if the header insert is completed if it is 4 minutes, will sleep for 1 minute & 10 seconds. Sleep of 1 minute & 10 seconds will automatically send disconnect command and resumed after 1 minute. By doing so, you noticed no loss of invoice line But yes inserting time increase due to sleep.
Please use below script:
SET NOCOUNT ON
DECLARE curOutput CURSOR FOR SELECT CustomerRefListID,TemplateRefListID,TxnDate,TermsRefListID,CustomerMsgRefListID,InvoiceLineItemRefListID,InvoiceLineQuantity,InvoiceLineRate,InvoiceLineAmount,FQSavetoCache FROM dbo.QBQODBC_INVOICEPOSTINGDATA WHERE CustomerRefListID IS NOT NULL ORDER BY CustomerRefListID, FQSavetoCache DESC DECLARE @CustomerID VARCHAR(100) DECLARE @TemplateID VARCHAR(100) DECLARE @TransactionDate DATETIME DECLARE @CustomerTermsID VARCHAR(100) DECLARE @CustomerMsgID VARCHAR(100) DECLARE @ItemID VARCHAR(100) DECLARE @Quantity DECIMAL(18,2) DECLARE @Rate DECIMAL(18,2) DECLARE @Amount DECIMAL(18,2) DECLARE @QBFlag INT DECLARE @i INT DECLARE @firstTime datetime DECLARE @currentTime datetime SET @i = 0 set @firstTime=GETDATE(); set @currentTime=GETDATE();
OPEN curOutput FETCH NEXT FROM curOutput INTO @CustomerID,@TemplateID,@TransactionDate,@CustomerTermsID,@CustomerMsgID,@ItemID,@Quantity,@Rate,@Amount,@QBFlag; --Subroutine for QB insert WHILE @@FETCH_STATUS = 0
BEGIN INSERT INTO QRemote...INVOICELINE (CustomerRefListID,TemplateRefListID,TxnDate,TermsRefListID,CustomerMsgRefListID,InvoiceLineItemRefListID, InvoiceLineQuantity,InvoiceLineRate,InvoiceLineAmount,FQSaveToCache) VALUES( @CustomerID, @TemplateID, @TransactionDate, @CustomerTermsID, @CustomerMsgID, @ItemID, @Quantity, @Rate, @Amount, @QBFlag )
PRINT 'Transaction quantity of ' + CAST(@Quantity AS VARCHAR(12)) + ' for item ID ' + @ItemID + ' for customer ID ' + @CustomerID + ' is now posted.' set @currentTime=GETDATE();
IF @QBFlag = 0 and ((SELECT DATEDIFF(ss,@firstTime,@currentTime))> 240) --240=4 minutes. 270=4:30minutes
BEGIN PRINT 'It is Sleep time ' + CAST(@QBFlag AS VARCHAR(12)) + ' for @firstTime : ' + CONVERT(nvarchar(90), @firstTime, 21) + ' @currentTime :' + CONVERT(nvarchar(90), @currentTime, 21) + ' ------. ' WAITFOR DELAY '00:01:10' set @firstTime=GETDATE(); set @currentTime=GETDATE(); PRINT 'Wake up time ' + CAST(@QBFlag AS VARCHAR(12)) + ' for @firstTime : ' + CONVERT(nvarchar(90), @firstTime, 21) + ' @currentTime :' + CONVERT(nvarchar(90), @currentTime, 21) + ' ------. ' END
FETCH NEXT FROM curOutput INTO @CustomerID, @TemplateID, @TransactionDate, @CustomerTermsID, @CustomerMsgID, @ItemID, @Quantity, @Rate, @Amount, @QBFlag ; END
--End subroutine for QB insert CLOSE curOutput DEALLOCATE curOutput
Workaround 2:
You can use another workaround as below:
1) Insert an Invoice Line along with Invoice header fields with FQSaveToCache as 0. This will create an invoice in QuickBooks.
2) Get the Last Insert ID of the Invoice.
3) Using a loop for the rest of the invoice line, Insert each of the Invoice lines to the existing Invoice by Passing TxnID & with FQSaveToCache as 0 in the insert statement. This will update the existing invoice and add a new line item.
As we above solution does not use FQSaveToCache, SQL Server Disconnect issue will not affect.
Please use below script:
SET NOCOUNT ON
DECLARE curOutput CURSOR FOR SELECT CustomerRefListID,TemplateRefListID,TxnDate,TermsRefListID,CustomerMsgRefListID,InvoiceLineItemRefListID,InvoiceLineQuantity,InvoiceLineRate,InvoiceLineAmount,FQSavetoCache FROM dbo.QBQODBC_INVOICEPOSTINGDATA WHERE CustomerRefListID IS NOT NULL ORDER BY CustomerRefListID, FQSavetoCache DESC DECLARE @CustomerID VARCHAR(100) DECLARE @TemplateID VARCHAR(100) DECLARE @TransactionDate DATETIME DECLARE @CustomerTermsID VARCHAR(100) DECLARE @CustomerMsgID VARCHAR(100) DECLARE @ItemID VARCHAR(100) DECLARE @Quantity DECIMAL(18,2) DECLARE @Rate DECIMAL(18,2) DECLARE @Amount DECIMAL(18,2) DECLARE @QBFlag INT DECLARE @i INT DECLARE @txnID VARCHAR(100) SET @i = 0 OPEN curOutput set @txnID ='' FETCH NEXT FROM curOutput INTO @CustomerID,@TemplateID,@TransactionDate,@CustomerTermsID,@CustomerMsgID,@ItemID,@Quantity,@Rate,@Amount,@QBFlag; --Subroutine for QB insert WHILE @@FETCH_STATUS = 0 BEGIN
if @txnID ='' begin INSERT INTO QRemote...INVOICELINE (CustomerRefListID,TemplateRefListID,TxnDate,TermsRefListID,CustomerMsgRefListID,InvoiceLineItemRefListID, InvoiceLineQuantity,InvoiceLineRate,InvoiceLineAmount,FQSaveToCache) VALUES( @CustomerID, @TemplateID, @TransactionDate, @CustomerTermsID, @CustomerMsgID, @ItemID, @Quantity, @Rate, @Amount, 0 ) --Get the Last inserted it
Set @txnid= (select LastInsertId from openquery(QRemote,'sp_lastinsertid InvoiceLine'))
end
else
begin INSERT INTO QRemote...INVOICELINE (txnID,CustomerRefListID,TemplateRefListID,TxnDate,TermsRefListID,CustomerMsgRefListID,InvoiceLineItemRefListID, InvoiceLineQuantity,InvoiceLineRate,InvoiceLineAmount,FQSaveToCache) VALUES( @txnID,@CustomerID, @TemplateID, @TransactionDate, @CustomerTermsID, @CustomerMsgID, @ItemID, @Quantity, @Rate, @Amount, 0 ) end
if @QBFlag=0 begin set @txnID ='' end
PRINT 'Transaction quantity of ' + CAST(@Quantity AS VARCHAR(12)) + ' for item ID ' + @ItemID + ' for customer ID ' + @CustomerID + ' is now posted.' FETCH NEXT FROM curOutput INTO @CustomerID, @TemplateID, @TransactionDate, @CustomerTermsID, @CustomerMsgID, @ItemID, @Quantity, @Rate, @Amount, @QBFlag ; END --End subroutine for QB insert
CLOSE curOutput DEALLOCATE curOutput
|