SQLServerでのキーの追加スクリプト

テーブル仕様が一度は固まったけど、やっぱりこの項目がキーじゃないとやばい!
といった時には、以下の様なスクリプトを流すと解決して幸せです。

USE データベース名
GO
ALTER TABLE テーブル名 DROP CONSTRAINT プライマリキー名
GO
ALTER TABLE テーブル名 ADD PRIMARY KEY(
キー項目1,
キー項目2)
GO

一度Dropで削除して、その後追加します。

例文

USE [DB_NERV]
GO
ALTER TABLE [dbo].[Staff] DROP CONSTRAINT [PK_Staff]
GO
ALTER TABLE [dbo].[Staff] ADD PRIMARY KEY(
[ID] ASC,
[BirthDay] ASC)
GO

SQLServerCEのImage型における画像データの取扱い

環境
・SQLServer CompactEdition 3.5
・VisualStudio2010 SP1 (VB)

SQLServerのImage型は廃止予定らしいので、あまり一生懸命追いかけなくてもいいと思うのですが、
Image型を使用して画像の出し入れをする必要に迫られたので、描いておきます。

○テーブル構造 (テーブル名:PilotData)
UserID     nvarchar(50)
UserName   nvarchar(100)
Photo1      image


1.Image型に画像データを格納する

'画像データをバイト配列にする。
Dim stream As New System.IO.FileStream("C:\asuka.jpg", IO.FileMode.Open, IO.FileAccess.Read)
Dim reader As New System.IO.BinaryReader(stream)
Dim photo As Byte() = reader.ReadBytes(stream.Length)
reader.Close()
stream.Close()

'SQLCeConnectionオブジェクトの用意
Dim conn as New SqlCeConnection("Data Source=|DataDirectory|\NERV.sdf")
conn.Open()

’SQLCeCommandオブジェクトの用意
Dim cmd As New System.Data.SqlServerCe.SqlCeCommand
'SQL文の設定
cmd.CommandText = "INSERT INTO PilotData(UserID, UserName, Photo1) VALUES (@UserID, @UserName, @Photo1)
’パラメータの格納
cmd.Parameters.Add("@UserID", SqlDbType.NVarChar).Value = "Asuka"
cmd.Parameters.Add("@UserName", SqlDbType.NVarChar).Value = "アスカ・ラングレー"
cmd.Parameters.Add("@Photo1", SqlDbType.Image, photo.Length).Value = photo
’コネクションオブジェクトとの紐付け
cmd.Connection = conn

'実行
cmd.ExecuteNonQuery()
;


2.Image型から画像データを取り出しPictureBoxに表示する。

'SQLCeConnectionオブジェクトの用意
Dim conn as New SqlCeConnection("Data Source=|DataDirectory|\NERV.sdf")
conn.Open()

’SQLCeCommandオブジェクトの用意
Dim cmd As New System.Data.SqlServerCe.SqlCeCommand
'SQL文の設定
cmd.CommandText = "SELECT * FROM PilotData WHERE UserID = @UserID "
’パラメータの格納
cmd.Parameters.Add("@UserID", SqlDbType.NVarChar).Value = "Asuka"
’コネクションオブジェクトとの紐付け
cmd.Connection = conn

'実行
Dim ds As New DataSet
Using adapter As New SqlCeDataAdapter(cmd)
   adapter.Fill(ds)
End Using

'データをPictureBoxに表示する。
Dim b As Byte() = DirectCast(ds.Tables(0).Rows(0)("Photo1"), Byte())
Dim stm As New MemoryStream(b)
PictureBox1.Image = System.Drawing.Image.FromStream(stm)
stm .Dispose()
;

SQLServer2008R2 Expressへの接続が遅い (VB6 + ADO)

環境
Windows7Pro 32bit SP1
SQLServer2008R2 Express SP2
VB6 SP6

今時VB6も無いように思いますが、とあるリプレースで上記環境でのシステムを作りました。

とりあえず動作はするのですが、データベースの接続が遅い。繋げるたびに3~5秒もかかってる・・・
さすがにまずいので対応の為、いろいろいじったところ以下のようにしたら速くなりました。
ちなみに接続はネットワーク越しではなくローカルでの接続です。
また、接続インスタンスは「既定のインスタンス」です。(SQLEXPRESSが付かない)

1.SQLServer構成マネージャーを開きます。
[スタート]→[すべてのプログラム]→[Microsoft SQL Server 2008 R2]→[構成ツール]→[SQL Server構成マネージャー]

SQLServer構成マネージャー

2.「SQLServerネットワークの構成」の「MSSQLSERVERのプロトコル」を見る。
(最初はTCP/IPが無効になっている。)

SQLServer構成マネージャー2

3.TCP/IPを有効にする。
右クリックで「有効化」を選択します。

SQLServer構成マネージャー4

確認メッセージが出るのでOKボタンを押す。

SQLServer構成マネージャー3

4.SQL Serverサービスの再起動
引続き構成マネージャーの「SQL Serverのサービス」からSQLServerを再起動します。
右クリックで「再起動」を選択します。

SQLServer構成マネージャー5

以上の設定で速くなりました。

この設定はネットワーク越しに接続する際のものかと思っていたので盲点でした。
VB6+ADOだからこうなのか、それともまったく固有の環境の問題なのかは不明です。

SQLServer2008 R2 Express のSP適用時のバージョン情報

先日、マイクロソフトDownload CenterからSQLServer2008 R2 Express SP1をダウンロードしました。
Microsoft SQL Server 2008 R2 Express Service Pack1
ダウンロードしたファイルは「SQLEXPRWT_x86_JPN.exe」です。

インストール後のバージョンは「10.51.2500.0」
つまりSP1適用後のバージョン番号です。

次にSP2をダウンロードしました。
Microsoft SQL Server® 2008 R2 Service Pack 2
ダウンロードしたファイルは「SQLServer2008R2SP2-KB2630458-x86-JPN.exe」です。

SP2適用後のバージョンは「10.52.4000.0」でした。

SQLServerExpressに対してのBCP実行時のエラー

「名前付きパイプのプロバイダ:SQL Serverへの接続を開けませんでした」
とのエラーが出てSQLServer2008R2 Standerdで動作していたBCPがSQLServer2008Expressでは動作しなくなってしまいました。
原因は、SQLServer2008では「既定のインスタンス」でインストールされていたのですが Expressでは名前付きインスタンスでインストールしてしまったためです。かなり重要なところのデフォルト動作が違うというのもどうかと思うのですが、対策を調べてみました。

BCPの命令は以下のような感じです。

BCP [db_name]..[table_name] out [file_name] -c -T

これを次のように -Sオプションをつけて実行します。

BCP [db_name]..[table_name] out [file_name] -c -T -S [pc_name]¥SQLEXPRESS

これでばっちりです。

次にコマンドを変えないで出来ないものかと思い、SQLServer構成マネージャーで以下のようにしてみました。
結果として「かろうじて」実行できました。なぜかBCPの実行がすごく遅いです。
他にもなにかしないとだめなのでしょう・・・ 現状では参考程度の情報です。


[SQL Serverネットワークの構成]の[SQLEXPRESSのプロトコル]を選択
名前付きパイプをダブルクリック

パイプ名の[¥¥.¥pipe¥MSSQL$SQLEXPRESS¥sql¥query]を以下のように書換え

¥¥.¥pipe¥sql¥query

SQLServer2008R2でのリモート接続時の流れ

OS: Windows7Pro(32bit), WindowsServer2008R2
DB:SQLServer2008R2 (「既定のインスタンス」でインストール)

———————————————–
1.「SQLServer Browser」サービスの状態を確認
このサービスはインストール中に有効に出来ますが、何もしてない場合は当然インストール直後は「SQLServer Browser」サービスが無効になっているので、まずはこれを確認、及び自動実行されるようにします。

    「コントロールパネル」→「管理ツール」→「サービス」
    ここに「SQLServer Browser」というサービスがあるのでプロパティ画面を開く
    スタートアップの種類を「自動」に変更して「適用」ボタンを押下
    サービスの状態の「開始」ボタンを押下

これで、SQLServerBrowserサービスが起動され、再起動後も今後は自動で起動されます。

———————————————–
2.ファイアウォール有効ならポートを開く
ファイアフォールが無効なら特に意識しなくてもいいのですが、通常はそんなこともないのでしょうからポートを開く必要があります。
ポートを開く手段は色々でしょうけど、以下のサイトのスクリプトを流すのが簡単で、確実です。

http://support.microsoft.com/kb/968872/ja

スクリプトがコピーできる状態で公開されているので、
これをメモ帳なりに張り付けて、Batファイルとして実行しましょう。

以上の作業でほとんど繋がると思います。

———————————————–
3.名前解決
通常、WorkGroupや、ちゃんと管理されたドメインネットワークのLAN内でなら問題ないのでしょうけど、
ちょっと特殊な状況で 、こんなことがありました。
ISDNによるLAN間接続で、VPNっぽく繋げる時に、接続先にあるSQLServerにログイン出来ない・・・

エクスプローラで、相手先のサーバーの共有フォルダなどは当然見れるのですが、
SQLServerにログインできない・・・。なぜかといえばサーバー名の名前解決が出来ないから・・・

DNSなどがちゃんと機能しているネットワーク環境でしたら良いのでしょうけど、
とにかくそのあたりは適当だったので、ポンっと繋がらないのですね。

そんな時は往年のHostsファイル!
このファイルに対応するIPアドレスとPC名を書いておけば万事解決です。

    hostsファイルの場所 C:\Windows\System32\drivers\etc
    メモ帳などで開く。
    一番後ろの行にIPアドレスとPC名を書く
    192.168.0.39   MIKUMIKU_Server
    といった感じに、TAB区切りで記述。
    保存後にPCを再起動

SQLの問合せで、COUNTの結果が0件のデータを取得する。

環境:SQLServer

例えば・・・、

SELECT COUNT(*) AS cnt, Col_1 FROM Table_1 WHERE ~ GROUP BY Col_1

という事をやった際に、条件によっては行が抽出されない。
抽出されないということは、「0件」という結果を取得したくてもできない・・・

0件なら0件と返してほしいのです・・・
という切なる願いを叶えるには、もう一工夫してみると幸せ。

そこで・・・

SELECT COUNT(*) AS cnt
FROM
(
SELECT Col_1 FROM Table_1 WHERE ~ GROUP BY Col_1
) A

といったように副問合せで囲むと、結果0件がなにやら取得できます。