We have 2 SQL Servers that update each other using Merge Replication. We then connect to the tables via an ODBC link within Access and this worked without any problems until we upgraded our SQL Server from 2000 to 2008 R2.
Since the upgrade we are having problems within Access. When we come to add a new record, the record returned is different to the one we added. After much research we discovered its down to the triggers within SQL Server (from versions 2005 onwards) where the trigger updates the global variable @@IDENTITY. We've also found out that MS Access uses this variable to return the record that was supposedly last added but since it gets altered as part of a merge trigger it makes Access problematic.
With this being a very big issue with MS Access and SQL Server Merge Replication triggers I can assume that others would have managed to work around this and come up with a solution.
can anyone help?
Since @@IDENTITY returns the last identity values generated in current session, if there are some triggers in any tables manipulated in current session, we will get unexpected value. In order to get the required value, please use SCOPE_IDENTITY. This function will return value inserted only within the current scope. For more information about SCOPE_IDENTITY, you could refer to this link: http://msdn.microsoft.com/en-us/library/ms190315.aspx
If anything is unclear, please let me know.
Many thanks for this Tom,
have read through the usage of SCOPE_IDENTITY and its clear on how it works. the problem, however, is that the triggers are created automatically by SQL Server 2008 when merge replication is set up so i'm unsure of how to modify these triggers to return SCOPE_IDENTITY and not @@IDENTITY.
Also, @@IDENTITY is returned internally within Access and again i'm unsure how to change Access to fetch SCOPE_IDENTITY instead of @@IDENTITY when adding data directly to bound forms.
In order to modify a trigger, please refer to this: http://msdn.microsoft.com/en-us/library/ms176072.aspx
I understand the procedures to modify a trigger. what I do need to know is how it can resolve my problem.
Access fetches @@IDENTITY to show the record that was added within a bound form, the replication triggers modifies the @@IDENTITY variable, thus altering the record that is returned (showing a different record to the one added).
what i need to know is do i need to modify the triggers so that @@IDENTITY returns the correct record that was added
This is bug in Access and SQL comunication. Access take identity of new record from @@IDENTITY and when you finish entering record it reload data based on value from @@IDENTITY value from SQL. In SQL 200 inserted merge trigger and Acces usualy work ok. From SQL 2005 merge trigger have some part in which data are entered in some merge replication table which have identity to and change value of @IDDENTITY form that of newly entered rcord from Access.
One solution is to chanege all merege insert trigger to save @IDDENTITY on begining of it in variable and at the end of trigger insert dumy record in #temp table as identity column with starting value of variable previosly saved.
This solution I found somewhere the net when before week I was affected with this problem too. I was moving database from SQL 200 to SQL 2008 and then I found this problem with identity in Access. I suspect replication because when I was removing one of subscription all start to work well but after recreating it erased again.
I use this for solving problem (takem from somewhere on net).
at the begining of merge insert trigger
DECLARE @identity int
DECLARE @strsql varchar(128)
and at the end of merge insert trigger
set @strsql='select identity(int,'+CAST(@identity as varchar(15)) +',1) as id into #temp'
last code should be placed on the place of /*insert end on this place */ in merge replication code
if @@error <> 0
/*insert end on this place */
But I'm searching for a way to do that automaticly for all existing merge trigger on publication and on all existing merge trigger on existing and future subscriptions.
I have found this
but I don't know can I use it on SQL 2008.
After all I figure out following:
You shoud not correct merge system trigger on publisher directly because it will replicate then on subscriber (strange way system trigger are replicated) and will cause identity range on subscraber to be frequently reasdigned.
On base of jagbarcelo blog and his examples I figure out how to change system tiger which generate merge system triggers. Because jagbarcelo example show solution for transaction replication after some investigation and on the base of referencing on one thread undocumented store procedure sp_MSaddmergetrigger I have found that foloowing three merge store procedure are sp_MSaddmergetrigger_internal (complicated version of triggers with identity, logical record and so on), sp_MSaddmergetrigger_form_template (for base version of triggers) ans sp_addupdatetrigger (this one is called by sp_MSaddmergetrigger_internal for update triggers I think because sp_MSaddmergetrigger_internal is so big). Before changing them i have after some time succed to get code of this sp's form mssqlsystemrecource databse for SQL server 2008 R2. (Data about hove to applay changed trigger code to this database and distributing it is described on jagbarcelo blog). (His version for transaction based trigger was not accesibel ad begining but now is because he put them now on his new data server, thanks for that). After changing code of this sp's in mssqlsystemresource database and distribution of them on client I have recreate triggers on publisher and subscriber by calling sp_MSaddmergetrigger for each identity table article on both publisher and subscriber (you can do that for other table article too). Good think is that with sp_addmergetrigger you do not need to reinicialize publisher and subscriptions). Parameters for this sp are source_table and table_owner. After that merge system trigger code are cahnged and MS acces now correctly work with identitys.
If someone need changed code (scripts for changeing triggers you can e-mail me and I will send them or put them on suggested plase on net.
- Proposed as answer by Željko Matić Sunday, October 31, 2010 6:27 PM
I have send you e-mail with script whisch you need to applay.
You should foloow instruction from http://jagbarcelo.blogspot.com/search/label/identity site but his description is for transactional replication fro SQL 2005 SP1 amd this main are for merge replication for SQL 2008 R2. Scripts change system sp's which generate merge system triggers on subscriber and publisher and are called from sp_Msaddmergetriggers and all of them are stored in resource database (mssqlsystemresource) in %PROGRAMFILES%\Microsoft SQL Server\MSSQL.1\MSSQL\Binn directory usualy on same disc as master database.
1. Do a backup of resources database
net stop mssqlserver
copy mssqlsystemresources.mdf mssqlsystemresourced.mdf.original
copy mssqlsystemresources.ldf mssqlsystemresources.ldf
copy mssqlsystemresource.ldf mssqlsystemresource.ldf.original
2. Start SQL in single-user mode
net stop slserveragent
net stop (all SQL server related services)
net start mssqlserver /m
3. Instead of his script you should applied this three on following way (all must be than from sqlcmd utility):
sqlcmd -U sa -A -d master -i "sp_Msaddmergetrigger_from_template.sql"
sqlcmd -U sa -A -d master -i "sp_MSaddupdatetrigger"
sqlcmd -U sa -A -d master -i "sp_MSaddmergetriggers_internal.sql"
4. Start sql without /m switch and all other sql services
net stop mssqlserver
net start mssqlserver
net start stop slserveragent
net start (all SQL server related services previously stoped)
5. The best thing is that your don't need to reinitialize publications and subscription in merge replication (like is told in jogbarcelo blog) if you use sp_msaddmergetriggers for eatch table article (only table article with identity or all table articles). This sp will regenerate triggers and other objects for merge table articles.
exec sp_msaddmergetriggers @table_owner=N'schema of table',@source_table=N'name of source table' – important this isname of table and not of article
this sp has more then this @table_owner and @source_table parameters but this two are most inportant and I have being using sp_Msaddmergetriggers only with them.
Code added change this sp which generate nerge system trigger in way that on begining of generated trgger code is added which store value of @@IDENTITY variable and on end of system trigger add coe which store previously stored value in temp table as identity and by this restore value in @@IDENTITY variable.
For subscribers you can repeat procedures from 1-5 or you can just copy mssqlsystemresource database from binn directory from publisher and replace this database on subscribers (before ovewrite backup orginal mssqlsystemresource database). Before overwrite stop services and after overwrite start them again and call sp_Msaddmergetriggers for each identity table article.
I haven't seen your mail till now. I will search for trigger scripts. But you wouldn't use this but take version of this scripts from your version of SQL (mssqlsystemresource) (my scripts are for SQL 2008 R2) and you shoul change the code on same places with inputing code marked in triggers.
When you pathc on this way on publisher you can transfer them on subscriber by coping mssqlsystemresource on it (but you should do that before cteating subscription or if you create subscription before than you should recreate all merge triggers with calling one store procedure wihic do taht job).
I'm using this solution and it work well.
When I find data I will send them to you.
For anyone still bashing thier heads over this significant issue, I came up with a simple workaround. I added a field called InsertGUID to every table that I needed to know the assigned SQL ID. Before the insert, I populate the field with a GUID, save the record, then lookup the record by the GUID to get the ID assigned by the SQL server.
Using this workaround, I don't have to worry about running the trigger modification scripts on every SQL server, then worry about it breaking if our users decide to update SQL server without our knowledge. Also don't have to worry about SQL version. Not sure if this workaround will work for every situation, but it seems to work fine for me so far.
I'll post results of a beta test with a live user.
The problem is if you use MS Access thet in SQL 2005 and SQL 2008 link table connected to query, form or even directly from table automaticly swithc after insert to record which have ID returned by SQL (wrong one returned from merge trigger). If merge trigger templates from msysresource.mdf is changed then all work fine in Access in forms, queries and link tables.