2016年8月30日 星期二

Delete large amount of data from a table 刪除大量資料作法

Delete large amount of data from a table
刪除大量資料作法
from my MSDN blog - August 30, 2016

Method 1

若刪除完成之後留下的資料較多的話(例如要刪除1/3的資料),就用WHILE DELETE top語法來刪除
declare @n int
while 1=1
begin
DELETE top(2000)
FROM dbo.BigTable
WHERE time <= '2013-09-03 22:00:00.000'
OPTION(MAXDOP 1) -- 可考慮是否只使用一個CPU來執行刪除動作
set @n=@@ROWCOUNT
if @n<2000
break
end

Method 2

若留下的資料比較少(例如要刪除2/3的資料或更多的資料),就可以考慮INSERT INTO再TRUNCATE或INSERT INTO再RENAME

INSERT INTO and TRUNCATE
1.將要保留的資料INSERT INTO到dbo.Temp_BigTable
SELECT * INTO dbo.Temp_BigTable
 FROM dbo.Temp_BigTable
 WHERE Date < '2015/1/1';

2.清空dbo.Temp_BigTable
TRUNCATE TABLE dbo.Temp_BigTable;

3.INSERT INTO dbo.BigTable from dbo.Temp_BigTable
INSERT INTO dbo.BigTable
SELECT * FROM dbo.Temp_BigTable;

INSERT INTO再RENAME
1.將要保留的資料INSERT INTO到dbo.Temp_BigTable
2.DROP TABLE dbo.Temp_BigTable
3.RENAME dbo.Temp_BigTable to dbo.BigTable

注意:
因為原Table會被刪除,所以需事先調查與保存與重新設定以下項目
1.權限
2.Trigger
3.Index

PS.以下狀況無法直接DROP TABLE
1.被Foreign Key或view with SCHEMABINDING reference的資料表
2.複寫發行資料表
3.啟用CDC的資料表

若有view with schemabinding
CREATE VIEW v_Table_2
WITH SCHEMABINDING

DROP TABLE會出現以下錯誤

Msg 3729, Level 16, State 1, Line 2
Cannot DROP TABLE 'dbo.Table_1' because it is being referenced by object 'v_Table_2'.

若有Foreign key reference

DROP TABLE會出現以下錯誤

Msg 3726, Level 16, State 1, Line 2
Could not drop object 'dbo.Table_1' because it is referenced by a FOREIGN KEY constraint.


VBScript Common Constants

VBScript Common Constants
from my MSDN blog - August 30, 2016

1.FileSystemObject

'For FileSystemObject
Const ForWriting      = 2
Const ForAppending   = 8
Const ForReading     = 1

Const OpenAsDefault = -2  ' Opens the file using the system default.
Const OpenAsUnicode = -1  ' Opens the file as Unicode.
Const OpenAsUSAscii =  0  ' Opens the file as ASCII.
Const DontCreate =  False ' do not create a new file if doesn't exist
Const CreateFile =  True  ' create a new file if the specified filename doesn't exist

'Constants returned by File.Attributes
Const FileAttrNormal    = 0
Const FileAttrReadOnly  = 1
Const FileAttrHidden    = 2
Const FileAttrSystem    = 4
Const FileAttrVolume    = 8
Const FileAttrDirectory    = 16
Const FileAttrArchive     = 32
Const FileAttrAlias        = 64
Const FileAttrCompressed = 128



2.ADSI

' For ADS_PROPERTY_OPERATION_ENUM
ADS_PROPERTY_CLEAR    = 1
ADS_PROPERTY_UPDATE  = 2
ADS_PROPERTY_APPEND  = 3
ADS_PROPERTY_DELETE  = 4

'For ADS_GROUP_TYPE_ENUM
ADS_GROUP_TYPE_GLOBAL_GROUP         = 0x00000002,
ADS_GROUP_TYPE_DOMAIN_LOCAL_GROUP  = 0x00000004,
ADS_GROUP_TYPE_LOCAL_GROUP           = 0x00000004,
ADS_GROUP_TYPE_UNIVERSAL_GROUP      = 0x00000008,
ADS_GROUP_TYPE_SECURITY_ENABLED     = 0x80000000

'For ADS_USER_FLAG
ADS_UF_ACCOUNTDISABLE             =  0X0002,
ADS_UF_LOCKOUT                      =  0X0010,
ADS_UF_PASSWD_NOTREQD            =  0X0020,
ADS_UF_PASSWD_CANT_CHANGE       =  0X0040,
ADS_UF_DONTEXPIREPASSWD          =  0X10000,
ADS_UF_TRUSTED_FOR_DELEGATION   =  0X80000,
ADS_UF_NOT_DELEGATED              =  0X100000

CONST UF_ACCOUNTDISABLE=&H0002
CONST UF_DONT_EXPIRE_PASSWD=&H10000
CONST UF_HOMEDIR_REQUIRED=&H0008
CONST UF_INTERDOMAIN_TRUST_ACCOUNT=&H0800
CONST UF_LOCKOUT=&H0010
CONST UF_MNS_LOGON_ACCOUNT=&H20000
CONST UF_NORMAL_ACCOUNT=&H0200
CONST UF_PASSWD_CANT_CHANGE=&H0040
CONST UF_PASSWD_NOTREQD=&H0020
CONST UF_SCRIPT=&H0001
CONST UF_SERVER_TRUST_ACCOUNT=&H2000
CONST UF_TEMP_DUPLICATE_ACCOUNT=&H0100
CONST UF_WORKSTATION_TRUST_ACCOUNT=&H1000

'Parameter for search AD
Const ADS_SCOPE_SUBTREE = 2      'Subtree
Const ADS_SCOPE_ONELEVEL = 1     'Top Level



3.Registry

'For Registry Catalog
Const HKEY_CLASSES_ROOT  = &H80000000
Const HKEY_CURRENT_USER  = &H80000001
Const HKEY_LOCAL_MACHINE  = &H80000002
Const HKEY_USERS        = &H80000003
Const HKEY_CURRENT_CONFIG  = &H80000005

Const HKEY_DYN_DATA       = &H80000006

'For Registry Type

Const REG_NONE      = 0
Const REG_SZ       = 1
Const REG_EXPAND_SZ = 2
Const REG_BINARY      = 3
Const REG_DWORD  = 4
Const REG_MULTI_SZ   = 7



4.ADO

'---- CursorTypeEnum Values ----
Const adOpenForwardOnly = 0
Const adOpenKeyset = 1
Const adOpenDynamic = 2
Const adOpenStatic = 3

'---- LockTypeEnum Values ----
Const adLockReadOnly = 1
Const adLockPessimistic = 2
Const adLockOptimistic = 3
Const adLockBatchOptimistic = 4

'---- CursorLocationEnum Values ----
Const adUseServer = 2
Const adUseClient = 3

' ADODB

'ActiveX Data Objects (ADO)
CONST adAddNew=&H01000400
CONST adAffectAllChapters=4
CONST adAffectCurrent=1
CONST adAffectGroup=2
CONST adApproxPosition=&H00004000
CONST adArray=&H2000
CONST adAsyncConnect=&H00000010
CONST adAsyncExecute=&H00000010
CONST adAsyncFetch=&H00000020
CONST adAsyncFetchNonBlocking=&H00000040
CONST adBigInt=20
CONST adBinary=128
CONST adBookmark=&H00002000
CONST adBookmarkCurrent=0
CONST adBookmarkFirst=1
CONST adBookmarkLast=2
CONST adBoolean=11
CONST adBSTR=8
CONST adChapter=136
CONST adChar=129
CONST adClipString=2
CONST adCmdFile=&H0100
CONST adCmdStoredProc=&H0004
CONST adCmdTable=&H0002
CONST adCmdTableDirect=&H0200
CONST adCmdText=&H0001
CONST adCmdUnknown=&H0008
CONST adCollectionRecord=1
CONST adCompareEqual=1
CONST adCompareGreaterThan=2
CONST adCompareLessThan=0
CONST adCompareNotComparable=4
CONST adCompareNotEqual=3
CONST adCopyAllowEmulation=4
CONST adCopyNonRecursive=2
CONST adCopyOverWrite=1
CONST adCopyUnspecified=-1
CONST adCR=13
CONST adCreateCollection=&H00002000
CONST adCreateNonCollection=&H00000000
CONST adCreateOverwrite=&H04000000
CONST adCreateStructDoc=&H80000000
CONST adCriteriaAllCols=1
CONST adCriteriaKey=0
CONST adCriteriaTimeStamp=3
CONST adCriteriaUpdCols=2
CONST adCRLF=-1
CONST adCurrency=6
CONST adDate=7
CONST adDBDate=133
CONST adDBTime=134
CONST adDBTimeStamp=135
CONST adDecimal=14
CONST adDefaultStream=-1
CONST adDelayFetchFields=&H00008000
CONST adDelayFetchStream=&H00004000
CONST adDelete=&H01000800
CONST adDouble=5
CONST adEditAdd=&H0002
CONST adEditDelete=&H0004
CONST adEditInProgress=&H0001
CONST adEditNone=&H0000
CONST adEmpty=0
CONST adErrBoundToCommand=&He7b
CONST adErrCannotComplete=&He94
CONST adErrCantChangeConnection=&Hea4
CONST adErrCantChangeProvider=&Hc94
CONST adErrCantConvertvalue=&He8c
CONST adErrCantCreate=&He8d
CONST adErrCatalogNotSet=&Hea3
CONST adErrColumnNotOnThisRow=&He8e
CONST adErrDataConversion=&Hd5d
CONST adErrDataOverflow=&He89
CONST adErrDelResOutOfScope=&He9a
CONST adErrDenyNotSupported=&Hea6
CONST adErrDenyTypeNotSupported=&Hea7
CONST adErrFeatureNotAvailable=&Hcb3
CONST adErrFieldsUpdateFailed=&Hea5
CONST adErrIllegalOperation=&Hc93
CONST adErrIntegrityViolation=&He87
CONST adErrInTransaction=&Hcae
CONST adErrInvalidArgument=&Hbb9
CONST adErrInvalidConnection=&He7d
CONST adErrInvalidParamInfo=&He7c
CONST adErrInvalidTransaction=&He82
CONST adErrInvalidURL=&He91
CONST adErrItemNotFound=&Hcc1
CONST adErrNoCurrentRecord=&Hbcd
CONST adErrNotReentrant=&He7e
CONST adErrObjectClosed=&He78
CONST adErrObjectInCollection=&Hd27
CONST adErrObjectNotSet=&Hd5c
CONST adErrObjectOpen=&He79
CONST adErrOpeningFile=&Hbba
CONST adErrOperationCancelled=&He80
CONST adError=10
CONST adErrOutOfSpace=&He96
CONST adErrPermissionDenied=&He88
CONST adErrPropConflicting=&He9e
CONST adErrPropInvalidColumn=&He9b
CONST adErrPropInvalidOption=&He9c
CONST adErrPropInvalidValue=&He9d
CONST adErrPropNotAllSettable=&He9f
CONST adErrPropNotSet=&Hea0
CONST adErrPropNotSettable=&Hea1
CONST adErrPropNotSupported=&Hea2
CONST adErrProviderFailed=&Hbb8
CONST adErrProviderNotFound=&He7a
CONST adErrReadFile=&Hbbb
CONST adErrResourceExists=&He93
CONST adErrResourceLocked=&He92
CONST adErrResourceOutOfScope=&He97
CONST adErrSchemaViolation=&He8a
CONST adErrSignMismatch=&He8b
CONST adErrStillConnecting=&He81
CONST adErrStillExecuting=&He7f
CONST adErrTreePermissionDenied=&He90
CONST adErrUnavailable=&He98
CONST adErrUnsafeOperation=&He84
CONST adErrURLDoesNotExist=&He8f
CONST adErrURLIntegrViolSetColumns=&He8f
CONST adErrURLNamedRowDoesNotExist=&He99
CONST adErrVolumeNotFound=&He95
CONST adErrWriteFile=&Hbbc
CONST adExecuteNoRecords=&H00000080
CONST adFailIfNotExists=-1
CONST adFieldAlreadyExists=26
CONST adFieldBadStatus=12
CONST adFieldCannotComplete=20
CONST adFieldCannotDeleteSource=23
CONST adFieldCantConvertValue=2
CONST adFieldCantCreate=7
CONST adFieldDataOverflow=6
CONST adFieldDefault=13
CONST adFieldDoesNotExist=16
CONST adFieldIgnore=15
CONST adFieldIntegrityViolation=10
CONST adFieldInvalidURL=17
CONST adFieldIsNull=3
CONST adFieldOK=0
CONST adFieldOutOfSpace=22
CONST adFieldPendingChange=&H40000
CONST adFieldPendingDelete=&H20000
CONST adFieldPendingInsert=&H10000
CONST adFieldPendingUnknown=&H80000
CONST adFieldPendingUnknownDelete=&H100000
CONST adFieldPermissionDenied=9
CONST adFieldReadOnly=24
CONST adFieldResourceExists=19
CONST adFieldResourceLocked=18
CONST adFieldResourceOutOfScope=25
CONST adFieldSchemaViolation=11
CONST adFieldSignMismatch=5
CONST adFieldTruncated=4
CONST adFieldUnavailable=8
CONST adFieldVolumeNotFound=21
CONST adFileTime=64
CONST adFilterAffectedRecords=2
CONST adFilterConflictingRecords=5
CONST adFilterFetchedRecords=3
CONST adFilterNone=0
CONST adFilterPendingRecords=1
CONST adFind=&H00080000
CONST adFldCacheDeferred=&H00001000
CONST adFldFixed=&H00000010
CONST adFldIsChapter=&H00002000
CONST adFldIsCollection=&H00040000
CONST adFldIsDefaultStream=&H00020000
CONST adFldIsNullable=&H00000020
CONST adFldIsRowURL=&H00010000
CONST adFldKeyColumn=&H00008000
CONST adFldLong=&H00000080
CONST adFldMayBeNull=&H00000040
CONST adFldMayDefer=&H00000002
CONST adFldNegativeScale=&H00004000
CONST adFldRowID=&H00000100
CONST adFldRowVersion=&H00000200
CONST adFldUnknownUpdatable=&H00000008
CONST adFldUpdatable=&H00000004
CONST adGetRowsRest=-1
CONST adGUID=72
CONST adHoldRecords=&H00000100
CONST adIDispatch=9
CONST adIndex=&H00800000
CONST adInteger=3
CONST adIUnknown=13
CONST adLF=10
CONST adLockBatchOptimistic=4
CONST adLockOptimistic=3
CONST adLockPessimistic=2
CONST adLockReadOnly=1
CONST adLongVarBinary=205
CONST adLongVarChar=201
CONST adLongVarWChar=203
CONST adMarshalAll=0
CONST adMarshalModifiedOnly=1
CONST adModeRead=1
CONST adModeReadWrite=3
CONST adModeRecursive=&H400000
CONST adModeShareDenyNone=&H10
CONST adModeShareDenyRead=4
CONST adModeShareDenyWrite=8
CONST adModeShareExclusive=&Hc
CONST adModeUnknown=0
CONST adModeWrite=2
CONST adMoveAllowEmulation=4
CONST adMoveDontUpdateLinks=2
CONST adMoveOverWrite=1
CONST adMovePrevious=&H00000200
CONST adMoveUnspecified=-1
CONST adNotify=&H00040000
CONST adNumeric=131
CONST adOpenAsync=&H00001000
CONST adOpenDynamic=2
CONST adOpenForwardOnly=0
CONST adOpenIfExists=&H02000000
CONST adOpenKeyset=1
CONST adOpenRecordUnspecified=-1
CONST adOpenSource=&H00800000
CONST adOpenStatic=3
CONST adOpenStreamAsync=1
CONST adOpenStreamFromRecord=4
CONST adOpenStreamUnspecified=-1
CONST adParamInput=&H0001
CONST adParamInputOutput=&H0003
CONST adParamLong=&H0080
CONST adParamNullable=&H0040
CONST adParamOutput=&H0002
CONST adParamReturnValue=&H0004
CONST adParamSigned=&H0010
CONST adParamUnknown=&H0000
CONST adPersistADTG=0
CONST adPersistXML=1
CONST adPosBOF=-2
CONST adPosEOF=-3
CONST adPosUnknown=-1
CONST adPriorityAboveNormal=4
CONST adPriorityBelowNormal=2
CONST adPriorityHighest=5
CONST adPriorityLowest=1
CONST adPriorityNormal=3
CONST adPromptAlways=1
CONST adPromptComplete=2
CONST adPromptCompleteRequired=3
CONST adPromptNever=4
CONST adPropNotSupported=&H0000
CONST adPropOptional=&H0002
CONST adPropRead=&H0200
CONST adPropRequired=&H0001
CONST adPropVariant=138
CONST adPropWrite=&H0400
CONST adReadAll=-1
CONST adReadLine=-2
CONST adRecalcAlways=1
CONST adRecalcUpFront=0
CONST adRecCanceled=&H0000100
CONST adRecCantRelease=&H0000400
CONST adRecConcurrencyViolation=&H0000800
CONST adRecDBDeleted=&H0040000
CONST adRecDeleted=&H0000004
CONST adRecIntegrityViolation=&H0001000
CONST adRecInvalid=&H0000010
CONST adRecMaxChangesExceeded=&H0002000
CONST adRecModified=&H0000002
CONST adRecMultipleChanges=&H0000040
CONST adRecNew=&H0000001
CONST adRecObjectOpen=&H0004000
CONST adRecOK=&H0000000
CONST adRecordURL=-2
CONST adRecOutOfMemory=&H0008000
CONST adRecPendingChanges=&H0000080
CONST adRecPermissionDenied=&H0010000
CONST adRecSchemaViolation=&H0020000
CONST adRecUnmodified=&H0000008
CONST adResync=&H00020000
CONST adResyncAllValues=2
CONST adResyncUnderlyingValues=1
CONST adRsnAddNew=1
CONST adRsnClose=9
CONST adRsnDelete=2
CONST adRsnFirstChange=11
CONST adRsnMove=10
CONST adRsnMoveFirst=12
CONST adRsnMoveLast=15
CONST adRsnMoveNext=13
CONST adRsnMovePrevious=14
CONST adRsnRequery=7
CONST adRsnResynch=8
CONST adRsnUndoAddNew=5
CONST adRsnUndoDelete=6
CONST adRsnUndoUpdate=4
CONST adRsnUpdate=3
CONST adSaveCreateNotExist=1
CONST adSaveCreateOverWrite=2
CONST adSchemaAsserts=0
CONST adSchemaCatalogs=1
CONST adSchemaCharacterSets=2
CONST adSchemaCheckConstraints=5
CONST adSchemaCollations=3
CONST adSchemaColumnPrivileges=13
CONST adSchemaColumns=4
CONST adSchemaColumnsDomainUsage=11
CONST adSchemaConstraintColumnUsage=6
CONST adSchemaConstraintTableUsage=7
CONST adSchemaCubes=32
CONST adSchemaDBInfoKeywords=30
CONST adSchemaDBInfoLiterals=31
CONST adSchemaDimensions=33
CONST adSchemaForeignKeys=27
CONST adSchemaHierarchies=34
CONST adSchemaIndexes=12
CONST adSchemaKeyColumnUsage=8
CONST adSchemaLevels=35
CONST adSchemaMeasures=36
CONST adSchemaMembers=38
CONST adSchemaPrimaryKeys=28
CONST adSchemaProcedureColumns=29
CONST adSchemaProcedureParameters=26
CONST adSchemaProcedures=16
CONST adSchemaProperties=37
CONST adSchemaProviderSpecific=-1
CONST adSchemaProviderTypes=22
CONST adSchemaReferentialConstraints=9
CONST adSchemaSchemata=17
CONST adSchemaSQLLanguages=18
CONST adSchemaStatistics=19
CONST adSchemaTableConstraints=10
CONST adSchemaTablePrivileges=14
CONST adSchemaTables=20
CONST adSchemaTranslations=21
CONST adSchemaTrustees=39
CONST adSchemaUsagePrivileges=15
CONST adSchemaViewColumnUsage=24
CONST adSchemaViews=23
CONST adSchemaViewTableUsage=25
CONST adSearchBackward=-1
CONST adSearchForward=1
CONST adSeek=&H00400000
CONST adSeekAfter=&H8
CONST adSeekAfterEQ=&H4
CONST adSeekBefore=&H20
CONST adSeekBeforeEQ=&H10
CONST adSeekFirstEQ=&H1
CONST adSeekLastEQ=&H2
CONST adSimpleRecord=0
CONST adSingle=4
CONST adSmallInt=2
CONST adStateClosed=&H00000000
CONST adStateConnecting=&H00000002
CONST adStateExecuting=&H00000004
CONST adStateFetching=&H00000008
CONST adStateOpen=&H00000001
CONST adStatusCancel=&H0000004
CONST adStatusCantDeny=&H0000003
CONST adStatusErrorsOccurred=&H0000002
CONST adStatusOK=&H0000001
CONST adStatusUnwantedEvent=&H0000005
CONST adStructDoc=2
CONST adTinyInt=16
CONST adTypeBinary=1
CONST adTypeText=2
CONST adUnsignedBigInt=21
CONST adUnsignedInt=19
CONST adUnsignedSmallInt=18
CONST adUnsignedTinyInt=17
CONST adUpdate=&H01008000
CONST adUpdateBatch=&H00010000
CONST adUseClient=3
CONST adUserDefined=132
CONST adUseServer=2
CONST adVarBinary=204
CONST adVarChar=200
CONST adVariant=12
CONST adVarNumeric=139
CONST adVarWChar=202
CONST adWChar=130
CONST adWriteChar=0
CONST adWriteLine=1
CONST adwrnSecurityDialog=&He85
CONST adwrnSecurityDialogHeader=&He86
CONST adXactAbortRetaining=&H00040000
CONST adXactBrowse=&H00000100
CONST adXactChaos=&H00000010
CONST adXactCommitRetaining=&H00020000
CONST adXactCursorStability=&H00001000
CONST adXactIsolated=&H00100000
CONST adXactReadCommitted=&H00001000
CONST adXactReadUncommitted=&H00000100
CONST adXactRepeatableRead=&H00010000
CONST adXactSerializable=&H00100000
CONST adXactUnspecified=&Hffffffff

'ADC / ADO Constants
CONST adcExecAsync=2
CONST adcExecSync=1
CONST adcFetchAsync=3
CONST adcFetchBackground=2
CONST adcFetchUpFront=1
CONST adcReadyStateComplete=4
CONST adcReadyStateInteractive=3
CONST adcReadyStateLoaded=2



5.CDO

'For SMTP server authentication
Const cdoAnonymous  = 0
Const cdoBasic       = 1
Const cdoNTLM       = 2

'For SMTP server network option
Const cdoSendUsingPickup = 1
Const cdoSendUsingPort   = 2

2016年8月29日 星期一

Microsoft VBScript

Microsoft VBScript
from my MSDN blog - August 29, 2016

Quick Start:

VBScript Operators
https://msdn.microsoft.com/en-us/library/9da4s2eh(v=vs.84).aspx

 ' default value of variable is "" or Empty or 0
Dim p1
If p1 = "" Then
 Wscript.Echo "p1="""""
End If

If p1 = 0 Then
 Wscript.Echo "p1=0"
End If

If isEmpty(p1) Then
 Wscript.Echo "isEmpty(p1)"
End If

' default value is not null
If isNull(p1) Then
 Wscript.Echo "isNull(p1)"
End If

Constant  Value            Description
----------------------------------------------------------------
vbCr     Chr(13)                      Carriage return
vbCrLf   Chr(13)& Chr(10)  Carriage return–linefeed combination
vbLf     Chr(10)                      Line feed

  • vbCr : - return to line beginning
    Represents a carriage-return character for print and display functions.
  • vbCrLf : - similar to pressing Enter
    Represents a carriage-return character combined with a linefeed character for print and display functions.
  • vbLf : - go to next line
    Represents a linefeed character for print and display functions.

 ' String
strMyString = "Line1"
strMyString = strMyString & vbNewLine
strMyString = strMyString & "Line2"
strMyString = strMyString & vbCr & vbLf
strMyString = strMyString & "Line3"
strMyString = strMyString & vbCrLf
strMyString = strMyString & "Line4"

 ' Date
Dim dteExpiredDate
    dteExpiredDate = "2008/12/31"
    dteExpiredDate = #2008/12/31#              

'Time
Now()
Time()
Hour(Now())
Minute(Now())
Second(Now())

 ' Constants
Const COMP_NAME = "Microsoft"
Const LOCATION = "Taiwan"

 '  Intrinsic Constants (Build-in Constants)
FormatDateTime(Now(),vbShortDate)
FormatDateTime(Now(),vbLongDate)
FormatDateTime(Now(),vbShortTime)
FormatDateTime(Now(),vbLongTime)

 ' Buildin Datediff Function
DateDiff("d", Now, "2008/05/30")

 ' Buildin String Function
strMyString = "This is a book."
WScript.Echo "Upper Case : " & UCase(strMyString)
WScript.Echo "Lower Case : " & LCase(strMyString)

LEN(strMyString)
Left(strMyString,4)
Right(strMyString,5)
InStr(strMyString,"is")
InStr(4,strMyString,"is")

 ' Round 
Randomize
vRnd = Rnd * 100
WScript.Echo "vRnd : " & vRnd
WScript.Echo "Round(vRnd) : " & Round(vRnd)

 'Conversion Functions
'CInt
Dim MyDouble, MyInt, MyByte, MyString, MyVal1, MyVal2, MyLong1, MyLong2
MyDouble = 2345.5678     ' MyDouble is a Double.
MyInt = CInt(MyDouble)   ' MyInt contains 2346.

 'CBytes
MyDouble = 125.5678        ' MyDouble is a Double.
MyByte = CByte(MyDouble)   ' MyByte contains 126.

 'CStr
MyDouble = 437.324         ' MyDouble is a Double.
MyString = CStr(MyDouble)   ' MyString contains "437.324".

 'CLng
MyVal1 = 25427.45: MyVal2 = 25427.55   ' MyVal1, MyVal2 are Doubles.
MyLong1 = CLng(MyVal1)   ' MyLong1 contains 25427.
MyLong2 = CLng(MyVal2)   ' MyLong2 contains 25428.


Using Conditional Statements

 ' If Then Else
'    Dim myDate
    myDate = #2/13/95#
    If myDate < Now Then myDate = Now

   If value = 0 Then
      WScript.Echo "value=0"
   ElseIf value = 1 Then
      WScript.Echo "value=1"
   Else
      WScript.Echo "Value out of range!"
   End If

MyVar = 2
Select Case MyVar
 Case "0"
  WScript.Echo "MyVar = 0"
 Case "1"
  WScript.Echo "MyVar = 1"
 Case "2"
  WScript.Echo "MyVar = 2"
 Case Else
  WScript.Echo "Sorry value is out of range"
End Select

Looping Through Code

    For j = 2 To 10 Step 2
      total = total + j
   Next
   For myNum = 16 To 2 Step -2
      total = total + myNum
   Next
   Do While myNum2 > 10
      myNum2 = myNum2 - 1
      counter = counter + 1
   Loop
   Do Until myNum3 = 1
      myNum3 = myNum3 - 1
      counter2 = counter2 + 1
      If myNum3 < 3 Then Exit Do
   Loop
   Set d = CreateObject("Scripting.Dictionary")
   d.Add "0", "Athens"   'Add some keys and items
   d.Add "1", "Belgrade"
   d.Add "2", "Cairo"

   For Each I in d
      WScript.Echo "D.Item(" & I & ") : " & d.Item(I)
   Next

 'Array & Dynamic Array
'== 1 Dimension Array ==
Dim Names(9)       ' Declare an array with 10 elements.

'== 2 Dimension Array ==
Dim aryTwoDimension(2,2)

Dim aryTripleDimension
aryTripleDimension = Array(10,20,30)
B = aryTripleDimension(2)   ' B is now 30.

'== Dynamic Array ==
Dim NumArray()
Dim DynamicArray()      ' Declare a dynamic array.
ReDim DynamicArray(9)   ' Allocate storage space.
ReDim Preserve DynamicArray(10)   ' Allocate storage space.

'== Erase Array ==
Erase NumArray          ' Each element is reinitialized.
Erase DynamicArray      ' Free memory used by array.

'== Ubound Function ==
Dim A(100,3,4)
WScript.Echo "UBound(A, 1) : " & UBound(A, 1)
WScript.Echo "UBound(A, 2) : " & UBound(A, 2)
WScript.Echo "UBound(A, 3) : " & UBound(A, 3)

'== Split ==
Dim MyString, MyArray, Msg
MyString = "VBScriptXisXfun!"
MyArray = Split(MyString, "x", -1, 1)
' MyArray(0) contains "VBScript".
' MyArray(1) contains "is".
' MyArray(2) contains "fun!".
Msg = MyArray(0) & " " & MyArray(1)
Msg = Msg   & " " & MyArray(2)
WScript.Echo Msg

'== Is Array ==
Dim MyVariable
Dim MyArray5(3)
MyArray5(0) = "Sunday"
MyArray5(1) = "Monday"
MyArray5(2) = "Tuesday"
MyVariable = IsArray(MyArray5) ' MyVariable contains "True".
WScript.Echo "MyVariable : " & MyVariable

Sub Procedures

Sub Procedures
 Sub ConvertTemp
   temp = InputBox("Please enter the temperature in degrees F.", 1)
   MsgBox "The temperature is " & Celsius(temp) & " degrees C."
End Sub


Function Procedures

 Sub ConvertTemp
   temp = InputBox("Please enter the temperature in degrees F.", 1)
   MsgBox "The temperature is " & Celsius(temp) & " degrees C."
End Sub

Function Celsius(fDegrees)
   Celsius = (fDegrees - 32) * 5 / 9
End Function

 '== Pass array parameter ==
Dim aryDemo(2)
 aryDemo(0) = 1
 aryDemo(1) = 2
 aryDemo(2) = 3

Call PrintArray(aryDemo)

Sub PrintArray(aryParameter)
 
 Dim i
 For i=0 To UBound(aryParameter)
  WScript.Echo "Array(" & i & ") Value Is : " & aryParameter(i)
 Next
 
End Sub

 '== Pass Object parameter ==
' List Items in the My Computer Folder
Const MY_COMPUTER = &H11&

Set objShell = CreateObject("Shell.Application")

Call ListItemInMyComputer(objShell)

Sub ListItemInMyComputer(objShellObject)

 Set objFolder = objShellObject.Namespace(MY_COMPUTER)
 Set objFolderItem = objFolder.Self
  WScript.Echo objFolderItem.Path
 
 Set colItems = objFolder.Items
 For Each objItem in colItems
     Wscript.Echo objItem.Name
 Next
 
End Sub

Set objShell = Nothing

Sub TestSub(ByRef MyParam) 
    MyParam = 5
End Sub

Dim MyArg 
MyArg = 123

TestSub MyArg
' MyArg in changed in TestSub to 5.

Sub TestSub(ByVal MyParam) 
    MyParam = 5
End Sub

Dim MyArg 
MyArg = 123
TestSub MyArg
' MyArg is still 123.


更多ByRef,ByVal用法請參考
ByRef and ByVal Parameters
https://msdn.microsoft.com/en-us/library/ee478101(v=vs.84).aspx

Error handling
To Err Is VBScript – Part 1
https://technet.microsoft.com/en-us/library/ee692852.aspx

 On Error Resume Next
strComputer = "fictional"
strPrinter = "TestPrinter"
Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2")
If Err = 0 Then
    WScript.Echo "No error binding to " & strComputer
    strPrinter = "TestPrinter"
    Set objPrinter = objWMIService.Get _
      ("Win32_Printer.Name='" & strPrinter & "'")
    If Err = 0 Then
        WScript.Echo "No error connecting to " & strPrinter
    Else
        DisplayErrorInfo
    End If
Else
    DisplayErrorInfo
End If

'******************************************************************************

Sub DisplayErrorInfo

    WScript.Echo "Error:      : " & Err
    WScript.Echo "Error (hex) : &H" & Hex(Err)
    WScript.Echo "Source      : " & Err.Source
    WScript.Echo "Description : " & Err.Description
    Err.Clear

End Sub


VBScript:

Script Center > Learn > Learn Beginning Scripting

Class is in Session

Scripting Guide

Overview

Introduction

Document Conventions

Support Policy

Scripting Concepts and Technologies for System Administration

Scripting Solutions for System Administration

Scripting for the Enterprise

Download Documents or Tools

VBScript Constants

VBScript Common Constants

VBScript Template

Template - Sample

Security:

vbe file

Script Encoder Overview (Script Encoder is not supported in Windows Vista or Windows 7)

  SCRENC [/s] [/f] [/xl] [/l defLanguage ] [ /e defExtension] input file output file

Script Encoder , Script Encoder

Encode a Script

Encode and Decode a VB script

HTML Applications (HTAs):

Calendar Control Reference

Creating Your Own HTAs - Try It Yourself: Add a Multi-Line Text Box to an HTA

Creating Your Own HTAs - Try It Yourself: Add Radio Buttons to an HTA

Creating Your Own HTAs - Try It Yourself: Add a Button to an HTA

Creating Your Own HTAs - Try It Yourself: Add a Check Box to an HTA

Creating Your Own HTAs - Try It Yourself: Add a Drop-down List Box to an HTA

Creating Your Own HTAs - Try It Yourself: Add a List Box to an HTA

Creating Your Own HTAs - Try It Yourself: Add a Multi-select List Box to an HTA

Creating Your Own HTAs - Try It Yourself: Add a Password Box to an HTA

Creating Your Own HTAs - Try It Yourself: Add a Subroutine to an HTA

Creating Your Own HTAs - Try It Yourself: Add a Text Box to an HTA

Creating Your Own HTAs - Try It Yourself: Display Process Names and IDs in an HTA

Creating Your Own HTAs - Try It Yourself: Write Data to a <span>

Extreme Makeover, Part 2: Wrap Your Scripts Up in a GUI Interface

Extreme Makeover: Wrap Your Scripts Up in a GUI Interface

The ABCs of HTAs Archive

The ABCs of HTAs: Scripting HTML Applications - Add Color Options to a List Box

The ABCs of HTAs: Scripting HTML Applications - Add a Gradient Background to Your HTAs

The ABCs of HTAs: Scripting HTML Applications - AutoRefresh an HTA Using a Timer

The ABCs of HTAs: Scripting HTML Applications - Create an HTA Without a Title Bar

The ABCs of HTAs: Scripting HTML Applications - Run a Script from a Text Link

The ABCs of HTAs: Scripting HTML Applications - Use a File Open Dialog Box to Populate a List Box (Windows XP-only)

 

Development Tools for VBScript:

  • Visual Studio
  • Notepad
  • Notepad++ (Third-Party)
  • UltraEdit (Third-Party)
  • VbsEdit (Third-Party)
  • Admin Script Editor (Third-Party)

Code Auto Generator:




2016年8月28日 星期日

Using SQL Server in Windows 8 and later versions of Windows operating system (在Windows 8與更新版的Windows作業系統使用SQL Server)

Using SQL Server in Windows 8 and later versions of Windows operating system
from my MSDN blog - August 28, 2016



在Windows 8與更新版的Windows作業系統使用SQL Server

雖然KB2681562文件說明Windows 10不支援SQL 2008 R2

但經過測試在Windows 10安裝SQL 2008 R2只會跳出警告,最後還是可以安裝成功 ,但此種搭配微軟並不支援,將來也不會有hotfix

Using SQL Server in Windows 8 and later versions of Windows operating system
https://support.microsoft.com/en-us/kb/2681562

OS versionSQL versionMinimum SQL Server version requirements
Windows 10SQL 2014SupportSQL Server 2014 Service Pack 1 or a later update
SQL 2012SupportSQL Server 2012 Service Pack 2 or a later update
SQL 2008 R2Not SupportSQL Server 2008 R2 is not supported on Windows 10
SQL 2008Not SupportSQL Server 2008 is not supported on Windows 10
Windows Server 2012 R2 or Windows 8.1SQL 2014SupportSQL Server 2014 RTM or a later version
SQL 2012SupportSQL Server 2012 Service Pack 1 or a later update
SQL 2008 R2SupportSQL Server 2008 R2 Service Pack 2 or a later update
SupportSQL Server 2008 Service Pack 3 or a later update
Windows Server 2012 or Windows 8SQL 2014SupportSQL Server 2014 RTM or a later version
SQL 2012SupportSQL Server 2012 RTM or a later update
SQL 2008 R2SupportSQL Server 2008 R2 Service Pack 1 or a later update
SupportSQL Server 2008 Service Pack 3 or a later update


SQL Server Planning, Pricing and License

  Server-CALs授權模式 1.需購買的量應該是所有終端用戶的電腦 例如:一台Data warehouse主機 與 一台Web報表主機,但公司有50人或電腦會連進Web報表主機開啟報表,則應該每一台用戶端電腦都需要有CALs授權,Data warehouse主機購買Ser...