none
Are ODBC apis synchronous by default ? RRS feed

  • Question

  •  

    Repro Steps:

    My application  uses SQL Server 2005 Sp1 as the database. We are having an issue with SQL Server 2005 Sp1 in a replicated scenario. Setup:

    · Publisher W2k3 Sp1 - running SQL 2005 SP1

    · Subscriber W2k3 x64 - running SQL 2005 SP1

    Transactional Replication with Updateable Subscriptions (Immediate Updating) has been configured using the Wizards provided by SQL 2005 Mgmt Studio between these servers. Our app is installed on W2k3 Sp1 servers. Our app when pointing to the subscriber, creates/updates/delete rows in 3 tables (A,B and C). One specific transaction consists of the following operations in sequence:

    1. Update 1 row in table A (using SQLExecute)

    2. Delete 1 row in table B and Insert 1 new row in table B (using SQLExecute) 

    3. Delete 3 rows in table C and Insert 3 new rows in table C. (using SQLExecute)

    4. Commit (using SQLEndTran)

    5. After a few milliseconds (50-100), query rows in table C. (using SQLExecute)

    In step 5, our query is expected to return only the 3 new rows that were added to table C in Step3. However, our query returns 6 rows (i.e. the 3 old rows and the 3 new rows). If the same query is executed in SQL query analyzer, after seeing this error, the 3 new rows are returned (as expected). This leads us to believe that this is a timing issue.

    Some more info:

    a) The problem is intermittent - it can only be reproduced about 20% of the time.

    b) All the 5 steps above are done in a single thread.

    c) We are using ODBC apis to access the database.

    d) This problem is not seen when using SQL 2005 without replication or when using SQL 2000 with or without replication. Its only seen when using SQL 2005 with replication.

     

    I am wondering if in a replicated environment whether ODBC apis are synchronous i.e. when SQLExecute or SQLEndTran(commit) return then, the data is already committed ?

    Any help is greatly appreciated.

     

    Thanks

    Tuesday, August 15, 2006 2:30 PM