SQLserver – VBA SQL

2020年6月24日2020年6月29日

WSUS WIDからSQL server expressへ移行　覚え書き

ドメコンのままでは難しくてできなかった
降格した後AD削除
SQL server express　インスト
サーバ名　\\.\pipe\microsoft##wid\tsql\query　のWIDからSUSDBデタッチ
expressの新しいインスタンスにアタッチ
新しいログイン　NT AUTHORITY\NETWORK SERVICE　サーバロール　publicとsysadmin　ユーザーマッピング　SUSDBにpublicとwebservice

以下はregeditで
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Update Services\Server\Setup
SqlServerName　は　インスタンス名
SqlDatabaseName　は　SUSDB
\HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Update Services\Server\Setup\Installed Role Services
UpdateServices-WID　を　UpdateServices-Database　に変更

なぜ移行したというとSSMSでデータ圧縮とかインデックス再構築とかやりたかったから。
ここまで来てexpressではできないことが判明

でもクエリではできるらしい。
クエリ例
dbcc shrinkdatabase(susdb,10)
exec sp_MSforeachtable @command1=’if object_id(”susdb.?”) is not null dbcc DBREindex(”susdb.?”);’

難しかった

2020年4月21日2020年5月23日

format関数（3回目ぐらい）

format関数はvba、SqlServer、VB.netにありますが少し入力値に違いありです

vbaは文字でも数字でも日付型でも良いみたい
format(“41″,”000”) は041
だけど
SqlServer、VB.netの入力値は数字または日付型のみ許されるようです。
sqlserverでは
format(cast(’41’ as int),’000′)
としないと思った通りに返ってこない

知らなかったがVBAで
strconv(“abc”,vbUpperCase)　は　ABC　だけど
format(“abc”,”>”) 　も　ABC
知らなくても良さそうだが書いておきます。

2020年3月19日

Sqlserverエラー行番号

ストアドでエラー行とSSMSのストアド編集画面の行番号にズレがある。
その前に、表示はv17.5でツール、オプション、ダイアログ中の、テキスト　エディタ、全ての言語、全般の中の行番号をチェック

話戻って、SSMSでクエリ発行　sp_helptext ストアドプロシージャ名
の行番号を見る

2020年3月10日2020年3月19日

値が合格でも明示的変換できないこともある

SqlServerのトリガーをいじって大失敗

select columnA= case when columnA is null and columnB(datetime)>cast(columnC(decimal) as datetime) then

として大失敗。columnCの値は20200101などdate型として通用するんですがなぜかdecimal型なんです。
一度文字型にしてから日付型へ変換が必要でした。

https://docs.microsoft.com/ja-jp/sql/t-sql/data-types/data-type-conversion-database-engine?view=sql-server-ver15　からです
decimalからdatetimeは暗黙的変換なら可能になっているけど明示的変換はできない？
まあ、いくら値の評価が合格でも不可能な型変換がある事を学びました。

2020年3月4日

ACCESSのレコードロック

バックエンドSQLserver　リンクテーブルでaccessのクエリでフォーム作成している。
クエリのレコードロックを全部にすると他者はレコードを見ることもできない。
編集レコードロックはロックしないと同じ挙動。

2019年11月21日

SQL serverが突然つながらなくなった

朝、突然に上記症状出現。
クライアントからつながらない。ローカルはつながる。
pingは通るし、ポートは開いている。nslookupはちゃんとしてるし、sqlbrowserも動いている。
クライアントのログインを作り直してもだめ。
ローカルはつながるので何とか業務をこなしつつ、SQLbrowserサービスを再起動させてみたら、
突然全部つながりました。
何だったのだろう。

2019年7月9日2020年6月24日

VBAで配列をソート　テーブルを使う

テーブルに入れると言う手もあります
これが一番簡単
SQLserverとつながっているなら一時テーブルに入れ込む

Public Function TableSort(Ary As Variant) As Variant
'Dim Ary
'Ary = Array("5", "4", "1", "2", "900", "888")

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset
cn.ConnectionString = "接続文字列"
cn.Open

Dim I As Long
I = 0
cn.Execute "drop table if exists #AryT"
cn.Execute "select " & CLng(Ary(I)) & "as C into #AryT"

For I = 1 To UBound(Ary)
    cn.Execute "insert into #AryT(C) values(" & CLng(Ary(I)) & ")"
Next I

rs.Open "select C from #AryT order by C asc", cn, adOpenStatic, adLockOptimistic

rs.MoveFirst

I = 0
Do Until rs.EOF
    Ary(I) = rs!C
    rs.MoveNext
    I = I + 1
Loop

'For I = 0 To UBound(Ary)
'    Debug.Print Ary(I)
'Next

TableSort = Ary

 rs.Close
 cn.Close

End Function

Public Function TableSort(Ary As Variant) As Variant

'Dim Ary

'Ary = Array("5", "4", "1", "2", "900", "888")

Dim cn As ADODB.Connection

Dim rs As ADODB.Recordset

Set cn = New ADODB.Connection

Set rs = New ADODB.Recordset

cn.ConnectionString = "接続文字列"

cn.Open

Dim I As Long

I = 0

cn.Execute "drop table if exists #AryT"

cn.Execute "select " & CLng(Ary(I)) & "as C into #AryT"

For I = 1 To UBound(Ary)

cn.Execute "insert into #AryT(C) values(" & CLng(Ary(I)) & ")"

Next I

rs.Open "select C from #AryT order by C asc", cn, adOpenStatic, adLockOptimistic

rs.MoveFirst

I = 0

Do Until rs.EOF

Ary(I) = rs!C

rs.MoveNext

I = I + 1

Loop

'For I = 0 To UBound(Ary)

' Debug.Print Ary(I)

'Next

TableSort = Ary

rs.Close

cn.Close

End Function

2019年6月8日

openrowsetで既存Excelファイルへexport

vb.netでSQLserverテーブルをクエリして既存excelファイルに出力

accessのVBAからなら
docmd.transferspreadsheet acexport,acspreadsheettypeexcel9,”クエリ”,”ファイルパス”
でしたがvb.netでは難しいです

SQLserverにはopenrowsetというクエリでexportできるようなのでvb.netからExecutenonqueryで操作

SQLserverで準備
SQLserverは64bitでaccess32bitがすでにインストされているのでACEの32bitが既存
SQLserverのリンクサーバに64bitのACEのプロバイダ登録が必要なので
https://knowledge.autodesk.com/ja/support/autocad/learn-explore/caas/sfdcarticles/sfdcarticles/JPN/How-to-install-64-bit-Microsoft-Database-Drivers-alongside-32-bit-Microsoft-Office.htmlを参照にACEの64bit版をちょっと強引にインスト

SQLserverのリンクサーバーにACE登録

SQLserverでmasterデータベースで以下クエリ発行
exec sp_configure ‘show advanced options’,1;
reconfigure;
exec sp_configure ‘ad hoc distributed queries’,1;
reconfigure;
exec sp_MSset_oledb_prop N’Microsoft.ACE.OLEDB.12.0′ ,N’AllowInProcess’,1;
reconfigure;
exec sp_MSset_oledb_prop N’Microsoft.ACE.OLEDB.12.0′ ,N’DynamicParameters’,1;
reconfigure;

exportするクエリは
insert into openrowset(‘Microsoft.ACE.OLEDB.12.0’,
‘Excel 8.0;DATABASE=ファイルパス;imex=0;hdr=1;’,
‘SELECT カラム FROM [excelのワークシート名$]’)
select カラム from sqlserverテーブル where 条件カラム=パラメータ

接続文字がとっても厳格、ここまで試行錯誤数日
vbaでは列名含めて新しいワークシートに出力できたが
openrowsetはワークシートと列名は作成しておく

クエリが問題なければvb.netからExecutenonqueryで出力

最初VB.NETのコードでゴリゴリとやっていたけど難しくて断念
openrowsetもかなり難しいし環境が違えば同様にはいかないだろうし後日再検討しよう

2019年5月12日

datagridview iifエラー

VB.NETでdatagridviewをGUIで作成するとき
クエリに
select iif(bit型カラム=0,’男’,’女’) as 性別,…from sqlserverTable
と書くとエラー
select 性別=case when bit型カラム=0 then ‘男’ else ‘女’ end,…
と書くと大丈夫

VBAのレコードセットも癖が強いがVB.NETも一筋縄では行かない気がする

2019年4月19日2019年4月19日

ちょっとベンチ

バックエンドSQLserverフロントエンドAccessVBA　VS　VB.NET
‘VBA
Public Function Queryspeedtest()
Dim T1 As Double
Dim T2 As Double
T1 = Timer
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset

cn.ConnectionString = “OLEDB接続文字”
cn.Open
rs.cursorlocation=adUseClient
rs.Open “select A.code,B.codename from TableA as A inner join TableB as B on(A.code=B.code)”, cn, adOpenStatic, adLockOptimistic

rs.Close
cn.Close

T2 = Timer
Debug.Print T2 – T1
End Function

‘VB.NET
Public Shared Function Queryspeedtest()
Dim SW = New System.Diagnostics.Stopwatch()
SW.Start()

Dim cn = New System.Data.OleDb.OleDbConnection(OLEDB接続文字)
cn.Open()
Dim cmd = New System.Data.OleDb.OleDbCommand(
“select A.code,B.codename from TableA as A inner join TableB as B on(A.code=B.code)”, cn
)
Dim DT = New DataTable(“test”)
Dim adp = New System.Data.OleDb.OleDbDataAdapter(cmd)
adp.Fill(DT)
adp.Dispose()
cn.Close()

SW.Stop()
Debug.WriteLine(SW.Elapsed)
End Function

tableAは20万件、TableBは1万件弱のマスターファイル
TableAのレコードのcodeからTableBのcodenameを得るよくあるクエリ
もちろん主キーは両方あり

単純なselect column from tableは若干VBが勝利
上記のクエリはVBA 0.4s NET 0.8s

倍とはいえ差はコンマ数秒なのでまあいいかと思うがdatagridviewでtextbox表示とかcomboboxに入れたりするとちょっと・・
Accessフォームの方が速い、VB.NETではちょっと工夫が要りそうな

vbaでcursolocationがクライアントの時はcursoltype、locktypeによる差はない
サーバサイドでは最速と覚えていたadopenforwardonlyよりadopendynamicの方がなぜか早かった

ADODBとADO.NETはまったく別物なので比較するなと言われそうだけど・・
でもVSのエディタはすばらしい、VBAエディターが残念に思えるほど

月	火	水	木	金	土	日
« 7月
					1	2
3	4	5	6	7	8	9
10	11	12	13	14	15	16
17	18	19	20	21	22	23
24	25	26	27	28	29	30
31