Docker MSSQL on Ubuntu 21.04

Install Docker Engine on Ubuntu

Quickstart: Run SQL Server container images with Docker

Docker Hub - Microsoft SQL Server

Install sqlcmd and bcp the SQL Server command-line tools on Linux

Current Docker Images from Docker Hub do not support Diagram Design Visual Tools.  That means you can not use SQL Management Studio to design the table. 

You can use Azure Data Studio to connect to MSSQL, but there is no fancy tool for table design. 

I just found Visual Studio Server Explorer can do the table design in VS.

Running SQL Server Developer in a Windows-based Docker Container

MSSQL UNPIVOT and PIVOT example

MSSQL UNPIVOT and PIVOT example

drop table #tmp_1;

SELECT UB92_Exp1Id, row_number() over (order by DxCode) as DxIdx, DxCode, DxCodes
into	#tmp_1
FROM   
   (SELECT	UB92_Exp1Id
		,UB_67 as DiagA
		,UB_68 as DiagB
		,UB_69 as DiagC
		,UB_70 as DiagD
		,UB_71 as DiagE
		,UB_72 as DiagF
		,UB_73 as DiagG
		,UB_74 as DiagH
		,UB_75 as DiagI
		,UB_67_I as DiagJ
		,UB_67_J as DiagK
		,UB_67_K as DiagL
	FROM Rey.UB92_Exp1
	where FileNo='xxxxxx') p  
UNPIVOT  
   (DxCodes FOR DxCode IN   
      (DiagA, DiagB, DiagC, DiagD, DiagE, DiagF, DiagG, DiagH, DiagI, DiagJ, DiagK, DiagL)  

) AS unpvt;  
  

select * from #tmp_1

SELECT	*
FROM
(
	select	a.UB92_Exp1Id, 'Diag'+Char(64+a.DxIdx) as Dx, a.DxCodes
	from	#tmp_1 a

) AS src 
PIVOT(
	max(DxCodes) FOR [Dx] IN ([DiagA],[DiagB],[DiagC],[DiagD],[DiagE],[DiagF],[DiagG],[DiagH],[DiagI],[DiagJ],[DiagK],[DiagL])
) AS DiagRow;

幾個與 MSSQL Server Backup 有關的 Link

• Step-by-Step Approach to Differential Backup and Recovery
• How to increase the SQL Database Restore speed using DB compression and Solid State Disks
• How to: Restore a Differential Database Backup (Transact-SQL)
• Recovering a database that is in the restoring state

How to check progress of DBCC SHRINKFILE?

當我們在 SQL Server Management Studio 做 Database Shrink 時, 往往看不到 Progress.

原文請參考: 這裡!

下面這個指令, 可以讓我們看到 Progress.

select T.text, R.Status, R.Command, DatabaseName = db_name(R.database_id) , R.cpu_time, R.total_elapsed_time, R.percent_complete from sys.dm_exec_requests R cross apply sys.dm_exec_sql_text(R.sql_handle) T

MSSQL 2005 Check Backup/Restore Progress

因為工作上需要, 有時候要 Restore SQL 2005 in command line mode.

底下的 Script 可以檢查大概的 progress.

相關資料請參考 這裡 .

SELECT r.session_id,r.command,CONVERT(NUMERIC(6,2),r.percent_complete) AS [Percent Complete],CONVERT(VARCHAR(20),DATEADD(ms,r.estimated_completion_time,GetDate()),20) AS [ETA Completion Time], CONVERT(NUMERIC(10,2),r.total_elapsed_time/1000.0/60.0) AS [Elapsed Min], CONVERT(NUMERIC(10,2),r.estimated_completion_time/1000.0/60.0) AS [ETA Min], CONVERT(NUMERIC(10,2),r.estimated_completion_time/1000.0/60.0/60.0) AS [ETA Hours], CONVERT(VARCHAR(1000),(SELECT SUBSTRING(text,r.statement_start_offset/2, CASE WHEN r.statement_end_offset = -1 THEN 1000 ELSE (r.statement_end_offset-r.statement_start_offset)/2 END) FROM sys.dm_exec_sql_text(sql_handle))) FROM sys.dm_exec_requests r WHERE command IN ('RESTORE DATABASE','BACKUP DATABASE')

Talend - MSSQL Connection for Multiple Instances

狀況:	Install 不同 Version 的 MSSQL 在同一台機器上, 當我們要使用 Talend MSSQL Connection 時, 該如何設定?

MSSQL Express 2017 problem - MSSQL configure does not set the default TCP port to 1433. We need to change the setting.

如何得知 MSSQL Database Restore Progress?

狀況	使用 Command Line mode restore a huge DB, 但是無法得知目前 Restore 進度.
參考資料	How to monitor backup and restore progress in SQL Server 2005 and 2008
Solution	SELECT session_id as SPID, command, start_time, percent_complete, dateadd(second,estimated_completion_time/1000, getdate()) as estimated_completion_time FROM sys.dm_exec_requests r WHERE r.command in ('RESTORE DATABASE');

How to write a text field in MS SQL server to file

最近做ㄧ些 EDI 的東西,由 SQL Server 直接產生 X12 的檔案, 存在 SQL Server 裏. 因為這些檔案必須寫出來變成 Text File, 所以在網路上找方法.

參考網址: https://www.simple-talk.com/sql/t-sql-programming/reading-and-writing-files-in-sql-server-using-t-sql/

這個網址上的 Function 可以 Work, 但是有些 SP 在 SQL 中, Default 是被 Disable 掉的, 所以請參考其他兩篇 Post, 去 Enable ㄧ些 System 的 SP.

http://blog.barksoftware.com/2015/05/the-execute-permission-was-denied-on.html

http://blog.barksoftware.com/2015/05/sql-server-enable-xpcmdshell-using.html

另外, 這個 Function 有一個嚴重的問題, 當使用 cursor 去呼叫這個 Function 寫出檔案, 超過 255 個檔案時, 就會 Error 了.  不過現在沒時間去找問題, 將就用吧.

Update: 5/14/2015
這個東西不能將就使用, 我在 SQL 2005 上, 寫出去 Unicode 的 Text File 有問題. 他就好像早期 VB 裡面使用 Unicode 那樣, 寫出去的 File 都是 ASCII + 00.  這個東西還是放棄吧.

SQL SERVER – Enable xp_cmdshell using sp_configure

MS SQL Server 因為安全考量, Default Disable 掉了 xp_cmdshell stored procedure.

參考網址: http://blog.sqlauthority.com/2007/04/26/sql-server-enable-xp_cmdshell-using-sp_configure/

底下是 Enable 的方法

---- To allow advanced options to be changed.
EXEC sp_configure ‘show advanced options’, 1
GO
—- To update the currently configured value for advanced options.
RECONFIGURE
GO
—- To enable the feature.
EXEC sp_configure ‘xp_cmdshell’, 1
GO
—- To update the currently configured value for this feature.
RECONFIGURE
GO

The EXECUTE permission was denied on the object 'sp_OACreate', database 'mssqlsystemresource', schema 'sys'.

有些與 File 操作有關的 SP 因為安全考量, Default 被 Disable 掉, 底下是 Solution.

參考網址: http://hardimodi.blogspot.com/2013/03/the-execute-permission-was-denied-on.htm

use [master]
GO
GRANT EXECUTE ON [sys].[sp_OASetProperty] TO [public]
GO
use [master]
GO
GRANT EXECUTE ON [sys].[sp_OAMethod] TO [public]
GO
use [master]
GO
GRANT EXECUTE ON [sys].[sp_OAGetErrorInfo] TO [public]
GO
use [master]
GO
GRANT EXECUTE ON [sys].[sp_OADestroy] TO [public]
GO
use [master]
GO
GRANT EXECUTE ON [sys].[sp_OAStop] TO [public]
GO
use [master]
GO
GRANT EXECUTE ON [sys].[sp_OACreate] TO [public]
GO
use [master]
GO
GRANT EXECUTE ON [sys].[sp_OAGetProperty] TO [public]
GO
sp_configure 'show advanced options', 1
GO
reconfigure
go

exec sp_configure
go
exec sp_configure 'Ole Automation Procedures', 1
-- Configuration option 'Ole Automation Procedures' changed from 0 to 1. Run the RECONFIGURE statement to install.
go
reconfigure
go

MS SQL Cursor Template

DECLARE x_cursor CURSOR FOR

-- add select here
SELECT name
FROM MASTER.dbo.sysdatabases
WHERE name NOT IN ('master','model','msdb','tempdb')

OPEN x_cursor
FETCH NEXT FROM x_cursor INTO @name

WHILE @@FETCH_STATUS = 0
BEGIN
       -- do something here

       FETCH NEXT FROM x_cursor INTO @name
END

CLOSE x_cursor
DEALLOCATE x_cursor

Original Source: http://www.cryer.co.uk/brian/sqlserver/replication_requires_actual_server_name.htm

SQL Server replication requires the actual server name to make a connection to the server

---

Symptom:

When attempting to create a new replication publication the following error is produced:

New Publication Wizard

SQL Server is unable to connect to server 'SSSS'.

Additional information:
SQL Server replication requires the actual server name to make a connection to the server. Connections through a server alias, IP address, or any other alternative name are not supported. Specify the actual server name, 'OOOO'. (Replication.Utilities)

Where "SSSS" is the name of the current server, and (in my case) "OOOO" was the previous name of the same server.
Steps to reproduce this problem:

1. Microsoft SQL Server Management Studio
2. Expand the server
3. Expand Replication
4. Right click "Local Publications" and select "New Publication ..."

Cause:

This error has been observed on a server that had been renamed after the original installation of SQL Server, and where the SQL Server configuration function ‘@@SERVERNAME’ still returned the original name of the server. This can be confirmed by:

select @@SERVERNAME
go

This should return the name of the server. If it does not then follow the procedure below to correct it.

Remedy:

To resolve the problem the server name needs to be updated. Use the following:

sp_addserver 'real-server-name', LOCAL

if this gives an error complaining that the name already exists then use the following sequence:

sp_dropserver 'real-server-name'
go
sp_addserver 'real-server-name', LOCAL
go

If instead the error reported is 'There is already a local server.' then use the following sequence:

sp_dropserver old-server-name
go
sp_addserver real-server-name, LOCAL
go

Where the "old-server-name" is the name contained in the body of the original error.
Stop and restart SQL Server.

Get size of all tables in MSSQL 2005 database

Original Doc: http://stackoverflow.com/questions/7892334/get-size-of-all-tables-in-database

SELECT 
    t.NAME AS TableName,
    p.rows AS RowCounts,
    SUM(a.total_pages) * 8 AS TotalSpaceKB, 
    SUM(a.used_pages) * 8 AS UsedSpaceKB, 
    (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB
FROM 
    sys.tables t
INNER JOIN      
    sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN 
    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN 
    sys.allocation_units a ON p.partition_id = a.container_id
WHERE 
    t.NAME NOT LIKE 'dt%' 
    AND t.is_ms_shipped = 0
    AND i.OBJECT_ID > 255 
GROUP BY 
    t.Name, p.Rows
ORDER BY 
    t.Name

Similar Text

在 Programming 的時候, 有很多地方我們需要用到字串相似度的檢驗, 在 PHP 裡有現成的 Function 可以使用, 但是在其他地方就未必.

這裡有一個 JavaScript 的 Implementation. Click here!

在 MS SQL 上, 我根據這個 JavaScript 改了一個版本, 勉強可以使用

CREATE FUNCTION [dbo].[similar_text] (     @first   NVARCHAR(100),     @second  NVARCHAR(100),     @percent BIT = 1 ) RETURNS DECIMAL(10,3) AS BEGIN     DECLARE @ret DECIMAL(10,3);        DECLARE @s1 NVARCHAR(100);     DECLARE @s2 NVARCHAR(100);        DECLARE @pos1 INT;     DECLARE @pos2 INT;     DECLARE @max  INT;     DECLARE @fl1  INT;     DECLARE @fl2  INT;     DECLARE @p    INT;     DECLARE @q    INT;     DECLARE @l    INT;     DECLARE @sum  INT;     SET @s1 = ISNULL(@first,'');     SET @s2 = ISNULL(@second,'');        IF (@s1='') OR (@s2='') BEGIN         SET @ret = 0.00;     END     ELSE BEGIN         SET @pos1 = 1;         SET @pos2 = 1;         SET @max  = 1;           SET @fl1  = len(@s1);         SET @fl2  = len(@s2);         SET @p    = 1;                WHILE (@p<=@fl1) BEGIN             SET @q = 1;             WHILE (@q<=@fl2) BEGIN                 SET @l = 1;                 WHILE (@p+@l<=@fl1) AND (@q+@l<=@fl2) AND (SUBSTRING( @s1,@p+@l,1)=SUBSTRING( @s2,@q+@l,1)) BEGIN                     IF (@l>=@max) BEGIN                         SET @max = @l;                         SET @pos1 = @p;                         SET @pos2 = @q;                     END;                     SET @l = @l+1;                 END;                 SET @q = @q+1;             END;             SET @p = @p+1;         END;                SET @sum = @max;         IF (@sum>1) BEGIN             IF (@pos1>=1) AND (@pos2>=1) BEGIN                 SET @sum = @sum + dbo.similar_text(SUBSTRING(@s1,1,@pos2),SUBSTRING(@s2,1,@pos2),0);             END;                        IF (@pos1+@max<=@fl1) AND (@pos2+@max<=@fl2) BEGIN                 SET @sum = @sum + dbo.similar_text(SUBSTRING(@s1,@pos1+@max,@fl1-@pos1-@max),SUBSTRING(@s2,@pos2+@max,@fl2-@pos2-@max),0);             END;               END;                IF (@percent=0)             SET @ret = @sum;         ELSE             SET @ret = (@sum * 200.00) / (@fl1 + @fl2);     END;        RETURN @ret; END GO

SQL Server Backup File 太大, 無法 Copy 回來 Restore.

這次碰到的狀況是 SQL Server 的 Backup File 太大, 想要 Copy 到 Local 來做 Restore 不可行. 使用 UNC 透過網路來 Restore 也失敗, 所以想到一個方法, 因為 SQL Server 的 backup file 壓縮率都很高, 好幾百 MB 壓到最後可能只有 20MB. 所以先把 backup file 壓縮起來 Copy 到 local, 然後用 WinMount 這個軟體把 Compressed 檔案 Mount 進來當作一個 Hard Drive, 然後再讓 MS SQL 去做 Restore. 這樣就可以解決 Backup File 太大無法 Copy 回來 Restore 的問題.

Get Database Table Field Name

有的時候, DB Table 的 fields 太多, 要慢慢打嫌多, 又怕打錯, 所以從 DB 中抓出來是最保險的. 底下指令可以這個動作,

For MSSQL:

SELECT * FROM information_schema.columns WHERE table_name = 'TableName'; 

For MYSQL:

SHOW [FULL] COLUMNS {FROM | IN} tbl_name [{FROM | IN} db_name] [LIKE 'pattern' | WHERE expr]

SQL Server 2008 Change Computer Name

如果我們在已經裝好的 SQL Server 機器上, Change 了 Computer Name, 會造成連接不上的狀況. 處理方式如下,

URL : http://support.microsoft.com/kb/899159