SQL Server 2019で、「データベース メール」の設定を確認したので、そのメモ。
下記のSQL文を実行することで、「データベース メール」に設定されたSMTPサーバや送信元メールアドレスなどの設定を確認することができる。SQL Server 2019とSQL Server 2005で確認した。
select *
from msdb.dbo.sysmail_profile p
join msdb.dbo.sysmail_profileaccount pa on p.profile_id = pa.profile_id
join msdb.dbo.sysmail_account a on pa.account_id = a.account_id
join msdb.dbo.sysmail_server s on a.account_id = s.account_idこのSQL文は、こちらのサイトより。
SELECTの結果がない場合には、「データベース メール」は設定されていない。
SQL Serverのプロシージャからメールを送信する場合は、「MSDB.DBO.SP_SEND_DBMAIL」を使用する。
MSDB.DBO.SP_SEND_DBMAIL構文等： https://learn.microsoft.com/ja-jp/sql/relational-databases/system-stored-procedures/sp-send-dbmail-transact-sql?view=sql-server-ver16
SQL Serverからメールを奏するための SMTPサーバの設定については、「データベース メール」「Database Mail」を別途設定する必要がある。
SSMSのGUIで設定するのが楽で、左のツリーで「管理」の下に「データベース メール」があるので「データベース メールの構成」を選択して、ウィザードに従って設定する。
参考： https://sql55.com/query/send-email-from-sql-server.php
SQL Serverのプロシージャからメールを送信する場合は、「MSDB.DBO.SP_SEND_DBMAIL」を使用する。
MSDB.DBO.SP_SEND_DBMAILSQL Serverからメールを奏するための SMTPサーバの設定については、「データベース メール」「Database Mail」を別途設定する必要がある。
SSMSのGUIで設定するのが楽で、左のツリーで「管理」の下に「データベース メール」があるので「データベース メールの構成」を選択して、ウィザードに従って設定する。
SQL Server 2019からSQL Server 2005へのリンクサーバ経由でアクセスするプロシージャーでエラーが発生した。
SQL ServerにSSMSでアクセスしてみると、リンクサーバーは存在する。リンクサーバーへの接続も問題なし。Select文でもリンクサーバー先のテーブルを参照できる。プロシージャーを実行すると、OLEDBのエラーが表示される。
リンク サーバー "サーバ名" の OLE DB プロバイダー "MSOLEDBSQL" から、メッセージ "トランザクションは既に暗黙的または明示的に、コミットまたは中止されています。" が返されました。
メッセージ 7391、レベル 16、状態 2、プロシージャ dbo.プロシージャー名、行 99 [バッチ開始行 2]
リンク サーバー "サーバ名" の OLE DB プロバイダー "MSOLEDBSQL" で分散トランザクションを開始できなかったので、この操作を実行できませんでした。リンク サーバー "サーバ名" の OLE DB プロバイダー "MSOLEDBSQL" で分散トランザクションを開始できなかったので、この操作を実行できませんでした。状況をまとめると・・・
いろいろと切り分けた結果、認証先？のAD（Active Directory）のOSバージョンによって、プロシージャーの実行が成功するか失敗するかが分かれていた。古いWindowsOSのADに問い合わせがいくと成功する。新しいOS（Windows Server 2022）だと失敗する。
Windows Server 2022のADに問い合わせされて、SQL Server 2005にアクセスされると、プロトコルとかセキュリティのキーセットの問題ではじかれて分散トランザクションが失敗して、タイムアウトになっているようだ。古いものは、互換性も無くなってきているので、きつい。
.NET6のサポート終了が近づいているので、.NET8に変更して実行したところ、SQL Serverとの接続でエラーが発生した。
An unhandled exception occurred while processing the request.
Win32Exception: 信頼されていない機関によって証明書チェーンが発行されました。
Unknown location
SqlException: A connection was successfully established with the server, but then an error occurred during the login process. (provider: SSL Provider, error: 0 - 信頼されていない機関によって証明書チェーンが発行されました。)
Microsoft.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, bool breakConnection, Action<Action> wrapCloseInAction)「Microsoft.Data.SqlClient パッケージ」の破壊的変更の影響だった。対処としては、ConnectionStringsの接続文字列の最後に、「TrustServerCertificate=True;」を追加した。下記みたいな感じ。
"ConnectionStrings": {
～～～略～～～;Database=DBname;TrustServerCertificate=True
},これでもう一度実行して、実行できることを確認した。
参考： https://learn.microsoft.com/ja-jp/ef/core/what-is-new/ef-core-7.0/breaking-changes?tabs=v7
メモとして。
SQL Server 2005のDBFファイルをデタッチして、SQL Server 2019に持っていってアタッチしても、エラーになる。SQL Server 2019上は、一応存在するが修復が必要が状態として表示されており、オンラインにはならない。DBファイルのバージョンアップが必要になる。
SQL Serverで、個別のデータベースに対して、権限を与えても、SQL Server エージェントのジョブの部分はSQL Server Management Studio（SSMS）には、「SQL Server エージェント」も「ジョブ」も表示されない。これの権限は、データベースの権限とは別につける必要がある。
SQL Serverのジョブ（SQL Server エージェント）に権限を付ける一番簡単な方法は、以下の操作でsysadmin権限の付与を行う。
これで権限が付与されるので、権限を与えたユーザで、SSMSで接続して、「SQL Server エージェント」と「ジョブ」が表示されることを確認する。
もし、SQL Server エージェントだけの権限を与える必要がある場合は、データベースの「msdb」にあるロールを割り当てる。
選択できるロールの概要は以下。
SQLAgentUserRole
→SSMSで接続したときに、SQL Serverエージェントが表示される。
表示されるのは、接続ユーザの権限があるもののみ。
SQLAgentReaderRole
→SSMSで接続したときに、SQL Serverエージェントが表示される。
他のユーザのSQL Serverのジョブも表示される。
メニューで、ジョブが実行できるように見えるが、実行すると権限がないものはエラーになる。
SQLAgentOperatorRole
→SSMSで接続したときに、SQL Serverエージェントが表示される。
SQL Serverのジョブの表示や実行ができる。
SQL Serverでストアドプロシージャの一覧を表示するためには、「INFORMATION_SCHEMA」の「ROUTINES」ビューを参照する。’ROUTINE_TYPE’で’PROCEDURE’ を指定することでプロシージャのみに絞り込める。絞り込みを行わない場合は、FUNCTION なども表示される。
■プロシージャのみで絞り込み
SELECT
*
FROM
INFORMATION_SCHEMA.ROUTINES
WHERE
ROUTINE_TYPE = 'PROCEDURE'
ORDER BY
SPECIFIC_CATALOG, SPECIFIC_SCHEMA, SPECIFIC_NAME■プロシージャだけでなく、ファンクションも表示する
SELECT
*
FROM
INFORMATION_SCHEMA.ROUTINES
ORDER BY
SPECIFIC_CATALOG, SPECIFIC_SCHEMA, SPECIFIC_NAME■必要なものだけ表示する
SELECT
SPECIFIC_CATALOG, SPECIFIC_SCHEMA, SPECIFIC_NAME, ROUTINE_CATALOG, ROUTINE_SCHEMA, ROUTINE_NAME, ROUTINE_TYPE
FROM
INFORMATION_SCHEMA.ROUTINES
ORDER BY
SPECIFIC_CATALOG, SPECIFIC_SCHEMA, SPECIFIC_NAME参考：
INFORMATION_SCHEMA.ROUTINES の説明
現在のデータベースの現在のユーザーがアクセスできるストアド プロシージャと関数ごとに、1 行のデータを返します。 戻り値を記述する列は、関数にのみ適用されます。 ストアド プロシージャの場合、これらの列は NULL になります。
SQL Serverのバージョン違いによるTransact-SQLの差は、忘れたころに踏み抜く。SQL Server 2005で、yyyy/mm/dd形式で日付を出力しようとしたところ、下記のエラーが表示されて、実行できず。
'format' は 組み込み関数名 として認識されません。formatは、SQL Server 2016以降はつかえるようだ。対象は、SQL Server 2005なので、format関数は追加されておらず、convert関数を使って、yyyy/mm/ddの形式にする。例としては下記。
SQL Server 2005で、日付（datetime型、smalldatetime型など）を、yyyy/mm/dd の形式で表示する。
select convert(nvarchar,GetDate(),111) AS 'yyyy/mm/ddフォーマット'SQL Server 2019で、日付（datetime型、smalldatetime型など）を、yyyy/mm/dd の形式で表示する。
select format(GetDate(),'yyyy/MM/dd') AS 'yyyy/mm/ddフォーマット'SQL Server 2019は、convert関数でも動作する。
SQL Server 2005から、SQL Server 2019へのリンクサーバで、リンクサーバ先のプロシージャーを起動させたら、下記のRPCのエラーがでた。
RPC に対して構成されていません。下記の方法で対処した。
１．SQL Server Management Studioで、リンクサーバを選択して、「プロパティ」を開く。
２．プロパティの「サーバー オプション」を開く。
３．「RPC出力」の箇所を、「True」に変更する。
これで、もう一度実行してみる。