SQL Server取得當月第一天及最後一天

方法一：
參考出處
當月第一天
DECLARE @StartTime datetime = DATEADD(day,-DAY(GETDATE())+1, CONVERT(datetime,CONVERT(date, GETDATE())))

當月最後一天
DECLARE @EndTime datetime = DATEADD(ss, -1, DATEADD(m,1,@StartTime))

上月第一天
DECLARE @StartTime datetime = DATEADD(day,-DAY(GETDATE())+1, CONVERT(datetime, CONVERT(date, DATEADD(MM, -1,GETDATE()))))

方法二：
德瑞克：SQL Server 學習筆記

需求：
計算與目前日期為基礎，其相差特定月數的最後一天

公式：

SELECT DATEADD(MM, DATEDIFF(MM, -1, GETDATE()) + <相差特定月數>, 0) - 1 AS N'xxx月最後一天'

 

請參考以下的範例程式碼：

DECLARE @date DATETIME

SET @date = GETDATE()

 

SELECT DATEADD(MM, DATEDIFF(MM, -1, @date), 0) - 1 AS N'本月最後一天' , @date N'今天'

SELECT DATEADD(MM, DATEDIFF(MM, -1, @date) - 1, 0) - 1 AS N'前一個月最後一天' , @date N'今天'

 

SELECT DATEADD(MM, DATEDIFF(MM, -1, @date) + 1, 0) - 1 AS  N'下一個月最後一天' , @date N'今天'

 

SELECT DATEADD(MM, DATEDIFF(MM, -1, @date) - 2, 0) - 1 AS N'前兩個月最後一天' , @date N'今天'

 

SELECT DATEADD(MM, DATEDIFF(MM, -1, @date) + 2, 0) - 1 AS  N'下兩個月最後一天' , @date N'今天'

 

SQL Agent 無法啟動

在SQL Server的組態管理工具中，要啟動SQL Agent 一直失敗，解決方法如下。

1. 開始->SQL Server 2008 R2 –> 組態工具 –> SQL Server 組態管理員

2.組態管理員->SQL Server 服務 –>SQL Server Agent (右鍵內容)

方法一: 選擇本機系統，以後修改了administrator密碼,不用再調整(但要求登陸操作系統的是系統管理員)

方法二: 選擇這個帳戶，以後修改了administrator密碼,還要再重複做上面的操作。

PRIMARY 檔案群組已滿

--先找出DB的邏輯名稱
sp_helpdb DateBaseName
sp_helpfile

--DB的屬性
alter database DateBaseName
modify file
(
 name='edwinbk_100707_log'
 ,maxsize=UNLIMITED
    --,maxsize=42gb
 ,FILEGROWTH=10%
)

如何移除SQL SERVER 2012

要怎麼樣才能把SQL SERVER 2012 移除的乾淨呢

先到命令提示CMD

看自己的檔案路徑通常會是

CD "C:\Program Files\Microsoft SQL Server\110\Setup Bootstrap\SQLServer2012"

然後再輸入Setup.exe /action=uninstall

就可以了

提升資料庫效能-索引

資料庫使用3、4年後，常出現資料庫效能問題。

在設定資料庫備份設定時，多多利用SQL的維護計劃
加入設定重建索引及其他增進資料庫效能的設定，
讓每日由SQL自行進行系統最佳化。

要了解相關內容，可參考如下：

http://www.wretch.cc/blog/wthomasu/9827849

http://www.test104.com/tw/tech/9017.html

以下的每日維護計劃為例。

 

整理索引碎片，提升SQL Server速度

轉載自整理索引碎片，提升SQL Server速度

資料庫表A有十萬條記錄，查詢速度本來還可以，但導入一千條資料後，問題出現了。當選擇的資料在原十萬條記錄之間時，速度還是挺快的；但當選擇的資料在這一千條資料之間時，速度變得奇慢。
憑經驗，這是索引碎片問題。檢查索引碎片DBCC SHOWCONTIG（表），得到如下結果：
DBCC SHOWCONTIG 正在掃描 'A' 表……
表： 'A'（884198200）；索引 ID： 1，資料庫 ID： 13
已執行 TABLE 級別的掃描。
- 掃描頁數……： 3127
- 掃描擴展盤區數……： 403
- 擴展盤區開關數……： 1615
- 每個擴展盤區上的平均頁數……： 7.8
- 掃描密度[最佳值：實際值]……： 24.20%[391：1616]
- 邏輯掃描碎片……： 68.02%
- 擴展盤區掃描碎片……： 38.46%
- 每頁上的平均可用位元組數……： 2073.2
- 平均頁密度（完整）……： 74.39%
DBCC 執行完畢。如果 DBCC 輸出了錯誤資訊，請與系統管理員聯繫。
由上我們看出，邏輯掃描碎片和擴展盤區掃描碎片都非常大，果真需要對索引碎片進行處理了。
一般有兩種方法解決，一是利用DBCC INDEXDEFRAG整理索引碎片，二是利用DBCC DBREINDEX重建索引。二者各有優缺點。調用微軟的原話如下：
DBCC INDEXDEFRAG 命令是線上作業，所以索引只有在該命令正在運行時才可用。而且可以在不丟失已完成工作的情況下中斷該操作。這種方法的缺點是在重新組織資料方面沒有聚集索引的除去/重新創建操作有效。
重新創建聚集索引將對資料進行重新組織，其結果是使資料頁填滿。填滿程度可以使用 FILLFACTOR 選項進行配置。這種方法的缺點是索引在除去/重新創建週期內為脫機狀態，並且操作屬原子級。如果中斷索引創建，則不會重新創建該索引。
也就是說，要想獲得好的效果，還是得用重建索引，所以決定重建索引。
DBCC DBREINDEX（表，索引名，填充因數）
第一個參數，可以是表名，也可以是表ID.
第二個參數，如果是''，表示影響該表的所有索引。
第三個參數，填充因數，即索引頁的資料填充程度。如果是100，表示每一個索引頁都全部填滿，此時select效率最高，但以後要插入索引時，就得移動後面的所有頁，效率很低。如果是0，表示使用先前的填充因數值。
DBCC DBREINDEX（A，''，100）
重新測試查詢速度，飛快。
另：一般來說填充因數使用系統默認的值即可。

索引重整與效率

轉載自索引重整與效率

剛接觸程式設計工作時，只知道設定索引是一件很重要的事
依著前輩的指點，囫圇吞棗般只會把常query或是關鍵欄位設定為索引...
直到一次，不知情的同事將一個近百萬筆資料table的欄位中，
連包含非key值的長字串資料欄位全都設為索引之後....
這動作一下..效能盡失，最後嚴重拖垮整個資料庫服務
此時，我才知道「會不會」設索引，才是一件重要的事~~~
最近利用DBCC SHOWCONTIG這指令去觀查幾個資料量較多的table
在觀察數據的同時，對於DBCC INDEXDEFRAG和DBCC DBREINDEX兩指令又有一新認知。
step1.執行 DBCC SHOWCONTIG(my_table)
單純針對table進行層級掃描先取得分頁相關資訊
- 掃描的分頁................................: 64
- 掃描的範圍..............................: 11
- 範圍切換..............................: 12
- 每個範圍的平均分頁........................: 5.8
- 掃描密度 [最佳次數:實際次數].......: 33.54% [4:13]
- 邏輯掃描片段 ..................: 53.13%
- 範圍掃描片段 ...................: 36.36%
- 每個分頁的平均可用位元組.....................: 376.5
- 平均分頁密度 (全滿).....................: 95.35%
step2.執行 DBCC INDEXDEFRAG (MyDB, 'my_table',PK_my_table)
執行破碎頁面的整理後，掃描密度明顯地變大，分頁程度變得更緊密更平均。
邏輯掃描片段(cluster index叢集索引的葉層分頁狀況)愈大表示指標的指向愈紊亂，
也就是說「失序」頁面的比例，重整破碎頁面後確實有得到明顯的效果。
- 掃描的分頁................................: 63
- 掃描的範圍..............................: 9
- 範圍切換..............................: 8
- 每個範圍的平均分頁........................: 7.0
- 掃描密度 [最佳次數:實際次數].......: 88.89% [8:9]
- 邏輯掃描片段 ..................: 0.00%
- 範圍掃描片段 ...................: 44.44%
- 每個分頁的平均可用位元組.....................: 254.0
- 平均分頁密度 (全滿).....................: 96.86%
step3.執行 DBCC DBREINDEX (my_table,'' ,90)
執行索引的重整，並依需求重新設定填滿因子
(這個TABLE 查詢的比例多，資料異動機率並不是很頻繁，因此設為90%)
發現掃描密度、範圍掃描片段、分頁平均可用位元組得到更多的改善空間。
- 掃描的分頁................................: 68
- 掃描的範圍..............................: 9
- 範圍切換..............................: 8
- 每個範圍的平均分頁........................: 7.6
- 掃描密度 [最佳次數:實際次數].......: 100.00% [9:9]
- 邏輯掃描片段 ..................: 0.00%
- 範圍掃描片段 ...................: 0.00%
- 每個分頁的平均可用位元組.....................: 830.6
- 平均分頁密度 (全滿).....................: 89.74%
後記：
索引重整就像梳頭髮一樣，原本雜亂的頭髮愈梳他就會愈順
定期整理這些資料量大的table，讓index分頁的b-tree目錄在搜尋資料時發揮應有的效能，
索引一旦建立之後，應該更需要去維護它，放著不管最終反而變成是影響效能的因素之一。
但是索引並不是完全只有好處，異動頻繁的table除了不要建立太多的索引之外
對於索引的填滿率(填滿因子)更需注意，保留適當的空間，讓新刪修頻繁的table展現最好的效益。
微軟的官方文件中也有提到....
下一版的 Microsoft SQL Server 將不再提供此功能。
請避免在新的開發工作中使用這項功能，並規劃修改目前使用這項功能的應用程式。 請改用 ALTER INDEX。

SQL服務Client無法連線解決方式

當SQL Server有超過一個具名管道時(安裝2個以上的SQL Server服務)，會發生本機可以連上服務，但遠端無法連上之問題，有可能是SQL Browser或具名管道的Port被防火牆擋住了。

解決步驟如下：

1.先確定SQL Browser的Port(預設值：1434)是否有加入防火牆

2.檢查各具名管道所使用的POTR

先開啟SQL Server組態管理員

第一個安裝的SQL Server服務預設Port是：1433

第二個安裝的SQL Server服務則需指定通訊Port

將所使用的Port設入防火牆的例外清單

運作原理：當SQL Serer只安裝一個服務時，預設會用Port 1433進行溝通，但若裝了二個以上的服務則需使用具名管道，當用具名管道提出服務時，SQL 主機會依據具名管道，透過SQL Browser去尋找對應服務的Port，再將Port回傳給Client端，Client再依據此Port提出服務需求，當防火牆沒有將此Port設定在例外清單時，Client提出的服務則會被防火牆擋住，導致無法連線。

Note：

利用Dos指令 netstat – a可以檢視目前主機監聽的Port有哪些

SQL Server服務的Port：1433

SQL Browser的Port：1434

SQL2008 LOCK解決方案

--顯示被lock之序號

SP_LOCK

--刪除LOCK spid

KILL spid

--列出最初鎖住資源，導致一連串其它處理序被鎖住的起始源頭
IF EXISTS(SELECT * FROM master.dbo.sysprocesses WHERE spid in (SELECT blocked FROM master.dbo.sysprocesses))
BEGIN
SELECT spid 處理序, status 狀態
, 登入帳號=SUBSTRING(SUSER_SNAME(sid), 1, 30)
, 使用者機器名稱=SUBSTRING(hostname, 1, 12)
, 是否被鎖住=CONVERT(char(3), blocked)
, 資料庫名稱=SUBSTRING(DB_NAME(dbid), 1, 10)
, cmd 命令, waittype 等待狀態
FROM master.dbo.sysprocesses
--列出鎖住別(在別的處理序中，blocked欄位出現的值)，但自己未被鎖住(blocked=0)
WHERE spid IN (SELECT blocked FROM master.dbo.sysprocesses)
AND blocked = 0
END
ELSE
BEGIN
  SELECT '沒有處理序被鎖住'
END

--開啟進階顯示(SQL2008以後需開啟)
EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE WITH OVERRIDE
GO

--取消平行處理
EXEC sp_configure 'max degree of parallelism', 1
GO
RECONFIGURE WITH OVERRIDE
GO

轉換腔調字

如題：簡/繁體依腔調字做定序，若定序不一致，JOIN資料表時會有問題

簡體建立資料表時需指定腔調字

--繁體轉簡體

  ALTER TABLE FDRCALSI

  ALTER COLUMN RECMAN varchar(10) COLLATE Chinese_PRC_CI_AS  

在SQL內用參數傳入執行(以備份資料庫為範例)

--將自動備份寫成PROCEDURE
CREATE  PROCEDURE DBDACKUP AS
DECLARE  @BACKDATE VARCHAR(8)
DECLARE @ACOMMAND VARCHAR(200)
--備份日期
SET  @BACKDATE  =  CONVERT(VARCHAR(8),  GETDATE(),  112)
--備份指令
SET @ACOMMAND =
'BACKUP DATABASE [QRSP_HM] TO  DISK = N''D:\databak\QRSP_HM_backup_'+@BACKDATE+'.bak'''
+'WITH NOFORMAT, NOINIT,  NAME = N''QRSP_HM_backup_'+@BACKDATE+'.bak'''+', SKIP, REWIND, NOUNLOAD,  STATS = 10'
--執行備份指令
--因指令內有包含變數(@BACKDATE)，所以需用EXEC方式執行
EXEC(@ACOMMAND)

--刪除備份檔(保留21天)
SET  @BACKDATE  =  CONVERT(VARCHAR(8),  GETDATE()-21,  112)
SET @ACOMMAND = 'MASTER.dbo.xp_delete_file 0,N''D:\databak'',N''BAK'',N'''+@BACKDATE+''''
EXEC(@ACOMMAND)

在win2008上面安裝sql2008後注意事項

如果安裝了win2008及sql2008之後，發現ap無法連到sql這台主機的sql服務，請注意以下幾點：

1、防火牆(依網頁上面來執行開啟就好，不用再手動設定防火牆了)：http://support.microsoft.com/kb/968872/zh-tw

2、檢查sql主機的sql組態設定，確定所有的服務都有開(今天裝的主機就沒開)

A. 連線設定：via不要開，其它全開

B. SQL browser的服務要開。

Note:手動將SqlServer防火牆打開

1.將SQLSERVER程式手動加入允許清單

2.將以下指令做成批次檔(OpenSqlServerPort.bat)執行：

@echo ========= SQL Server Ports ===================
@echo Enabling SQLServer default instance port 1433
netsh firewall set portopening TCP 1433 "SQLServer"
@echo Enabling Dedicated Admin Connection port 1434
netsh firewall set portopening TCP 1434 "SQL Admin Connection"
@echo Enabling conventional SQL Server Service Broker port 4022
netsh firewall set portopening TCP 4022 "SQL Service Broker"
@echo Enabling Transact-SQL Debugger/RPC port 135
netsh firewall set portopening TCP 135 "SQL Debugger/RPC"
@echo ========= Analysis Services Ports ==============
@echo Enabling SSAS Default Instance port 2383
netsh firewall set portopening TCP 2383 "Analysis Services"
@echo Enabling SQL Server Browser Service port 2382
netsh firewall set portopening TCP 2382 "SQL Browser"
@echo ========= Misc Applications ==============
@echo Enabling HTTP port 80
netsh firewall set portopening TCP 80 "HTTP"
@echo Enabling SSL port 443
netsh firewall set portopening TCP 443 "SSL"
@echo Enabling port for SQL Server Browser Service's 'Browse' Button
netsh firewall set portopening UDP 1434 "SQL Browser"
@echo Allowing multicast broadcast response on UDP (Browser Service Enumerations OK)
netsh firewall set multicastbroadcastresponse ENABLE

SQL語句導入導出大全[轉載]

以下文章轉錄自：SQL server匯入 Access檔

USE test
GO
SELECT * INTO zone FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',  
'd:\test\test.mdb';'admin';'',filename)

匯入Excel檔

SELECT * INTO zone FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',  
'Excel 8.0;Database=D:\test\data.xls', [sheetname$]) 

/*******  導出到excel
EXEC master..xp_cmdshell 'bcp SettleDB.dbo.shanghu out c:\temp1.xls -c -q -S"GNETDATA/GNETDATA" -U"sa" -P""'

/***********  導入Excel
SELECT *
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
  'Data Source="c:\test.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...xactions

/*動態文件名
declare @fn varchar(20),@s varchar(1000)
set @fn = 'c:\test.xls'
set @s ='''Microsoft.Jet.OLEDB.4.0'',
''Data Source="'+@fn+'";User ID=Admin;Password=;Extended properties=Excel 5.0'''
set @s = 'SELECT * FROM OpenDataSource ('+@s+')...sheet1$'
exec(@s)
*/

SELECT cast(cast(科目編號 as numeric(10,2)) as nvarchar(255))+'　' 轉換後的別名
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
  'Data Source="c:\test.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...xactions

/********************** EXCEL導到遠程SQL
insert OPENDATASOURCE(
         'SQLOLEDB',
         'Data Source=遠程ip;User ID=sa;Password=密碼'
         ).庫名.dbo.表名 (列名1,列名2)
SELECT 列名1,列名2
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
  'Data Source="c:\test.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...xactions

/** 導入文本文件
EXEC master..xp_cmdshell 'bcp dbname..tablename in c:\DT.txt -c -Sservername -Usa -Ppassword'

/** 導出文本文件
EXEC master..xp_cmdshell 'bcp dbname..tablename out c:\DT.txt -c -Sservername -Usa -Ppassword'
或
EXEC master..xp_cmdshell 'bcp "Select * from dbname..tablename" queryout c:\DT.txt -c -Sservername -Usa -Ppassword'

導出到TXT文本，用逗號分開
exec master..xp_cmdshell 'bcp "庫名..表名" out "d:\tt.txt" -c -t ,-U sa -P password'

BULK INSERT 庫名..表名
FROM 'c:\test.txt'
WITH (
    FIELDTERMINATOR = ';',
    ROWTERMINATOR = '\n'
)

--/* dBase IV文件
select * from
OPENROWSET('MICROSOFT.JET.OLEDB.4.0'
,'dBase IV;HDR=NO;IMEX=2;DATABASE=C:\','select * from [客戶資料4.dbf]')
--*/

--/* dBase III文件
select * from
OPENROWSET('MICROSOFT.JET.OLEDB.4.0'
,'dBase III;HDR=NO;IMEX=2;DATABASE=C:\','select * from [客戶資料3.dbf]')
--*/

--/* FoxPro 數據庫
select * from openrowset('MSDASQL',
'Driver=Microsoft Visual FoxPro Driver;SourceType=DBF;SourceDB=c:\',
'select * from [aa.DBF]')
--*/

/**************導入DBF文件****************/
select * from openrowset('MSDASQL',
'Driver=Microsoft Visual FoxPro Driver;
SourceDB=e:\VFP98\data;
SourceType=DBF',
'select * from customer where country != "USA" order by country')
go
/***************** 導出到DBF ***************/
如果要導出數據到已經生成結構(即現存的)FOXPRO表中,可以直接用下面的SQL語句

insert into openrowset('MSDASQL',
'Driver=Microsoft Visual FoxPro Driver;SourceType=DBF;SourceDB=c:\',
'select * from [aa.DBF]')
select * from 表

說明:
SourceDB=c:\  指定foxpro表所在的文件夾
aa.DBF        指定foxpro表的文件名.

/*************導出到Access********************/
insert into openrowset('Microsoft.Jet.OLEDB.4.0',
   'x:\A.mdb';'admin';'',A表) select * from 數據庫名..B表

/*************導入Access********************/
insert into B表 selet * from openrowset('Microsoft.Jet.OLEDB.4.0',
   'x:\A.mdb';'admin';'',A表)

文件名為參數
declare @fname varchar(20)
set @fname = 'd:\test.mdb'
exec('SELECT a.* FROM opendatasource(''Microsoft.Jet.OLEDB.4.0'',
'''+@fname+''';''admin'';'''', topics) as a ')

SELECT *
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
  'Data Source="f:\northwind.mdb";Jet OLEDB:Database Password=123;User ID=Admin;Password=;')...產品

*********************  導入 xml　文件

DECLARE @idoc int
DECLARE @doc varchar(1000)
--sample XML document
SET @doc ='
      Customer was very satisfied
            Important
            Happy Customer.
'
-- Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc

-- Execute a SELECT statement using OPENXML rowset provider.
SELECT *
FROM OPENXML (@idoc, '/root/Customer/Order', 1)
      WITH (oid     char(5),
            amount  float,
            comment ntext 'text()')
EXEC sp_xml_removedocument @idoc

???????

/**********************Excel導到Txt****************************************/
想用
select * into opendatasource(...) from opendatasource(...)
實現將一個Excel文件內容導入到一個文本文件

假設Excel中有兩列，第一列為姓名，第二列為很行帳號(16位)
且銀行帳號導出到文本文件後分兩部分，前8位和後8位分開。

鄒健：
如果要用你上面的語句插入的話,文本文件必須存在,而且有一行:姓名,銀行賬號1,銀行賬號2
然後就可以用下面的語句進行插入
注意文件名和目錄根據你的實際情況進行修改.

insert into
opendatasource('MICROSOFT.JET.OLEDB.4.0'
,'Text;HDR=Yes;DATABASE=C:\'
)...[aa#txt]
--,aa#txt)
--*/
select 姓名,銀行賬號1=left(銀行賬號,8),銀行賬號2=right(銀行賬號,8)
from
opendatasource('MICROSOFT.JET.OLEDB.4.0'
,'Excel 5.0;HDR=YES;IMEX=2;DATABASE=c:\a.xls'
--,Sheet1$)
)...[Sheet1$]

如果你想直接插入並生成文本文件,就要用bcp

declare @sql varchar(8000),@tbname varchar(50)

--首先將excel表內容導入到一個全局臨時表
select @tbname='[##temp'+cast(newid() as varchar(40))+']'
,@sql='select 姓名,銀行賬號1=left(銀行賬號,8),銀行賬號2=right(銀行賬號,8)
into '+@tbname+' from
opendatasource(''MICROSOFT.JET.OLEDB.4.0''
,''Excel 5.0;HDR=YES;IMEX=2;DATABASE=c:\a.xls''
)...[Sheet1$]'
exec(@sql)

--然後用bcp從全局臨時表導出到文本文件
set @sql='bcp "'+@tbname+'" out "c:\aa.txt" /S"(local)" /P"" /c'
exec master..xp_cmdshell @sql

--刪除臨時表
exec('drop table '+@tbname)

/********************導整個數據庫*********************************************/

用bcp實現的存儲過程

/*
實現數據導入/導出的存儲過程
         根據不同的參數,可以實現導入/導出整個數據庫/單個表
調用示例:
--導出調用示例
----導出單個表
exec file2table 'zj','','','xzkh_sa..地區資料','c:\zj.txt',1
----導出整個數據庫
exec file2table 'zj','','','xzkh_sa','C:\docman',1

--導入調用示例
----導入單個表
exec file2table 'zj','','','xzkh_sa..地區資料','c:\zj.txt',0
----導入整個數據庫
exec file2table 'zj','','','xzkh_sa','C:\docman',0

*/
if exists(select 1 from sysobjects where name='File2Table' and objectproperty(id,'IsProcedure')=1)
drop procedure File2Table
go
create procedure File2Table
@servername varchar(200)  --服務器名
,@username varchar(200)   --用戶名,如果用NT驗證方式,則為空''
,@password varchar(200)   --密碼
,@tbname varchar(500)   --數據庫.dbo.表名,如果不指定:.dbo.表名,則導出數據庫的所有用戶表
,@filename varchar(1000)  --導入/導出路徑/文件名,如果@tbname參數指明是導出整個數據庫,則這個參數是文件存放路徑,文件名自動用表名.txt
,@isout bit      --1為導出,0為導入
as
declare @sql varchar(8000)

if @tbname like '%.%.%' --如果指定了表名,則直接導出單個表
begin
set @sql='bcp '+@tbname
  +case when @isout=1 then ' out ' else ' in ' end
  +' "'+@filename+'" /w'
  +' /S '+@servername
  +case when isnull(@username,'')='' then '' else ' /U '+@username end
  +' /P '+isnull(@password,'')
exec master..xp_cmdshell @sql
end
else
begin --導出整個數據庫,定義游標,取出所有的用戶表
declare @m_tbname varchar(250)
if right(@filename,1)<>'\' set @filename=@filename+'\'

set @m_tbname='declare #tb cursor for select name from '+@tbname+'..sysobjects where xtype=''U'''
exec(@m_tbname)
open #tb
fetch next from #tb into @m_tbname
while @@fetch_status=0
begin
  set @sql='bcp '+@tbname+'..'+@m_tbname
   +case when @isout=1 then ' out ' else ' in ' end
   +' "'+@filename+@m_tbname+'.txt " /w'
   +' /S '+@servername
   +case when isnull(@username,'')='' then '' else ' /U '+@username end
   +' /P '+isnull(@password,'')
  exec master..xp_cmdshell @sql
  fetch next from #tb into @m_tbname
end
close #tb
deallocate #tb
end
go

/************* Oracle **************/
EXEC sp_addlinkedserver 'OracleSvr',
   'Oracle 7.3',
   'MSDAORA',
   'ORCLDB'
GO

delete from openquery(mailser,'select *  from yulin')

select *  from openquery(mailser,'select *  from yulin')

update openquery(mailser,'select * from  yulin where id=15')set disorder=555,catago=888

insert into openquery(mailser,'select disorder,catago from  yulin')values(333,777)

補充：

對於用bcp導出,是沒有字段名的.

用openrowset導出,需要事先建好表.

用openrowset導入,除ACCESS及EXCEL外,均不支持非本機數據導入

/***********  導入Excel
SELECT *
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
  'Data Source="c:\test.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...xactions

/*動態文件名
declare @fn varchar(20),@s varchar(1000)
set @fn = 'c:\test.xls'
set @s ='''Microsoft.Jet.OLEDB.4.0'',
''Data Source="'+@fn+'";User ID=Admin;Password=;Extended properties=Excel 5.0'''
set @s = 'SELECT * FROM OpenDataSource ('+@s+')...sheet1$'
exec(@s)
*/

SELECT cast(cast(科目編號 as numeric(10,2)) as nvarchar(255))+'　' 轉換後的別名
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
  'Data Source="c:\test.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...xactions

/********************** EXCEL導到遠程SQL
insert OPENDATASOURCE(
         'SQLOLEDB',
         'Data Source=遠程ip;User ID=sa;Password=密碼'
         ).庫名.dbo.表名 (列名1,列名2)
SELECT 列名1,列名2
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
  'Data Source="c:\test.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...xactions

/** 導入文本文件
EXEC master..xp_cmdshell 'bcp dbname..tablename in c:\DT.txt -c -Sservername -Usa -Ppassword'

/** 導出文本文件
EXEC master..xp_cmdshell 'bcp dbname..tablename out c:\DT.txt -c -Sservername -Usa -Ppassword'
或
EXEC master..xp_cmdshell 'bcp "Select * from dbname..tablename" queryout c:\DT.txt -c -Sservername -Usa -Ppassword'

導出到TXT文本，用逗號分開
exec master..xp_cmdshell 'bcp "庫名..表名" out "d:\tt.txt" -c -t ,-U sa -P password'

BULK INSERT 庫名..表名
FROM 'c:\test.txt'
WITH (
    FIELDTERMINATOR = ';',
    ROWTERMINATOR = '\n'
)

--/* dBase IV文件
select * from
OPENROWSET('MICROSOFT.JET.OLEDB.4.0'
,'dBase IV;HDR=NO;IMEX=2;DATABASE=C:\','select * from [客戶資料4.dbf]')
--*/

--/* dBase III文件
select * from
OPENROWSET('MICROSOFT.JET.OLEDB.4.0'
,'dBase III;HDR=NO;IMEX=2;DATABASE=C:\','select * from [客戶資料3.dbf]')
--*/

--/* FoxPro 數據庫
select * from openrowset('MSDASQL',
'Driver=Microsoft Visual FoxPro Driver;SourceType=DBF;SourceDB=c:\',
'select * from [aa.DBF]')
--*/

/**************導入DBF文件****************/
select * from openrowset('MSDASQL',
'Driver=Microsoft Visual FoxPro Driver;
SourceDB=e:\VFP98\data;
SourceType=DBF',
'select * from customer where country != "USA" order by country')
go
/***************** 導出到DBF ***************/
如果要導出數據到已經生成結構(即現存的)FOXPRO表中,可以直接用下面的SQL語句

insert into openrowset('MSDASQL',
'Driver=Microsoft Visual FoxPro Driver;SourceType=DBF;SourceDB=c:\',
'select * from [aa.DBF]')
select * from 表

說明:
SourceDB=c:\  指定foxpro表所在的文件夾
aa.DBF        指定foxpro表的文件名.

/*************導出到Access********************/
insert into openrowset('Microsoft.Jet.OLEDB.4.0',
   'x:\A.mdb';'admin';'',A表) select * from 數據庫名..B表

/*************導入Access********************/
insert into B表 selet * from openrowset('Microsoft.Jet.OLEDB.4.0',
   'x:\A.mdb';'admin';'',A表)

文件名為參數
declare @fname varchar(20)
set @fname = 'd:\test.mdb'
exec('SELECT a.* FROM opendatasource(''Microsoft.Jet.OLEDB.4.0'',
'''+@fname+''';''admin'';'''', topics) as a ')

SELECT *
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
  'Data Source="f:\northwind.mdb";Jet OLEDB:Database Password=123;User ID=Admin;Password=;')...產品

*********************  導入 xml　文件

DECLARE @idoc int
DECLARE @doc varchar(1000)
--sample XML document
SET @doc ='
      Customer was very satisfied
            Important
            Happy Customer.
'
-- Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc

-- Execute a SELECT statement using OPENXML rowset provider.
SELECT *
FROM OPENXML (@idoc, '/root/Customer/Order', 1)
      WITH (oid     char(5),
            amount  float,
            comment ntext 'text()')
EXEC sp_xml_removedocument @idoc

???????

/**********************Excel導到Txt****************************************/
想用
select * into opendatasource(...) from opendatasource(...)
實現將一個Excel文件內容導入到一個文本文件

假設Excel中有兩列，第一列為姓名，第二列為很行帳號(16位)
且銀行帳號導出到文本文件後分兩部分，前8位和後8位分開。

鄒健：
如果要用你上面的語句插入的話,文本文件必須存在,而且有一行:姓名,銀行賬號1,銀行賬號2
然後就可以用下面的語句進行插入
注意文件名和目錄根據你的實際情況進行修改.

insert into
opendatasource('MICROSOFT.JET.OLEDB.4.0'
,'Text;HDR=Yes;DATABASE=C:\'
)...[aa#txt]
--,aa#txt)
--*/
select 姓名,銀行賬號1=left(銀行賬號,8),銀行賬號2=right(銀行賬號,8)
from
opendatasource('MICROSOFT.JET.OLEDB.4.0'
,'Excel 5.0;HDR=YES;IMEX=2;DATABASE=c:\a.xls'
--,Sheet1$)
)...[Sheet1$]

如果你想直接插入並生成文本文件,就要用bcp

declare @sql varchar(8000),@tbname varchar(50)

--首先將excel表內容導入到一個全局臨時表
select @tbname='[##temp'+cast(newid() as varchar(40))+']'
,@sql='select 姓名,銀行賬號1=left(銀行賬號,8),銀行賬號2=right(銀行賬號,8)
into '+@tbname+' from
opendatasource(''MICROSOFT.JET.OLEDB.4.0''
,''Excel 5.0;HDR=YES;IMEX=2;DATABASE=c:\a.xls''
)...[Sheet1$]'
exec(@sql)

--然後用bcp從全局臨時表導出到文本文件
set @sql='bcp "'+@tbname+'" out "c:\aa.txt" /S"(local)" /P"" /c'
exec master..xp_cmdshell @sql

--刪除臨時表
exec('drop table '+@tbname)

/********************導整個數據庫*********************************************/

用bcp實現的存儲過程

/*
實現數據導入/導出的存儲過程
         根據不同的參數,可以實現導入/導出整個數據庫/單個表
調用示例:
--導出調用示例
----導出單個表
exec file2table 'zj','','','xzkh_sa..地區資料','c:\zj.txt',1
----導出整個數據庫
exec file2table 'zj','','','xzkh_sa','C:\docman',1

--導入調用示例
----導入單個表
exec file2table 'zj','','','xzkh_sa..地區資料','c:\zj.txt',0
----導入整個數據庫
exec file2table 'zj','','','xzkh_sa','C:\docman',0

*/
if exists(select 1 from sysobjects where name='File2Table' and objectproperty(id,'IsProcedure')=1)
drop procedure File2Table
go
create procedure File2Table
@servername varchar(200)  --服務器名
,@username varchar(200)   --用戶名,如果用NT驗證方式,則為空''
,@password varchar(200)   --密碼
,@tbname varchar(500)   --數據庫.dbo.表名,如果不指定:.dbo.表名,則導出數據庫的所有用戶表
,@filename varchar(1000)  --導入/導出路徑/文件名,如果@tbname參數指明是導出整個數據庫,則這個參數是文件存放路徑,文件名自動用表名.txt
,@isout bit      --1為導出,0為導入
as
declare @sql varchar(8000)

if @tbname like '%.%.%' --如果指定了表名,則直接導出單個表
begin
set @sql='bcp '+@tbname
  +case when @isout=1 then ' out ' else ' in ' end
  +' "'+@filename+'" /w'
  +' /S '+@servername
  +case when isnull(@username,'')='' then '' else ' /U '+@username end
  +' /P '+isnull(@password,'')
exec master..xp_cmdshell @sql
end
else
begin --導出整個數據庫,定義游標,取出所有的用戶表
declare @m_tbname varchar(250)
if right(@filename,1)<>'\' set @filename=@filename+'\'

set @m_tbname='declare #tb cursor for select name from '+@tbname+'..sysobjects where xtype=''U'''
exec(@m_tbname)
open #tb
fetch next from #tb into @m_tbname
while @@fetch_status=0
begin
  set @sql='bcp '+@tbname+'..'+@m_tbname
   +case when @isout=1 then ' out ' else ' in ' end
   +' "'+@filename+@m_tbname+'.txt " /w'
   +' /S '+@servername
   +case when isnull(@username,'')='' then '' else ' /U '+@username end
   +' /P '+isnull(@password,'')
  exec master..xp_cmdshell @sql
  fetch next from #tb into @m_tbname
end
close #tb
deallocate #tb
end
go

/************* Oracle **************/
EXEC sp_addlinkedserver 'OracleSvr',
   'Oracle 7.3',
   'MSDAORA',
   'ORCLDB'
GO

delete from openquery(mailser,'select *  from yulin')

select *  from openquery(mailser,'select *  from yulin')

update openquery(mailser,'select * from  yulin where id=15')set disorder=555,catago=888

insert into openquery(mailser,'select disorder,catago from  yulin')values(333,777)

補充：

對於用bcp導出,是沒有字段名的.

用openrowset導出,需要事先建好表.

用openrowset導入,除ACCESS及EXCEL外,均不支持非本機數據導入

DBCC CHECKDB 與 DBCC CHECKTABLE 搭配使用 REPAIR_REBUILD 修復參數

DBCC CHECKDB 的檢查與修復對象是指定的資料庫。
DBCC CHECKTABLE 的檢查與修復對象是指定的資料表或索引檢視表。
DBCC CHECKDB 與 DBCC CHECKTABLE 都支援修復的作業，以下為參數的說明：
(1) REPAIR_ALLOW_DATA_LOSS
嘗試修復所有報告的錯誤。這些修復可能會造成某些資料的遺失。
(2) REPAIR_FAST
維護這個語法的目的，只是為了與舊版相容。不會執行任何修復動作。
(3) REPAIR_REBUILD
執行不可能造成資料遺失的修復，這可包括快速修復 (例如，修復非叢集索引中遺失的資料列) 以及更耗時的修復 (例如，重建索引)。
REPAIR_REBUILD 不會修復與 FILESTREAM 資料有關的錯誤。

而且，指定的資料庫必須為「單一使用者模式」。
重要事項：
* 最好不要使用這些 REPAIR 選項。
* 若要修復錯誤，我們建議您從備份中還原。
* 修復作業並不考慮資料表或資料表之間的任何條件約束。
* 如果指定的資料表涉及一或多項條件約束，建議您在修復作業之後，執行 DBCC CHECKCONSTRAINTS。
* 如果您必須使用 REPAIR，請執行不含修復選項的 DBCC CHECKDB 來尋找要使用的修復層級。
* 如果您使用 REPAIR_ALLOW_DATA_LOSS 層級，建議您在搭配這個選項執行 DBCC CHECKDB 之前，先備份資料庫。
以下為 DBCC CHECKDB 搭配使用 REPAIR_REBUILD 修復參數的範例程式碼：

USE master
GO
-- 先設定此資料庫的狀態為：SINGLE_USER
ALTER DATABASE [資料庫名稱]
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
GO

-- 執行 DBCC CHECKDB 搭配使用 REPAIR_REBUILD 參數
DBCC CHECKDB (Northwind, REPAIR_REBUILD);
GO

/*
若是非處於「單一使用者模式」時，將遇到以下的錯誤：
訊息 7919，層級 16，狀態 3，行 1
修復陳述式並未處理。資料庫必須處於單一使用者模式。
*/

-- 設定此資料庫的狀態為：MULTI_USER，回復正常上線
ALTER DATABASE [資料庫名稱]
SET MULTI_USER;
GO
以下為 DBCC CHECKTABLE 搭配使用 REPAIR_REBUILD 修復參數的範例程式碼：

USE master
GO
-- 先設定此資料庫的狀態為：SINGLE_USER
-- 使用終止選項 WITH ROLLBACK IMMEDIATE。所有未完成的交易都會回復，而且資料庫的任何其他連接都會立即中斷
ALTER DATABASE [資料庫名稱]
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
GO

-- 執行 DBCC CHECKTABLE 搭配使用 REPAIR_REBUILD 參數
USE [資料庫名稱]
GO
DBCC CHECKTABLE ([資料表], REPAIR_REBUILD);
GO
/*
若是非處於「單一使用者模式」時，將遇到以下的錯誤：
訊息 7919，層級 16，狀態 3，行 1
修復陳述式並未處理。資料庫必須處於單一使用者模式。
*/

-- 設定此資料庫的狀態為：MULTI_USER，回復正常上線
ALTER DATABASE [資料庫名稱]
SET MULTI_USER;
GO


[SQL]提升查詢效率與避免LOCK發生

以下文章轉貼：湯瑪的吳@安達利.機車行.台南
要提升SQL的查詢效能，一般來說大家會以建立索引(index)為第一考量
其實除了index的建立之外，當我們在下SQL Command時，
在語法中加一段WITH (NOLOCK)
可以改善線上大量查詢的環境中資料集被LOCK的現象藉此改善查詢的效能。

不過有一點千萬要注意的就是，WITH (NOLOCK)的SQL SELECT 有可能會造成Dirty Read。 例如：
SELECT COUNT(UserID)
FROM EMPLOYEE WITH (NOLOCK)
JOIN WORKING_GROUP WITH (NOLOCK) ON
EMPLOYEE.UserID = WORKING_GROUP.UserID

因為SQL Server 會執行對應的鎖定一致性檢查。
欲改善整體資料庫查詢的效能，請將WITH (NOLOCK)加在您的SELECT語法中 Table名稱的後面，
雖然(NOLOCK)也可以，但是微軟還是建議大家要加WITH
除了簡單的SELECT之外，有JOIN 的SELECT語法也是可以使用的。
但是DELETE、INSERT、UPDATE這些需要transaction的指令就不行了…

有些文件說，加了WITH (NOLOCK)的SQL查詢效率可以增加33%
小弟試的結果…是有快了點，但是33%的效能提升…
我就不太清楚是怎麼算出來的!!

加了WITH (NOLOCK)即告訴SQL Server，我們的這段SELECT指令
無需去考慮目前table的transaction lock狀態，因此效能上會有明顯的提升，
而且資料庫系統的Lock現象會有明顯的減少(包含Dead Lock)。

有一點要特別注意，因為WITH (NOLOCK)不考慮目前table的transaction lock，
因此當有某些資料正處於多個phase交易(例如跨多個table的transaction交易-->如提款系統)，
WITH (NOLOCK)會讓目前處理交易process的資料被乎略…

講白話一點，也就是說當使用NoLock時，它允許閱讀那些已經修改但是還沒有交易完成的資料。
因此如果有需要考量transaction交易資料的即時完整性時，使用WITH (NOLOCK)就要好好考慮一下。

如果不需考量transaction，WITH (NOLOCK)或許是個好用的參考

註1：WITH ( < table_hint > )
指定由查詢最佳化器使用的資料表掃描、一或多個索引，
或由查詢最佳化器利用此資料表以及為此陳述式使用鎖定模式。

註2：WITH (NOLOCK)相當於 READ UNCOMMITTED

如何取出數字的第一碼

今天在做報表實有遇到一個需求，就是希望能取出數字的第一碼
例如：73.56 就顯示7折，68.55就顯示6折
SQL 可以如此下
SELECT CAST (SUM(D.RECAMT)/(SUM(D.QTY)*G.SETPRI)*10 AS INT) AS DIS FROM TABLE
概念：就是想辦法將其轉成整數
以上例而言：(SUM(D.RECAMT)/(SUM(D.QTY)*G.SETPRI)計算結果會是0.78(小數以下)
所以先乘上10使其變成7.8
在轉成整數 則會無條件捨去 變成7
之後再轉成字串即可

資料庫 LOG 檔案太大怎麼清除？(MS SQL 清除 LOG 並縮減空間的語法)

一般時候我們都用以下的語法進行清理
DUMP TRANSACTION {DBName} WITH NO_LOG
可是實體的檔案大小卻不會減少，一樣佔用那麼多的硬碟空間。
我曾經在網路上找到以下的語法，分享給大家參考。

SET NOCOUNT ON DECLARE @LogicalFileName sysname, @MaxMinutes INT, @NewSize INT
USE {DBName ex HRMS} -- 要操作的資料庫名
SELECT
@LogicalFileName = '{Log file name ex HRMS_Log}', -- 日誌文件名
@MaxMinutes = 10, -- Limit on time allowed to wrap log. (M)
@NewSize = 1 -- 你想設定的日誌文件的大小(M)

-- Setup / initialize
DECLARE @OriginalSize int
SELECT @OriginalSize = size FROM sysfiles WHERE name = @LogicalFileName

SELECT 'Original Size of ' + db_name() + ' LOG is ' +
CONVERT(VARCHAR(30),@OriginalSize) + ' 8K pages or ' +
CONVERT(VARCHAR(30),(@OriginalSize*8/1024)) + 'MB'
FROM sysfiles WHERE name = @LogicalFileName

CREATE TABLE DummyTrans (DummyColumn char (8000) not null)

DECLARE @Counter INT, @StartTime DATETIME, @TruncLog VARCHAR(255)
SELECT @StartTime = GETDATE(), @TruncLog = 'BACKUP LOG ' + db_name() + ' WITH TRUNCATE_ONLY'

DBCC SHRINKFILE (@LogicalFileName, @NewSize)

EXEC (@TruncLog)

-- Wrap the log if necessary.
WHILE @MaxMinutes > DATEDIFF (mi, @StartTime, GETDATE()) -- time has not expired
AND @OriginalSize = (SELECT size FROM sysfiles WHERE name = @LogicalFileName)
AND (@OriginalSize * 8 /1024) > @NewSize
BEGIN -- Outer loop.
SELECT @Counter = 0
WHILE ((@Counter < @OriginalSize / 16) AND (@Counter < 50000))
BEGIN -- update
INSERT DummyTrans VALUES ('Fill Log')
DELETE DummyTrans
SELECT @Counter = @Counter + 1
END
EXEC (@TruncLog)
END

SELECT 'Final Size of ' + db_name() + ' LOG is ' +
CONVERT(VARCHAR(30),size) + ' 8K pages or ' +
CONVERT(VARCHAR(30),(size*8/1024)) + 'MB'
FROM sysfiles WHERE name = @LogicalFileName

DROP TABLE DummyTrans
SET NOCOUNT OFF

如果不要想得那麼複雜和看那些資訊，直接這樣下眼睛閉一閉就過去了...

USE [@DBName]
BACKUP LOG [@DBName] WITH TRUNCATE_ONLY
DBCC SHRINKFILE (@LogicalFileName, @NewSize)

列出sql索引資料

SELECT T.NAME AS TABLE_NAME, C.NAME AS COLUMN_NAME, K.COLID, I.NAME AS INDEX_NAME, I.INDID, K.KEYNO
FROM SYSINDEXES AS I LEFT JOIN SYSOBJECTS AS T ON I.ID = T.ID LEFT JOIN SYSINDEXKEYS AS K ON I.ID = K.ID AND I.INDID = K.INDID LEFT JOIN SYSCOLUMNS AS C ON I.ID = C.ID AND K.COLID = C.COLID
WHERE T.XTYPE = 'U' AND (I.NAME LIKE 'PK_%' OR I.NAME LIKE 'IX_%')
ORDER BY T.NAME, I.INDID, K.KEYNO, C.NAME

DataBase資訊SQL語法

SELECT NAME, DBID, SID, MODE, STATUS, STATUS2, CRDATE, RESERVED, CATEGORY, CMPTLEVEL, FILENAME, VERSION, ' ' AS YN
FROM master.dbo.sysdatabases
ORDER BY NAME

連接遠端SQL資料庫

以下做法可操作Sql Server遠端主機之資料庫
Step1.必須先將連線的 ANSI_NULLS 與 ANSI_WARNINGS 選項開啟，它才能執行分散式查詢
資料程式庫應用程式連線會將 SET ANSI_WARNINGS 設定為 OFF，所以須手動開啟

SET ANSI_NULLS ON
SET ANSI_WARNINGS ON
或
SET ANSI_DEFAULTS ON

Step2.連接遠端SQL資料庫

'WINSERVER':Sql Server之執行個體，亦可輸入ip，當輸入ip時會自動抓取該台主機之預設值行個體
指定執行個體之方式：'WINSERVER\SQL2005'或'192.168.0.1'或'192.168.0.1\SQL2005'
EXEC sp_addlinkedserver 'WINSERVER', N'SQL Server'
exec sp_addlinkedsrvlogin 'WINSERVER', false, null,'sa','pw'

Step3.操作遠端資料庫之語法

select * from [WINSERVER].STDB_MCN.DBO.FDRCUSMI

Step4.登出遠端資料庫

EXEC sp_droplinkedsrvlogin 'WINSERVER', NULL
EXEC sp_droplinkedsrvlogin 'WINSERVER', 'sa'
exec sp_dropserver 'WINSERVER'

Step5.最後需將開啟之選項關閉

SET ANSI_NULLS OFF
SET ANSI_WARNINGS OFF
或
SET ANSI_DEFAULTS OFF

PS：登入與登出動作可將之寫成STORE PROCDURE

CREATE PROCEDURE [dbo].[PROC_LoginSQLServer]
AS
--Link不存在才建立連結
if not exists( select * from master.dbo.sysservers where srvname= '10.24.13.26')
begin
EXEC sp_addlinkedserver '10.24.13.26', N'SQL Server'
exec sp_addlinkedsrvlogin '10.24.13.26', false, null,'sa','admin'
end


CREATE PROCEDURE [dbo].[PROC_LogOutSQLServer]
AS
--Link存在才刪除連結
if exists( select * from master.dbo.sysservers where srvname= '10.24.13.26')
begin
EXEC sp_droplinkedsrvlogin '10.24.13.26', NULL
EXEC sp_droplinkedsrvlogin '10.24.13.26', 'sa'
exec sp_dropserver '10.24.13.26'
end

NOTE:
//判斷是否有註冊過(登陸遠端機器_sp_addlinkedserver)
select * from master.dbo.sysservers where srvname= '127.0.0.1'