SQL Server の Service Broker が正常に動作しない場合の対処法

Service Broker にメッセージやキューなどを登録したにもかかわらず、正常に処理されない場合の確認と対処方法について記載します。ただ、実際に動作しない原因については多岐にわたる場合がありますので、ここではその一例としてとらえておいてください。

Service Broker 自体が有効かどうか確認
select name, is_broker_enabled from sys.databases
Service Broker を有効にする
alter database [データベース名] set enable_broker with rollback immediate
なんのエラーが発生しているかを確認

最後の列にエラーの理由が載っています。

select * from sys.transmission_queue order by enqueue_time desc
データベースの所有者が正しく設定されているかを確認(null なら設定されていない)
select d.name as 'database', s.name as 'owner'
from sys.databases d
left join sys.server_principals s
  on d.owner_sid = s.sid;

設定されていなければデータベースを右クリックしてプロパティから所有者を設定 (sa とか Administrator とか本来設定されるべきもの)

SQL Server のセキュリティ-ログインに設定しているユーザーと、対象データベースのユーザーが一致しているか

他のサーバーにあったデータベースを復元すると一致していないことが多い


[SSRS] レポートマネージャーでレポートがサブスクリプションによって自動エクスポートされたときのログを確認する

メモ書きです。

環境

  • SQL Server : SQL Server 2008 R2

内容

ログを確認すると、エクスポートされたレポートの種類や、エクスポートにかかった時間、結果、出力サイズ、行数などが参照できます。

ログを確認するには SQL Server Reporting Services をインストールした時に作成されるデータベース「ReportServer」のテーブルを参照します。

以下の SQL を実行するとログの内容を確認できます。

use [ReportServer];

select
  B.[Path]                -- レポートのパスと名前
 ,A.[LogEntryId]
 ,A.[InstanceName]        -- レポートサーバーインスタンス名
 ,A.[ReportID]
 ,A.[UserName]            -- 実行ユーザー
 ,A.[ExecutionId]
 ,A.[RequestType]
 ,A.[Format]
 ,A.[Parameters]          -- レポートの出力に指定したパラメーター
 ,A.[ReportAction]
 ,A.[TimeStart]           -- 出力を開始した時間
 ,A.[TimeEnd]             -- 出力が完了した時間
 ,A.[TimeDataRetrieval]   -- データの取得にかかった時間 (単位はミリ秒)
 ,A.[TimeProcessing]      -- レポートの処理にかかった時間 (単位はミリ秒)
 ,A.[TimeRendering]       -- レポートの表示にかかった時間 (単位はミリ秒)
 ,A.[Source]
 ,A.[Status]              -- rsSuccess(成功) またはエラー コード
 ,A.[ByteCount]           -- レポートのファイルサイズ
 ,A.[RowCount]            -- クエリ結果の行数
from [ExecutionLogStorage] A
inner join [Catalog] B
  on B.[ItemID] = A.[ReportID]
where convert(varchar(8), A.[TimeStart], 112) = '20151104'  -- いつのデータを取得したいか、などの条件
order by A.[LogEntryId] desc;

各パラメータの詳しい情報などは以下の URL 先を参照してください。SharePoint について書かれていますが、内部では Reporting Service を使っているので大体同じです。


今年書いた TIps まとめ

今年に入ってから書いた Tips をまとめてみました。一覧にするとジャンルあまりまとまってませんね…。


Windows 8.1 や Windows Server 2012 R2 で「SQL Server 構成マネージャー」を起動する方法

以前の OS では「SQL Server 構成マネージャー」はスタートメニューの SQL Server 関連のフォルダの中にショートカットとして配置されていたため、そこから起動することができたのですが、Windows 8.1 や Windows Server 2012 R2 に SQL Server をインストールするとなぜか SQL Server 構成マネージャーがスタートメニューのどこにも見当たらなくなります。(もしかしたら Windows8, Windows Server 2012 もかもしれません)

SQL Server 構成マネージャーを起動(使う)には以下の方法を行います。

スタートボタンを右クリックして「コンピューターの管理」を選択。

2014-08-06 08_48_56-rmtdt.sorceryforce.net_3404 - リモート デスクトップ接続

「コンピューターの管理」⇒「サービスとアプリケーション」の中に SQL Server 構成マネージャーがあります。

2014-08-06 08_49_21-rmtdt.sorceryforce.net_3404 - リモート デスクトップ接続


Tips 更新

最近また忙しかったりしてブログにも Twitter にも書き込んでいない状態でしたが少し落ち着いてきたので久々に書き込みます。

sorceryforce.net の方で Tips を4つほど公開しました。SQL Server 関連ですが、大体はインストールとかセットアップとかそんなところの内容になってます。しかも CTP 版や Preview 版なのであんまり Tips に載せるべきものでもないような気がしますが (^^;)

後、ページ一覧をグループ分けして表示できるように作りました。前まではとにかくページをずらずら並べているだけだったのでちょっと見にくかったかもしれません。こういうのって HTML で直書きすると一瞬で終わるのにプログラムで作ろうとするとかなり手間なんですよね…。


[SSIS] Excel から型情報を意識せずに確実に読み込む方法

環境

SQL Server 2005, 2008, 2008 R2

発生する問題

SSIS (SQL Server Integration Services) の データクローから「Excel ソース」を使用すると Excel ファイルからデータを取り込むことができます。Excel の中身がヘッダ1行と2行目以降がデータという一般的なテーブルレイアウトであれば、そのままデータベースに取り込めるので結構便利です。

しかし、実際に使ったことがある方は経験されていることが多いと思いますが、Excel のデータの中身が変わると、SSIS デザイナ上でなぜか型が一致しないという警告が出ることがあります。一度詳細を開いて更新すればもとに戻りますが、Excel の中のデータが変わるとまた警告がでる、の繰り返しになります。

また、場合によっては Excel の中にはきちんとデータがあるにも関わらず、実際に読み込んでみると null で読み込まれる場合があります。

これらの現象は SSIS の問題ではなく、SSIS が Excel の読み込みコンポーネントとして使用している「Excel ISAM ドライバ」の仕様の問題で発生します。これは「Microsoft Jet 4.0 OLE DB Provider」でも「Microsoft Office 12.0 Access Database Engine OLE DB Provider」でも同様です。

このドライバが何をしているかというと、データを読み込む際にわざわざ型指定して読み込まなくても、Excel の中身を見て自動的に型を判別してくれるという便利そうな処理をしていますが、その代り、データの中身の型がころころ変わると読み込む際の型情報も変わってしまいます。そのため、SSIS 上のデザイナでも、事前に型を決定しているにもかかわらず、Excel の中身の型が変わってしまうことによって型が不一致であるという警告が表示されるのです。

このあたりの使用は下記のサイトをみるとわかりやすいかと思います。

簡単にまとめると、Excel の上位8行を読み込み、その列で最も多い型を読み込む型として使用するようになっています。例えば8行内に、文字列が3つ、数値が5つあればその列は「数値型」になります。逆に文字列が6つ、数値が2つであればその列は「テキスト型」になります。同じ個数の場合は「数値型」が優先されます。

ちなみに SSIS で説明していますが、ISAM ドライバーの問題なので、これを使うプログラムはすべて同じ問題を抱えています。

対応方法

ではこれを確実に回避するにはどうすればいいかというと、少し手を込んだことをしなければいけません。

まず、今回の対応方法としては前提条件としてすべての列を文字列として読み込みます。もともと数値の列も文字列として読み込むので、読み込んだ後は数値に変換する必要があります。これは臨機応変に対応してください。

ISAMドライバーで読み込む際、「IMEX」というパラメータを指定することができます。これに「1」を指定すると「インポート モード」になり、読み込むすべてのデータがテキストになります。ただし、Excel 内の指定した列のデータがすべて数値の場合は数値になってしまうので注意してください。一つでも文字列があれば読み込む型は文字列になります。

IMEX パラメータは「接続文字列」で指定します。SSIS デザイナで接続文字列を編集する場合は、接続マネージャーで作成した Exce ソースを右クリックし、プロパティを開きます。プロパティエリアに「ConnectionString」という項目がありますので、その文字列を直接編集します。GUI で設定するパターンもありますが、こちらは IMEX が設定できないので注意してください。

IMEX パラメータは下記のように設定します。一番後ろにある「Extended Properties」内にセミコロン区切りで「IMEX=1」を追加します。

  • Provider=Microsoft.ACE.OLEDB.12.0; Data Source=C:Test.xlsx; Extended Properties="Excel 12.0; HDR=YES; IMEX=1;”

接続文字列については以前まとめましたのでこちらのページを参照してください。

さて、これですべての列は文字列として読み込める「はず」なのですが、前述したとおり、Excel にすべて数値が入力されていた場合、または何も入力されていない場合はどうしても列の型が数値として読み込まれてしまいます。ですので、Excel の1行目か2行目に「1行すべて文字列だけを入力した行」を入れるようにします。こうすればほぼ間違いなく文字列として読み込めるようになります。読み込みもとの Excel ファイルにもよりますが、テキスト行を作ってその行だけ非表示にすれば編集上は問題ないかと思います。

 

まあ、個人的には Excel で読み込むのではなく、型を意識しない CSV ファイルのほうがよかったりもするんですけどね。Excel 上で編集すると「0000」とテキストとして貼り付けたつもりが数値として 0 になってしまってて気づかなかったりとかあるので。実は CSV も CSV でダブルクォーテーションのエスケープが考慮されてなかったりとかいろいろ問題はありますが (^^;)


SQL Server Reporting Services で登録されているサブスクリプションを一括実行する SQL

概要

サブスクリプションは登録したスケジュールに従って自動的に実行されますが、任意のタイミングで一括実行したい場合もあるかと思います。ここではそれを行うための実行 SQL を載せています。

注意

この記事の内容はすべての環境で実行を保証しているわけではありません。また公式でサポートしている内容でもありませんので自己責任でお使いください。

動作確認環境

  • SQL Server 2008 Standard SP3

実行 SQL

-- SubscriptionID を入れる変数
declare @id as uniqueidentifier

-- ReportServer データベースの Subscriptions テーブルから登録されている
-- サブスクリプション ID 一覧を取得
declare NEW_cursor cursor for
select SubscriptionID
from ReportServer.dbo.Subscriptions

-- カーソルオープン
open NEW_cursor

-- FETCH スタート
fetch next from NEW_cursor
into @id

while @@FETCH_STATUS = 0
begin
  -- サブスクリプションの実行イベント追加 (非同期)
  exec ReportServer.dbo.AddEvent @EventType='TimedSubscription', @EventData=@id

  -- 次の ID へ
  fetch next from NEW_cursor
  into @id
end		

-- カーソルクローズ
close NEW_cursor
deallocate NEW_cursor

補足

この SQL はあくまでもイベントを登録しているだけなので即座にサブスクリプションの内容が実行されるわけではありません(非同期)。この SQL を実行すればサブスクリプションの実行内容がすべて完了しているものと思い込んでしまうと、次の処理を実行たときに、実はまだ処理が完了していなかった、なんてことになりかねないので注意してください。


HTTP経由でキューブにアクセスする方法

ネットワークが特殊な環境で2383ポートが使用できず、80(または443)ポートが使用可能な場合、HTTP経由でキューブにアクセスする方法があります。

詳しくは公式サイトに載っていますので手順通りに設定すれば問題ないかと思います。

※SQL Server 2012 へのリンクページですが、デフォルトのページが一部英語であったため、すべて日本語になっている上記のリンクに飛ばしています。

 

上記のリンク先の説明で気をつける点があります。

まず「msmdpump.dll」「msmdpump.ini」「Resources」の配置先が「C:inetpubwwwrootolap」と「C:inetpubwwwrootolapisapi」の2パターンが書いてありますが、isapiフォルダはなくても問題ありません。接続先のURLを短くしたいのであれば olap フォルダの直下に上記の3つを入れてしまった方がいいでしょう。

もう一点「msmdpump.ini」ファイル内の「ServerName」のサーバー名は「IISから見たキューブ(Anarysis Services)があるサーバーの名前」です。ですので、IISとキューブが同じサーバーにある場合はそのまま「localhost」で問題ありません。勘違いして外から見たIISサーバー名を指定するとつながらなくなってしまう場合があります。私はこれで結構はまってしまいました。


[SSRS] Tablix の列ヘッダを改ページしても表示させたままにする方法

設定がわかりにくかったのでメモ。

下のような列ヘッダー1行と詳細データ1行の Tablix を配置しているものとします。

列ヘッダーA 列ヘッダーB 列ヘッダーC 列ヘッダーD
詳細データA 詳細データB 詳細データC 詳細データD

 

レポートを表示した際に詳細データに表示される行数が多くなると自動的に改ページされるようになりますが、改ページを行った場合、2ページ目以降も列ヘッダーを表示してほしい場合が多いと思いますが、残念ながらデフォルトでは最初のページにしか表示されません。(改ページさせない設定は Tablix のプロパティにあります)

2ページ目以降も列ヘッダーを表示するために画面の項目を調べていくと一般的に Tablix のプロパティに「すべてのページにヘッダー行を表示する」というチェックボックスを見つけられると思いますので、そのチェックで解決すると思われがちなのですが、このチェックをつけても期待した動作にはなってくれません。(このプロパティがどんな動作をするかはわかりません)

改ページでも列ヘッダーを表示させるには以下の手順を踏む必要があります。

  1. Tablix をクリックして選択
  2. デザイナの下にあるグループペイン(行グループ、列グループのエリア)の「右上」にある「▼」をクリック。(行グループ、列グループの下ではなく右側にあります)
  3. 「詳細設定モード」を選択
  4. 行グループの下に「(静的)」の項目が追加されるのでそれを選択。(列グループにも同様に表示されますが、列ヘッダーを表示させたい場合は行グループの方を選択します。選択するとデザイナ上の Tablix で左上のセルが選択されると思います)
  5. プロパティペインを開き(ない場合は表示メニューから「プロパティ ウインドウ」)、「KeepWithGroup」のプロパティを「After」に、「RepeatOnNewPage」のプロパティを「True」に設定します。
  6. プレビューで動作確認