Troubleshooting - QODBC Driver dropping a few transactions on insert to InvoiceLines

Problem Description:

We seem to have 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 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 COUNT 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, except that it did not include the WAITFOR DELAY code 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.

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 set the delay to wait for a second or two after each row of data. 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).A



Do you have any suggestions? Please advice.

Solutions:

We did troubleshooting and found SQL Server sends 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 the Linked Server connection - Not delete and re-add

[social.technet.microsoft.com]Is there a way to Disconnect the Linked Server connection - Not delete and re-add

There seems to be no option in SQL Server, which does not cause why SQL Server is sending the 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, it will sleep for 1 minute & 10 seconds. The sleep of 1 minute & 10 seconds will automatically send a disconnect command and resume after 1 minute. By doing so, you noticed no loss of invoice line But yes, inserting time increased due to sleep.

Please use the 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 invoice line to the existing Invoice by Passing TxnID & with FQSaveToCache as 0 in the insert statement. This will update the current invoice and add a new line item.

As the above solution does not use FQSaveToCache, the SQL Server Disconnect issue will not affect it.

Please use the 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