SQL Server PowerShell を使ってBulk Insertを実行する

SQL Serverに大量データをインサートする場合、Bulk Insertをよく利用しています。
利用するたびにSQL Server Management Studioを起動していますが、環境によっては起動が遅いので困っています。
より早く、簡単、そして手軽に実行するためSQL Server PowerShell を使ってBulk Insertを実行するサンプルを書いてみました。

SQL Server PowerShellをインストールする方法～SQLPS モジュール

PowerShell からSQL Serverを管理したい、その場合はSQLPS モジュールが必要です。
いつもはSQL Server Express+SSMS(SQL Server Management Studio)をインストールしているので気にしてなかったのですが、個別にSQL Serevr PowerShellを使用する方法を理解していませんでした。
今回は、SQL Server PowerShellをインストールする方法をメモ。

SQL Server 2014 へ接続するための注意点とSQL Server Native Clientのバージョンについて

クライアントPCからSQL Server 2008 R2 Expressへ接続するための設定方法は約2年前の記事「クライアントPCからSQL Serverにリモート接続やコネクトする方法」で説明しました。
最新のSQL Server 2014 でも同じ設定方法が可能なのか？を確認してみました。
また、クライアントにインストールするSQL Server Native Clientのバージョンについても気になる点がありましたのでメモします。

SQL Server 複数バックアップ ファイルからDBを復元する～エラー3231

知人から質問を受けた「SQL Server の複数バックアップ ファイルからデータベースを復元する方法」をメモ。
基本的な内容ですが、知らずに復旧作業を行うと焦るんじゃないかと思います。
なお、ほかのデータベースに復元する時にも使えると思います。

SQL Server 2014 CU 1（Cumulative Update 1）がリリースされました

昨日インストールしたSQL Server 2014のCU 1（Cumulative Update：累積的更新プログラム）を友人から教えていただいたのでメモ。
私の場合、今のところ適用しなくてはならない状況ではないですね。

Visual Studio 「An incompatible SQL Server version was detected.」が表示されたら「Microsoft SQL Server Data Tools」をインストールしよう

開発者ではない私ですが、とある目的のためVisual Studio 2013 Professionalを購入。
検証環境にSQL Server 2014 Expressを導入したので、Visual Studio 2013 から接続確認をしてみました。
予想外なエラー メッセージ「An incompatible SQL Server version was detected.」に遭遇、インストールすべきソフトウェアを入れていなかった事が分かったのでメモ。

Windows Server バックアップが失敗する～VSSと0x800423F4

「Windows Server バックアップの失敗」になる現象と少し格闘、無事解決しました。
今回は、現象および調査方法等についてメモ。
Windows Server バックアップは、WHS2011やWSE 2012R2にも搭載されている機能です。
バックアップ関連のトラブル シューティング時に役立つはずです。

今回の環境

• Windows Server 2008 R2
• SQL Server 2008 R2 SP2 Express Edition
• WORKGROUP（非ドメイン環境）
• Windows Server バックアップを使用（BMR：Bare Metal Recovery）

現象

• 毎日、Windows Server バックアップが失敗する

イベント ログの内容はこの後に説明します。

原因および対処法

今回の原因は、

1. SQL Server のmdfおよびldfファイルが破損していた
2. VSSのライターが異常を検出
3. Windows Server バックアップが完了せずエラーを出力

したためでした。
SQL Serverは各種バックアップを行っているので、バックアップ ファイルから復元し、対処しました。

調査方法

ここで大切なのが調査方法です。
私自身「調査方法が確立できていなかった」ので、実際に調査した内容をまとめておきます。
最初に、Windows Server バックアップはVSS(ボリューム・シャドウコピー・サービス）を使用するため、VSS周りを調査します。

VSS Writerの状態をチェック

コマンド プロンプトから下記コマンドを実行し、各アプリケーションのWriterのステータスをチェックします。

vssadmin list writers

下記ステータスはサンプルになります。
[状態]および[最後のエラー]をチェックし、エラーがあればそのアプリケーションを調査します。
修復前後のログは下記リンクから閲覧できます。

http://sdrv.ms/1h1r3T6

正常ステータス

ライター名: 'Task Scheduler Writer'
   ライター Id: {xxxxxx}
   ライター インスタンス Id: {xxxxxx}
   状態: [1] 安定
   最後のエラー: エラーなし

異常ステータス

ライター名: 'SqlServerWriter'
   ライター Id: {xxxxxx}
   ライター インスタンス Id: {xxxxxx}
   状態: [8] 失敗
   最後のエラー: 再試行できないエラー

イベント ビューア

ソース：Backup
イベントID：521
レベル：エラー
バックアップ対象のボリュームのシャドウ コピーを作成するボリューム シャドウ コピー サービスの操作が、次のエラー コード '2155348129' により失敗したため、'‎2013‎-‎12‎-‎11T23:30:45.388000000Z' に開始したバックアップ操作は失敗しました。イベントの詳細で解決策を確認し、問題の解決後にバックアップ操作を再実行してください。

ソース：VSS
イベントID：8229
レベル：警告
エラー 0x800423f4, ライターで一時的でないエラーが発生しました。バックアップ処理を再試行しても、
おそらくエラーは再発します。
により、VSS ライターはイベントを拒否しました。イベントの処理中に VSS ライターがライター コンポーネントに加えた変更は、要求側では利用できません。 VSS ライターをホストしているアプリケーションからの関連イベントについては、イベント ログを参照してください。
操作:
   PrepareForSnapshot イベント
コンテキスト:
   実行コンテキスト: Writer
   ライター クラス ID: {xxxx}
   ライター名: SqlServerWriter
   ライター インスタンス名: SQL Server 2008 R2:SQLWriter
   ライター インスタンス ID: {xxxx}
   コマンド ライン: "C:\Program Files\Microsoft SQL Server\90\Shared\sqlwriter.exe"
   プロセス ID: 2416

Microsoft Virtual Academy(MVA)で勉強してみませんか？

以前から提供されているコンテンツですが、マイクロソフトではMicrosoft Virtual Academyというオンラインのトレーニング・サイトを提供しています。
Windows ServerやSQL Server、開発等、IT Proやデベロッパーを対象とした製品のテクノロジーを勉強できます。
スキルアップしたい！と思っている方は是非アクセスしてみてください。
いくつかおススメしたいコンテンツをメモ。

Windows

• Windows 7 を展開する
• Windows 7 への移行の基礎知識 ～大規模移行のポイント～

Windows Server

• Windows Server 2012 管理のための Windows PowerShell 3.0 新機能解説
• 初めての Windows Server 2012
• Windows Server 2012 のバックアップ
• Windows Server 2012 におけるネットワーク アクセス保護
• Windows Server 2012 Hyper-V 徹底解説
• Hyper-V 仮想化管理のシナリオ
• Windows Server 2012 のストレージ新機能
• Windows Server 2012 における ネットワークインフラストラクチャ アップデート

SQL Server

• SQL Server 2012 の全体像を理解する

Office

• Web 時代の新しい Office 開発に備えよう～高度な Apps for Office/SharePoint 開発のための前提知識

Develop

• Windows ストア アプリ開発概要と Internet Explorer 11
• Windows 8.1 ストア アプリで周辺機器を制御する～基礎編～

SQL Server 認証を使用しBulk Insert ステートメントを実行する方法

SQL Serverに対し大量のレコードをインサートするする方法として、BULK INSERT ステートメントをいつも利用しています。
通常はWindows 認証を使用していますが、SQL Server 認証を使用した経験がなかったのでテストし内容をメモします。
仕様を知るって大事ですね。

msdn ライブラリの説明では分からなかった・・・

今回の内容は下記msdn ライブラリにしっかり説明されています。

BULK INSERT または OPENROWSET(BULK...) を使用した一括データのインポート(SQL Server) 

しかし、私の場合、当初はライブラリを読んだだけでは半分も理解できませんでしたが、実機でテストしていくと徐々に内容が分かりました。
下記引用文は、今回の中核をなす大切な文章です。

セキュリティの注意点

ユーザーが SQL Server ログインを使用する場合は、SQL Server プロセス アカウントのセキュリティ プロファイルが使用されます。 SQL Server 認証を使用したログインは、データベース エンジン以外では認証できません。 そのため、SQL Server 認証を使用したログインによって BULK INSERT コマンドが開始されると、SQL Server プロセス アカウント (SQL Server データベース エンジン サービスで使用されるアカウント) のセキュリティ コンテキストを使用してデータへの接続が行われます。 ソース データを正しく読み取るには、SQL Server データベース エンジンで使用されるアカウントに対して、ソース データへのアクセス権を付与する必要があります。これに対して、SQL Server ユーザーが Windows 認証を使用してログインした場合、そのユーザーは SQL Server プロセスのセキュリティ プロファイルに関係なく、そのユーザー アカウントでアクセス可能なファイルのみを読み取ることができます。

たとえば、SQL Server のインスタンスに Windows 認証を使用してログインしたユーザーを考えます。 ユーザーが BULK INSERT または OPENROWSET を使用してデータ ファイルから SQL Server テーブルにデータをインポートするには、アカウントにデータ ファイルの読み取りアクセス許可が与えられていなければなりません。 データ ファイルへのアクセスで、SQL Server プロセスにそのファイルへのアクセス許可がなくても、ユーザーはそのファイルからテーブルにデータをインポートできます。 SQL Server プロセスにファイル アクセス許可を与える必要はありません。
SQL Server および Microsoft Windows では、認証されている Windows ユーザーの資格情報を転送することで、SQL Server インスタンスから別の SQL Server インスタンスへ接続できるように構成することが可能です。 この設定を、"権限借用" または "権限委譲" といいます。 BULK INSERT または OPENROWSET を使用する場合は、SQL Server バージョンによってユーザーの権限借用のセキュリティがどのように処理されるかを理解しておくことが重要です。 ユーザーの権限を借用することで、SQL Server プロセスまたはユーザーのいずれかが使用しているコンピューターとは異なるコンピューターにデータ ファイルを常駐させることができます。 たとえば、Computer_A 上のユーザーが Computer_B 上のデータ ファイルにアクセスでき、資格情報の委任が適切に設定されている場合、このユーザーは、Computer_C 上で実行されている SQL Server のインスタンスに接続して、Computer_B 上のデータ ファイルにアクセスし、そのファイルから Computer_C 上のテーブルにデータを一括インポートできます。

BULK INSERT を実行するための設定

SQL Server 認証を使用する場合は諸々の設定が必要です。
設定をせずにBULK INSERT ステートメントを実行すると

ファイル "\\ServerName\xxx\xxxx.csv" を開けなかったので、一括読み込みできません。オペレーティング システム エラー コード 5(アクセスが拒否されました。)。

一括読み込みステートメントを使用する権限がありません。

のエラーが表示されました。
原因は、データ ソースへのアクセス許可がないためです。
設定方法は下記手順となります。

1. サーバ上にCSVファイルを保存できるようにする
   例）\\ServerName\ShareFolder$\xxx.csv
   ※$は隠し共有フォルダを意味してます。
2. CSVファイルを保存するフォルダに対し、アクセス許可を追加
   追加するユーザは、私の環境では[NETWORK SERVICE]でした。
   許可項目は初期値のまま。
   設定は、SQL Serverをインストールしたサーバ上で行いました。
   
3. 該当のテーブルに対し、[挿入]許可を付与
   SSMS（SQL Server Management Studio）上で設定します。
   
4. [セキュリティ]→[サーバー ロール]→[bulkadmin]のロール メンバーにSQL Server 認証を使用しログインするユーザを追加
   

フォルダのアクセス許可に追加するユーザ

CSVファイルを保存したフォルダにアクセス許可を追加すると前述しました。
追加するユーザは、[SQL Server 構成 マネージャ]→[SQL Serverのサービス]の一覧にあるSQL Serverのログオン ユーザになります。

私の環境では、ログオン ユーザは[NT AUTHORITY\NETWORK SERVICE]、実際にアクセス許可に追加するユーザ名は[NETWORK SERVICE]です。

BULK INSERT ステートメントのサンプル

サンプルは以前の記事をご覧ください。

SQL Serverで大量レコードをインサートする方法

余談：Truncate Tableを実行する場合

BULK INSERT ステートメントを実行する前にテーブルを空にしたい場合があると思います。
DELETE ステートメントではなく、Truncate Tableを実行すると[高速]かつ[リソースの低消費]となります。
実際に実行する場合、データベース ロールでリソースへの制限を行っている場合は設定が必要です。

[データベース ロールのプロパティ]→[セキュリティ保護可能なリソース]→[明示的]リストの[変更]の[許可]にチェック

を行う事で、VBA等からTruncate Tableを実行する事が出来ます。

SQL Serverで大量レコードをインサートする方法

SQL Serverに大量データを挿入（インサート）する機会があり、どうすれば効率的にできるか調べてみました。
BCPを使う方法は知っているんですけどね・・・
私はデータベース屋ではないので、もっといい方法があるかもしれません。
2013.10.22 加筆
BULK INSERTに「読み込み開始行（FIRSTROW）」を追記しました。
BULK INSERTの改行文字列の既定値を修正しました。
2015.04.13 加筆
SQL Server PowerShellを使ったBulk Insertの実行サンプル記事のリンクを追記しました。

SQL Server カラム定義を変更すると「変更の保存が許可されていません」が表示された場合の対処法

MSSMS（Microsoft SQL Server Management Studio）のテーブル デザイナーから手動変更を行うとしたところ、[変更の保存が許可されていません～]というエラーメッセージが表示されました。
今回は、このメッセージが表示された場合の対処法をメモ。
設定方法が複数ありますが、ちゃんと理解して対応しないと痛い目にあいます。

[オプション]設定は場合によっては非推奨方法の様子

当初、MSSMSの設定かと思い、[プロパティ]や[オプション]を確認してみました。
結果、[オプション]→[デザイナー]→[テーブル デザイナーおよびデータベース デザイナー]に

テーブルの再作成を必要とする変更を保存できないようにする(S)

がありましたので、チェックを外す事でテーブル デザイナーから変更が可能になりました。
一件落着！

と、ここで記事が終わりそうですが、実は違います！
一概に[オプション]設定を変更してはしけません。

そもそも、なぜMSSMSが警告を出力するのだろうか？

今回のエラーメッセージについて、ふと立ち止まって考えてみました。

1. なぜ、この警告メッセージをMSSMSが出力してんだろうか
2. なぜ、[オプション]に今回のチェックボックスがあるのか

もう少し調べてみたところ、Microsoft サポートに答えが掲載されていました。
Microsoft サポートページ

エラー メッセージ "変更の保存が許可されていません" が、SQL Server でテーブルを保存しようとすると表示される

を見ると、

このオプションを無効にすると、テーブルの再作成を回避するのに役立ちますが、変更内容が失われる可能性も生じます。
たとえば、SQL Server 2008 で変更の監視機能を有効にしてテーブルへの変更を追跡する場合を考えます。
テーブルが再作成される原因になる操作を実行すると、「現象」に記載されているエラー メッセージが表示されます。
ただし、このオプションを無効にしていると、テーブルが再作成される際に既存の変更の追跡情報が削除されます。
そのため、このオプションを無効にしてこの問題を回避することはお勧めしません。

と明記されています。
この内容をまとめると

1. [テーブル]の[変更の追跡]機能を有効（True）にしていると[テーブルの再作成を必要とする変更を保存できないようにする]オプションは有効になる
2. 1の状況でオプションを無効（False）にし、テーブル デザイナーからテーブルのメタデータ情報を変更すると[変更の追跡]情報が削除される

となります。
運用中にテーブルのメタデータ情報を変更することはほぼないと思いますが・・・

正しい変更方法

Microsoft サポートページ

エラー メッセージ "変更の保存が許可されていません" が、SQL Server でテーブルを保存しようとすると表示される

によると、テーブル情報を変更する場合は

Transact-SQL ステートメントを使用しテーブルのメタデータ情報を変更します

と書かれています。
忘れてはいけないのですが、忘れそうなのでいくつかのサンプルをメモしておきます。
下記Transact-SQLはSQL Server 2008R2をベースにしています。

カラム定義を変更

ALTER TABLE テーブル名 ALTER COLUMN col01　varchar(10) NULL

カラムを追加

ALTER TABLE テーブル名 ADD col01　varchar(10) NOT NULL DEFAULT ‘999’

稼働中のテーブルに列を追加する場合はNULL許可、ないしは、既定値(DEFAULT)が必要です。
カラムは最後尾に追加され、任意の場所に追加する事はSSMS上から行う必要があります。
ただし、この操作はベストプラクティスではない！とMS SQL Server ライブラリに明記されています。
他DBにあるAFTERがつかれば楽ですが、そもそも設計が悪い！というお話になりますかね・・・

カラムの削除

ALTER TABLE テーブル名 DROP COLUMN col01

[制約]にDEFAULT定義(DF__テーブル名__列名__xxxxx)が定義されている場合、上記クエリだけでは失敗します。
運用上DEFAULT定義を削除してもいい場合、[列の削除]を行う前に[DEFAULT定義の削除]を行ってください。

ALTER TABLE テーブル名 DROP DF__テーブル名__testcol__08EA5793
go
ALTER TABLE テーブル名 DROP COLUMN testcol
go

ALTER TABLEの詳細

msdnの”ALTER TABLE (Transact-SQL)”をご覧ください。

---

SQL Serverは他製品と同様、比較的簡単に導入できます。
しかし、いざ仕様の変更やトラブルシューティングとなると、知識と経験がないと一筋縄ではいきませんね。
今回も身をもって体験させてもらいました。。。
今日も精進したいと思います。

余談ですが、以前MySQLやPostgreSQLで列の追加や削除を行う時

1. 列の追加や削除を適用した一時テーブルを作成
2. 既存テーブルから一時テーブルへINSERT SELECTでデータを流し込み
3. 既存テーブルを削除
4. 既存テーブル名と同じテーブルを作成、一時テーブルのデータを流し込み
5. 一時テーブルを削除

という手順方法を教えていただいたことがあり、この方法もSQL Serverで使えそうですね。
とはいえ、運用中は「出来るからやってみる！」のような安易な発想は御法度ですよね。
テスト、大事です！

SQL Server CHECKDBの修復オプション[REPAIR_ALLOW_DATA_LOSS]、恐ろしや・・・

SQL Serverのバックアップから復元するトレーニングを行うため、以前作成した手順を確認してみました。
復元手順書を読み返した時に気が付いた事は、CHECKDBの[REPAIR_ALLOW_DATA_LOSS]レベルを使った復旧作業をした事がない事。
手順の確認は一時ペンディングしてテストを行った結果、Microsoft Technetに記載されている通り「非常にまずい状況」となりましたのでメモ。
今回の内容を教訓に、「CHECKDBの[REPAIR_ALLOW_DATA_LOSS]レベルは今後使わない」事に決め、基本である「バックアップ ファイルから復元」のみとしたいです。

REPAIR オプションは最後の手段

Microsoft Technetの[REPAIR_ALLOW_DATA_LOSS | REPAIR_FAST | REPAIR_REBUILD]項目には、”REPAIR オプションは最後の手段としてだけ使用ください。”と明記されています。特に”REPAIR_ALLOW_DATA_LOSS”レベルは「修復を実行するとデータが失われる事があり、事前にバックアップする事！」と明記されています。

正直「最後の手段なんかい？」と思った数分後、テストデータがすべて吹き飛びました。
もともと飛ばしていいデータですから、痛くも痒くもありませんが、これが本番データだと思うと恐ろしすぎです。

今回のテスト内容

データベースを論理的に破損させ、復旧オプション付きのCHECKDBを実行してみました。

USE TestDB
GO
ALTER DATABASE TestDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
DBCC CHECKDB('TestDB', REPAIR_ALLOW_DATA_LOSS)
GO
ALTER DATABASE TestDB SET MULTI_USER
Go

結果ログの最後尾に「一貫性エラーが解決されました！」と書かれていますが、修復内容をよ～く読むと、ファイルID：1、ページID：78（1:78）に問題があり、整合性がとれないためオブジェクトから切り離され、データが消失したようなアラートがあります。

'emp' の DBCC 結果。
修復: Clustered インデックスは、データベース "TestDB" のオブジェクト "dbo.emp" で正しく再構築されました。
修復: ページ (1:78) が、オブジェクト ID 2105058535、インデックス ID 1、パーティション ID 72057594038845440、アロケーション ユニット ID 72057594039894016 (型 In-row data) から割り当て解除されました。
メッセージ 8945、レベル 16、状態 1、行 2
テーブル エラー: オブジェクト ID 2105058535、インデックス ID 1 は再構築されます。
        エラーは修復されました。
メッセージ 8928、レベル 16、状態 1、行 2
オブジェクト ID 2105058535、インデックス ID 1、パーティション ID 72057594038845440、アロケーション ユニット ID 72057594039894016 (型 In-row data): ページ (1:78) を処理できませんでした。詳細については、他のエラーを参照してください。
        エラーは修復されました。
メッセージ 8939、レベル 16、状態 98、行 2
テーブル エラー: オブジェクト ID 2105058535、インデックス ID 1、パーティション ID 72057594038845440、アロケーション ユニット ID 72057594039894016 (型 In-row data)、ページ (1:78)。テスト (IS_OFF (BUF_IOERR, pBUF->bstat)) が失敗しました。値は 12584969 と -4 です。
        エラーは修復されました。
メッセージ 8980、レベル 16、状態 1、行 2
テーブル エラー: オブジェクト ID 2105058535、インデックス ID 1、パーティション ID 72057594038845440、アロケーション ユニット ID 72057594039894016 (型 In-row data)。インデックス ノード ページ (0:0)、スロット 0 が、子ページ (1:78) と前の子ページ (0:0) を参照していますが、これらの子ページは見つかりませんでした。
        エラーは修復されました。
オブジェクト "emp" の 0 ページには 0 行あります。
CHECKDB により、テーブル 'emp' (オブジェクト ID 2105058535) に 0 個のアロケーション エラーと 3 個の一貫性エラーが見つかりました。
CHECKDB により、テーブル 'emp' (オブジェクト ID 2105058535) で 0 個のアロケーション エラーと 3 個の一貫性エラーが解決されました。
CHECKDB により、データベース 'TestDB' に 0 個のアロケーション エラーと 3 個の一貫性エラーが見つかりました。
CHECKDB により、データベース 'TestDB' で 0 個のアロケーション エラーと 3 個の一貫性エラーが解決されました。
DBCC の実行が完了しました。DBCC がエラー メッセージを出力した場合は、システム管理者に相談してください。

復旧後のテーブルを検索するとデータがありませんでした。

この結果より、CHECKDBの[REPAIR_ALLOW_DATA_LOSS]レベルによる復旧は成功しテーブルに接続できるようになりましたが、Microsoft Technetの説明どおり、データは損失・・・という結果になりました。

---

今回のテスト結果はMicrosoft Technetの記事のとおり、CHECKDBの[REPAIR_ALLOW_DATA_LOSS]レベルは最悪の事態を招くという事を体感した非常に良いテストでした。
Facebookのお友達からも助言がありましたが、やはり「毎日取得しているバックアップファイルから復元作業を行うのが最も安全」ですね。
もし、CHECKDBの[REPAIR_ALLOW_DATA_LOSS]レベルを使ってエラーを解決しよう！とお考えの方は、今回の事例を踏まえお止めになった方が宜しいかと思います。
やはり、バックアップは大切ですね～
いいテストでした！

Oracleのダンプファイルを読み取りSQL Serverへ転送する方法

ホンのちょっぴりしか触ったことがないOracle。

Oracleの仕様もわからなきゃ操作もほぼ分からない私ですが、仕事でOracleのダンプファイルからデータを引っこ抜く作業をすることに・・・

作業を行うにあたって、ちょうどいいツールを見つけたのでご紹介。

Oracleのダンプファイルを操作/エクスポートできる「EX Dump Editor」

今回紹介するCOM総合研究所さんが開発したEX Dump Editorは

• ダンプファイルの中身を確認したい
• インポート時にエラーになるオブジェクトを削除したい
• BLOBの内容をファイルとして取り出したい
• ダンプファイルを別のデータベースにインポートしたい

などが可能なツールです。

開発者の方、素晴らしいツールありがとうございます。

まさに私のような初心者のためにあるようなツール！

SQL Server 2008 R2にエクスポートしてみる

私はSQL Serverしか使えないので、Oracleのダンプファイルに入っている各種データをSQL Serverに取込んでみたいと思います。

1. EX Dump Editorを起動
2. Ctrl+Oを押して、ダンプファイルを指定
   
3. 左側のDBアイコンを開くと各種データが閲覧可能
   
4. インポートしたいテーブルを選択、右クリック→[データのエクスポート]を選択
   
5. エクスポート先を選択、今回はMicrosoft SQL Serverを選択
   
6. エクスポートするテーブルを選択
   
7. エクスポート先のデータベース情報を入力
   
8. [開始]ボタンをクリックして転送開始

転送結果を見ると・・・

エクスポートした結果を見てみたところ

• データは問題なく取り込まれている
• キーはなく、インデックスにPK_xxxxが作成されている

といった結果でした。

主キーなどは手動で作成するなりすればいいので、私の使い方だと必要十分すぎるツールでした。

ダウンロード

ダウンロードは下記リンクからどうぞ！

EX Dump Editor Ver0.12.0.0

SQLServer 最近勉強した内容をまとめてみました

SQL Serverを素人ながら使い続けていますが、知らない事が多すぎるんですよね。
今回は最近勉強した内容を自分用としてメモ。

リモートサーバの設定と削除

小規模ではあまり使わないと思いますが、リモートサーバ上のデータにアクセスする場合にsp_addlinkedserverプロシージャとopenquery関数を使えます。
SQL Serverをもう一台増設した時に使えますね。

リンクサーバの作成方法

http://msdn.microsoft.com/ja-jp/library/ms190479.aspx

OpenQueryの使い方

http://msdn.microsoft.com/ja-jp/library/ms190479.aspx

リンクサーバの削除方法

http://msdn.microsoft.com/ja-jp/library/ms186218.aspx

MARS（Multiple Active Result Set）

SQL Server 2000時代に「複数の結果セット」を得るには
オープン⇒検索⇒クローズ
を複数回実行していましたが、SQL Server 2005から複数のアクティブな結果セットが得られるMARSという機能が搭載されました。
MSDNのサンプルコードは下記の通りですが、えらく便利ですな～

パーティションテーブル

パーティションテーブルは、行方向に複数の単位に分割することで大きいテーブルとインデックスの管理可能性と拡張性が向上します。ただし、SQL Server 2008R2系で使えるのは下記エディションだけです。

• DataCenter Edition
• Enterprise Edition
• Developer Edition

※SQL Server 2008 R2 の各エディションがサポートする機能より
使ってみたいなと思うけど、購入できるエディションがございません。
なお、SQL Server 2012でもサポート機能は同じようです。概要等を知りたい方は@ITの記事が分かりやすいですね。

データパーティショニングで巨大DBも楽々管理

余談：オープンソースのPostgreSQLはパーティショニング使えます。

パーティショニング : 用途と利点

ローカル内の異なるデータベース間で検索

データベースの完全修飾名は4つの要素（識別子）から構成されています。

[[[サーバ名].[データベース].][所有者].]データベースオブジェクト

基本を知っていればすぐにSELECT文が書けます。
例えばこんな感じ。

SELECT a.[氏名コード] , b.[氏名] FROM [db1].[dbo].[table1] a INNER JOIN [db2].[dbo].[table2] b ON a.[氏名コード] = b.[氏名コード]

カラムにNULLを挿入する方法

SSMS上でテーブルのカラムにNULLを挿入する

Ctrl＋0

を押します。

coalesce関数の使い方

英和辞典の発音記号を見るとコウアレスと読むそうな。
coalesce関数に与えた複数の引数を順番にチェックし、NULLではない最初の引数を表示します。
例えば

SELECT coalesce(NULL, 100, 200)

を実行するとNULLをスキップして、次の値「100」が結果として得られます。
表の外部結合をするときにCASE WHENを使ってNULL判定していた自分が恥ずかしい・・・

SQL Injectionのチートシート

SQL Injectionのチートシート記事があったのでメモしておきます。
PostgreSQL以外にSQL ServerやOracleでも使えるように書かれています。
セキュリティ対策に必須な内容ですので、テスト番長な方はブックマークしておいた方がいいでしょうね。

Postgres SQL Injection Cheat Sheet

余談：勉強用の本

先日ツイートした内容とカブリますが、SQL Serverを勉強するために購入＆購入予定の本をご紹介。
紹介した本以外に、「これいいよ！」という本あれば是非教えてほしいな～

SQL Server 2000 ストアドプロシージャ プログラミング

厚さがかなりあるストアドプロシージャのプログラミング本です。
新品は高いですが、中古本なら送料込みで1000円しません。
Transact-SQLとストアドプロシージャについてかなり詳しく書いています。
新しい機能はありませんが、基本は勉強できますのでオススメです。
なお、私はこの本を使ってSQL Server 2008 R2上にストアドプロシージャを作成・稼働させています。

絵で見てわかるSQL Serverの内部構造（DB Magazine）

DB Magazineの連載を1冊にしたものです。
SQL Server 2000の古い本を所持していますが、DB Magazineが発行した本は非常に分かりやすいです。
まだ購入していませんが、はよ読んでみたい・・・

達人に学ぶSQL徹底指南書

こちらもまだ購入していませんが、Amazonでかなり評価がいいので気になっています。
Google Playでも購入できるので、Google Nexus7が到着次第購入してみようかと思っています。

Windows8RTMからSQL Server 2008 R2に接続する方法

やっと接続できた・・・

Windows8RTMからSQL Server 2008 R2に接続する方法をメモ。

Windows8RTMを使い始めた頃からずっと接続できなかったので、正直うれしい！

なお、接続するログインユーザに気をつけないとハマります！

Windows8RTMからSQL Server 2008 R2に接続する方法

昨日いつも使用しているA5:SQL Mk-2のベータ版一覧を見ていたところ、Native Client11なる文字を発見！

これか？と思い、実際にテストしてみたところWindows8RTMからSQL Server 2008R2に接続できました。

接続方法は下記の通りです。

1.最新のSQL Server Native Clientをインストール

最新のSQL Server 2012 Native Client（SQLNCLI11）をダウンロードしてインストールします。

Microsoft® SQL Server® 2012 Feature Pack

ページ中央部にある「Microsoft® SQL Server® 2012 Native Client」欄を検索してください。

x86とx64の2種類がありますので、環境にあったプログラムをダウンロードします。

2.接続テスト

やり方はいろいろありますが、私はODBCデータ・ソース・アドミニストレイターからテストしました。

システムDSNを選択し、データソースを追加。

SQL Server Native Client 11.0を選択、各種情報を入力して接続テストを実行。

Windows認証の場合は、ログインしているユーザに気をつけてくださいね。

3.アプリケーションから接続してみる

Excel2007で作ったOfficeアプリをExcel2013プレビューで修正、Windows8RTM上で実行してみたところ問題なく動作しました。

バージョンが違ってもちゃんと動作するMSの作り、バンザイですね。

なお、SQL Native Clientを使った場合のサンプルVBAはこんな感じ。

接続プロバイダはSQLNCLI11になりますので、この点を注意してください。

ADOアプリケーションの接続文字列も上記を参考にするか、MSDNの説明を見てください。

SQL Server Native Client と ADO の併用

注意：Microsoftアカウント＋Windows認証だと接続に失敗

Windows8からログインユーザにMicrosoftアカウント（旧Liveアカウント）＋パスワードが使えるようになりましたが、Windows8RTMからSQL Server 2008R2に接続しようとしてハマったのが、このログインユーザ！

Microsoftアカウントでログインし、Windows認証を使ってSQL Serverに接続すると

接続ユーザがxxxxx\Guest

となり、接続に失敗しました。

この場合は旧来どおりログインユーザをローカルアカウントに変更して対応しました。

下記画像はローカルアカウント状態のスクリーンショットです。

SQL Server 2008R2 Expressの自動バックアップ方法

SQL Server 2008R2 Expressのバックアップを手動でとってましたが、万が一を考えて自動バックアップに行いました。

有償版ではSQL Server Agentを使うことで自動バックアップが可能なんですが、Express版はAgentがないためsqlcmdコマンドを使うんですが、忘れそうなのでメモしておきます。

自動バックアップはsqlcmd＋タスクスケジューラを使う

自動バックアップのやり方は下記の通り。

1. SSMSを起動
2. データベース→タスク→バックアップを選択
   
3. 各種設定を行い、スクリプトを保存する（backup.sql）
   ファイルの保存先はシステムドライブ以外の方がいいかな。
   
4. バッチファイルを作成（backup.bat)
   バッチファイルに記述するコマンドは下記の通り。
   >sqlcmd –S サーバ名（インスタンス名も） –E –i スクリプトファイルパス
   ※Windows認証を使っている場合は、-E。
   SQL Server認証の場合は、-U ユーザ名 –P パスワード。
5. タスクスケジューラに4のバッチファイルを登録
   

設定後はテストをしましょうね

設定後、一度タスクを実行し、データを変更・削除し、復元してみましょう。

復元方法がわからないと、障害発生後の対応に時間がかかりますから。

当然ですが、変更・削除するデータはテスト用にしましょうね！

余談：ファーストサーバの大規模障害から学ぶこと

先日起きた「ファーストサーバの大規模障害」ニュースを見て、非常に危機感を覚えました。

「ホスティング会社に預けておけばハード障害があっても大丈夫！」と思っている人が多いと思いますが、私はホスティング系を一切信用していません。

回線費用やサーバ管理費などランニングコストを考えるとホスティングは非常にいいのですが、基本「消えてもいい」「消されてもしょうがない」くらいの気持ちで使うべきではないでしょうか。

また、利用者側のバックアップ運用も重要です。

リスク管理のしっかりした会社、または、人間がいればサービス利用者側での多重バックアップは普通行なっています。

小容量だろうが大容量だろうが関係なくです。

RAID1 HDDや光ディスクのコストは全データ削除・紛失に比べれば大した金額ではありませんから。

今回の障害を対岸の火事と思わず、自分のことだと思い、一度見なおしてみてはいかがでしょうか。

※理解できない・知ろうとしない上司・経営者がいると大変でしょうが・・・

クライアントPCからSQL Serverにリモート接続やコネクトする方法

以前「ExcelからSQL Serverへ接続し、SQLステートメントを実行する方法」や「SQL Serverで大量レコードをインサートする方法」を投稿しましたが、クライアントPCからSQL Serverにリモート接続やコネクトする場合にSQL Server側の設定が必要です。
設定しないと接続できませんので、今回はその点をまとめてみました。

ExcelからSQL Serverへ接続し、SQLステートメントを実行する方法

ExcelからSQL Serverへ接続し、SelectやInsertなどのSQLステートメントを実行する方法をメモ。

手順

1. Alt+F11を押してMicrosoft Visual Basic for Applicationsを起動
2. ツール→参照設定を選択
3. Microsoft ActiveX Data Object 2.8 Libraryを選択
   ※様々な環境で使用できるようにするため2.8を選定
   
4. ADOで接続し、SQL文を実行

SELECT文の例

GX999さんのブログがわかりやすいと思います。

Excel 2007 VBAでSQL Server 2008 Expressのデータを取得する

INSERT文の例

Excel マクロ・VBAのお勉強というサイトがわかりやすいとおもいます。

SQLServerにInsert文発行＠Excel マクロ・VBA

注意点は接続文字列かな？

GX999さんが書いてもいますが、データベースに接続する文字列がわかれば簡単だと思います。
私の場合は、A5:SQL Mk-2のデータベース接続文字列をコピペし、不要な文字を削除して使っています。
どうしても接続できない方はA5:SQL Mk-2で一度SQL Serverに接続して接続文字列をコピペすると楽チンですよ。

余談：SQL ServerのデータをExcelへインポートするのは簡単！

Excel 2007や2010からなのかな？
データタブを選択し、その他データソースからSQL Serverを選択すれば簡単にインポートできます。
ファイルを開くたびに更新することもできます。
　

SQL Serverを使った「任意の時間帯データ」を抽出する方法

SQLを使ったデータベース操作は非常に楽チンですが、データの抽出するSQL文を考えるのは難しいですよね。
今回はSQL Serverを使ったある期間内で任意の時間帯に該当するデータを抽出するSQL文をメモ。

ある期間内で任意の時間帯に該当するデータを抽出したい！

例えば、4/26～4/29の期間で10:00～10:30のデータを抽出したいとします。
↓の赤字が該当データとなります。

---

時間                          ｜回数
---------------------------------------------
2012/04/26 10:00:00｜0
2012/04/26 10:20:00｜1
2012/04/27 09:00:00｜0
2012/04/28 10:00:00｜1
2012/04/28 10:10:00｜2
2012/04/28 10:20:00｜0
2012/04/29 10:40:00｜3
2012/04/29 11:30:00｜1

---

この場合はwhere句は

• convert関数を使って、時間カラムを時分秒に変換
• betweenを使って時間帯を指定

することでデータ抽出ができます。

SQL文

select * from [hogehoge] 
where convert(varchar(20),[時間], 108) between '10:00:00' and '10:30:00'

注意点

この方法はSQL Server用ですので、OracleやMySQLなどでは使えないはずです。
考え方は同じですので、使っているデータベースにあわせてSQL文を変更してください。

SSMSが重いので「汎用SQL開発環境」を使ってみた～A5:SQL Mk-2

SQL Server ExpressはWindowsで使える無料データベース。

非常にいいんですが、管理ツールSQL Server Management Studio（SSMS）が重すぎる・・・

2008で起動が速くなった感じはしますが、SQL Server 2000 Engerprise Managerに比べるとまだまだ重い感じがします。

細かい設定はSSMSを使うとして、日常業務用として使いづらいところがあるので汎用SQL開発環境を使ってみました。

---

2012.05.03 A5:SQL Mk-2の開発者様からコメントいただきました。

ナイス情報を教えていただきましたので記事を追記しました！

SQL Serverにも対応している汎用SQL開発環境

SQL Serverに対応しているWindowsで使える汎用SQL開発環境を探してみました。

日本語に対応して、使いやすそうなのは以下の2点かな？

• A5:SQL Mk-2
• 黒猫 SQL Studio Next

A5:SQL Mk-2は軽いな～

時間がなかったので、試しにA5:SQL Mk-2を使ってみましたが、非常にいいですね。

とくによかったのが、

• 起動＆動作が軽い
• ADO接続が可能
• エクセルのセルをコピー→テーブルに貼り付けが可能
• ER図の作成が可能

といったところ。

エクセルとの親和性が高いのがいいですね！

黒猫SQL Studio Nextも軽くていい！

黒猫さんも使ってみましたが、こちらもいいですね。

特にインターフェースはA5:SQL Mk-2よりも洗練されています！

機能的にはA5:SQL Mk-2の方がよさげかな？

両者ともにインストーラはなし！

A5:SQL Mk-2も黒猫さんインストールする必要がありません。

USBメモリに入れておけるので、出先で環境さえ整っていればすぐに使えます。

A5:SQL Mk-2はストアドプロシージャが動作しない・・・しました

A5:SQL Mk-2ですが、ストアドプロシージャが正しく動作しないんですよね。

↓の簡単なストアドプロシージャを実行すると「SQLの実行：-1件実行しました」と表示されます。

ストアドプロシージャを使う場合は、SSMSで実行か、黒猫SQL Studio Nextを使いましょう。

---

Declare @hoge int = 100

select * from taro where num < @hoge

---

※2012.05.03 記事修正

A5:SQL Mk-2の開発者様からコメントいただきました。

ストアドプロシージャの実行結果が「SQLの実行：-1件実行しました」になる件は今後修正していただけるようです。

また、開発者様からおしえていただいたコマンドCtrl+Shift+Enterを押すと下記のように結果セットを取得するストアドプロシージャが実行されました。

このコマンドはA5:SQL Mk-2のホームページに記載されていないとのこと。

いいこと教えてもらっちゃった！

使い勝手がよかったら是非寄付を！

A5:SQL Mk-2は誇示転移に使い勝手がよかったので先ほど寄付をしました。

A5:SQL Mk-2 寄付の募集

SSMSの重さに耐えられない方は導入してみてください。

個人的にはいいツールだと思います！