ODBC・・・リンクテーブルでの更新に失敗
マイクロソフトデータベースアクセス（以下Access）とSqlServerにてシステム構築したのですが、Accessから更新すると、ODBC・・・リンクテーブルでの更新に失敗となり結局、ストアドプロシージャ（以下ストアド）にてSqlServer側で更新処理をすることにしました。
https://pctips.jp/server/sqlserver-stored-error/
もう、ストアドにすれば問題ないだろ！って思っていましたが、今度はAccessがフリーズしてしまう現象になってしまいました。
エラーにでもなってヒントをもらえるならまだ良しとしますが、フリーズしてプロセスを切らない限り、ずーーとフリーズしていました。
本当に困ったソフトです。
ストアドの実行方法に問題！？（関係なし）
VBAからストアドを実行する方法を使っていましたが、パススルークエリから簡単にストアドを実行できるというので試しにやってみました。
https://pctips.jp/pc-soft/sqlserver-access-stored-procedure202002/
ストアドを実行するには、VBAしか方法がないと思っていましたので目から鱗でしたが、やっぱりフリーズしました。
ストアドの実行方法による原因ではないようです。
VPN接続の問題（未テスト）
今回のシステムでの最大の問題は、更新すると、ODBCが切れたり、Accessがフリーズする事です。
しかし、実際に運用使用している方の環境では、ODBCが切れる現象は出ても、Accessがフリーズする現象は出てないようです。
●さくらのクラウド（SqlServer）＝＝サイト間VPN＝＝VPNルーター（実際に運用環境）
自分が試している環境が、
●VPNルーター＝＝IPsecリモート接続＝＝自分のPC
という環境でテストをしています。
もしかすると、VPNルーター環境での接続なら問題なくAccessの更新はできるのか！？ということをテストしたいのですが、やれるタイミングが無くてやっていません。
ただ、SqlServerへのリモートディスクトップ接続には何もストレスはありませんので、そこそこの回線スピードは出ているのかと推測します。
ストアド実行時のフリーズ原因（行ロック）
ストアドの更新（UPDATE）コードの部分をコメントアウトして実行すれば、もちろんちゃんと動いてくれます。
ストアド実行時のAccessでのフリーズは、更新が原因なのは推測できます。
更に、SqlServerからもストアドの実行すると、毎回では無いもののクエリをキャンセルしない限り実行している表示から変わらない状態になりました。
さらに詳しく
トランザクション内でUPDATE後に処理待ちになる
というQ&Aの記事を見つけました。
SQLServer2005のストアドプロシージャで以下の処理を実行すると
処理待ち状態になり、固まってしまいます。
行ロックが行われていないだけではないでしょうか。
"SQL Server 行ロック"とかでググってみるといろいろ出てくると思いますよ。
行ロックって何？
他のトランザクションがテーブルにロックをして解放するまで待ち状態にするような仕組みのようです。
下記のサイトにて実験している記事があるので参考になりますね。
行ロック状態を確認できるクエリがありました。
SELECT
resource_type AS type --オブジェクトの種類
,resource_associated_entity_id as entity_id --エンティティID
,( CASE WHEN resource_type = 'OBJECT' THEN
OBJECT_NAME( resource_associated_entity_id )
ELSE
( SELECT
OBJECT_NAME( OBJECT_ID )
FROM
sys.partitions
WHERE
hobt_id=resource_associated_entity_id )
END)
AS object_name
,request_mode --ロックの種類
,request_type --要求の種類
,request_status --状態
,request_session_id AS Session_id --セッションID
,(SELECT hostname
FROM sys.sysprocesses
WHERE spid = request_session_id) AS ProcessName
FROM
sys.dm_tran_locks
WHERE
resource_type <> '調べるデータベース名'
ORDER BY
request_session_id
ストアドでフリーズしていた時に、上のクエリをSqlServerで実行すると、沢山の行ロック状態が確認することができました。
今回のケースは、行ロックが原因だったようです。
行ロックってどうやって解除するのか？
--UPDATE文実行時の例 SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED UPDATE テーブル名 SET 更新内容 WHERE 更新条件
UPDATEの前に、「SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED」を付加すれば、ロック中でも参照や更新が可能という記事を見つけました。
自分の場合は、更新がされない状態になってしまいました。
いろいろと解説しているサイトがあるのですが、自分の理解力が無くて良く分かりません。
さらに調べてみると、デッドロックと言うらしいという事がわかりました。怖い！
なんとなく理解できそうなサイト見つけました。ただ、回避策は理解できない。
ストアドに下記のコードをいれると変化がありました。
SET LOCK_TIMEOUT 0
これは、ロックされているテーブルや行を待つ時間のようです。
０ということは、待たない・・・つまり、スグにロックエラーとなるってことでしょうかね。
このコードをいれたら、フリーズする事がなくなりましたが、更新もできませんでした。
そこで、０じゃなく、１０にしたら更新できたりできなかったりになりました。あー辛い。
できなかったりできたりと言うのが一番嫌な結果ですよね。
調査は続けますが・・・
SqlServerからのリンクテーブルをAccessVBAで更新かけると、ODBC・・・更新に失敗しましたとなったので、ストアドに変更したら、Accessフリーズ（ストアド実行中状態）となり困ってしまいました。
ストアドで処理した方が早いに決まっているのでストアドにて処理したいと考えていますが、もしかすると、１つのストアド内で、２つのUPDATEしているのが今回の原因か！？って今思いました。
USE [SGDB]
GO
/****** Object: StoredProcedure [dbo].[order_edit] Script Date: 2022/09/25 14:33:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[order_edit]
@eno VARCHAR(10)
AS
BEGIN TRY
--SET LOCK_TIMEOUT 10
BEGIN TRANSACTION
ここから処理コード
UPDATE
[order]
SET
更新するフィールド設定
FROM [order] INNER JOIN
[order_job] ON [order].NO = [order_job].NO
WHERE ([order].NO = @eno)
UPDATE
[trader_ag]
SET
更新するフィールド設定
FROM [trader_ag] INNER JOIN
order_job ON [trader_ag].od_no = order_job.NO
WHERE ([trader_ag].od_no = @eno)
--ここまで処理コード
COMMIT TRANSACTION
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
--RETURN ERROR_NUMBER()
END CATCH
RETURN 0
上のUPDATEと下のUPDATEで、同じorder_job.NOと言うキーを使っています。
同じトランザクション内で更新処理をしているのが問題なのかは、後ほど実験して報告したいと思います。