Hi - I'm using my QODBC driver to extact all the tables to SQL Server so that I can run reports offline across the company officers.  The major problem is that I don't have the stored procedure syntax used by QuickBooks 2004 Professional Services Edition.  This is a huge limitation, as right now I can get my account balances for today, but if I want to know them for 3 months ago, I have no idea of how to get this information.  Is it possible to get the Stored Procedures, or to determine where historical information for GL accounts is stored?  I want to be able to create a report that gives me my P&L for any date from my SQL Server copy.

QODBC has a number of stored procedures to assist you in programming your applications. These include: SP_COLUMNS tablename - returns a recordset of the columns in the specified table.
 
SP_PRIMARYKEYS tablename - returns a recordset of the primary key segments in the specified table.
 
SP_SPECIALCOLUMNS tablename [ROWVER]|[BEST_ROWID] - returns a recordset of the Best ID column or RowVer column for the specified table.
 
SP_TABLES - returns a recordset of all available tables.
 
SP_VOID - Use just like the SQL DELETE command on Transactions to void the record instead of deleting it. Use the DELETE command syntax, substituting SP_VOID for the word DELETE.
 
SP_LASTINSERTID tablename - returns a recordset of 1 row and 1 column that is the ListID/TxnID of the last record inserted on that table in the current connection. The value returned is the ListID or TxnID of the last inserted record for that table on the current connection. Its value is NULL if a record has not been inserted into the requested table on the current connection.
 
SP_REPORT - Runs the internal QuickBooks reports
 

Sorry, the QODBC tables are header and transaction lists, and don't contain historical data other than that within the data itself. While it's possible to reconstruct certain balances from transaction dates etc., I have to say from the start that what you want here is to use the QuickBooks Reporting Engine.

The QuickBooks Reporting Engine is accessed via the stored procedure sp_report.

See: How do I use the QuickBooks Reporting Engine with QODBC? I've heard something about sp_report ? for more information.

To use sp_report from a MS SQL Server Linked Server, just use the standard OPENQUERY syntax. An example would look like:

SELECT * FROM OPENQUERY(QODBC, 'sp_report CheckDetail show TxnType_Title, RefNumber_Title, Date_Title, Name_Title, Item_Title, Account_Title, PaidAmount_Title, OriginalAmount_Title, TxnType, RefNumber, Date, Name, Item, Account, PaidAmount, OriginalAmount parameters DateMacro = ''ThisMonthToDate''')

Use the QuickBooks Visual Field Explorer to view the report you require by clicking on the following link:
http://www.qodbc.com.au/helpdesk.asp?target=sales_by_item_detail&sp_report_name_id=26