No Money No Honey

一直都不知到啥是預存程序，後來大概知道它是做什麼用的，直到現在才開始面對它，大概了解一下它怎麼用。   出處:http://yes.nctu.edu.tw/SQL/StoredProcedure/StoredProcedure.htm 撰寫 Stored Procedures 利用 ADO 來存取資料庫是一件既輕鬆又簡單的工作, 不過有的時候又臭又長的 SQL 敘述實在很煩人, 這篇文章將介紹如何利用 Stored Procedure 來簡化你的 ASP 程式 本質上 Stored procedures (有時又稱 Sproc) 就是儲存在資料庫中的 Script 或 Batch, 撰寫 Sproc 並不是太難的一件事, 通常你會這樣查詢資料庫 Select * From Table 如何將它變成 Sproc? 以下是簡單的例子 Create Proc sp_SprocName1
As
  Select * From Table
Go 現在 Sproc 已成功的建立, 你可以試著去執行它 Exec sp_SprocName1 你可以為你的 Sproc 取任何的名字, 在 SQL 7.0 的命名原則以 128 個字元為限, SQL 並不規定 Stores procedure 之命名要以 sp_ 開頭, 但強烈建議你這麼做! 接下來我們再做些例子, 例如在查詢中加入 where 子句, Select * From Table Where Column1 = 0 , 這裡你是不是會覺得 Column1 的值如果可以改為一個輸入參數由我們輸入, 那就更加完美! Create Proc sp_SprocName2
@MyInput int
As
  Select * From Table
  Where Column1 = @MyInput
Go 完整的參數宣告語法為 @parameter_name datatype [= default|Null] [Varying] [Output] 如果你未提供 default 值, 則這個參數會被假設為必要輸入的, 在宣告參數時你還必須指定資料型態, 應為合法的 SQL Server 資料型態 在 Sproc 接收到輸入值後, 便將它存放在 @MyInput 變數中, 變數以 @ 符號開頭, 以 @@ 開頭的則為全域變數, 你可以這麼執行這個 Sproc Exec sp_SprocName2 0 我們再來看一個 Insert 的例子 Create Proc sp_SprocName3
  @Column1 varchar(20)
  @Column2 varchar(36) = Null
As
  Insert Into Table
  Values
    (@Column1, @Column2)
Go 在這例子中, Column2 的值即為選擇性輸入, 你可以這麼執行 Exec sp_SprocName3 'SomeString' 最後要告訴你如何在 ASP 中使用 Stored procedure, 不使用 Sproc 時你會這麼做 Set oConn = Server.CreateObject("Adodb.Connection")
oConn.Open YpurDSN
sSQL = "Select * From Table"
Set oRs = oConn.Execute(sSQL) 使用 Sproc 時, 這樣改就可以了 Set oConn = Server.CreateObject("Adodb.Connection")
oConn.Open YourDSN
sSQL = "sp_MySproc"
Set oRs = oConn.Execute(sSQL) 如果你的 Sproc 需要輸入參數, 你可以這麼做 Set oConn = Server.CreateObject("Adodb.Connection")
oConn.Open YourDSN
sSQL = "sp_MySproc " & MyInput
Set oRs = oConn.Execute(sSQL) 是不是很簡單呢! 希望這篇文章對你有幫助!

預設資料庫看起來沒什麼，但是如果您剛好把預設資料庫離線…………那好笑的事就發生了，你會無法再使用SQL Server Enterprise Manager登入，會出現類似 無法開啟使用者預設資料庫。登入失敗。的訊息，要再讓剛離線的資料庫上線也不行，阿程式也不會自動詢問或是自動幫您改成其它預設資料庫，只能說  掯~~~微”軟” 的SQL2005 實在有夠笨…………………   問題出現了還是要解決， 登入時先選別的資料庫 下指令把預設資料庫修正即可 

參考資訊：http://support.microsoft.com/kb/196076/zh-tw

SELECT declno as no
FROM decldatah 
union all 
SELECT declno
FROM decldatab 

看科學家研究，人會覺得走在左邊的人比較有男(女)人味，因此想泡妞的請走在女伴的左邊……………不是沒有道理的，台灣屬於靠右邊走的習慣，如果男士在女生左邊，會有保護女生讓女生走比較路邊比較安全的感覺............................再說連電腦SQL語法也是要你在左邊呢!!!!   昨天去書店剛好看到有介紹Join的書翻了一下，發現 left join 正符合我的需求，試了三個Table的 left Join一次把我要的都列表出來，省了另外兩個額外查詢，看文獻也說使用join比較有效率。 select g.*, cbas.custid, cbas.cmpabv, cntc.cntcid, cntc.phone from dbo.gdsinfomt g
left join dbo.custbas cbas on (g.Agent = cbas.custid)
left join dbo.custcntc cntc on ((g.Agent = cntc.custid) and (cntc.cntcid = substring(g.declno,1,2)))
where g.senddate >= '20100128'
and g.senddate <= '20100128'
and g.Gdsno like 'D%' 如果是土法鍊鋼的做法會有三個階段的查詢，會先查詢 select g.* from dbo.gdsinfomt g
where g.senddate >= '20100128'
and g.senddate <= '20100128'
and g.Gdsno like 'D%' 然後在 select g.* from dbo.gdsinfomt g 資料回圈內再一筆一筆依 g.Agent = cbas.custid 為條件查詢要的資料 select * from dbo.custbas cbas where cbas.custid = g.Agent(上個查詢查到的資料) 第三個TABLE在 select g.* from dbo.gdsinfomt g 資料回圈內再一筆一筆依 g.Agent = cntc.custid and cntc.cntcid = substring(g.declno,1,2) 這兩個條件查詢 select * from dbo.custcntc cntc where g.Agent = cntc.custid and cntc.cntcid = substring(g.declno,1,2)

SQL與法中直接擷取字串的函式使用 substring(原字串, 啟始取值位置, 欲取的字元數) 這裡與DELPHI的Copy用法一摸摸一樣樣~ 範例：取g.declno前兩字元 cntc.cntcid = substring(g.declno,1,2)

文章出處：http://www.dotblogs.com.tw/jimmyyu/archive/2009/07/03/9088.aspx 這是一個曾經發生過的案例，兩年前我在客戶家處理一個效能的案子時，客戶告知：你們的系統最近跑起來比過去慢了不少，每個動作平均大約慢了2-3秒鐘，我心想：我們最近不會改了什麼東西吧。 本著追根究柢的精神，開始跟客戶喇勒：最近有人做什麼嗎？有人版更嗎？DBA有改設定嗎？MIS有做系統調整嗎？這一連串的閒聊問答，最後得到的結果很好：沒有.... 根據我在客戶家處理多年問題的經驗，客戶有這種回答也是很合理的，但我直覺問題並不單純，就開始進行問題的測試，從一些現象上看起來，最後把問題轉到DB上去了，最後找到DB中有個設定被修改了，這個設定叫作Auto Close(自動關閉)，詳細的內容可以看這個連結，講的蠻清楚的：http://sharedderrick.blogspot.com/2009/02/autoclose.html 針對這個設定有個地方要特別注意，在SQL Server 2000以前，Create database時，這個值預設是ON；SQL Server 2005以上的版本預設值則是OFF。     http://sharedderrick.blogspot.com/2009/02/autoclose.html =============================================================================================================認識 SQL Server Database 資料庫選項：AUTO_CLOSE(自動關閉) 資料庫 AUTO_CLOSE 選項 選項值
說明 ON(True) 當設定為：ON 時，資料庫會完全關閉，並在最後一個使用者結束之後釋放其資源。當使用者試圖重新使用資料庫時，會自動重新開啟資料庫。例如，藉由發出 USE database_name 陳述式。 這個選項可能會造成經常存取之資料庫的效能降低，因為在每一個連接之後都會增加開啟和關閉資料庫的負擔。
而且在每一個連接之後，啟用此選項也會排清程序快取。 對於桌面資料庫而言，啟用此選項非常有用，因為它可讓您將資料庫檔案當做一般檔案來管理。您可以移動它們、複製它們來建立備份，甚至可以用電子郵件將它們傳給其他使用者。 在舊版 SQL Server 中，AUTO_CLOSE 是同步處理序，當有重複建立和中斷與 Database Engine 之連接的應用程式存取資料庫時，該處理序可能會降低效能。從 SQL Server 2005 開始，AUTO_CLOSE 處理序是非同步的；重複開啟和關閉資料庫不再降低效能。 當啟用此選項時，sys.databases 目錄檢視中的某些資料行及 DATABASEPROPERTYEX 函數會傳回 NULL，因為資料庫無法擷取資料。若要解決這個問題，請執行 USE 陳述式來開啟資料庫。 此啟用使選項也會造成，當資料庫關閉的作業執行時，會自動清除 SQL Server 執行個體的計畫快取。 若清除計畫快取會導致重新編譯所有後續執行計畫，而且可能會導致查詢效能突然暫時下降。在 SQL Server 2005 Service Pack 2 中，針對每次清除計畫快取的快取存放區，SQL Server 錯誤記錄檔會包含下列參考訊息：「由於某些資料庫維護或重新設定作業，SQL Server 的 '%s' 快取存放區 (計畫快取的一部分) 發生 %d 次快取存放區排清。」只要快取發生排清，這個訊息就會每五分鐘記錄一次。 OFF(False) 當設定為：OFF 時，則在最後一個使用者結束之後資料庫仍為開啟。
資料庫鏡像需要 AUTO_CLOSE OFF。 預設值：
不論作業系統為何，當使用 SQL Server 2000 Desktop Engine 或 SQL Server Express 時，這個選項對所有資料庫是設為：ON(True)；其他版本的 SQL Server 則是設為：OFF(False)。 最佳作法建議
如果經常存取資料庫，請將此資料庫的 AUTO_CLOSE 選項設定為 OFF。 啟用資料庫選項：AUTO_CLOSE的方式如下： view plaincopy to clipboardprint?

任務1：建立資料庫  

步驟01. 執行以下範例程式碼：  

USE [master]  

GO  

IF  EXISTS (SELECT name FROM sys.databases WHERE name = N'DB01')  

DROP DATABASE [DB01]  

GO  

CREATE DATABASE [DB01] ON PRIMARY

( NAME = N'DB01', FILENAME = N'C:\DB01.mdf' , SIZE = 3072KB , FILEGROWTH = 1024KB )  

LOG ON

( NAME = N'DB01_log', FILENAME = N'C:\DB01_log.ldf' , SIZE = 1024KB , FILEGROWTH = 10%)  

GO  

任務2：啟用資料庫選項：AUTO_CLOSE  

步驟01. 執行以下範例程式碼：  

USE [master]  

GO  

-- 設定資料庫DB01的AUTO_CLOSE屬性為ON

ALTER DATABASE [DB01]  

SET AUTO_CLOSE ON WITH NO_WAIT  

GO 

請參考圖1所示：
圖1：設定資料庫選項：AUTO_CLOSE view plaincopy to clipboardprint?

或是使用SSMS(SQL Server Management Studio)管理工具  

步驟01. 執行SSMS管理工具，在「物件總管」，展開「資料庫」，點選指定的資料庫，滑鼠右鍵，選取：「屬性」。  

步驟02. 在「資料庫屬性」視窗，輸入以下的選項：  

在左邊「選取頁面」窗格，點選「選項」。  

在右邊窗格，在「其他選項」區域，在「自動」區域下，在「自動關閉」方塊部分，下拉選取「True」。  

步驟03. 點選「關閉」，完成設定。  

任務3：測試資料庫選項：AUTO_CLOSE  

步驟01. 執行以下範例程式碼數次後：  

USE DB01  

GO  

USE master  

GO  

USE DB01  

GO  

USE tempdb  

GO  

USE DB01  

GO  

USE master  

GO  

任務4. 檢視「SQL Server 記錄檔」  

步驟01. 執行SSMS管理工具，在「物件總管」，展開「管理」＼「SQL Server 記錄檔」。  

步驟02. 滑鼠雙擊，開啟最新的記錄檔。  

步驟03. 在「記錄檔檢視器」，可以看到有數筆啟動此資料庫的訊息：  

Starting up database 'DB01'. 

請參考圖2所示：
圖2：訊息：啟動資料庫Starting up database view plaincopy to clipboardprint?

任務5. 直接複製此資料庫的資料檔案  

步驟01. 使用檔案總管，在 C: 碟根目錄下，建立資料夾 C:\ArchiveDB。  

步驟02. 使用檔案總管，在 C: 碟根目錄下，選取此資料庫的檔案：DB01.mdf 與 DB01_log.ldf。  

步驟03. 複製此兩個檔案到 C:\ArchiveDB  資料夾內。  

由上，將資料庫選項：AUTO_CLOSE設定為：ON，在確認關閉後，就可以執行複製檔案。也類似於將此資料庫設定為「離線(OFFLINE)」。  

提醒您：  

若資料庫選項：AUTO_CLOSE是設定為OFF(false)時，資料庫引擎將鎖定資料庫檔案，也無法複製或是刪除。 

請參考圖3所示：
圖3：檔案已經開啟，無法複製或是刪除