sqlserverで
select * into tableA from (select * from tableB order by columnC asc)
はサブクエリでorder byは無効ですとエラーになりますがaccessでのクエリなら問題ない
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.?”);’
難しかった
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
知らなくても良さそうだが書いておきます。
Sqlserverエラー行番号
ストアドでエラー行と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は暗黙的変換なら可能になっているけど明示的変換はできない?
まあ、いくら値の評価が合格でも不可能な型変換がある事を学びました。
ACCESSのレコードロック
バックエンドSQLserver リンクテーブルでaccessのクエリでフォーム作成している。
クエリのレコードロックを全部にすると他者はレコードを見ることもできない。
編集レコードロックはロックしないと同じ挙動。
SQL serverが突然つながらなくなった
朝、突然に上記症状出現。
クライアントからつながらない。ローカルはつながる。
pingは通るし、ポートは開いている。nslookupはちゃんとしてるし、sqlbrowserも動いている。
クライアントのログインを作り直してもだめ。
ローカルはつながるので何とか業務をこなしつつ、SQLbrowserサービスを再起動させてみたら、
突然全部つながりました。
何だったのだろう。
VBAで配列をソート テーブルを使う
テーブルに入れると言う手もあります
これが一番簡単
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 41 |
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 |
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もかなり難しいし環境が違えば同様にはいかないだろうし後日再検討しよう
datagridview iifエラー
VB.NETでdatagridviewをGUIで作成するとき
クエリに
select iif(bit型カラム=0,’男’,’女’) as 性別,…from sqlserverTable
と書くとエラー
select 性別=case when bit型カラム=0 then ‘男’ else ‘女’ end,…
と書くと大丈夫
VBAのレコードセットも癖が強いがVB.NETも一筋縄では行かない気がする