ADO.NET分類文章　顯示方式：簡文 | 列表

2008年06月30日

bcp 備份所有資料表 (2)

bcp 備份所有資料表 的另一種寫法，利用 Cursor 來拼湊執行指令然後執行

SQL 指令 :

...繼續閱讀

Posted by chhuang0123 at 樂多Roodo!│ 19:25 │ 回應(0) │ 引用(0)

2008年06月28日

70-431 通過

拖了好幾個月，今天終於考到 MCTS-SQL SERVER 2005 認證了。不過，過程實在有點曲折就是了。因為工作實在太忙碌，一直沒有時間好好靜下心去看 Trainning Kit，導致完全沒信心去考試，才會一拖再拖從一月報名以後延期再延期。

報名費早早就繳了，也約好時間去準備去 III 考試。但是同樣的理由，因循且過了好幾次，每次都拖拖拉拉，每個月都上網去向 Prometric 延期。這星期三 6/25 我又想延期了，但是在 Prometric 上一直無法延期成功。主要是因為整個七月都沒有時間可選，反應給 Prometric 也沒有下文，眼看無法延期只好硬著頭皮準備了。

我自己並不是 DBA 或是 DBD 出身。只因我們公司的 MCITP-SQL SERVER 2005 DBD 離職了，只好換我來撐撐場面。撐一撐的過程之中也算是小有心得，加上這三天狂跑圖書館還有熬夜猛 K 書。幸好，老天是有眼的讓我考過了。

70-431 認證考試我拿到的考題共有 47 題 ：單選 29 題、複選 6題、模擬題 12 題。單選題我的答案 A: 3 題、B: 11 題、 C: 8題、D: 7 題。模擬題 12 題範圍：備份 3 題、View 2 題、Trigger 2 題、Index 、建資料庫、死結、安全性、CLR 等等。最後我考了 982 分通過了考試，真是太開心了。(滿分 1000，而 700 是最低通過認證的分數。)

PS. 考完後，跑去二二八捐血回饋一下社會！

PS2. 在微風台北車站 2F 跟 Dotjum 及 wxvbbo 聚餐

...繼續閱讀

Posted by chhuang0123 at 樂多Roodo!│ 23:06 │ 回應(2) │ 引用(0)

2008年04月19日

SQL Server 2005 - 關於 IDENTITY 的小技巧

最近，備份資料庫遇到一個問題，就是將舊資料搬移到新主機資料表的時候，如果主鍵是的 IDENTITY 是設定自動增加的話，那麼舊有的主鍵資料，在新資料表上面就好像是重新編號一樣又從第 1 筆開始塞到第 N 筆。

( PS. 我不是專業的 DBA 或是 DBD，所以我只會很笨的方法。)


...繼續閱讀

Posted by chhuang0123 at 樂多Roodo!│ 17:07 │ 回應(2) │ 引用(0)

2008年03月19日

將多筆相同鍵值的欄位內容合併

常常遇到這樣的問題，如何將相同鍵值的藍位內容值串接 ？
舉例來說 TableA 如下：

ID Type DESC
1 cpu 處理器
1 cpu 雙核心
1 cpu 800外頻
2 HD 硬碟
2 HD 500G
2 HD 5400轉

希望將欄位內容串接並以【,】分隔，在 SQL Server 2005 可以利用 XML PATH 來將功能實作，如：

ID Type DESC
1 cpu 處理器,雙核心,800外頻
2 HD 硬碟,500G,5400轉

   1:  SELECT T1.id, T1.type,
   2:  (
   3:    STUFF( (
   4:             SELECT ',' + [DESC]
   5:             FROM   @TABLEA T2
   6:             WHERE  T2.id = T1.id
   7:             FOR XML PATH('')
   8:            ), 1, 1, ''
   9:         )
  10:  ) AS [DESC]
  11:  FROM @TABLEA T1
  12:  GROUP BY id, type

 

如果只是想單純地將欄位內容單純串接，則可以用下列做法：

ID Type DESC
1 cpu 處理器雙核心800外頻
2 HD 硬碟500G5400轉

   1:  SELECT T1.id, T1.type,
   2:  (
   3:    SELECT [DESC] + ''
   4:    FROM   @TABLEA T2
   5:    WHERE  T2.id = T1.id
   6:    FOR XML PATH('')
   7:  ) AS [DESC]
   8:  FROM @TABLEA T1
   9:  GROUP BY id, type
如果是 SQL Server 2000 則請參考 Concatenate the values in a column in SQL Server 2000 and 2005 文章
關於 PATH 與 SUTFF 用法則參考：

 

...繼續閱讀

Posted by chhuang0123 at 樂多Roodo!│ 23:37 │ 回應(0) │ 引用(0)

2007年11月27日

bcp 備份所有資料表

利用 bcp 指令，將資料庫中所有的資料表匯出至 C:\bcp 目錄上。

  1. -- STEP 1, Create Directory bcp in C:\
  2. -- STEP 2, Modify Server Configuration
  3. EXEC sp_configure 'show advanced options', 1
  4. GO
  5. RECONFIGURE
  6. GO
  8. EXEC sp_configure 'xp_cmdshell', 1
  9. GO
  10. RECONFIGURE
  11. GO
  13. -- STEP 3, Backup All Tables in Some Database
  14. USE DBName
  15. GO
  17. SET NOCOUNT ON
  19. CREATE TABLE #a (name varchar(128), id int identity)
  20. INSERT #a (name) SELECT name FROM sysobjects WHERE XTYPE = 'U'
  22. DECLARE @id int, @cmd varchar(2000)
  23. SELECT @id = 0
  24. WHILE @id < (SELECT max(id) FROM #a)
  25. BEGIN
  26. SELECT @id = min(id) FROM #a where id > @id
  27. SELECT @cmd = 'bcp ' + db_name() + '.' + '[' + name + ']'+'
  28.    out "c:\bcp\' + name + '.txt" -c
  29.    -S' + @@servername + ' -Uusername -Ppassword'
  30. FROM #a where id = @id
  31. EXEC master..xp_cmdshell @cmd
  32. SELECT @cmd
  33. END
  35. DROP TABLE #a
  36. GO

Posted by chhuang0123 at 樂多Roodo!│ 23:10 │ 回應(0) │ 引用(0)

2007年11月19日

SQL CLR Function 查詢範圍 IP

上一篇【用 SQL 語法查詢符合條件範圍的 IP】中的 IP2INT 的 SQL 語法實在是太醜陋了，所以利用 SQL 可呼叫 CLR 的方式來撰寫查詢。

1. 用 C# 撰寫 SQL Function，請自行編譯成 IPUtility.dll
  1. using System;
  2. using System.Data.SqlTypes;
  3. using Microsoft.SqlServer.Server;
  5. public class IPUtility
  6. {
  7.     [SqlFunction]
  8.     public static SqlInt32 IsInRange(SqlString ip, SqlString startIP, SqlString endIP)
  9.     {
  10.         if (IPToLong(startIP) <= IPToLong(ip) && IPToLong(ip) <= IPToLong(endIP))
  11.         {
  12.             return 1;
  13.         }
  14.         return 0;
  15.     }
  17.     private static long IPToLong(SqlString ip)
  18.     {
  19.         string[] ipClass = ip.ToString().Split();
  20.         string strhexip = string.Format("{0:X2}{1:X2}{2:X2}{3:X2}", int.Parse(ipClass[0]), int.Parse(ipClass[1]), int.Parse(ipClass[2]), int.Parse(ipClass[3]));
  21.         return Convert.ToInt64(strhexip, 16);
  22.     }
  23. }

2. 在 SQL 引入 IPUtility 並建立 Function
  1. -- Enable CLR integration (啟動 SQL Server 2005 CLR 功能)
  2. sp_configure 'clr enabled', 1
  3. GO
  4. RECONFIGURE
  5. GO
  7. -- Register Assembly (註冊組件)
  8. USE master
  10. CREATE ASSEMBLY IPUtility
  11. FROM 'C:\TMP\IPUtility.dll' -- 請依照 dll 放置位置填寫
  12. WITH PERMISSION_SET = Safe
  13. GO
  15. ---- Drop assembly
  16. --DROP ASSEMBLY IPUtility
  17. --GO
  19. -- Create the managed user-defined function
  20. USE master
  21. GO
  23. CREATE FUNCTION dbo.IsInRange
  24. (
  25.     @IP nvarchar(15), @startIP nvarchar(15), @endIP nvarchar(15) -- 需要三參數: 比對 IP, 範圍起始 IP , 範圍結束 IP
  26. )
  27. RETURNS int
  28. AS EXTERNAL NAME IPUtility.IPUtility.IsInRange
  29. GO
  31. -- Test Run (測試 Function)
  32. DECLARE @IPTable TABLE (IP nvarchar(15) )
  33. INSERT INTO @IPTable VALUES ( N'172.16.57.103')
  34. INSERT INTO @IPTable VALUES ( N'172.16.58.103')
  35. INSERT INTO @IPTable VALUES ( N'172.16.59.103')
  36. INSERT INTO @IPTable VALUES ( N'172.16.60.103')
  37. INSERT INTO @IPTable VALUES ( N'172.16.61.103')
  38. INSERT INTO @IPTable VALUES ( N'172.16.62.103')
  40. SELECT IP
  41. FROM @IPTable
  42. WHERE dbo.IsInRange(IP, '172.16.58.103', '172.16.61.109') = 1
  43. GO

OUTPUT:

172.16.58.103
172.16.59.103
172.16.60.103
172.16.61.103


Posted by chhuang0123 at 樂多Roodo!│ 0:53 │ 回應(0) │ 引用(0)

2007年11月15日

SQL Server 2005 - Truncate All Tables

Truncate All Tables - Part II by  Madhivanan

  1. SET NoCount ON
  3. DECLARE @tableName varchar(200)
  4. SET @tableName=''
  6. WHILE EXISTS
  7. (
  8.   --Find all child tables and those which have no relations
  9.   SELECT T.table_name
  10.   FROM INFORMATION_SCHEMA.TABLES T
  11.        LEFT OUTER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC
  12.   ON T.table_name = TC.table_name
  13.   WHERE ( TC.constraint_Type = 'Foreign Key' OR TC.constraint_Type IS NULL )
  14.        AND T.table_name NOT IN ('dtproperties', 'sysconstraints', 'syssegments')
  15.        AND Table_type = 'BASE TABLE'
  16.        AND T.table_name > @TableName
  17. )
  19. Begin
  20.   SELECT @tableName = min(T.table_name)
  21.   FROM INFORMATION_SCHEMA.TABLES T
  22.        LEFT OUTER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC
  23.   ON T.table_name=TC.table_name
  24.   WHERE ( TC.constraint_Type = 'Foreign Key' OR TC.constraint_Type IS NULL )
  25.        AND T.table_name NOT IN ('dtproperties', 'sysconstraints', 'syssegments')
  26.        AND Table_type = 'BASE TABLE'
  27.        AND T.table_name > @TableName
  29.   --Truncate the table
  30.   Exec('Truncate table '+@tableName)
  31. End
  33. SET @TableName=''
  35. WHILE EXISTS
  36. (
  37.   --Find all Parent tables
  38.   SELECT T.table_name
  39.   FROM INFORMATION_SCHEMA.TABLES T
  40.        LEFT OUTER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC
  41.   ON T.table_name = TC.table_name
  42.   WHERE TC.constraint_Type = 'Primary Key'
  43.        AND T.table_name <> 'dtproperties'
  44.        AND Table_type='BASE TABLE'
  45.        AND T.table_name > @TableName 
  46. )
  48. Begin
  49.   SELECT @tableName = min(T.table_name)
  50.   FROM INFORMATION_SCHEMA.TABLES T
  51.        LEFT OUTER JOIN  INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC
  52.   ON T.table_name=TC.table_name
  53.   WHERE TC.constraint_Type = 'Primary Key'
  54.        AND T.table_name <> 'dtproperties'
  55.        AND Table_type = 'BASE TABLE'
  56.        AND T.table_name > @TableName
  58.   --Delete the table
  59.   Exec('Delete from '+@tableName)
  61.   --Reset identity column
  62. IF EXISTS
  63. (
  64.    SELECT *
  65.    FROM INFORMATION_SCHEMA.COLUMNS
  66.    WHERE COLUMNPROPERTY(
  67.     OBJECT_ID( QUOTENAME(table_schema)+ '.' + QUOTENAME(@tableName) ),
  68.     column_name,'IsIdentity'
  69.    ) = 1
  70. )
  72. DBCC CHECKIDENT (@tableName, RESEED, 1)
  73. End
  75. SET NoCount OFF

Posted by chhuang0123 at 樂多Roodo!│ 11:25 │ 回應(0) │ 引用(0)

用 SQL 語法查詢符合條件範圍的 IP

  1. CREATE FUNCTION IP2INT( @ip nvarchar(15) ) RETURNS bigint AS
  2. BEGIN
  3.   DECLARE @number bigint
  4.   SET @number = 0
  6.   SELECT @number = @number +
  7.          LEFT( @ip, CHARINDEX('.', @ip + '.') -1 ) * t,
  8.                @ip = STUFF(@ip, 1, CHARINDEX('.', @ip + '.' ), '')
  9.   FROM
  10.   (
  11.     SELECT t = CAST(16777216 AS bigint)
  12.     UNION ALL SELECT 65536
  13.     UNION ALL SELECT 256
  14.     UNION ALL SELECT 1
  15.   ) AS T
  17.   RETURN(@number)
  18. END
  19. GO
  21. DECLARE @IPTable TABLE (ip nvarchar(15) )
  22. INSERT INTO @IPTable VALUES ( N'172.16.57.103')
  23. INSERT INTO @IPTable VALUES ( N'172.16.58.103')
  24. INSERT INTO @IPTable VALUES ( N'172.16.59.103')
  25. INSERT INTO @IPTable VALUES ( N'172.16.60.103')
  26. INSERT INTO @IPTable VALUES ( N'172.16.61.103')
  27. INSERT INTO @IPTable VALUES ( N'172.16.62.103')
  29. SELECT ip
  30. FROM @IPTable
  31. WHERE dbo.IP2INT(ip)
  32. BETWEEN dbo.IP2INT('172.16.58.1') AND dbo.IP2INT('172.16.60.1')

Posted by chhuang0123 at 樂多Roodo!│ 11:19 │ 回應(0) │ 引用(0)

2007年11月13日

SQL Server - Random Number Generator

SQL SERVER - Random Number Generator Script - SQL Query
Apr 29th, 2007 by pinaldave


Method 1 : Generate Random Numbers (Int) between Rang

-- Create the variables for the random number generation
DECLARE @Random int;
DECLARE @Upper int;
DECLARE @Lower int

-- This will create a random number between 1 and 999
SET @Lower = 1 -- The lowest random number
SET @Upper = 999 -- The highest random number
SELECT @Random = Round(((@Upper - @Lower -1) * Rand() + @Lower), 0)
SELECT @Random


Method 2 : Generate Random Float Numbers


SELECT RAND( (DATEPART(mm, GETDATE()) * 100000 )
+ (DATEPART(ss, GETDATE()) * 1000 )
+ DATEPART(ms, GETDATE()) )


Method 3 : Random Numbers Quick Scripts


-- random float from 0 up to 20 - [0, 20)
SELECT 20*RAND()


-- random float from 10 up to 30 - [10, 30)
SELECT 10 + (30-10)*RAND()


-- random integer between 0 and 20 - [0, 20]
SELECT CONVERT(int, (20+1)*RAND())


-- random integer between 10 and 30 - [10, 30]
SELECT 10 + CONVERT(int, (30-10+1)*RAND())


Method 4 : Random Numbers (Float, Int) Tables Based with Time


DECLARE @t table( randnum float )

DECLARE @cnt int; set @cnt = 0
WHILE  @cnt <=10000
BEGIN
set @cnt = @cnt + 1
  INSERT INTO @t
  SELECT RAND( (DATEPART(mm, GETDATE()) * 100000 )
  + (DATEPART(ss, GETDATE()) * 1000 )
  + DATEPART(ms, GETDATE()) )
END

SELECT randnum, count(*)
FROM @t
GROUP BY randnum


Method 5 : Random number on a per row basis

-- The distribution is pretty good however there are the occasional peaks.
-- If you want to change the range of values just change the 1000 to the maximum value you want.
-- Use this as the source of a report server report and chart the results to see the distribution


SELECT randomNumber, Count(1) countOfRandomNumber
FROM (SELECT abs(cast(newid() as binary(6)) %1000) + 1 randomNumber
FROM sysobjects) sample
GROUP BY randomNumber
ORDER BY randomNumber


Posted by chhuang0123 at 樂多Roodo!│ 22:12 │ 回應(0) │ 引用(0)

2007年09月21日

DataSet 只有一筆資料，GridView 會出現 Title 消失的問題

如果 DataSet 內只有一筆資料，將 DataSet 直接 assign 給 GridView 十，會造成 Title 消失。或是 DataSet.WriteXml 輸出 XML 文件，利用 Excel 開啟，也會有標題消失的情況發生。

解決辦法：

ds.Tables[0].Rows.Add(ds.Tables[0].NewRow());

有一個偷機的辦法是在 DataTable 加上一個空的 DataRow，可以避免只有一筆記錄時不會出現表頭的問題，也不會影響原有的資料。

 


Posted by chhuang0123 at 樂多Roodo!│ 22:36 │ 回應(0) │ 引用(0)
 [1]  [2]  [最終頁] 
回頁首▲