memo.

タグ: SQL Server

  • 読了：ソフトウェアデザイン 2025年7月号

    やっと読む時間をとれた。積読が多すぎた。Rustはやっぱり難しそう。気にはなっているけれど、手を出さなくてもいいかも。特性だけわかっていればいいかな。

    データ分析のためのSQL講座はよかった。WITH句があるのか、知識のアップデートができていなかったな。VIEWをつくるよりも柔軟だし良さそうだ。分析だとパフォーマンスを気にすることも、それほどないだろうし、覚えておいて損はない感じ。

    Amazon: https://amzn.to/44uDLK4


  • SQL Serverの「データベースメール」の確認用SQL

    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文は、こちらのサイトより。

    https://www.web-dev-qa-db-ja.com/ja/sql-server/%E7%8F%BE%E5%9C%A8%E3%81%AE%E3%83%87%E3%83%BC%E3%82%BF%E3%83%99%E3%83%BC%E3%82%B9%E3%83%A1%E3%83%BC%E3%83%AB%E6%A7%8B%E6%88%90%E3%82%92%E7%A2%BA%E8%AA%8D%E3%81%99%E3%82%8B%E3%81%AB%E3%81%AF%E3%81%A9%E3%81%86%E3%81%99%E3%82%8C%E3%81%B0%E3%82%88%E3%81%84%E3%81%A7%E3%81%99%E3%81%8B%EF%BC%9F/l956680474

    SELECTの結果がない場合には、「データベース メール」は設定されていない。

  • SQL Server のプロシージャからメール送信する

    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 のプロシージャからメール送信する

    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 2019からSQL Server 2005へのリンクサーバ経由でアクセスするプロシージャーがエラー

    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" で分散トランザクションを開始できなかったので、この操作を実行できませんでした。

    状況をまとめると・・・

    • SQL Server 2005もSQL Server 2019も稼働している。
    • もともとはプロシージャーも正常に実行できていた。
    • リンクサーバーの接続確認は問題なし
    • Select文などを発行すると、正常にリンクサーバーのテーブルにアクセスできる。（Viewでも同様）
    • プロシージャーは、失敗する。
    • 分散トランザクションの設定はちゃんとできている（もともとは成功していた）。
    • リンクサーバーの接続で使用するユーザはアクティブであり、パスワードも問題なし。
    • 実行結果を見ていくと、ごくまれに成功しているときがある（SQL Serverへのコネクションは異なる）

    いろいろと切り分けた結果、認証先？のAD（Active Directory）のOSバージョンによって、プロシージャーの実行が成功するか失敗するかが分かれていた。古いWindowsOSのADに問い合わせがいくと成功する。新しいOS（Windows Server 2022）だと失敗する。

    Windows Server 2022のADに問い合わせされて、SQL Server 2005にアクセスされると、プロトコルとかセキュリティのキーセットの問題ではじかれて分散トランザクションが失敗して、タイムアウトになっているようだ。古いものは、互換性も無くなってきているので、きつい。

  • Win11のVS2019からSQL Server 2005に接続できない

    開発環境をWindows10からWindows11にアップグレードした。Windows11のVisual Studio 2019の開発環境のコードから、Windows Server 2003 R2上のSQL Server 2005に対して、接続ができなくなった。接続時のセキュリティの不一致のため。

  • SQL Server 2005のDBFファイルをSQL Server 2019にアタッチするとエラー

    メモとして。

    SQL Server 2005のDBFファイルをデタッチして、SQL Server 2019に持っていってアタッチしても、エラーになる。SQL Server 2019上は、一応存在するが修復が必要が状態として表示されており、オンラインにはならない。DBファイルのバージョンアップが必要になる。

  • SQL Server エージェントのジョブの権限を与える方法

    SQL Serverで、個別のデータベースに対して、権限を与えても、SQL Server エージェントのジョブの部分はSQL Server Management Studio（SSMS）には、「SQL Server エージェント」も「ジョブ」も表示されない。これの権限は、データベースの権限とは別につける必要がある。

    SQL Serverのジョブ（SQL Server エージェント）に権限を付ける一番簡単な方法は、以下の操作でsysadmin権限の付与を行う。

    1. SSMSを使って、管理者権限のあるユーザでSQL Serverに接続する。
    2. SSMSのオブジェクトエクスプローラーで、「セキュリティ」「サーバー ロール」の順に開く（展開する）。
    3. サーバーロールに表示された「sysadmin」を右クリックして、プロパティを開く。
    4. プロパティの「メンバー」の「追加」をクリックする
    5. 権限を与えるユーザを指定して、OKをクリックする。

    これで権限が付与されるので、権限を与えたユーザで、SSMSで接続して、「SQL Server エージェント」と「ジョブ」が表示されることを確認する。

    もし、SQL Server エージェントだけの権限を与える必要がある場合は、データベースの「msdb」にあるロールを割り当てる。

    1. SSMSを使って、管理者権限のあるユーザでSQL Serverに接続する。
    2. 「セキュリティ」、「ログイン」から、SQL Server エージェントを使わせるユーザのプロパティを開く
    3. 「ユーザーマッピング」を開く。
    4. 「msdb」にチェックをいれ、選択した状態にする
    5. ロールの「SQLAgent～～～」で、最適な権限を選択して、OKをクリックする。

    選択できるロールの概要は以下。

    SQLAgentUserRole
    →SSMSで接続したときに、SQL Serverエージェントが表示される。
    　表示されるのは、接続ユーザの権限があるもののみ。

    SQLAgentReaderRole
    →SSMSで接続したときに、SQL Serverエージェントが表示される。
    　他のユーザのSQL Serverのジョブも表示される。
    　メニューで、ジョブが実行できるように見えるが、実行すると権限がないものはエラーになる。

    SQLAgentOperatorRole
    →SSMSで接続したときに、SQL Serverエージェントが表示される。
    　SQL Serverのジョブの表示や実行ができる。

    参考： https://learn.microsoft.com/ja-jp/sql/ssms/agent/sql-server-agent-fixed-database-roles?view=sql-server-ver15

  • Windows11からWindows Server 2003上のSQL ServerにODBC接続できない。

    タイトルのままではあるが、Windows11からWindows Server 2003 R2上のSQL Server 2005にODBC接続できない。

    セキュリティの関係で接続できない。ODBCドライバのバージョンを古いものに変えてもできないので、OSレベルでのセキュリティの問題で接続できない。

  • SQL Serverで日付をyyyy/mm/ddで表示しようとしたら関数が認識されずエラーになった

    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関数でも動作する。