2. 歡迎頁
3. 選取資料庫
4. 設定選項, 記得要選取 "編寫資料的指令碼"
5. 選取資料表
6. 選擇要匯出的資料表
7. 指定 Script 要儲存的位置
8. 檢視摘要
9. 完工
加入可指定資料庫的改良
SELECT TOP 100 execution_count, total_worker_time, last_worker_time, max_worker_time, min_worker_time, CONVERT(INT, att.value) AS dbid, DB_NAME(CONVERT(INT, att.value)) as dbname ,SUBSTRING(st.text, (qs.statement_start_offset/2) + 1,((CASE statement_end_offset WHEN -1 THEN DATALENGTH(st.text)ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) + 1) as statement_text
FROM sys.dm_exec_query_stats as qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as st
CROSS APPLY sys.dm_exec_plan_attributes(qs.plan_handle) att
WHERE att.attribute='dbid' and DB_NAME(CONVERT(INT, att.value)) = 'P9'
ORDER BY execution_count DESC
以執行次數排序，可檢查爆量的Sql
Where statement_start_offset =0 測起來可以篩掉系統的Query，如:AspNet_SqlCache
SELECT TOP 10 execution_count, total_worker_time, last_worker_time, max_worker_time, min_worker_time, SUBSTRING(st.text, (qs.statement_start_offset/2) + 1,((CASE statement_end_offset WHEN -1 THEN DATALENGTH(st.text)ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) + 1) as statement_text
FROM sys.dm_exec_query_stats as qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as st
WHERE statement_start_offset = 0
ORDER BY execution_count DESC
以總執行時間排序，檢查寫耗時的Sql
SELECT TOP 10 total_worker_time, last_worker_time, max_worker_time, min_worker_time, SUBSTRING(st.text, (qs.statement_start_offset/2) + 1,((CASE statement_end_offset WHEN -1 THEN DATALENGTH(st.text)ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) + 1) as statement_text
FROM sys.dm_exec_query_stats as qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as st
ORDER BY max_worker_time DESC
結果如下
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title></title>
<script type="text/javascript" src="/Scripts/jquery-1.7.1.min.js"></script>
<script type="text/javascript">
$(document).ready(function () {
$(window).bind("paste", function () {
if (event.clipboardData) {
var clipboardData, found;
found = false;
clipboardData = event.clipboardData;
Array.prototype.forEach.call(clipboardData.items, function (item, i) {
if (item.type.indexOf("image") !== -1) {
var blob = clipboardData.items[i].getAsFile();
var URL = window.URL || window.webkitURL;
var source = URL.createObjectURL(blob);
pastedImage = new Image();
pastedImage.src = source;
pastedImage.onload = function () {
var canvas = document.createElement("canvas");
var ctx = canvas.getContext("2d");
canvas.width = pastedImage.width;
canvas.height = pastedImage.height;
ctx.drawImage(pastedImage, 0, 0);
var base64 = canvas.toDataURL();
base64 = base64.substring(base64.indexOf("iVBOR"));
$.ajax({
url: "/API/AddTaskImages.aspx",
async: false,
type: "POST",
data: ({
Image: base64
}),
dataType: "html",
success: function (msg) {
var oRes;
try {
oRes = eval(msg);
}
catch (err) {
alert("無法解析傳回資料 !");
alert(msg);
alert(err);
}
if (oRes.success) {
alert("Upload Success..");
//alert(oRes.msg);
$("#MyImageDataLoadDemo").attr("src", oRes.msg);
} else {
alert(oRes.msg);
}
},
error: function (xhr, ajaxOptions, thrownError) {
alert(xhr.status);
alert(thrownError);
}
})
};
return false;
}
});
}
});
});
</script>
</head>
<body>
<div id="hold">
<div id="imgHold"><img src="" id="MyImageDataLoadDemo"></div>
<div>Click on the area above and ctrl+p an image to paste</div>
</div>
</body>
</html>
Server Side 的程式碼如下:
Partial Class API_AddTaskImages
Inherits System.Web.UI.Page
Protected Sub Page_Load(sender As Object, e As System.EventArgs) Handles Me.Load
'DB.sysconfig.Path.TaskAttachment
Dim Base64 As String = Me.Request("Image")
'UW.JSON.WriteErrorMessage(Base64)
'Return
Dim imageBytes As Byte() = Convert.FromBase64String(Me.Request("Image"))
Dim ms As System.IO.MemoryStream = New System.IO.MemoryStream(imageBytes, 0, imageBytes.Length)
ms.Write(imageBytes, 0, imageBytes.Length)
Dim image As System.Drawing.Image = System.Drawing.Image.FromStream(ms, True)
Dim map As Bitmap
Dim Filename As String = Now.ToString("yyyyMMddHHmmssfff") & ".png"
Try
System.IO.Directory.CreateDirectory(DB.sysconfig.Path.TaskAttachment & "UploadImages")
image.Save(DB.sysconfig.Path.TaskAttachment & "UploadImages\" & Filename, System.Drawing.Imaging.ImageFormat.Png)
UW.JSON.WriteSuccessMessage(DB.sysconfig.URL.TaskAttachment & "UploadImages/" & Filename)
Catch ex As Exception
UW.JSON.WriteErrorMessage(ex.ToString)
End Try
End Sub
End Class
vb: a mod b
c#: a % b
*****2012/06/27************************************************
Server.UrlEncode("xxxx") => HttpUtility.UrlEncode("xxxxx");
Response.Write("XXX") => HttpContext.Current.Response.Write("XXX");
******************************************************************
型別轉換(int)XXXXXX，將XXXXX轉換為型別int
轉換Value 至 Enum
oRec.Status = (DB.ForumArticle.EN.Status)Enum.Parse(typeof(DB.ForumArticle.EN.Status), this.ddlENStatus.SelectedValue);
強制轉換型別至int
int Status = (int)DB.ForumArticle.EN.Status.Deleted;
vb
Public Shared Funciont AA() as string
return xxxxx
End Function
C#
public static string aa()
{
return xxxx
}
vb
Public Shared Sub AA()
......
End Sub
C#
public static void AA()
{
.....
}
vb
Try
xxxxxx
Catch ex As Exception
UW.WU.WriteErrorToLog(ex)
End Try
C#
try
{
xxxxxx
}
catch (Exception ex)
{
tran.Rollback();
}
Split
char[] delimiterChars = { ',' };
foreach (string tags in cbTags.Split(delimiterChars))
{
}vb
select case AA
case "AA"
xxx
case "BB"
bbb
case else
aaaa
end select
C#
switch (AA) {
case "AA":
xxx();
break;
case "BB":
bbb();
break;
default:
aaaa();
break;
}
Sample 如下:
Imports System.Drawing.Printing
Namespace X
Partial Public Class PrintSomething
Dim PD As PrintDocumentDim ppc As New Printing.PreviewPrintController()
Dim TotalPage As Int32 = 0
''' <summary>
''' 第幾次列印, 用來控制寫出的圖檔
''' </summary>
''' <remarks></remarks>
Dim PrintCount As Int32 = 1Dim PageCount As Int32 = 1
:
:
:
Sub New(ByVal oPM As DB.PackingListMain, ByVal dtDetail As DataTable, dtUsedCoupon As DataTable)
'初始化資料
:
:
:
End SubSub InitPrintDocument()
'在這裡設定 PrintDocument 的紙張, 印表機, 邊界, 橫印或直印PD = New PrintDocument
:
:
:End Sub
Sub print()
InitPrintDocument()PD.PrintController = ppc
' 第一次先把列印結果存在 Printing.PreviewPrintController 中
AddHandler PD.PrintPage, AddressOf GeneratePreview'再來儲存圖片
AddHandler PD.EndPrint, AddressOf SaveImagePD.Print()
End Sub
Dim ppi() As Printing.PreviewPageInfo
Dim PrintPage As Int32 = 0Sub SaveImage(ByVal sender As Object, ByVal ev As PrintEventArgs)
PrintDebug("Save Image Start, " & Now.ToString("yyyy-MM-dd HH:mm:ss.fff"))
'把圖檔存起來
Dim TF As String = Now.ToString("yyyyMMddHHmmss") ' DB.SysConfig.Path.LocalDataRoot
Dim PathLocal As String = "C:\PrintLog\" & TF.Substring(0, 4) & "-" & TF.Substring(4, 2) & "\"
Dim PathPackingListSheetImage As String = DB.SysConfig.PackingListSheetImage & TF.Substring(0, 4) & "-" & TF.Substring(4, 2) & "\"System.IO.Directory.CreateDirectory(PathLocal)
System.IO.Directory.CreateDirectory(PathPackingListSheetImage)Dim LatestImages As String = ""
ppi = ppc.GetPreviewPageInfo()For x As Integer = 0 To ppi.Length - 1
Dim Filename As String = TF & "_" & Me.oPM.Id & "_" & x & ".png"ppi(x).Image.Save(PathLocal & Filename, System.Drawing.Imaging.ImageFormat.Png)
Next
'把圖檔印出來
For x As Integer = 0 To ppi.Length - 1
'建立新的 PrintDocument
InitPrintDocument()If CST.WebConfig.Server_NAME = "測試主機" Then
UW.WU.DebugWriteLine("PrinterName: " & Me.PD.PrinterSettings.PrinterName)
UW.WU.DebugWriteLine("PaperSize: " & PD.DefaultPageSettings.PaperSize.PaperName)PD.DocumentName = "PackingList"
PD.PrinterSettings.PrintFileName = "C:\PDF\PackingList" & Now.ToString("yyyyMMddHHmmss") & "_" & PrintPage & ".prn"
PD.PrinterSettings.PrintToFile = True'PD.PrinterSettings.
End If'很奇怪, 這裡不會分頁, 所以要一張一張印
AddHandler PD.PrintPage, AddressOf pd_PrintPage2
PD.Print()
PrintPage += 1Next
End SubPrivate Sub pd_PrintPage2(ByVal sender As Object, ByVal ev As PrintPageEventArgs)
'很奇怪, 這裡不會分頁, 所以要一張一張印
ev.Graphics.DrawImage(ppi(PrintPage).Image, 0, 0)
ev.HasMorePages = False
End Sub
Private Sub GeneratePreview(ByVal sender As Object, ByVal ev As PrintPageEventArgs)
' 列印內容
End SubEnd Class
End ClassEnd Namespace
當principal DB異常時，若無見證DB，可以手動執行下面指令，讓mirror DB可以正常運作
ALTER DATABASE [ReikoTEST] SET PARTNER OFF
RESTORE DATABASE [ReikoTEST] WITH RECOVERY
[InvalidOperationException: 由於該物件目前的狀態，導致作業無效。]
System.Web.HttpValueCollection.ThrowIfMaxHttpCollectionKeysExceeded() +2692302
System.Web.HttpValueCollection.FillFromEncodedBytes(Byte[] bytes, Encoding encoding) +61
System.Web.HttpRequest.FillInFormCollection() +148
[HttpException (0x80004005): URL 編碼型式資料無效。]
System.Web.HttpRequest.FillInFormCollection() +206
System.Web.HttpRequest.get_Form() +68
System.Web.HttpRequest.get_HasForm() +8735447
System.Web.UI.Page.GetCollectionBasedOnMethod(Boolean dontReturnNull) +97
System.Web.UI.Page.DeterminePostBackMode() +63
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +133
Error Msg:
若你的 Web 應用程式真的會傳遞超過 1,000 個欄位時，這個預設值也是可以設定的，請修改網站根目錄下的 web.config 檔，並在
<appSettings>
<add key="aspnet:MaxHttpCollectionKeys" value="2500" />
</appSettings>
http://blog.miniasp.com/post/2012/01/01/Efficient-Denial-of-Service-Attacks-on-Web-Application-Platforms.aspx
Principal(來源), Mirror(鏡像), Witness(見證，非必要，系統出現錯誤時，自動切換資料庫)
上方三個資料庫需使用相同版本
/*查資料庫版本號*/
SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'),SERVERPROPERTY ('edition')
參考資料：
MS SQL Server 2008_容錯轉移_資料庫鏡像實作
Simple Step by Step Database Mirroring
SQL Server 2008 的執行個體之間傳送登入和密碼
Troubleshooting Database Mirroring Error 1418
Microsoft SQL 2008 SERVER -- Fix : Error: 1418 Mirroring
/*
DB: Principal
Step 1, 備份來源資料庫
*/
backup database reikoTest to disk='D:\MirrorTEST_Full.bak'
with format
go
backup log reikoTest to disk='D:\MirrorTEST_log.bak'
go
/*
DB: Mirror
Step 2, 建立mirror資料庫
*/
Create database ReikoTEST
go
restore database ReikoTEST from disk='D:\MirrorTEST_Full.bak'
with replace, norecovery
go
restore log ReikoTESt from disk='D:\MirrorTEST_log.bak'
with norecovery
go
/*
DB: Principal
Step 3, 建立SQL Server 2008 的執行個體之間傳送登入和密碼
注意 這個指令碼會在 master 資料庫中建立兩個預存程序。這兩個預存程序名為 sp_hexadecimal 預存程序和 sp_help_revlogin 預存程序。
※第一次建立mirror時(預存程序不存在時)，才需執行本指令
*/
SQL指令碼
/*
DB: Principal
Step 4, 查詢傳送登入帳號和密碼
sp_help_revlogin 預存程序所產生的輸出指令碼是登入指令碼。這個登入指令碼會建立具有原始「安全性識別碼」(SID) 和原始密碼的登入。
*/
EXEC sp_help_revlogin
/*
執行後，複製要使用的帳號，至mirror資料庫執行
ex.
-- Login:reiko
CREATE LOGIN [reiko] WITH PASSWORD = 0x010036D88E850F3F29E70120CB4CD2DEE7CBAD1AB83AA4A6A927 HASHED, SID = 0x495A943A23B12C48A43C39776B879A49, DEFAULT_DATABASE = [master], CHECK_POLICY = OFF, CHECK_EXPIRATION = OFF
*/
/*
DB: Mirror
Step 5, 建立傳送登入帳號和密碼
刪掉DEFAULT_DATABASE = [master],(上方紅字部份)
ex.
-- Login:reiko
CREATE LOGIN [reiko] WITH PASSWORD = 0x010036D88E850F3F29E70120CB4CD2DEE7CBAD1AB83AA4A6A927 HASHED, SID = 0x495A943A23B12C48A43C39776B879A49, CHECK_POLICY = OFF, CHECK_EXPIRATION = OFF
*/
-- Login:reiko
CREATE LOGIN [reiko] WITH PASSWORD = 0x010036D88E850F3F29E70120CB4CD2DEE7CBAD1AB83AA4A6A927 HASHED, SID = 0x495A943A23B12C48A43C39776B879A49, CHECK_POLICY = OFF, CHECK_EXPIRATION = OFF