RSS Feed
Knowledgebase : TroubleShooting > SQL Language Support (Syntax)
     

[QODBC ALL] - HOW TO INCREASE THE UPDATE OR DELETE SQL STATEMENT PERFORMANCE. PROBLEM DESCRIPTION: Ran this SQL to move an invoice from one Customer to another: Update invoice set CustomerRefListID = '1500' where TxnNumber = 8453 It took about 15+ min...
HOW TO USE POWERSHELL WITH QODBC Download Sample [http://support.flexquarters.com/esupport/newimages/PowerShell/DisplaySQL.zip] SAMPLE CODE FOR USING QODBC DSN: $connectstring = "DSN=QuickBooks Data;" $sql = "Select TOP 10 Name from Customer" $conn ...
TROUBLESHOOTING - UPDATE FROM A SELECT STATEMENT NOT WORKING PROBLEM DESCRIPTION: I am trying to update records through the select statement. But it is not working. UPDATE Customer SET CustomFieldProjectNumber = i.JobNumber FROM ( SELECT ListID, {fn...
TROUBLESHOOTING - GETTING INCOMPLETE DATA WHEN RUNNING THE PROFITANDLOSSBUDGETVSACTUAL REPORT PROBLEM DESCRIPTION: Good Day To You. Using This Sp_report And Having Some Problems With The Column Layout. Command Text Is As Follows: Sp_report Profitandlo...
HOW TO IMPORT QUICKBOOKS DATA TO SQL SERVER DATABASE (LOCAL TABLE) First, you need to create Linked Server in SQL Server. For 32-bit SQL Server. Please refer to How to create Link Server with MS SQL 2005/2008/2012 32-bit Using QODBC/QRemote [http://sup...
TROUBLESHOOTING - QODBC RECORD SET ISSUE PROBLEM DESCRIPTION: I am using the below code. It is returning records & giving me the correct result. But If I change the query from "SELECT Name FROM customer" to "SELECT * FROM customer," it does not return...
SAMPLE CODE FOR C++ WITH QODBC #include #include #include #include #include #include #include #include using namespace std; void ConvertTimeStampStructToString(TIMEST...
TROUBLESHOOTING - HOW DO I SEE BACKORDER ITEMS THAT HAVE BEEN INVOICED BUT NOT SHIPPED PROBLEM DESCRIPTION: Could someone kindly provide me with a sample, or is there a stored procedure that would allow me to only pull out back ordered invoices from t...
TROUBLESHOOTING - TRYING TO UPDATE THE ITEMINVENTORYASSEMBLYLINE TABLE PROBLEM DESCRIPTION: All our products are Inventory Assemblies. I am using MS Access connected to QuickBooks via QODBC. I am using queries in MS Access to loading/synchronize Inven...
TROUBLESHOOTING - HOW DO I UPDATE CUSTOMER CUSTOM FIELDS PROBLEM DESCRIPTION: How do I update Customer custom fields? SOLUTION: You can update Customer custom fields using the update query. For Example: Update Customer Set CustomFieldContract='QO...
TROUBLESHOOTING - GETTING ERROR "INDEX WAS OUTSIDE THE ARRAY BOUNDS." WHEN USING THE IMPORT/EXPORT TOOL IN SQL SERVER PROBLEM DESCRIPTION 1: I recently installed QODBC to get data out of a QuickBooks company file, but I get this error: "Index was outs...
TROUBLESHOOTING - HOW DO I REMOVE SUBTOTALS AND BLANK LINES IN SP_REPORT PROBLEM DESCRIPTION: I'm trying to link a general ledger report in Excel. I've created the sp_report SQL query in Microsoft Query just fine, but the data it returns contains subt...
TROUBLESHOOTING - UPGRADING TO QUICKBOOKS AND QODBC PROBLEM DESCRIPTION: I have a custom program written in Access 2003 communicating with QuickBooks 2013 using QODBC 13, which works fine. Will upgrade to the latest QuickBooks and QODBC require any co...
TROUBLESHOOTING - HOW TO USE BETWEEN IN QODBC PROBLEM DESCRIPTION: I need to use Between, in a range of dates, to return new customers in one period. I try this: SELECT FullName FROM Customer Where TimeCreated Between {d'2007-12-18'} AND {d'2007-12...
TROUBLESHOOTING - ODBC DRIVER DOESN'T SUPPORT THIS STATEMENT. UPDATING EXISTING RECORD PROBLEM DESCRIPTION: I'm trying to make Inventory Items In-Active after a sale programmatically or Active after voiding a sale. I thought the following SQL statem...
TROUBLESHOOTING - HOW DO I GET TABLE RECORD COUNT THE FASTEST WAY USING QODBC PROBLEM DESCRIPTION: What is the fastest way to get the number of records from a table? SOLUTION: QODBC would need to scan the whole table to get the record or row count....
TROUBLESHOOTING - HOW CAN I USE WILDCARD (*) WITH CRYSTAL REPORT AND SP_REPORT PROBLEM DESCRIPTION: I am using the following SQL statement: sp_report JobProfitabilityDetail show AmountActualCost_Title, AmountActualRevenue_Title, AmountDifferenceActu...
TROUBLESHOOTING - HOW CAN I GET THE LAST ITEMS SOLD TO CUSTOMER DETAILS PROBLEM DESCRIPTION: I created a report using a custom set with the customer as the parent and InvoiceLine as the child giving me items ordered in the past. My problem is I only...
TROUBLESHOOTING - HOW TO EXTRACT PRINTED CHECKS INFORMATION USING QODBC PROBLEM DESCRIPTION: We are involved in a new project with our bank involving a 'Positive Payment' solution that requires I send them a file with the check summary data each time ...
TROUBLESHOOTING - CORRELATED SUBQUERIES IN THE UPDATE STATEMENT PROBLEM DESCRIPTION: I want to update the salesrep field in my existing invoices. I want to use the salesrep value stored in the Customer file. I would normally do this in SQL using a cor...
TROUBLESHOOTING - BILLABLE PAYROLL - NEED TO DISPLAY MORE FIELDS ON INVOICE PROBLEM DESCRIPTION: We need to bill all payroll costs to various customers. Payroll costs are defined as all of the following: 1. Employee's gross salary 2. Company share ...
HOW TO IMPORT DATA TO QUICKBOOKS THROUGH CSV USING QODBC NOTE: QODBC does not support direct import, But you can write VBA code that reads a CSV / Excel file & generates insert statements. There are two ways to Import data to QuickBooks through CSV us...
TROUBLESHOOTING - HOW TO STOP ANY QUERY ON VB DEMO APPLICATION PROBLEM DESCRIPTION: How to Stop any query on VBDEMO when it takes time. How to convert the date to QuickBooks date. I want to insert TxnDate in the ReceivePayment table, but it pops u...
TROUBLESHOOTING - HOW DO I VOID AN INVOICE? PROBLEM DESCRIPTION: I don't see any indicator in the Invoice table. How do I void an Invoice? And How do I query for invoices that have been voided? SOLUTION: You can use SP_VOID just like you would use ...
TROUBLESHOOTING - HOW DO I GET TIMEFRAME INFORMATION - SALES BY HOUR REPORTING USING QODBC PROBLEM DESCRIPTION: This is a no technical question but one I need to answer myself before jumping into QODBC. I'm considering working with QODBC to get the in...
TROUBLESHOOTING - HOW DO I DELETE ORPHANED INVOICELINE RECORDS? PROBLEM DESCRIPTION: The InvoiceLine table contains orphan records (the parent invoice record no longer exists). I tried: DELETE FROM Invoice WHERE TxnID = 'xxxx-xxxxxxxxx' The TxnID is...
PROBLEM DESCRIPTION: How can I get daily payment bill information? And How can I get daily Bill Payment information using QODBC, and How can I get these details? SOLUTION: You can use the query below to get details about daily Bill Payments. For Exa...
TROUBLESHOOTING - HOW CAN I GET ALL THE RECORDS FOR A SPECIFIED YEAR PROBLEM DESCRIPTION: I need to compute a Year to Date total paid using the Invoice table. I'm working in MS-Access and need to run the following statement: SELECT SUM(Subtotal) FRO...
TROUBLESHOOTING - HOW CAN I CHANGE THE PRICING AND QUANTITY OF ITEMS IN AN ITEMGROUP? PROBLEM DESCRIPTION: How can I change the price of each item in a group? Is it possible? If yes, please give me an example. SOLUTION: The ItemInventory, ItemServi...
TROUBLESHOOTING - EXPENSES WRITTEN TO BILLEXPENSELINE ARE NOT SHOWING AS BILLABLE WHEN VIEWED IN QUICKBOOKS PROBLEM DESCRIPTION: The field ExpenseLineBillableStatus contains the status of the BillExpenseLine and uses the following stored procedure "sp...
TROUBLESHOOTING - HOW DO I LIST THE SUBTOTAL OF INVOICES WITHIN THE DATE RANGE PROBLEM DESCRIPTION: I want to list the subtotal of all Invoices by a customer for not only that particular customer but all jobs assigned to that customer within a specifi...
TROUBLESHOOTING - INVOICE LINE RATE IS NOT LOADING INTO QUICKBOOKS ONLINE. PROBLEM DESCRIPTION: The InvoiceLineRate value passed in the insert query to create the InvoiceLine record is not being loaded into QBO. Below are the two queries I am using t...
TROUBLESHOOTING - [QODBC] INVALID OPERAND FOR THE OPERATOR: PROBLEM DESCRIPTION: I am getting the error "Invalid operand for the operator: " while trying to insert an invoice line item: INSERT INTO InvoiceLine ( CustomerRefL...
TROUBLESHOOTING - [QODBC] COLUMN NOT FOUND REORDERBUILDPOINT PROBLEM DESCRIPTION: We just upgraded to v16 on both QB and QODBC, and now refreshes from Excel aren't working. We're getting the message "[QODBC] Column not found ReorderBuildPoint." SOLUT...
TROUBLESHOOTING - GETTING UNEXPECTED EXTRA TOKEN ERROR IN DATE FIELD QUERY PROBLEM DESCRIPTION: I am trying to query the Transaction table based on the TxnDate field. I tried the below code. OdbcCommand SourceCmd = new OdbcCommand("Select * from Tra...
WHAT IS JUMPIN TYPE? PROBLEM DESCRIPTION: I am looking for the definition and usage of "JUMPIN TYPE." SOLUTION: In QODBC JUMPIN provides relationship details with other tables and information about value coming from another table. Please refer to...
TROUBLESHOOTING - "CANNOT UPDATE TABLE ITEMINVENTORY ERROR 10004." PROBLEM DESCRIPTION: I am trying to run an update on the Name field in ItemInventory. I've checked to see if it's updateable, and it is. I get "Cannot update table ItemInventory Error...
PROBLEM DESCRIPTION: How to insert employee time information from MS Excel time spreadsheet into QuickBooks using QODBC SOLUTION: You cannot perform insert/update operations directly in MS Excel because Excel is just exporting tables in the spreadsh...
HOW TO RETRIEVE CHARACTER FIELDS THAT ARE RETURNED AS NULL IN SSIS USING QODBC PROBLEM: Using SQL Server Integration Service (SSIS) and QODBC, All character fields are being returned as NULL. SOLUTION: Please use the function CONVERT to convert the ...
HOW TO GET CONVERSION RATIO INFORMATION FROM UNITOFMEASURESETRELATEDUNIT TABLE IN QODBC Conversion ratio information is stored in a table called UnitOfMeasureSetRelatedUnit. PROBLEM: How to get Conversion Ratio information from UnitOfMeasureSetRelated...
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...
TROUBLESHOOTING - QODBC CONNECTION TO TALEND OPEN STUDIO NOTE: THE JDBC-ODBC BRIDGE DRIVER WAS REMOVED IN JAVA 8, PREVENTING ODBC DRIVERS FROM BEING USABLE IN NEWER VERSIONS OF JAVA OR OTHER JAVA-BASED APPLICATIONS. Refer: Oracle JDBC-ODBC Bridge [http...
TROUBLESHOOTING - HOW TO GET VBA TO EXECUTE TWO STATEMENTS AT THE SAME TIME PROBLEM DESCRIPTION: We use a Visual Basic app with an SQL Server database to write data to QB through QODBC. We are executing two SQL statements as described in your help. We ...
GENERAL CONNECTION STRING INSTRUCTIONS The most difficult part of using QRemote in programming is the connection string. The simplest form includes a reference to a DSN. A system DSN called "QuickBooks Data QRemote" for 32-bit applications & "QuickBook...
TROUBLESHOOTING - HOW TO LOCATE DATA FOUND IN QUICKBOOKS BY TABLE AND FIELD NAME INSIDE QODBC PROBLEM DESCRIPTION: I have a question about How to locate data found in QuickBooks by table and field name inside the QODBC system. SOLUTIONS: The fastest ...
TROUBLESHOOTING - HOW TO GIVE REFUND TO EXISTING CREDITMEMO USING QODBC PROBLEM DESCRIPTION: How can I apply a refund check to an existing credit memo using QODBC? In QuickBooks, I can refund by opening the Create Credit Memos/Refunds dialogue box usin...
TROUBLESHOOTING - GETTING QODBC NOT SUPPORTED ERROR WHILE INSERTING INVOICE PROBLEM DESCRIPTION: I am trying to insert an Invoice, But I am getting the below error: [QODBC] Not supported (#10003) I am using below SQL statements: INSERT INTO "Invo...
TROUBLESHOOTING - ERROR WHILE INSERTING BILL PROBLEM DESCRIPTION 1: I am following the below steps and getting the error. 1. First, we inserted the records in Bill & BillExpenseLine table, and the papers got inserted successfully. 2. Second, we are i...
EXAMPLES OF CREATING INVOICE USING QREMOTE FROM SQL SERVER NOTE: For multi-line invoices, some may find it easier to NOT include the header information with each line item, but rather to have separate smaller commands for the Invoice Lines and a final ...
TROUBLESHOOTING - HOW TO GET BANK RECONCILIATION DETAILS USING QODBC PROBLEM DESCRIPTION: How to get Bank Reconciliation details using QODBC I've not successfully identified any field/flag identifying the bank reconciliation status. Is this informatio...
DIFFERENCE BETWEEN QODBC FOR QUICKBOOKS DESKTOP AND ONLINE. TABLE DIFFERENCES: Number of Tables available in QODBC DESKTOP QODBC ONLINE 150 83 List of Tables available in QODBC Desktop [http://doc.qodbc.com/qodbc/usa/TableList.php?catego...
TROUBLESHOOTING - QREMOTE DOES NOT CONSIDER FQSAVETOCACHE WITH WORKING WITH ODBCCOMMAND & PARAMETERS PROBLEM DESCRIPTION: QRemote Does not consider FQSaveToCache with working with OdbcCommand & Parameters. I have an application that creates Sales Orde...
TROUBLESHOOTING - GETTING MULTIPLE-STEP OLE DB OPERATION GENERATED ERRORS IN SQL SERVER PROBLEM DESCRIPTION: Trying to insert into SalesOrderLine and getting a 'Multiple-step' error. INSERT INTO QBREMOTE...SalesOrderLine (CustomerRefListID, TemplateR...
PROBLEM DESCRIPTION: How to use parameters in OPENQUERY SOLUTIONS: OPENQUERY does not accept variables for its arguments. You need to Pass Basic Values as below example: SELECT QUERY: DECLARE @TSQL VARCHAR(8000), @ID VARCHAR(25) SELECT @ID = '1...
TROUBLESHOOTING - HOW TO SELECT A RECORD WHEN THE VALUE IS NULL PROBLEM DESCRIPTION: I am trying to run a report that returns ItemInventory where LastReceived is NULL. I have tried in vain to accomplish this. What syntax do you use to select a blank/nu...
TROUBLESHOOTING - HOW TO USE DATE() AND DATEADD() FUNCTION IN QODBC PROBLEM DESCRIPTION: I want to write some select statements on InvoiceLine and SalesReceiptLine that return all records dated WITHIN the past 30 days relative to whatever TODAY is. I'm...
TROUBLESHOOTING - INVOICE REFNUMBER FAQ PROBLEM DESCRIPTION: I have the invoice number (RefNumber) from my table outside QuickBooks. But if a QuickBooks user creates invoices directly in QuickBooks, then QuickBooks keeps new incrementing invoices autom...
TROUBLESHOOTING - HOW TO CREATE A BLANK INVOICE IN QUICKBOOKS ONLINE USING QODBC PROBLEM DESCRIPTION: How do I create a note (or a blank line) on the invoice? If I do it the same way that I do it using QODBC for QuickBooks, I get an error : Error send...
TROUBLESHOOTING - GETTING [QODBC] NOT SUPPORTED ERROR WHEN TRYING TO MODIFY THE TXNDELETED TABLE PROBLEM DESCRIPTION: I am trying to modify the TxnDeleted table from QuickBooks in QODBC but getting the "[QODBC] Not Supported" error. SOLUTIONS: The T...
TROUBLESHOOTING - CANNOT USE ALIAS IN MS QUERY PROBLEM DESCRIPTION: I am getting the below error message when trying to SELECT data fields AS Alias, the statement runs fine otherwise. [sql syntax error] Expected lexical element not found:= Please se...
TROUBLESHOOTING - MS ACCESS CRASHES HARD ON DATE QUERY PROBLEM DESCRIPTION: I've linked all the QODBC tables into an Access 2010 DB. I have created table subset queries on the original tables and would like to do selects based on date ranges - other thi...
TROUBLESHOOTING - HOW TO GET ALT.EMAIL1,2 & CC EMAIL FIELDS FROM A THE CUSTOMER TABLE PROBLEM DESCRIPTION: How do I access the alternate email address 1 and 2 and the cc email fields? I don't see them at the customer table. SOLUTIONS: You can get CC ...
TROUBLESHOOTING - INTERNAL ERROR WHEN PROCESSING THE QBXML REQUEST PROBLEM DESCRIPTION: I have QuickBooks 2014 version & I am using QODBC's latest version. I have a problem with querying the customer table using QODBC. QODBC driver consistently gets s...
TROUBLESHOOTING: ERROR 3250 - THIS FEATURE IS NOT ENABLED OR NOT AVAILABLE IN THIS VERSION OF QUICKBOOKS. PROBLEM DESCRIPTION: I am getting the error "Error 3250 - This feature is not enabled or not available in this version of QuickBooks." while I am ...
TROUBLESHOOTING - ERROR [42S00] [QODBC] INSERT VALUE MUST BE A SIMPLE VALUE PROBLEM DESCRIPTION 1: We have a customer trying to import a credit memo from our database into QuickBooks using a vb.net program. They are getting the following error: ERROR [4...
TROUBLESHOOTING - CANNOT CREATE A NEW TABLE USING QODBC / CANNOT ADD A NEW FIELD TO THE QODBC TABLE PROBLEM DESCRIPTION: I'm trying to create a new table with VB DEMO or QODBC TEST TOOL. Can you send me a sample query to do this? SOLUTIONS: QODBC ...
PROBLEM DESCRIPTION: When inserting an invoice via a looping SQL procedure, I have invoice lines (InvoiceLine table) items that can potentially be assigned to varying classes. Using the ClassRefListID field, I class the lines accordingly and do not addre...
TROUBLESHOOTING: SP_REPORT PROFITANDLOSSBUDGETPERFORMANCE IS RETURNING NO ROWS. PROBLEM DESCRIPTION 1: I am running ProfitAndLossBudgetPerformance through MS Query & It is returning no rows. But using QuickBooks UI, I can get records from the ProfitAnd...
HOW CAN I FIND QUICKBOOKS DELETED RECORD INFORMATION FROM QODBC? PROBLEM DESCRIPTION: Is there a stored procedure that provides all deleted transactions? I.e., Voided/Deleted Transactions Detail? For Example: If an Invoice gets deleted from Quick...
HOW TO CREATE AN ITEM INVENTORY ASSEMBLY THROUGH MS ACCESS USING QODBC AN EXAMPLE OF CREATING ONE ITEMINVENTORYASSEMBLY WITH TWO LINES First, please create a new MS Access database & link ItemInventoryAssembly, and ItemInventoryAssemblyLine tables. P...
HOW TO ADD ADDITIONAL QUOTE (') IN INSERT STATEMENT USING QODBC PROBLEM DESCRIPTION: I have exported the customer list from QB, and Now I want to insert these using the insert query. But some of them fail because of the apostrophe ('). How to deal with...
PROBLEM DESCRIPTION 1: I'm working in PHP and using the QODBC Test Tool. I am trying to Insert value into the Customer table. But I am getting the below error: EXPECTED LEXICAL ELEMENT NOT FOUND: My insert statement is: Insert into Customer (...
PROBLEM DESCRIPTION: How can I add blank InvoiceLine in QuickBooks using QODBC? SOLUTION: There are empty Invoice line records in QuickBooks, and QODBC can view them too, but you may not be able to create an empty InvoiceLine in QODBC. You cannot cr...
PROBLEM DESCRIPTION: I am attempting to apply payments to invoices. I have also determined the TxnID of the Invoice I want to apply a payment to and used it in the SQL code specified on How to apply a payment to an invoice [http://support.flexquarters.co...
TROUBLESHOOTING: QODBC HANGS WHEN TRYING TO EXECUTE QUERY OR VIEW TABLES PROBLEM DESCRIPTION: When executing a simple query like: SELECT * FROM Invoice Application and QuickBooks will both get hang, and it Does Not Respond. Then After, the applic...
TROUBLESHOOTING: UPDATING JOURNALENTRY RECORDS VIA QODBC SHOWS ERROR, TRANSACTION NOT IN BALANCE. INSTRUCTION: Under QuickBooks, when saving a Journal Entry record, the Debit total should match the Credit total. Even thou you are not changing the amo...
TROUBLESHOOTING: EXPECTED LEXICAL ELEMENT NOT FOUND PROBLEM DESCRIPTION  When trying to execute a query statement, I get the error message " EXPECTED LEXICAL ELEMENT NOT FOUND." SOLUTIONS It seems to be the issue in the SQL Statement. Please check al...
ClearedStatus Table problem TROUBLESHOOTING: COULD NOT MARK TRANSACTION AS CLEARED IN CLEAREDSTATUS TABLE PROBLEM DESCRIPTION 1: Try to Mark a transfer-type transaction as cleared in the ClearedStatus table via the statement: INSERT INTO ClearedSt...
The item Sites table contains no data TROUBLESHOOTING: COULD NOT FIND DATA IN ITEMSITES TABLE QUICKBOOKS PROBLEM DESCRIPTION: When referring to Intuit SDK OSR, a subject named Item Site can be found. It stores the quantities for each item by the site...
QODBC can't mark fund transfers as cleared TROUBLESHOOTING: CAN'T MARK FUND TRANSFER AS "CLEARED" PROBLEM DESCRIPTION: Want to use QODBC to mark a fund transfer transaction as "Cleared" but don't know how to do it? Try to modify the field Transactio...
Why Special Character Turns into
TROUBLESHOOTING: QUICKBOOKS ERROR: TRANSACTION NOT IN BALANCE PROBLEM DESCRIPTION The QODBC report Error: 3180-There was an error when saving a General Journal transaction. QuickBooks error message: Transaction not in balance. When trying to do some...
Problem With Date Format TROUBLESHOOTING: PROBLEMS WITH THE DATE FORMAT PROBLEM DESCRIPTION 1: When you want to filter records by date, input a query like the one below: select top 1 * from DepositLine WHERE TimeCreated < '2011-06-06' ORDER BY TimeC...
TROUBLESHOOTING: "ERROR PARSING COMPLETE XML RETURN STRING (8)" IN SOME INSERT STATEMENTS PROBLEM DESCRIPTION Someone might get an error "[QODBC]Error parsing complete XML return string. " when running some inserts queries such as PurchaseOrders, Invoic...
TWO EXAMPLES OF WRITING DEPOSITS INSTRUCTIONS NOTE: Deposit inserts are done only to the DEPOSITLINE table. The Deposit table is a header table that cannot be inserted without cached DepositLine transactions. TROUBLESHOOTING NOTE If you encounter ...
TROUBLESHOOTING: HOW TO CREATE MULTIPLE INVOICES WITH MULTIPLE LINE ITEMS USING ACCESS 2003 AND QODBC PROBLEM DESCRIPTION 1 I use MS Access 2003 to generate calculated totals for invoices I need to post into QB. Using an Append Query, I have been...
TROUBLESHOOTING: HOW TO QUERY INVOICE LINES WHERE TXNDATE=YESTERDAY USING CURDATE PROBLEM DESCRIPTION 1 I am using Excel to pull info from the InvoiceLine QB table. This triggers the 'Microsoft Query' program, which looks like an access design grid. ...