タグ: SQL Server

【Entity Framework Core Tips】オプティミスティック同時実行制御を行うことによって複数アクセスによる更新で後勝ちによるデータ損失を防ぐ (SQL Server)

onodera_sf コメントをする

Web アプリケーションなど複数のアクセスによってデータが更新される環境において意図しないデータの後勝ち更新が行われてしまうことを防ぎます。

【Entity Framework Core Tips】Entity Framework Core を使用して SQL Server にアクセスする準備を行う (データベースファースト)

onodera_sf コメントをする

Entity Framework Core を使用することにより SQL を記述しなくても SQL Server のレコードを操作できるようになります。

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

onodera_sf コメントをする

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

onodera_sf コメントをする

メモ書きです。

環境

  • 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 まとめ

onodera_sf コメントをする

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

]]>

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

onodera_sf コメントをする

以前の 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 更新

onodera_sf コメントをする

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

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

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

]]>

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

onodera_sf コメントをする

環境

SQL Server 2005, 2008, 2008 R2

発生する問題

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

しかし、実際に使ったことがある方は経験されていることが多いと思いますが、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 の上位８行を読み込み、その列で最も多い型を読み込む型として使用するようになっています。例えば８行内に、文字列が３つ、数値が５つあればその列は「数値型」になります。逆に文字列が６つ、数値が２つであればその列は「テキスト型」になります。同じ個数の場合は「数値型」が優先されます。

ちなみに 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 の１行目か２行目に「１行すべて文字列だけを入力した行」を入れるようにします。こうすればほぼ間違いなく文字列として読み込めるようになります。読み込みもとの Excel ファイルにもよりますが、テキスト行を作ってその行だけ非表示にすれば編集上は問題ないかと思います。

 

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

]]>