SQL Serverネタです。

Windows8.1、SQL Server 2012の環境でSQL Server Configuration Managerを立ち上げようと思いアプリケーションを探したところすぐに見つかりませんでした。
そこで、備忘録です。

SQL Server 構成マネージャーは Microsoft 管理コンソール プログラムのスナップインであり、スタンドアロン プログラムではありません。そのため、Windows 8 を実行している場合、SQL Server 構成マネージャーはアプリケーションとして表示さないそうです。
SQL Server 構成マネージャーを立ち上げるには、「SQLServerManager11.msc」(SQL Server 2012 の場合) または「SQLServerManager10.msc」(SQL Server 2008 の場合) を検索に入力してManagement Saved Consoleを探します。

多分、検索結果に表示され（ると思い）ます。
クリックすれば、Microsoft Management Consoleが立ち上がりSQL Server Configuration Managerが使用できます。

以上、SQL Sever絡みの備忘録まで。

DTSXでDB接続PASSWORDをConifgから参照させるように手動で修正する

手元の開発PCのSql Server Business Intelligence Development Studioのパッケージ構成ツールが動かなくなっていました。。。
SQL SERVER 2008やVisual studio 2008やVisual studio 2010などを入れてしまったので、何かおかしくなってしまったのかと思います。

Sql Server Business Intelligence Development Studio 2005で過去に作成したDTSXを外部のConfigを参照させるようにしたいため、手で書き換えてやることにしました。
その備忘録です。

１）まずは、外部のConfigファイルを参照できる権限を与えます。
DTSXを作った当初は、

<DTS:Property DTS:Name="EnableConfig">0</DTS:Property>

　　　になっています。
　　　そこで、

<DTS:Property DTS:Name="EnableConfig">-1</DTS:Property>

　　　のように値を修正します。

２）DBのPASSWORDを保存している箇所を修正します。
　　　今までのPASSWORDの保存方法でどのように格納されているかで変わってきますが、

　　　<DTS:Password DTS:Name="Password" Sensitive="1" Encrypted="1">********</DTS:Password>

　　　等々になっているとこを

　　　<DTS:Password DTS:Name="Password" Sensitive="1"></DTS:Password>

　　　にしてやります。

３）Gonfigファイルの在処を追加します。
　　　DTSIDの値は、ConnectionManagerあたりののDTSIDのコピーで私の環境では動きました。

　　　
<DTS:ConnectionManager></DTS:ConnectionManager>

　　　の次に追加します。

　　　<DTS:Configuration>
　　　　　<DTS:Property DTS:Name="ConfigurationType">1</DTS:Property>
　　　　　<DTS:Property DTS:Name="ConfigurationString">ここにファイルの在処を記述</DTS:Property>
　　　　　<DTS:Property DTS:Name="ConfigurationVariable"></DTS:Property>
　　　　　<DTS:Property DTS:Name="ObjectName">構成 </DTS:Property>
　　　　　<DTS:Property DTS:Name="DTSID">{ConnectionManagerのDTSIDをコピー}</DTS:Property>
　　　　　<DTS:Property DTS:Name="Description"></DTS:Property>
　　　　　<DTS:Property DTS:Name="CreationName"></DTS:Property>
　　　　</DTS:Configuration>

４）Configファイルを作成し指定したフォルダに保存します。

　　　<DTS:ConnectionManager>

　　　で記述した

<DTS:Property DTS:Name="ObjectName">DB接続名１</DTS:Property>

を指定します。
　　　ファイル内容は以下のとおり。ファイル名は適当にSetting.xmlとかに。

　　<?xml version="1.0"?>
　　<DTSConfiguration>
　　　　<Configuration ConfiguredType="Property" Path="\Package.Connections[DB接続名１].Properties[Password]" ValueType="String">
　　　　　<ConfiguredValue>PASSWORD</ConfiguredValue>
　　　　</Configuration>
　　</DTSConfiguration>

以上で、実行時に指定されたConfigファイルのPASSWORDを見に行くようになります。

今日はSQLの処理がどこまで進んだか経過を見たい(keyのmax値を取得したい)等の場合、テーブルにロックが掛かってしまい値が取れない時に、ダーティーリードを行う備忘録です。
普段の処理ではあやふやなデータが返ってくるためほとんど使用しません。
そもそもコミット前なので、ロールバックすることも有るし。
で、データの一括修正を行って居て、現在どのへんまで終わったか、全体が終わるのはいつかなどの目安の時間を知るためなどに使用します。

sql文はヒント「NOLOCK」を付けるだけの簡単なものです。
　select * from テーブル名 WITH(NOLOCK)

簡単なテストをシたい場合は、以下のようにすることで確かめられます。
１）まずは、テスト用テーブル作成

create table ##tbl1(
 id int,
 val int 
)

２）次に、SQLの挿入処理・・・時間を稼ぐ為にWAITFOR DELAY 使用

begin tran
  --	delete from ##tbl1
  declare @i int
  set @i=0

  while @i<100
   begin	
    insert into ##tbl1 values(@i,1)
    WAITFOR DELAY '00:00:03'
    set @i=@i+1
   end
commit tran

３）select文の発行
上の挿入SQLと異なるクエリウィンドウを立ち上げてチェック実施
また、下のSQLの個別のクエリウィンドウで確認した方がわかりやすい。

select max(id) from ##tbl1                        --・・・・commit されるまで返って来ない
select max(id) from ##tbl1 WITH(NOLOCK) --・・・・直ぐに返って来る

※）注意
　NOLOCKヒントを付けても、テーブルのスキーマ変更やSQLServerが内部で統計情報を更新するときに発生するスキーマロックが掛かっていると、待たされます。
　なので、必ず想定する処理時間内に戻ってくるとは限りませんので、重要な仕事で使用時にはタイムアウト等考えておきましょう。

以上です。

SQL SERVERで簡単なロジックテストを行う際、仮のテーブルを作成して動作を確認したい場合が有ります。
そんな時、今までは、
　　declare @tbl1 table(id int, val int)
　　insert into @tbl1 values(1,10)
　　insert into @tbl1 values(1,20)
　　insert into @tbl1 values(2,10)
　　select * from @tbl1

のようにデータを作成していました。
それが、知らぬ間にSQL SERVER 2008から次のようなSQLでできるようになったそうです！
えー、ちなみに確認は、SQL SERVER 2012でしか行なっていません。。。

　　declare @tbl1 table(id int, val int)
　　insert into @tbl1 values(1,10)
　　, (1,20)
　　, (2,10)
　　select * from @tbl1

元々コピペをすれば大した手間では無いですが、なんとなく損していた気がしています＾＾；

ではでは。

SQL ServerでSleepをしたいと思ったことありませんか？
そんな場合は、WAITFOR ステートメントで実現できます。

例）　WAITFOR DELAY ’00:00:20’　–２０秒待ち

簡単ですね＾＾
詳しくは、WAITFOR (Transact-SQL)まで

久しぶりにSQL Serverの開発備忘録です。
seoばかりアップしてて、開発備忘録では検索順位が悪いので、開発備忘録を多めに書きます＾＾

で、順位付け関数ですが、先日同僚が使って居るのを見て、整理しようと思いたちました。
順位付け関数には、

1. row_number
2. rank
3. dense_rank
4. ntile

の4種類が有ります。

基本的な使用方法は、
　関数() over(order by カラム名)
　ntile(数値）over(order by カラム名)
の様な感じで書きます。
overの中には、パーティションも入れることができ、例えば部門やカテゴリ内での順位付けもできます。
そんな場合は、関数() over(partition by 部門,カテゴリ order by カラム名) のようになります。

それぞれの関数の返す値ですが、

1. row_numberは、overで指定された条件の結果セットに基づいて連続した数値を返します。なので、oder byで指定したカラムの値が同じ場合、どちらの行が上に来るかは保証は有りません。
2. rankは、ランキングなので、oder byで指定したカラムの値が同じ場合、同位となり、次に来る行の順位は飛ぶことになります。1位1位3位という感じですね。
3. dense_rankは、rankに似ていますが、順位が飛ぶことがなく1位1位2位のようになります。
4. ntileは、グループ分けをしてくれる感じです。ntile(4)とするとoverで指定された条件の結果セットに基づいて４行づつ同じ数値を返してくれます。ただし、行が4で割れないと後ろの方のグループで3行とかに調整されます。

具体的な例を以下に示します。
データ：
declare @tbl1 table(employ_cd int,section_cd int, uriage_kingaku int, rieki_kingaku int)
insert into @tbl1 (employ_cd, section_cd, uriage_kingaku,rieki_kingaku) values (000000001,001,180,10)
insert into @tbl1 (employ_cd, section_cd, uriage_kingaku,rieki_kingaku) values (000000011,001,150,9)
insert into @tbl1 (employ_cd, section_cd, uriage_kingaku,rieki_kingaku) values (000000012,001,220,15)
insert into @tbl1 (employ_cd, section_cd, uriage_kingaku,rieki_kingaku) values (000000013,001,210,14)
insert into @tbl1 (employ_cd, section_cd, uriage_kingaku,rieki_kingaku) values (000000014,001,180,11)
insert into @tbl1 (employ_cd, section_cd, uriage_kingaku,rieki_kingaku) values (000000015,001,190,12)
insert into @tbl1 (employ_cd, section_cd, uriage_kingaku,rieki_kingaku) values (000000016,001,200,13)
insert into @tbl1 (employ_cd, section_cd, uriage_kingaku,rieki_kingaku) values (000000017,001,210,14)
insert into @tbl1 (employ_cd, section_cd, uriage_kingaku,rieki_kingaku) values (000000018,001,220,15)
insert into @tbl1 (employ_cd, section_cd, uriage_kingaku,rieki_kingaku) values (000000019,001,200,10)
insert into @tbl1 (employ_cd, section_cd, uriage_kingaku,rieki_kingaku) values (000000020,002,200,11)
insert into @tbl1 (employ_cd, section_cd, uriage_kingaku,rieki_kingaku) values (000000030,002,250,15)
insert into @tbl1 (employ_cd, section_cd, uriage_kingaku,rieki_kingaku) values (000000040,002,220,15)
insert into @tbl1 (employ_cd, section_cd, uriage_kingaku,rieki_kingaku) values (000000050,002,210,10)
insert into @tbl1 (employ_cd, section_cd, uriage_kingaku,rieki_kingaku) values (000000060,002,220,11)
insert into @tbl1 (employ_cd, section_cd, uriage_kingaku,rieki_kingaku) values (000000070,002,230,12)
insert into @tbl1 (employ_cd, section_cd, uriage_kingaku,rieki_kingaku) values (000000080,002,240,13)
insert into @tbl1 (employ_cd, section_cd, uriage_kingaku,rieki_kingaku) values (000000090,002,250,14)

検索：
select row_number() over(order by rieki_kingaku desc, uriage_kingaku desc) as row_number
, rank() over(order by rieki_kingaku desc, uriage_kingaku desc) as rank
, dense_rank() over(order by rieki_kingaku desc, uriage_kingaku desc) as dense_rank
, ntile(3) over(order by rieki_kingaku desc, uriage_kingaku desc) as ntile, * from @tbl1 S1

結果：
row_number rank dense_rank ntile employ_cd section_cd uriage_kingaku rieki_kingaku
———- —- ———- —– ——— ———- ————– ————-
1      1      1      1      30      2      250      15
2      2      2      1      40      2      220      15
3      2      2      1      12      1      220      15
4      2      2      1      18      1      220      15
5      5      3      1      90      2      250      14
6      6      4      1      17      1      210      14
7      6      4      2      13      1      210      14
8      8      5      2      80      2      240      13
9      9      6      2      16      1      200      13
10      10      7      2      70      2      230      12
11      11      8      2      15      1      190      12
12      12      9      2      60      2      220      11
13      13      10      3      20      2      200      11
14      14      11      3      14      1      180      11
15      15      12      3      50      2      210      10
16      16      13      3      19      1      200      10
17      17      14      3      1      1      180      10
18      18      15      3      11      1      150      9

少し見辛いですが、こんな感じに並びます。
partitionを加えた結果は出しませんが、SQLはこんな感じになります。

select row_number() over(partition by section_cd order by rieki_kingaku desc, uriage_kingaku desc) as row_number
, rank() over(partition by section_cd order by rieki_kingaku desc, uriage_kingaku desc) as rank
, dense_rank() over(partition by section_cd order by rieki_kingaku desc, uriage_kingaku desc) as dense_rank
, ntile(3) over(partition by section_cd order by rieki_kingaku desc, uriage_kingaku desc), * from @tbl1 S1

SQL Server 2012のFILETABLEを少し試してみました時の備忘録です。

まず、FILETABLEって？ですが、実は私もほとんど判ってません＾＾；
ですので、試してみようってことです。。。あしからず＾＾；；

で、基本的にどんな機能かというと、
SQL　Server　2008からのFILESTREAM機能を利用して、ファイルシステムの
ファイルをDATABASEに保存するものです。

うーん・・・という感じですが、使用局面は、
ファイルサーバに共有ファイルをおいておきた場合、古いファイルが整理もつかずに
どんどん溜まっていきませんか？
そんな時、DB内にファイルが置いてあると、ファイル作成日、最終更新日、最終アクセス日
を条件に不要と思われるファイルを（バックアップをして）削除したり、他の場所に移動したり
通常のデータのように管理ができます。
ファイルシステム管理のバッチやスクリプトで同じ事をするよりも、簡単に管理ができるか
なってとこが考えられます。
その他、きっといろいろなメリットが有る？と思います＾＾；；
まだこれから試すので・・・詳しくは・・・

それから、ファイルの保存はプログラムからでもWindows操作（ドラッグ＆ドロップ）でも
どちらでも可能です。試してませんが。。。
ディレクトリは、\\サーバ名\Windows共有名\ディレクトリ名でネットワークディレクトリの
ように表示されますが、直接編集とかはできません。一度、どこかにコピーしてそこから
編集する操作に成ります。編集後の保存は元のディレクトリに戻してあげます。

FILETABLEを使用するためには幾つかの手順を踏む必要があります。
[SQL Server] 2012 FILETABLEを使うためにFILESTREAMの有効化

[SQL Server] 2012 データベース レベルでの FILESTREAM ファイル グループの指定

を参考にしてください。

その後、下の様な作成SQLを流せば、準備完了です。
USE TEST
GO

CREATE TABLE FileTable01 AS FILETABLE
WITH
(
FILETABLE_DIRECTORY = ‘DocumentTable’,
FILETABLE_COLLATE_FILENAME = database_default
)
GO

CREATE TABLE FileTable02 AS FILETABLE
WITH
(
FILETABLE_DIRECTORY = ‘開発DOC’,
FILETABLE_COLLATE_FILENAME = database_default
)
GO

ちなみに、作成したテーブルを[SQL Server Management Studio]で右クリックして
[FileTabelディレクトリの検索]をクリックすると、作成したFILETABLEのディレクトリが
開き、ファイルの一覧が表示されます。削除などもできます。

とりあえず、ここまで。。。。

SQL　Server　2012の機能のFILETABLEを使用するためには、
予め「インスタンス レベルでの FILESTREAM の有効化」 を実施した後、
データベース レベルでの非トランザクション アクセスの有効化が必要になります。
また、データベース レベルでの FileTable のディレクトリ指定も必要です。
この2つの処理は一緒にできます。

なぜ必要になるか？、私にも判っていませんが、必要だそうです。
そもそも、非トランザクションとは。。。というのも判っていません。
トランザクション処理を全く実行されないモード？という曖昧な感じで考えている程度です＾＾；

ちなみに、使用できる非トランザクション アクセスのレベルは、FULL、READ_ONLY、および OFF です。
OFF・・・・・・・・・・・通常。普通のトランザクションを実施するモード
FULL・・・・・・・・・・完全アクセス。制限なしでアクセスできるモード
READ_ONLY・・・読み取り専用アクセス。読み取り専用モード
。。。。。。だと思います＾＾；；；

また、ファイルシステムとして FIleTable にアクセスをする際に使用するパスは、FILESTREAM ディレクトリ名に指定します。

では、設定方法に
　　既存のDATABASEに変更を加えます。
ALTER DATABASE TEST3
SET FILESTREAM ( NON_TRANSACTED_ACCESS = FULL, DIRECTORY_NAME = N’C001′ )

　　ちなみに、データベースのプロパティ設定画面からでも指定できます。
　　その場合は、「オプション」の一番上にFILESTREAMが表示されていますので
　　そのFILESTREAMディレクトリ名、FILESTREAM 非トランザクション アクセスを設定します。

　　FILESTREAMディレクトリ名は、データベースの中で一意になるようにする必要が有ります。
　　もし、幾つかに分ける場合、重ならないように設計する必要があります。

　　新規のDATABESEの場合、以下のようにWITH　FILESTREAM句を指定します。
　　　WITH FILESTREAM ( NON_TRANSACTED_ACCESS = FULL, DIRECTORY_NAME = N’C001′ )

以上で、データベース レベルでの非トランザクション アクセスの有効化が設定できました。

SQL　Server　2012の機能のFILETABLEを使用するためには、
予め「インスタンス レベルでの FILESTREAM の有効化」 を実施した後、
データベース レベルでの FILESTREAM ファイル グループの指定が必要になります。
FILESTREAM は特殊なファイル グループを使用するので、データベースの作成時に少なくとも 1 つのファイル グループに対して CONTAINS FILESTREAM 句を指定する必要があるからです。

新規でDATABASEを作成する場合は、CREATE　DATABASEからも指定できます。
下の例は、TEST２というDATABASEを作成し、FileStreamGroup1 というFILESTREAM ファイル グループを指定しました。
CREATE DATABASE TEST2
ON
PRIMARY ( NAME = TEST2,
FILENAME = ‘C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\TEST2.mdf’),
FILEGROUP FileStreamGroup1 CONTAINS FILESTREAM( NAME = TEST2fs,
FILENAME = ‘C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\filestream2’)
LOG ON ( NAME = TEST2log1,
FILENAME = ‘C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\TEST2log1.ldf’)
GO

既存のDATABASEに追加する場合は、ALTER　DATABASEを使用します。
下の例は、TEST３という既存のDATABASEに対し、
■FileStreamGroup３ というFILESTREAM ファイル グループを指定し、
ALTER DATABASE TEST3
ADD FILEGROUP FileStreamGroup3 CONTAINS FILESTREAM

■そのファイルストリームにfilestream3というファイルを追加しましましす。
ALTER DATABASE TEST3
ADD FILE
( NAME = ‘TEST3fs’,
FILENAME = ‘C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\filestream3’
)
TO FILEGROUP FileStreamGroup3

ALTER DATABASEを使用しないで、データベースのプロパティ設定画面からも指定することもできます。
まずは、目的のDATABASEのプロパティ画面を開きます。
■ファイルグループを追加

1）左側メニューの「ファイルグループ」をクリック
2）FILESTREAMの追加をクリック
3）名前を入力
4）ファイルグループを追加できたので、次のファイル指定をするために左側メニューの「ファイル」をクリック
■ファイルの追加

1）データベースファイルを追加する為に、右下の「追加」をクリック
2）新たに行が追加されるので、その行のファイルの種類をFILESTREAMに指定
3）ファイルグループの指定は、先程上で作成したファイルグループを選択
4）パスを指定
5）最後に「OK」をクリック

以上で、DATABASEにFILESTREAM ファイル グループを指定することができました。

SQL　Server　2012の機能のFILETABLEを使用するためには、
予め「インスタンス レベルでの FILESTREAM の有効化」 が必要になります。

その設定方法の備忘録です。

1. [スタート] ボタンをクリックし、[すべてのプログラム]、[Microsoft SQL Server コードネーム 2012]、[構成ツール] の順にポイントして、[SQL Server 構成マネージャー] をクリックします。
2. サービスの一覧で、[SQL Server のサービス] を右クリックし、[開く] をクリックしす。
3. [SQL Server 構成マネージャー] スナップインで、FILESTREAM を有効にする ためにSQL Server のインスタンスを探します。画像では、上から3番目のSql Server（MSSQLSERVER）



4. インスタンスを右クリックし、[プロパティ] をクリックしSql Server（MSSQLSERVER）のプロパティ画面を開きます。



5. [SQL Server のプロパティ] ダイアログ ボックスで、[FILESTREAM] タブをクリックします。
6. [Transact-SQL アクセスに対して FILESTREAM を有効にする] チェック ボックスをオンにします。
7. Windows から FILESTREAM データの読み取りと書き込みを行う場合は、[ファイル I/O ストリーム アクセスに対して FILESTREAM を有効にする] をクリックします。 Windows 共有の名前を [Windows 共有名] ボックスに入力します。
8. この共有に格納された FILESTREAM データにリモート クライアントからアクセスする必要がある場合は、[リモート クライアントに FILESTREAM データへのストリーム アクセスを許可する] を選択します。
9. [適用] もしくは[OK] をクリックします。Sql Server（MSSQLSERVER）のプロパティ画面の設定はここまでです。
10. SQL Server Management Studioを開き、[新しいクエリ] をクリックしてクエリ エディターを表示します。
11. クエリ エディターで、次の Transact-SQL コードを入力します。
    Transact-SQLEXEC sp_configure filestream_access_level, 2
    RECONFIGURE
12. [実行] をクリックします。
13. SQL Server サービスを再開します。

以上で、「インスタンス レベルでの FILESTREAM の有効化」が出来ました。
尚、データベース レベルで以下の設定が必要です。
　　・データベース レベルでの FILESTREAM ファイル グループの指定
　　・データベース レベルでの非トランザクション アクセスの有効化
　　・データベース レベルでの FileTable のディレクトリ指定