Whew! What a harrowing experience that was. First off, a quick note to the MS Access team:
If you haven't already done it for A2010, take five minutes and do a find and replace on your codebase replacing every instance of SELECT @@IDENTITY with SCOPE_IDENTITY() where it interfaces with SQL Server. This is a good example of where an open-source project would have been fixed a long time ago.... But I digress.
My apologies upfront for what will be a very lengthy post. I'm going to try and record all of the knowledge I've gained over the past week trying to fix this vexing problem. My original post summed up the behavior I was seeing. Please read that first for full context.
The thing that gave me the most fits with this problem was its seemingly random nature. We would do an insert and it would fail. Then we would do fifty more and they would all succeed. Then the next day we would do an insert and it would fail again. We'd then do ten more and they would all succeed. Then a few hours later one would fail again. And on and on.
The problem was being caused by merge replication. When a table gets added as an article to a merge replication publication, several triggers are automatically generated to manage the replication. This was not a problem for us when we used merge replication in SQL Server 2000. However, these triggers were modified starting with SQL Server 2005. The specific modification that caused the problem is in these lines of code that were auto-generated in the insert trigger for the affected tables:
select @newgen = NULL
select top 1 @newgen = generation from [dbo].[MSmerge_genvw_8D1ADB4453634BF39DA4AA582FE18F78] with (rowlock, updlock, readpast)
where art_nick = 14201004 and genstatus = 0
and changecount <= (1000 - isnull(@article_rows_inserted,0))
if @newgen is NULL
begin
insert into [dbo].[MSmerge_genvw_8D1ADB4453634BF39DA4AA582FE18F78] with (rowlock)
(guidsrc, genstatus, art_nick, nicknames, coldate, changecount)
values (newid(), 0, @tablenick, @nickbin, @dt, @article_rows_inserted)
select @error = @@error, @newgen = @@identity
if @error<>0 or @newgen is NULL
goto FAILURE
end
else
begin
-- now update the changecount of the generation we go to reflect the number of rows we put in this generation
update [dbo].[MSmerge_genvw_8D1ADB4453634BF39DA4AA582FE18F78] with (rowlock)
set changecount = changecount + @article_rows_inserted
where generation = @newgen
if @@error<>0 goto FAILURE
end
Here's what's going on in the above code snippet. SQL Server is checking to see if there is an open row in the MSmerge_genhistory table (MSmerge_genvw_8D1ADB4453634BF39DA4AA582FE18F78 is a system view into that table). If there is an open row (genstatus = 0) and the number of inserts plus the change count does not exceed 1000 then the counter gets incremented. But if there is not an open row a new one gets inserted. Which resets the @@IDENTITY variable. Mass hysteria ensues. Cats and dogs, living together. Etc, etc.
To be clear, the fault here is with the Access team for using @@IDENTITY, not the SQL Server team for modifying the internals of merge replication. But, geez, I thought you guys were playing for the same team...
It's worth noting that there is a different row for every table involved in merge replication. I found the following query most helpful for troubleshooting and understanding what's going on in the genhistory table:
SELECT A.name
,H.generation
,H.art_nick
,H.coldate
,H.genstatus
,H.changecount
FROM [MSmerge_genhistory] AS H
INNER JOIN [sysmergearticles] AS A
ON H.art_nick = A.nickname
ORDER BY H.generation DESC
So what closes a row in the genhistory table? Well, among other things, every row in the genhistory table is closed whenever a merge agent runs against the database. Any merge agent. For any publication. In our case, we have two separate merge replication publications that run out of the same database. One merge agent runs hourly; the other runs nightly.
This brings us back to the seemingly random behavior. I'll annotate my earlier paragraph to explain the behavior:
We would do an insert and it would fail. [New row inserted in genhistory table.] Then we would do fifty more and they would all succeed. [Row in genhistory table incremented.] Then the next day [after the nightly (and hourly) merge agent ran and closed the row in the genhistory table] we would do an insert and it would fail again. [New row inserted in genhistory table.] We'd then do ten more and they would all succeed. [Row in genhistory table incremented.] Then a few hours later [after the hourly merge agent ran and closed the row in the genhistory table] one would fail again. [New row inserted in genhistory table.]
Now that we finally know what's going on we need some way to fix it. The "right" way would be to use SCOPE_IDENTITY() instead of SELECT @@IDENTITY. However, that behavior is hard-coded into MS Access so we're forced to put a workaround in SQL Server. This link provided by @Roland suggests there are three workarounds (see the link for full details).
The third workaround seemed like the best option, but it required multiple shutdowns and restarts of the entire database server plus the author's own additional caveats:
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.
....So after starting and stopping the entire database server I need to make some unsupported changes to core SQL Server behavior plus remember to back out those changes and re-apply them before applying future service packs. Ummm, no thanks.
I re-read the article several times and realized the key piece of information was this trick to save and restore the @@IDENTITY value. I realized all I needed to do was apply these four lines to every merge insert trigger:
declare @identity int, @strsql varchar(128)
set @identity=@@identity
set @strsql='select identity (int, ' + cast(@identity as varchar(10)) + ',1) as id into #tmp'
execute (@strsql)
The author's approach was to modify the base merge insert trigger so that the auto-generated triggers were created with the above lines already in place. This involves editing only one place, but has the drawbacks already mentioned. My approach was to modify the triggers after they were already created. The only drawback is that you have to do it in a lot more places (ie, once for every table). But if you could script it somehow.....
The final piece of the puzzle to get it all to work was figuring out what all the current triggers were. This required the use of two system views: triggers and syscomments. I used the triggers view to identify the offending triggers (their names all start with 'MSmerge_ins'). I then used the syscomments view to get the T-SQL used to create each trigger. The syscomments.text field has a size of 4000. If the T-SQL exceeds 4000 characters, it gets split into several rows ordered by the syscomments.colid.
My final algorithm goes as follows:
- Cycle through every 'MSMerge_ins' trigger
- Rebuild the CREATE TRIGGER T-SQL from the syscomments view
- Run the CREATE TRIGGER T-SQL through a regular expression
- If the trigger needs to be modified the regex returns an
ALTER TRIGGER statement
- If the trigger has already been modified, the regex returns the T-SQL unaltered
- If an
ALTER TRIGGER statement is returned it gets executed as a pass-through query that modifies the trigger
I still have to remember to run this code whenever I create (or re-create) a merge replication publication. Another drawback to my approach is that my regular expression may need to be modified to handle future changes to the automatically generated triggers. But I can do it on the live server, I don't have to shut anything down, and I'm not agonizing over what I might break in the core functionality. Decide for yourself what you can and can't live with.
I wrote this to run in MS Access. To simplify the code, I created links to the triggers, syscomments, and sys_tables views. The sys_tables view is not strictly necessary but I left it in for debugging.
Here's the code:
Sub FixInsertMergeTriggers()
Dim SQL As String, TriggerSQL As String, AlterSQL As String
Dim PrevTrigger As String, ProcessTrigger As Boolean
SQL = "SELECT Ta.Name AS TblName, Tr.Name AS TriggerName, " & _
" C.Text, C.Number, C.colid " & _
"FROM (syscomments AS C " & _
"INNER JOIN triggers AS Tr ON C.id=Tr.object_id) " & _
"INNER JOIN sys_tables AS Ta ON Tr.parent_id=Ta.object_id " & _
"WHERE Tr.name like 'MSmerge_ins*' " & _
"ORDER BY Tr.name, C.colid"
With CurrentDB.OpenRecordset(SQL)
Do
If .EOF Then
If Len(PrevTrigger) > 0 Then
ProcessTrigger = True
Else
Exit Do
End If
Else
ProcessTrigger = (!TriggerName <> PrevTrigger)
End If
If ProcessTrigger Then
If Len(TriggerSQL) > 0 Then
AlterSQL = ModifyTrigger(TriggerSQL)
If AlterSQL <> TriggerSQL Then
ExecPT AlterSQL
Debug.Print !TblName; " insert trigger altered"
End If
End If
TriggerSQL = ""
If .EOF Then Exit Do
End If
TriggerSQL = TriggerSQL & !Text
PrevTrigger = !TriggerName
.MoveNext
Loop
End With
Debug.Print "Done."
End Sub
Private Function ModifyTrigger(TriggerSQL As String) As String
Const DeclarationSection As String = " declare @identity int, @strsql varchar(128)" & vbCrLf & _
" set @identity=@@identity"
Const ExecuteSection As String = " set @strsql='select identity (int, ' + cast(@identity as varchar(10)) + ',1) as id into #tmp'" & vbCrLf & _
" execute (@strsql)"
Dim P As String 'variable that holds our regular expression pattern'
'Use regular expression to modify the trigger'
P = P & "(.*)" '1. The beginning'
P = P & "(create trigger)" '2. Need to change 'CREATE' to 'ALTER''
P = P & "(.*$)" '3. Rest of the first line'
P = P & "(^\s*declare\s*@is_mergeagent)" '4. First declaration line'
P = P & "([\s\S]*)" '5. The middle part'
P = P & "(if\s*@@error[\s\S]*)" '6. The lines after ...'
P = P & "(FAILURE:[\s\S]*)" '7. ... where we add our workaround'
ModifyTrigger = RegExReplace(P, TriggerSQL, _
"$1ALTER trigger$3" & vbCrLf & _
DeclarationSection & vbCrLf & _
"$4$5" & vbCrLf & _
ExecuteSection & vbCrLf & vbCrLf & _
"$6$7", , True, True)
End Function
Private Function RegExReplace(SearchPattern As String, TextToSearch As String, ReplacePattern As String, _
Optional GlobalReplace As Boolean = True, _
Optional IgnoreCase As Boolean = False, _
Optional MultiLine As Boolean = False) As String
Dim RE As Object
Set RE = CreateObject("vbscript.regexp")
With RE
.MultiLine = MultiLine
.Global = GlobalReplace
.IgnoreCase = IgnoreCase
.Pattern = SearchPattern
End With
RegExReplace = RE.Replace(TextToSearch, ReplacePattern)
End Function
'Execute pass-through SQL'
Private Sub ExecPT(SQL As String, Optional DbName As String = "MyDB")
Const QName As String = "TemporaryPassThroughQuery"
Dim qdef As DAO.QueryDef
On Error Resume Next
CurrentDB.QueryDefs.Delete QName
On Error GoTo 0
Set qdef = CurrentDB.CreateQueryDef(QName)
qdef.Connect = "ODBC;Driver={SQL Server};Server=myserver;database=" & DbName & ";Trusted_Connection=Yes;"
qdef.SQL = SQL
qdef.ReturnsRecords = False
CurrentDB.QueryDefs(QName).Execute
End Sub
AfterUpdateevent we insert that value into a history table. So the history table record should have had a value of 210272. However, the value that got inserted was 1077, which was the auto ID of a much older record. – mwolfe02@@IDENTITYbehind the scenes to get the newly inserted record back from SQL Server. I should add that the problem is occurring in Access versions 2000 and 2002. Haven't been able to test other versions. – mwolfe02