Re: sql CLR trigger causing havoc in Access linked table

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

Thank you. I suspected I'd get the same effect from straight SQL, but would be able to compensate using scope_identity(). I suppose it is obvious, as @@identity is a global variable, but was the first time I've seen this behavior from Access.

Am surprised to learn the CLR puts a heavy strain on the server. Will investigate this, as the opposite is my goal.

Thank you for the reply,
Lew




Sylvain Lafontaine wrote:
Your problem has nothing to do with the fact that you are using CLR or not for your trigger and you will get the same effect if you are using classical T-SQL for your insert trigger. A possible solution is to reset the @@identity value at the end of the insert trigger, see:

http://groups.google.com/group/microsoft.public.sqlserver.programming/browse_frm/thread/31c1dd4693ca321b/93ec304f6d8e5c4a?q=adp+trigger+%40%40identity&rnum=1#93ec304f6d8e5c4a

For a strict point of vue of performance, it might also be a better idea to use a pure T-SQL solution without any CLR because the latter is probably setting an heavy load on the sql server.

For complicated databases, you will have to leave ODBC linked tables behind and use passthrough queries and make your own updating or to use .NET technologies instead.

Finally, the correct newsgroup for this would be m.p.a.odbclientsvr or m.p.a.externaldata .


.