Problems with identity fields in MS Access when using SQL Server's transactional replication with queued updates

Scenario: Transactional replication among SQL Server 2005 publisher and 2000 or 2005 subscribers with queued updates. Articles/Tables with a primary key defined by an identity field throw errors in MS Access when inserted at the subscriber (SQL Server 2005 Standard SP1 & MS Access 2003 SP2).

Problem description: @@identity is changed by replication triggers deployed to subscribers.

Side effects: Since MS Access 2003 uses @@identity (see BOL for more information) to retrieve the last inserted record for tables indexed by identity fields, every time you try to insert a new record on a subscriber of transactional replication with queued updates on a table with an identity primary key, the following error message appears: "The data was added to the database but the data won't be displayed in the form because it doesn't satisfy the criteria in the underlying record source."

Good solution (but out of our hands): MS Access 2003 should use SCOPE_IDENTITY() (see BOL) instead of @@identity to retrieve the record that it has just inserted. Since @@identity is not scope-aware, if there are triggers in the offending table that do other inserts on other tables also managed with identities, @@identity would return the wrong value to client application (MS Access). SCOPE_IDENTITY() solves this problem by returning the last inserted identity within the same scope. Since this behavior is hard-coded inside MS Access there is very little we can do with user defined code (VB) to change this. Until a MS Office Service Pack solves this issue, the only things that programmers can do are workarounds.

Workarounds:

a. Do not use identity as PK on tables. Sometimes identities are avoidable, some other times not. If you have read up to this point, you probably need them and not using them is not an option for you.

b. Create triggers on tables experiencing this problem to discard inserts done by MS Access and do the real inserts through VB code inside a form. First of all, you need a way for the trigger to know when the insertion is done by MS Access directly and when the insertion comes through your user defined code. For this purpose you can use SET CONTEXT_INFO as in the following VB code (self documented):

Private ResyncCommandORI As String
Private Sub Form_BeforeUpdate(Cancel As Integer)
    Dim strSQL As String
    Dim rst As ADODB.Recordset
    Dim NewIdentity As Long

    If (Me.NewRecord) Then ' We are inserting
        ' Set this context variable so that the trigger knows the insertion
        ' is done via VB. If this context variable is not set, the trigger
        ' will delete the inserted record transparently.
        CurrentProject.Connection.Execute "SET CONTEXT_INFO 0x1"

        strSQL = "INSERT INTO TestTable (TestField) " & _
                 "VALUES ('" & TestField.Value & "'); SELECT SCOPE_IDENTITY() AS Id"
        Set rst = CurrentProject.Connection.Execute(strSQL)

        ' Once the insertion is done, we delete the context info
        CurrentProject.Connection.Execute "SET CONTEXT_INFO 0x0"

        ' And now we retrieve the fresh new identity
        ' Skip first recordset, closed and without data (insert statement)
        Set rst = rst.NextRecordset
        If (rst.State <> adStateClosed) Then
            If Not rst.EOF Then
                NewIdentity = rst.Fields(0)
            End If
        End If

        ' In order to MS Access copes with the insertion that he thinks
        ' he will be able to do without problems, we need to change
        ' ResyncCommand so that it does not use ? (@@identity) and
        ' use the NewIdentity we have just read.
        ResyncCommandORI = Me.ResyncCommand
        Me.ResyncCommand = Replace(Me.ResyncCommand, "?", Trim(Str(NewIdentity)))
        ' ResyncCommand is ready to be used by MS Access so that it
        ' can read the new record.
    End If
End Sub
Private Sub Form_AfterInsert()
    ' We just leave ResyncCommand as it was originally (with the ?)
    ' so that updates will work also.
    Me.ResyncCommand = ResyncCommandORI
End Sub

Besides this VB user code in every form dealing with tables with identities, we need to create the following trigger on those tables:

CREATE TRIGGER [dbo].[insert_TestTable_trigger] on [dbo].[TestTable]
   AFTER INSERT
   NOT FOR REPLICATION
AS
BEGIN
    DECLARE @v varbinary(1024) -- 128bytes

    -- added to prevent extra result sets from interfering with SELECT statements.
    SET NOCOUNT ON
    SELECT @v = context_info
      FROM master.dbo.sysprocesses
      WHERE spid = @@SPID;

    IF @v <> 0x1 BEGIN
      -- Do not complete the insertion. If we do a ROLLBACK MS Access
      -- will know and will raise an error. We just do a DELETE of the
      -- record just inserted and nothing wrong happens.
      DELETE dbo.TableTest
        FROM dbo.TableTest
        INNER JOIN inserted ON dbo.TableTest.Id = inserted.Id
    END
END

Using this code in every form and in every table that uses identities is a hard work and a nasty workaround. You will agree that this is not an elegant nor scalable solution and it is not a good idea to mix this complex logic between client (MS Access) and server (SQL Server). If you are not satisfied with this workaround, please keep on reading...

c. Modify SQL Server 2005 core system stored procedures to leave @@identity unchanged on sync triggers deployed to subscribers. SQL Server has two kind of transactional replication modes: immediate and queued. When in immediate mode, every subscriber connects to publisher in every insert/update/delete using a two-phase protocol (2PC) so that the statement is only 'approved' for the subscriber when it has been committed previously on the publisher. When transactional replication drops into queued mode (because of a network connection loss or intentionally because you want it that way), the changes are queued and they are tried to be applied on a scheduled basis, hence a little (but selectable) latency occurs. No matter which mode you use, the identities are generated (by default) at the side doing the insertion and that is the reason for them to need a reserved space for assigned identities (identity ranges) so that there are no collisions (conflicts). This is another changed behavior since SQL Server 2000. When using SQL Server 2000 and transactional replication with immediate updates, identities were generated always at the publisher even though the insertions were done on a subscriber. In 2005, identity ranges are used always (by default), even when using immediate updates. In order to implement replication SQL Server deploys a series of stored procedures and triggers to subscribers. The names for those triggers and stored procedures are like:

trg_MSsync_del_TestTable
trg_MSsync_ins_TestTable
trg_MSsync_upd_TestTable
sp_MSdel_dboTestTable
sp_MSins_dboTestTable
sp_MSupd_dboTestTable

It seems that, internally, the *ins* trigger or stored procedure involved during an insertion do another insert into a table with identities, thus, changing the value of @@identity and making it unusable for MS Access afterwards. As I said before, it's MS Access fault but, as long as we cannot change MS Access behavior, we will modify SQL Server core system stored procedures that create the scripts that create the former triggers so that @@identity is preserved when they are executed. Our work is based on a trick widely used by programmers to circumvent MS Access bad behavior (thanks Sylvain Lafontaine for your information on my post at news microsoft.public.access.adp.sqlserver). This trick is commented in this post and basically the idea is saving @@identity on a local variable right at the beginning of the trigger and then restore it before leaving out such as:

CREATE TRIGGER [dbo].[trg_MSsync_ins_TestTable] on [dbo].[TestTable]
AFTER INSERT
NOT FOR REPLICATION
AS BEGIN
 DECLARE @identity int, @strsql varchar(128)
 SET @identity=@@identity
   -- Main block of the trigger here
 SET @strsql='select identity (int, ' + cast(@identity as varchar(10)) + ', 1) as id into #tmp'
 EXECUTE(@strsql)
END

This is the trick that people have been using on their own triggers. Making this goes against the definition of @@identity, that must always return the last inserted identity in any table for the current connection, but as I said, this is a trick and it does the job (MS Access does the inserts without any error despite its own bug). The main challenge for us now is that the offending triggers are not user-generated, but system-generated. Where do I need to 'touch' to modify SQL Server's behavior and include those 4 lines of code into their own generated triggers? That's the question. And here is the answer: The Resource Database (or RDB). I first read about it here and here are some official words from Microsoft:

[...]The Resource database is a read-only database that contains all the system objects that are included with SQL Server 2005. SQL Server system objects, such as sys.objects, are physically persisted in the Resource database, but they logically appear in the sys schema of every database. The Resource database does not contain user data or user metadata.[...]

First of all, I must say that the following procedure is completely unsupported by Microsoft. As stated in the former URL:

[...]The Resource database should only be modified by or at the direction of a Microsoft Customer Support Services (CSS) specialist. The only supported user action is to move the Resource database to the same location as the master database. For more information, see Moving System Databases.[...]

The (unsupported) steps to make things work:

1. Do a backup. As you have probably guessed, the first thing you should do before making any unsupported change is a backup. Do a backup of your databases and (more important) do a backup of your original files mssqlsystemresource.mdf and mssqlsystemresource.ldf. They must be in the same directory as your master database files, usually %PROGRAMFILES%\Microsoft SQL Server\MSSQL.1\MSSQL\Data. Since that database is read only, there should be no problem if you try to do the backup with mssqlserver service running, but just for security, stop the service and do the backup afterwards (from a cmd line):

net stop mssqlserver
copy mssqlsystemresource.mdf mssqlsystemresource.mdf.original
copy mssqlsystemresource.ldf mssqlsystemresource.ldf.original

The backup could be as simple as that. You can try this whole procedure on a virtualised / testing environment for improved security.

2. Start SQL Server in single-user mode. In order to modify the resource database, we need to start SQL Server in single-user mode (/m parameter). We need to stop SQL Server Agent and any other service that is SQL Server related (i.e. reporting services, analysis services, IIS if apply) so that the agent (or other) could use the unique allowed connection.

net stop sqlserveragent
net stop (any other service related)
net start mssqlserver /m

3. Use sqlcmd to make the changes. This is the core of all this article. You need now to connect to the server and execute the ALTER PROCEDURE to modify/patch the system stored procedures sys.sp_MSscript_sync_del_trig, sys.sp_MSscript_sync_ins_trig and sys.sp_MSscript_sync_upd_trig. You can download the full source code for patching SQL Server 2005 SP1 here: sp_MSscript_sync_xxx_trig_for_SQL_Server_2005_SP1_9.00.2047.00.zip. Unzip the files and run the following commands:

sqlcmd -U sa -P yoursapass -A -d master -i sp_MSscript_sync_del_trig.sql
sqlcmd -U sa -P yoursapass -A -d master -i sp_MSscript_sync_ins_trig.sql
sqlcmd -U sa -P yoursapass -A -d master -i sp_MSscript_sync_upd_trig.sql

If everything has gone nicely, you should be again on the command prompt. Now you just need to stop the service and start it again as usual (not in single-user mode).

net stop mssqlserver
net start mssqlserver
net start (anything previously stopped)

Congratulations! Now the system stored procedures sys.sp_MSscript_sync_xxx_trig contain our custom pieces of code. You won't need to follow this procedure for every server involved in replication as long as they are all SQL Server 2005 with the same service pack applied. You just need to copy the new (updated) mssqlsystemresource.mdf and mssqlsystemresource.ldf files to every server/instance (stop service first). Furthermore, the files are the same for 32bit versions and x64 versions of SQL Server 2005 (you can check it using any crc32, md5 file checker as I did). It is important that every subscriber have this patched version of resource database files since the patched stored procedure is called at the subscribers. When a publication is marked for reinitailization at the publisher and the initial snapshot is run, the scripts sent to the subscribers do not include the offending triggers, they are generated and applied directly at the subscribers. The initial snapshot publication files include:

• bulk copy files with the contents of the tables (.bcp)
• DDL commands to recreate the tables, sp_MSxxx_ stored procedures and schema (.sch)
• Constraints dealing with identity ranges repl_identity_range_tran_xxx (.dri)
• Indexes and primary key constraints (.idx)
• Pre-script to drop foreign keys pointing to the table we are publishing and need to drop and recreate (.pre)
• User defined triggers (.trg)

As you can see, there is no trace about the triggers that appear finally on subscribers. That is because they are generated and applied locally on the subscribers and that is the reason because every subscriber must have the patched files.

4. The final step. Reinit every publication that you had already running so that the new patched triggers are generated and applied on every subscriber. After that, MS Access should work nicely without any problem dealing with identities.

Some final words (disclaimer): As I already stated, this patching procedure is completely unsupported by Microsoft. I shall not be liable for any damage (including, without limitation, damage for loss of business or loss of profits) arising from the use of, or inability to use, this document or any material contained in it, or from any action or decision taken as a result of using this document or any such material. I tested this procedure in my environment, solved the problem and worked as expected. I encourage you to do your own test in virtualised/testing environments first. Before applying any new SQL Server Service Pack that might arise in the future, I suggest to stop the server, restore the original files we copied at step 1 of the procedure, and then follow the instructions of the SP. After the SP is applied (also on subscribers), you can reinit the publications and see if the MS Access error appears again. If so, I suppose you could re-patch the new resource database that the SP might have set in place. In my scenario SQL Server 2005 was Spanish localized and a SELECT SERVERPROPERTY('ResourceVersion'), SERVERPROPERTY('ResourceLastUpdateDatetime') returned: Original RDB: 9.00.2047 2006-04-14 04:17:29.497 Patched RDB: 9.00.2047 2006-06-15 19:08:00.210

Note for SQL Server 2000 subscribers: Solving this issue for SQL Server 2000 subscribers is much more easier. We need to modify the same dbo.sp_MSscript_sync_xxx_trig system stored procedures, located at the master database. No need to deal with Resource Databases, needing to stop and start the server in single-user mode, etc... just a simple ALTER PROCEDURE and a reinitialization of the publications. Well, there are some additional steps to be taken in order to still have those stored procedures marked as system objects. If you just do an ALTER PROCEDURE, they will no longer be marked as system objects. The complete scripts for patching SQL Server 2000 SP4 are available here.

Links: You receive an error message when you add a record to a subform in an Access 2002 Project Moving System Databases (SQL Server 2005) http://msdn2.microsoft.com/en-us/library/ms187342.aspx BOL chapter about SCOPE_IDENTITY() function

My feedback to Microsoft on this issue: http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=125475

Keywords: Identity, @@IDENTITY, transactional replication with queued updates, SQL Server 2005, Microsoft, MS Access, SCOPE_IDENTITY(), Reserved Resource Database, sp_MSscript_sync_ins_trig, bug, error, workaround, resolution

NOTE: Just as sp_MSscript_sync_ins_trig purpose is scripting the trg_MSsync_ins_TableName trigger on subscribers, trg_MSsync_upd_TableName is scripted by sp_MSscript_sync_upd_trig, trg_MSsync_upd_TableName is created by sp_MSscript_sync_del_trig and the all three need to be patched the same way. All of them make inserts into MSreplication_queue table, which has an identity column called orderkey. You can read about MSreplication_queue table on SQL Server 2005 Books Online. Any command that should be delivered to publisher is inserted into this table, thus changing the value of @@identity variable. Now I have the three stored procedures (sp_MSscript_sync_ins_trig, sp_MSscript_sync_upd_trig and sp_MSscript_sync_del_trig) patched and everything is running smoothly.