SQL Server 2012 新增 Link Server SQL Server 2000

SQL Server 2012 已經沒有辦法直接在連結的伺服器中新增 SQL Server 2000 了，會出現下面這個討厭的錯誤。

 這時候就想到用 ODBC 來處理，果然上網找了一下就有資料了，趕緊動手設定

 先在 ODBC 中建立連線

 名字跟伺服器都自己填吧

SQL Server 2012 更新後造成無法導出 EXCEL

自從更新 VS 2012 的 SSIS 的套件之後，SSMS 突然就變成無法 Excel 匯出了，只要使用在匯出資料中使用 SQL 語法篩選資料就會出現 200 跟 202 這種奇怪的資料格式，然後就不能匯出了，Google 查看了一下就發現看起來像是更新造成的災難，

http://social.msdn.microsoft.com/Forums/sqlserver/en-US/97ff1f01-c02a-4c9a-b867-8eaecc464cfb/2012-sp1-no-longer-recognizes-common-data-types?forum=sqlintegrationservices

只好自己去修改一下 XML

修改一下這個檔案
C:\Program Files (x86)\Microsoft SQL Server\110\DTS\MappingFiles\MSSQLToSSIS10.XML

加入之後重新開啟 SSMS 就可以使用了

 <!-- varchar -->
 <dtm:DataTypeMapping >
  <dtm:SourceDataType>
   <dtm:DataTypeName>varchar</dtm:DataTypeName>
  </dtm:SourceDataType>
  <dtm:DestinationDataType>
   <dtm:CharacterStringType>
    <dtm:DataTypeName>DT_STR</dtm:DataTypeName>
    <dtm:UseSourceLength/>
   </dtm:CharacterStringType>
  </dtm:DestinationDataType>
 </dtm:DataTypeMapping>
 <dtm:DataTypeMapping >
  <dtm:SourceDataType>
   <dtm:DataTypeName>200</dtm:DataTypeName>
  </dtm:SourceDataType>
  <dtm:DestinationDataType>
   <dtm:CharacterStringType>
    <dtm:DataTypeName>DT_STR</dtm:DataTypeName>
    <dtm:UseSourceLength/>
   </dtm:CharacterStringType>
  </dtm:DestinationDataType>
 </dtm:DataTypeMapping>

 <!-- nvarchar -->
 <dtm:DataTypeMapping >
  <dtm:SourceDataType>
   <dtm:DataTypeName>nvarchar</dtm:DataTypeName>
  </dtm:SourceDataType>
  <dtm:DestinationDataType>
   <dtm:CharacterStringType>
    <dtm:DataTypeName>DT_WSTR</dtm:DataTypeName>
    <dtm:UseSourceLength/>
   </dtm:CharacterStringType>
  </dtm:DestinationDataType>
 </dtm:DataTypeMapping> 
 <dtm:DataTypeMapping >
  <dtm:SourceDataType>
   <dtm:DataTypeName>202</dtm:DataTypeName>
  </dtm:SourceDataType>
  <dtm:DestinationDataType>
   <dtm:CharacterStringType>
    <dtm:DataTypeName>DT_WSTR</dtm:DataTypeName>
    <dtm:UseSourceLength/>
   </dtm:CharacterStringType>
  </dtm:DestinationDataType>
 </dtm:DataTypeMapping> 

ObjectDataSource 與 GridView 的使用

每次寫新的頁面的時候，總是會忘記先將沒有資料的GridView隱藏起來，就會出現下面這種奇怪的錯誤，總是要找了好久才會發現，因為ObjectDataSource所引起的錯誤，在沒有任何參數與回傳的情形下，要先把這兩個物件都隱藏起來，這樣頁面才能正常使用呀

SQL Server Ole Automation Procedures無法使用

來源：http://msdn.microsoft.com/zh-tw/library/ms191188(v=sql.110).aspx

每次重新安裝 Database 後都會需要修改這個設定，因為前人使用的發信會有下面的錯誤

SQL Server 阻止了对组件“Ole Automation Procedures”的 过程“sys.sp_OACreate”的访问，因为此组件已作为此服务器安全配置的一部分而被关闭。系统管理员可以通过使用 sp_configure 启用“Ole Automation Procedures”。有关启用“Ole Automation Procedures”的详细信息，请搜索 SQL Server 联机丛书中的“Ole Automation Procedures”。


只要啟用就行了，從不知道什麼版本之後預設就是未啟用

 sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ole Automation Procedures', 1;
GO
RECONFIGURE;
GO

去掉字段最後面的符號

在程式中常常會使用到 , 或是 ; 這種分隔符號，

在最後顯示的時候都希望可以把最後一個去掉，

在 C# 中就可以用 TrimEnd(',') 來處理

Label.Text.TrimEnd(',');

而在 SQL 處理的話有兩種方式

 SUBSTRING(@str,1,LEN(@str)-1)

或是

 REVERSE(STUFF(REVERSE(@str),1,1,'')

因為在某些時候 SUBSTRING 無法使用，就只能用 REVERSE 來處理了。

IIS 7.5 URL Rewrite設定

在網址列傳值一直都是一個很危險的事情，如果可以把這部分隱藏起來就可以安全很多，在開週會的時候剛好有同事提到 URL Rewrite 的功能，剛好引起我的興趣了，於是就開始動手改自己電腦的環境進行測試。

先到這裡下載 IIS 的元件
http://www.iis.net/downloads/microsoft/url-rewrite

提供了不少語言的版本，安裝完成後，在 Internet Information Services (IIS) 管理員就可以看到多一個設定出來

點選後就可以開始新增規則了，

選擇新增空白規則

然後設定一下內容

在測試模式中可以先測試，就可以知道重寫 URL 中的變數要用哪一個了，這邊只需要最後一個斜線後的資訊，使用的是 {R:2}

這樣只要在網址列輸入 localhost/Product/Test123
然後在 IIS 真正執行的是 localhost/Product.aspx?PdtNo=Test123

這樣也可以把網頁的資訊隱藏起來，只要在 IIS 設定，程式都不需要修改，太方便了。

取得各個Table的列數等資料

最討厭的事情發生了，正式環境的資料庫版本比我們在使用的開發及測試環境還高，這時候就累人了，當要把正式環境的資料備份到測試環境時，又是一個浩大的工程，Google 後發現，降級還原是所有人的惡夢呀，產生 Script 之後來導入資料，SQL 檔都超過 1G，SSMS根本就開不起來，最後我決定只針對新增的資料進行匯入就好，於是需要下列這一段了，找出各個 Table 的列數後，我只需要比對後針對有新增的資料進行直接進行匯入即可，修改的資料就不管它了，哈哈～～～～

SET NOCOUNT ON 

--http://msdn.microsoft.com/zh-tw/library/ms188414.aspx
--更新目前資料庫中之所有物件的頁面及 (或) 資料列計數
DBCC UPDATEUSAGE(0) 

-- DB size.
EXEC sp_spaceused

-- Table row counts and sizes.
CREATE TABLE #t 
( 
    [name] NVARCHAR(128),
    [rows] CHAR(11),
    reserved VARCHAR(18), 
    data VARCHAR(18), 
    index_size VARCHAR(18),
    unused VARCHAR(18)
) 
 
--把每個Table使用的資訊存到#t之中
INSERT #t EXEC sys.sp_MSforeachtable 'EXEC sp_spaceused ''?''' 

--依使用空間較大的依序排列並顯示MB
SELECT *
, LTRIM(STR(CAST(LEFT(reserved,LEN(reserved)-3) AS NUMERIC(18,0)) / 1024, 18)) + 'MB' 

AS reservedSize_M
, LTRIM(STR(CAST(LEFT(data,LEN(data)-3) AS NUMERIC(18,0)) / 1024, 18)) + 'MB' 

AS dataSize_M
, LTRIM(STR(CAST(LEFT(index_size,LEN(index_size)-3) AS NUMERIC(18,0)) / 1024, 18)) + 'MB' 

AS indexSize_M
FROM #t
ORDER BY CAST(LEFT(data,LEN(data)-3) AS NUMERIC(18,0)) DESC

-- 顯示總共筆數及總共使用資訊
SELECT SUM(CAST([rows] AS int)) AS [rows]
, LTRIM(STR(SUM(CAST(LEFT(reserved,LEN(reserved)-3) AS NUMERIC(18,0))) / 1024, 18)) + 'MB' 

AS sumOfreservedSize_M
, LTRIM(STR(SUM(CAST(LEFT(data,LEN(data)-3) AS NUMERIC(18,0))) / 1024, 18)) + 'MB' 

AS sumOfdataSize_M
, LTRIM(STR(SUM(CAST(LEFT(index_size,LEN(index_size)-3) AS NUMERIC(18,0))) / 1024, 18)) + 'MB' 

AS sumOfindexSize_M
FROM #t
 
DROP TABLE #t 

出處: http://www.dotblogs.com.tw/rainmaker/archive/2012/02/02/67498.aspx

看看SQL Server在跑什麼SQL

最近常常發生sp_who2看到一堆連線，有些還在跑，這時候想要把它Kill掉前就一定要檢查一下它在執行什麼了，偏偏sp_who2就只能看到正在跑，看不到執行什麼指令，還好網路上真的是一個很方便的地方，馬上就找到指令了

1. SELECT      r.scheduler_id as 排程器識別碼,  
2.             status         as 要求的狀態,  
3.             r.session_id   as SPID,  
4.             r.blocking_session_id as BlkBy,  
5.             substring(  
6.                 ltrim(q.text),  
7.                 r.statement_start_offset/2+1,  
8.                 (CASE  
9.                  WHEN r.statement_end_offset = -1  
10.                  THEN LEN(CONVERT(nvarchar(MAX), q.text)) * 2  
11.                  ELSE r.statement_end_offset  
12.                  END - r.statement_start_offset)/2)  
13.                  AS [正在執行的 T-SQL 命令],  
14.             r.cpu_time      as [CPU Time(ms)],  
15.             r.start_time    as [開始時間],  
16.             r.total_elapsed_time as [執行總時間],  
17.             r.reads              as [讀取數],  
18.             r.writes             as [寫入數],  
19.             r.logical_reads      as [邏輯讀取數],  
20.             -- q.text, /* 完整的 T-SQL 指令碼 */  
21.             d.name               as [資料庫名稱]  
22. FROM        sys.dm_exec_requests r   
23.             CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS q  
24.             LEFT JOIN sys.databases d ON (r.database_id=d.database_id)  
25. WHERE       r.session_id > 50 AND r.session_id <> @@SPID  
26. ORDER BY    r.total_elapsed_time desc  

結果在執行的時後會發生sql_handle錯誤，原來這指令只能在SQL Server 2005以上使用，但是我們的資料庫相容性設定為SQL Server 2000，所以將資料庫相容性設定為SQL Server 2005就可以執行了。

出處: http://blog.miniasp.com/post/2010/10/13/How-to-get-current-executing-statements-in-SQL-Server.aspx

找出資料庫中自動編號的欄位名稱與資料表名稱

最近在做資料庫的移轉，因為資料庫中的東綁西綁的，導致無法很簡單的完成，最後只好土法煉鋼，先將結構建立好後，然後用INSERT INTO去把資料轉過去，討厭的是還有很多自動編號的欄位會造成錯誤，這部份就只好特別挑出來處理，首先就要先找出有哪些Table中有自動編號的欄位了

SELECT QUOTENAME(t.TABLE_SCHEMA) + '.' + QUOTENAME(t.TABLE_NAME)  AS TableName,
  c.COLUMN_NAME AS ColumnName,
  c.DATA_TYPE AS 'DataType',
  IDENT_CURRENT(t.TABLE_SCHEMA  + '.' + t.TABLE_NAME) AS CurrentIdentityValue,
  CASE c.DATA_TYPE
   WHEN 'bigint' THEN (IDENT_CURRENT(t.TABLE_SCHEMA  + '.' + t.TABLE_NAME) * 100.) / 9223372036854775807
   WHEN 'int' THEN (IDENT_CURRENT(t.TABLE_SCHEMA  + '.' + t.TABLE_NAME) * 100.) / 2147483647
   WHEN 'smallint' THEN (IDENT_CURRENT(t.TABLE_SCHEMA  + '.' + t.TABLE_NAME) * 100.) / 32767
   WHEN 'tinyint' THEN (IDENT_CURRENT(t.TABLE_SCHEMA  + '.' + t.TABLE_NAME) * 100.) / 255
  END AS 'PercentageUsed'
 FROM INFORMATION_SCHEMA.COLUMNS AS c
  INNER JOIN
  INFORMATION_SCHEMA.TABLES AS t
  ON c.TABLE_SCHEMA = t.TABLE_SCHEMA AND c.TABLE_NAME = t.TABLE_NAME
 WHERE COLUMNPROPERTY(OBJECT_ID(t.TABLE_SCHEMA + '.' + t.TABLE_NAME), c.COLUMN_NAME, 'isIdentity') = 1

出處：http://wushinetlife.blogspot.tw/2012/03/blog-post_15.html

搜尋Stored Procedure內容

最近在整理 Stored Procedure，順便做系統改版的更新，

所以必須要搜尋有哪些地方用到有修改的欄位，

這時候只好請教 Google，找到一個全文搜尋的方法

select name,text
from syscomments a,sys.objects b
where a.id=b.object_id and b.type='P' and text like '%OOXX%'


原址:http://dba.24zz.com/2012/09/sql-store-procedure.html