SELECT TOP 1 * FROM TABLE

MS SQL SERVER COMMAND

SELECT TOP 5 * FROM TABLE

ORACLE COMMAND

SELECT * FROM (
      SELECT * FROM TABLE ORDER BY COLUMN1
)
WHERE ROWNUM <= 5
ORDER BY ROWNUM ASC

FOR WEB

FOR WEB

using System.Collections;

ArrayList ArrSql1 = new ArrayList();

ArrSql1.Add("COMMAND");


        private bool DBWriter(ArrayList SQL)
        {
            try
            {
                SqlCommand DBCommand = new SqlCommand();
                SqlTransaction DBTransaction;
                SqlConnection DBConnection = new SqlConnection();
                DBConnection.ConnectionString = ConfigurationManager.ConnectionStrings["INFOBCKConnectionString"].ConnectionString.Trim();
                DBConnection.Open();
                DBCommand = DBConnection.CreateCommand();
                DBTransaction = DBConnection.BeginTransaction("SQLTransaction");
                DBCommand.Connection = DBConnection;
                DBCommand.Transaction = DBTransaction;
                try
                {
                    for (Int32 i = 0; i < SQL.Count; i++)
                    {
                        DBCommand.CommandText = SQL[i].ToString();
                        DBCommand.ExecuteNonQuery();
                    }
                    DBTransaction.Commit();
                    return true;
                }
                catch (Exception ex)
                {
                    DBTransaction.Rollback();
                    ex.ToString();
                    return false;
                }
            }
            catch (Exception ex)
            {
                ex.ToString();
                return false;
            }
        }

處理中英文夾雜時，要寫入oracle卻發生長度過常的處理方式

直接使用

System.Text.Encoding.GetEncoding("big5").GetByteCount("中文字串ABC")

抓取長度，上例是 11

SQL Server Management Studio無法記住密碼

用sa賬戶登錄sql server 2008，勾選了“記住密碼”，但重新登錄時，SQL Server Management Studio無法記住密碼。

後來發現，在重新登錄時，登錄名顯示的並非是sa賬戶，而是其他賬戶。點擊下拉框，發現記錄的登錄名不止一個。於是嘗試清除這些歷史記錄。

清除SQL Server Management Studio的歷史記錄很簡單，只要刪除或重命名文件SqlStudio.bin即可。該文件通常在以下目錄：
xp在C:\Documents and Settings\Administrator\Application Data\Microsoft\Microsoft SQL Server\100\Tools\Shell\SqlStudio.bin
win7在C:\Users\%username%\AppData\Roaming\Microsoft\Microsoft SQL Server\100\Tools\Shell\SqlStudio.bin

清除之後，重新打開SQL Server Management Studio發現，之前那些記錄的登錄名果然沒了，然後重新用sa賬戶登錄，勾選“記住密碼”。如此反复幾次，發現就可以記住sa密碼了！
如果是 sql server 2005 出現同樣的問題，也可以嘗試以上解決方案。

sql server 2005記錄登錄名的文件是mru.dat，
路徑xp位於C:\Documents and Settings\Administrator\Application Data\Microsoft\Microsoft SQL Server\90\Tools\Shell\文件夾下。

將多筆記錄做成一個欄位資料

Select count(*) AS [A]
From CNTMGM.DBO.CNTCMSD_TAB WHERE CNT_NO = 'M10200118000' AND Isnull(CheckDate, '') = ''
And BPKEY Not In ('4A409A56-97AB-42C6-A54D-32E077CC3ADC','15C7C764-32EF-40CF-B9C9-29D4595C1964')
UNION ALL
Select SUM(CONVERT(DECIMAL, REPLACE(INFOBCK.DBO.FN_ISEMPTY(ISNULL(OriMoney, '0'), '0'), ',', ''))) AS [A]
From CNTMGM.DBO.CNTCMSD_TAB WHERE BPKEY In ('4A409A56-97AB-42C6-A54D-32E077CC3ADC','15C7C764-32EF-40CF-B9C9-29D4595C1964')


SELECT ';' + CONVERT(NVARCHAR(MAX),A.A) FROM (
    Select count(*) AS [A]
    From CNTMGM.DBO.CNTCMSD_TAB WHERE CNT_NO = 'M10200118000' AND Isnull(CheckDate, '') = ''
    And BPKEY Not In ('4A409A56-97AB-42C6-A54D-32E077CC3ADC','15C7C764-32EF-40CF-B9C9-29D4595C1964')
    UNION ALL
    Select SUM(CONVERT(DECIMAL, REPLACE(INFOBCK.DBO.FN_ISEMPTY(ISNULL(OriMoney, '0'), '0'), ',', ''))) AS [A]
    From CNTMGM.DBO.CNTCMSD_TAB WHERE BPKEY In ('4A409A56-97AB-42C6-A54D-32E077CC3ADC','15C7C764-32EF-40CF-B9C9-29D4595C1964')
)A
FOR XML PATH('')


SELECT SeqNo,
(
SELECT DISTINCT S2.ProjectNo + ',' FROM CNTCMSD_TAB as s2
WHERE s1.SeqNo=s2.SeqNo
FOR XML PATH('')
) as ProductID
FROM CNTCMSD_TAB as s1
WHERE SeqNo LIKE 'NCNT-1020000142'
group by SeqNo

利用 CURSOR 跑迴圈

--宣告三個變數，@colA及@colB分別為table中的欄位,@MyCursor為指標
DECLARE @colA nvarchar(10)
DECLARE @colB nvarchar(10)
DECLARE @MyCursor CURSOR
--指定啟用了效能最佳化的 FORWARD_ONLY、READ_ONLY 資料指標
SET @MyCursor = CURSOR FAST_FORWARD
--假設TableA中有100筆，則下列的語法就會直接傳回100筆
FOR
Select colA,colB
From tableA
--開啟指標
OPEN @MyCursor
--取得第一筆的值放到@colA及@colB
FETCH NEXT FROM @MyCursor
INTO @ColA,@ColB
-- @@FETCH_STATUS=回針對連接目前開啟的任何資料指標而發出的最後一個資料指標 FETCH 陳述式的狀態
--傳回值
--  0  FETCH 陳述式成功。
-- -1 FETCH 陳述式失敗，或資料列已超出結果集。
-- -2 遺漏提取的資料列。
--開始繞迴圈
WHILE @@FETCH_STATUS = 0
BEGIN
--顯示@colA及@colB的值在畫面上
PRINT @ColA
PRINT @ColB
--你也可以在這之中處理其他sql部份，例如你可以寫依據@colA及@colB這兩個key在tabl2查詢後的資料寫到table3
--這是其中一種應用
insert into table3
select *
from table2
where table2.c1=@colA and table2.c2=@colB
--取得下一筆記錄的colA及colB
FETCH NEXT FROM @MyCursor
INTO @ColA,@ColB
END
CLOSE @MyCursor
DEALLOCATE @MyCursor

批次UPDATE跨資料庫

SQL Server 只收 [server].[database].[owner].[object name] 四個階層，超出的話就會錯誤，請改用別名 (alias) 方式來處理。

EXAMPLE:


Update PROJMGM.DBO.PISMINT_TAB set PROJMGM.DBO.PISMINT_TAB.G1TEMP = WKFSRV01.WEBFLOWDATA.DBO.TB_161_1.G1TEMP
FROM WKFSRV01.WEBFLOWDATA.DBO.TB_161_1
WHERE PROJMGM.DBO.PISMINT_TAB.EFORMSN = WKFSRV01.WEBFLOWDATA.DBO.TB_161_1.EFORMSN


Update PROJMGM.DBO.PISMINT_TAB set PROJMGM.DBO.PISMINT_TAB.G1TEMP = B.G1TEMP
FROM WKFSRV01.WEBFLOWDATA.DBO.TB_161_1 B
WHERE PROJMGM.DBO.PISMINT_TAB.EFORMSN = B.EFORMSN