Browse by Tags

對SQL XML節點進行JOIN查詢

今天遇到的隨堂測驗，考題如下: SQL Server有一資料表，其中Records欄為XML型別，其中包含多筆記錄，目標要將記錄展開成多筆查詢結果: .xmljoin-tab td { padding: 3px; } 001 Jeffrey 2014-01-01 9999 001 Jeffrey 2014-04-01 32767 002 Darkthread 2014-06-10 65535 要實現以上需求，關鍵在於兩項SQL功能: SQL XML nodes() 將XML型別轉成關聯式資料 CROSS...

Posted 10 June 2014 10:21 PM 由 Jeffrey | with no comments 9,202

Filed under: SQL 2005, SQL 2008

使用陣列參數執行SQL WHERE IN比對

傳入字串或數字陣列當作篩選參數是很常見的SQL查詢情境，例如: 使用者在UI勾選取10項類別代碼，希望從Products資料表找出這10類的所有產品，轉換成SQL語法，相當於SELECT * FROM Products WHERE CategoryId IN (1,3,8,...,215)。 遇到這類需求，好傻好天真的開發者不小心會寫成恐怖的 SQL Injection自殺式查詢 : string sql = "SELECT * FROM Products WHERE CategoryId...

Posted 08 May 2014 10:01 PM 由 Jeffrey | with no comments 37,889

Filed under: .NET, SQL 2008

【茶包射手日記】只涉及單一資料表的Deadlock

在我原本狹隘的SQL知識裡，Deadlock發生情境需要兩個Process A與B跟兩個Table X與Y搭配演出: A鎖定住X想更新Y，B鎖定Y等著要更新X，產生無解的僵持，再由SQL二者擇一選為犠牲者，令其失敗來成立另一個Process。 直到最近處理一起Deadlock案例，才又長了見識。一個處理流水序號的Stored Procedure，讀取與更新對象只限同一Table，並不構成井底之蛙心中的Deadlock成立要件: 兩個鎖定對象、相反的讀取/更新順序，但Deadlock卻硬生生地發生了...

Posted 20 December 2012 03:40 PM 由 Jeffrey | 14 comment(s) 36,995

Filed under: SQL 2005, .NET, SQL 2000, SQL 2008

SQL Server x64建立Oracle Linked Server筆記

過去已有很多在SQL 2005/SQL 2008建立Oracle Linked Server的經驗，本屬稀鬆平常小事，今天第一次在SQL 2008 R2 x64執行，發現有些眉角，特筆記之。 需安裝64位元版Oracle Provider for OLE DB 明明機器每天在跑.NET程式連Oracle，卻發現Linked Server的Provider中沒有Oracle Provider for OLE DB可選! 這才想到，原本只裝了32位元版ODAC 11.2，.NET程式也都切成x86執行...

Posted 26 October 2012 11:23 PM 由 Jeffrey | 4 comment(s) 33,496

Filed under: ORACLE, SQL 2008

筆記-在SQL查詢中計算所佔百分比

最近遇到的小需求，要計算資料表中某一數字欄位所佔百分比，例如: ItemId GroupId CostAmt ------ ------- -------- X1000 A 50 X1001 A 250 X1002 A 200 X1003 B 200 X1004 B 300 目標是要由以上資料算出X1000佔全部成本5%，X1001佔25%... 等百分比資訊。另外則還要再進一步分組，算出各ItemId佔該組總成本的百分比，例如: X1000佔A群組成本總和的10%，X1001佔50%，X1002佔40...

Posted 13 February 2012 10:32 PM 由 Jeffrey | 5 comment(s) 25,877

Filed under: ORACLE, SQL 2005, SQL 2000, SQL 2008

CODE-將SQL Server SP/UDF存成檔案

想把SQL Server上的Stored Procedure/User-Defined Function的內容存成檔案備存。 利用 INFORMATION_SCHEMA.ROUTINES檢視 查詢，就可以調出所有的SP/UDF資訊，唯一要留意的地方是ROUTINES檢視用來存放SP/UDF的ROUTINE_DEFINITION欄位有NVARCHAR(4000)的限制，所以改用 OBJECT_DEFINTION 傳回NVARCHAR(MAX)，才能避免部分又臭又長的SP/UDF被腰斬。 static...

Posted 30 June 2011 05:59 AM 由 Jeffrey | 3 comment(s) 9,426

Filed under: SQL 2008, CODE

【茶包射手日記】SqlDataReader讀取FOR XML結果發生字元截斷

一段使用很久的程式今天忽然出錯，追查原因，是一段古早寫的 FOR XML 查詢出了問題。 程式用SqlCommand執行"SELECT ... FROM ... FOR XML RAW('Boo’)”，最近因資料異動，結果傳回超長的字串，透過SqlDataReader讀取時，只得到最前端的2,033個字元，XML內容被不正常截斷。 微軟有一篇KB310378 The XML data row is truncated at 2,033 characters when you use...

Posted 11 June 2011 07:46 AM 由 Jeffrey | with no comments 9,111

Filed under: SQL 2005, .NET, Trouble-Shooting, SQL 2000, SQL 2008

SSMS Intellisense強化工具-dbForge SQL Complete

在查SSMS的Intellisense 為何失效 時，無意看到demo的 介紹文 ，發現好物一枚— dbForge SQL Complete ! 好東西的推薦文永遠不嫌多，故特別PO文一篇介紹給讀者朋友們周知。 dbForge的構想與SSMS 2008相同，就是在輸入T-SQL時給予提示及自動完成，但dbForge SQL Complete支援的項目更多範圍更廣，整理幾點我認為值得一提的特色: (產品分為免費的SQL Complete Express及付費版SQL Complete Standard...

Posted 04 June 2011 01:37 AM 由 Jeffrey | 4 comment(s) 25,550

Filed under: Tools, SQL 2005, SQL 2000, SQL 2008

SQL 2008 TVP資料匯入之火力展示

切入主題前，先感謝網友小言在 留言 中分享了SQL 2008 TVP(Table Value Parameter，資料表值參數)，一個我差點錯過的好物。 過去遇到要從程式端塞大量資料到資料庫的場合，我慣用的 伎倆 手法不外乎產生INSERT Script與BULK INSERT這兩招，坦白說都挺麻煩的，逃不掉將記憶體中DataTable物件轉換為Script或CSV Text的過程，而既然有 Object -> Text的需求，就免不了得針對不同型別寫邏輯。如果能直接把DataTable轉成資料庫端的Table...

Posted 26 May 2011 01:01 AM 由 Jeffrey | 6 comment(s) 23,083

Filed under: .NET, SQL 2008

SQL與ORACLE處理IsolationLevel.Serializable的行為差異

接觸ORACLE之前，玩過好一陣子的SQL，對Serializable 隔離等級 的認知是: 在隔離範圍內，所有讀取資料的動作都會產生鎖定，將資料保護起來避免外界更動 雖然Serializable隔離等級的代價高昂，對效能殺傷力極大應盡量避免，但因其執行邏輯簡單不易出錯，還是會有其適用時機。 以下是在SQL上實現Serializable隔離的示範: 使用Visual Studio逐行偵錯功能，在兩次讀取間設定中斷點，中斷時用SSMS試圖更動同一筆資料，就可發現UPDATE指令會擋住，要等到.NET程式完成或放棄Transaction後才會繼續執行...

Posted 27 January 2011 02:30 PM 由 Jeffrey | with no comments 8,953

Filed under: ORACLE, .NET, SQL 2008

SQL 2008 / Windows 2008 之 MSDTC 注意事項

過去累積不少MSDTC的 茶包處理經驗 ，也想出了 用Mini C# Lab測試MSDTC 的技巧。原本射MSDTC茶包還算得心應手，但最近又踢了鐵板，學到新的心得: SQL 2008啟動分散式交易的時機改變 依過去的 研究心得 ，只要不是用同一個SqlConnection物件執行查詢，即便連線字串完全相同，SQL 2005也會啟用分散式交易，所以在 Mini C# Lab MSDTC測試 中，就用了這點特性故意引發分散式交易進行測試。但今天被騙很慘，使用Mini C#測試看來MSDTC正常，但Web程式卻一直傳回分散式交易失敗...

Posted 12 November 2010 06:43 PM 由 Jeffrey | 5 comment(s) 19,701

Filed under: Trouble-Shooting, SQL 2008, Windows 2008, MSDTC

【茶包射手日記】ORACLE Linked Server查詢出現長度錯誤

同事報案: 在SQL 2008 R2使用Oracle Provider for OLE DB建立Linked Server，執行SELECT * FROM ORASVR1..SCHEMANAME.TABLNAME時出現以下錯誤: The OLE DB provider "OraOLEDB.Oracle" for linked server "ORASVR1" supplied inconsistent metadata for a column. The column...

Posted 23 September 2010 09:31 PM 由 Jeffrey | 1 comment(s) 7,845

Filed under: ORACLE, Trouble-Shooting, SQL 2008

【茶包射手日記】不限條件選項之SQL組裝與資料庫OR運算特性驗證

在設計查詢介面時，下拉式選單是很常見的條件選取方式，而往往我們都會再加上一個"不限條件"選項，允許使用者不限制條件，列出所有項頁。舉例來說: 在設定查詢地區的下拉選單(例如: ddlArea)，除了列出基隆市、台北市、台北縣、桃園縣...等各縣市Option外，常會再多一個"所有地區"選項，將Option Value設定='*'，選取時表示不限定地區。在Server端，便要將這些篩選條件轉為SQL指令，如何將'*'視為不限地區...

Posted 02 March 2010 01:39 AM 由 Jeffrey | 17 comment(s) 22,609

Filed under: ASP.NET, SQL 2005, .NET, Trouble-Shooting, SQL 2000, SQL 2008

CODE-在SQL Server XQuery中使用欄位或變數值當條件

今天處理到一個需求，要在SQL 2005中用Table1 JOIN Table2，Table2中有個XML欄位，裡面有多筆資料，JOIN時要用Table1的某個欄位當條件在XML中挑出特定XmlNode。 聽起來很模糊對吧? 我用來實例來比喻，假設有個Player資料表跟Team資料表，Team資料表中有個MemberListXml欄位，其中有該球隊成員的相關資料。我今天要用Player資料表去JOIN Team資料表，先找出Player所屬球隊，再從該球隊MemberListXml中用XPath語法調出該名球員的資料...

Posted 08 January 2010 08:03 PM 由 Jeffrey | 1 comment(s) 10,645

Filed under: SQL 2005, SQL 2008, CODE

MEMO-Procedure Transaction: SQL Server vs Oracle

同事詢問關於Procedure內是否會自動包成Transaction的問題，我的認知是SQL要額外下SET XACT_ABORT ON，但Oracle則預設會自動包成Transaction。記憶有些模糊，所以索性做個實驗最準。 分別在SQL, Oracle寫了Procedure，先塞入兩筆資料，第三筆故意產生PK重覆錯誤，觀察資料庫是否有前兩筆資料來判別是否有Transaction保護。實驗證明，原先的認知沒錯，二者行為不同。 SQL Server Procedure要將操作包成Transaction的話...

Posted 06 November 2009 10:15 AM 由 Jeffrey | 8 comment(s) 16,288

Filed under: ORACLE, SQL 2005, SQL 2000, SQL 2008

搜尋