SQL Server メモ – テックメモ

接続文字列(プロバイダ)

・OLE DB

(非推奨)SQL Server Native Client(SQLNCLI)
(非推奨)Microsoft OLE DB Provider for SQL Server(SQLOLEDB)
Microsoft OLE DB Driver for SQL Server(MSOLEDBSQL)※

ODBCにのみ対応しているDB製品に対しては、
Microsoft OLE DB Provider for ODBC(MSDASQL)

OLE DB接続文字列サンプル
“Provider=MSOLEDBSQL;Data Source=192.168.4.201,1433\MSSQLSERVER;Initial Catalog=xxx;User ID=sa;Password=xxx;”

・ODBC

(非推奨)SQL Server
(非推奨)SQL Server Native Client
Microsoft ODBC Driver for SQL Server

ODBC接続文字列サンプル
“Driver={ODBC Driver 18 for SQL Server};Server=192.168.4.201,1433\MSSQLSERVER;Database=xxx;Uid=sa;Pwd=xxx;Encrypt=Optional”

ドライバー(DSN確認)
cmd＞odbcad32

※MSOLEDBSQL 3706エラー

・Microsoft OLE DB Driver for SQL Server 19.3(x64)
https://go.microsoft.com/fwlink/?linkid=2220017
がインストールできず、以下が必要と表示された。

Microsoft Visual C++ Redistributable for Visual Studio 2022
https://aka.ms/vs/17/release/vc_redist.x64.exe
を追加してもインストールできなかった。

・Microsoft OLE DB Driver for SQL Server 19.2(x64)
https://go.microsoft.com/fwlink/?linkid=2212594
はインストールできたが、
(3706)プロバイダーが見つかりません
エラーとなってしまう。

接続文字列を
“Provider=MSOLEDBSQL.19; … Use Encryption for Data=False”
としたがダメだった。

Microsoft ODBC Driver 13.1 for SQL Server
https://www.microsoft.com/ja-JP/download/confirmation.aspx?id=53339
を入れて見たがダメだった。

コマンドラインの場合

ダウンロードする種類

64ビット環境
JPN\x64\sqlncli.msi
JPN\x64\SqlCmdLnUtils.msi
の２つを保存

32ビット環境
JPN\x86\sqlncli.msi
JPN\x86\SqlCmdLnUtils.msi
の２つを保存

C:\Program Files\Microsoft SQL Server\110\Tools\Binn
にパスを通す

コマンドでの利用方法

・ヘルプ
sqlcmd /?

・接続
sqlcmd -S サーバ名 -d DB名 -U user -P pass
sqlcmd -E -S HOST名\sqlexpress
または、
sqlcmd -E -S localhost\sqlexpress

※接続と同時にクエリを渡さないと、対話状態になる。

・オプション

-E
Windows認証
-Q
インラインでSQL
-i
SQLのファイルを渡せる。

・データベース一覧の表示
1> select name from sys.databases
2> go

・データベースの指定
1> use my_database
2> go

・テーブル一覧の表示
1> select name from sys.tables
2> go

バッチファイルでバックアップ

バッチファイルには以下を記述。
sqlcmd -E -S host名\sqlexpress -i “sql.txt”

sql.txt

declare @weekday varchar(1)
set @weekday = datepart(weekday,getdate())

declare @path varchar(255)
set @path = 'D:\xxx\backup_' + @weekday + '.bak'

declare @date varchar(8)
set @date = format(getdate(),'yyyyMMdd')

backup database my_database
to disk = @path
with init , name = @date

declare @weekday varchar(1)

set @weekday = datepart(weekday,getdate())

declare @path varchar(255)

set @path = 'D:\xxx\backup_' + @weekday + '.bak'

declare @date varchar(8)

set @date = format(getdate(),'yyyyMMdd')

backup database my_database

to disk = @path

with init , name = @date

バックアップ情報

restore headeronly
from disk = 'C:\xxx\xxx.bak'
restore filelistonly
from disk = 'C:\xxx\xxx.bak'

restore headeronly

from disk = 'C:\xxx\xxx.bak'

restore filelistonly

from disk = 'C:\xxx\xxx.bak'

バックアップ復元

restore database my_database
from disk = 'C:\xxx\xxx.bak'

1 2	restore database my_database from disk = 'C:\xxx\xxx.bak'

リモート接続の設定

最初の接続時、情報がなければ、
localhost\SQLEXPRESS
Windows認証
で接続する。

・SQL Server Management Studio(SSMS)

セキュィティ＞ログイン＞sa＞プロパティ＞全般
パスワードを設定

セキュィティ＞ログイン＞sa＞プロパティ＞状態
ログインを有効化

インスタンス右クリック＞プロパティ＞セキュリティ
SQL Server認証とWindows 認証モード

インスタンス右クリック＞プロパティ＞接続
リモート接続を許可

・自動起動

services.msc
(もしなっていない場合)SQL Server Browserを自動へ

・SQL Server構成マネージャ
C:\Windows\System32\SQLServerManager15.msc
(スタートメニューの場合、SQL Server xxx 構成マネージャー)

SQLEXPRESSのプロトコル
共有メモリ＞有効
名前付きパイプ＞無効
TCP/IP＞有効

TCP/IPプロパティ＞IPアドレス＞IPALL
TCPポートを1433(任意)
TCP動的ポートを空白

・ファイアーウォールの設定(ローカルの場合)

firewall.cpl
操作＞新しい規則＞ポート指定＞TCP＞特定のローカルポート(1433)＞接続を許可する＞プライベートのみ選択

ここまでやるとSSMSから
sa
指定したパスワード
でログインできる。

接続できない場合の対応

「SQL Server が存在しないか、アクセスが拒否されました」というエラーで接続できませんでした。

C:\Windows\System32\SQLServerManager15.msc
(スタートメニューの場合、SQL Server xxx 構成マネージャー)
TCP/IPが有効になっているか

ポート番号はカンマ区切り。
インスタンス名はservices.mscから調べることができる。

サービスについて

SQL Server Browser
ネットワーク上のSQL Serverを自動検出。リモートで必要な場合も。

SQL Server CEIP サービス
カスタマーエクスペリエンス向上プログラム。

SQL Server VSS Writer
ボリュームシャドウコピーサービス。

SQL Server エージェント
スケジュールされたジョブの実行。

弥生会計17から利用

上記のSQL Server Management Studioから設定し、
ユーザー名：sa
パスワード：指定したパスワード
からログインできる状態。

・設定

インストール後、弥生会計ではデータベースの新規作成を進めていき、サーバーの設定画面で、
保存場所：localhost\SQLEXPRESS
ユーザー名：sa
パスワード：指定したパスワード
とすればOK。

＊＊＊

SQL Serverの設定を変えた場合
接続できない場合。
接続できるけどデータがない場合。
の２通り。(エラーの表示が違う)

ファイル＞開く＞参照先の設定＞編集
から修正できるが、データ自体がなくなっている場合は、
ファイル＞新規作成＞新規にデータを作成する
を実行する。

・バックアップ

バックアップ中に、オペレーティングシステムエラー５のエラーがでる場合。

サービスからSQL Server (SQLEXPRESS)のプロパティを開いて、ログオンをローカルシステムに変更。対話サービスも許可。

・バックアップの復元

ファイル＞バックアップファイルの復元＞対象のKB17ファイルを選択
バックアップファイルの復元というダイアログが出る。
復元先＞参照先＞参照先の設定＞サーバーの追加
を実行し、
保存場所：localhost\SQLEXPRESS
サーバーにログインするユーザーを指定する＞
ユーザー名：sa
パスワード：指定したパスワード
を指定する。

・その他

Windowsの機能から.NET3.5.1のチェックを外すと起動しない。

アンインストール

・手動削除

Win10設定＞アプリ＞アプリと機能(またはappwiz.cpl)
から
Microsoft SQL Server xxx Native Client：OLE DB・ODBCドライバー(非推奨)
Microsoft SQL Server xxx LocalDB：リモート機能を省略した簡略版
Microsoft ODBC Driver xx for SQL Server：ODBCドライバー
Microsoft OLE DB Driver for SQL Server：OLE DBドライバー
Microsoft SQL Server xxx T-SQL 言語サービス
を削除する。以下2つはエラーとなった。
Microsoft SQL Server xxx Setup (English)
Microsoft SQL Server xxx (64 ビット)

Microsoft SQL Server xxx Native Client
Microsoft SQL Server xxx LocalDB
SQL Server xxx 用 Microsoft System CLR Types
などは、SQL Server Management Studioをインストールすると入る。

＊＊＊

・msiexec削除

HKLM\SOFTWARE\Microsoft\Windows\CurrentVersion\Uninstall
HKLM\SOFTWARE\Wow6432Node\Microsoft\Windows\CurrentVersion\Uninstall
DisplayName
“SQL Server xxx”が含まれるキー(GUID)をメモし以下のようにコマンド実行。

msiexec /x {GUID}

msiexec /x {0FB552DD-543E-48E7-A6F4-2F8D82723C6A}
msiexec /x {17DCED0E-5B27-453A-B2B4-E487B869B28A}
msiexec /x {2129312E-5204-4F3A-9039-B6D34DBB00FB}
msiexec /x {28ED6838-D8E5-454C-A813-12C5EB447CAB}
msiexec /x {6213D6CB-D258-47A3-B1A0-EE1E5C080DCF}
msiexec /x {814D5077-C93F-42E2-B875-717007C186B9}
msiexec /x {8DDAEBCA-4267-4E16-9FE0-D87F21D36891}
msiexec /x {9198AD57-6396-4DF8-8D0C-20EA764F7986}
msiexec /x {D057EE39-361E-4773-8DB9-FE9AD65A1C78}
msiexec /x {D459615B-83B0-408F-8F39-6CC07C277BA6}
msiexec /x {DE5B7937-D5B5-4157-BC30-BB87F021CFF0}
msiexec /x {FD730873-33D1-4D1F-9AE0-E259586F8827}

msiexec /x {0FB552DD-543E-48E7-A6F4-2F8D82723C6A}

msiexec /x {17DCED0E-5B27-453A-B2B4-E487B869B28A}

msiexec /x {2129312E-5204-4F3A-9039-B6D34DBB00FB}

msiexec /x {28ED6838-D8E5-454C-A813-12C5EB447CAB}

msiexec /x {6213D6CB-D258-47A3-B1A0-EE1E5C080DCF}

msiexec /x {814D5077-C93F-42E2-B875-717007C186B9}

msiexec /x {8DDAEBCA-4267-4E16-9FE0-D87F21D36891}

msiexec /x {9198AD57-6396-4DF8-8D0C-20EA764F7986}

msiexec /x {D057EE39-361E-4773-8DB9-FE9AD65A1C78}

msiexec /x {D459615B-83B0-408F-8F39-6CC07C277BA6}

msiexec /x {DE5B7937-D5B5-4157-BC30-BB87F021CFF0}

msiexec /x {FD730873-33D1-4D1F-9AE0-E259586F8827}

※PowerShellで一覧を取得する場合

Get-ChildItem "HKLM:SOFTWARE\Microsoft\Windows\CurrentVersion\Uninstall" | ForEach-Object {
  if ($_.GetValue("DisplayName") -match "SQL Server"){
    $_.GetValue("DisplayName") + "`n" + $_.Name + "`n"
  }
}

Get-ChildItem "HKLM:SOFTWARE\Wow6432Node\Microsoft\Windows\CurrentVersion\Uninstall" | ForEach-Object {
  if ($_.GetValue("DisplayName") -match "SQL Server"){
    $_.GetValue("DisplayName") + "`n" + $_.Name + "`n"
  }
}

Get-ChildItem "HKLM:SOFTWARE\Microsoft\Windows\CurrentVersion\Uninstall" | ForEach-Object {

if ($_.GetValue("DisplayName") -match "SQL Server"){

$_.GetValue("DisplayName") + "`n" + $_.Name + "`n"

}

Get-ChildItem "HKLM:SOFTWARE\Wow6432Node\Microsoft\Windows\CurrentVersion\Uninstall" | ForEach-Object {

if ($_.GetValue("DisplayName") -match "SQL Server"){

$_.GetValue("DisplayName") + "`n" + $_.Name + "`n"

}

アンインストール中にエラーがあったら指示通りの順番で処理する。

・レジストリ削除

HKLM\SOFTWARE\Microsoft
HKLM\SOFTWARE\Wow6432Node\Microsoft
“Microsoft SQL”、”MSSQL”
を含むキーを削除

HKLM\SOFTWARE\Classes\Installer\Products
ProductName
“SQL Server”
を含むキーを削除

※PowerShellで一覧を取得する場合

Get-ChildItem "HKLM:SOFTWARE\Classes\Installer\Products" | ForEach-Object {
  if ($_.GetValue("ProductName") -match "SQL Server"){
    $_.GetValue("ProductName") + "`n" + $_.Name + "`n"
  }
}

Get-ChildItem "HKLM:SOFTWARE\Classes\Installer\Products" | ForEach-Object {

if ($_.GetValue("ProductName") -match "SQL Server"){

$_.GetValue("ProductName") + "`n" + $_.Name + "`n"

}

＊＊＊

以下は、URLのコマンドそのまま
https://docs.microsoft.com/ja-jp/archive/blogs/jpsql/sql-server-4

・GAC削除

if exist "%windir%\assembly\GAC\*SQLServer*" del /s /q /f "%windir%\assembly\GAC\*SQLServer*"
if exist "%windir%\assembly\GAC_32\*SQLServer*" del /s /q /f "%windir%\assembly\GAC_32\*SQLServer*"
if exist "%windir%\assembly\GAC_64\*SQLServer*" del /s /q /f "%windir%\assembly\GAC_64\*SQLServer*"
if exist "%windir%\assembly\GAC_MSIL\*SQLServer*" del /s /q /f "%windir%\assembly\GAC_MSIL\*SQLServer*"

if exist "%windir%\assembly\GAC\*SQLServer*" del /s /q /f "%windir%\assembly\GAC\*SQLServer*"

if exist "%windir%\assembly\GAC_32\*SQLServer*" del /s /q /f "%windir%\assembly\GAC_32\*SQLServer*"

if exist "%windir%\assembly\GAC_64\*SQLServer*" del /s /q /f "%windir%\assembly\GAC_64\*SQLServer*"

if exist "%windir%\assembly\GAC_MSIL\*SQLServer*" del /s /q /f "%windir%\assembly\GAC_MSIL\*SQLServer*"

・フォルダ、ファイル削除

if exist "%USERPROFILE%\Local Settings\Application Data\Microsoft\Microsoft SQL Server" rmdir /S /Q "%USERPROFILE%\Local Settings\Application Data\Microsoft\Microsoft SQL Server"
if exist "%USERPROFILE%\Application Data\Microsoft\Microsoft SQL Server" rmdir /S /Q "%USERPROFILE%\Application Data\Microsoft\Microsoft SQL Server"
if exist "%USERPROFILE%\All Users\Application Data\Microsoft\Microsoft SQL Server" rmdir /S /Q "%USERPROFILE%\All Users\Application Data\Microsoft\Microsoft SQL Server"
if exist "%ProgramFiles%\Microsoft SQL Server" rmdir /S /Q "%ProgramFiles%\Microsoft SQL Server"
if exist "%ProgramFiles(x86)%\Microsoft SQL Server" rmdir /S /Q "%ProgramFiles(x86)%\Microsoft SQL Server"

if exist "%USERPROFILE%\Local Settings\Application Data\Microsoft\Microsoft SQL Server" rmdir /S /Q "%USERPROFILE%\Local Settings\Application Data\Microsoft\Microsoft SQL Server"

if exist "%USERPROFILE%\Application Data\Microsoft\Microsoft SQL Server" rmdir /S /Q "%USERPROFILE%\Application Data\Microsoft\Microsoft SQL Server"

if exist "%USERPROFILE%\All Users\Application Data\Microsoft\Microsoft SQL Server" rmdir /S /Q "%USERPROFILE%\All Users\Application Data\Microsoft\Microsoft SQL Server"

if exist "%ProgramFiles%\Microsoft SQL Server" rmdir /S /Q "%ProgramFiles%\Microsoft SQL Server"

if exist "%ProgramFiles(x86)%\Microsoft SQL Server" rmdir /S /Q "%ProgramFiles(x86)%\Microsoft SQL Server"

※インストールフォルダが別にある場合手動で削除。

＊＊＊

最終的にインストールのエントリーが残ってしまった。実体を削除しているので、
HKLM\SOFTWARE\Microsoft\Windows\CurrentVersion\Uninstall
HKLM\SOFTWARE\Wow6432Node\Microsoft\Windows\CurrentVersion\Uninstall
でGUIDのキーを手動で削除すればエントリーは消える。