四捨五入不進位??

人客反應一個四捨五入到小數二位的問題...
我沒什麼頭緒要怎麼解決, 但在不小心中發現一個方法,暫時可以解決四捨五入不進位的問題
這是sql server 的float bug嗎, 還是本來就這樣??

二組數字分別是1.055, 1.1243,型態是float
依四捨五入到小數第二位的話,  結果應該是  c1 =  1.06  c2= 1.12

但結果卻不如人意,而且只有當此float 刪好只有到小數第三位時,欲四捨五入進位到小數二位才會有問題

先reproduce一下狀況..

declare @tab table (c1 float,c2 float)
insert into @tab
select 1.055, 1.1243

select 
c1,c2
,cast(c1 as decimal(5,2)) as r1,cast(c2 as decimal(5,2)) as r2
,cast(c1 + 0.001 as decimal(5,2)) as t1,cast(c2 + 0.001 as decimal(5,2)) as t2
,cast(c1 + 0.0001 as decimal(5,2)) as s1,cast(c2 + 0.0001 as decimal(5,2)) as s2
 from @tab
但結果如下

c1	c2	r1	r2	t1	t2	s1	s2
1.055	1.124	1.05	1.12	1.06	1.13	1.06	1.12

有趣的是, 1.124 + 0.001 =  1.125 , 轉成decimal(5,2)後,又很精準的四捨五入了, 但結果是不對的....


所以再做下列測試
如果c1為1.0551 又或者將型態改成decimal(5,4)  則結果是正確的

declare @tab table (c1 float,c2 float)
insert into @tab
select 1.0551, 1.124

select  
c1,c2
,cast(c1 as decimal(5,2)) as r1,cast(c2 as decimal(5,2)) as r2
,cast(c1 + 0.001 as decimal(5,2)) as t1,cast(c2 + 0.001 as decimal(5,2)) as t2
,cast(c1 + 0.0001 as decimal(5,2)) as s1,cast(c2 + 0.0001 as decimal(5,2)) as s2
 from @tab

c1	c2	r1	r2	t1	t2	s1	s2
1.0551	1.124	1.06	1.12	1.06	1.13	1.06	1.12

declare @tab table (c1 decimal(5,4),c2 decimal(5,4))
insert into @tab
select 1.055, 1.124

select  
c1,c2
,cast(c1 as decimal(5,2)) as r1,cast(c2 as decimal(5,2)) as r2
,cast(c1 + 0.001 as decimal(5,2)) as t1,cast(c2 + 0.001 as decimal(5,2)) as t2
,cast(c1 + 0.0001 as decimal(5,2)) as s1,cast(c2 + 0.0001 as decimal(5,2)) as s2
 from @tab

c1	c2	r1	r2	t1	t2	s1	s2
1.055	1.124	1.06	1.12	1.06	1.13	1.06	1.12

將小數點變成四位後,四捨五入到小數二位就對了

再來一個測試

declare @tab table  (c1 float,c2 float)
 insert into @tab
select 1.055, 1.124

select  
c1,c2
,cast(c1+0.000 as decimal(5,2)) as r1,cast(c2 as decimal(5,2)) as r2
,cast(c1 + 0.001 as decimal(5,2)) as t1,cast(c2 + 0.001 as decimal(5,2)) as t2
,cast(c1 + 0.0001 as decimal(5,2)) as s1,cast(c2 + 0.0001 as decimal(5,2)) as s2
 from @tab

c1	c2	r1	r2	t1	t2	s1	s2
1.055	1.124	1.05	1.12	1.06	1.13	1.06	1.12

加上0.000是沒有用的...

結論:

1.float使用真的要小心謹慎,不是那麼精準

2.四捨五入到小數二位, 則先加0.0001變成有小數四位後,再進行四捨五入

大家有看懂嗎???

Informix執行計劃

執行SQL前,先執行下列
SET EXPLAIN ON  , 開啟執行計劃, 要整個跑完後才能看
SET EXPLAIN OFF, 關閉執行計劃
SET EXPLAIN ON AVOID_EXECUTE, 開始執行計劃,但只評估不執行

所以查看SQL 用到index的狀況,可選擇第3種方式
詳細使用可查看informix online help

完成後會輸出到 sqexplain.out
vi 查看內容如下

QUERY:
------
select * from test
Estimated Cost: 453
Estimated # of Rows Returned: 8835
  1) xxxx.test: SEQUENTIAL SCAN

QUERY:
------
select * from ss_code
Estimated Cost: 453
Estimated # of Rows Returned: 8835
  1) xxxx.test: SEQUENTIAL SCAN
~
~
"sqexplain.out" 21 lines, 263 characters

Could not load file or assembly 'ReportingServicesNativeClient

在Windows 2008 R2上安裝 SQL Server 2005 64位元
Reporting Server 網站進入時出現下列錯誤
Could not load file or assembly 'ReportingServicesNativeClient, Version=0.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies. An attempt was made to load a program with an incorrect format.
找到討論回文,照做後,問題解決轉貼abhijitc的回文
 The problem is not SSRS, the problem is asp runing on 32-64 bits. to enable the SSRS follow these steps:
1. To run the 64-bit version of ASP.NET 2.0, Click Start, click Run, type cmd, and then click OK.

2. Type the following command to disable the 32-bit mode:
cscript %SYSTEMDRIVE%\inetpub\adminscripts\adsutil.vbs SET W3SVC/AppPools/Enable32bitAppOnWin64 0
3. Type the following command to install the version of ASP.NET 2.0 and to install the script maps at the IIS root and under:
%SYSTEMROOT%\Microsoft.NET\Framework64\v2.0.50727\aspnet_regiis.exe -i
4. Make sure that the status of ASP.NET version 2.0.50727 is set to Allowed in the Web service extension list in Internet Information Services Manager.

5. Check the SSRS configuration

Note The build version of ASP.NET 2.0 may differ depending on what the currently released build version is. These steps are for build version 2.0.50727.

view this link : http://support.microsoft.com/kb/894435/en-us

無法安裝silverlgiht tool 4

vs 2010安裝 silverlight tool 4 無法安裝, 出現

Visual Studio 2010 or Visual Web Developer Express 2010 or Visual Phone Developer Express 2010 that matches the language version of Silverlight Tools 4 must be installed before installation of Silverlight Tools can continue. Silverlight Tools is available in other languages at http://go.microsoft.com/fwlink/?LinkId=177432. 

試了很多方法..還是無法安裝, 最後參考這篇文章, 先移除vs 2010 SP1後, 就可以安裝tool了.

裝完後再重裝vs2010 SP1

powerdesigner 15 產生SQL指令

設計好PDM,接下來就要在DB上把物件都建立起來.

到  Database >> Generate Database  產生create 語法檔

結果出錯了,原來產生出來的create table  syntax中, 欄位的data type都變成了domain name 了

到 Database >> Edit Current DMBS >> Script >> Objects >> Domain >> Enable , 將Value點選為No即可

  

但問題又來了, 明明是int和smallint的欄位, 怎麼create table 中變成後加collate ??

/*==============================================================*/
/* Table: BatchLog                                              */
/*==============================================================*/
create table dbo.BatchLog (
   BatchLogID             int                  identity(1, 1),
   StartDateTime        datetime             not null,
   EndDateTime          datetime             null,
   Status                      smallint             collate Chinese_Taiwan_Stroke_CI_AS  not null,
   ProjectCode          nvarchar(256)        collate Chinese_Taiwan_Stroke_CI_AS null,
   StartDate               int                  collate Chinese_Taiwan_Stroke_CI_AS null,
   EndDate                int                  collate Chinese_Taiwan_Stroke_CI_AS null,
   constraint PK_BatchLog primary key (BatchLogID)
         on "PRIMARY"
)
on "PRIMARY"
go

有夠奇怪啦...找了半天, 才發現原來剛開始這些table是由現存DB反向轉成PDM的

轉入時,字串欄位在Microsoft 頁籤,都會將Collation name一起轉入

後來當把某些字串欄位分別改成了smallint及int型態時, PD15並未將其Collation name 給清空, 所以在generate script時,就變成了上面那樣了.

最後一個個欄位調整, 重建一下script就OK了.

所以如果有指定collation時,當改變資料型態時要記得自已去刪掉collation 定義.

VS2011 Pluralization of name

表格名稱若帶有s字母結尾, 使用Linq to SQL,建立.dbml時

記得先做下列設定..

Tools >> Options  >> Database Tools >> O/R Designer >> Pluralization  of name

Enabled 設成 False

不然VS2010會亂亂命名class , 有s的亂改成沒s, 沒有s的自已亂改成有s,


Pluralization  of name = 名稱的複數表示

我咧傻傻搞不清

IIS 7無法上傳超過30MB 檔案

網站由windows 2003換到windows 2008後, 上傳大旳檔案時會出現404 not found的錯

web.config httpruntim 有設定maxlength, 但仍無法上傳, google 到此篇

以下是更改設定指令.

REM切換到appcmd目錄

cd c:\Windows\systems32\inetsrv

REM 先備份IIS設定
appcmd add backup test20110718

REM 查看IIS備份紀錄是否存在
appcmd list backup

REM 先查主機的SiteName及APP list
appcmd list app

REM 查看目前上傳設定,找關鍵字requestLimits
appcmd list config "Default Web Site/mySite" -section:requestFiltering

REM 更改size 單位為byte ,以下為100MB左右

appcmd set config "Default Web Site/mySite" -section:requestFiltering -requestLimits.maxAllowedContentLength:102400000 -commitpath:apphost