NTTデータ社「達人シリーズ」SQLServerへアクセスできない現象

達人シリーズ用サーバをリプレースし、過去年度含めすべてのデータベースを一括移行したが、最新版以外データベース参照できない現象が発生した。

結果的に達人CubeからSQL追加コンポーネントをインストールすることで解決したが経緯を簡単に書いておく。

エラー

１．データ管理

接続先((local/DB名)への接続がきませんでしたと表示されデータベースに接続することができない。

サーバ参照を見てもサーバ一覧が表示されず以下が表示される。

Microsoft Terminal Services

　｜＿tsclient

Microsoft Windows Network

２．データベース管理

データベースサーバに管理者権限で接続できませんでしたと表示されsaのパスワードを入力してもパスワードが違うと弾かれる。

原因

どの現象もSQLServerに接続できていないように見受けられる。

NTTデータ社に確認したところ「2020年9月以降にリリースされたものはSQLServer接続の方法が変更となったため、それ以前のプログラムはSQL追加コンポーネントが必要」とのこと。

環境

WindowsServer2019（ActiveDirectory環境下のメンバサーバ）

SQL Server 2019 Standard

参考

ActiveDirectory環境下での注意事項

達人CubeでSQLデータのバックアップスケジュールした際にバックアップファイルが保存できないことがある。

SQLServerサービス起動ユーザが保存先フォルダのアクセス権限がないことが原因のため、同サービス起動ユーザをローカルシステムアカウントに変更すると良い。

SQL Server オブジェクト作成日／更新日

ストアド、ユーザ定義関数、テーブル関数、トリガ等の作成日や最終更新日を調べる。

use [DBNAME]
go

SELECT name AS object_name
  ,SCHEMA_NAME(schema_id) AS schema_name
  ,type_desc
  ,create_date --作成日
  ,modify_date --最終更新日
FROM sys.objects
WHERE is_ms_shipped = 0 --1はシステム
and
(
type_desc = 'PRIMARY_KEY_CONSTRAINT' --プライマリキー
or
type_desc = 'DEFAULT_CONSTRAINT' --デフォルト制約
or
type_desc = 'VIEW' --ビュー
or
type_desc = 'SQL_STORED_PROCEDURE' --ストアドプロシージャ
or
type_desc = 'SQL_SCALAR_FUNCTION' --ユーザー定義関数
or
type_desc = 'SQL_TRIGGER' --トリガ
or
type_desc = 'SQL_TABLE_VALUED_FUNCTION' --テーブル関数
)
ORDER BY modify_date DESC;

go

Windows Server 2008 R2 に SQL Server 2005 インストール

●環境
Dell R310＋VMware ESXi 5.0上にWindows Server 2008 R2をフルインストール構成しWindowsUpdateをすべて適用した状態のマシンです。また役割および機能は一切追加されていない状態から行いました。

SQL ServerはStandard Edition x64でコンポ―ネットは基本的にすべてインストールしますが、サンプルデータベース、サンプルコードとアプリケーションはインストールしません。

事前にVisual Studio 2005 Team Suite SP1およびVisual Studio 2005 SP1 Update for Windows Vistaをダウンロードしておきましょう。ついでにSQL Server SP3もダウンロード。※2011/12/16現在SP4もリリースされています。

各ダウンロード先

Visual Studio 2005 Team Suite Service Pack 1 (KB926602)

Visual Studio 2005 Service Pack 1 Update for Windows Vista (KB932236)

For VistaとなっていますがWindows Server 2008にも対応しています。（Windows7もOK）

SQL Server 2005 Service Pack3

●手順概要

Reporting Servicesで必要となるIISをインストール
↓

SQL Serverのインストール
↓

途中でdevenv.exeの互換性問題が出るが大丈夫です
↓

SQL Server SP3の適用
Visual Studio 2005 Team Suite SP1の適用
Visual Studio 2005 SP1 Update for Windows Vistaの適用

●IISのインストール

Webサーバ IISへチェック

役割サービスを指定する

・HTTPの基本機能から
「静的なコンテンツ」
「既定のドキュメント」
「ディレクトリの参照」
「HTTPエラー」
「HTTPリダイレクション」を追加チェックします。

・アプリケーション開発から
「ASP.NET」を追加チェックします。

　ASP.NETにチェックを入れると関連サービスも必要だと聞かれますのでそのまま素直に追加します。

・セキュリティから
「Windows認証」を追加チェックします。

・管理ツールから
「IIS 6 メタベース互換」
「IIS 6 WMI互換」
「IIS 6 スクリプトツール」
「IIS 6 管理コンソール」を追加チェックします。

追加チェックされた状態

これでインストールを開始します。

インストール後、Web サーバ（IIS）に移動から実行されているか確認してください。サーバ上のブラウザでhttp://localhostと入力してIISのようこそページが表示されるかどうかで判断しても良いと思います。

●SQL Serverのインストール

これは私の独自設定で行うため部分的にはインストールする環境に合わせた設定にしてください。

インストールにある「サーバーコンポーネント、ツール、Books Online、およびサンプル」を指定して実行します。後でSP3以降を適用しなさいと表示されます。インストール後に実行するためとりあえず「プログラムを実行する」で進めます。

使用許諾契約書に同意して次へ

SQLServerインストール前に必要なコンポーネントのインストールします。

ではSQL Serverをインストールしていきます。

エラーや警告が出ていないかチェックします。

登録情報を入力します。

インストールするコンポーネントを指定します。

詳細設定を確認します。

名前付きインスタンス名は使わないので既定のインスタンスとします。

インスタンスについてはこちらを参照してください。

アカウントは「ドメインユーザアカウントを使用する」にしてサーバのローカルユーザ（Administrator）を指定します。※ドメイン環境下で必ずドメインを入れる必要はありません。ドメインに依存されないためにもドメインは空白の方が扱いやすいと思います。

認証モードの設定

セキュリティ的にはWindows 認証モードの方が良いが管理的にはパスワード認証があると便利なので混合モードにします。

照合順序の設定はデフォルトのままでよいと思います。変更したい方はどうぞ。

既定の構成をインストールします。

エラーレポートを自動送信するか設定します。

いよいよインストール開始です。エラーがないことを祈りましょう。

はい出ました！devenv.exeの互換性問題、承知しているのでそのままプログラムを実行しましょう。念のため「オンラインで解決策の有無を確認する」でチェックしておきましょう。また後半にも同じメッセージが出てきますが同じ方法で進めましょう。

完了です。

ではVisual Studio 2005 Team Suite SP1およびVisual Studio 2005 SP1 Update for Windows Vistaの順で適用していきましょう。

無事インストールが完了したら次にSP3の適用です。

自動的に検出してくれるので通常、対象となるプログラムを選択する必要はありません。

SQL Serverへの認証を行います。

実行プロセスがあるので、サービスからSQL Serve ***をすべてストップさせロックファイルがないようにしましょう。

完了したら念のためサーバを再起動してSQL Serverが起動しているかチェックしましょう。

Agent XPsコンポーネントは、このサーバのセキュリティ構成の一環としてOFFに設定されています。

SQL Server Management Studio（SQL Server 2005 SP3）でメンテナンスプランを参照しようとしたら、以下のメーッセージが表示された。SQL Server エージェントの拡張ストアド プロシージャが無効とのことらしい・・・いつの間にこんな状態になったのだろう？

セキュリティ構成ツールを使用して SQL Server エージェント サービスを開始すると、SQL Server エージェントの拡張ストアド プロシージャが自動的に有効になるらしいが状況は変わらずでした。

仕方ないのでコマンドで有効にすることにしました。

その前にOS側でローカルグループポリシーを編集します。

（Windows Server 2008 R2）

ファイル名を指定して実行＞gpedit.msc

コンピュータの構成＞Windowsの設定＞セキュリティの設定＞ローカルポリシー＞ユーザー権利の割り当て＞メモリ内のページロック

SQL Serverを稼動させているユーザーが存在しているか確認する。なければ追加しておく。

SQL Server Management Studioを使用して管理者でログインし以下のコマンドを実行します。

　

sp_configure 'show advanced options', 1;

GO
RECONFIGURE;
GO
sp_configure 'Agent XPs', 1;
GO
RECONFIGURE
GO
　
メンテナンスプランが開けるようになりました。
　

SQL Server DEFAULT 制約名を取得

ユーザーが定義したテーブルの各フィールドに定義されている制約名を取得する。
　
●SQL Server 2005以降
　
SELECT
　tb.id AS tb_id,　--（テーブルID）
　tb.name AS tb_name,　--（テーブル名）
　col.object_id AS col_id, 　--（フィールドID）
　col.name AS col_name, 　　--（フィールド名）
　col.default_object_id AS const_id,　--（制約ID）
　const.name AS const_name　--（制約名）
FROM sys.columns AS col
　LEFT JOIN sysobjects AS tb ON col.object_id = tb.id
　LEFT JOIN sys.default_constraints AS const ON col.default_object_id = const.object_id
WHERE (col.object_id IN(SELECT id FROM sysobjects WHERE (xtype = 'U'))) --ユーザーテーブルのみに限定
ORDER BY tb.id
　
　
●SQL Server 2000（SQL Server 2005も可能）
　
SELECT
　tb.id as tb_id,
　tb.name as tb_name,
　col.name as col_name,
　col.cdefault as const_id,
　const.name as const_name
FROM syscolumns as col
　LEFT JOIN sysobjects as tb on col.id = tb.id
　INNER JOIN sysobjects as const on col.cdefault = const.id
WHERE tb.xtype IN('U', 'D') AND
OTDER BY tb.id
　

MS SQLServer 2005 SP3

サービスパック３がリリースされました。
　
・修正される問題の一覧はこちら
　
・Readme ファイルはこちら
　
・ダウンロードはこちら

※Express Editionはこちら
　
・Books Onlineはこちら
　

　

ランキング（SQLServer 2005 分析関数の利用）

SQLServer2005の分析関数を利用して売上ランキングを作る

--売上テーブル（非現実的なテーブルですが・・・ご勘弁ください）
--ここではテーブル変数を利用します
DECLARE @UriageData TABLE (
GroupCD SMALLINT DEFAULT 0, --グループ
TantosyaCD SMALLINT DEFAULT 0, --担当者
UriageDate DATETIME, --売上日
Uriagegaku MONEY DEFAULT 0 --売上額
)

--売上データ
INSERT INTO @UriageData(GroupCD, TantosyaCD, UriageDate, Uriagegaku) VALUES(1, 1, '2008/01/01', 10000)
INSERT INTO @UriageData(GroupCD, TantosyaCD, UriageDate, Uriagegaku) VALUES(1, 2, '2008/01/02', 20000)
INSERT INTO @UriageData(GroupCD, TantosyaCD, UriageDate, Uriagegaku) VALUES(1, 3, '2008/01/01', 9000)
INSERT INTO @UriageData(GroupCD, TantosyaCD, UriageDate, Uriagegaku) VALUES(2, 4, '2008/01/01', 30000)
INSERT INTO @UriageData(GroupCD, TantosyaCD, UriageDate, Uriagegaku) VALUES(2, 5, '2008/01/03', 7000)
INSERT INTO @UriageData(GroupCD, TantosyaCD, UriageDate, Uriagegaku) VALUES(1, 1, '2008/01/10', 20000)
INSERT INTO @UriageData(GroupCD, TantosyaCD, UriageDate, Uriagegaku) VALUES(1, 2, '2008/01/12', 15000)
INSERT INTO @UriageData(GroupCD, TantosyaCD, UriageDate, Uriagegaku) VALUES(1, 3, '2008/01/11', 10000)
INSERT INTO @UriageData(GroupCD, TantosyaCD, UriageDate, Uriagegaku) VALUES(2, 4, '2008/01/11', 9000)
INSERT INTO @UriageData(GroupCD, TantosyaCD, UriageDate, Uriagegaku) VALUES(2, 5, '2008/01/10', 25000)

--ランキング（担当者別に売上額の高い順にソートして順位を付ける）
SELECT RANK() OVER(ORDER BY SUM(Uriagegaku) DESC) AS 'ランク',
　　　　TantosyaCD AS '担当者', SUM(Uriagegaku) AS '売上額'
FROM @UriageData
GROUP BY TantosyaCD






--順位が同じだったら・・・
--担当者１と２を同額になるようにデータを調整
INSERT INTO @UriageData(GroupCD, TantosyaCD, UriageDate, Uriagegaku) VALUES(1, 1, '2008/01/11', 5000)



担当者１と２共に２位となります。そのとき３位は歯抜けとなる



--RANK()の変わりにDENSE_RANK()を使うと歯抜けをしない形で返る
SELECT DENSE_RANK() OVER(ORDER BY SUM(Uriagegaku) DESC) AS 'ランク',
　　　　TantosyaCD AS '担当者', SUM(Uriagegaku) AS '売上額'
FROM @UriageData
GROUP BY TantosyaCD





--結果からトップ３を抽出する
SELECT AllData.rank AS 'ランク', AllData.tanto AS '担当者', AllData.uriage AS '売上額'
FROM (SELECT DENSE_RANK() OVER(ORDER BY SUM(Uriagegaku) DESC) AS rank,
TantosyaCD AS tanto, SUM(Uriagegaku) AS uriage
FROM @UriageData
GROUP BY TantosyaCD
) AS AllData
WHERE AllData.rank BETWEEN 1 AND 3





--グループ内でそれぞれランキングを付ける（PARTITION BYを付加します）
--その前にわかりやすい結果になるようちょっとデータを直します
INSERT INTO @UriageData(GroupCD, TantosyaCD, UriageDate, Uriagegaku) VALUES(1, 1, '2008/01/11', -5000)

SELECT RANK() OVER(PARTITION BY GroupCD ORDER BY SUM(Uriagegaku) DESC) AS 'ランク', GroupCD AS 'グループ', TantosyaCD AS '担当者', SUM(Uriagegaku) AS '売上額'
FROM @UriageData
GROUP BY TantosyaCD, GroupCD





--余談ですがROW_NUMBER()を使うと単純に結果レコードに番号を振ります
SELECT ROW_NUMBER() OVER(ORDER BY SUM(Uriagegaku) DESC) AS 'Ｎｏ',
　　　　TantosyaCD AS '担当者', SUM(Uriagegaku) AS '売上額'
FROM @UriageData
GROUP BY TantosyaCD

大文字小文字区別するＳＱＬ（SQLServer）

今さらですが・・・通常大文字と小文字を区別するかどうかはSQLserverのインストール時に決定されるが、
もし区別しない形式でインストールしてしまった場合は、COLLATE句を付けてSQL文を記述するとよい。

SELECT * FROM testTB WHERE name = 'STDMAN' COLLATE Japanese_CS_AS_KS_WS

・CI　大文字、小文字を区別しない。全角アルファベットの大文字、小文字も区別しない
・CS　大文字、小文字を区別する。全角アルファベットの大文字、小文字も区別する
・AI　アクセント、濁音、破裂音を区別しない。
・AS　アクセント、濁音、破裂音を区別する。
・KS　ひらがなとカタカナを区別する。半角でも区別する
・WS　文字幅を区別する。（全角半角を区別する）

詳しくはSQLServer開発チームのブログ(2008/06/16)を参考にしてください。


また、サーバー照合順序はインストール後でも変更することはできるが、大変そうです。
SQLServer2005：http://msdn.microsoft.com/ja-jp/library/ms179254.aspx

マネーフォーマット（３桁ごとのカンマ区切り）

数値を999,999,999形式に変換し戻り値は文字列とする。
ここではSQL文の中に組み込みやすいユーザ定義関数（スカラ関数）を利用します。


CREATE FUNCTION [dbo].[MoneyFormat]
(
@value money --対象数値
)
-- 三桁カンマのある文字列形式で返します
RETURNS varchar(20)
AS
BEGIN
-- moneyデータ型からvarchar に変換します
-- 最後に .00 を削除します
RETURN REPLACE(CONVERT(varchar, @value, 1), '.00', '')
END

--実行してみる

select dbo.MoneyFormat(999999999) AS res

０"ゼロ"フォーマット

数値を000001形式に変換し戻り値は文字列とする。
ここではSQL文の中に組み込みやすいユーザ定義関数（スカラ関数）を利用します。

CREATE FUNCTION [dbo].[IntZeroFormat]
(
@value INT,　--対象数字
@size INT　　--桁数
)
RETURNS varchar(20)
AS
BEGIN
　　RETURN RIGHT(REPLICATE('0', @size) + CONVERT(VARCHAR(26), @value), @size)
END

--実行してみる。
--10 → 00010に変換する。

SELECT dbo.IntZeroFormat(10, 5) AS res

SQLServer ストアドの実行権限を与えるストアド

通常はストアドを作成後、実行できるユーザを設定します。
しかしDBを他のサーバに移した場合など再設定が必要な場合があります。
ひとつずつ設定するのは面倒なので、指定ユーザを一気に設定するストアドを作ってしまおう。
※エラー処理は省略しています。必要な方はご自分で考えてみてください。

CREATE PROCEDURE [dbo].[GRANT_ADD]
　(
　　　--パラメータ　許可を与えるユーザーを指定します（DBに存在しログインできる状態のユーザ）
　　　@UserName NVARCHAR(256) = ''
　)
AS
--[DBユーザ]に実行権限を与える

　SET NOCOUNT ON

　--変数定義
　DECLARE @objectname NVARCHAR(256), @cur_cnt INT, @loop_cnt INT
　DECLARE @sql NVARCHAR(100)

　--カーソル定義（ストアド名を抽出）
　DECLARE Cur CURSOR LOCAL SCROLL STATIC
　FOR
　SELECT NAME FROM sysobjects WHERE OBJECTPROPERTY(id, N'IsProcedure') = 1
　　　　　　　　　　　　　　　　　　　　　　　　　　　　　AND name NOT LIKE 'dt_%'

　--カーソルをオープンする
　OPEN Cur

　SET @loop_cnt = 0

　IF (@@CURSOR_ROWS <> 0)
　BEGIN

　　--@@が付くときはグローバルなので念のため別途保持しておく
　　SET @cur_cnt = @@CURSOR_ROWS
　　
　　WHILE (@cur_cnt <> @loop_cnt)
　　BEGIN
　　　　--ストアド名を@objectnameへ
　　　　FETCH NEXT FROM Cur INTO @objectname

　　　　--実行権限を与えるSQLの発行
　　　　SET @sql = 'GRANT EXECUTE ON [dbo].[' + @objectname + '] TO [' + @UserName + ']'
　　　　--SQL実行
　　　　EXEC sp_executesql @sql

　　　　--次のストアドへ
　　　　SET @loop_cnt = @loop_cnt + 1
　　END
　END
　ELSE
　BEGIN
　　　PRINT 'ストアドが見つかりません'
　END

----------------------------------------------------------------------------------

あとは以下を実行して完了！（ただしログインユーザは先に作っておいてね）
EXEC GRANT_ADD @UserName = 'ExecUser'

※ExecUserは自分の環境に合わせてください。

SQLServer トランザクションログ 肥大化

気がついたら、トランザクションログファイル（拡張子LDF）が××Gb！

通常は、トランザクションログファイルのバックアップを実行すると、ログの切捨てが自動で行われ圧縮されるのですが、バックアップ中にドライブの空き領域が不足してバックアップが実行できない！なんてことがあります。

ログの管理が不要であれば、ログだけ切り捨てる方法があります。

以下のSQLを実行
ただし、データベースファイルがひとつの場合に限定されますので注意

BACKUP LOG データベース名 WITH TRUNCATE_ONLY



これを実行すると、ログの切捨てが即実行されます。
ただし物理的にファイルは小さくはなりませんので次にデータベースの圧縮を行います。


以下のSQLを実行

DBCC SHRINKFILE( ’ 論理ファイル名 ’)

これによって圧縮が行われます。

※トランザクションログも定期的にバックアップしておきましょう。

例）
データベース名がmydbの場合

use mydb

--圧縮前のサイズ確認など
SELECT * FROM sys.database_files

--理論ファイル名の取得
DECLARE @FileName varchar(max)
SELECT @FileName = name FROM sys.database_files

--ログ切り捨て
BACKUP LOG mydb WITH TRUNCATE_ONLY

--ファイルの圧縮
DBCC SHRINKFILE( @FileName )

--圧縮後のサイズ確認など
SELECT * FROM sys.database_files

GO

SQLServer MDF LDFファイルからの復旧

SQLServerマシンが故障！

幸いハードディスクの中身はみられるようだ。
バックアップを取っていなかったデータベースが・・・だが、MDFとLDFファイルは救えた。
新サーバにそのファイルを配置して以下のSQLを実行。

EXEC sp_attach_db
　　　　　@dbname = N'データベース名',
　　　　　@filename1 = N'パス指定￥＊＊＊.MDF',
　　　　　@filename2 = N'パス指定￥＊＊＊.LDF'

救出したMDFとLDFがそのまま使えるようになります。