SE奮闘記

SQL Server 2000からPostgreSQLにデータ移行したときのメモ。困るのがEnterprise Managerではinsert文をエクスポートしてくれる機能がない。 しょうがないのでCSVで吐き出して、インポートするのが一般的。やり方は 前の記事 を参考に。 でもinsert文を出力してくれるツールが世の中にはあるみたい。 [Ruizata Project]SQL Dumper 英語だけど直感的に分かる。 PostgreSQLで実行するためには少し置換しないといけないけど、それは大したことはない。 元データがあるサーバー（SQL Server）を直接触れない場合は、 「必要なテーブルだけ別データベースにコピーして、そのデータベースをデタッチしてMDFとLDFファイルをください。」 と説明するのが大変。 私の知り合いは全体バックアップをもらって「データベースの復元」からやっていたので、その方がデータの受け渡しはスムーズかも。   ＜関連記事＞ PostgreSQLにCSVデータをファイルから取り込む

昔SQL Server 2000でページングを実装していたときにやった方法をメモ。 そのときの環境はSQL Server 2000, IIS, レガシーASP, XML, XSLT。 SQL Server 2005では ROW_NUMBER という関数で簡単に実現できるらしい。 SQL Server 2000ではoffsetもlimitもないので、TOPとWHERE句でうまいこと出力を制限するやり方が一般的。 ここ を参照。 私の場合はカーソル（ cursor ）と変数テーブルを使って実装していた。（カーソルを使うとfor文みたいな繰り返し処理ができる） 方法としては条件で絞り込んだ（プライマリ）キー情報を取得するSQLをカーソルで定義する。 offsetは FETCH absolute で代替。FETCH nextでループしながら変数テーブルにinsertしていく、同時に行数をカウントしてlimitまでいったら終了。 この最小のテーブルと他のテーブルと結合して欲しい情報を出力。 ポイントはカーソルとして定義するSQLでWHERE句を指定しきることと、変数テーブルに挿入する情報は（インデックスを使えるように）最小の情報にとどめること。さらに情報が欲しい場合は最後の結合で同じテーブルと結合し直す。 この辺はクエリアナライザで実行プランを眺めながらやると、最適解が得られると思う。 こんなめんどくさいことをやらなくてもいいような気がするが、昔自分が悩んで辿り着いた方法なので、どっかで役に立つことがあるかも。

PostgreSQL8.3からデフォルトで autovacuum が有効になり、 vacuum analyze とかしなくてもよくなりましたが、8.2までの場合は気にしないといけません。 8.1と8.2の場合はautovacuumの機能を有効にします。 ［PostgreSQLウォッチ］第19回 ベータ・リリースを間近に控えたPostgreSQL 8.1 - オープンソース：ITpro 7.4まではcronなどで定期的にvacuumしてあげないといけない。 PostgreSQLのチューニングに関しては、下記記事が分かりやすい。 SQL Power - Tuning PostgreSQLを遅くしている犯人はどこだ？（1/3） - @IT 今までSQL Serverでの開発が多かったで、統計情報に関しては特に気にしていませんでしたが（SQL Serverは統計情報の収集を自動でやってくれる）、統計情報についても勉強しとかないと後々はまりそう。 Microsoft SQL Server 2005 のクエリ オプティマイザが使用する統計情報   < 2011/12/22 Modified > Tweetされたので体裁を整えた。

テストでSQL Server 2005 Express Editionをインストールして試そうとしたけど、リモートでログインできない・・・。 サービスと接続のセキュリティ構成からリモート接続を許可してあるか確認。 Standard Editionだとこれでさくっとログインできたのですが、Express Editionだとさらに設定が必要みたい。 ここ を参考に。 SQL Server 2005 ネットワークの構成→SQLEXPRESSのプロトコル→TCP/IP→プロパティでＩP AllのＴＣＰポートが1027になっているのを1433に変更。そしてSQL Serverのサービスを再起動。 これでもできなかったら、コマンドプロンプトで c:>telnet （サーバーのＩＰ） 1433 を打ってみて接続できれば、SQL Serverの設定。できなければファイヤーフォールの設定だと思う。

Oracle, SQL server, PostgreSQL, MySQLなどのリレーショナルデータベースはロックという機能をサポートしていて、複数ユーザによる同じデータへのアクセスを保証しています。 ですが、各データベースでロックの仕組みは微妙な違いがあります。この辺の違いを理解していないとバッチ処理などで一つのトランザクションで大量のデータ更新を行う際に、はまりやすくなります。 ↓表にしてみましたが、どれも基本行ロックで、明示的にテーブルロックにするなどのコマンドは用意されているので、ロック状態は任意に設定できます。 データベース ロック 備考 Oracle 行ロック どれだけロックが増えても行ロック。その分メモリを食う。 参考 。 SQL Server 行ロック→テーブルロック システムのリソースが少なくなると行ロックからテーブルロックに自動的に変更（ロックエスカレーション機能）。 参考 。 MSDN 。 SQL Server 2005 と Oracle 10g の真実 PostgreSQL 行ロック メモリ上に変更された行の情報を記憶しないので同時にロックできる行数に上限はない。ただしテーブルに書き込むため、定期的に VACUUM してあげないといけない。 8.3では自動でVACUUMしてくれるらしい。 wikipedia MySQL MyISAM：テーブルロック InnoDB：行ロックとテーブルロック MyISAMとInnoDB 詳細は こちら 。 行ロックの利点と欠点がMySQLのサイトにあったので メモ 。 ほとんどの場合ロックを気にしなくても大丈夫ですが、IDの採番はちゃんとロックしないと重複したIDとなる場合がありえる（めったない）。 ちゃんとロックしないと駄目でしたー ...

この 続きで、64bitのWindows Server上のIISで動かすと「アプリケーションプールの制限値を越えました」と怒られる現象ですが、原因はSQL Serverに接続後、ちゃんとcloseしていなかった（箇所がある）ため接続プールがクリアされなかったのが原因でした。 私の認識ではたとえcloseを実行しなくても.NETが自動的に切断（プールを解放）してくれるものだと思ってました。もちろんできるだけcloseするようプログラミングしますが。 ここ にSQL Serverの接続プールに関しての説明があります。これにはcloseとdisposeを使って接続を終了することを強く勧めていますが、接続が長時間アイドル状態のときは.NETが自動的に接続を切断するという記述もあります。 64bitのバグ？実際32bitだといくらやってもエラーとなりませんでした。でも、必ずcloseするよう癖をつけといた方がよいみたいです。 ここ に実際にオブジェクト破棄を保証するコードのサンプルがあるので参考に。 さらに.NETの自動メモリ管理（ガベージコレクション）について知りたい時は ここ 。

ストアドプロシージャを書いていると、変数を使って動的にSELECTする値を絞り込むようなことをしたくなります。 そんなとき検索してみつけたのは ここ にあるサンプルコードライブラリ。 SQL CLRをやり始めるときやT-SQLのTipsなど結構参考になりました。 変数を使う方法は括弧を使うとすんなりできます。 declare @countnumber int set @countnumber = 10 select top (@countnumber) * from person.contact

SQL Server 2005のSQL CLRの開発で壁にぶち当たって、そのとき調査したメモ。 SQL CLRで開発したモジュールは結局.Net FrameworkのCLR（Common Lanuage Runtime）で動く（と思う）ので、そのバージョンの取得方法。 //【C#】 string clrVersionBuildtime = System.Reflection.Assembly. GetExecutingAssembly().ImageRuntimeVersion; '【VB.NET】 Dim clrVersionBuildtime As String = System.Reflection.Assembly. GetExecutingAssembly().ImageRuntimeVersion ちなみに現時点では「v2.0.50727」だった。 問題解決にはならなかったけど、CLRの問題ではなさそうなのは分かった。

【SQL Server 2005】 SQL CLRを利用してストアドプロシージャからPOPサーバにアクセスするプログラムを書いたときのメモ。POPアクセスのコードは ここ を参考に。 ストアドプロシージャでSQL CLRを利用する場合、.NETで実現できることは基本できるようですが、外部のPOPサーバにアクセスしないといけないので、気にしないといけないのがセキュリティ権限。ＳＱＬ CLRの作り方は ここ を参考に。 データベースから外部へのアクセス権限に関して参考になるのが、 ここ と ここ 。 最初はコンパイル時に下のようなエラーが出てきたけど、よく分からず。 アセンブリ 'SqlServer_dbPOP' の CREATE ASSEMBLY が失敗しました。 PERMISSION_SET が EXTERNAL_ACCESS の場合、アセンブリ 'SqlServer_dbPOP' は許可されません。 アセンブリが許可されるのは、データベース所有者 (DBO) に EXTERNAL ACCESS ASSEMBLY 権限があり、 データベースの TRUSTWORTHY データベース プロパティが有効になっている場合、 または、アセンブリが証明書または非対称キーで署名されており、 それに対応するログインに EXTERNAL ACCESS ASSEMBLY 権限がある場合です。 このデータベースを復元またはアタッチした場合は、 データベース所有者がこのサーバー上の正しいログインにマップされていることを確認してください。 マップされていない場合は、sp_changedbowner を使用して問題を解決してください。 調べた結果、まずデータベース側の設定はManagement Studioでデータベースを右クリック→プロパティで「信頼可能」が「True」になっている必要がある。 ＧＵＩで変更できなかったので、下記SQLを実行してTrueにする。データベースから外部にアクセスするときにはこの設定が必要。 ALTER DATABASE (データベース名) SET TRUSTWORTHY ON 次はVisual Studio...

Visual Studio 2005をインストールした後、SQL Server Management Studioをインストールしようとしても「既にインストール済み」と言われ、インストールできないのですが、データベースの管理は圧倒的にManagement Studioの方が楽なので、なんとかしてインストールする方法です。 全ての答えが ここに 。 手順だけ書くと SQL Server 2005（ワークステーションコンポーネントのみ）をアンインストール。 CDから「Integration Service」と「ワークステーションコンポーネント」をインストール。 SQL Server 2005のサービスパックをインストール SQL Server 2000のEnterprise Managerの方が軽くてよかったなぁ。

前は検索したらさくっと出てきたような気がしましたが、見つからないので拾い物ですが置いときます。 ストアドプロシージャやユーザー定義関数では、実行するごとに値の変わる関数は使えないのですが（例えばgetdateとか）、 それを別の方法で取得するやり方です。 ビューを使ってselect getdate()としといて、それを使う方法もありますが、こっちの方が汎用性がありそうなので参考に。 vbscriptを実行してその値を取得してるので、vbscriptでできることは全部できそう。あとWindowsのシェルにストアドからコマンドを投げる方法もあるので、やろうと思えばなんでもできる気がします。 ここ とかを参考に。 以下、ユーザー定義関数で現在日時を取得するサンプルです。これをそのままクエリアナライザで実行して、select dbo.func_GetDate()とすれば実行結果を見れます。 CREATE FUNCTION func_GetDate() RETURNS datetime AS /*************************************** FUNCTION ： getdate()が関数内で使えないのでこっちで現在の日付を取得 RETURN    ： datetime ***************************************/ BEGIN DECLARE @object int DECLARE @hr int DECLARE @ScriptText varchar(8000) DECLARE @temp varchar(100) DECLARE @return datetime --スクリプトエンジンのロード EXEC @hr = sp_OACreate 'MSScriptControl.ScriptControl', @object OUT IF @hr <> 0 BEGIN return '' END --失敗時 -- Languageプロパティ設定 EXEC @hr = sp_OASetProper...