Just another WordPress site
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
知らなくても良さそうだが書いておきます。
ストアドでエラー行とSSMSのストアド編集画面の行番号にズレがある。
その前に、表示はv17.5でツール、オプション、ダイアログ中の、テキスト エディタ、全ての言語、全般の中の行番号をチェック
話戻って、SSMSでクエリ発行 sp_helptext ストアドプロシージャ名
の行番号を見る
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は暗黙的変換なら可能になっているけど明示的変換はできない？
まあ、いくら値の評価が合格でも不可能な型変換がある事を学びました。
バックエンドSQLserver リンクテーブルでaccessのクエリでフォーム作成している。
クエリのレコードロックを全部にすると他者はレコードを見ることもできない。
編集レコードロックはロックしないと同じ挙動。
朝、突然に上記症状出現。
クライアントからつながらない。ローカルはつながる。
pingは通るし、ポートは開いている。nslookupはちゃんとしてるし、sqlbrowserも動いている。
クライアントのログインを作り直してもだめ。
ローカルはつながるので何とか業務をこなしつつ、SQLbrowserサービスを再起動させてみたら、
突然全部つながりました。
何だったのだろう。
テーブルに入れると言う手もあります
これが一番簡単
SQLserverとつながっているなら一時テーブルに入れ込む
|
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 32 33 34 35 36 37 38 39 40 |
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 "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 |
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もかなり難しいし環境が違えば同様にはいかないだろうし後日再検討しよう
VB.NETでdatagridviewをGUIで作成するとき
クエリに
select iif(bit型カラム=0,’男’,’女’) as 性別,…from sqlserverTable
と書くとエラー
select 性別=case when bit型カラム=0 then ‘男’ else ‘女’ end,…
と書くと大丈夫
VBAのレコードセットも癖が強いがVB.NETも一筋縄では行かない気がする
バックエンド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エディターが残念に思えるほど
が遅い
sqlserverテーブルのシンプルな選択クエリ。
フォームに表示が遅いのではなく デザイナーで作成途中の確認の段階で遅い。
accessから移行中のクエリだがaccessのリンクテーブルのクエリの方がはるかに速い
ADODBのデータソースレス接続の様にコードにクエリを書いてdataadapter作成したほうが速いのか？
ODBC接続のaccessクエリに ADO.NETが速さで劣る？
どこか間違えているのかも、明日落ち着いて見直そう