Member Login

Username
Password
Forget Password
New Sign Up
Search Forum

Buy Support
Incidents

If you can't find your answer in the FREE PUBLIC QDeveloper Forum, require URGENT Priority Support, or you need to send us private or confidential information:

Click Here
If you can't login and post questions or you are having trouble viewing forum posts:
Click Here
Callback
Support

If you live in USA, UK, Canada, Australia or New Zealand, you can leave us details on your question and request us to call you back and discuss them with you personally  (charges apply).

Click Here
 
Buy Support
Incidents
If you can't find your answer in the FREE PUBLIC QDeveloper Forum, require URGENT Priority Support, or you need to send us private or confidential information:
Click Here

Forum : Openquery / JournalEntryDebitLine / SP_LASTINSERTIDSearch Forum

Forum Home > QODBC - ODBC Driver for QuickBooks > QODBC Frequently Asked Questions

 New Topic 
 
 Post Reply 
[1]  
 Openquery / JournalEntryDebitLine / SP_LASTINSERTID 
 Author   Message 
  Louis 
  
 Group: Members 
 Posts: 6 
 Joined: 2006-07-05 
 Profile
 Posted : 2006-08-04 05:22:53

Hi

Why would I wan't to use update openquery(myslinkserver, Select * FROM Customer....) SET .... instead of

UPDATE myslinkserver...Customer SET ..... ?

 I also have a few questions, how can I retreive LastInsertId with a linkserver ?

SELECT * FROM openquery(my,'SP_LASTINSERTID Customer')

always returns, (on the canadian version (enterprise 2005) and with the us version of quickbooks (2006 enterprise))

 (sql server 2000)

SqlDumpExceptionHandler: Process 55 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.

Connection Broken

is there another way to run stored procudre via a linkserver ?

 

Also, I'm trying to add journal entries, it seem to work randomly,  every now and then, I get transaction not in balance, but I'm pretty sure it is, it looks like once a while, after I flush the journal entry cache to memory, it keeps some remaining amount into the cache, screwing up the next entry. I say this because, if I run the same transaction again, it works.

That's how I insert lines

   INSERT INTO qodbcmy...JournalEntryDebitLine
     (
    JournalDebitLineEntityRefListID,
    JournalDebitLineClassRefListID,
    TxnDate,
    JournalDebitLineMemo,
    JournalDebitLineAmount,
    JournalDebitLineAccountRefListID,
    FQSaveToCache
    )
   VALUES (
    @CustomerJobRefListID,
    @LineClassRefListID, --class is null
    @TxnDate,
    @Memo,
    @LineAmount,
    @DebitAccountRefListID,
    @FQSaveToCache  --1 to keep in memory, 0 to flush it down, 0 at the end
    )

same with the creditline but FQSaveToCache  is always 0 on the credit side

I tryed running FQSAVETOCACHEROLLBACK off, but I can't find a way to run it via MS SQL server

Do you have any tip on how should I trace this error? btw, I always start with the creditlines...if it matters

Here is part of my msg log

2006-08-03 09:06:16 QODBC Ver:  6.00.00.176 *************************************************************************************
IsAService: False
SQL Statement: INSERT INTO JournalEntryDebitLine ( TxnDate,JournalDebitLineAccountRefListID,JournalDebitLineAmount,JournalDebitLineMemo,JournalDebitLineEntityRefListID,
JournalDebitLineClassRefListID,FQSaveToCache) VALUES(?,?,?,?,?,?,?)
3180 - There was an error when saving a General Journal transaction.  QuickBooks error message: Transaction not in balance.
Input XML:
<?xml version="1.0" encoding="ISO-8859-1"?>
<?qbxml version="5.0"?>
<QBXML>
    <QBXMLMsgsRq onError = "continueOnError" responseData = "includeAll">
        <JournalEntryAddRq requestID = "1">
            <JournalEntryAdd defMacro = "TxnID:ABFAAD03-5D45-494E-97BC-63ACDC352390">
                <TxnDate>2006-07-31</TxnDate>
                <JournalDebitLine>
                    <AccountRef>
                        <ListID>F040000-1151682621</ListID>
                    </AccountRef>
                    <Amount>91.18</Amount>
                    <Memo>(Auto Provision #4648 ) Syndicat des Co-propriétés ,Provision SubTotal = 15.5004 Ref: 13062 Tag:Juillet-r2 #4650#</Memo>
                    <EntityRef>
                        <ListID>8B70000-1151704237</ListID>
                    </EntityRef>
                    <ClassRef>
                        <ListID>4D0000-1151699760</ListID>
                    </ClassRef>
                </JournalDebitLine>
            </JournalEntryAdd>
        </JournalEntryAddRq>
    </QBXMLMsgsRq>
</QBXML>
Output XML:
<?xml version="1.0" ?>
<QBXML>
    <QBXMLMsgsRs>
        <JournalEntryAddRs requestID="1" statusCode="3180" statusSeverity="Error" statusMessage="There was an error when saving a General Journal transaction.  QuickBooks error message: Transaction n
            ot in balance." />
    </QBXMLMsgsRs>
</QBXML>

 

Thanks a lo

 

  Top 
  Tom 
  6c3c1_sdk-qodbc.gif
 Group: Administrator 
 Posts: 5510 
 Joined: 2006-02-17 
 Profile
 Posted : 2006-08-04 08:50:41

Sorry, the QODBC SP_TABLES and SP_LASTINSERTID stored procedures cannot be currently called from SQL Server 2000 or 2005 linked tables.

To locate the last invoice for the customer you can do this instead:

SELECT  TOP 1 TxnID FROM Invoice WHERE CustomerRefListID = '20000-1148055137' ORDER BY TimeModified DESC

The use of

update openquery(myslinkserver, Select * FROM Customer....) SET ....

instead of

UPDATE myslinkserver...Customer SET .....

is up to you.

Try turning on the trace log to see what is being posted at the time of the error as per: How do I turn on the trace log? 

 

  Top 
 New Topic 
 
 Post Reply 
[1]  

Jump to