SQLの窓

2020年08月31日


VBScript : ADO : ODBC接続 : SQLExpress(SQLServer) 接続と通常処理( 更新は SQL で行う )



ドライバは、古い {SQL Server} ドライバを使用していますが、VBS では、{SQL Native Client} を使っても同じだと思います。

SQLExpress(SQLServer) の 32ビット クライアントの設定です

MSSQLServer のレジストリ位置を開く VBScript のダウンロード

※ コメント部分ですが、このサンプルで SQL の更新を接続オブシェクトのメソッドで SQL 文字列を使って実行しています。

フィールドオブジェクトにセットして更新する方法もありますが、他の言語間での移植が困難になるので、このほうが良い環境はたくさんあると思います

関連する記事

SQLExpress 2005 の接続設定
VBScript : ADO : フィールドオブジェクトを使用した同一フォーマットのテーブル間のデータコピー
VBScript : ADO : 純正接続 : SQLExpress(SQLServer) 接続と通常処理

SQLExpress にインポートするデータ(MDB)


' ***********************************************************
' SQLExpress / ODBC / {SQL Server}
' ADO : 文字列更新
' FileSystemObject : CSV出力
' ***********************************************************
strDriver = "{SQL Server}"
strTarget = "reiwa"		' 別名
strDB = "lightbox"
strUser = "sa"
strPass = "passwordpassword"

' ***********************************************************
' ADO + FileSystemObject
' ***********************************************************
Set Cn = CreateObject( "ADODB.Connection" )
Set Rs = CreateObject( "ADODB.Recordset" )
Set Fso = CreateObject( "Scripting.FileSystemObject" )

' **********************************************************
' 接続文字列
' **********************************************************
ConnectionString = _
	"Provider=MSDASQL;" & _
	"Driver=" & strDriver & ";" & _
	"SERVER=" & strTarget & ";" & _
	"DATABASE=" & strDB & ";" & _
	"UID=" & strUser & ";" & _
	"PWD=" & strPass & ";"

' **********************************************************
' 接続
' クライアントカーソル(3)を使う事が推奨されます
' **********************************************************
Cn.CursorLocation = 3
on error resume next
Cn.Open ConnectionString
if Err.Number <> 0 then
	WScript.Echo Err.Description
	Wscript.Quit
end if
on error goto 0

Query = "select * from [社員マスタ]"

' **********************************************************
' レコードセット
' オブジェクト更新時はレコード単位の共有的ロック(3)を
' 使用します( デフォルトでは更新できません )
' ※ デフォルトでも SQLによる更新は可能です
' **********************************************************
'Rs.LockType = 3
on error resume next
Rs.Open Query, Cn
if Err.Number <> 0 then
	Cn.Close
	Wscript.Echo Err.Description
	Wscript.Quit
end if
on error goto 0

' **********************************************************
' 出力ファイルオープン
' **********************************************************
Set Csv = Fso.CreateTextFile( "社員マスタ.csv", True )

' **********************************************************
' タイトル出力
' **********************************************************
Buffer = ""
For i = 0 to Rs.Fields.Count - 1
	if Buffer <> "" then
		Buffer = Buffer & ","
	end if
	Buffer = Buffer & Rs.Fields(i).Name
Next
Csv.WriteLine Buffer

' **********************************************************
' データ出力
' **********************************************************
' UpdateCnt = 0
Do While not Rs.EOF
	Buffer = ""
	For i = 0 to Rs.Fields.Count - 1
		if Buffer <> "" then
			Buffer = Buffer & ","
		end if
		Buffer = Buffer & Rs.Fields(i).Value
	Next

	' 更新
'	strDay = (UpdateCnt mod 10) + 1
'	Query = "update [社員マスタ] set [生年月日] = '2005/01/0" & strDay & "'"
'	Query = Query & " where 社員コード = '" 
'	Query = Query & Rs.Fields("社員コード").Value
'	Query = Query & "'"
	Cn.Execute( Query )

	Csv.WriteLine Buffer
	Rs.MoveNext
'	UpdateCnt = UpdateCnt + 1
Loop

' **********************************************************
' ファイルクローズ
' **********************************************************
Csv.Close
' **********************************************************
' レコードセットクローズ
' **********************************************************
Rs.Close
' **********************************************************
' 接続解除
' **********************************************************
Cn.Close


hanbaic.mdb よりインポートする為の バッチファイル
C:\Windows\SysWOW64\cscript.exe import.vbs
Windows10 64ビットで、Office が 32ビットの場合を想定しています

hanbaic.mdb よりインポートする為の VBScript
' ***********************************************************
' hanbaic.mdb からインポート
' ***********************************************************
strMdbPath = "C:\Users\lightbox\Downloads\hanbaic_mdb\hanbaic.mdb"
strConnect = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & strMdbPath & ";"

' ***********************************************************
' ADO
' ***********************************************************
Set Cn = CreateObject( "ADODB.Connection" )

' **********************************************************
' MDB 用接続文字列
' **********************************************************
ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & strMdbPath & ";"

' **********************************************************
' 接続
' **********************************************************
on error resume next
Cn.Open ConnectionString
if Err.Number <> 0 then
	WScript.Echo Err.Description
	Wscript.Quit
end if
on error goto 0

' **********************************************************
' 実行
' **********************************************************
RefString = "[ODBC;Driver={SQL Server};Server=reiwa;Database=lightbox;Uid=sa;Pwd=passwordpassword]"
Query = "select * into " & RefString & ".[社員マスタ] from [社員マスタ]"

on error resume next
Cn.Execute Query
if Err.Number <> 0 then
	WScript.Echo Err.Description
	Wscript.Quit
end if
on error goto 0

' **********************************************************
' 接続解除
' **********************************************************
Cn.Close






posted by lightbox at 2020-08-31 09:06 | SQLExpress | このブログの読者になる | 更新情報をチェックする

2020年02月11日


Microsoft SQLExpress : sa に空のパスワードを設定する方法


ALTER LOGIN sa WITH CHECK_POLICY = OFF
ALTER LOGIN sa WITH PASSWORD = ''

CHECK_POLICY = OFF を実行しない場合は、以下のようなメッセージが表示されます

メッセージ 15118、レベル 16、状態 1、サーバー VPC-028\SQLEXPRESS、行 1
パスワードの検証に失敗しました。このパスワードは Windows のポリシーで指定された
強力なパスワードの基準を満たすほど複雑ではありません。
関連する Microsoft ドキュメント パスワード ポリシー ALTER LOGIN (Transact-SQL) 強力なパスワード プログラムによるパスワードの変更 別のコンピューターからの接続 関連する Microsoft ダウンロード Microsoft SQL Server 2016 Express Edition SP2 Microsoft SQL Server 2017 Express 関連する記事 SQLExpress 2005 の接続設定 LoginMode のあるレジストリを開くスクリプト ログインモード設定 ( Windows 認証 : 1、混合モード認証 : 2 ) sa を使う場合は 2 です
// レジストリ書き込み用
var WshShell = new ActiveXObject("WScript.Shell");
// 管理者権限実行用
var Shell = new ActiveXObject("Shell.Application");

var path = "HKEY_LOCAL_MACHINE\\SOFTWARE\\Microsoft\\Microsoft SQL Server";

var sqs = WshShell.RegRead( path + "\\Instance Names\\SQL\\SQLEXPRESS" );

path = path + "\\" + sqs + "\\MSSQLServer"

openReg(path)

function openReg(path) {

	// 既に regedit が実行中の場合はいったん終了させます
	Shell.ShellExecute( "taskkill.exe", "/fi \"imagename eq regedit.exe\"", "", "runas", 1 );

	// 1秒スリープ
	WScript.Sleep(1000);
	// WshShell.Run( "timeout /T 1", 0, true );

	// レジストリエディタが最後に開いていたキーの登録を行います
	var strPath = "Software\\Microsoft\\Windows\\CurrentVersion\\Applets\\Regedit\\LastKey";
	WshShell.RegWrite( "HKCU\\" + strPath, path, "REG_SZ" );

	// レジストリエディタを起動します
	WshShell.Run( "regedit.exe" )

}


別名が定義された場所を開くスクリプト
// レジストリ書き込み用
var WshShell = new ActiveXObject("WScript.Shell");
// 管理者権限実行用
var Shell = new ActiveXObject("Shell.Application");

var path = "HKEY_LOCAL_MACHINE\\SOFTWARE\\Microsoft\\MSSQLServer\\Client\\ConnectTo";

openReg(path)

function openReg(path) {

	// 既に regedit が実行中の場合はいったん終了させます
	Shell.ShellExecute( "taskkill.exe", "/fi \"imagename eq regedit.exe\"", "", "runas", 1 );

	// 1秒スリープ
	WScript.Sleep(1000);
	// WshShell.Run( "timeout /T 1", 0, true );

	// レジストリエディタが最後に開いていたキーの登録を行います
	var strPath = "Software\\Microsoft\\Windows\\CurrentVersion\\Applets\\Regedit\\LastKey";
	WshShell.RegWrite( "HKCU\\" + strPath, path, "REG_SZ" );

	// レジストリエディタを起動します
	WshShell.Run( "regedit.exe" )

}

SQL Server の別名定義用のソフトウェアが無い場合、ODBC ドライバの SQLServer の接続用構成の、クライアントの設定ボタンで確認あるいは定義してみて下さい。( ポートは 固定で 1433 を指定し、サーバ側も固定で 1433 を指定して ファイアウォールのポートを開きます  )



※ ODBC で接続を成功完了する必要があります


関連する Microsoft ドキュメント

SQL Server のバージョンとエディションを識別する方法


このページの PDF


posted by lightbox at 2020-02-11 11:25 | SQLExpress | このブログの読者になる | 更新情報をチェックする

2019年12月26日


SQLExpress バッチ DB バックアップ

タスクスケジューラにバッチファイルを登録して運用しています。Windows Server に登録していますが、注意するのは『開始オプション』のフィールドにバッチファイルが存在するフォルダを指定する事と、ユーザのログオンの有無にかかわらず実行可能にするという事です( 可能なら最上位の特権で )

バックアップなので、同じ PC 上に保存しても意味無いのでネットワーク上の別のサーバーへコピーしています。このバッチを2種類作り、保存フォルダも二つ作って1日おきに交互にコピーするようにしています。

backup.sql
use DB名
go
BACKUP DATABASE DB名 TO DISK = 'C:\保存場所\DB保存名.dat'
go


バッチファイル
del C:\保存場所\DB保存名.dat

sqlcmd -E -S .\SQLEXPRESS -t 3600 -i C:\batch\db\backup.sql

net use x: \\PC名\共有名 パスワード /USER:ユーザ名

copy C:\保存場所\DB保存名.dat X:\共有内保存フォルダ

net use x: /DELETE /yes

※ 3600 は タイムアウト設定

関連する情報

リストア

このページの PDF



posted by lightbox at 2019-12-26 14:05 | SQLExpress | このブログの読者になる | 更新情報をチェックする

2018年10月09日


C# : VB.net : SQLExpress(SQLServer) : SQL-DMO と同等の SMO によるバックアップ

SQL-DMO は、COM なので主に VBScript から使われていましたが、SMO は、SQL-DMO に取って代わる Framework ベースの API です。もともとが巨大なクラスの集合体なので、バックアップにしか通常使う事がありませんが、SQLServer の運用を行うなら知っておく必要はあります

バックアップは SQL からも出来、そちらのほうが簡単です。SQL-DMO や SMO は、テープによるデータのバックアップを想定しており、デバイスを作成しても実体は作成されません。ディスクファイルでも、追加でバックアップされます

バックアップ後の内容の確認は以下のようにして行います
RESTORE FILELISTONLY FROM DISK = 'C:\tmp\backup.dmp'
参照としては以下の5つを追加します 1) Microsoft.SqlServer.ConnectionInfo 2) Microsoft.SqlServer.Management.Sdk.Sfc 3) Microsoft.SqlServer.Smo 4) Microsoft.SqlServer.SmoExtended 5) Microsoft.SqlServer.SqlEnum SMO ライブラリのダウンロードは nuget.exe が簡単です。 nuget.exe をダウンロードして専用のフォルダで実行します。
nuget install Microsoft.SqlServer.SqlManagementObjects -Version 140.17283.0
※ バージョン選択の参考ページ nuget.exe と同じ場所に、Microsoft.SqlServer.SqlManagementObjects.140.17283.0 フォルダが作成されるので、lib\net40 内から dll を参照して使用します。 ▼ C#
// サーバー
Server srv;
// インスタンス
srv = new Server();

// サーバーインスタンスの情報
srv.ConnectionContext.AutoDisconnectMode = AutoDisconnectMode.NoAutoDisconnect;
srv.ConnectionContext.LoginSecure = false;
srv.ConnectionContext.Login = "sa";
srv.ConnectionContext.Password = "パスワード";
// 接続
srv.ConnectionContext.Connect();

// バージョンの表示
Console.WriteLine(srv.Information.Version);

// デバイスオブジェクト
BackupDevice backup_dev = new BackupDevice();

// サーバーと関係付ける
backup_dev.Parent = srv;
// 論理名を設定
backup_dev.Name = "BACKUP";
// 装置のタイプ
backup_dev.BackupDeviceType = BackupDeviceType.Disk;
// 実際の場所( バックアップするまで作成されません )
backup_dev.PhysicalLocation = @"c:\tmp\backup.dmp";

// 処理サンプルとして一旦削除して作成する
if (srv.BackupDevices["BACKUP"].State == SqlSmoState.Existing)
{
	srv.BackupDevices["BACKUP"].Drop();
	backup_dev.Create();
}

// バックアップオブジェクト
Backup dbBackup = new Backup();

// デフォルトは Database
dbBackup.Action = BackupActionType.Database;
dbBackup.Database = "lightbox";
dbBackup.Devices.AddDevice("BACKUP", DeviceType.LogicalDevice);
dbBackup.BackupSetName = "BACKUP_lightbox";

// バックアップ開始
dbBackup.SqlBackup(srv);

// 接続解除
srv.ConnectionContext.Disconnect();

Console.WriteLine("バックアップが終了しました");

▼ VB.net
' サーバー
Dim srv As Server
' インスタンス
srv = New Server()

' サーバーインスタンスの情報
srv.ConnectionContext.AutoDisconnectMode = AutoDisconnectMode.NoAutoDisconnect
srv.ConnectionContext.LoginSecure = False
srv.ConnectionContext.Login = "sa"
srv.ConnectionContext.Password = "パスワード"
' 接続
srv.ConnectionContext.Connect()

' バージョンの表示
Console.WriteLine(srv.Information.Version)

' デバイスオブジェクト
Dim backup_dev As BackupDevice = New BackupDevice()

' サーバーと関係付ける
backup_dev.Parent = srv
' 論理名を設定
backup_dev.Name = "BACKUP"
' 装置のタイプ
backup_dev.BackupDeviceType = BackupDeviceType.Disk
' 実際の場所( バックアップするまで作成されません )
backup_dev.PhysicalLocation = "c:\tmp\backup.dmp"
Try
	' 既に存在する場合はそのまま使う
	backup_dev.Create()
Catch ex As Exception
End Try


' バックアップオブジェクト
Dim dbBackup As Backup = New Backup()

' デフォルトは Database
dbBackup.Action = BackupActionType.Database
dbBackup.Database = "lightbox"
dbBackup.Devices.AddDevice("BACKUP", DeviceType.LogicalDevice)
dbBackup.BackupSetName = "BACKUP_lightbox"

' バックアップ開始
dbBackup.SqlBackup(srv)

' 接続解除
srv.ConnectionContext.Disconnect()

Console.WriteLine("バックアップが終了しました")

関連する記事

SQLServer : SQLでバックアップ
SQL-DMO による VBScriptによるバックアップ

関連するMicrosoft のリンク

SMO クラス ライブラリ
SQL-DMO のインストール( SQLServer2005_BC.msi )



posted by lightbox at 2018-10-09 15:40 | SQLExpress | このブログの読者になる | 更新情報をチェックする

2018年09月01日


各RDBMS の正規表現の実装状況 : MySQL, SQLServer, PostgreSQL, Oracle

今日この日まで、SQL の条件に『正規表現』を使うという発想が無かったのですが(たいていそんな複雑な事はしないし解りにくい)、最近は日本人以外のカタカナ氏名の検索をする事が多くなった事を起因とする要望から、『どうなんだろう』と思って調べた結果です。( 外国人のカタカナ表記では微妙にゆらぎがあるのです )

※ 結果的にウチは、SQLServer なので、or で文字列作っていくよりは楽になりそうです。

SQLServer

検索条件のパターン照合

LIKE で使用される % や _ 以外に、LIKE 対象の文字列に [] と [^] が使用できます。これだけでも文字列作成が格段に楽です。

ただ、他の RDBMS と比べると『正規表現』としては一番未熟です。


MySQL

3.3.4.7 パターンマッチング

12.5.2 正規表現

LIKE では無く REGEXP を使用して、『select * from 社員マスタ where フリガナ REGEXP '.+オ.+'』みたいな書き方ができます。正規表現として使える演算子はそれほど多くはありませんが、逆に単純で使いやすいとは思います。

PostgreSQL

9.7.3. POSIX正規表現

正規表現をそのまま使える記法のようで、~( チルダ ) を使用して、where の中で検索できるようです。

例) select * from テーブル名 where 列名 ~ '[a-zA-Z]'

Oracle

REGEXP_LIKE

どうやら関数のようなものらしく、where の中で利用すると正規表現に一致する行が戻されるようです。
( 第三引数で、関数のパターン一致オプション )







posted by lightbox at 2018-09-01 01:31 | SQLExpress | このブログの読者になる | 更新情報をチェックする

2014年10月14日


SQLServer の OPENDATASOURCE 関数による Excel の参照( Microsoft.Jet.OLEDB.4.0 と Microsoft.ACE.OLEDB.12.0 )

OPENDATASOURCE 関数を使用可能にするには、まず show advanced options を 1 にして、詳細オプションを可能に設定して反映するために、RECONFIGURE を実行します。その後、Ad Hoc Distributed Queries を 1 に設定してから RECONFIGURE を実行する必要があります
さらに、Excel 2007 以降の(拡張子 .xlsx)で、 Microsoft.ACE.OLEDB.12.0 プロバイダを利用する場合は、以下のコマンドを実行する必要があります

EXEC sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1
GO
EXEC sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1
GO
Microsoft ドキュメントsp_configure Microsoft のサンプルのように実行するとエラーになります。設定単位で GO しないとエラーになります。 一度設定すると設定は保持されます。SQL の実行前に毎回行う必要はありません 基本設定
sp_configure 'show advanced options', 1
RECONFIGURE
GO
sp_configure 'Ad Hoc Distributed Queries', 1
RECONFIGURE
GO


Microsoft.ACE.OLEDB.12.0用
EXEC sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1
GO
EXEC sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1
GO

それぞれのプロバイダでの実行
select * from
OPENDATASOURCE(
	'Microsoft.Jet.OLEDB.4.0',
	'Data Source="C:\user\data\商品.xls";Extended properties=Excel 8.0'
)...[Sheet1$]

select * from
OPENDATASOURCE(
	'Microsoft.ACE.OLEDB.12.0',
	'Data Source="C:\user\data\商品.xlsx";Extended properties=Excel 12.0'
)...[Sheet1$]


▼ ローカルの SQLServer(SQLExpress)へコマンドプロンプトから接続する一例
sqlcmd -U sa -P password -S .\SQLEXPRESS
sqlcmd を使った資料 SQLExpress 2005( および 2008 ) の接続設定 参考記事 Excel Import to SQL Server using Distributed Queries ( Microsoft.ACE.OLEDB.12.0 を使えるようにする為の処理が記述されていました ) 関連する記事 VBScript : ADO で Excel2007(Excel 2010、Excel 2013) にアクセスする( シートをテーブルとして読み込む ) ( VBScript から直接 Excel を DB として読む為の接続文字列と処理です )
posted by lightbox at 2014-10-14 21:47 | SQLExpress | このブログの読者になる | 更新情報をチェックする
Seesaa の各ページの表示について
Seesaa の 記事がたまに全く表示されない場合があります。その場合は、設定> 詳細設定> ブログ設定 で 最新の情報に更新の『実行ボタン』で記事やアーカイブが最新にビルドされます。

Seesaa のページで、アーカイブとタグページは要注意です。タグページはコンテンツが全く無い状態になりますし、アーカイブページも歯抜けページはコンテンツが存在しないのにページが表示されてしまいます。

また、カテゴリページもそういう意味では完全ではありません。『カテゴリID-番号』というフォーマットで表示されるページですが、実際存在するより大きな番号でも表示されてしまいます。

※ インデックスページのみ、実際の記事数を超えたページを指定しても最後のページが表示されるようです

対処としては、このようなヘルプ的な情報を固定でページの最後に表示するようにするといいでしょう。具体的には、メインの記事コンテンツの下に『自由形式』を追加し、アーカイブとカテゴリページでのみ表示するように設定し、コンテンツを用意するといいと思います。


※ エキスパートモードで表示しています

アーカイブとカテゴリページはこのように簡単に設定できますが、タグページは HTML 設定を直接変更して、以下の『タグページでのみ表示される内容』の記述方法で設定する必要があります

<% if:page_name eq 'archive' -%>
アーカイブページでのみ表示される内容
<% /if %>

<% if:page_name eq 'category' -%>
カテゴリページでのみ表示される内容
<% /if %>

<% if:page_name eq 'tag' -%>
タグページでのみ表示される内容
<% /if %>
この記述は、以下の場所で使用します
container 終わり



フリーフォントで簡単ロゴ作成
フリーフォントでボタン素材作成
フリーフォントで吹き出し画像作成
フリーフォントではんこ画像作成
ほぼ自由に利用できるフリーフォント
フリーフォントの書体見本とサンプル
画像を大きく見る為のウインドウを開くボタンの作成

CSS ドロップシャドウの参考デモ
イラストAC
ぱくたそ
写真素材 足成
フリーフォント一覧
utf8 文字ツール
右サイド 終わり
base 終わり