[2022.LEARN.003]TSQL 使用OpenJson

• 問題：OPENJSON does not work in SQL Server？
• TSQL使用OPENJSON指令時發生錯誤。
• 
  
• 解法：
• 更改層級：
  ALTER DATABASE database_name SET COMPATIBILITY_LEVEL = 130 

[Note][SSMS]編輯器預設全形符號

問題：

• 本篇寫在2019/01/10。
• 發生編寫執行預存時，半形常常會被換成全型。
• 環境：中文版，所以執行預存後他會自動切換到中文全形。(不勝其擾)
• 希望SSMS能夠在後面的更新解決。

參考：

• Windows 10 SSMS > 微軟注音選擇英文 > 執行SQL > 會自動變為中文 情況
• 如何不要讓 Microsoft SQL Server Management Studio 一直跳出中文輸入法

筆記：

剛裝好的SSMS輸入英數字都會變成全形。

在網路上找到的解法，將語言換成與Ｍicrosoft Windows相同。

• 簡單的說：就是要寫預存的時候，切到ENG鍵盤。 
  
• 另外一種方式：就是使用英文介面
  
  
  執行時自然就是英文模式。
  
  
  

[TSQL]function的寫法

[TSQL]Cursor寫法(迴圈)

SQL中的迴圈寫法之一。

[TSQL]修改欄位長度

ALTER TABLE invtempdb
ALTER COLUMN InvoiceID varchar(50)

[TSQL]如何寫預存程序

MSSQL中提供的功能，可供使用者撰寫預存程序（Stored Procedure)

預存程序，簡單的來說就是你寫好了一段(連串)SQL語法，存在資料庫中,
之後可以透過呼叫執行（EXEC　預存程序名稱），去執行已寫好的語法。

寫法如下：

• 新增預存程序使用 Create
• 新增預存程序使用 Alter

[MSSQL]更改欄位名稱

EXEC sp_rename 'TableName.OldColumnName', 'NewColumnName', 'COLUMN'; 

[筆記]SQL語法學習(一)

如何開始。

• 線上學習網站：
• https://www.w3schools.com/sql/
• https://www.1keydata.com/tw/sql/sql.html
• 線上寫code：
• sql fiddle：http://sqlfiddle.com
  教學說明：http://sqlfiddle.com/#!9/a6c585/1215
• 北風資料庫:微軟提供的範例資料庫
• Northwind and pubs Sample Databases for SQL Server 2000：
• https://www.microsoft.com/en-us/download/details.aspx?id=23654
• 語法：https://gist.github.com/KyleShen/a0821be841173ac05d4c7baff9caa53f

[筆記]資料庫寫入中文變成亂碼

參考文章：

• 插入中文字到SQL Server中，部分中文字變成"?"

問題描述：

• 在輸入文字「憙」時，發現中文字變成亂碼。

解法一：

• 在寫入欄位時，使用TSQL 前綴N'憙'，EX:Set Name=N'憙'

解法二：

• 查了當初寫入資料庫的語法，發現了問題點。
• SqlParameter tParam1 = new SqlParameter("@Name", SqlDbType.VarChar);
  tParam1.Value = "憙";
• 資料庫中欄位的型態，設定是NVARCHAR；程式端設定為NVARCHAR，中文就可以正確寫入。
• SqlParameter tParam1 = new SqlParameter("@Name", SqlDbType.NVarChar);
  tParam1.Value = "憙";

[筆記]MSSQL 限制存取

資料庫還原後，資料庫變成「限制存取」。

查了一下資料，限制存取被改動了。

【屬性】>【選項】>【限制存取】，改為MULTI_USER，即可。

參考：

• MSSQL 資料庫 - 限制的使用者

[筆記]查詢SQL SEVER連線數

select  db_name(dbid) , count(*) 'connections count'
from master..sysprocesses
where 1=1
and spid > 50
group by  db_name(dbid)
order by count(*) desc

資料庫排程備份

環境：

SQL Server 2008

首先要先確認一下 SQL Server Agent 服務是啟動的狀態，若是上線的 SQL Server 主機建議將服務設定為自動啟動。
以下就是登入 SQL Server Management Studio 時操作的畫面。
開啟物件總管，SQL Server Agent > 按右鍵 >新增作業

依照左側選單依序設定，前三項是必要執行的：一般、步驟、排程。
這個設定如下圖直接設定即可。


每一個作業中可以設定多項步驟。進入[步驟]，點擊下方的 [新增]

選取：類型、資料庫
再命令區中輸入要執行的T-SQL指令碼，可以點擊 [剖析] 測試是否可以正常執行。

T-SQL 指令建議事件撰寫好預存程序，在這個畫面上單純只是呼叫預存程序，不宜將太多程序放在這裡，避免日後要變更動作較繁瑣。
此處輸入自行撰寫的預存程序，主要功能是進行壓縮某一個資料庫的LOG檔案，完整語法可參閱
SQL 2008 Scheduling Backup and Shrink all db。
排程-設定
每一個作業中可以設定多項排程。進入[排程]，點擊下方的 [新增]

進入排程設定畫面，選取要執行的類型、執行頻率、時間…等。

這三個設定後，點擊下方的 [確定]，就完成一項新作業，可以在[物件總管]中會看到。

補充：壓縮語法
> USE MyDB;
GO
-- changing the database recovery model to simple.
ALTER DATABASE MyDB
SET RECOVERY SIMPLE;
GO
-- Shrink UserDB_log file to 20 MB.
DBCC SHRINKFILE (MyDB_log, 20);
GO
-- changing the database recovery model to FULL.
ALTER DATABASE MyDB
SET RECOVERY FULL;
GO

MSSQL 透過Database Mail發送信件

• 環境：
  • MSSQL 2008R2
• 參考資料：
  • https://vito-sql.blogspot.tw/2013/10/database-mail.html
  • https://msdn.microsoft.com/zh-tw/library/ms190307.aspx
  • https://technet.microsoft.com/zh-tw/library/ms188719(v=sql.105).aspx
  • https://www.dotblogs.com.tw/dc690216/archive/2009/09/10/10553.aspx
  • https://dba.stackexchange.com/questions/168395/execute-sql-statement-and-send-result-to-email

MSSQL 在【管理】>【Database Mail】提供發信的功能。

透過組態精靈設定：


啟用Database Mail 功能：

點選加入設定你要使用的SMTP帳戶。

新增DatabaseMail帳戶，請設定容易辨識的名稱，在發信時會用到，目前範例為DBMail。






測試是否設定正確：


使用預存程序發送郵件：

但仔細看一下發信的內容使用到了msdb的資料庫權限，所以不是管理者權限的帳號，
必須授權msdb下的DatabaseUserRole。

MSSQL修改設計Table不允許儲存變更

"不允許儲存變更，您所做的變更要求下列資料表必須先卸除然後再重新建立…”

其實是環境設定被預設值保護住了，將此選項拿掉就可以了。

尚未啟用目前資料庫的SQLServer Service Broker

最近在做資料庫備份還原設定的測試，
結果在執行程式是跳出了錯誤訊息
"尚未啟用目前資料庫的SQLServer Service Broker，
因此不支援查詢通知。如果您想要使用通知，請啟用這個資料庫的Service Broker。"

參考文章：

• 保哥：用SqlCacheDependency快取LINQtoSQL的查詢結果

執行以下語法，啟用Service Broker功能：

ALTER DATABASE [myTableName] SET ENABLE_BROKER

指令的確跑很久都不會停止，那是因為有人在使用資料庫。
執行sp_who，看看是誰在使用。

若是想要移除某個連線(EX:54)，執行Kill 54指令即可。
此時再次執行"ALTER DATABASE [myTableName] SET ENABLE_BROKER"，
即可成功。
也可以使用"SELECT name,is_broker_enabled FROM sys.databases"來檢測是否啟用成功。

MSSQL 產生Table結構含資料的指令碼

資料庫按右鍵選擇(1)工作(2)產生指令碼。

在此勾選你要匯出的table，並點選下一步。

 

點選[進階]，設定要匯出的資訊。

因為要匯出table的資料與結構的語法，所以選擇"結構描述和資料"，按下確定。

最後選擇要匯出的位置或方式，再按下一步，即可取得語法。

還原MSSQL資料時，遇到"無法獲得獨佔存權，因為資料庫正在使用中"的解決方法

在進行資料還原時，發現要還原的資料庫有人在連線，

所以便無法成功還原。

此時可以使用sp_who，查詢目前連線的資訊。

Ex:

sp_who

如上例，我們可以看到連線的機器與連線的帳號，此筆ID為68。

如果我們想要移除此連線，可以使用kill指令。

Ex:

kill 68

作完以上動作，即可正常還原資料庫。

 

相關參考連結：
http://www.dotblogs.com.tw/terrychuang/archive/2011/08/15/33186.aspx

Linked Server(連結的伺服器)

A與B資料庫存在於同一個伺服器，資料互相取得相當容易。

假設A與B資料分散為兩台伺服器，B要取得A的資料時，

可以使用Linked Server(連結的伺服器)技術來做串接。

設定方法如下

--Setp 1-Create LinkServer
--USE MASTER
--GO
--//[1] Create Linkserver
Exec sp_addlinkedserver
   @server='LDB_MEIHO_MIS', --//linkserver name.
   @srvproduct='LDB_MEI_MIS', --//一般描述
   @provider='SQLOLEDB', --//OLEDB Provider name, check BOL for more providers
   @datasrc='sql2k8r2', --//遠端Server Name  192.168.11.100\sql2k8
   @catalog='MEI_MIS' --//default database for linkserver
GO
--Step 2-Add linked server login
--//[2]Add linked server login
Exec sp_addlinkedsrvlogin
@useself='false', --//false=使用遠端使用者/密碼登入
--//true=使用本地端使用者/密碼連線遠端SERVER                       
@rmtsrvname='LDB_MEI_MIS', --//Linked server name
@rmtuser='admin' , --//遠端登入使用者
@rmtpassword='XXXX' --//遠端登入使用者密碼
GO

建立完成後可在  管理工具>伺服器物件>連結的伺服器  中，看到剛剛建立的物件。

取得LDB_MEI_MIS的資料，範例如下：

SELECT     TOP 1 *
FROM       LDB_MEI_MIS.MEI_MIS.dbo.UNIT

延伸：縮短資料庫物件名稱

寫預存程序時，若要使用資料表(LDB_MEI_MIS.MEI_MIS.dbo.UNIT)，畫面上打了一大串，可讀性變得很差。

提供兩種方式

• View
  • 方便預存撰寫
  • 自行定義調整
• 同義字 CREATE SYNONYM UNIT
FRO MEI_MIS.DBO.UNIT
• 

如何更換SQLServer2008R2的金鑰

• 進入安裝中心，在維護分類下，選擇版本升級

  

   

• 進入金鑰輸入的畫面：

  在此就可以進行更換金鑰或者更改版本的動作

  

• 接著進行以下動作

  
  
  
• 最後選擇升級，即可完成更換金鑰。