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


2016年5月25日 星期三

Oracle Client for SQL Server

Oracle Client for SQL Server
from my MSDN blog - May 25, 2016

Oracle ClientSupport Windows versionDocument / Download
Oracle 12c for Microsoft Windows x64 (64-Bit)•Windows Server 2008 x64 and Windows Server 2008 R2 x64 - Standard, Enterprise, Datacenter, Web, and Foundation editions.
•Windows 7 x64 - Professional, Enterprise, and Ultimate editions •Windows 8 x64 and Windows 8.1 x64 - Pro and Enterprise editions •Windows Server 2012 x64 and Windows Server 2012 R2 x64 - Standard, Datacenter, Essentials, and Foundation editions
Client Quick Installation Guide 12c Release 1 (12.1) for Microsoft Windows x64 (64-Bit) http://docs.oracle.com/database/121/NXCQI/toc.htmOracle Database 12c Release 1 (12.1.0.2.0) http://www.oracle.com/technetwork/database/enterprise-edition/downloads/database12c-win64-download-2297732.html
Oracle 11g R2 for Microsoft Windows x64 (64-Bit)•Windows Server 2003 - all x64 editions •Windows Server 2003 R2 - all x64 editions •Windows XP Professional x64 •Windows Vista x64 - Business, Enterprise, and Ultimate editions •Windows Server 2008 x64 - Standard, Enterprise, Datacenter, and Web editions. •Windows Server 2008 R2 x64 - Standard, Enterprise, Datacenter, Web, and Foundation editions. •Windows 7 x64 - Professional, Enterprise, and Ultimate editions •Windows 8 x64 - Pro and Enterprise editions •Windows 8.1 x64 - Pro and Enterprise editions •Windows Server 2012 x64 and Windows Server 2012 R2 x64 - Standard, Datacenter, Essentials, and Foundation editionsClient Quick Installation Guide 11g Release 2 (11.2) for Microsoft Windows x64 (64-Bit) http://docs.oracle.com/cd/E11882_01/install.112/e49700/toc.htmOracle Database 11g Release 2 (11.2.0.1.0) http://www.oracle.com/technetwork/database/enterprise-edition/downloads/112010-win64soft-094461.html
Oracle 10g R2 for Microsoft Windows x64 (64-Bit)•Windows Server 2003, Standard x64 Edition •Windows Server 2003, Enterprise x64 Edition •Windows Server 2003, Datacenter x64 Edition •Windows XP Professional x64 Edition •Windows Vista x64, Service Pack 1 - Business, Enterprise, and Ultimate editions •Windows Server 2008 x64 - Standard, Enterprise, Datacenter, Web, Standard without Hyper-V, Enterprise without Hyper-V, and Datacenter without Hyper-V editions The specific operating system components that are not supported are Windows Server 2008 x64 Hyper-V and Server Core.Database Client Installation Guide for Microsoft Windows (x64) http://docs.oracle.com/cd/B19306_01/install.102/b15684/toc.htm


2014年7月24日 星期四

SQL Server and SQL Server Agent Service Account(Startup Account) and Permissions (SQL Server與SQL Server Agent 服務帳戶(啟動帳戶)與權限)

SQL Server and SQL Server Agent Service Account(Startup Account) and Permissions
from my MSDN blog - July 24, 2014

SQL Server與SQL Server Agent 服務帳戶(啟動帳戶)與權限

可設定的種類

1.Domain User Account (使用一個網域使用者帳戶)
2.Local User Account (使用一個本機使用者帳戶)
3.Local Service Account (NT AUTHORITY\LOCAL SERVICE) 本機服務帳戶
4.Network Service Account (NT AUTHORITY\NETWORK SERVICE) 本機網路帳戶
5.Local System Account (NT AUTHORITY\SYSTEM) 本機系統帳戶

6.Virtual Accounts (start from Windows 7 and Windows Server 2008 R2)

NT SERVICE\ <SERVICENAME>

ServiceVirtual Account Name
Default instance of the Database Engine serviceNT SERVICE\MSSQLSERVER
SQL Server Agent service on the default instance of SQL ServerNT SERVICE\SQLSERVERAGENT
SQL Server Analysis service on the default instance of SQL ServerNT SERVICE\MSSQLServerOLAPService
SQL Server Reporting service on the default instance of SQL ServerNT SERVICE\ReportServer

7.Managed Service Accounts (start from Windows 7 and Windows Server 2008 R2)

Configure Windows Service Accounts and Permissions
https://msdn.microsoft.com/en-us/library/ms143504.aspx

 

Q & A

1.Does service account need sysadmin role?

[SQL Server(Database Engine)]
沒有文件說明一定需要sysadmin role
no document found saying No or Yes explicitly about if sql service account need to be a member of sysdamin role
(1)SQL 2005,service account is always granted with sysadmin by default (e.g. if we pick Local System as our service account, then we would have NT Authority/System having sysadmin in the SQL instance).
How to change the SQL Server or SQL Server Agent service account without using SQL Enterprise Manager in SQL Server 2000 or SQL Server Configuration Manager in SQL Server 2005
https://support.microsoft.com/kb/283811
Base on KB283811 for SQL 2005, When changing new SQL service Account, sysadmin role is a must

(2)SQL 2008,in Windows 2008 R2, Virtual Accounts(SQL Server Per-service SID)=NT SERVICE\<SERVICENAME>is always granted with sysadmin by default
Configure Windows Service Accounts and Permissions
https://msdn.microsoft.com/en-us/library/ms143504.aspx

基於以上文件,建議將SQL Server service accoount(startup account)加入sysadmin role

 

[SQL Server Agent]
雖然SQL Server Agent service的啟動帳戶不是sysadmin role也能啟動SQL Server Agent,但可能會限制某些功能。

(1)必須是sysadmin role
From Books Online and Training kit 70-462
The SQL Server Agent service startup account must be a member of the SQL Server sysadmin fixed server role, and if multiserver job processing is used, the account must also be a member of the msdb database role TargetServersRole on the master server.
Set the Service Startup Account for SQL Server Agent (SQL Server Configuration Manager)
https://msdn.microsoft.com/en-us/library/ms186264.aspx

From Training kit 70-462
(2)The account you assign for the SQL Server Agent service during installation is added automatically to the sysadmin fixed server role during installation.
PS.但是經測試SQL 2008 R2安裝過程時所指定一個local or domain user account也不會自動加入sysadmin role

(3)If you modify the account used by the SQL Server Agent service at a later point, SQL Server Configuration Manager does not automatically add the account to sysadmin role.
PS.透過SQL Server Configuration Manager修改啟動帳戶,只會給予需要的權限,不會自動加sysadmin role

 

2.Does service account need Windows Administrator Permission?(local Administrators Group member)

[SQL Server(Database Engine)]
(1)不需要是Administrators group member
(2)視需求選擇其中一種account
Domain User Account
Local User Account
Local Service Account(NT AUTHORITY\LOCAL SERVICE)
Network Service Account(NT AUTHORITY\NETWORK SERVICE)
Local System Account(NT AUTHORITY\SYSTEM)

Windows 7 and Windows Server 2008 R2 have two new types of service accounts called managed service accounts (MSA) and virtual accounts.
Virtual Accounts
Managed Service Accounts

Configure Windows Service Accounts and Permissions
https://msdn.microsoft.com/en-us/library/ms143504.aspx

 

[SQL Server Agent]
(1)Beginning with SQL 2005, 不需要是Administrators Group member
Beginning with SQL Server 2005, SQL Server Agent no longer requires that the service startup account be a member of the Microsoft Administrators group.
However, there are some restrictions associated with using a non-administrative account for the SQL Server Agent service. For more information, see Service Account Types Supported for SQL Server Agent.
https://msdn.microsoft.com/en-us/library/ms345380(v=sql.105).aspx

(2)如果是domain account可能需要以下權限
From Training kit 70-462
When choosing to use a domain-based security account, ensure that the account has the following permissions:
■ The logon as a service right. You assign this right by using Group Policy.
■ Membership of the Pre-Windows 2000 Compatible Access security group at the domain level. If you do not add the domain-based security account used for the SQL Server Agent service to this group, any jobs owned by domain users who are not members of the local Administrators group on the host computer will fail.

 

PS.

1.Default provisioning login after SQL Server setup finish

2.Default provisioning windows groups


資料治理實施

資料治理實施