SQL Serverでサーバマシンの時計の標準時が何であるかによらずJSTのタイムスタンプを取得

DATEADD(hour, 9, GETUTCDATE())

SQL Serverで実行されたクエリを取得する

例えば、実行時刻が2019/01/16 16:10:00以降でクエリが「hogehoge」を含むものを取得する場合

SELECT st.text, last_execution_time
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
WHERE last_execution_time >= '2019/01/16 16:10:00' AND st.text like '%hogehoge%'
ORDER BY last_execution_time

管理者権限のないWindowsでPython3とOpenPyXLのインストール

Windows10の64bit版が前提。

1. Pythonのインストール

https://www.python.org/downloads/windows/

にアクセスし、Python3最新安定板の Windows x86-64 executable installer をダウンロードする。

ダウンロードしたインストーラを起動し、「Add Python 3.7 to PATH」にのみチェックして「Install Now」をクリックする。


2. OpenPyXLのインストール

コマンドプロンプトでまず

python -m pip install --trusted-host pypi.org --trusted-host files.pythonhosted.org --upgrade pip

を実行して、pipをバージョンアップする。
つぎに

python -m pip install --trusted-host pypi.org --trusted-host files.pythonhosted.org openpyxl

を実行してOpenPyXLをインストールする。

以上。

Windowsサーバにセッションの空きがなくてリモートデスクトップ接続できない場合（sshで接続できる前提）

Windowsサーバにリモートデスクトップ接続したいが、いつまで経ってもつながらない場合、Activeなセッションが複数あって空きがない可能性が考えられる。
WindowsサーバにCygwinがインストールされていて、sshdが立っていれば手がある。
ターミナルアプリでWindowsサーバにsshで接続し

$ query session

を実行して、リモートデスクトップ接続のセッションIDと状態を確認する。
リモートデスクトップ接続のセッション名は

rdp-tcp#0

のようになっている。
状態が「Active」なセッションについて

$ /cygdrive/c/Windows/system32/reset session セッションID

を実行して削除する。
以上。

LinuxからWindowsのSQL ServerにODBCで接続

以前にも似たような投稿をしているが、現時点で最新の環境であることと、以前とは少し設定が異なるので再度投稿。
LinuxはCentOS 7.3。

1. 各種RPMパッケージインストール

$ sudo yum install unixODBC*
$ sudo yum install freetds*
$ sudo yum install perl-DBI
$ sudo yum install perl-DBIx-Connector --enablerepo=epel
$ sudo yum install perl-DBD-ODBC


2. 設定ファイル編集、作成

$ sudo vi /etc/odbcinst.ini

末尾につぎの行を追加（odbc.iniで指定するドライバの設定）

----- ここから -----
[FreeTDS]
Description=FreeTDS ODBC
Driver=/usr/lib64/libtdsodbc.so
Setup=/usr/lib64/libtdsS.so
UsageCount=1
----- ここまで -----


$ sudo touch /etc/odbc.ini
$ sudo vi /etc/odbc.ini

つぎの行を追加（ホスト名でDSNの設定をする）

----- ここから -----
[db1]
Description=SQL Server 2012
Driver=FreeTDS
Trace=No
Server=192.168.10.1
Database=master
Port=1433
TDS_Version=8.0
ClientCharset=UTF-8
----- ここまで -----


3. 接続テスト

$ isql -v db1 db_user_name db_user_passwd


4. Perlサンプル

データベースsampleにカラム[group_id][user_id]を持つテーブルtest_tblがある前提。

----- ここから -----
#!/usr/bin/env perl

use strict;
use warnings;

use DBIx::Connector;

my $db_host   = 'db1';
my $dsn       = 'dbi:ODBC:' . $db_host;
my $db_name   = 'sample';
my $db_user   = 'db_user_name';
my $db_passwd = 'db_user_passwd';

my $test_user  = 'connect_test_user';
my $test_group = 'connect_test_group';

my $conn = DBIx::Connector->new($dsn, $db_user, $db_passwd) or die $!;
my $dbh  = $conn->dbh;

# test_tblに対象レコードがないことの確認（何も出力されない）
my $stmt = "SELECT * FROM $db_name.$db_user.test_tbl WHERE user_id = ?";
my $sth = $dbh->prepare($stmt) or die $dbh->errstr;
$sth->{LongTruncOk}=1;
$sth->{LongReadLen}=2000000;
$sth->execute($test_user) or die $dbh->errstr;
while (my @row = $sth->fetchrow_array) {
    print "select1:@row\n";
}
$sth->finish;

# test_tblにレコード作成
$stmt = "INSERT INTO $db_name.$db_user.test_tbl ([group_id], [user_id]) VALUES (?, ?)";
$sth = $dbh->prepare($stmt) or die $dbh->errstr;
my $rc = $sth->execute($test_group, $test_user) or die $dbh->errstr;
print "insert:$rc\n";
$sth->finish;

# test_tblに対象レコードがあることの確認
$stmt = "SELECT * FROM $db_name.$db_user.test_tbl WHERE user_id = ?";
$sth = $dbh->prepare($stmt) or die $dbh->errstr;
$sth->{LongTruncOk}=1;
$sth->{LongReadLen}=2000000;
$sth->execute($test_user) or die $dbh->errstr;
while (my @row = $sth->fetchrow_array) {
    print "select2:@row\n";
}
$sth->finish;

# test_tblからレコード削除
$stmt = "DELETE FROM $db_name.$db_user.test_tbl WHERE user_id = ?";
$sth = $dbh->prepare($stmt) or die $dbh->errstr;
$rc = $sth->execute($test_user) or die $dbh->errstr;
print "delete:$rc\n";
$sth->finish;

# test_tblに対象レコードがないことの確認（何も出力されない）
$stmt = "SELECT * FROM $db_name.$db_user.test_tbl WHERE user_id = ?";
$sth = $dbh->prepare($stmt) or die $dbh->errstr;
$sth->{LongTruncOk}=1;
$sth->{LongReadLen}=2000000;
$sth->execute($test_user) or die $dbh->errstr;
while (my @row = $sth->fetchrow_array) {
    print "select3:@row\n";
}
$sth->finish;

$conn->disconnect;

exit;
----- ここまで -----

Microsoft Azureで仮想マシンが応答しなくなったときの対応

Azureの管理ポータル上は稼働しているように見える仮想マシン上のサービスが利用できず、リモート接続もできなくなった場合、仮想マシンの構成を変更して保存すると復旧する確率が高い。

テキストエディタGitHub Atomのバージョンは当面0.188.0で固定がよろしいようで(Windows版) （解決）

Windowsのシステム環境変数に

変数名：ATOM_NODE_URL
変数値：http://gh-contractor-zcbenz.s3.amazonaws.com/atom-shell/dist

を追加することで問題解消。

---------------------------------------------------------------------------------
GitHubのAtomというテキストエディタがかなり良さ気だが、バージョン0.189.0から機能を追加する各種パッケージがインストールできなくなった。
Windows7 pro sp1 (64bit) のproxy経由。

https://github.com/atom/atom/releases

から0.188.0のZIP版をダウンロードし、解凍したものを使えば自動updateされないので、このバグが解消されるまでは当面このバージョンで。
パッケージがインストールできない状態では流石に使いにくい。

MS SQL Serverで復旧モデルが「完全」の場合はトランザクションログをバックアップ

MS SQL Serverでミラーリングの構成にする場合、データベースの復旧モデルは「完全」でなくてはならない。

その場合、トランザクションログのバックアップを定期的にとらないと、ログファイルの肥大化を止められない。

データベースのバックアップをとるだけではトランザクションログのバックアップはとられないので、メンテナンスプランでデータベースのバックアップをとるのと同時に別途トランザクションログのバックアップもとるように設定する必要がある。

SQL Serverでミラーリング状態の確認

Management Studioで

SELECT DB_NAME(database_id) [DBName],mirroring_role_desc,mirroring_state_desc,mirroring_witness_state_desc FROM sys.database_mirroring WHERE mirroring_guid IS NOT NULL ORDER BY DBName

を実行する。
コマンドプロンプトなら

sqlcmd -S localhost -U sa -P パスワード -d master -Q "SELECT DB_NAME(database_id) [DBName],mirroring_role_desc,mirroring_state_desc,mirroring_witness_state_desc FROM sys.database_mirroring WHERE mirroring_guid IS NOT NULL ORDER BY DBName"

を実行する。

SQL Server でミラーリング削除

パートナー（ミラー先）に接続し、Management Studioで

ALTER DATABASE データベース名 SET PARTNER OFF

を実行する。

Azureで仮想ネットワーク内仮想マシンのDNS名

仮想マシンOSがWindowsの場合、コマンドプロンプトで


C:\Users\Administrator>ipconfig

Windows IP 構成


イーサネット アダプター Ethernet 21:

   接続固有の DNS サフィックス . . . . .: eldb.m2.internal.cloudapp.net
   リンクローカル IPv6 アドレス. . . . .: fe80::7de3:9900:a6a0:eda1%34
   IPv4 アドレス . . . . . . . . . . . .: 192.168.91.4
   サブネット マスク . . . . . . . . . .: 255.255.255.128
   デフォルト ゲートウェイ . . . . . . .: 192.168.91.1

Tunnel adapter isatap.eldb.m2.internal.cloudapp.net:

   メディアの状態. . . . . . . . . . . .: メディアは接続されていません
   接続固有の DNS サフィックス . . . . .: eldb.m2.internal.cloudapp.net

Tunnel adapter Teredo Tunneling Pseudo-Interface:

   接続固有の DNS サフィックス . . . . .:
   IPv6 アドレス . . . . . . . . . . . .: 2001:0:9d38:6abd:2cf8:151e:3f57:a4fb
   リンクローカル IPv6 アドレス. . . . .: fe80::2cf8:151e:3f57:a4fb%14
   デフォルト ゲートウェイ . . . . . . .: ::

C:\Users\Administrator>


この中の「接続固有の DNS サフィックス . . . . .: eldb.m2.internal.cloudapp.net」を確認。
例えばそのマシンのホスト名が hoge だったら

hoge.eldb.m2.internal.cloudapp.net

がその仮想ネットワーク内で使えるそのマシンのDNS名。
OSがLinuxの場合、


$ cat /etc/resolv.conf
; generated by /sbin/dhclient-script
search elweb.m5.internal.cloudapp.net
nameserver 168.63.129.16
$


この中の「search elweb.m5.internal.cloudapp.net」を確認。
そのマシンのホスト名が hogehoge だったら

hogehoge.elweb.m5.internal.cloudapp.net

がその仮想ネットワーク内で使えるそのマシンのDNS名。

CPU使用率

5秒おきにCPUの平均使用率を表示

（Linuxの場合）
$ mpstat 5

（Windowsの場合）
> typeperf -si 5 "\Processor(_Total)\% Processor Time"

Bracketsの編集画面のフォントを変える (Windows)

C:\Documents and Settings\ユーザ名\Application Data\Brackets\extensions\user

に例えば code-font というフォルダを作成し、その中に main.js というファイル名で

define(function (require, exports, module) {
  "use strict";
  var ExtensionUtils = brackets.getModule("utils/ExtensionUtils");
  ExtensionUtils.addEmbeddedStyleSheet(".CodeMirror { font-family: 'MS Gothic'; }");
});

を保存する。
フォント名は MS Gothic のところで指定する。
以上。

MS SQL Server 2008 R2 Expressのインストールでつまづいた

Windows XP SP3 (32bit)での話。
事前に「.NET Framework 4」やら「Windowsインストーラ4.5」やら「Windows PowerShell 1.0」のインストールが必要。
管理ツール -> 「ローカルセキュリティポリシー」 -> 「ローカルポリシー」 -> 「ユーザ権利の割り当て」の「プログラムのデバッグ」に「NETWORK SERVICE」やら「LOCAL SERVICE」やらデータベース管理者を追加した。
また同じく「ローカルセキュリティポリシー」の「サービスとしてログオン」から古いバージョンのSQL Serverのユーザを削除した。
インストールが失敗した場合、再インストールの前に「プログラムの追加と削除」でインストールしたインスタンスを削除する必要あり。
厄介なこった。

（参考）
http://awoni.net/fc/sql-server-2008-express/
http://d.hatena.ne.jp/haradago/20100211/p1

MS SQL Server：他のマシンのバックアップを復元した際のログイン名変更

Management Studioにデータベース管理者で接続し、対象のデータベースに対してつぎのクエリを実行する。

EXEC sp_change_users_login 'Update_One', '変更前ログイン名', '変更後ログイン名'

Android SDKのエミュレータ起動（Proxy経由） on Windows

Android SDKのインストールディレクトリ下の tools にパスが通っている前提。
AVD名が「hoge」として、コマンドプロンプトで

> emulator -avd hoge -http-proxy http://172.16.10.11:3128

のようにタイプする。

Perl: Linux -> SQLServer 画像ファイル格納

LinuxマシンからWindowsマシンのSQL ServerにはFreeTDSとunixODBCで接続できるようになっている前提。
データベース接続にはDBIとDBD::ODBCを使う。
DBD::Sybaseではプレースホルダーが使えない（？）のでDBD::ODBCしかないが、動きが怪しい。
レコードが存在する場合にupdateしようとしたがうまくいかない。
そこで、レコードが存在する場合deleteしてinsertすることにしたが、これも同一接続内ではうまくいかない。
仕方ないので、select、deleteで一旦切断し、insert用に再度接続するようにした。
以下サンプル。

#!/usr/bin/perl --

use MIME::Parser;
use DBI;
use warnings;
use strict;

my $dbname  = 'hoge';
my $user_id = "";
my $item_id = '10';

my $parser = new MIME::Parser;
$parser->output_to_core(1);
my $entity;

eval {
    local $SIG{ALRM} = sub { die "timeout" };
    alarm 10;
    $entity = $parser->parse(\*STDIN) or die;
    alarm 0;
};
alarm 0;
if ($@) { exit; }

my $from = $entity->head->get('from');
if ($from =~ /<(.+?\@.+?)>/) {
    $from = $1;
}

$user_id = _check_from($dbname, $from);

if ($user_id ne "" and $entity->is_multipart) {
    _save_attached_file($entity, $dbname, $user_id, $item_id);
}

exit;


sub _connect_db($$$$) {
    my $db_host   = shift;
    my $db_name   = shift;
    my $db_user   = shift;
    my $db_passwd = shift;

    my $data_source = "dbi:ODBC:"
                    . "server=" . $db_host . ";"
                    . "database=" . $db_name;
    my $dbh = DBI->connect($data_source, $db_user, $db_passwd)
                  or die $DBI::errstr;
    $dbh->{AutoCommit} = 0;
    $dbh->{RaiseError} = 1;

    return $dbh;
}

sub _check_from($$) {
    my $dbname  = shift;
    my $from    = shift;
    my $user_id = "";

    my $db_server = 'windows-db';
    my $db_name   = $dbname;
    my $db_user   = 'foo';
    my $db_passwd = 'bar';
    my $dbh
        = _connect_db($db_server, $db_name, $db_user, $db_passwd);
    my $stmt = "SELECT user_id"
             . " FROM users WITH (NOLOCK)"
             . " WHERE email = '$from'";
    my $sth = $dbh->prepare($stmt)
              or die $dbh->errstr;
    $sth->execute or die $dbh->errstr;
    while (my $row = $sth->fetchrow_arrayref) {
        ($user_id) = @$row;
    }
    $sth->finish;
    $dbh->disconnect;

    return $user_id;
}

sub _save_attached_file($$$$) {
    my $entity  = shift;
    my $dbname  = shift;
    my $user_id = shift;
    my $item_id = shift;

    my $parts_count = $entity->parts;
    for (my $i = 1; $i < $parts_count; $i++) {
        my $part_entity = $entity->parts($i) ?
                          $entity->parts($i) :
                          $entity->parts($i)->parts($i);
        my $content_type = $part_entity->head->mime_type;

        if ($content_type =~ /\/(html|plain|alternative)/) {
            next;
        }

        my $attachment = $part_entity->bodyhandle->as_string;
        my $filename   = $part_entity->head->recommended_filename;

        my $db_server = 'earth-db';
        my $db_name   = $dbname;
        my $db_user   = 'foo';
        my $db_passwd = 'bar';
        my $dbh
            = _connect_db($db_server, $db_name, $db_user, $db_passwd);
        my $stmt = "SELECT user_id FROM files WITH(NOLOCK)"
                 . " WHERE user_id = '$user_id'"
                 . " AND item_id = '$item_id'";
        my $sth = $dbh->prepare($stmt) or die $dbh->errstr;
        $sth->execute or die $dbh->errstr;
        my $result_user_id = "";
        while (my $row = $sth->fetchrow_arrayref) {
            ($result_user_id) = @$row;
        }
        $sth->finish;
        if ($result_user_id) {
            $stmt = "DELETE FROM files"
              . " WHERE user_id = '$user_id'"
              . " AND item_id = '$item_id'";
            $sth = $dbh->prepare($stmt) or die $dbh->errstr;
            eval {
                $sth->execute;
                $dbh->commit;
            };
            if ($@) {
                $dbh->rollback or die $dbh->errstr;
            }
        }
        $dbh->disconnect;

        $dbh = _connect_db($db_server, $db_name, $db_user, $db_passwd);
        $stmt = "INSERT INTO files ("
              . "user_id,"
              . "item_id,"
              . "content_type,"
              . "content,"
              . "filename,"
              . "date"
              . ") VALUES ("
              . "'$user_id',"
              . "'$item_id',"
              . "'$content_type',"
              . "?,"
              . "'$filename',"
              . "GETDATE()"
              . ")";
        $sth = $dbh->prepare($stmt) or die $dbh->errstr;
        $sth->bind_param( 1, $attachment, DBI::SQL_LONGVARBINARY );
        eval {
            $sth->execute;
            $dbh->commit;
        };
        if ($@) {
            $dbh->rollback or die $dbh->errstr;
        }
        $dbh->disconnect;

        last;
    }
}

__END__

MS SQL Serverでtext型カラムの文字列を置換

テーブルitemのカラムitem_valueがtext型の場合で実際の値が8000バイト以下なら

UPDATE item SET item_value=REPLACE(CAST(item_value AS varchar(8000)),'foo','bar') WHERE item_id='1'

で「foo」を「bar」に置換できる。

WindowsでCPU使用率をみるコマンド

（2009/06/17perlfreak.vox.comより転載）

typeperf -sc 10 -si 1 "\Processor(_Total)\% Processor Time"

scは回数、siは間隔（秒）。