PL/SQL Developer 無法登入

PL/SQL Developer類似SSMS 的功能, 連結到oracel db GUI工具

今天安裝了Oracle 10.2 client後 net manager 設定及測試連結都OK

但安裝了PL/SQL Developer 7.0.2.1076 後,卻無法連線,出現以下錯誤

Initialization error 
Could not locate OCI dll

OracleHomeKey: SOFTWARE\ORACLE\KEY_OraClient10g_home1
OracleHomeDir: E:\oracle\product\10.2.0\client_1

參考此篇文章 Could not locate OCI dll Error 

將 E:\oracle\product\10.2.0\client_1\ 下的所有單一檔案 (不包含目錄)複製並覆蓋到 E:\oracle\product\10.2.0\client_1\bin\ 下即可

SQL 2008 R2 Drillthrough slow

環境:
windows 7 enterprise  with sp1 + SQL Server 2008 R2 standard 10.50.2500
or
windows 2008 R2 enterpeise with sp1 + SQL Server 2008 R2 enterprise 10.50.2500
 
SSAS Dimension 25個, 若單人多次或多人一次進行dirllthrough時,此時記憶體使用量急上升,直到用滿了為止,接著就出現以下的錯誤,

同事反覆測試後的結論是, 在10.50.1600時不會有問題,記憶體使用量也不會暴增

TITLE: Microsoft SQL Server Management Studio

------------------------------

The selected action cannot be completed because of the following error.

伺服器: 已因記憶體不足的壓力而取消作業。

伺服器: 已因記憶體不足的壓力而取消作業。

This error may have occurred because the definition for the action is not valid. Verify the definition using the Actions view.

------------------------------

ADDITIONAL INFORMATION:

伺服器: 已因記憶體不足的壓力而取消作業。

伺服器: 已因記憶體不足的壓力而取消作業。 (Microsoft SQL Server 2008 R2 Analysis Services)

反應給MS,最後獲得的回答是在CU6之後會有此問題, 要等SQL Server 2012才會修正. 

目前可提供了一個解決方法

1、 開啟下列的檔案：

C:\Program Files\Microsoft SQL Server\MSAS10_50.Instance Name\OLAP\Config\ msmdsrv.ini

2、 修改下列的值

原本的值：

<SpaceDecomposition>8</SpaceDecomposition>

修改後的值：

<SpaceDecomposition>11</SpaceDecomposition>

3、 重新啟動 SQL Server Analysis Services

測試後,解決了問題. 恭喜大家,歷經一個月的溝通與測試終於解決了...

 

 2022/08/26 後記...

在SSAS 2019上，遇到drillthrough 很慢很慢的問題，同樣地調整這個參數後，健步如飛啊。

四捨五入不進位??

人客反應一個四捨五入到小數二位的問題...
我沒什麼頭緒要怎麼解決, 但在不小心中發現一個方法,暫時可以解決四捨五入不進位的問題
這是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 定義.