浮雲雅築: 9月 2016

2016年9月30日星期五

[研究] 快速檢視 Outlook 2010 郵件標頭 (header)

[研究] 快速檢視 Outlook 2010 郵件標頭 (header)

2016-09-30

一點通 - 爲什麽在 Outlook 2010 找不到察看郵件標頭資訊的功能呢？ (MVP 撰寫)
https://support.microsoft.com/zh-tw/kb/983328
這篇圖文都有，但是要看 header 必須先開啟信件
而一般對於可疑信件，是不該開啟的

需要快速存取 Outlook 2010 中的郵件標頭嗎？
https://blogs.technet.microsoft.com/office_cht/2011/06/15/outlook-2010-2/
這篇說明不用開啟信件就可以看到 header 的方法，但是按鈕的產生沒有說明

方法如下

(完)

2016年9月24日星期六

[研究] WordPress 4.6.1 部落格伺服器架設 (CentOS 7.2 x64)(繁體中文版)

[研究] WordPress 4.6.1 部落格伺服器架設 (CentOS 7.2 x64)(繁體中文版)

2016-09-24

WordPress 台灣正體中文網站
http://tw.wordpress.org/

安裝參考
http://tw.wordpress.org/txt-install/

下載
https://tw.wordpress.org/wordpress-4.6.1-zh_TW.tar.gz

# 建立 WordPress 系統使用的資料庫，稱為 wordpressdb，連線帳號 wordpresser，密碼 123456
(可以依自己喜好修改)

安裝

# 立刻關閉 SELinux
/usr/sbin/setenforce 0

# 設定 reboot 後也關閉 SELinux
sed -i -e "s@SELINUX=enforcing@#SELINUX=enforcing@" /etc/selinux/config
sed -i -e "s@SELINUX=permissive@#SELINUX=permissive@" /etc/selinux/config
sed -i -e "/SELINUX=/aSELINUX=disabled" /etc/selinux/config

yum -y install php php-mysql mysql httpd mariadb-server

#chkconfig httpd on
#service httpd restart
systemctl enable httpd
systemctl start httpd
systemctl status httpd

#chkconfig mysqld on
#service mysqld restart
systemctl enable mariadb
systemctl start mariadb
systemctl status mariadb

# 替 MySQL 的 root 帳號設定密碼
/usr/bin/mysql_secure_installation

[root@localhost ~]# /usr/bin/mysql_secure_installation
/usr/bin/mysql_secure_installation: line 379: find_mysql_client: command not found

NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB
SERVERS IN PRODUCTION USE! PLEASE READ EACH STEP CAREFULLY!

In order to log into MariaDB to secure it, we'll need the current
password for the root user. If you've just installed MariaDB, and
you haven't set the root password yet, the password will be blank,
so you should just press enter here.

Enter current password for root (enter for none):
OK, successfully used password, moving on...

Setting the root password ensures that nobody can log into the MariaDB
root user without the proper authorisation.

Set root password? [Y/n]
New password:
Re-enter new password:
Password updated successfully!
Reloading privilege tables..
... Success!

By default, a MariaDB installation has an anonymous user, allowing anyone
to log into MariaDB without having to have a user account created for
them. This is intended only for testing, and to make the installation
go a bit smoother. You should remove them before moving into a
production environment.

Remove anonymous users? [Y/n]
... Success!

Normally, root should only be allowed to connect from 'localhost'. This
ensures that someone cannot guess at the root password from the network.

Disallow root login remotely? [Y/n]
... Success!

By default, MariaDB comes with a database named 'test' that anyone can
access. This is also intended only for testing, and should be removed
before moving into a production environment.

Remove test database and access to it? [Y/n]
- Dropping test database...
... Success!
- Removing privileges on test database...
... Success!

Reloading the privilege tables will ensure that all changes made so far
will take effect immediately.

Reload privilege tables now? [Y/n]
... Success!

Cleaning up...

All done! If you've completed all of the above steps, your MariaDB
installation should now be secure.

Thanks for using MariaDB!
[root@localhost ~]#

# 建立 WordPress 系統使用的資料庫，稱為 wordpressdb，連線帳號 wordpresser，密碼 123456
(可以依自己喜好修改)

[root@localhost ~]# mysql -u root -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 10
Server version: 5.5.50-MariaDB MariaDB Server

Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> create database wordpressdb;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> grant all privileges on wordpressdb.* to wordpresser@localhost identified by '123456';
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> exit
Bye
[root@localhost ~]#

# 假設 MySQL root 的密碼為 ABCDE
mysqladmin --user=root -pABCDE create wordpressdb
# 建立使用者 galerauser，密碼 123456，給 Cluster 使用
mysql -e "GRANT ALL PRIVILEGES ON *.* TO wordpresser@localhost IDENTIFIED BY '123456' ; " -pABCDE
#mysql -e "GRANT ALL PRIVILEGES ON *.* TO wordpresser@% IDENTIFIED BY '123456' ; " -pABCDE
mysql -e " flush privileges; " -pABCDE

mysql -uroot -pABCDE -e "show databases;"

#mysql -e -uroot -pABCDE "use wordpressdb; show databases; "

#mysql -e "use wordpressdb; show tables; "
#mysql -e "GRANT ALL PRIVILEGES ON wordpressdb.* TO wordpresser@localhost IDENTIFIED BY 'wordpresser' ; "
#mysql -e " flush privileges; "
#mysql -e "use mysql; select * from user where User='wordpresser'; "

cd /usr/local/src
wget https://tw.wordpress.org/wordpress-4.6.1-zh_TW.tar.gz
tar xzvf wordpress-4.6.1-zh_TW.tar.gz -C /var/www/html
# 為了寫入 wp-config.php
chmod -R a+w /var/www/html/wordpress
#touch /var/www/html/wordpress/wp-config.php
#chmod a+w /var/www/html/wordpress/wp-config.php

# 啟動瀏覽器，連上網站
firefox httpd://192.168.128.71/wordpress

(完)

[研究] WordPress 4.6.1 部落格伺服器架設 (CentOS 7.2 x64)(繁體中文版)
http://shaurong.blogspot.com/2016/09/wordpress-461-centos-72-x64.html

[研究] WordPress 4.2.2 部落格伺服器架設 (CentOS 7.1 x64)(繁體中文版)(失敗)
http://shaurong.blogspot.com/2015/06/wordpress-422-centos-71-x64.html

[研究] WordPress 4.2.2 部落格伺服器架設 (CentOS 6.6 x64)(繁體中文版)
http://shaurong.blogspot.com/2015/06/wordpress-422-centos-66-x64.html

[研究] WordPress 3.8.1 部落格伺服器架設 (CentOS 6.5 x64)(繁體中文版)
http://shaurong.blogspot.com/2014/01/wordpress-381-centos-65-x64.html

[研究] WordPress 3.7 部落格伺服器架設 (CentOS 6.4 x64)(繁體中文版)
http://shaurong.blogspot.tw/2013/10/wordpress-27-centos-64-x64.html

[研究] WordPress網誌快速安裝程式(CentOS 5.5 x86)
http://forum.icst.org.tw/phpbb/viewtopic.php?t=19279

2016年9月14日星期三

[研究] 無法插入NULL值到資料行，資料行不得有Null。UPDATE失敗

[研究] 無法插入NULL值到資料行，資料行不得有Null。UPDATE失敗

2016-09-14

環境：Visual Studio 2015 with Update 3
語言 C#

錯誤訊息：無法插入NULL值到資料行，資料行不得有Null。UPDATE失敗

解決：

用 <asp:Parameter 當關鍵字搜尋整個方案

<InsertParameters>
<asp:Parameter Name="xxx" Type="String" />
</InsertParameters>

<UpdateParameters>
<asp:Parameter Name="xxx" Type="String" />
</UpdateParameters>

把
<asp:Parameter Name="xxx" Type="String" />
改為
<asp:Parameter Name="xxx" DefaultValue="" ConvertEmptyStringToNull="false" Type="String" />

Type 為整數的，預設值可以給個 0

(完)

[研究] 無法插入NULL值到資料行，資料行不得有Null。UPDATE失敗

2016年9月13日星期二

[研究] SQL Server 資料庫 NULL 欄位調正(一)

[研究] SQL Server 資料庫 NULL 欄位調正(一)

2016-09-10

由於不斷的測試、修改，改到有點亂了，有問題請參考第二篇

[研究] SQL Server 資料庫 NULL 欄位調正(二) ASP.NET + C#
http://shaurong.blogspot.com/2016/09/sql-server-null-aspnet-c.html

準備把 Database 中所有允許 NULL 欄位做調整，預計

備份資料庫
所有允許 NULL 欄位都設定預設值 (DEFAULT)
欄位中 NULL 換成非 NULL 值 (不然無法把欄位格式轉成 NOT NULL)
把欄位格式轉成 NOT NULL

********************************************************************************

1.備份資料庫 (作法略)

********************************************************************************

前置瞭解狀況

A.先了解有哪幾種 Data Type

在 SQL Server Manage Studio 中執行，列出資料庫中所有用到的欄位種類

select DATA_TYPE from information_schema.columns group by DATA_TYPE

結果

bit
datetime
datetime2
int
ntext
nvarchar
uniqueidentifier
varbinary

B.評估要處理的資料表(table)有哪些

在 SQL Server Manage Studio 中執行，列出所有資料表 (table) 名稱，評估要排除的，增加 WHERE 條件去排除

select distinct TABLE_NAME from information_schema.columns order by TABLE_NAME

結果 (這是小弟的情況，排除的資料表基本上是系統資料表或某些套件建立的，留下自己建立的)

SELECT * FROM information_schema.columns
WHERE table_catalog = '資料庫名稱'
AND table_name NOT LIKE '%zDel%'
AND table_name NOT LIKE '%aspnet%'
AND table_name NOT LIKE '%__MigrationHistory%'
AND table_name NOT LIKE 'View%'
AND table_name <> '%ELMAH_Error%'
AND table_name <> 'Sessions'
AND table_name <> 'sysdiagrams'
AND is_nullable = 'YES'

********************************************************************************

2.所有欄位都設定預設值

A.所有欄位都設定預設值 (失敗，留下參考)

會出現 "不允許對系統目錄的特定更新。" 錯誤

而且基本上不建議修改系統資料表，很危險，下面僅供參考，最後放棄。

-- 解決 "不允許對系統目錄的特定更新。"
sp_configure 'allow updates',1
RECONFIGURE WITH override
go
-- 修正欄位格式 ntext, nvarchar 預設值為 ''
UPDATE information_schema.columns
SET column_default = ''
WHERE table_catalog = '資料庫名稱'
AND table_name NOT LIKE '%zDel%'
AND table_name NOT LIKE '%aspnet%'
AND table_name NOT LIKE '%__MigrationHistory%'
AND table_name NOT LIKE 'View%'
AND table_name <> '%ELMAH_Error%'
AND table_name <> 'Sessions'
AND table_name <> 'sysdiagrams'
AND is_nullable = 'YES'
AND ( data_type = 'ntext'
OR data_type = 'nvarchar' );

-- 修正欄位格式 datetime, datetime2 預設值為 datetime()
UPDATE information_schema.columns
SET column_default = 'datetime()'
WHERE table_catalog = '資料庫名稱'
AND table_name NOT LIKE '%zDel%'
AND table_name NOT LIKE '%aspnet%'
AND table_name NOT LIKE '%__MigrationHistory%'
AND table_name NOT LIKE 'View%'
AND table_name <> '%ELMAH_Error%'
AND table_name <> 'Sessions'
AND table_name <> 'sysdiagrams'
AND is_nullable = 'YES'
AND ( data_type = 'datetime'
OR data_type = 'datetime2' );

-- 修正欄位格式 int 預設值為 0
UPDATE information_schema.columns
SET column_default = 0
WHERE table_catalog = '資料庫名稱'
AND table_name NOT LIKE '%zDel%'
AND table_name NOT LIKE '%aspnet%'
AND table_name NOT LIKE '%__MigrationHistory%'
AND table_name NOT LIKE 'View%'
AND table_name <> '%ELMAH_Error%'
AND table_name <> 'Sessions'
AND table_name <> 'sysdiagrams'
AND is_nullable = 'YES'
AND ( data_type = 'int' );

-- 修正欄位格式 bit, 欄位名稱 IsValid 預設值為 1
UPDATE information_schema.columns
SET column_default = 1
WHERE table_catalog = '資料庫名稱'
AND table_name NOT LIKE '%zDel%'
AND table_name NOT LIKE '%aspnet%'
AND table_name NOT LIKE '%__MigrationHistory%'
AND table_name NOT LIKE 'View%'
AND table_name <> '%ELMAH_Error%'
AND table_name <> 'Sessions'
AND table_name <> 'sysdiagrams'
AND is_nullable = 'YES'
AND ( data_type = 'ntext'
OR data_type = 'nvarchar' )
AND ( column_name = 'IsValid' );

-- 修正欄位格式 bit, 欄位名稱不是 IsValid 預設值為 0
UPDATE information_schema.columns
SET column_default = 0
WHERE table_catalog = '資料庫名稱'
AND table_name NOT LIKE '%zDel%'
AND table_name NOT LIKE '%aspnet%'
AND table_name NOT LIKE '%__MigrationHistory%'
AND table_name NOT LIKE 'View%'
AND table_name <> '%ELMAH_Error%'
AND table_name <> 'Sessions'
AND table_name <> 'sysdiagrams'
AND is_nullable = 'YES'
AND ( data_type = 'ntext'
OR data_type = 'nvarchar' )
AND ( column_name <> 'IsValid' );
go
-- 復原 "不允許對系統目錄的特定更新。"
sp_configure 'allow updates',0
RECONFIGURE WITH override
go

B.所有欄位都設定預設值 (成功)

利用語法
ALTER TABLE [資料表名稱] ADD CONSTRAINT [DF_資料表名稱_欄位名稱] DEFAULT (欄位預設值 ) FOR [欄位名]

要記得依據自己資料庫名稱、欄位名稱做調整。

因為資料表名稱 or 欄位名稱可能是有保留字，要用 [ 和 ] 括弧。
use 資料庫名稱
TABLE_CATALOG='資料庫名稱'

use pms
go

SELECT 'ALTER TABLE ['+ [TABLE_NAME] + '] ADD CONSTRAINT [DF_' + [TABLE_NAME] + '_' + [COLUMN_NAME] +'] DEFAULT ('''') FOR [' + [COLUMN_NAME] + '];'
FROM information_schema.columns
WHERE TABLE_CATALOG='pms'
and [TABLE_NAME] not like '%zDel%'
and [TABLE_NAME] not like '%aspnet%'
and [TABLE_NAME] not like '%__MigrationHistory%'
and [TABLE_NAME] not like 'View%'
and [TABLE_NAME] <> '%ELMAH_Error%'
and [TABLE_NAME] <> 'Sessions'
and [TABLE_NAME] <> 'sysdiagrams'
and IS_NULLABLE ='YES'
and ( DATA_TYPE='ntext' or DATA_TYPE='nvarchar' )

union

SELECT 'ALTER TABLE ['+ [TABLE_NAME] + '] ADD CONSTRAINT [DF_' + [TABLE_NAME] + '_' + [COLUMN_NAME] +'] DEFAULT (''getdate()'') FOR [' + [COLUMN_NAME] + '];'
FROM information_schema.columns
WHERE TABLE_CATALOG='pms'
and [TABLE_NAME] not like '%zDel%'
and [TABLE_NAME] not like '%aspnet%'
and [TABLE_NAME] not like '%__MigrationHistory%'
and [TABLE_NAME] not like 'View%'
and [TABLE_NAME] <> '%ELMAH_Error%'
and [TABLE_NAME] <> 'Sessions'
and [TABLE_NAME] <> 'sysdiagrams'
and IS_NULLABLE ='YES'
and ( DATA_TYPE='datetime' or DATA_TYPE='datetime2' )

union

SELECT 'ALTER TABLE ['+ [TABLE_NAME] + '] ADD CONSTRAINT [DF_' + [TABLE_NAME] + '_' + [COLUMN_NAME] +'] DEFAULT (0) FOR [' + [COLUMN_NAME] + '];'
FROM information_schema.columns
WHERE TABLE_CATALOG='pms'
and [TABLE_NAME] not like '%zDel%'
and [TABLE_NAME] not like '%aspnet%'
and [TABLE_NAME] not like '%__MigrationHistory%'
and [TABLE_NAME] not like 'View%'
and [TABLE_NAME] <> '%ELMAH_Error%'
and [TABLE_NAME] <> 'Sessions'
and [TABLE_NAME] <> 'sysdiagrams'
and IS_NULLABLE ='YES'
and ( DATA_TYPE='int')

union

SELECT 'ALTER TABLE ['+ [TABLE_NAME] + '] ADD CONSTRAINT [DF_' + [TABLE_NAME] + '_' + [COLUMN_NAME] +'] DEFAULT (1) FOR [' + [COLUMN_NAME] + '];'
FROM information_schema.columns
WHERE TABLE_CATALOG='pms'
and [TABLE_NAME] not like '%zDel%'
and [TABLE_NAME] not like '%aspnet%'
and [TABLE_NAME] not like '%__MigrationHistory%'
and [TABLE_NAME] not like 'View%'
and [TABLE_NAME] <> '%ELMAH_Error%'
and [TABLE_NAME] <> 'Sessions'
and [TABLE_NAME] <> 'sysdiagrams'
and IS_NULLABLE ='YES'
and ( DATA_TYPE='bit')
and ( COLUMN_NAME='IsValid')

union

SELECT 'ALTER TABLE ['+ [TABLE_NAME] + '] ADD CONSTRAINT [DF_' + [TABLE_NAME] + '_' + [COLUMN_NAME] +'] DEFAULT (0) FOR [' + [COLUMN_NAME] + '];'
FROM information_schema.columns
WHERE TABLE_CATALOG='pms'
and [TABLE_NAME] not like '%zDel%'
and [TABLE_NAME] not like '%aspnet%'
and [TABLE_NAME] not like '%__MigrationHistory%'
and [TABLE_NAME] not like 'View%'
and [TABLE_NAME] <> '%ELMAH_Error%'
and [TABLE_NAME] <> 'Sessions'
and [TABLE_NAME] <> 'sysdiagrams'
and IS_NULLABLE ='YES'
and ( DATA_TYPE='bit')
and ( COLUMN_NAME<>'IsValid')

結果

未免其中一行執行失敗，後面不會再執行，每一行之後要插入 GO指令
GO 和 ALTER TABLE 可先寫到 Notepad 上，用 UltraEdit 進行取代動作 ( 很多 Editor 的取代無法包含看不到的換行字元，UltraEdit 可以做到)，最後一行要手動新增，再執行 GO

貼到 SQL Server Management Studio 中執行，注意錯誤訊息，修正之後再度執行 ( 設定過再設一次會出錯)

********************************************************************************

3.欄位中 NULL 換成非 NULL 值 (不然無法把欄位格式轉成 NOT NULL)

因為資料表名稱 or 欄位名稱可能是有保留字，要用 [ 和 ] 括弧。
use 資料庫名稱
TABLE_CATALOG='資料庫名稱'
datetime 和 datetime2 本來塞 DEFAULT (''getdate()'')，但不行，改用 DEFAULT (''0-1-1'')

use pms ;

-- 若 ntext, nvarchar 欄位允許 null ，先更新欄位內容
SELECT 'UPDATE ['+ [TABLE_NAME] + '] SET [' + [COLUMN_NAME] +']='''' WHERE [' + [COLUMN_NAME] + '] is NULL;'
FROM information_schema.columns
WHERE TABLE_CATALOG='pms'
and [TABLE_NAME] not like '%zDel%'
and [TABLE_NAME] not like '%aspnet%'
and [TABLE_NAME] not like '%__MigrationHistory%'
and [TABLE_NAME] not like 'View%'
and [TABLE_NAME] <> '%ELMAH_Error%'
and [TABLE_NAME] <> 'Sessions'
and [TABLE_NAME] <> 'sysdiagrams'
and IS_NULLABLE ='YES'
and ( DATA_TYPE='ntext' or DATA_TYPE='nvarchar' )
group by TABLE_NAME, COLUMN_NAME, DATA_TYPE, COLUMN_DEFAULT, IS_NULLABLE
--order by TABLE_NAME, COLUMN_NAME, DATA_TYPE, COLUMN_DEFAULT, IS_NULLABLE
union
-- 若 datetime, datetime2 欄位允許 null ，先更新欄位內容
SELECT 'UPDATE ['+ [TABLE_NAME] + '] SET [' + [COLUMN_NAME] +']=''0-1-1'' WHERE [' + [COLUMN_NAME] + '] is NULL;'
FROM information_schema.columns
WHERE TABLE_CATALOG='pms'
and [TABLE_NAME] not like '%zDel%'
and [TABLE_NAME] not like '%aspnet%'
and [TABLE_NAME] not like '%__MigrationHistory%'
and [TABLE_NAME] not like 'View%'
and [TABLE_NAME] <> '%ELMAH_Error%'
and [TABLE_NAME] <> 'Sessions'
and [TABLE_NAME] <> 'sysdiagrams'
and IS_NULLABLE ='YES'
and ( DATA_TYPE='datetime' or DATA_TYPE='datetime2' )
group by TABLE_NAME, COLUMN_NAME, DATA_TYPE, COLUMN_DEFAULT, IS_NULLABLE
--order by TABLE_NAME, COLUMN_NAME, DATA_TYPE, COLUMN_DEFAULT, IS_NULLABLE
union
-- 若 int 欄位允許 null ，先更新欄位內容
SELECT 'UPDATE ['+ [TABLE_NAME] + '] SET [' + [COLUMN_NAME] +']=''0'' WHERE [' + [COLUMN_NAME] + '] is NULL;'
FROM information_schema.columns
WHERE TABLE_CATALOG='pms'
and [TABLE_NAME] not like '%zDel%'
and [TABLE_NAME] not like '%aspnet%'
and [TABLE_NAME] not like '%__MigrationHistory%'
and [TABLE_NAME] not like 'View%'
and [TABLE_NAME] <> '%ELMAH_Error%'
and [TABLE_NAME] <> 'Sessions'
and [TABLE_NAME] <> 'sysdiagrams'
and IS_NULLABLE ='YES'
and ( DATA_TYPE='int')
group by TABLE_NAME, COLUMN_NAME, DATA_TYPE, COLUMN_DEFAULT, IS_NULLABLE
--order by TABLE_NAME, COLUMN_NAME, DATA_TYPE, COLUMN_DEFAULT, IS_NULLABLE

union
-- 若 bit 欄位允許 null ，且欄位為 IsValid，先更新欄位內容
SELECT 'UPDATE ['+ [TABLE_NAME] + '] SET [' + [COLUMN_NAME] +']=''1'' WHERE [' + [COLUMN_NAME] + '] is NULL;'
FROM information_schema.columns
WHERE TABLE_CATALOG='pms'
and [TABLE_NAME] not like '%zDel%'
and [TABLE_NAME] not like '%aspnet%'
and [TABLE_NAME] not like '%__MigrationHistory%'
and [TABLE_NAME] not like 'View%'
and [TABLE_NAME] <> '%ELMAH_Error%'
and [TABLE_NAME] <> 'Sessions'
and [TABLE_NAME] <> 'sysdiagrams'
and IS_NULLABLE ='YES'
and ( DATA_TYPE='bit')
and ( COLUMN_NAME='IsValid')
group by TABLE_NAME, COLUMN_NAME, DATA_TYPE, COLUMN_DEFAULT, IS_NULLABLE
--order by TABLE_NAME, COLUMN_NAME, DATA_TYPE, COLUMN_DEFAULT, IS_NULLABLE

union
-- 若 bit 欄位允許 null ，且欄位為 IsValid，先更新欄位內容
SELECT 'UPDATE ['+ [TABLE_NAME] + '] SET [' + [COLUMN_NAME] +']=''0'' WHERE [' + [COLUMN_NAME] + '] is NULL;'
FROM information_schema.columns
WHERE TABLE_CATALOG='pms'
and [TABLE_NAME] not like '%zDel%'
and [TABLE_NAME] not like '%aspnet%'
and [TABLE_NAME] not like '%__MigrationHistory%'
and [TABLE_NAME] not like 'View%'
and [TABLE_NAME] <> '%ELMAH_Error%'
and [TABLE_NAME] <> 'Sessions'
and [TABLE_NAME] <> 'sysdiagrams'
and IS_NULLABLE ='YES'
and ( DATA_TYPE='bit')
and ( COLUMN_NAME<>'IsValid')
group by TABLE_NAME, COLUMN_NAME, DATA_TYPE, COLUMN_DEFAULT, IS_NULLABLE
--order by TABLE_NAME, COLUMN_NAME, DATA_TYPE, COLUMN_DEFAULT, IS_NULLABLE

貼到 SQL Server Management Studio 中執行

未免其中一行執行失敗，後面不會再執行，每一行之後要插入 GO指令
GO 和 ALTER 可先寫到 Notepad 上，用 UltraEdit 進行取代動作 ( 很多 Editor 的取代無法包含看不到的換行字元，UltraEdit 可以做到)

(注意下面 UPDATE 後面要多一個空格，免得換到別的字)

最後一行要手動新增 GO

再貼回 SQL Server Management Studio 中執行

某些錯誤去看一下發生甚麼事情，可能是已經被設定過了，或是外部 KEY

********************************************************************************

4.把欄位格式轉成 NOT NULL

列出允許 NULL 欄位

select c.name, t.name + case when t.name in ('nvarchar','nchar')
then '('+ cast(c.max_length/2 as nvarchar) +')'
when t.name in ('varchar','binary', 'char', 'varbinary')
then '('+ cast(c.max_length as nvarchar) +')'
else '' end, case when c.is_nullable = 1 then 'Yes' ELSE 'No' end
from sys.columns c join sys.types t on c.system_type_id = t.system_type_id
where object_id = object_id ('dbo.account') and t.name <> 'sysname'
order by column_id

產生把 NULL 改為 NOT NULL 的 SQL 語法

網路找的，有問題

select 'ALTER TABLE dbo.資料表名稱 ALTER COLUMN ' +
QUOTENAME(c.name) + ' ' + t.name +
case when t.name in ('nvarchar','nchar')
then '('+ cast(c.max_length/2 as nvarchar) +')'
when t.name in ('varchar','binary', 'char', 'varbinary')
then '('+ cast(c.max_length as nvarchar) +')'
else '' end + ' NOT NULL'
from sys.columns c join sys.types t on c.system_type_id = t.system_type_id
where object_id = object_id ('dbo.資料表名稱') and t.name <> 'sysname'
order by column_id

分析

資料表名稱和欄位名稱可能用到保留字，要加 [ 和 ]，裡面不能有 dbo

nvarchar(50) NULL 改成 nvarchar(MAX) NOT NULL 會失敗，長度必須相同

另外 nvarchar(MAX) 在 sys.columns 的 max_length 是紀錄為 -1，除 2 會變成 0，會出錯

-------------------------------------------
c.max_length, t.max_length, c.name, t.name
-------------------------------------------
4 4 Id int
4 4 Priority int
8 8 CreatTime datetime
1 1 IsValid bit
1 1 Role_Admin bit
1 1 Role_Plan bit
1 1 Role_Todo bit
1 1 Role_Audit bit
-1 8000 OID nvarchar
-1 8000 Name nvarchar
-1 8000 Username nvarchar
-1 8000 Password nvarchar
-1 8000 Email nvarchar
-1 8000 Provider nvarchar
100 8000 Impersonate nvarchar
-------------------------------------------

修正為

select 'ALTER TABLE [' + 資料表名稱 + '] ALTER COLUMN ' +
QUOTENAME(c.name) + ' ' + t.name +
case when t.name in ('nvarchar','nchar') and c.max_length <>-1
then '('+ cast(c.max_length/2 as nvarchar) +')'
when t.name in ('nvarchar','nchar') and c.max_length =-1
then '(MAX)'
when t.name in ('varchar','binary', 'char', 'varbinary')
then '('+ cast(c.max_length as nvarchar) +')'
else '' end + ' NOT NULL'
from sys.columns c join sys.types t on c.system_type_id = t.system_type_id
where
object_id = object_id ('dbo.資料表名稱')
and t.name <> 'sysname'

C# ASP.NET 程式

(下圖) 產出修整改用的 SQL 語法

(下圖) 貼到 SQL Server Management 中執行，產出了可以把 NULL 欄位轉成 NOT NULL 的SQL 語法，再放到 SQL Server Management 中執行

********************************************************************************

(完)

相關

[研究] 用SQL指令找出資料庫的資料表、欄位名、PK欄位、資料筆數
http://shaurong.blogspot.tw/2016/02/sqlpk.html

[研究] SQL Server 資料庫 NULL 欄位調正(一)
http://shaurong.blogspot.com/2016/09/sql-server-null.html

[研究] SQL Server 資料庫 NULL 欄位調正(二) ASP.NET + C#
http://shaurong.blogspot.com/2016/09/sql-server-null-aspnet-c.html

[研究] SQL Server 資料庫 NULL 欄位調正(二) ASP.NET + C#

[研究] SQL Server 資料庫 NULL 欄位調正(二) ASP.NET + C#

2016-09-12

續這篇

[研究] SQL Server 資料庫 NULL 欄位調正(一)
http://shaurong.blogspot.com/2016/09/sql-server-null.html

後來打算寫個程式來處理，產生3個 SQL 語法，可以用來直接丟到 SQL Server Management Studio 2016 v16.3 上跑。

為何不寫程式跑完全部？為了可以在SQL Server Management Studio 2016上看到是否仍有其他問題。

WebForm1.aspx

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="WebForm1.aspx.cs" Inherits="WebApplication1.WebForm1" %>

<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<meta http-equiv="Content-Type" content="text/html; charset=utf-8"/>
<title></title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:Button ID="Button1" runat="server" Text="2. Generate SQL - Set NULL field Format the DEFAULT Value" OnClick="Button1_Click" /> 
 
<asp:Button ID="Button2" runat="server" Text="3. Generate SQL - Give NULL field a Value" OnClick="Button2_Click" /> 
 
<asp:Button ID="Button3" runat="server" Text="4. Generate SQL - Change NULL Format to NOT NULL Format" OnClick="Button3_Click" /> 
 
<asp:Literal ID="Literal1" runat="server"></asp:Literal> 
<asp:Literal ID="Literal2" runat="server" Visible="false"></asp:Literal> 
</div>
</form>
</body>
</html>

資料庫的帳號、密碼、主機IP、資料庫名稱請依照自己環境修改

WebForm1.aspx.cs

using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

namespace WebApplication1
{
public partial class WebForm1 : System.Web.UI.Page
{
const string connectionString = "Data Source=.;Initial Catalog=pms;User ID=sa;Password=P@ssw0rd";
protected void Page_Load(object sender, EventArgs e)
{
}

protected void Button1_Click(object sender, EventArgs e)
{
Literal1.Text = "";
using (SqlConnection connection = new SqlConnection(
connectionString))
{
SqlCommand command = new SqlCommand(@"

SELECT 'ALTER TABLE ['+ [TABLE_NAME] + '] ADD CONSTRAINT [DF_' + [TABLE_NAME] + '_' + [COLUMN_NAME] +'] DEFAULT ('''') FOR [' + [COLUMN_NAME] + '];'
FROM information_schema.columns
WHERE TABLE_CATALOG='pms'
and [TABLE_NAME] not like '%zDel%'
and [TABLE_NAME] not like '%aspnet%'
and [TABLE_NAME] not like '%__MigrationHistory%'
and [TABLE_NAME] not like 'View%'
and [TABLE_NAME] <> '%ELMAH_Error%'
and [TABLE_NAME] <> 'Sessions'
and [TABLE_NAME] <> 'sysdiagrams'
and IS_NULLABLE ='YES'
and COLUMN_DEFAULT is NULL
and ( DATA_TYPE='ntext' or DATA_TYPE='nvarchar' )

union

SELECT 'ALTER TABLE ['+ [TABLE_NAME] + '] ADD CONSTRAINT [DF_' + [TABLE_NAME] + '_' + [COLUMN_NAME] +'] DEFAULT (getdate()) FOR [' + [COLUMN_NAME] + '];'
FROM information_schema.columns
WHERE TABLE_CATALOG='pms'
and [TABLE_NAME] not like '%zDel%'
and [TABLE_NAME] not like '%aspnet%'
and [TABLE_NAME] not like '%__MigrationHistory%'
and [TABLE_NAME] not like 'View%'
and [TABLE_NAME] <> '%ELMAH_Error%'
and [TABLE_NAME] <> 'Sessions'
and [TABLE_NAME] <> 'sysdiagrams'
and IS_NULLABLE ='YES'
and COLUMN_DEFAULT is NULL
and ( DATA_TYPE='datetime' or DATA_TYPE='datetime2' )

union

SELECT 'ALTER TABLE ['+ [TABLE_NAME] + '] ADD CONSTRAINT [DF_' + [TABLE_NAME] + '_' + [COLUMN_NAME] +'] DEFAULT (0) FOR [' + [COLUMN_NAME] + '];'
FROM information_schema.columns
WHERE TABLE_CATALOG='pms'
and [TABLE_NAME] not like '%zDel%'
and [TABLE_NAME] not like '%aspnet%'
and [TABLE_NAME] not like '%__MigrationHistory%'
and [TABLE_NAME] not like 'View%'
and [TABLE_NAME] <> '%ELMAH_Error%'
and [TABLE_NAME] <> 'Sessions'
and [TABLE_NAME] <> 'sysdiagrams'
and IS_NULLABLE ='YES'
and COLUMN_DEFAULT is NULL
and ( DATA_TYPE='int')

union

SELECT 'ALTER TABLE ['+ [TABLE_NAME] + '] ADD CONSTRAINT [DF_' + [TABLE_NAME] + '_' + [COLUMN_NAME] +'] DEFAULT (1) FOR [' + [COLUMN_NAME] + '];'
FROM information_schema.columns
WHERE TABLE_CATALOG='pms'
and [TABLE_NAME] not like '%zDel%'
and [TABLE_NAME] not like '%aspnet%'
and [TABLE_NAME] not like '%__MigrationHistory%'
and [TABLE_NAME] not like 'View%'
and [TABLE_NAME] <> '%ELMAH_Error%'
and [TABLE_NAME] <> 'Sessions'
and [TABLE_NAME] <> 'sysdiagrams'
and IS_NULLABLE ='YES'
and COLUMN_DEFAULT is NULL
and ( DATA_TYPE='bit')
and ( COLUMN_NAME='IsValid')

union

SELECT 'ALTER TABLE ['+ [TABLE_NAME] + '] ADD CONSTRAINT [DF_' + [TABLE_NAME] + '_' + [COLUMN_NAME] +'] DEFAULT (0) FOR [' + [COLUMN_NAME] + '];'
FROM information_schema.columns
WHERE TABLE_CATALOG='pms'
and [TABLE_NAME] not like '%zDel%'
and [TABLE_NAME] not like '%aspnet%'
and [TABLE_NAME] not like '%__MigrationHistory%'
and [TABLE_NAME] not like 'View%'
and [TABLE_NAME] <> '%ELMAH_Error%'
and [TABLE_NAME] <> 'Sessions'
and [TABLE_NAME] <> 'sysdiagrams'
and IS_NULLABLE ='YES'
and COLUMN_DEFAULT is NULL
and ( DATA_TYPE='bit')
and ( COLUMN_NAME<>'IsValid')
;",
connection);
connection.Open();

SqlDataReader reader = command.ExecuteReader();
string str = "";
if (reader.HasRows)
{
while (reader.Read())
{

str = reader[0].ToString();
if (Literal1.Text == "")
{
Literal1.Text = "use pms;" + " GO ";
Literal1.Text = Literal1.Text + "delete InformationSystem where todoid is null;" + " GO "; // 修正 Bug
}
else
Literal1.Text = Literal1.Text + str + " GO ";
}
}
else
{
//Console.WriteLine("No rows found.");
}
reader.Close();
connection.Close();
}
}

protected void Button2_Click(object sender, EventArgs e)
{
Literal1.Text = "";
using (SqlConnection connection = new SqlConnection(
connectionString))
{
SqlCommand command = new SqlCommand(@"

-- 若 ntext, nvarchar 欄位允許 null ，先更新欄位內容
SELECT 'UPDATE ['+ [TABLE_NAME] + '] SET [' + [COLUMN_NAME] +']='''' WHERE [' + [COLUMN_NAME] + '] is NULL;'
FROM information_schema.columns
WHERE TABLE_CATALOG='pms'
and [TABLE_NAME] not like '%zDel%'
and [TABLE_NAME] not like '%aspnet%'
and [TABLE_NAME] not like '%__MigrationHistory%'
and [TABLE_NAME] not like 'View%'
and [TABLE_NAME] <> '%ELMAH_Error%'
and [TABLE_NAME] <> 'Sessions'
and [TABLE_NAME] <> 'sysdiagrams'
and IS_NULLABLE ='YES'
and ( DATA_TYPE='ntext' or DATA_TYPE='nvarchar' )
group by TABLE_NAME, COLUMN_NAME, DATA_TYPE, COLUMN_DEFAULT, IS_NULLABLE
--order by TABLE_NAME, COLUMN_NAME, DATA_TYPE, COLUMN_DEFAULT, IS_NULLABLE
union
-- 若 datetime, datetime2 欄位允許 null ，先更新欄位內容
SELECT 'UPDATE ['+ [TABLE_NAME] + '] SET [' + [COLUMN_NAME] +']=''0-1-1'' WHERE [' + [COLUMN_NAME] + '] is NULL;'
FROM information_schema.columns
WHERE TABLE_CATALOG='pms'
and [TABLE_NAME] not like '%zDel%'
and [TABLE_NAME] not like '%aspnet%'
and [TABLE_NAME] not like '%__MigrationHistory%'
and [TABLE_NAME] not like 'View%'
and [TABLE_NAME] <> '%ELMAH_Error%'
and [TABLE_NAME] <> 'Sessions'
and [TABLE_NAME] <> 'sysdiagrams'
and IS_NULLABLE ='YES'
and ( DATA_TYPE='datetime' or DATA_TYPE='datetime2' )
group by TABLE_NAME, COLUMN_NAME, DATA_TYPE, COLUMN_DEFAULT, IS_NULLABLE
--order by TABLE_NAME, COLUMN_NAME, DATA_TYPE, COLUMN_DEFAULT, IS_NULLABLE
union
-- 若 int 欄位允許 null ，先更新欄位內容
SELECT 'UPDATE ['+ [TABLE_NAME] + '] SET [' + [COLUMN_NAME] +']=''0'' WHERE [' + [COLUMN_NAME] + '] is NULL;'
FROM information_schema.columns
WHERE TABLE_CATALOG='pms'
and [TABLE_NAME] not like '%zDel%'
and [TABLE_NAME] not like '%aspnet%'
and [TABLE_NAME] not like '%__MigrationHistory%'
and [TABLE_NAME] not like 'View%'
and [TABLE_NAME] <> '%ELMAH_Error%'
and [TABLE_NAME] <> 'Sessions'
and [TABLE_NAME] <> 'sysdiagrams'
and IS_NULLABLE ='YES'
and ( DATA_TYPE='int')
group by TABLE_NAME, COLUMN_NAME, DATA_TYPE, COLUMN_DEFAULT, IS_NULLABLE
--order by TABLE_NAME, COLUMN_NAME, DATA_TYPE, COLUMN_DEFAULT, IS_NULLABLE

union
-- 若 bit 欄位允許 null ，且欄位為 IsValid，先更新欄位內容
SELECT 'UPDATE ['+ [TABLE_NAME] + '] SET [' + [COLUMN_NAME] +']=''1'' WHERE [' + [COLUMN_NAME] + '] is NULL;'
FROM information_schema.columns
WHERE TABLE_CATALOG='pms'
and [TABLE_NAME] not like '%zDel%'
and [TABLE_NAME] not like '%aspnet%'
and [TABLE_NAME] not like '%__MigrationHistory%'
and [TABLE_NAME] not like 'View%'
and [TABLE_NAME] <> '%ELMAH_Error%'
and [TABLE_NAME] <> 'Sessions'
and [TABLE_NAME] <> 'sysdiagrams'
and IS_NULLABLE ='YES'
and ( DATA_TYPE='bit')
and ( COLUMN_NAME='IsValid')
group by TABLE_NAME, COLUMN_NAME, DATA_TYPE, COLUMN_DEFAULT, IS_NULLABLE
--order by TABLE_NAME, COLUMN_NAME, DATA_TYPE, COLUMN_DEFAULT, IS_NULLABLE

union
-- 若 bit 欄位允許 null ，且欄位為 IsValid，先更新欄位內容
SELECT 'UPDATE ['+ [TABLE_NAME] + '] SET [' + [COLUMN_NAME] +']=''0'' WHERE [' + [COLUMN_NAME] + '] is NULL;'
FROM information_schema.columns
WHERE TABLE_CATALOG='pms'
and [TABLE_NAME] not like '%zDel%'
and [TABLE_NAME] not like '%aspnet%'
and [TABLE_NAME] not like '%__MigrationHistory%'
and [TABLE_NAME] not like 'View%'
and [TABLE_NAME] <> '%ELMAH_Error%'
and [TABLE_NAME] <> 'Sessions'
and [TABLE_NAME] <> 'sysdiagrams'
and IS_NULLABLE ='YES'
and ( DATA_TYPE='bit')
and ( COLUMN_NAME<>'IsValid')
group by TABLE_NAME, COLUMN_NAME, DATA_TYPE, COLUMN_DEFAULT, IS_NULLABLE
--order by TABLE_NAME, COLUMN_NAME, DATA_TYPE, COLUMN_DEFAULT, IS_NULLABLE
;",
connection);
connection.Open();

SqlDataReader reader = command.ExecuteReader();
string str = "";
if (reader.HasRows)
{
while (reader.Read())
{

str = reader[0].ToString();
if (Literal1.Text == "")
{
Literal1.Text = "use pms;" + " GO ";
}
else
Literal1.Text = Literal1.Text + str + " GO ";
}
}
else
{
//Console.WriteLine("No rows found.");
}
reader.Close();
connection.Close();
}
}

protected void Button3_Click(object sender, EventArgs e)
{
Literal1.Text = "";
Literal2.Text = "";
using (SqlConnection connection = new SqlConnection(
connectionString))
{
SqlCommand command = new SqlCommand(
@"select DISTINCT [TABLE_NAME] FROM information_schema.columns
WHERE TABLE_CATALOG = 'pms'

and[TABLE_NAME] not like '%zDel%'

and[TABLE_NAME] not like '%aspnet%'

and[TABLE_NAME] not like '%__MigrationHistory%'

and[TABLE_NAME] not like 'View%'

and[TABLE_NAME] <> '%ELMAH_Error%'

and[TABLE_NAME] <> 'Sessions'

and[TABLE_NAME] <> 'sysdiagrams'
; ",
connection);
connection.Open();

SqlDataReader reader = command.ExecuteReader();
string str = "";
if (reader.HasRows)
{
while (reader.Read())
{

str = @" select 'ALTER TABLE [" + reader[0].ToString() + @"] ALTER COLUMN ' +
QUOTENAME(c.name) + ' ' + t.name +
case when t.name in ('nvarchar','nchar') and c.max_length <>-1
then '('+ cast(c.max_length/2 as nvarchar) +')'
when t.name in ('nvarchar','nchar') and c.max_length =-1
then '(MAX)'
when t.name in ('varchar','binary', 'char', 'varbinary')
then '('+ cast(c.max_length as nvarchar) +')'
else '' end + ' NOT NULL; '
from sys.columns c join sys.types t on c.system_type_id = t.system_type_id
where object_id = object_id('dbo." + reader[0].ToString() + @"') and t.name <> 'sysname'
and t.is_nullable =1
";
//order by column_id";
if (Literal1.Text == "")
{
Literal1.Text = "use pms;" + " GO ";
Literal1.Text = "(" + str + ")";
Literal2.Text = "use pms;\nGO\n";
Literal2.Text = "(" + str + ")\n";
}
else
Literal1.Text = Literal1.Text + " union " + "(" + str + ")";
Literal2.Text = Literal2.Text + "\nunion\n" + "(" + str + ")\n";
}
}
else
{
//Console.WriteLine("No rows found.");
}
reader.Close();
}
//

Literal1.Text = "";
using (SqlConnection connection = new SqlConnection(
connectionString))
{
SqlCommand command = new SqlCommand(Literal2.Text,connection);
connection.Open();

SqlDataReader reader = command.ExecuteReader();
string str = "";
if (reader.HasRows)
{
while (reader.Read())
{

str = reader[0].ToString();
if (Literal1.Text == "")
{
Literal1.Text = "use pms;" + " GO ";
}
else
Literal1.Text = Literal1.Text + str + " GO ";
}
}
else
{
//Console.WriteLine("No rows found.");
}
reader.Close();
connection.Close();
}
}
}
}

(完)

相關

[研究] 用SQL指令找出資料庫的資料表、欄位名、PK欄位、資料筆數
http://shaurong.blogspot.tw/2016/02/sqlpk.html

[研究] SQL Server 2016 的 datetime 最早可填入的日期測試、日期範圍、日期區間

[研究] SQL Server 2016 的 datetime 最早可填入的日期測試、日期範圍、日期區間

2016-09-13

SQL Server Management Studio (SSMS) 中測試

USE [TestDB]
GO

INSERT INTO [dbo].[TestTable]
([myname]
,[addr]
,[createtime])
VALUES
('name1','addr1','1753-1-1');
GO

select * from TestTable;

0-1-1 插入後會變成 2000-01-01
1-1-1 插入後會變成 2001-01-01
1753-1-1 可以，1752-12-31 失敗

訊息 242，層級 16，狀態 3，行 4
將 varchar 資料類型轉換成 datetime 資料類型時，產生超出範圍的值。
陳述式已經結束。

********************************************************************************

後來發現有官方資料

日期和時間 (Transact-SQL)
https://msdn.microsoft.com/zh-tw/library/ms187819(v=sql.90).aspx
datetime日期範圍：1753 年 1 月 1 日到 9999 年 12 月 31 日
smalldatetime日期範圍：1900 年 1 月 1 日到 2079 年 6 月 6 日

datetime2 (Transact-SQL)
https://msdn.microsoft.com/zh-tw/library/bb677335(v=sql.120).aspx
日期範圍：0001-01-01 到 9999-12-31，西元 1 年 1 月 1 日到西元 9999 年 12 月 31 日

(完)

相關

DateTime.TryParseExact

https://learn.microsoft.com/zh-tw/dotnet/api/system.datetime.tryparseexact?view=net-7.0

Convert.ToDateTime 方法 (System) | Microsoft Learn

https://learn.microsoft.com/zh-tw/dotnet/api/system.convert.todatetime?view=net-7.0

2016年9月10日星期六

[研究] SQL Server 新舊資料庫 - 資料比較

[研究] SQL Server 新舊資料庫 - 資料比較

2016-09-10

使用工具：Visual Studio 2015 Enterprise with Update 3 (不是 SQL Server )

(免費 or 等級較低的 Express, Community, Professional 不保證有，沒研究)
(舊版 Visual Studio 2013, 2012, 2010 不保證有，沒研究)