試著把流水號pkey做成另一種流水號id吧

T-SQL
SELECT CONVERT(VARCHAR, GETDATE(),126)+'-'+CONVERT(NVARCHAR(50),NEWID())

為查詢的結果加上序號(ROW_NUMBER,RANK,OVER)

在MS SQL2005以後，增加了一些幫查詢結果加上序號的函數

以下的範例使用北風(NorthWind)資料庫

介紹如下：

1.ROW_NUMBER

依照指定的欄位排序，並逐筆加上順號的方式

例如：

SELECT 
 ROW_NUMBER() OVER(ORDER BY CustomerID) AS ROWID
 ,*
FROM Orders

2.RANK

依照排序的欄位，相同的資料相同排名，下一個不同會【跳脫】

SELECT 
 RANK() OVER(ORDER BY CustomerID) AS ROWID
 ,*
FROM Orders

3.DENSE_RANK

依照排序的欄位，相同的資料相同排名，下一個不同會【不跳脫】

SELECT 
 --ROW_NUMBER() OVER(ORDER BY CustomerID) AS ROWID
 --RANK() OVER(ORDER BY CustomerID) AS ROWID
 DENSE_RANK() OVER(ORDER BY CustomerID) AS ROWID
 ,*
FROM Orders

在SQL SERVER建立LINK SERVER的SQL SERVER別名

1）第1步：

• 在SQL Server Management Studio中打開連結的伺服器，然後點擊“新增連結的伺服器”。
• 選擇一般頁面
• 在“連結的伺服器”中指定別名。
• 選擇SQL Native Client的提供者。
• 在“產品名稱”輸入 sql_server。
• 在“資料來源”指定要使用的的主機名。

2）第2步：

• 在安全性 - 使用此安全性內容建立

3）步驟3：

• 在服務器選項卡 - 把“資料存取”，RPC，“RPC輸出”和“使用遠端定序”設為true。

PL-SQL DB-LINK 查詢 MS-SQL

SELECT * FROM EMSECS_TAB@CPCIS1_INFO

SQL Server 2005 如何解開 Lock

使用 select object_id('TABLE_NAME') 或是 select object_name('OBJECT_ID') 去查出該 Lock 是屬於哪個 Table

使用 sp_lock 去查詢被 Lock 的資料有哪些, 因為他不會直接列出 Table 名稱, 所以可透過上述取得OBJID

接下來針對SPID去下指令即可

kill 81

SQL去除欄位前後空白及斷行字元

SQL 中的 TRIM 函數是用來移除掉一個字串中的字頭或字尾。最常見的用途是移除字首或字尾的空白。這個函數在不同的資料庫中有不同的名稱：

MySQL: TRIM(), RTRIM(), LTRIM()
Oracle: RTRIM(), LTRIM()
SQL Server: RTRIM(), LTRIM()

SQL Server及Oracle沒有TRIM()函數，因此可用下列的語法清除:

-- SQL去除斷行字元 (1st)
UPDATE [Donor]
SET [DonorList] = REPLACE(([DonorList]), CHAR(10), '');

-- SQL去除前後空白 (2rd)
UPDATE [Donor]
SET [DonorList] = LTRIM(RTRIM([DonorList]));
而UI裏該欄位也要在異動資料時，自動去除斷行及空白字元。

// C#去除斷行及空白字元
this.ctrlDonorList.Text = this.ctrlDonorList.Text.Trim('\r', '\n', ' ');

如此內服外敷，即可藥到病除。

密碼都對了，可是就是登不進去!!?? 可能是變成非multiple囉

 

怎麼還原資料庫（Restore）後，資料庫的名稱旁多了一個「限制的使用者」呢？該怎麼更改設定？
不要以為這是惡作劇，這不是把資料庫的名稱多加了這一串字，沒那麼無聊





在資料庫的屬性的「選項」中，可以來修改資料庫的限制存取方式

下圖中可以看到總共有三種模式：「Multiple」、「Single」與「Restricted」

• Single User Mode ：同一時間只能任一個使用者登入使用
• Restricted User Mode : 只有db_owner、dbcreator、sysadmin 群組的人可以登入
• Multiple User Mode：有權限的都能依權限範圍使用

當然你也可以用 T-SQL 的方式來改變限制存取的方式

ALTER DATABASE [DB_Name] SET MULTI_USER WITH NO_WAIT

ALTER DATABASE [DB_Name] SET SINGLE_USER WITH NO_WAIT

或者

EXEC sp_dboption 'DB_Name', 'single user', 'false'

EXEC sp_dboption 'DB_Name', 'single user', 'true'

底下就改成了「單一使用者」了

還原資料庫時,"無法獲得獨佔存取權,因為資料庫正在使用中"的解決方法

使用Sql Server Management Studio 還原資料庫時,
常會遇到 "無法獲得獨佔存取權,因為資料庫正在使用中" 的錯誤訊息

我的一勞永逸解決方法
用指令還原
指令如下

ALTER DATABASE 你的DB名稱 SET OFFLINE WITH ROLLBACK IMMEDIATE
RESTORE DATABASE 你的DB名稱 FROM  DISK='C:\你的路徑\你的備份檔點BAK' WITH RESTRICTED_USER,REPLACE

絕對有效

TRIGGER SAMPLE

USE [DB_NAME]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TRIGGER [dbo].[tg_NAME]
   ON  [dbo].[TABLE_TAB]
   AFTER INSERT,UPDATE,DELETE
AS
BEGIN
    SET NOCOUNT ON;
  
    IF (SELECT COUNT(*) FROM INSERTED) > 0 AND (SELECT COUNT(*) FROM DELETED) = 0 BEGIN --新增
        INSERT INTO TABLE_LOG
        SELECT *, 'INSERT' FROM INSERTED
    END
  
    IF (SELECT COUNT(*) FROM INSERTED) > 0 AND (SELECT COUNT(*) FROM DELETED) > 0 BEGIN --變更
        INSERT INTO TABLE_LOG
        SELECT *, 'UPDATE_BEFORE' FROM DELETED
        INSERT INTO TABLE_LOG
        SELECT *, 'UPDATE_AFTER' FROM INSERTED
    END
  
    IF (SELECT COUNT(*) FROM INSERTED) = 0 AND (SELECT COUNT(*) FROM DELETED) > 0 BEGIN --刪除
        INSERT INTO TABLE_LOG
        SELECT *, 'DELETE' FROM DELETED
    END
  
              
END

GO

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

TSQL顯示資料表, 欄位名稱, 型別, 長度 語法

這語法很好用，可以順利撈出資料庫中的所有資料表名稱，各資料表中的欄位名稱，型別，與長度。
另外補充:
要找出資料庫中的所有資料表==>查sys.objects
要找出某資料表中的所有欄位與欄位型別id, 長度==>查sys.columns
要找出欄位型別名稱==>查sys.types

--===============================================================
自從訂閱了點部落之後，對資訊系統的自我認知，已經遠超越「Mammut」與「Marmot」 的大小差異，並且日漸渺小當中，每日只能從眾多高手牙慧中，盼望能多吸取一些知識。這幾年來由於工作的不穩定，我得常接手別人開發的專案，每一個專案跟程 式設計師一樣，有著不同的個性，有的有高度的模組化，有的ASP互相亂 include 一堆，有的把4-5個專案放到同一個目的網站中，有的遺留了許多無用的程式碼......。程式碼這麼的多元化，當然資料庫就更千奇百怪了，相同的 是......都沒有DD、都沒有有用的文件。

所以我便常常需要一個一個去看資料表的欄位名稱，也得用 Query 的結果猜測，這個欄位的「意義」還有他可能會跟「哪些資料表」是有關連的。
今天看到「尋找某欄位存在那幾些資料表」文章，發現這個功能不就是我之前很想要的功能嗎？一次把相關連的欄位名稱都找出來，真棒～這個功能我需要。
可是我還想知道其他的一些資訊，所以動手改了一下 T-SQL，在看整個程式前，先來看三個資料庫物件
SELECT * FROM sysobjects where type='U' -- 查詢所有使用者資料表

SELECT * FROMsyscolumns whereid=117575457 -- 依某資料表ID查所有欄位

SELECT * FROM systypes  -- 查欄位屬性xtype的意思

當然你要查詢上述的資料庫物件，要先切換到你想查的資料庫囉（USE DataBase_Name;）！
底下以 「NorthWind」資料庫查詢為對象
請注意，當中的「store」的值是決定要不要把 Query 結果給儲存起來，當 store=1 則不儲存，其他值會寫到同一個DB裡，建立名為「__T_all___」的資料表，每次會刪除重新建立。

useNorthwind ;    -- 更換成你要查詢的資料庫 DECLARE@tablename NVARCHAR(50)DECLARE @cloumnname NVARCHAR(50)DECLARE @store TINYINT SET @tablename=''   -- 輸入要查詢的資料表，留下空的表示查全部 SET@cloumnname=''  -- 輸入要查詢的欄位名稱，留下空的表示查全部 SET@store =2      -- 設定store != 1 會將結果暫存在 [__T_all___]  資料表，                        --請注意是否跟既有資料表同名 IF@store =1       -- store=1 只會顯示、傳回結果～不會儲存 BEGIN SELECT so.name 'Table',sc.name 'Column',st.name 'Type', sc.length 'Length' FROM sysobjects so INNER JOIN syscolumns sc ON so.id =sc.id INNER JOIN systypes st ON st.xtype=sc.xtype WHERE (so.type='U'AND st.name <> 'sysname') AND so.name LIKE '%'+@tablename+'%' AND  sc.name LIKE '%'+@cloumnname+'%'ORDER BY1 END ELSE   --不成立則存在[__T_all___]  資料表 BEGIN IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[__T_all___]') AND TYPE in (N'U'))DROP TABLE[__T_all___] SELECT so.name 'Table',sc.name 'Column',st.name 'Type', sc.length 'Length' INTO__T_all___ FROM sysobjectsso INNER JOIN syscolumns sc ON so.id =sc.id INNER JOIN systypes st ON st.xtype=sc.xtype WHERE (so.type='U'AND st.name <> 'sysname') AND so.name LIKE '%'+@tablename+'%' AND  sc.name LIKE '%'+@cloumnname+'%'ORDER BY1 END

多了欄位屬性與欄位大小，我想在做比對的時候，會準確更容易找到真正對應的欄位名稱。
另一個例子：搜尋 emp 相關欄位
SET@cloumnname='emp'


～ End

利用暫存資料表逐筆完成工作

DECLARE @cts As DECIMAL
DECLARE @rcount As DECIMAL
SET @cts = 1

SELECT DISTINCT IDENTITY(INT,1,1) AS sno, *
INTO #temp1
FROM TABLE1

SELECT @rcount = Count(*) FROM #temp1

WHILE (@cts <= @rcount) Begin
    SELECT @Times = Count(*) FROM #temp1 Where sno = @cts
    SET @cts = @cts + 1
END

DROP TABLE #temp1

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

DECLARE @TEMP TABLE
    (
      ID int IDENTITY PRIMARY KEY,
      seqno nvarchar(50),
      number nvarchar(50),
     )

Declare @cts As Decimal
    Declare @rcount As Decimal
    Set @cts = 1

    insert into @TEMP
    Select  seqno, number
    From cntmgm.dbo.CNTCMSD_TAB

    Select @rcount = Count(*) From @TEMP

    While (@cts <= @rcount) Begin
        Select @ResultVar = number + '|' + plandate + '|' + projectno
        From @TEMP
        Where ID = @cts       
        Set @cts = @cts + 1
    End

產生GUID的方法

Select CONVERT(varchar(255), NEWID()) As PKEY

[SQL]並未為 RPC 設定伺服器 'SQL-LINK-SERVER' (Msg 7411)

今天同事問說他Run Link Server的STORE PROCEDURE，出現了以下的錯誤!
Msg 7411, Level 16, State 1, Line 1
並未為 RPC 設定伺服器 'SQL-LINK-SERVER'
用中文去查，居然找不到，用Msg 7411就查到了，要把LINK-SERVER的屬性「RPT Out」設成「True」就搞定了!

另外，如果DB的定序與LINK SERVER的定序不同，LINK-SERVER的屬性「Use Remote Collation」要設成「False」哦!

讓你的資料欄位可以用時間當預設值

日期可以使用：(CONVERT([char](10),getdate(),(120)))  →  2012-10-05

時間可以使用：(CONVERT([char](8),getdate(),(114)))  →  10:33:07

T-SQL sp_password 修改sa語法

EXEC sp_password NULL, '你的密碼', 'sa' Go

壓縮 SQL Server Log 檔案 for SQL Server 2005 (含以前)

在資料庫使用一段時間後, 會發現Log.ldf資料變的非常龐大
比主資料庫*.mdf還大的情況, 這時候我們可以用下面的指令壓縮*.ldf(SQL Server Log)

語法如下
--（1）截斷交易記錄檔
BACKUP LOG [資料庫名稱] WITH TRUNCATE_ONLY
--（2）顯示資料庫檔案，找出交易記錄檔的邏輯檔名
EXEC sp_helpdb '資料庫名稱'    --SBODemoCN1012為資料庫名稱
--（3）壓縮交易記錄檔
USE 資料庫名稱
DBCC SHRINKFILE([資料庫_log],2)    --ldf檔的邏輯檔名，在(2)可以找出