Post reply

Issue with ODBC and Trigger

  • April 10, 2008 at 2:53 pm

    #205687

    We have what I thought was a simple trigger:

    ALTER TRIGGER [dbo].[trDB_LTXDOCU_I_SyncPendingAction] ON [dbo].[DB_LTXDOCU]

    FOR INSERT

    AS

    Insert into SyncPendingAction(TableName, RecordPK, RecordAction, JournalTimestamp)

    Select

    'LTXDOCU',

    I.P_K,

    'I',

    I.JRNL_TIMESTAMP

    from

    Inserted I

    When we execute this Insert Statment through ODBC we get an error:

    ERROR:this statement failed:INSERT INTO DB_LTXDOCU VALUES (+0000015236939020,+0000015236939020,+0000015236939020,+0000015236939020,+0000015236939017,+0000015236939017,0012,'082007655',' ','2008-04-07 14:53:51','772220','LTTD3000') errcode -1

    Message from ODBC:[Microsoft][ODBC SQL Server Driver]Invalid cursor state.

    I am thinking this has something to do with the fact we are doing this through ODBC. As well, this is running on SQL Server 2005 64 bit on a 64 bit version of Windows 2003.

    Any help would be greatly appreciated.

  • April 10, 2008 at 3:23 pm

    #801209

    Try putting:

    Set NoCount On

    after the AS in the trigger. The ODBC driver may be receiving the "N rows affected message" from the trigger when it is not expecting it. I know I have seen similar issues when you get a "Null Values eliminated message" when using an aggregate function which is eliminated by Set ANSI_Warnings OFF.

    Jack Corbett
    Consultant - Straight Path Solutions
    Check out these links on how to get faster and more accurate answers:
    Forum Etiquette: How to post data/code on a forum to get the best help
    Need an Answer? Actually, No ... You Need a Question

  • April 11, 2008 at 8:58 am

    #801545

    Jack....you are awesome!!! Thank you so much.

    I think the issue is resolved based on my testing. I have to wait until April 16 for the client to get back and assist with the testing but it looks like we are headed in the right direction. I'm glad it was something so simple. I'll post back if it doesn't correct our issue when UAT starts up again.

    Thanks again. 🙂

    Jessica

  • April 11, 2008 at 10:07 am

    #801608

    I generally use the following statements after the 'AS' statement:

    [font="Courier New"] SET NOCOUNT ON

    SET XACT_ABORT ON

    SET ARITHABORT ON [/font]

    to avoid any errors or unexpected results in applications updating the same table.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply