SQLの窓

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 | このブログの読者になる | 更新情報をチェックする

2014年07月14日


SQLServer(SQLExpress) : SQL学習用販売管理データとインポート用スクリプト



■ プログラム名
SQLExpress(SQLServer)用学習用販売管理データ作成

■ 概要

販売管理B.mdb に格納されている販売管理用のテーブルとビューを SQLExpress(SQLServer) にインポートします

■ 実行方法

1) データベースを前もって作成して下さい
2) hanbaiB_ss.vbs の strDb を作成したデータベース名に変更
3) strPwd を正しいパスワードに変更
4) エクスプローラから、hanbaiB_ss.vbs をダブルクリックして下さい

Crun()



' 別名 または SQLServer のインスタンス文字列を指定します
' インスタンス文字列は、PC名\インスタンス名 ですが、
' SQLExpress2005 ならば .\SQLEXPRESS で接続できるかもしれません
' ( 別名が確実です )
' 【以下URLは別名等接続設定参考ページ】
' http://lightbox.matrix.jp/ginpro/patio.cgi?mode=view&no=228
' NIGHT_TCP は別名定義です
strCon = "NIGHT_TCP"

' ▼ SQLExpress2012 / .\sqlexpress or localhost\sqlexpress or pcname\sqlexpress
strCon = ".\sqlexpress"

' データベース
' 事前に create database 文で作成する必要があります
' 上記URLを参考に、sqlcnmd を使って作成します
strDb = "lightbox"

' ユーザ
' 上記URLの設定で、sa が有効になります
strUser = "sa"

' パスワード
' 上記URLの設定で、パスワードを設定できます
strPwd = "passwordpassword"

' 使用する ODBC ドライバです
' SQLServer 2000 より後のバージョンですと、{SQL Native Client} も利用できます
' ODBC アドミニストレータで確認して下さい
strDriver = "{SQL Server}"
' strDriver = "{SQL Native Client}"
' ▼ SQLExpress2012
' strDriver = "{SQL Server Native Client 11.0}"


' ************************************************
' 基本設定
' ************************************************
' このスクリプトが存在するディレクトリを取得
strCurDir = WScript.ScriptFullName
strCurDir = Replace( strCurDir, WScript.ScriptName, "" )
strMdbPath = strCurDir & "販売管理B.mdb"

strMessage = "対象 MDB は " & strMdbPath & "です" & vbCrLf & vbCrLf

strMessage = strMessage & "↓SQLServerの環境です" & vbCrLf
strMessage = strMessage & "SERVER : " & strCon & vbCrLf
strMessage = strMessage & "DB : " & strDb & vbCrLf
strMessage = strMessage & "USER : " & strUser & vbCrLf
strMessage = strMessage & "PASS : " & strPwd & vbCrLf & vbCrLf

strMessage = strMessage & "既にテーブルが存在する場合はメッセージが出ません" & vbCrLf
strMessage = strMessage & "それ以外ではエラーメッセージが出ますが、問題ありません"
if vbCancel = MsgBox( strMessage, vbOkCancel ) then
	Wscript.Quit
end if

' ************************************************
' 処理用文字列設定
' ************************************************
' MDB の接続文字列
strConnectMdb = _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & strMdbPath & ";"

strConnectSQLServer = _
" in '' [ODBC;Driver="&strDriver&";SERVER=" & _
strCon & ";Database=" & strDb & ";UID=" & strUser & ";PWD=" & strPwd & "]"

strConnectSQLServer2 = _
"Provider=MSDASQL;Driver="&strDriver&";SERVER=" & _
strCon & ";Database=" & strDb & ";UID=" & strUser & ";PWD=" & strPwd

' ************************************************
' 初期処理
' ************************************************
Set Cn = CreateObject("ADODB.Connection")
Set Cn2 = CreateObject("ADODB.Connection")
Cn.Open strConnectMdb
Cn2.Open strConnectSQLServer2

' ************************************************
' コード名称マスタ
' ************************************************
Query = _
"create table コード名称マスタ (" & _
"	区分 INT" & _
"	,コード VARCHAR(10)" & _
"	,名称 NVARCHAR(50)" & _
"	,数値1 INT" & _
"	,数値2 INT" & _
"	,作成日 DATETIME" & _
"	,更新日 DATETIME" & _
"	,primary key(区分,コード)" & _
")"
Call SSTransfer( "コード名称マスタ", Query )

' ************************************************
' コントロールマスタ
' ************************************************
Query = _
"create table コントロールマスタ (" & _
"	キー VARCHAR(1)" & _
"	,売上日付 DATETIME" & _
"	,売上伝票 INT" & _
"	,primary key(キー)" & _
")"
Call SSTransfer( "コントロールマスタ", Query )

' ************************************************
' メッセージマスタ
' ************************************************
Query = _
"create table メッセージマスタ (" & _
"	コード VARCHAR(4)" & _
"	,メッセージ NVARCHAR(100)" & _
"	,primary key(コード)" & _
")"
Call SSTransfer( "メッセージマスタ", Query )

' ************************************************
' 取引データ
' ************************************************
Query = _
"create table 取引データ (" & _
"	取引区分 VARCHAR(2)" & _
"	,伝票番号 INT" & _
"	,行 INT" & _
"	,取引日付 DATETIME" & _
"	,取引先コード VARCHAR(4)" & _
"	,商品コード VARCHAR(4)" & _
"	,数量 INT" & _
"	,単価 INT" & _
"	,金額 INT" & _
"	,primary key(取引区分,伝票番号,行)" & _
")"
Call SSTransfer( "取引データ", Query )

' ************************************************
' 商品マスタ
' ************************************************
Query = _
"create table 商品マスタ (" & _
"	商品コード VARCHAR(4)" & _
"	,商品名 NVARCHAR(50)" & _
"	,在庫評価単価 INT" & _
"	,販売単価 INT" & _
"	,商品分類 VARCHAR(3)" & _
"	,商品区分 VARCHAR(1)" & _
"	,作成日 DATETIME" & _
"	,更新日 DATETIME" & _
"	,primary key(商品コード)" & _
")"
Call SSTransfer( "商品マスタ", Query )

' ************************************************
' 商品分類マスタ
' ************************************************
Query = _
"create table 商品分類マスタ (" & _
"	商品分類 VARCHAR(3)" & _
"	,名称 NVARCHAR(50)" & _
"	,作成日 DATETIME" & _
"	,更新日 DATETIME" & _
"	,primary key(商品分類)" & _
")"
Call SSTransfer( "商品分類マスタ", Query )

' ************************************************
' 得意先マスタ
' ************************************************
Query = _
"create table 得意先マスタ (" & _
"	得意先コード VARCHAR(4)" & _
"	,得意先名 NVARCHAR(50)" & _
"	,得意先区分 VARCHAR(1)" & _
"	,担当者 VARCHAR(4)" & _
"	,郵便番号 VARCHAR(7)" & _
"	,住所1 NVARCHAR(100)" & _
"	,住所2 NVARCHAR(100)" & _
"	,作成日 DATETIME" & _
"	,更新日 DATETIME" & _
"	,primary key(得意先コード)" & _
")"
Call SSTransfer( "得意先マスタ", Query )

' ************************************************
' 社員マスタ
' ************************************************
Query = _
"create table 社員マスタ (" & _
"	社員コード VARCHAR(4)" & _
"	,氏名 NVARCHAR(50)" & _
"	,フリガナ NVARCHAR(50)" & _
"	,所属 VARCHAR(4)" & _
"	,性別 INT" & _
"	,作成日 DATETIME" & _
"	,更新日 DATETIME" & _
"	,給与 INT" & _
"	,手当 INT" & _
"	,管理者 VARCHAR(4)" & _
"	,primary key(社員コード)" & _
")"
Call SSTransfer( "社員マスタ", Query )

' ************************************************
' 郵便番号マスタ
' ************************************************
Query = _
"create table 郵便番号マスタ (" & _
"	郵便番号 VARCHAR(7)" & _
"	,都道府県名カナ NVARCHAR(255)" & _
"	,市区町村名カナ NVARCHAR(255)" & _
"	,町域名カナ NVARCHAR(255)" & _
"	,都道府県名 NVARCHAR(255)" & _
"	,市区町村名 NVARCHAR(255)" & _
"	,町域名 NVARCHAR(255)" & _
")"
Call SSTransfer( "郵便番号マスタ", Query )

' ************************************************
' ビュー
' ************************************************
RunSS( "drop view V_商品一覧" )
Query = _
"create view V_商品一覧 as" & _
"	SELECT 商品マスタ.商品コード" & _
"	, 商品マスタ.商品名" & _
"	, 商品マスタ.販売単価" & _
"	, 商品分類マスタ.商品分類" & _
"	, 商品分類マスタ.名称 AS 分類名" & _
"	, 商品マスタ.商品区分" & _
"	, コード名称マスタ.名称 AS 区分名" & _
" from" & _
"	(商品マスタ LEFT JOIN 商品分類マスタ" & _
"	ON 商品マスタ.商品分類 = 商品分類マスタ.商品分類" & _
"	) LEFT JOIN コード名称マスタ" & _
"	ON 商品マスタ.商品区分 = コード名称マスタ.コード" & _
" where" & _
"	コード名称マスタ.区分 = 3"
RunSS( Query )

RunSS( "drop view V_売上日付" )
Query = _
"create view V_売上日付 as" & _
"	SELECT コントロールマスタ.売上日付" & _
"	FROM コントロールマスタ" & _
"	WHERE コントロールマスタ.キー = '1'"
RunSS( Query )

RunSS( "drop view V_得意先台帳" )
Query = _
"create view V_得意先台帳 as" & _
"	SELECT 取引データ.取引先コード" & _
"	, 得意先マスタ.得意先名" & _
"	, 取引データ.取引日付" & _
"	, 取引データ.取引区分" & _
"	, 取引データ.伝票番号" & _
"	, 取引データ.行" & _
"	, 取引データ.商品コード" & _
"	, 商品マスタ.商品名" & _
"	, 取引データ.数量" & _
"	, 取引データ.単価" & _
"	, 取引データ.金額" & _
" from" & _
"	(取引データ INNER JOIN 商品マスタ" & _
"	ON 取引データ.商品コード=商品マスタ.商品コード" & _
"	) INNER JOIN 得意先マスタ" & _
"	ON 取引データ.取引先コード=得意先マスタ.得意先コード" & _
" where" & _
"	取引データ.取引区分 = '10'"
RunSS( Query )

RunSS( "drop view V_社員一覧" )
Query = _
"create view V_社員一覧 as" & _
" select 社員コード" & _
"	,氏名" & _
"	,フリガナ" & _
"	,名称1.名称 as 性別" & _
"	,所属" & _
"	,名称2.名称 as 所属名" & _
" from 社員マスタ" & _
"	,コード名称マスタ 名称1" & _
"	,コード名称マスタ 名称2" & _
" where 性別 = 名称1.コード" & _
"   and 名称1.区分 = 1" & _
"   and 所属 = 名称2.コード" & _
"   and 名称2.区分 = 2"
RunSS( Query )

' ************************************************
' 終了
' ************************************************
Query = "drop table 転送用テーブル"
RunSS( Query )

Cn2.Close
Cn.Close

Wscript.Echo "処理が終了しました"

' ************************************************
' SQLServer 転送
' ************************************************
function SSTransfer( strTable, QueryCreate )

	Dim Query

	Query = "drop table " & strTable
	RunSS( Query )

	RunSS( QueryCreate )

	Query = "drop table 転送用テーブル"
	RunSS( Query )
	Query = "select * into 転送用テーブル" & strConnectSQLServer & _
	" from " & strTable
	RunMdb( Query )

	Query = "insert into " & strTable & _
	" select * from 転送用テーブル"
	RunSS( Query )

end function

' ************************************************
' MDB 実行
' ************************************************
function RunMdb( Query )

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

end function

' ************************************************
' SQLServer 実行
' ************************************************
function RunSS( Query )

	on error resume next
	Cn2.Execute Query
	if Err.Number <> 0 then
		if Query <> "drop table 転送用テーブル" then
			Wscript.Echo Err.Description
		end if
	end if
	on error goto 0

end function


' **********************************************************
' Cscript.exe で実行を強制
' ウィンドウをアクティブにし、最大化ウィンドウとして表示(3)
' Cscript.exe の実行終了後 pause で一時停止
' **********************************************************
Function Crun( )

	Dim str,WshShell

	str = WScript.FullName
	str = Right( str, 11 )
	str = Ucase( str )
	if str <> "CSCRIPT.EXE" then
		str = WScript.ScriptFullName

		Set WshShell = CreateObject( "WScript.Shell" )

		strParam = " "
		For I = 0 to Wscript.Arguments.Count - 1
			if instr(Wscript.Arguments(I), " ") < 1 then
				strParam = strParam & Wscript.Arguments(I) & " "
			else
				strParam = strParam & Dd(Wscript.Arguments(I)) & " "
			end if
		Next
		Call WshShell.Run( "cmd.exe /c cscript.exe " & Dd(str) & strParam & " & pause", 3 )
		WScript.Quit
	end if

End Function
' **********************************************************
' 文字列を " で囲む関数
' **********************************************************
Function Dd( strValue )

	Dd = """" & strValue & """"

End function

Crun は、Cscript.exe(コマンドプロンプト) で実行を強制する処理です。最後に pause で停止するようになっています。

エクスポートに、in 句を使っていますが、参照構文は以下のようになっています。
strConnectSQLServer = _
" in '' [ODBC;Driver="&strDriver&";SERVER=" & _
strCon & ";Database=" & strDb & ";UID=" & strUser & ";PWD=" & strPwd & "]"
これは、結構昔からある ODBC テーブルへのリンク用の構文を使用しています。ODBC テーブルを開く接続文字列は、以下の形式を持ちます。

"ODBC; connectstring"



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

2013年05月10日


SQLExpress 2005( および 2008 ) の接続設定

Microsoft の Visual Studio をインストールすると拒否しないかぎり、SQLExpress がインストールされますが、この DB は本来とても使いやすく優秀な DB なのに、一般の DB として使えないような設定でインストールされています。

これを使えるように設定するには、かなり煩雑な処理を行う必要があり、過去まとめておいたものを今回 Windows7 で設定したのでさらにわかり易く更新しました。

SQLExpress 2005( および 2008 ) の接続設定



とにかく、素人では設定に失敗するように意図されていると思えるほど妙なトラップがたくさんあります。経験者なら15分ほどで使えるようにできますが、未経験者では小さなところで失敗するので本文を良く読んで正しく設定して下さい。

SQLExpress は、フリーですが、そもそも製品版との差は容量が数G しか使えなかったくらいだと記憶しています。ですから、たいていの処理で問題無く使えます。( 後、管理用ツールが無いので全て自分で運用する必要があるだけです )

注意事項

DB の管理を真剣にするならば、別途ユーザを作るのですが、ここでは sa という最上位ユーザを使えるようにして運用する事を想定しています

また、sa を使えるようにできる Windows ユーザは、デフォルトではインストールしたユーザのみです
posted by lightbox at 2013-05-10 14:26 | SQLExpress | このブログの読者になる | 更新情報をチェックする

2013年02月20日


SQLExpress 内に存在する表の CREATE 文を取得する

SQL-DMO を使って VBScript で簡単に取得する事ができます。但し、SQL-DMO は最新の環境からは削除されています。
SQL Server データベース管理オブジェクト (SQL-DMO) は、SQL Server 2008 R2 Express および SQL Server 2008 R2 Feature Pack から削除されました
ですが、個別にインストールしてサポートできるようになっています。
Microsoft SQL Server 2005 の旧バージョンとの互換性コンポーネントSQL Server 下位互換パッケージには、最新バージョンの DTS (データ変換サービス) 2000 ランタイム、SQL 分散管理オブジェクト (SQL-DMO)、意思決定支援オブジェクト (DSO)、SQL 仮想デバイス インターフェイス (SQLVDI) が含まれています。これらのバージョンは SQL Server 2005 および SQL Server 2008 との互換性を維持するために更新されたもので、SQL Server 2000 Service Pack 4 (SP4) と SQL Server 2005 SP2 で配布されたすべての修正が含まれています。

対象: カスタマー、パートナー、開発者

X86 パッケージ (SQLServer2005_BC.msi)
X64 パッケージ (SQLServer2005_BC.msi)
IA64 パッケージ (SQLServer2005_BC.msi)
インストール時の選択 インストールするとすぐ利用可能になるので、以下のスクリプトを利用可能です。
' **********************************************************
' 変数定義
' **********************************************************
Dim Server : Server = "サーバ"
Dim Db : Db = "データベース"
Dim User : User = "sa"
Dim Pass : Pass = "パスワード"
Dim ListTable : ListTable = "TR_出席,TR_成績2,TR_成績3"
Dim List : List = Split( ListTable, "," )

Dim objServer,objDatabase,objTable,Fso,TargetFile

Set Fso = CreateObject( "Scripting.FileSystemObject" )

' **********************************************************
' オブジェクト作成
' **********************************************************
on error resume next
Set objServer = CreateObject("SQLDMO.SQLServer")
if Err.Number <> 0 then
	Wscript.Echo "SQL-DMO はインストールされていません"
	Wscript.Quit
end if
on error goto 0

' **********************************************************
' 接続
' **********************************************************
on error resume next
Call objServer.Connect( Server, User, Pass )
if Err.Number <> 0 then
	Wscript.Echo "接続に失敗しました [" & Err.Description & "]"
	Wscript.Quit
end if
on error goto 0

' **********************************************************
' データーベースオブジェクト取得
' **********************************************************
on error resume next
Set objDatabase = objServer.Databases(Db)
if Err.Number <> 0 then
	Wscript.Echo Err.Description
	Wscript.Quit
end if
on error goto 0

' **********************************************************
' テーブルオブジェクト
' **********************************************************
Set TargetFile = Fso.OpenTextFile( "定義ファイル.sql", 2, True )
on error resume next
For i = 0 to Ubound( List )
	Set objTable = objDatabase.Tables(List(i))
	if Err.Number <> 0 then
		Wscript.Echo Err.Description
		Wscript.Quit
	end if

	' 定義を一つ出力
	TargetFile.WriteLine objTable.Script()
Next
on error goto 0
TargetFile.Close()

' **********************************************************
' 切断
' **********************************************************
Call objServer.DisConnect()


CREATE 文は システムが出力しているので、作成した時と違った厳密な記述がされているので注意して下さい。

作成時

CREATE TABLE TR_成績3 (
	コード int,
	行1 varchar (20),
	行2 varchar (20),
	行3 varchar (20),
	行4 varchar (20),
	行5 varchar (20),
	行6 varchar (20),
	行7 varchar (20),
	行8 varchar (20),
	備考 varchar (1024),
	PRIMARY KEY (コード)
)
システムからの出力
CREATE TABLE [TR_成績3] (
	[コード] [int] NOT NULL ,
	[行1] [varchar] (20) COLLATE Japanese_CI_AS NULL ,
	[行2] [varchar] (20) COLLATE Japanese_CI_AS NULL ,
	[行3] [varchar] (20) COLLATE Japanese_CI_AS NULL ,
	[行4] [varchar] (20) COLLATE Japanese_CI_AS NULL ,
	[行5] [varchar] (20) COLLATE Japanese_CI_AS NULL ,
	[行6] [varchar] (20) COLLATE Japanese_CI_AS NULL ,
	[行7] [varchar] (20) COLLATE Japanese_CI_AS NULL ,
	[行8] [varchar] (20) COLLATE Japanese_CI_AS NULL ,
	[備考] [varchar] (1024) COLLATE Japanese_CI_AS NULL ,
	 PRIMARY KEY  CLUSTERED 
	(
		[コード]
	)  ON [PRIMARY] 
) ON [PRIMARY]
GO
タグ:SQL VBScript
posted by lightbox at 2013-02-20 15:00 | SQLExpress | このブログの読者になる | 更新情報をチェックする

2012年12月18日


ファイルから直接データベースを別のPCでリストアする / SQLExpress

SQLExpress 2008 バッチDB バックアップ でパックアップしたファイルを別の PC でリストアします。ここでは、MYDB というデータベースを MYDB.dat というファイルでバックアップしたものと考えます。

まず、MYDB.dat をどこに置いてもいいわけでは無く、ログインしているユーザがフルコントロールの権限を持つ場所に保存します。

その後内部の保存した時の情報を MYDB.dat の中から読み取る為に以下のSQL を sqlcmd で実行します。

sqlcmd -E -S .\SQLEXPRESS -i コマンドが書かれたL.txt
※ owner を sa にしたい場合は、-E では無く sa とパスワードで -U sa -P パスワード でログイン情報を使用します
RESTORE FILELISTONLY FROM DISK = 'C:\temp\MYDB.dat'
go
情報として、保存した時の論理名と、物理パスの情報がセットで存在し、データとログと合わせて4つの情報が必要になります。通常、データベース名と論理名は一致しているものですが、そもそも複製データーベースだと論理名が違う場合があります。その場合は最後に論理名も変更しないとオリジナルと同じ構成にならないので注意して下さい

これによって、元の情報が手に入るので、リストアする PC の環境を 『目的のDBが使用している物理ファイルのパスの確認 / SQLExpress 』で得たうえで以下のようなコマンドを作成して実行します
※ 必ずしもこのディレクトリ内に置く必要は無いはずですが、今回は管理上同じ場所へリストアします
RESTORE DATABASE MYDB 
	FROM DISK = 'C:\temp\MYDB.dat'
WITH
	MOVE 'MYDB' TO 'c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\MYDB.mdf',
	MOVE 'MYDB_log' TO 'c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\MYDB_log.LDF'
go

これは、ファイル内の論理名を使って、現在の PC のどの場所にリストアするかを決めるものです。

※ 削除する場合は、use master をしてから、drop database MYDB です。


タグ:SQLExpress DB
posted by lightbox at 2012-12-18 16:19 | SQLExpress | このブログの読者になる | 更新情報をチェックする

目的のDBが使用している物理ファイルのパスの確認 / SQLExpress

まず、コマンドファイル(sp.txt)を作成し、sqlcmd を実行します。標準出力では無く、ファイルに出力したい場合は、-o ファイル名を追加します。

sqlcmd -E -S .\SQLEXPRESS -i sp.txt

-E は、信頼関係接続です。SQLExpress がインストールされている PC から実行するのが最も確実であると思いますが、他の PC より実行したい場合は -U と -P でユーザとパスワードを指定する必要があるかもしれません。

sqlcmd -U sa -P password -S pc-000\SQLEXPRESS -i sp.txt

sp.txt
-- lightbox は、DB名です
use lightbox
go
-- sp_helpfile はシステムストアードプロシージャです
-- ドキュメントは http://msdn.microsoft.com/ja-jp/library/ms174307.aspx
sp_helpfile
go


▼ 出力結果
データベース コンテキストが 'lightbox' に変更されました。
name                                                                                                                             fileid filename                                                                                                                                                                                                                                                             filegroup                                                                                                                        size               maxsize            growth             usage    
-------------------------------------------------------------------------------------------------------------------------------- ------ -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ------------------ ------------------ ------------------ ---------
lightbox                                                                                                                              1 c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\lightbox.mdf                                                                                                                                                                                                PRIMARY                                                                                                                          2240 KB            Unlimited          1024 KB            data only
lightbox_log                                                                                                                          2 c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\lightbox_log.LDF                                                                                                                                                                                            NULL                                                                                                                             1088 KB            2147483648 KB      10%                log only 

関連する記事

ファイルから直接データベースを別のPCでリストアする / SQLExpress

SQLExpress 2008 バッチDB バックアップ



タグ:DB SQLExpress
posted by lightbox at 2012-12-18 12:22 | SQLExpress | このブログの読者になる | 更新情報をチェックする

2012年12月11日


SQLExpress 2008 バッチ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 は タイムアウト設定

関連する情報

バックアップしたデーターベースを元に複製データベースを作成する :[SQL] リストア

関連する記事

ピンポイントですが、SQLServer 2008 は SQLServer 7.0 のデータをロード出来ないのです



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

2012年06月06日


ピンポイントですが、SQLServer 2008 は SQLServer 7.0 のデータをロード出来ないのです

SQLServer 2005 までは SQL の RESTORE でふつうにロードできます。但し、SQLServer 2005 では、VIEW に問題があって、SQLServer 2008 にアップグレードすると動くようになるのですが・・・・。今回データを再ロードしようとしたら 『互換性がありません』という主旨のエラーが出ました。世間にさすがに例が無くってあわてましたが、自分のやった手順を思い出して、たぶんそれに違い無いという事に。

検証は明日です。
検証しました。

結局、自分のPC に SQLExpress 2005 がインストールされているので、まずそこへ RESTORE しました。その後、いろいろメンテナンス( 必要無いデータを削除 )してから BACKUP しました。そのファイルを SQLExpress 2008 に RESTORE して事無きを得ました。

※ データが大き過ぎてタイムアウト設定が必要でした
( 以下は思い切って大きくしています )

--------------------------------------------------
sqlcmd -E -S .\SQLEXPRESS -t 3600

use master
go

DUMP TRANSACTION データベース名 WITH NO_LOG
go

DBCC SHRINKDATABASE (データベース名)
go

use データベース名
go
EXEC sp_updatestats
go
--------------------------------------------------

RESTORE
FILELISTONLY 
FROM DISK = 'ファイルのパス'

で、BACKUP されたフォルダのパスをチェックして、PC のそのフォルダを作っておけば以下のコマンドで簡単にリストアできます( DB は存在しない状態で )

RESTORE DATABASE データベース名
FROM DISK = 'ファイルのパス'

※ いろいろ変更してロードする場合はこちら




タグ:トラブル
posted by lightbox at 2012-06-06 23:55 | SQLExpress | このブログの読者になる | 更新情報をチェックする

2012年04月10日


SQLExpress2005 で副問い合わせを含む VIEW が異常終了しました

結論として、SQLExpress 2008 で正しく動作するようになりました。

元々 SQLServer7 からの移行なので、経験のある SQLExpress2005 を Windows Server 2008 に
インストールしたのですが、VIEW が正しく実行されずに原因も不明でした。対症療法としては、
select 構文では正しく実行されるので from に副問い合わせとして元の select 構文を指定
する事でなんとかなるとは思いますが、SQLExpress 2008 にアップグレードする事が根本的な
解決方法のようです。

アップグレードは特に何の問題も無く実施され、別名もそのまま移行されて使用可能な状態です。

構成マネージャ

SQLExpress2005 では、C:\Windows\System32\mmc.exe /32 "C:\Windows\system32\SQLServerManager.msc"
となっていましたが、C:\Windows\System32\mmc.exe /32 "C:\Windows\system32\SQLServerManager10.msc"
とすると新しい構成マネージャが起動可能です



関連する記事

SQLExpress2005設定


タグ:トラブル
posted by lightbox at 2012-04-10 16:43 | SQLExpress | このブログの読者になる | 更新情報をチェックする

2011年03月01日


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 2005 Express Edition SP4 Microsoft SQL Server 2005 用 Feature Pack 関連する記事 SQLExpress 2005 の接続設定
LoginMode のあるレジストリを開くスクリプト

ログインモード設定 ( Windows 認証 : 1、混合モード認証 : 2 )
別名が定義された場所を開くスクリプト

バージョン文字列 のあるレジストリを開くスクリプト

以下 2005
リリースSqlservr.exe
RTM2005.90.1399
SQL Server 2005 Service Pack 12005.90.2047
SQL Server 2005 Service Pack 22005.90.3042
SQL Server 2005 Service Pack 32005.90.4035
SQL Server 2005 Service Pack 42005.90.5000
関連する Microsoft ドキュメント

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


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

2009年10月29日


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

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

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

VB.net の参照としては以下の3つを追加します
1) Microsoft.SqlServer.ConnectionInfo
2) Microsoft.SqlServer.Smo
3) Microsoft.SqlServer.SqlEnum

( ファイルの場所の例 : C:\Program Files\Microsoft SQL Server\90\SDK\Assemblies )
' サーバー
Dim srv As Server
' インスタンス
srv = New Server()

' サーバーインスタンスの情報
srv.ConnectionContext.AutoDisconnectMode = AutoDisconnectMode.NoAutoDisconnect
srv.ConnectionContext.LoginSecure = False
srv.ConnectionContext.Login = "sa"
srv.ConnectionContext.Password = "trustno1"
' 接続
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 クラス ライブラリ


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

2009年10月27日


VBS : SQLServerよりExcel へエクスポート(Excel製品は必要ありません)

Microsoft.Jet.OLEDB.4.0の利用
要は Microsoft 中心に考えると、たいていの DB に動的に接続して、テーブルを参照できるという事です。( ODBC ドライバ依存 )

Oracle は、常に、スキーマで修飾が必要ですが、他の DB ならテーブル名のみで参照できると思います。

結合もできるところが凄いというか、Microsoft 中心というか結構使い道が広い方法です。

ADOX は、他にも機能もありますが、ADO でたいてい事足りるので、MDB を作成したい場合にのみ利用します。

ADO は、MDB も Excel も 製品をインストールしていなくても DB として使うのならば何も問題無くアクセス可能です。つまり、Access や Excel をインストールしていなくても、ADO 経由でデータを見る事ができます
Set Cn = CreateObject( "ADODB.Connection" )
Set Fso = Wscript.CreateObject( "Scripting.FileSystemObject" )

' **************************************
' 実行中ディレクトリの取得
' **************************************
strPath = Wscript.ScriptFullName 
Set objFile = Fso.GetFile( strPath )
strCur = Fso.GetParentFolderName( objFile )

on error resume next
Fso.DeleteFile(strCur&"\DUMMY.mdb")
on error goto 0

Set ac = CreateObject("ADOX.Catalog")

' **************************************
' 接続用 MDB 作成
' **************************************
on error resume next
ac.Create "Provider=Microsoft.Jet.OLEDB.4.0;" & _
	"Data Source="&strCur&"\DUMMY.mdb"&";"
if Err.Number <> 0 then
	Wscript.echo Err.Description
	Wscript.quit
end if
on error goto 0

' **************************************
' 作成したMDB の開放
' **************************************
on error resume next
Set ac = Nothing
on error goto 0

' **************************************
' 接続文字列( ADOX と同じ )
' **************************************
ConnectionString = _
	"Provider=Microsoft.Jet.OLEDB.4.0;" & _
	"Data Source=" & strCur&"\DUMMY.mdb" & ";"

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

' **************************************
' エクスポート用 SQL / {SQL Server} は
' {SQL Native Client}に変更可
' **************************************
Query = _
"select * " & _
" into [Excel 8.0;DATABASE="&strCur&"\商品マスタ.xls"&"].[商品マスタ] " & _
" from [ODBC;Driver={SQL Server};" & _
"SERVER=.\SQLEXPRESS;Database=lightbox;UID=sa;PWD=password].[商品マスタ]"

' **************************************
' 実行
' **************************************
Cn.Execute Query

' **************************************
' MDB 開放
' **************************************
Cn.Close
Set Cn = Nothing

' **************************************
' 接続用 MDB 削除
' **************************************
on error resume next
Fso.DeleteFile(strCur&"\DUMMY.mdb")
on error goto 0

Wscript.echo "処理が終了しました   " 




posted by lightbox at 2009-10-27 16:59 | SQLExpress | このブログの読者になる | 更新情報をチェックする

SQLServer のドキュメント

ドキュメントダウンロード
オンラインでも見れますが Microsoft のサイトはサクサクは無理です。
ローカルの PC にインストールしたほうが良いです。

SQL Server 2005 Books Online


オンライン参照
それでも、自分が使う PC 全てにインストールするわけにもいかない
ので、オンラインで見れるようにする事も重要です。

1) Transact-SQL リファレンス
2) コマンド プロンプト ユーティリティ
3) システム ストアド プロシージャ (Transact-SQL)
  ● sp_helpdb (Transact-SQL)


関連する記事
SQL-DMO CSV入出力
bcp.exe を使用した CSV 入出力
xp_cmdshell でストアードプロシージャを知る
SQLの窓1.5(改)でTransact-SQLを学ぶ







posted by lightbox at 2009-10-27 08:43 | SQLExpress | このブログの読者になる | 更新情報をチェックする

2009年10月26日


VBS : SQLExpress(SQLServer) テーブルの正しい CREATE 文を取得する

概要

有償版の SQLServer ならばこんな苦労はしなくてもいいはずですが、SQL-DMO はこれ以外にも有用な処理がたくさんあるので、使えるにこした事はありません。

バックアップやリストアは、SQL から実行したほうが便利なので BCP と共に最も良く使う SQL-DMO の処理だと思います
if exists(select * from sysobjects where name = '商品マスタ' and type = 'U')
	drop table 商品マスタ
;
CREATE TABLE [商品マスタ] (
	[商品コード] [varchar] (4) COLLATE Japanese_CI_AS NOT NULL ,
	[商品名] [nvarchar] (50) COLLATE Japanese_CI_AS NULL ,
	[在庫評価単価] [int] NULL ,
	[販売単価] [int] NULL ,
	[商品分類] [varchar] (3) COLLATE Japanese_CI_AS NULL ,
	[商品区分] [varchar] (1) COLLATE Japanese_CI_AS NULL ,
	[作成日] [datetime] NULL ,
	[更新日] [datetime] NULL ,
	 PRIMARY KEY  CLUSTERED 
	(
		[商品コード]
	)  ON [PRIMARY] 
) ON [PRIMARY]
;


これは、出力した結果ですが if 文と削除のところは自前で作成しています。SQLExpress( SQLServer )のSQL実行は、セミコロンで文を区切って一度に実行できるので、上記 SQL は一回の呼び出しで実行可能です

関連する記事

PHP : SQLExpress(SQLServer) : SQL-DMO(COM) による CSV 出力

Set Fso = CreateObject( "Scripting.FileSystemObject" )
' ****************************************
' 定義テキスト取得
' ****************************************

Server = "NIGHT_TCP"		' 別名
Db = "lightbox"
User = "sa"
Pass = "passwordpassword"

ListTable = "社員マスタ,商品マスタ,得意先マスタ"
List = Split( ListTable, "," )

Dim objServer,objDatabase,objTable

' ****************************************
' オブジェクト作成
' ****************************************
on error resume next
Set objServer = CreateObject("SQLDMO.SQLServer")
if Err.Number <> 0 then
	Wscript.Echo "SQL-DMO はインストールされていません"
	Wscript.Quit
end if
on error goto 0

' ****************************************
' 接続
' ****************************************
on error resume next
Call objServer.Connect( Server, User, Pass )
if Err.Number <> 0 then
	Wscript.Echo "接続に失敗しました [" & Err.Description & "]"
	Wscript.Quit
end if
on error goto 0

' ****************************************
' データーベースオブジェクト取得
' ****************************************
on error resume next
Set objDatabase = objServer.Databases(Db)
if Err.Number <> 0 then
	Wscript.Echo Err.Description
	Wscript.Quit
end if
on error goto 0


' ****************************************
' テキストファイル
' ****************************************
Set OutObj = Fso.OpenTextFile( "create_script.sql", 2, True )


' ****************************************
' テーブルオブジェクト
' ****************************************
on error resume next
For i = 0 to Ubound( List )

	Set objTable = objDatabase.Tables(List(i))
	if Err.Number <> 0 then
		Wscript.Echo Err.Description
		Wscript.Quit
	end if

	strDrop = "if exists(select * from sysobjects where name = '"
	strDrop = strDrop & objTable.Name & "'" & " and type = 'U')" & vbCrLf
	strDrop = strDrop & "	drop table " & objTable.Name & vbCrLf
	strDrop = strDrop & ";" & vbCrLf

	OutObj.Write strDrop 
	OutObj.Write Replace( objTable.Script() & "", "GO", ";" ) & vbCrLf

Next
on error goto 0

OutObj.Close()

' ****************************************
' 切断
' ****************************************
Call objServer.DisConnect()



posted by lightbox at 2009-10-26 00:38 | SQLExpress | このブログの読者になる | 更新情報をチェックする

2009年10月25日


PHP : SQLExpress(SQLserver) : COM による接続処理

概要

要するに ADO です。ADO はかなり信頼性のある安定した Windows におけるDB 処理の為の
選択肢です。PHP における COM は完璧ではありませんが、PHP 側のインターフェイスを使
うより、慣れている者にとっては問題点が容易に予想でき、既存のコードもあるていど
そのまま利用できるという長所があります。

一部日本語列名を使った参照に問題が出るので、連想配列に入れなおす必要がありますが、
この部分で他のキャラクタセットの対応が出来ますし、オリジナルオプションを付加する
チャンスでもあります
posted by lightbox at 2009-10-25 22:33 | SQLExpress | このブログの読者になる | 更新情報をチェックする
Seesaa の各ページの表示について
Seesaa の 記事がたまに全く表示されない場合があります。その場合は、設定> 詳細設定> ブログ設定 で 最新の情報に更新の『実行ボタン』で記事やアーカイブが最新にビルドされます。

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

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

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

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


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

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

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

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

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


Windows
container 終わり

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

Android SDK ポケットリファレンス
改訂版 Webデザイナーのための jQuery入門
今すぐ使えるかんたん ホームページ HTML&CSS入門
CSS ドロップシャドウの参考デモ
PHP正規表現チェッカー
Google Hosted Libraries
cdnjs
BUTTONS (CSS でボタン)
イラストAC
ぱくたそ
写真素材 足成
フリーフォント一覧
utf8 文字ツール
右サイド 終わり
base 終わり