openrowset 卡住問題

對openrowset又愛又恨.........

愛的是讀外部資料檔很方便...恨的是不預期的卡住永世不得翻身....

終於找到一個可以解決卡住的方法...但為什麼會不預期卡住仍待努力。

參考這篇

SQL Server 2016 Linked Server OPENQUERY Hangs

 去https://docs.microsoft.com/en-us/sysinternals/downloads/sysinternals-suite

下載Sysinternals Suite，裡面真的好物...

找到Process Explorer(procexp64.exe)工具打開後，刪除dllhost.exe 是OLE DB Core Services的，且CPU狀態是Suspend的(但有遇到狀態沒有Suspend也是卡住的狀況)。

之前為了測試如何刪除openrowset session，已將連結的伺服器linked server中的microsoft.ACE.OLEDB.16.0 的允許InProcess停用，所以不確定是否得要這麼設才有dllhost.exe可刪。




借用截圖




 

2022/10/02 今天測試了一會，由事件檢視器訊息來看，

失敗的應用程式名稱: DllHost.exe，版本: 10.0.17763.1，時間戳記: 0x5d3b6f40
失敗的模組名稱: mso20win32client.dll，版本: 16.0.5254.1001，時間戳記: 0x61aca05e
例外狀況代碼: 0xc0000005
錯誤位移: 0x00000000000c57f2
失敗的處理程序識別碼: 0x920c
失敗的應用程式開始時間: 0x01d8d5b4a1a5e9b1
失敗的應用程式路徑: C:\Windows\system32\DllHost.exe
失敗的模組路徑: C:\Program Files\Common Files\Microsoft Shared\Office16\mso20win32client.dll
報告識別碼: a950796e-1eb0-49b8-b76b-4145e6c8b4c9
失敗的套件完整名稱:
失敗的套件相關應用程式識別碼: 

今天剛好測到一次讀三個外部檔，終於遇到了客戶常發生的狀況，openrowset的查詢又hang住了。

反覆測了好幾回，分別在WEB AP 及SSMS環境測試，發現在SSMS成功機率較高，在AP沒有一次查詢成功? 同樣的SQL在連線的DB，唯一的差別就是執行環境了。

瞎測一番後，好像在每個openrowset查詢間加了waitfor delay '00:00:10' 停個10秒再執行下一段openrowset，結果執行了幾次，竟然都可正常回傳結果。

怪哉~~~~~ 

2022/11/24 

這個問題看來無解 

https://blog.sqlauthority.com/2017/03/08/sql-server-queries-killedrollback-state-wait-preemptive_oledb_release/


唯一能做的是重啟SQL SERVER服務釋放SPID。

所以設成 InProcess停用後，就直接執行exec xp_cmdshell 'taskkill /f /im dllhost.exe'

把卡住的dllhosrt kill掉，通常再執行一次openrowset又可正常回傳資料了。


 

四捨五入不進位??

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

大家有看懂嗎???

Microsoft.ACE.OLEDB.12.0 讀excel 2010 64bit 儲存格格式

安裝了office 2010 64 bit後, 想要在 sql2008 64bit 測看看 用openrowset讀取excel的狀況

先建了一個excel檔 test.xlsx

里頭只新增了一行資料,共三個欄位, 如下圖

其中, 欄位C 實際存放的數值是0, 透過儲存格格式的自訂,將之轉換為 - 顯示

測試的語法如下

select * from
OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Format=xlsx;HDR=NO;IMEX=1;Database=C:\test.xlsx','SELECT * FROM [工作表1$] ')

結果回傳如下

 

第三個欄位應該要回傳0才對,

切回SQL 2005 32 bit, 用Microsoft.Jet.OLEDB.4.0測試

select * from
OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Format=xls;HDR=NO;IMEX=1;Database=C:\test.xls','SELECT * FROM [工作表1$] ')   

結果回傳如下

回傳結果第三欄是0無誤

看來 Microsoft.ACE.OLEDB.12.0讀到格式後的值
看來是driver的限制, 因為在32bit環境上使用Microsoft.ACE.OLEDB.12.0仍有此問題

http 方式連結 analysis server 之再測試

上次可以用網頁owc方式採http方式連結analysis server後.

現在人客說, 想要依不同的登入使用過濾OWC FILTER AREA中的機關list,

真是天殺的需求.

爬了許多文後.....整理如下,有二種做法

第一種, 透過網頁OWC 的屬性設定
1.先到MS KB上去COPY範例
2.在window_onload事件中,找到這一行, aStates = Array("Cleared", "Checked", "Gray"), 把他給刪掉
3.然後加上這三行
   oView.FilterAxis.FieldSets("Product").AllIncludeExclude = c.plAllExclude
   oView.FilterAxis.Fieldsets("Product").fields("Product Family").includedmembers = Split("Food",",")
   PTable.AllowFiltering = false
4.測試網頁, 進入後,你會看到在filter area的product 維度,只有Food被勾起來了,而且,你再也不能對這個維度做任何的修改, 因為 PTable.AllowFiltering = false

這個方法對人客不太實用, 因為會連同其他放在filter area的維度也跟著無法修改了.... 沒屁用,但測出了如何coding filter memers

第二種, 利用as本身的角色授權功能
1.先在http olap虛擬目錄的認證方式改成 基本驗證,
2.在網頁owc 連結as的connection string , 要加入使用者及密碼,  如下
"Data Source=http://SQL2000/OLAP/;Initial Catalog=TEST;Provider=MSOLAP.2;User Id=normaluser;Password=123456;"
按照這篇文章Basic authentication 那段的說明 , 必需使用基本驗證 加上connection string 中的使用者及密碼,才能透過http方式模擬連線使用者.
所以, 既然是走明碼傳輸, 文章也建議你要採用SSL方式,加密傳輸.
3.依需求, 在as中去定義不同的角色, 把維度資料的瀏覽做權限控管
4.最後,想辦法在網頁上動態依登入的使用者去替換connection string 中的使用者及密碼

這個方法人客應該比較尬意, 但測試後, 在AS 2000 OK,可以隨著使用者的切換顯示維度資料
但AS 2005 的角色雖然設定了.(方法和AS2000大同小異)...但網頁卻沒有隨著使用者切換而顯示維度資料

比較二者的差異
1.AS 2000那台主機不在AD中, AS2005那台主機則在AD Domain中
2.網頁測試機本身雖不在AD網域中,但其登入本機的帳號及密碼與AD上一組帳號及密碼相同 所以OWC 連線可以正常, 也可以順利看到預設的維度.

3.但在其他台測試機器開相同網頁,則出現以下錯誤
---------------------------
Microsoft Office 樞紐分析表清單
---------------------------
發生錯誤 0x80004005。沒有進一步的資訊。

JS錯誤內容為 " ActiveX元件,無法產生物件.."

4.相同的測試網頁, 放在windows 7 + IE8 的環境下,且符合上述2的環境下時, oView.FilterAxis.Fieldsets("Product").fields("Product Family").includedmembers = Split("Food",",")
這一段程式碼, 竟然出現, JS錯誤訊息:" 陣列索引超出範圍"

原來, IE8 + SSAS2005  ,  Split有問題哩.. 改成以下 就OK囉..
oView.FilterAxis.Fieldsets("Product").fields("Product Family").includedmembers = Array ( "Food", "Drink" )

然後, 整個OWC的速度超級無敵慢的.....為什麼連到SA2000的CUBE就很快呢....

亂亂亂.....

在網域環境中 , SSAS 2005 的權限設定 , OWC的顯示,  這款代誌金不係呆人想得卡哩啊簡單啦.....


待續

SSRS 造字匯出pdf

報表內容含造字(EUDC,End-User Defined Characters), 匯出EXCEL都沒問題
唯獨匯出pdf不成功

試了RS designer 的三個版本
RS 2000 designer ,造字preview 與匯出EXCEL正常,匯出PDFOK , 但造字變成 !
RS2005 designer , 造字preview 與匯出EXCEL正常,匯出PDFOK, 但造字變成口
RS2008 designer , 造字preview 變成口, 但匯出EXCEL造字顯示正常,
匯出PDF時, 出現以下訊息
 ---------------------------
Export Error
---------------------------
An error occurred during local report processing.
CreateFontPackage failed: Win32 error:1006

也試用了ASPOSE的 Aspose.Pdf for Reporting Services, 造字仍無法顯示

最後試了, VS2010 report viewer. 造字preview 與匯出EXCEL與WORD正常,匯出PDFOK, 但造字變成口
 
OO, 目前看來是無解

Excel openrowset 奇怪的問題(續)

人客家的SSIS,一段時間後都會出現以下的錯誤.
剛開始一頭霧水,以為是上傳的EXCEL檔有問題,
後來發現,原本在SSMS使用正常的excel openrowset, 在同時間也無法使用.

檢查了EXCEL檔案內容,檢查了openrowset的語法, 都沒更動過也沒寫錯...怪哉.


所有的錯誤都出現在Data Flow Component 的OLEDB Source, 里頭放的是一段excel openrowset的語法:

SSIS 錯誤碼 DTS_E_OLEDBERROR。發生 OLE DB 錯誤。錯誤碼: 0x80040E14。
有 OLE DB 記錄可用。來源: "Microsoft SQL Native Client" Hresult: 0x80040E14 描述: "無法初始化連結伺服器 "(null)" 的 OLE DB 提供者 "Microsoft.Jet.OLEDB.4.0" 的資料來源物件。"。
有 OLE DB 記錄可用。來源: "Microsoft SQL Native Client" Hresult: 0x80040E14 描述: "連結伺服器 "(null)" 的 OLE DB 提供者 "Microsoft.Jet.OLEDB.4.0" 報告了錯誤。提供者並未給予任何關於錯誤的資訊。"。

"元件 "Excel OpenRowset" (1230)" 驗證失敗，傳回驗證狀態 "VS_ISBROKEN"。

一或多個元件驗證失敗

工作驗證期間發生錯誤。

載入封裝檔案 "D:\SSIS\subImport.dtsx" 時發生錯誤 0xC0012050。ExecutePackage 工作發生封裝驗證失敗。無法執行封裝。


最後,什麼都沒做,重新啟動SQL Server Services, 又回復正常.

這次,我依照之前遇到的怪問題的解法方法,
在機碼
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\Providers\Microsoft.Jet.OLEDB.4.0 下新增一個名稱
DisallowAdHocAccess = 0

重新啟動SQL Server ,再觀察一段時間看看吧

route table ?

在人客家做工, 上網處處受限
有了iPhone, 路是人走出來的

想要指定IE上內網做事時要走人客家的路
出去外面花花世界逛逛時要走iPhone 3GS

同事指導教學..
首先, 先將原先內網中,人客家的GATEWAY移掉
接著, 利用ROUTE TABLE 指定哪些IP是要以內網要進出

在DOS COMMAND下, 加這麼一段指令
route add 172.1.0.0 mask 255.255.0.0 172.1.33.254
紅色是以172開頭的IP都走內網
綠色是範圍
藍色是目前所在環境的gateway, 如果有多個就要一一設置

如果不知所在GATEWAY, 先利用tracert去試看看,
例如, tracert http://www.facebook.com/,  然後就會一一將IP顯示出來....

用了幾天, 也不太確定OK不OK,因為時好時壞...
有什麼簡易方法可以讓IE和FIREFOX走不同的連線嗎?

不支援關鍵字: 'provider'

想要在SSIS中呼叫自訂的DLL,其中需要動態傳入connection string,以方便在不同的環境中使用

於是在script task中,利用讀取dtsConfig中的connectionString,再傳遞給自訂的DLL method

執行時,在script task出現了一段錯誤

 System.ArgumentException: 不支援關鍵字: 'provider'。  
 於 System.Data.Common.DbConnectionOptions.ParseInternal(Hashtable parsetable, String connectionString, Boolean buildChain, Hashtable
 synonyms, Boolean firstKey)  
 於 System.Data.Common.DbConnectionOptions..ctor(String connectionString, Hashtable synonyms, Boolean useOdbcRules)  
 於 System.Data.SqlClient.SqlConnectionString..ctor(String connectionString)   
 於 System.Data.SqlClient.SqlConnectionFactory.CreateConnectionOptions(String connectionString, DbConnectionOptions previous)   
 於 System.Data.ProviderBase.DbConnectionFactory.GetConnectionPoolGroup(String connectionString, DbConnectionPoolGroupOptions poolOptions, DbConnectionOptions& userConnectionOptions)   
 於 System.Data.SqlClient.SqlConnection.ConnectionString_Set(String value)  
 於 System.Data.SqlClient.SqlConnection.set_ConnectionString(String value)  
 於 System.Data.Linq.SqlClient.SqlProvider.System.Data.Linq.Provider.IProvider.Initialize(IDataServices dataServices, Object connection)   
 於 System.Data.Linq.DataContext.Init(Object connection, MappingSource mapping)   
 於 System.Data.Linq.DataContext..ctor(String fileOrServerOrConnection, MappingSource mapping)   

於是乎,將dtsConfig中connectionstring的provider移除, 接著又出現了以下的錯誤

"ConnectionString 中未指定 OLE DB 提供者。正確範例為 Provider=SQLOLEDB；。"

 於是乎,又將dtsConfig中connectionstring的Provider改成 Provider=SQLOLEDB

怎麼著..., 又出現剛剛的 不支援關鍵字: 'provider'。  的錯誤...

也過給多種不同的連線字串,也爬了很久很久的文, 攏無法度解決....

唉, 同樣的連線字串,在不同的地方使用, 有的OK, 有的出搥... 

最後, 試了半天,只好先在自訂的DLL,把接收過來的連線字串中,其中Provider的關鍵字後的字串全移除掉

所以原本傳入

Data Source=ttt;User ID=sa;Password=1234;Initial Catalog=test;Provider=SQLNCLI.1;Persist Security Info=True;

取代後就變成

 Data Source=ttt;User ID=sa;Password=1234;Initial Catalog=test;

然後,我的SSIS就可以正常執行了.

方法不好,只能解燃眉之急, 還得要Provider=SQLNCLI.1;Persist Security Info=True;這段寫在最後面才行

後記.....

同事在問, 你很奇怪耶, 既然要移掉Provider, 那何不在傳入時就不要給不就行了??


我也很想啊, 就是不行啊,一直出現
 "ConnectionString 中未指定 OLE DB 提供者。正確範例為 Provider=SQLOLEDB；。"

到底connectionString 要怎麼給呢?

Excel application in Windows 7

把WINDOWS在2003的網站轉移到WINDOWS 7 IIS7 上

被WINDOWS 7 的IIS設定搞得七葷八素的,
UI界面完全不一樣,功能拆得很細項, 完全無法在一時間用正常的邏輯來猜想下一步應該要怎麼設定
試了二個多小時,終於把網站轉移成功.....好辛苦, 是我年紀大到這般程度了嗎?

逐一測試每個頁面,試到一個含有動態產生EXCEL檔案的功能, 出現了以下的錯誤,
幸好IE8的JAVASCRIPT錯誤已經可以COPY PASTE...

網頁錯誤詳細資料


使用者代理程式: Mozilla/4.0 (compatible; MSIE 8.0; Windows NT 6.1; WOW64; Trident/4.0; SLCC2; .NET CLR 2.0.50727; .NET CLR 3.5.30729; .NET CLR 3.0.30729; Media Center PC 6.0; OfficeLiveConnector.1.4; OfficeLivePatch.1.3; MALC)


時間戳記: Sun, 31 Jan 2010 08:19:49 UTC 


訊息: Sys.WebForms.PageRequestManagerServerErrorException: 由於發生下列錯誤，為具有 CLSID {00024500-0000-0000-C000-000000000046} 的元件擷取 COM Class Factory 失敗: 80070005。

想起了以前WINDOWS 2003時曾遇見過相同錯誤,要在元件服務中去更改DCOM  的network service帳號的存取權限.(可以參考這篇討論區的做法...)

到了windows 7的元件服務看了半天,不知是不是因為只安裝office2007的關係,找了半天,根本看不到microsof excel 的dcom元件

最後在茫茫谷海中,看到要在web.config 的加入 identity impersonate="true" username="" password=""  利用模擬身份方式去授權.而且要用具ADMIN權限的帳號...

雖然解決了,但不知原來設定DCOM權限的方法有沒解呢? 而且這樣模擬的身份會不會太大了?
測試時,順便觀察到,原本在舊的環境是IIS6+OFFICE2003+OFFICE2007共存, 用DSOFRAMER開啟excel時, 工作管理員會有一個excel的process, 不管你開多少個excel, 似乎都只有一個excel

在windows 7 64位元下, 用dsoframer開excel時,只看到officelivesign.exe,看不到excel,
但直接開啟office excel 2007 , 卻又可看到excel的process

見鬼了,今天測試又不是這麼一回事了, 先開EXCEL再開DSOFRAMER,excel process又是只有一個了...,而且畫面也切不回原來開啟的EXCEL..

一頭霧水, windows 7 變好多, 一時間真是無法掌握...

目前唯一讓我感到波棒好的是windows 7 64位元 加上光世代來上網, 那速度真的快到只是一眨眼間呢.
facebook上的animal paradise里頭的動物, 似乎每隻小動物都是在快走中...
差很大

Request.ServerVariables["REMOTE_HOST"] 無法回傳機器名稱

在客戶的環境中, 有一段取client機器名稱的程式碼
Request.ServerVariables["REMOTE_HOST"] 一直無法取得機器名稱,只回傳ip address
但在自已的開發環境卻ok

看到這篇KB 297795

1.打開DOS命令提示字元
2. cd C:\Inetpub\Adminscripts.
3. set w3svc/EnableReverseDNS TRUE

設置了IIS 的EnableReverseDNS 後, 就OK了

KB里頭提到
Warnings
Enabling reverse DNS on your IIS server can affect the performance of your Web server and DNS servers. Some examples are:
Resources such as CPU utilization and network bandwidth may be taken up.
Client requests can take longer to process.
Client requests can be blocked if IP restrictions by DNS domain names are used.

這些警告.....待觀察啊

rdlc matrix overlap

通常習慣先用rdl design layuout測試sql,preview後沒問題再把rdl rename成rdlc,然後再放在asp.net進行程式碼撰寫

這幾天用到martix做了一張表,因為有複雜的邏輯,試了幾天好不容易試出來,用了二個martix做重疊再加上寫一些iif的判斷,最後preview報表數字也都很ok.

結果用rdlc 試跑程式後,發現有一行數字差很大, 而且二個martix overlap在rdl 很ok,但rdl 卻變成不無法重疊, 折成二塊了.

檢查了半天,看不出rdl和rdlc的差異在哪? 都是一樣的sql 啊???

無解ing......

並未將物件參考設定為物件的執行個體

vs2008+sp1, rebuild solution時會有這個error, 但沒有指出是哪一個page或物件,
再rebuild幾次,然後又ok
搞不清到底是哪兒發生問題
直接run web site又沒問題, 只有發生在build site的時候

也不是這位仁兄說的問題
"後來發現是 Solution 的 Startup Page 重新設一下就行了"-----> 沒用沒用

開始發生這個奇怪問題時,是把web site solution搬到另一個儲存路徑.

why????

後記....
半年後的一天,同事終於煩了,於是一個一個目錄加入想要測看看到底是什麼原因造成的
結果他說是因為在某個目錄有加入rdlc的原因,如果把rdlc檔案移除掉,compiler就不會出現問題了...
我在想,是不是因為在設計rdlc時,我通常都是先在SSRS 的report designer先做一個rdl,測試沒什麼問題後,再rename rdl to rdlc,然後再加入到vs的專案?

如果沒在vs專案修改過rdlc檔, compile似乎也沒問題.

another why????

.net 用css 將gridview 的表頭釘住

定CSS語法：

.FixHeaderSyle
{
background-position: top;
background-repeat: repeat-x;
background-color: #d1dbe0;
position:relative ;
top:expression(this.offsetParent.scrollTop -1);
z-index: 10;
}

.PnlGrid
{
position: relative;
z-index: 1;
top: 0px;
}

ASP.NET 中, GridView要放在 Panel中

Panel ScrollBars="Both" CSS的Style="pnlGrid"

GridView的HeaderSytle設定："FixHeaderStyle "

不過,scroll時, 表頭那一塊沒法貼齊panel,留下一點空隙