MS SQL Server ODBC - SQL 2005, ODBC link table, and replication

Author

SQL 2005, ODBC link table, and replication

TCorp

2006-02-13, 8:23 pm

I am experiencing some strange behaviour when I try to insert rows into my
SQL Server 2005 DB through ODBC.

I use MS-ACCESS ODBC linked tables to insert rows into my SQL Server 2005
db. When I insert a row into a linked table (by typing data into it) the
row "disappears" and is replaced by a previous row. Refreshing or resorting
the table seems to fix this and the data I inserted appears normally. The
behaviour is exactly as described in this KB article:

http://support.microsoft.com/kb/251289

The KB recommends installing the latest updates. But I believe have all the
latest updates on my desktop:
Windows XP SP2
MS-Access 2003 (version 11.6566.6568)
Jet 4 (msjet40.dll version 4.0.8618.0)
MDAC 2.8 (msado15.dll version 2.81.11117.0)

and on my db server
SQL Server 2005 (version 9.0.1399 Standard Ed.)
Windows 2003 Server Standard X64 Ed. SP1

Replication aspect:
It is a SQL Server 2005 compatibility database (used to be a SQL2000 db)
with several MSDE Pull subscribers (Merge Replication). None of the MSDE
pull subscribers experience this problem.
I have a SQL Express push subscriber that experiences this problem when
replicating with the SQL publisher. Strangely, when we delete the push
subscription the problem disappears. We setup replication with SQL Express
again and the problem came back.

Any suggestions on what is the problem would be appreciated.
Ted.

TCorp

2006-03-07, 1:23 pm

Here's an update about this problem for anyone who needs to know.

I was experiencing some strange behaviour immediately after our upgrade to
SQL SERVER 2005. When I insert a new record into an MS-ACCESS Table, all
the fields (which should be blank on a new record) appear to contain data
from a previous record in that table. Refreshing the dataset 'fixes' this
so that you see the blank record you added.

I started a support incident with Microsoft concerning this issue and after
a few days of demos and investigation, it looks like it is probably a bug
with how SQL Server and ACCESS communicate. They can reproduce it in their
labs, but as of yet do not have a solution for this problem.

Here are the conditions that seem to contribute to this problem:

a) SQL Server 2005 database on a Windows 2003 Server (both 64 & 32 bit)
b) SQL Server Merge Replication
c) Several pull subscriptions

These items seem to assuage the problem somewhat:

a) Reduce the number of subscribers (conversely increasing the number
of subscribers may exacerbate this issue)
b) Increasing the synchronization interval (reduce synchronization
frequency)

The extent of the problem is limited to any replicated table accessed
through an ACCESS link table linked to a SQL2K5 database (including a SQL
Server Express subscriber). So although the number of subscriptions seems
to make a difference, the main problem is with an ACCESS linked table
connecting to SQL2K5 replicated database. It might not be just an ODBC
problem as an ACCESS Project (.adp) still exhibits this behaviour.

As a workaround, I am considering creating a SQL Server 2000 subscriber for
my users experiencing this problem to connect to.

"TCorp" <tcorpus@hotmail.com> wrote in message
news:OCJxsOPMGHA.552@TK2MSFTNGP10.phx.gbl...
>I am experiencing some strange behaviour when I try to insert rows into my
>SQL Server 2005 DB through ODBC.
>
> I use MS-ACCESS ODBC linked tables to insert rows into my SQL Server 2005
> db. When I insert a row into a linked table (by typing data into it) the
> row "disappears" and is replaced by a previous row. Refreshing or
> resorting the table seems to fix this and the data I inserted appears
> normally. The behaviour is exactly as described in this KB article:
>
> http://support.microsoft.com/kb/251289
>
> The KB recommends installing the latest updates. But I believe have all
> the latest updates on my desktop:
> Windows XP SP2
> MS-Access 2003 (version 11.6566.6568)
> Jet 4 (msjet40.dll version 4.0.8618.0)
> MDAC 2.8 (msado15.dll version 2.81.11117.0)
>
> and on my db server
> SQL Server 2005 (version 9.0.1399 Standard Ed.)
> Windows 2003 Server Standard X64 Ed. SP1
>
> Replication aspect:
> It is a SQL Server 2005 compatibility database (used to be a SQL2000 db)
> with several MSDE Pull subscribers (Merge Replication). None of the MSDE
> pull subscribers experience this problem.
> I have a SQL Express push subscriber that experiences this problem when
> replicating with the SQL publisher. Strangely, when we delete the push
> subscription the problem disappears. We setup replication with SQL
> Express again and the problem came back.
>
> Any suggestions on what is the problem would be appreciated.
> Ted.
>

Sponsored Links