Declaring API functions for 64 bit Office (and Mac Office)

Original post:Declaring API functions for 64 bit Office (and Mac Office)

Content

Introduction

If you develop VBA code for multiple versions of Office, you may face a challenge: ensuring your code works on both 32 bit and 64 bit platforms.

This page is meant to be the first stop for anyone who needs the proper syntax for his API declaration statement in Office VBA.

Many of the declarations were figured out by Charles Williams of www.decisionmodels.com when he created the 64 bit version of our Name Manager.

All of these are Windows API calls. Some have Mac equivalents however (like the CopyMemory one). I’ll try to add those as I find them.

Links

Of course Microsoft documents how to do this. There is an introductory article on Microsoft MSDN:

Compatibility Between the 32-bit and 64-bit Versions of Office 2010

That article describes the how-to’s to properly write the declarations. What is missing is which type declarations go with which API function or sub.

Microsoft has provided an updated version of the Win32API.txt with all proper declarations available for download here:

Office 2010 Help Files: Win32API_PtrSafe with 64-bit Support

When you run the installer after downloading the file form the link above, it does not tell you where it installed the information. Look in this -new- folder on your C drive:

C:\Office 2010 Developer Resources\Documents\Office2010Win32API_PtrSafe

You can find a list of the old Win32 API declarations here:

Visual Basic Win32 API Declarations

Microsoft also published a tool to check your code for 64 bit related problems, called the Microsoft Office Code Compatibility inspector addin.

API functions that were added/modified in 64-bit Windows: http://msdn.microsoft.com/en-us/library/aa383663(VS.85).aspx

API Functions by Windows release:

http://msdn.microsoft.com/en-us/library/aa383687(VS.85).aspx

Utter Access API declarations (a comprehensive list of many declarations)

Last, but certainly not least: Dennis Walentin has built an API viewer that is really helpful. You can find the API viewer here.

Declarations by API function

CloseClipboard

#If VBA7 Then
Declare PtrSafe Function CloseClipboard Lib “User32” () As LongPtr
#Else
DeclareFunction CloseClipboard Lib “User32” () AsLong
#End If

CopyMemory

#If Mac Then
PrivateDeclare PtrSafe Function CopyMemory_byVar Lib “libc.dylib” Alias “memmove” (ByRef dest As Any, ByRef src As Any, ByVal size AsLong) As LongPtr
#Else
    #If VBA7 Then
PublicDeclare PtrSafe Sub CopyMemory Lib “kernel32” Alias “RtlMoveMemory” _
               (ByRef destination As Any, ByRef SOURCE As Any, ByVal Length As LongPtr)
    #Else
PublicDeclareSub CopyMemory Lib “kernel32” Alias “RtlMoveMemory” _
                                      (ByRef destination As Any, ByRef SOURCE As Any, ByVal Length AsLong)
    #End If
#End If

CreateProcess

This is a complicated one because it has a lot of arguments. A fully functional example is included below the example declaration lines.
Courtesy: The example code was taken from this page

‘Full example shown below, including the necessary structures
#If VBA7 Then
Declare PtrSafe Function CreateProcess Lib “kernel32” _
                                   Alias “CreateProcessA” (ByVal lpApplicationName AsString, _
ByVal lpCommandLine AsString, _
                                                           lpProcessAttributes As SECURITY_ATTRIBUTES, _
                                                           lpThreadAttributes As SECURITY_ATTRIBUTES, _
ByVal bInheritHandles AsLong, _
ByVal dwCreationFlags AsLong, _
                                                           lpEnvironment As Any, _
ByVal lpCurrentDriectory AsString, _
                                                           lpStartupInfo As STARTUPINFO, _
                                                           lpProcessInformation As PROCESS_INFORMATION) As LongPtr

Const INFINITE = &HFFFF
Const STARTF_USESHOWWINDOW = &H1
PrivateEnum enSW
    SW_HIDE = 0
    SW_NORMAL = 1
    SW_MAXIMIZE = 3
    SW_MINIMIZE = 6
EndEnum

PrivateType PROCESS_INFORMATION
    hProcess AsLongPtr
    hThread AsLongPtr
    dwProcessId AsLong
    dwThreadId AsLong
EndType

PrivateType STARTUPINFO
    cb AsLong
    lpReserved AsString
    lpDesktop AsString
    lpTitle AsString
    dwX AsLong
    dwY AsLong
    dwXSize AsLong
    dwYSize AsLong
    dwXCountChars As Long
    dwYCountChars As Long
    dwFillAttribute AsLong
    dwFlags AsLong
    wShowWindow AsInteger
    cbReserved2 AsInteger
    lpReserved2 AsByte
    hStdInput As LongPtr
    hStdOutput As LongPtr
    hStdError As LongPtr
EndType

PrivateType SECURITY_ATTRIBUTES
    nLength As Long
    lpSecurityDescriptor AsLongPtr
    bInheritHandle AsLong
EndType

PrivateEnum enPriority_Class
    NORMAL_PRIORITY_CLASS = &H20
    IDLE_PRIORITY_CLASS = &H40
    HIGH_PRIORITY_CLASS = &H80
EndEnum
#Else
DeclareFunction CreateProcess Lib “kernel32” _
                                   Alias “CreateProcessA” (ByVal lpApplicationName AsString, _
ByVal lpCommandLine AsString, _
                                                           lpProcessAttributes As SECURITY_ATTRIBUTES, _
                                                           lpThreadAttributes As SECURITY_ATTRIBUTES, _
ByVal bInheritHandles AsLong, _
ByVal dwCreationFlags AsLong, _
                                                           lpEnvironment As Any, _
ByVal lpCurrentDriectory AsString, _
                                                           lpStartupInfo As STARTUPINFO, _
                                                           lpProcessInformation As PROCESS_INFORMATION) AsLong

Const INFINITE = &HFFFF
Const STARTF_USESHOWWINDOW = &H1
PrivateEnum enSW
    SW_HIDE = 0
    SW_NORMAL = 1
    SW_MAXIMIZE = 3
    SW_MINIMIZE = 6
EndEnum

PrivateType PROCESS_INFORMATION
    hProcess AsLong
    hThread AsLong
    dwProcessId AsLong
    dwThreadId AsLong
EndType

PrivateType STARTUPINFO
    cb AsLong
    lpReserved AsString
    lpDesktop AsString
    lpTitle AsString
    dwX AsLong
    dwY AsLong
    dwXSize AsLong
    dwYSize AsLong
    dwXCountChars As Long
    dwYCountChars As Long
    dwFillAttribute AsLong
    dwFlags AsLong
    wShowWindow AsInteger
    cbReserved2 AsInteger
    lpReserved2 AsByte
    hStdInput AsLong
    hStdOutput AsLong
    hStdError AsLong
EndType

PrivateType SECURITY_ATTRIBUTES
    nLength AsLong
    lpSecurityDescriptor AsLong
    bInheritHandle As Long
EndType

PrivateEnum enPriority_Class
    NORMAL_PRIORITY_CLASS = &H20
    IDLE_PRIORITY_CLASS = &H40
    HIGH_PRIORITY_CLASS = &H80
EndEnum
#End If

PrivateFunction SuperShell(ByVal App AsString, ByVal WorkDir AsString, dwMilliseconds AsLong, _
ByVal start_size As enSW, ByVal Priority_Class As enPriority_Class) AsBoolean

Dim pclass AsLong
Dim sinfo As STARTUPINFO
Dim pinfo As PROCESS_INFORMATION
‘Not used, but needed
Dim sec1 As SECURITY_ATTRIBUTES
Dim sec2 As SECURITY_ATTRIBUTES
‘Set the structure size
    sec1.nLength = Len(sec1)
    sec2.nLength = Len(sec2)
    sinfo.cb = Len(sinfo)
‘Set the flags
    sinfo.dwFlags = STARTF_USESHOWWINDOW
‘Set the window’s startup position
    sinfo.wShowWindow = start_size
‘Set the priority class
    pclass = Priority_Class

‘Start the program
If CreateProcess(vbNullString, App, sec1, sec2, False, pclass, _
                     0&, WorkDir, sinfo, pinfo) Then
‘Wait
‘ WaitForSingleObject pinfo.hProcess, dwMilliseconds
        SuperShell = True
Else
        SuperShell = False
EndIf
EndFunction

Sub Test()
Dim sFile AsString
‘Set the dialog’s title
    sFile = Application.GetOpenFilename(“Executables (*.exe), *.exe”, , “”)
    SuperShell sFile, Left(sFile, InStrRev(sFile, “\”)), 0, SW_NORMAL, HIGH_PRIORITY_CLASS
EndSub

DrawMenuBar

#If VBA7 Then
PrivateDeclare PtrSafe Function DrawMenuBar Lib “user32” (ByVal hWnd As LongPtr) AsLong
#Else
PrivateDeclareFunction DrawMenuBar Lib “user32” (ByVal hWnd AsLong) As Long
#End If

EmptyClipboard

#If VBA7 Then
Declare PtrSafe Function EmptyClipboard Lib “User32” () AsLongPtr
#Else
DeclareFunction EmptyClipboard Lib “User32” () As Long
#End If

FindWindow

#If VBA7 Then
PrivateDeclare PtrSafe Function FindWindow Lib “USER32” Alias “FindWindowA” (ByVal lpClassName AsString, ByVal lpWindowName AsString) As LongPtr
#Else
Private Declare Function FindWindow Lib “USER32” Alias “FindWindowA” (ByVal lpClassName As String, ByVal lpWindowName As String) As Long
#End If

FindWindowEx

#If VBA7 Then
    PrivateDeclare PtrSafe Function FindWindowEx Lib “USER32” _
                                  Alias “FindWindowExA” (ByVal hWnd1 As LongPtr, ByVal hWnd2 As LongPtr, _
ByVal lpsz1 AsString, ByVal lpsz2 AsString) As LongPtr
#Else
    PrivateDeclareFunction FindWindowEx Lib “USER32” _
                                  Alias “FindWindowExA” (ByVal hWnd1 AsLong, ByVal hWnd2 AsLong, _
ByVal lpsz1 AsString, ByVal lpsz2 AsString) AsLong
#End If

GdipCreateBitmapFromFile

#If VBA7 Then
PrivateDeclare PtrSafe Function GdipCreateBitmapFromFile Lib “GDIPlus” (ByVal filename As LongPtr, bitmap As LongPtr) AsLongPtr
#Else
PrivateDeclareFunction GdipCreateBitmapFromFile Lib “GDIPlus” (ByVal filename AsLong, bitmap AsLong) As Long
#End If

GdipCreateHBITMAPFromBitmap

#If VBA7 Then
PrivateDeclare PtrSafe Function GdipCreateHBITMAPFromBitmap Lib “GDIPlus” (ByVal bitmap As LongPtr, hbmReturn AsLongPtr, ByVal background As Long) AsLongPtr
#Else
PrivateDeclareFunction GdipCreateHBITMAPFromBitmap Lib “GDIPlus” (ByVal bitmap AsLong, hbmReturn AsLong, ByVal background AsLong) As Long
#End If

GdipDisposeImage

#If VBA7 Then
PrivateDeclare PtrSafe Function GdipDisposeImage Lib “GDIPlus” (ByVal image As LongPtr) AsLongPtr
#Else
PrivateDeclareFunction GdipDisposeImage Lib “GDIPlus” (ByVal image AsLong) As Long
#End If

GdiplusShutdown

#If VBA7 Then
PrivateDeclare PtrSafe Function GdiplusShutdown Lib “GDIPlus” (ByVal token As LongPtr) AsLongPtr
#Else
PrivateDeclareFunction GdiplusShutdown Lib “GDIPlus” (ByVal token AsLong) As Long
#End If

GdiplusStartup

#If VBA7 Then
PrivateDeclare PtrSafe Function GdiplusStartup Lib “GDIPlus” (token As LongPtr, inputbuf As GdiplusStartupInput, OptionalByVal outputbuf As LongPtr = 0) AsLongPtr

PrivateType GdiplusStartupInput
        GdiplusVersion As Long
        DebugEventCallback AsLongPtr
        SuppressBackgroundThread AsLong
        SuppressExternalCodecs AsLong
EndType
#Else
PrivateDeclareFunction GdiplusStartup Lib “GDIPlus” (token AsLong, inputbuf As GdiplusStartupInput, OptionalByVal outputbuf AsLong = 0) AsLong
PrivateType GdiplusStartupInput
        GdiplusVersion AsLong
        DebugEventCallback AsLong
        SuppressBackgroundThread AsLong
        SuppressExternalCodecs As Long
EndType

#End If

GetClassName

#If VBA7 Then
PrivateDeclare PtrSafe Function GetClassName Lib “user32” Alias “GetClassNameA” _
(ByVal hWnd As LongPtr, ByVal lpClassName AsString, _
ByVal nMaxCount As LongPtr) AsLong
#Else
PrivateDeclareFunction GetClassName Lib “user32” Alias “GetClassNameA” _
(ByVal hWnd AsLong, ByVal lpClassName AsString, _
ByVal nMaxCount AsLong) As Long
#End If

GetDiskFreeSpaceEx

#If VBA7 Then
PrivateDeclare PtrSafe Function GetDiskFreeSpaceEx Lib “kernel32” Alias _
            “GetDiskFreeSpaceExA” (ByVal lpDirectoryName AsString, _
                                   lpFreeBytesAvailableToCaller AsCurrency, lpTotalNumberOfBytes As _
Currency, lpTotalNumberOfFreeBytes AsCurrency) As LongPtr
#Else
PrivateDeclareFunction GetDiskFreeSpaceEx Lib “kernel32” _
                                                Alias “GetDiskFreeSpaceExA” (ByVal lpDirectoryName AsString, _
                                                                             lpFreeBytesAvailableToCaller AsCurrency, _
                                                                             lpTotalNumberOfBytes As Currency, _
                                                                             lpTotalNumberOfFreeBytes AsCurrency) AsLong
#End If

GetDC

#If VBA7 Then
PrivateDeclare PtrSafe Function GetDC Lib “user32” (ByVal hWnd As LongPtr) AsLongPtr
#Else
PrivateDeclareFunction GetDC Lib “user32” (ByVal hWnd AsLong) As Long
#End If

GetDesktopWindow

#If VBA7 Then
PrivateDeclare PtrSafe Function GetDesktopWindow Lib “user32” () As LongPtr
#Else
PrivateDeclareFunction GetDesktopWindow Lib “user32” () AsLong
#End If

getDeviceCaps

#If VBA7 Then
PrivateDeclare PtrSafe Function GetDeviceCaps Lib “gdi32” (ByVal hDC As LongPtr, ByVal nIndex AsLong) AsLong
#Else
PrivateDeclareFunction GetDeviceCaps Lib “gdi32” (ByVal hDC AsLong, ByVal nIndex AsLong) AsLong
#End If

GetDriveType

#If VBA7 Then
PrivateDeclare PtrSafe Function GetDriveType Lib “kernel32” Alias _
“GetDriveTypeA” (ByVal sDrive AsString) As LongPtr
#Else
PrivateDeclareFunction GetDriveType Lib “kernel32” Alias _
“GetDriveTypeA” (ByVal sDrive AsString) AsLong
#End If

GetExitCodeProcess

#If VBA7 Then
Declare PtrSafe Function GetExitCodeProcess Lib “kernel32” (ByVal _
hProcess As LongPtr, lpExitCode As Long) AsLong
#Else
DeclareFunction GetExitCodeProcess Lib “kernel32” (ByVal _
hProcess AsLong, lpExitCode AsLong) As Long
#End If

GetForegroundWindow

#If VBA7 Then
Declare PtrSafe Function GetForegroundWindow Lib “user32.dll” () As LongPtr
#Else
DeclareFunction GetForegroundWindow Lib “user32.dll” () AsLong
#End If

GetFrequency

#If VBA7 Then
PrivateDeclare PtrSafe Function GetFrequency Lib “kernel32” Alias “QueryPerformanceFrequency” (cyFrequency AsCurrency) AsLong
#Else
PrivateDeclareFunction GetFrequency Lib “kernel32” Alias “QueryPerformanceFrequency” (cyFrequency AsCurrency) AsLong
#End If

GetKeyState

#If VBA7 Then
Declare PtrSafe Function GetKeyState Lib “USER32” (ByVal vKey AsLong) AsInteger
#Else
DeclareFunction GetKeyState Lib “USER32” (ByVal vKey AsLong) AsInteger
#End If

GetLastInputInfo

#If VBA7 Then
PrivateType LASTINPUTINFO
        cbSize As LongPtr
        dwTime As LongPtr
EndType
PrivateDeclare PtrSafe Sub GetLastInputInfo Lib “USER32” (ByRef plii As LASTINPUTINFO)
#Else
PrivateType LASTINPUTINFO
        cbSize AsLong
        dwTime AsLong
EndType
PrivateDeclareSub GetLastInputInfo Lib “USER32” (ByRef plii As LASTINPUTINFO)
#End If

GetOpenFileName

OptionExplicit

#If VBA7 Then
PrivateDeclare PtrSafe Function GetOpenFileName Lib “comdlg32.dll” Alias _
            “GetOpenFileNameA” (pOpenfilename As OPENFILENAME) AsLong

PrivateType OPENFILENAME
        lStructSize AsLong
        hwndOwner AsLongPtr
        hInstance AsLongPtr
        lpstrFilter AsString
        lpstrCustomFilter AsString
        nMaxCustFilter AsLong
        nFilterIndex AsLong
        lpstrFile AsString
        nMaxFile AsLong
        lpstrFileTitle AsString
        nMaxFileTitle AsLong
        lpstrInitialDir AsString
        lpstrTitle AsString
        flags AsLong
        nFileOffset AsInteger
        nFileExtension AsInteger
        lpstrDefExt AsString
        lCustData AsLongPtr
        lpfnHook AsLongPtr
        lpTemplateName AsString
EndType

#Else

PrivateDeclareFunction GetOpenFileName Lib “comdlg32.dll” Alias _
            “GetOpenFileNameA” (pOpenfilename As OPENFILENAME) AsLong

PrivateType OPENFILENAME
        lStructSize AsLong
        hwndOwner AsLong
        hInstance AsLong
        lpstrFilter AsString
        lpstrCustomFilter AsString
        nMaxCustFilter AsLong
        nFilterIndex AsLong
        lpstrFile AsString
        nMaxFile AsLong
        lpstrFileTitle AsString
        nMaxFileTitle AsLong
        lpstrInitialDir AsString
        lpstrTitle AsString
        flags AsLong
        nFileOffset AsInteger
        nFileExtension AsInteger
        lpstrDefExt AsString
        lCustData AsLong
        lpfnHook AsLong
        lpTemplateName AsString
EndType
#End If
‘/////////////////////////////////
‘// End code GetOpenFileName    //
‘/////////////////////////////////

PrivateFunction GetMyFile(strTitle AsString) AsString

Dim OpenFile    As OPENFILENAME
Dim lReturn     AsLong

    OpenFile.lpstrFilter = “”
    OpenFile.nFilterIndex = 1
    OpenFile.hwndOwner = 0
    OpenFile.lpstrFile = String(257, 0)
    #If VBA7 Then
        OpenFile.nMaxFile = LenB(OpenFile.lpstrFile) – 1
        OpenFile.lStructSize = LenB(OpenFile)
    #Else
        OpenFile.nMaxFile = Len(OpenFile.lpstrFile) – 1
        OpenFile.lStructSize = Len(OpenFile)
    #EndIf
    OpenFile.lpstrFileTitle = OpenFile.lpstrFile
    OpenFile.nMaxFileTitle = OpenFile.nMaxFile
    OpenFile.lpstrInitialDir = “C:\”
    OpenFile.lpstrTitle = strTitle
    OpenFile.flags = 0
    lReturn = GetOpenFileName(OpenFile)

If lReturn = 0 Then
        GetMyFile = “”
Else
        GetMyFile = Trim(Left(OpenFile.lpstrFile, InStr(1, OpenFile.lpstrFile, vbNullChar) – 1))
EndIf

EndFunction

GetSystemMetrics

#If VBA7 Then
PrivateDeclare PtrSafe Function GetSystemMetrics Lib “user32” (ByVal nIndex AsLong) AsLong
#Else
PrivateDeclareFunction GetSystemMetrics Lib “user32” (ByVal nIndex AsLong) AsLong
#End If

GetTempPath

#If VBA7 Then
PrivateDeclare PtrSafe Function GetTempPath Lib “kernel32” _
Alias “GetTempPathA” (ByVal nBufferLength As LongPtr, _
ByVal lpbuffer AsString) AsLong
#Else
PrivateDeclareFunction GetTempPath Lib “kernel32” _
Alias “GetTempPathA” (ByVal nBufferLength AsLong, _
ByVal lpbuffer AsString) AsLong
#End If

getTickCount

#If VBA7 Then
PrivateDeclare PtrSafe Function getTickCount Lib “kernel32” Alias “QueryPerformanceCounter” (cyTickCount AsCurrency) AsLong
#Else
PrivateDeclareFunction getTickCount Lib “kernel32” Alias “QueryPerformanceCounter” (cyTickCount AsCurrency) AsLong
#End If

timeGetTime

#If VBA7 Then
PrivateDeclare PtrSafe Function timeGetTime Lib “winmm.dll” () AsLong
#Else
PrivateDeclareFunction timeGetTime Lib “winmm.dll” () AsLong
#End If

GetWindow

#If VBA7 Then
PrivateDeclare PtrSafe Function GetWindow Lib “user32” _
(ByVal hWnd As LongPtr, ByVal wCmd AsLong) As LongPtr
#Else
PrivateDeclareFunction GetWindow Lib “user32” _
(ByVal hWnd AsLong, ByVal wCmd AsLong) AsLong
#End If

GetWindowLong

This is one of the few API functions that requires the Win64 compile constant:

#If VBA7 Then
    #If Win64 Then
PrivateDeclare PtrSafe Function GetWindowLongPtr Lib “USER32” Alias “GetWindowLongPtrA” (ByVal hWnd As LongPtr, ByVal nIndex AsLong) As LongPtr
    #Else
    PrivateDeclare PtrSafe Function GetWindowLongPtr Lib “USER32” Alias “GetWindowLongA” (ByVal hWnd As LongPtr, ByVal nIndex As Long) As LongPtr
    #End If
#Else
PrivateDeclareFunction GetWindowLong Lib “USER32” Alias “GetWindowLongA” (ByVal hWnd As Long, ByVal nIndex AsLong) As Long
#End If

GetWindowsDirectory

#If VBA7 Then
PrivateDeclare PtrSafe Function GetWindowsDirectory& Lib “kernel32” Alias _
“GetWindowsDirectoryA” (ByVal lpbuffer AsString, _
ByVal nSize As LongPtr)
#Else
PrivateDeclareFunction GetWindowsDirectory& Lib “kernel32” Alias _
“GetWindowsDirectoryA” (ByVal lpbuffer AsString, _
ByVal nSize AsLong)
#End If

GetWindowText

#If VBA7 Then
PrivateDeclare PtrSafe Function GetWindowText Lib “user32” Alias “GetWindowTextA” _
(ByVal hWnd As LongPtr, ByVal lpString As String, _
ByVal cch As LongPtr) AsLong
#Else
PrivateDeclareFunction GetWindowText Lib “user32” Alias “GetWindowTextA” _
(ByVal hWnd AsLong, ByVal lpString As String, _
ByVal cch AsLong) As Long
#End If

GetWindowTextLength

#If VBA7 Then
PrivateDeclare PtrSafe Function GetWindowTextLength Lib “user32” Alias “GetWindowTextLengthA” _
(ByVal hWnd As LongPtr) AsLong
#Else
PrivateDeclareFunction GetWindowTextLength Lib “user32” Alias “GetWindowTextLengthA” _
(ByVal hWnd AsLong) As Long
#End If

GlobalAlloc

#If VBA7 Then
PrivateDeclare PtrSafe Function GlobalAlloc Lib “kernel32” (ByVal wFlags AsLong, ByVal dwBytes As LongPtr) As LongPtr
#Else
PrivateDeclareFunction GlobalAlloc Lib “kernel32” (ByVal wFlags AsLong, ByVal dwBytes AsLong) AsLong
#End If

GlobalLock

#If VBA7 Then
PrivateDeclare PtrSafe Function GlobalLock Lib “kernel32” (ByVal hMem As LongPtr) AsLongPtr
#Else
PrivateDeclareFunction GlobalLock Lib “kernel32” (ByVal hMem AsLong) As Long
#End If

InternetGetConnectedState

#If VBA7 Then
PrivateDeclare PtrSafe Function InternetGetConnectedState _
Lib “wininet.dll” (lpdwFlags As LongPtr, _
ByVal dwReserved AsLong) AsBoolean
#Else
PrivateDeclareFunction InternetGetConnectedState _
Lib “wininet.dll” (lpdwFlags AsLong, _
ByVal dwReserved AsLong) AsBoolean
#End If

IsCharAlphaNumericA

#If VBA7 Then
PrivateDeclare PtrSafe Function IsCharAlphaNumericA Lib “user32” (ByVal byChar AsByte) AsLong
#Else
PrivateDeclareFunction IsCharAlphaNumericA Lib “user32” (ByVal byChar AsByte) AsLong
#End If

lstrcpy

#If VBA7 Then
PrivateDeclare PtrSafe Function lstrcpy Lib “kernel32” (ByVal lpString1 As Any, ByVal lpString2 As Any) As LongPtr
#Else
PrivateDeclareFunction lstrcpy Lib “kernel32” (ByVal lpString1 As Any, ByVal lpString2 As Any) AsLong
#End If

Mouse_Event

#If VBA7 Then
Private Declare PtrSafe Sub mouse_event Lib “user32” (ByVal dwFlags AsLong, ByVal dx AsLong, _
ByVal dy AsLong, ByVal cButtons AsLong, _
ByVal dwExtraInfo As LongPtr)
#Else
PrivateDeclareSub mouse_event Lib “user32” (ByVal dwFlags AsLong, ByVal dx AsLong, _
ByVal dy AsLong, ByVal cButtons AsLong, _
ByVal dwExtraInfo AsLong)
#End If
PrivateConst MOUSEEVENTF_MOVE = &H1 ‘ mouse move

OleCreatePictureIndirect

#If VBA7 Then
PrivateDeclare PtrSafe Function OleCreatePictureIndirect Lib “oleaut32.dll” (PicDesc As PICTDESC, RefIID As GUID, ByVal fPictureOwnsHandle As LongPtr, IPic As IPicture) As LongPtr

PrivateType PICTDESC
        Size AsLong
TypeAsLong
        hPic As LongPtr
        hPal AsLongPtr
EndType
#Else
PrivateDeclareFunction OleCreatePictureIndirect Lib “oleaut32.dll” (PicDesc As PICTDESC, RefIID As GUID, ByVal fPictureOwnsHandle AsLong, IPic As IPicture) AsLong

PrivateType PICTDESC
        Size AsLong
TypeAsLong
        hPic AsLong
        hPal As Long
EndType
#End If

OleTranslateColor

#If VBA7 Then
PrivateDeclare PtrSafe Function OleTranslateColor Lib “oleaut32.dll” (ByVal lOleColor AsLong, _
ByVal lHPalette AsLong, lColorRef AsLong) AsLong
#Else
PrivateDeclareFunction OleTranslateColor Lib “olepro32.dll” (ByVal lOleColor AsLong, _
ByVal lHPalette AsLong, ByRef lColorRef AsLong) AsLong
#End If

OpenClipboard

#If VBA7 Then
PrivateDeclare PtrSafe Function OpenClipboard Lib “user32” (ByVal hWnd As LongPtr) AsLongPtr
#Else
PrivateDeclareFunction OpenClipboard Lib “user32” (ByVal hWnd AsLong) As Long
#End If

OpenProcess

#If VBA7 Then
Declare PtrSafe Function OpenProcess Lib “kernel32” (ByVal _
        dwDesiredAccess AsLong, ByVal bInheritHandle AsLong, ByVal _
        dwProcessId AsLong) As LongPtr
#Else
DeclareFunction OpenProcess Lib “kernel32” (ByVal _
        dwDesiredAccess AsLong, ByVal bInheritHandle AsLong, ByVal _
        dwProcessId AsLong) AsLong
#End If

ReleaseDC

#If VBA7 Then
PrivateDeclare PtrSafe Function ReleaseDC Lib “user32” (ByVal hWnd As LongPtr, ByVal hDC As LongPtr) AsLong
#Else
PrivateDeclareFunction ReleaseDC Lib “user32” (ByVal hWnd AsLong, ByVal hDC AsLong) As Long
#End If

SendMessage

#If VBA7 Then
PrivateDeclare PtrSafe Function SendMessageA Lib “user32” (ByVal hWnd As LongPtr, ByVal wMsg AsLong, _
ByVal wParam As LongPtr, lParam As Any) As LongPtr
#Else
PrivateDeclareFunction SendMessageA Lib “user32” (ByVal hWnd AsLong, ByVal wMsg AsLong, _
ByVal wParam AsLong, lParam As Any) AsLong
#End If

SetActiveWindow

#If VBA7 Then
PrivateDeclare PtrSafe Function SetActiveWindow Lib “user32.dll” (ByVal hWnd As LongPtr) AsLongPtr
#Else
PrivateDeclareFunction SetActiveWindow Lib “user32.dll” (ByVal hWnd AsLong) As Long
#End If

SetClipboardData

#If VBA7 Then
PrivateDeclare PtrSafe Function SetClipboardData Lib “user32” (ByVal wFormat As LongPtr, ByVal hMem As LongPtr) AsLongPtr
#Else
PrivateDeclareFunction SetClipboardData Lib “user32” (ByVal wFormat AsLong, ByVal hMem AsLong) As Long
#End If

SetCurrentDirectory

#If VBA7 Then
PrivateDeclare PtrSafe Function SetCurrentDirectoryA Lib “kernel32” (ByVal lpPathName AsString) AsLong
#Else
PrivateDeclareFunction SetCurrentDirectoryA Lib “kernel32” (ByVal lpPathName AsString) AsLong
#End If

SetWindowLongPtr

This is another one of the few API functions that require the Win64 compile constant:

#If VBA7 Then
    #If Win64 Then
PrivateDeclare PtrSafe Function SetWindowLongPtr Lib “USER32” Alias “SetWindowLongPtrA” (ByVal hWnd As LongPtr, ByVal nIndex AsLong, ByVal dwNewLong As LongPtr) As LongPtr
    #Else
    PrivateDeclareFunction SetWindowLongPtr Lib “USER32” Alias “SetWindowLongA” (ByVal hWnd As LongPtr, ByVal nIndex As Long, ByVal dwNewLong As LongPtr) As LongPtr
    #End If
#Else
PrivateDeclareFunction SetWindowLong Lib “USER32” Alias “SetWindowLongA” (ByVal hWnd As Long, ByVal nIndex AsLong, ByVal dwNewLong As Long) As Long
#End If

SetWindowPos

#If VBA7 Then
PrivateDeclare PtrSafe Function SetWindowPos _
Lib “user32” (ByVal hwnd As LongPtr, ByVal hWndInsertAfter As LongPtr, _
ByVal X AsLong, ByVal Y AsLong, ByVal cx AsLong, _
ByVal cy AsLong, ByVal wFlags AsLong) AsLong
#Else
PrivateDeclareFunction SetWindowPos _
Lib “user32” (ByVal hwnd AsLong, _
ByVal hWndInsertAfter AsLong, _
ByVal X AsLong, ByVal Y AsLong, _
ByVal cx AsLong, _
ByVal cy AsLong, _
ByVal wFlags AsLong) AsLong
#End If

SHBrowseForFolder

#If VBA7 Then
PrivateType BROWSEINFO
        hOwner As LongPtr
        pidlRoot AsLongPtr
        pszDisplayName AsString
        lpszTitle AsString
        ulFlags AsLong
        lpfn As LongPtr
        lParam As LongPtr
        iImage AsLong
EndType

PrivateDeclare PtrSafe Function SHBrowseForFolder Lib “shell32.dll” Alias “SHBrowseForFolderA” _
        (lpBrowseInfo As BROWSEINFO) As LongPtr
#Else
PrivateType BROWSEINFO
        hOwner AsLong
        pidlRoot AsLong
        pszDisplayName AsString
        lpszTitle AsString
        ulFlags AsLong
        lpfn AsLong
        lParam AsLong
        iImage AsLong
EndType

PrivateDeclareFunction SHBrowseForFolder Lib “shell32.dll” Alias “SHBrowseForFolderA” _
        (lpBrowseInfo As BROWSEINFO) AsLong
#End If
PrivateConst BIF_RETURNONLYFSDIRS = &H1

ShellExecute

#If VBA7 Then
PrivateDeclare PtrSafe Function ShellExecute Lib “shell32.dll” Alias “ShellExecuteA” ( _
ByVal hWnd As LongPtr, ByVal lpOperation AsString, ByVal lpFile AsString, _
ByVal lpParameters AsString, ByVal lpDirectory AsString, ByVal nShowCmd AsLong) As LongPtr
#Else
PrivateDeclareFunction ShellExecute Lib “shell32.dll” Alias “ShellExecuteA” ( _
ByVal hWnd AsLong, ByVal lpOperation AsString, ByVal lpFile AsString, _
ByVal lpParameters AsString, ByVal lpDirectory AsString, ByVal nShowCmd AsLong) AsLong

#End If

SHFileOperation

#If VBA7 Then
Type SHFILEOPSTRUCT
        hWnd As LongPtr
        wFunc As Long
        pFrom AsString
        pTo AsString
        fFlags AsInteger
        fAborted AsBoolean
        hNameMaps As Longptr
        sProgress AsString
EndType
Declare PtrSafe Function SHFileOperation Lib “shell32.dll” Alias “SHFileOperationA” _
                                     (lpFileOp As SHFILEOPSTRUCT) AsLongPtr
#Else
Type SHFILEOPSTRUCT
        hWnd AsLong
        wFunc AsLong
        pFrom AsString
        pTo AsString
        fFlags AsInteger
        fAborted AsBoolean
        hNameMaps AsLong
        sProgress AsString
EndType
DeclareFunction SHFileOperation Lib “shell32.dll” Alias “SHFileOperationA” _
                                     (lpFileOp As SHFILEOPSTRUCT) As Long
#End If

SHGetPathFromIDList

#If VBA7 Then
PrivateDeclare PtrSafe Function SHGetPathFromIDList Lib “shell32.dll” Alias “SHGetPathFromIDListA” _
(ByVal pidl As LongPtr, ByVal pszPath AsString) AsBoolean
#Else
PrivateDeclareFunction SHGetPathFromIDList Lib “shell32.dll” Alias “SHGetPathFromIDListA” _
(ByVal pidl AsLong, ByVal pszPath AsString) AsBoolean
#End If

SHGetSpecialFolderLocation

#If VBA7 Then
PrivateDeclare PtrSafe Function SHGetSpecialFolderLocation Lib _
            “shell32.dll” (ByVal hwndOwner As LongPtr, ByVal nFolder AsLong, _
                           pidl As ITEMIDLIST) As LongPtr
PrivateType SHITEMID
        cb As LongPtr
        abID AsByte
EndType
#Else
PrivateDeclareFunction SHGetSpecialFolderLocation Lib _
            “shell32.dll” (ByVal hwndOwner AsLong, ByVal nFolder AsLong, _
                           pidl As ITEMIDLIST) AsLong
PrivateType SHITEMID
        cb AsLong
        abID AsByte
EndType
#End If
PrivateType ITEMIDLIST
    mkid As SHITEMID
EndType

timeGetTime

#If VBA7 Then
PrivateDeclare PtrSafe Function timeGetTime Lib “winmm.dll” () AsLong
#Else
PrivateDeclareFunction timeGetTime Lib “winmm.dll” () AsLong
#End If

URLDownloadToFile

#If VBA7 Then
PrivateDeclare PtrSafe Function URLDownloadToFile Lib “urlmon” _
Alias “URLDownloadToFileA” (ByVal pCaller AsLongPtr, _
ByVal szURL AsString, ByVal szFileName AsString, _
ByVal dwReserved AsLongPtr, ByVal lpfnCB AsLongPtr) AsLong
#Else
PrivateDeclareFunction URLDownloadToFile Lib “urlmon” _
Alias “URLDownloadToFileA” (ByVal pCaller AsLong, _
ByVal szURL AsString, ByVal szFileName AsString, _
ByVal dwReserved AsLong, ByVal lpfnCB AsLong) AsLong
#End If

Which Longs should become LongPtr?

It’s actually pretty easy to determine what requires LongPtr and what can stay as Long. The only things that require LongPtr are function arguments or return values that represent addresses in memory. This is because a 64-bit OS has a memory space that is too large to hold in a Long data type variable. Arguments or return values that represent data will still be declared Long even in 64-bit.

The SendMessage API is a good example because it uses both types:

32-bit:

PrivateDeclareFunction SendMessageA Lib “user32” (ByVal hWnd AsLong, ByVal wMsg AsLong, _
ByVal wParam AsLong, lParam As Any) AsLong

64 bit:

PrivateDeclare PtrSafe Function SendMessageA Lib “user32” (ByVal hWnd As LongPtr, ByVal wMsg AsLong, _
ByVal wParam AsLongPtr, lParam As Any) As LongPtr

The first argument -hWnd- is a window handle, which is an address in memory. The return value is a pointer to a function, which is also an address in memory. Both of these must be declared LongPtr in 64-bit VBA. The argument wMsg is used to pass data, so can be Long in both 32-bit and 64-bit.

How to determine what is a memory address and what is data? You just have to read the MSDN documentation for the API functions (the C++ version) and it will tell you. Anything called a handle, pointer, brush or any other object type will require a LongPtr in 64-bit. Anything that is strictly data can stay as Long.

Conditional compiling

If your code needs to run on both 32 bit and 64 bit Excel, then another thing to do is add conditional compilation to your VBA.

Microsoft devised two compile constants to handle this:

VBA7: True if you’re using Office 2010, False for older versions

WIN64: True if your Office installation is 64 bit, false for 32 bit.

Since the 64 bit declarations also work on 32 bit Office 2010, all you have to test for is VBA7:

And then in the routine where this function is put to use:

#If VBA7 Then
Dim hDC As LongPtr
#Else
Dim hDC AsLong
#EndIf
Dim lDotsPerInch AsLong
‘Get the user’s DPI setting
lDotsPerInch = GetDeviceCaps(hDC, LOGPIXELSX)

使用smtp.office365.com时Database Mail 的一些额外设置

Basic Authentication
PORT = 587
Use SSL = True
Server Name = smtp.office365.com

TLS 1.2 is required.

How to set up a multifunction device or application to send email using Microsoft 365 or Office 365 also says:

Transport Layer Security (TLS): Your device must be able to use TLS version 1.2 and above.

DatabaseMail.exe is built for .NET Framework 3.5, but you need a .NET Framework installed that supports TLS 1.2 (.NET Framework 4.5.2 or later).
TLS 1.2 client protocol should be enabled at the machine level in Registry

[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.2\Client]
"DisabledByDefault"=dword:00000000
"Enabled"=dword:00000001

TLS 1.2 client protocol should be enabled for .NET Framework 4.x in the Registry

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\.NETFramework\v4.0.30319]
"SchUseStrongCrypto"=dword:00000001

[HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\.NETFramework\v4.0.30319]
"SchUseStrongCrypto"=dword:00000001

An appropriate supportedRuntime should be in DatabaseMail.exe.config file, e.g.: with Microsoft .NET Framework 4.5.2 installed:

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <appSettings>
    <add key="DatabaseServerName" value="." />
    <add key="DatabaseName" value="msdb" />
  </appSettings>
  <startup> 
    <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.5.2" />
  </startup>
</configuration>

Via the Database Mail settings in SSMS, configure the sending account appropriately:

Server name: smtp.office365.com
Port number: 587 (preferred, or 25)
This server requires a secure connection (SSL): must be ticked (this enables STARTTLS)
SMTP Authentication:
- Basic authentication (selected)
  - User name: sending_mailbox_user@your_domain.com
  - Password: your_office365_password
  - Confirm password: your_office365_password_again

Form Post to Existing window issue in IE11

Problem resolved by support team from Microsoft Developer Support for Internet Explorer.

The frame/dialog targeting problem described above is the result of a change introduced with installation of IE11 on a computer, affecting behavior of User Account Control: Use Admin Approval Mode for the built-in Administrator account setting within local security policy.

This setting can be configured using the Local Security Policy snap-in, in Local Policies / Security Options branch, under Policy item called: “User Account Control: Use Admin Approval Mode for the built-in Administrator account”.

In simple terms, the steps needed to correct the problem are:

Before installation of IE11 – no need to do anything on any Windows or IE version to get frame targeting to work under default Admin. It works even with “User Account Control: Use Admin Approval Mode …” set to Disabled.

After installation of IE11 – need to Enable “User Account Control: Use Admin Approval Mode for the built-in Administrator account” to get frame/dialog targeting to work for default Admin.

Confirmed the solution works with IE11 on Windows 2012R2 as well as Windows 7 SPK1, the adjustment takes effect after a restart. There is no need to make any changes to HTML pages.

https://social.msdn.microsoft.com/Forums/ie/en-US/a5c294e2-e407-491d-ba6a-b7f7edbcabaf/ie11-cant-post-form-data-to-specific-frame-or-window-dialog-opened-via-windowopen?forum=iewebdevelopment

Exchange Online PowerShell V2 use cases and small tutorial

—https://blog.it-koehler.com/en/Archive/3170

Dealing with PowerShell is a great thing, especially if you have to administer a large and dynamic organization. Let’s spend some time to learn some default use cases lot of exchange admins have to do. In this article I’m showing a various combination of different PowerShell cmdlets, with special focus in the new Exchange Online cmdlets. You can do a much more crazy stuff, but I want to show some basics.
Enough blabla, lets go.

Installation of the new PowerShell Module is quite easy, just follow the instructions on this link.
Here are my commands to install, import and connect EX ON Module:

Install-Module -Name ExchangeOnlineManagement -Scope AllUsers -Verbose -ForceImport-Module -Name ExchangeOnlineManagement -VerboseGet-Module ExchangeOnlineManagement$creds = Get-CredentialConnect-ExchangeOnline -Credential $creds #Connect-ExchangeOnline -Credential $creds -ShowBanner:$false

After connecting (if you didn’t switch off) you see the new cmdlets in PowerShell console:

As you can see, most of the cmdlets are Get-commands, so the new version at the moment is for analysis. If you want to have an overview of all cmdlets included in this module, use this oneliner.

Get-Module ExchangeOnlineManagement | Select-Object ExportedCommands -ExpandProperty ExportedCommands

After this short introduction, what can we do with this set of cmdlets? Playing around with this new cmdlets

Get-EXONMailbox references

This cmdlet is used to get information about mailbox settings, see also propertysets described in this link.

#1.#find all mailboxes with "alex" in the following attributes:#CommonName (CN),DisplayName,FirstName,LastName,AliasGet-EXOMailbox -Anr alex

#2.#using different propertysets to get special information

Get-EXOMailbox -PropertySets Quota#combine different propertysets Get-EXOMailbox -PropertySets Quota,Delivery#combine different propertysets with individual propertiesGet-EXOMailbox -PropertySets Quota,Delivery -Properties EmailAddresses,RecipientTypeDetails

#3.#find users with special attributes

#find all shared mailboxes and their delivery options

Get-EXOMailbox -PropertySets Delivery -Properties RecipientTypeDetails | Where-Object{$_.RecipientTypeDetails -eq "SharedMailbox"} | Sort-Object UserPrincipalName
#find all usermailboxes with their delivery options
Get-EXOMailbox -PropertySets Delivery -Properties RecipientTypeDetails | Where-Object{$_.RecipientTypeDetails -eq "UserMailbox"} | Sort-Object UserPrincipalName

Get-EXOCasMailbox

with “Get-EXOCasMailbox you can see for example the connections properties for your mailboxes (POP/IMAP/MAPI…), CAS = Client Access Service
Link to the propertysets

#1.
#see all mailboxes with their cas settings 
Get-EXOCasMailbox

#2.
#see important information with different propertysets
Get-EXOCasMailbox -PropertySets Minimum
#see all mailboxes IMAP and POP settings
Get-EXOCasMailbox -PropertySets Imap,pop

#3.
#see all users with POP enabled
Get-EXOCasMailbox -PropertySets IMAP,POP | Where-Object {$_.PopEnabled -eq $true}
#see all users with POP and IMAP enabled
Get-EXOCasMailbox -PropertySets IMAP,POP | Where-Object {($_.PopEnabled -eq $true) -and ($_.ImapEnabled -eq $true)}
#see all users OWA enabled
Get-EXOCasMailbox -Properties OWAEnabled| Where-Object {$_.OWAEnabled -eq $true}

Get-EXOMailboxStatistics

With this cmdlet you are able to see sizes of all mailboxes

#see mailboxsize of special user 
#UPN,emailaddress,GUID are accepted parameters
Get-EXOMailboxStatistics -Identity alexander
Get-MailboxFolderStatistics -Identity alexander | ft Folderpath,FolderSize 
#see statistics of all users
Get-EXOMailbox | Get-EXOMailboxStatistics
#see statistics of all shared mailboxes
Get-EXOMailbox | Where-Object{$_.RecipientTypeDetails -eq "SharedMailbox"} | Get-EXOMailboxStatistics
#see detailed statistics of all users
Get-EXOMailbox | Get-EXOMailboxStatistics -PropertySets All

Script to get biggest mailbox

$Mailboxstats = Get-EXOMailbox | Get-EXOMailboxStatistics 
$MailboxStats | Add-Member -MemberType ScriptProperty -Name TotalItemSizeInBytes -Value {$this.TotalItemSize -replace "(.*\()|,| [a-z]*\)", ""} 
$overview = $MailboxStats | Select-Object DisplayName, TotalItemSizeInBytes,@{Name="TotalItemSize (GB)"; Expression={[math]::Round($_.TotalItemSizeInBytes/1GB,2)}} | Sort-Object "TotalItemSize (GB)" -Descending
$overview

Script to get all mailboxes bigger than…

#greaterthan value in GB and with "." as comma
$greaterthan = "0.5"
$Mailboxstats = Get-EXOMailbox | Get-EXOMailboxStatistics
$MailboxStats | Add-Member -MemberType ScriptProperty -Name TotalItemSizeInBytes -Value {$this.TotalItemSize -replace "(.*\()|,| [a-z]*\)", ""} 
$overview = $MailboxStats | Select-Object DisplayName,TotalItemSizeInBytes,@{Name="TotalItemSizeInGB"; Expression={[math]::Round($_.TotalItemSizeInBytes/1GB,2)}} | Sort-Object "TotalItemSizeInGB" -Descending
$overview | Where-Object {$_.TotalItemSizeInGB -gt "$greaterthan"}

Script to get all mailboxes and sum of all mailbox sizes

$Mailboxstats = Get-EXOMailbox -ResultSize unlimited | Get-EXOMailboxStatistics 
$MailboxStats | Add-Member -MemberType ScriptProperty -Name TotalItemSizeInBytes -Value {$this.TotalItemSize -replace "(.*\()|,| [a-z]*\)", ""} 
$overview = $MailboxStats | Select-Object DisplayName, TotalItemSizeInBytes,@{Name="TotalItemSizeInGB"; Expression={[math]::Round($_.TotalItemSizeInBytes/1GB,2)}} | Sort-Object "TotalItemSizeInGB" -Descending
$sumtemp = ($overview | Measure-Object TotalItemSizeInGB -Sum).Sum
$sum = $sumtemp.ToString()
$sum = "Sum of all Mailboxes in GB: "+$sum+" GB"
Write-Host "$sum" -ForegroundColor Yellow
$overview

Get-EXOMailboxFolderPermission

Getting all permissions inside a mailbox

#1.
#get special permission on one folder (need to know the foldername)
Get-EXOMailboxFolderPermission -Identity alexander:\Inbox

#2.
#get folders from one mailbox with their permissions
$email = "blablabla@it-koehler.com"
$folders = (Get-EXOMailboxFolderStatistics -Identity $email)
$perm = @()
foreach($folder in $folders){#
  $fname = ($folder.Folderpath -replace '/','\') 
  $foldername = $email +":" +$fname
  $temp = Get-EXOMailboxFolderPermission -Identity "$foldername" -ErrorAction SilentlyContinue | Select-Object Identity,User,AccessRights
  $perm += $temp
}
$perm | ft

Get-EXOMobileDeviceStatistics

Finding mobile devices connected with mailboxes

#1.
#get all mobile devices of one mailbox
Get-EXOMobileDeviceStatistics -Mailbox blablabla@it-koehler.com | ft DeviceFriendlyName,DeviceOS,DeviceID,DeviceImei,FirstSyncTime,LastSuccessSync

#2.
#get all users and show their mobile devices
$UPN = (Get-EXOMailbox -ResultSize unlimited).Userprincipalname 
foreach($user in $UPN){
  $mobiles = Get-EXOMobileDeviceStatistics -Mailbox $user -ErrorAction SilentlyContinue
  if($mobiles){
    Write-Host "User: $user" -ForegroundColor Yellow
    $mobiles | ft DeviceFriendlyName,DeviceOS,DeviceID,DeviceImei,FirstSyncTime,LastSuccessSync
  } 
}

Disconnect-ExchangeOnline

Disconnect-ExchangeOnline -Confirm:$false

There are much more possibilities with these cmdlets, if you have any questions or did some scripting on your own, write me an comment or email, we can share it here. If you liked these lines, please click on helpful. Stay tuned.

XML TAGS simplifies Excel Download

Source:https://wiki.scn.sap.com/wiki/display/Snippets/XML+TAGS+simplifies+Excel+Download?original_fqdn=wiki.sdn.sap.com

In this post I will be explaining how we can utilize starndard xml tags for formatting data in excel.The use of this approach would eliminate the need of external apis. With standard xml approach we could download data into excel, but we could not do any formatting in the excel file. But this approach also provides lots of flexibility in formatting the excel file. The first thing comes into mind when we hear the word formatting is changing font of the text. But this time we would try to do something beyond the traditional way of formatting e.g. merging of cells, adding drop downs, fixing width of a cell etc.

Note!

Below hierarchy contains only list of tags which we can use in XML document, but doesn’t mention about attributes available for each to use. For full reference on this please visit XML Spreadsheet Reference.

All of the formatting needed in the excel sheet are encapsulated in the <ss:Styles></ss:Styles> section or they can be specified individually for each cell within the tag <ss:Cell></ss:Cell>.

Example

XML CODE

<?xml version=”1.0″?>
<?mso-application progid=”Excel.Sheet”?>
<Workbook xmlns=”urn:schemas-microsoft-com:office:spreadsheet”
xmlns:o=”urn:schemas-microsoft-com:office:office”
xmlns:x=”urn:schemas-microsoft-com:office:excel”
xmlns:ss=”urn:schemas-microsoft-com:office:spreadsheet”
xmlns:html=”http://www.w3.org/TR/REC-html40″> <Styles>
<Style ss:ID=”Default” ss:Name=”Normal”>
   <Alignment ss:Vertical=”Bottom”/>
   <Borders/>
   <Font/>
   <Interior/>
   <NumberFormat/>
   <Protection/>
</Style>
<Style ss:ID=”s1″>
   <Alignment ss:Vertical=”Center”/>
   <Borders>
    <Border ss:Position=”Bottom” ss:LineStyle=”Continuous” ss:Weight=”1″/>
    <Border ss:Position=”Left” ss:LineStyle=”Continuous” ss:Weight=”1″/>
    <Border ss:Position=”Right” ss:LineStyle=”Continuous” ss:Weight=”1″/>
    <Border ss:Position=”Top” ss:LineStyle=”Continuous” ss:Weight=”1″/>
   </Borders>
</Style>
<Style ss:ID=”s2″>
   <Alignment ss:Vertical=”Center”/>
   <Borders>
    <Border ss:Position=”Bottom” ss:LineStyle=”Continuous” ss:Weight=”1″/>
    <Border ss:Position=”Left” ss:LineStyle=”Continuous” ss:Weight=”1″/>
    <Border ss:Position=”Right” ss:LineStyle=”Continuous” ss:Weight=”1″/>
    <Border ss:Position=”Top” ss:LineStyle=”Continuous” ss:Weight=”1″/>
   </Borders>
</Style>
<Style ss:ID=”s3″>
   <Borders>
    <Border ss:Position=”Bottom” ss:LineStyle=”Continuous” ss:Weight=”1″/>
    <Border ss:Position=”Left” ss:LineStyle=”Continuous” ss:Weight=”1″/>
    <Border ss:Position=”Right” ss:LineStyle=”Continuous” ss:Weight=”1″/>
    <Border ss:Position=”Top” ss:LineStyle=”Continuous” ss:Weight=”1″/>
   </Borders>
</Style>
<Style ss:ID=”s4″>
   <Borders>
    <Border ss:Position=”Bottom” ss:LineStyle=”Continuous” ss:Weight=”1″/>
    <Border ss:Position=”Left” ss:LineStyle=”Continuous” ss:Weight=”1″/>
    <Border ss:Position=”Right” ss:LineStyle=”Continuous” ss:Weight=”1″/>
    <Border ss:Position=”Top” ss:LineStyle=”Continuous” ss:Weight=”1″/>
   </Borders>
   <Font x:Family=”Swiss” ss:Bold=”1″/>
   <Interior ss:Color=”#CCFFFF” ss:Pattern=”Solid”/>
</Style>
<Style ss:ID=”s5″>
   <Alignment ss:Vertical=”Bottom” ss:WrapText=”1″/>
   <Borders>
    <Border ss:Position=”Bottom” ss:LineStyle=”Continuous” ss:Weight=”1″/>
    <Border ss:Position=”Left” ss:LineStyle=”Continuous” ss:Weight=”1″/>
    <Border ss:Position=”Right” ss:LineStyle=”Continuous” ss:Weight=”1″/>
    <Border ss:Position=”Top” ss:LineStyle=”Continuous” ss:Weight=”1″/>
   </Borders>
   <Font x:Family=”Swiss” ss:Bold=”1″/>
   <Interior ss:Color=”#CCFFFF” ss:Pattern=”Solid”/>
</Style>
<Style ss:ID=”s6″>
   <Alignment ss:Vertical=”Bottom” ss:WrapText=”1″/>
   <Borders>
    <Border ss:Position=”Bottom” ss:LineStyle=”Continuous” ss:Weight=”1″/>
    <Border ss:Position=”Left” ss:LineStyle=”Continuous” ss:Weight=”1″/>
    <Border ss:Position=”Right” ss:LineStyle=”Continuous” ss:Weight=”1″/>
    <Border ss:Position=”Top” ss:LineStyle=”Continuous” ss:Weight=”1″/>
   </Borders>
</Style>
<Style ss:ID=”s7″>
   <Alignment ss:Vertical=”Bottom” ss:WrapText=”1″/>
</Style>
<Style ss:ID=”s8″>
   <Alignment ss:Horizontal=”Center” ss:Vertical=”Bottom”/>
   <Borders>
    <Border ss:Position=”Bottom” ss:LineStyle=”Continuous” ss:Weight=”1″/>
    <Border ss:Position=”Left” ss:LineStyle=”Continuous” ss:Weight=”1″/>
    <Border ss:Position=”Right” ss:LineStyle=”Continuous” ss:Weight=”1″/>
    <Border ss:Position=”Top” ss:LineStyle=”Continuous” ss:Weight=”1″/>
   </Borders>
   <Font x:Family=”Swiss” ss:Size=”24″ ss:Bold=”1″/>
</Style>
</Styles>
<Worksheet ss:Name=”Sheet1″>
<Table ss:ExpandedColumnCount=”4″ ss:ExpandedRowCount=”9″ x:FullColumns=”1″
   x:FullRows=”1″>
   <Column ss:AutoFitWidth=”0″ ss:Width=”135″/>
   <Column ss:Index=”3″ ss:StyleID=”s7″ ss:AutoFitWidth=”0″ ss:Width=”66.75″/>
   <Row ss:Height=”30″>
    <Cell ss:MergeAcross=”3″ ss:StyleID=”s8″><Data ss:Type=”String”>Title</Data></Cell>
   </Row>
   <Row>
    <Cell ss:StyleID=”s4″><Data ss:Type=”String”>Column1</Data></Cell>
    <Cell ss:StyleID=”s4″><Data ss:Type=”String”>Column2</Data></Cell>
    <Cell ss:StyleID=”s5″><Data ss:Type=”String”>Column3</Data></Cell>
    <Cell ss:StyleID=”s4″><Data ss:Type=”String”>Column4</Data></Cell>
   </Row>
   <Row ss:Height=”76.5″>
    <Cell ss:MergeDown=”1″ ss:StyleID=”s1″><Data ss:Type=”String”>Row 3 & Row 4 Merged</Data></Cell>
    <Cell ss:StyleID=”s3″><Data ss:Type=”String”>Red</Data></Cell>
    <Cell ss:StyleID=”s6″><Data ss:Type=”String”>Wrap text when width of the text exceeds the specified width</Data></Cell>
    <Cell ss:StyleID=”s3″/>
   </Row>
   <Row>
    <Cell ss:Index=”2″ ss:StyleID=”s3″><Data ss:Type=”String”>Black</Data></Cell>
    <Cell ss:StyleID=”s6″/>
    <Cell ss:StyleID=”s3″/>
   </Row>
   <Row>
    <Cell ss:StyleID=”s3″><Data ss:Type=”String”>Row 4</Data></Cell>
    <Cell ss:StyleID=”s3″><Data ss:Type=”String”>Black</Data></Cell>
    <Cell ss:StyleID=”s6″/>
    <Cell ss:StyleID=”s3″/>
   </Row>
   <Row>
    <Cell ss:StyleID=”s3″><Data ss:Type=”String”>Row 5</Data></Cell>
    <Cell ss:StyleID=”s3″><Data ss:Type=”String”>Red</Data></Cell>
    <Cell ss:StyleID=”s6″/>
    <Cell ss:StyleID=”s3″/>
   </Row>
   <Row>
    <Cell ss:StyleID=”s3″><Data ss:Type=”String”>Row 6</Data></Cell>
    <Cell ss:StyleID=”s3″><Data ss:Type=”String”>Black</Data></Cell>
    <Cell ss:StyleID=”s6″/>
    <Cell ss:StyleID=”s3″/>
   </Row>
   <Row>
    <Cell ss:MergeDown=”1″ ss:StyleID=”s2″><Data ss:Type=”String”>Row 8 & 9 Merged</Data></Cell>
    <Cell ss:StyleID=”s3″><Data ss:Type=”String”>Green</Data></Cell>
    <Cell ss:StyleID=”s6″/>
    <Cell ss:StyleID=”s3″/>
   </Row>
   <Row>
    <Cell ss:Index=”2″ ss:StyleID=”s3″><Data ss:Type=”String”>Red</Data></Cell>
    <Cell ss:StyleID=”s6″/>
    <Cell ss:StyleID=”s3″/>
   </Row>
</Table> <DataValidation xmlns=”urn:schemas-microsoft-com:office:excel”>
   <Range>R2C2:R1000C2</Range>
   <Type>List</Type>
   <CellRangeList/>
   <Value>”Red, Black, Green”</Value>
</DataValidation>
</Worksheet></Workbook>

Save this code into an .xml file and open with excel to see whether your code is correct.Now all you have to do is to generate a xml string programmatically and download into .xml file.

Reference

http://msdn.microsoft.com/en-us/library/aa140062.aspx

Python 入门

条形码code128编码规则

条形码或称条码（barcode）是将宽度不等的多个黑条和空白，按照一定的编码规则排列，用以表达一组信息的图形标识符。常见的条形码是由反射率相差很大的黑条（简称条）和白条（简称空）排成的平行线图案。条形码可以标出物品的生产国、制造厂家、商品名称、生产日期、图书分类号、邮件起止地点、类别、日期等信息，因而在商品流通、图书管理、邮政管理、银行系统等许多领域都得到了广泛的应用。

条形码分类

条形码按类型可分为：线性条形码和矩阵（二维）条形码

code128编码

Code 128是ISO/IEC 15417:2007[1]定义的条形码规范。

Code 128条码可以对全部128个ASCII字符（包括数字、大小写字母、符号和控制符）进行编码。

code128码是广泛应用在企业内部管理、生产流程、物流控制系统方面的条码码制，由于其优良的特性在管理信息系统的设计中被广泛使用，CODE128码是应用最广泛的线性条形码制之一。

code128编码分类

code128 A字符集：包括大写字母、数字、常用标点符号和一些控制符。
code128B 字符集：包括大小写字母、数字、常用标点符号。
code128C 字符集：为纯数字序列。
code128Auto：是将上述三种字符集最佳优化组合。

code128编码构成

一个Code 128条形码由六部分组成。

空白区域
起始标记
数据区
校验符
终止符
空白区域

code128编码表

ID	Code128A	Code128B	Code128C	BandCode	图案
0	SP	SP	0	212222	11011001100
1	!	!	1	222122	11001101100
2	“	“	2	222221	11001100110
3	#	#	3	121223	10010011000
4	$	$	4	121322	10010001100
5	%	%	5	131222	10001001100
6	&	&	6	122213	10011001000
7	‘	‘	7	122312	10011000100
8	(	(	8	132212	10001100100
9	)	)	9	221213	11001001000
10	*	*	10	221312	11001000100
11	+	+	11	231212	11000100100
12	,	,	12	112232	10110011100
13	–	–	13	122132	10011011100
14	.	.	14	122231	10011001110
15	/	/	15	113222	10111001100
16	0	0	16	123122	10011101100
17	1	1	17	123221	10011100110
18	2	2	18	223211	11001110010
19	3	3	19	221132	11001011100
20	4	4	20	221231	11001001110
21	5	5	21	213212	11011100100
22	6	6	22	223112	11001110100
23	7	7	23	312131	11101101110
24	8	8	24	311222	11101001100
25	9	9	25	321122	11100101100
26	:	:	26	321221	11100100110
27	;	;	27	312212	11101100100
28	<	<	28	322112	11100110100
29	=	=	29	322211	11100110010
30	>	>	30	212123	11011011000
31	?	?	31	212321	11011000110
32	@	@	32	232121	11000110110
33	A	A	33	111323	10100011000
34	B	B	34	131123	10001011000
35	C	C	35	131321	10001000110
36	D	D	36	112313	10110001000
37	E	E	37	132113	10001101000
38	F	F	38	132311	10001100010
39	G	G	39	211313	11010001000
40	H	H	40	231113	11000101000
41	I	I	41	231311	11000100010
42	J	J	42	112133	10110111000
43	K	K	43	112331	10110001110
44	L	L	44	132131	10001101110
45	M	M	45	113123	10111011000
46	N	N	46	113321	10111000110
47	O	O	47	133121	10001110110
48	P	P	48	313121	11101110110
49	Q	Q	49	211331	11010001110
50	R	R	50	231131	11000101110
51	S	S	51	213113	11011101000
52	T	T	52	213311	11011100010
53	U	U	53	213131	11011101110
54	V	V	54	311123	11101011000
55	W	W	55	311321	11101000110
56	X	X	56	331121	11100010110
57	Y	Y	57	312113	11101101000
58	Z	Z	58	312311	11101100010
59	[	[	59	332111	11100011010
60	\	\	60	314111	11101111010
61	]	]	61	221411	11001000010
62	^	^	62	431111	11110001010
63	_	_	63	111224	10100110000
64	NUL	`	64	111422	10100001100
65	SOH	a	65	121124	10010110000
66	STX	b	66	121421	10010000110
67	ETX	c	67	141122	10000101100
68	EOT	d	68	141221	10000100110
69	ENQ	e	69	112214	10110010000
70	ACK	f	70	112412	10110000100
71	BEL	g	71	122114	10011010000
72	BS	h	72	122411	10011000010
73	HT	i	73	142112	10000110100
74	LF	j	74	142211	10000110010
75	VT	k	75	241211	11000010010
76	FF	I	76	221114	11001010000
77	CR	m	77	413111	11110111010
78	SO	n	78	241112	11000010100
79	SI	o	79	134111	10001111010
80	DLE	p	80	111242	10100111100
81	DC1	q	81	121142	10010111100
82	DC2	r	82	121241	10010011110
83	DC3	s	83	114212	10111100100
84	DC4	t	84	124112	10011110100
85	NAK	u	85	124211	10011110010
86	SYN	v	86	411212	11110100100
87	ETB	w	87	421112	11110010100
88	CAN	x	88	421211	11110010010
89	EM	y	89	212141	11011011110
90	SUB	z	90	214121	11011110110
91	ESC	{	91	412121	11110110110
92	FS	\|	92	111143	10101111000
93	GS	}	93	111341	10100011110
94	RS	~	94	131141	10001011110
95	US	DEL	95	114113	10111101000
96	FNC3	FNC3	96	114311	10111100010
97	FNC2	FNC2	97	411113	11110101000
98	SHIFT	SHIFT	98	411311	11110100010
99	CODEC	CODEC	99	113141	10111011110
100	CODEB	FNC4	CODEB	114131	10111101110
101	FNC4	CODEA	CODEA	311141	11101011110
102	FNC1	FNC1	FNC1	411131	11110101110
103	StartA	StartA	StartA	211412	11010000100
104	StartB	StartB	StartB	211214	11010010000
105	StartC	StartC	StartC	211232	11010011100
106	Stop	Stop	Stop	2331112	1100011101011

code128检验位计算

（开始位对应的ID值＋每位数据在整个数据中的位置×每位数据对应的ID值）% 103

Code128编码示例

以 PZ1704946715 为例，开始为code128A，开始位对应的ID为103，第1位数据P对应的ID为48，第2位数据Z对应的ID为58，依此类推，可以计算。

检验位 =(103 + 1*48 + 2*58 + 3*17 + 4*23 + 5*16 + 6*20 +7*25 + 8*20 + 9*22 + 10*23 + 11*17 + 12*21)%103 = 61

即检验位的ID为61。

对照编码表，PZ1704946715 编码表示为：开始位Start Code A（11010000100）＋数据位［P（11101110110）＋ Z（11101100010）＋1（10011100110）＋ 7（11101101110）＋ 0（10011101100）＋ 4（11001001110）＋ 9（11100101100）＋ 4（11001001110）＋ 6（11001110100）＋ 7（11101101110）＋ 1（10011100110）＋ 5（11011100100）］＋检验位61（11001000010）＋结束位Stop（1100011101011）

若要打印，只需将1用黑色线标出，0用白色线标出，就完成一个简单的条形码生成。

http://liuchang.men/2019/04/09/%E6%9D%A1%E5%BD%A2%E7%A0%81code128%E7%BC%96%E7%A0%81%E8%A7%84%E5%88%99/

How to receive Deadlock information automatically via email

By Geoff Albin, 2014/01/03 (first published: 2010/12/13)

Receive Deadlock detail via email.

I had spent many hours on Google trying to find the best way to send a notification on deadlock occurrences. While it was a fairly straight forward process on how we get notified a deadlock has occurred, I wanted a bit more. I not only wanted to be told when the Deadlock occurred, I wanted to also be emailed the actual Deadlock information.

Every time a deadlock occurs in SQL Server, the detailed info about things like the SPID, the statement that was running, who the victim was, and so on does get logged. But getting the generic alert that SQL Server can create simply will tell you “something” has occurred. It would be the dutiful DBA’s job to log into the server in question and dig into the Error Log to get the Deadlock details.

Since capturing Deadlock info is not turned on by default. We do have to do two things in order to make the scripts I have written work properly.

Requirement number one

The first requirement is to turn on the appropriate Trace Flags. We do that by running the following command.

DBCC TRACEON (3605,1204,1222,-1)

A brief overview to what those flags do is listed below.

3605 = write what we want to the error log.
1204 = Capture Deadlock Events.
1222 = Capture Deadlock Events with more info (SQL 2005 and higher)

It is important to note that setting trace flags this way will only work until the next time SQL Server is restarted. If you want to make sure your trace flags are always set this way, you can edit the startup options of SQL Server by using the -T switch or creating a SQL Agent job to run that code when the Agent starts.

Requirement number two

The second requirement is to ensure you have DB Mail setup and working. I will provide no detail on how to accomplish that. It will just be assumed that you have a working DB Mail profile.

Lets get started.

So now we have trace flags set and DB Mail is working we are ready to get into how we send the Deadlock information to an email address when a Deadlock occurs.

Since the structure of the error log changed in SQL 2005, we have two ways of doing this. Each method is basically the same, however, pay attention to where you deploy this script. I have included detailed comments so you can follow along.

For SQL Server 2000

--== This is for SQL 2000. ==--
--== We will create a temporary table to hold the error log detail. ==--
--== Before we create the temporary table, we make sure it does not already exist. ==--
 IF OBJECT_ID('tempdb.dbo.ErrorLog') IS Not Null
 BEGIN
 DROP TABLE tempdb.dbo.ErrorLog
 END
 --== We have checked for the existence of the temporary table and dropped it if it was there. ==--
 --== Now, we can create the table called tempdb.dbo.ErrorLog ==--
 CREATE TABLE tempdb.dbo.ErrorLog (Id int IDENTITY (1, 1) NOT NULL,
 ERRORLOG VARCHAR(4000), ContRow int)
--== We create a 3 column table to hold the contents of the SQL Server Error log. ==--
--== Then we insert the actual data from the Error log into our newly created table. ==--
 INSERT INTO tempdb.dbo.ErrorLog
 EXEC master.dbo.sp_readerrorlog
--== With our table created and populated, we can now use the info inside of it. ==--
 BEGIN
--== Set a variable to get our instance name. ==--
--== We do this so the email we receive makes more sense. ==--
 declare @servername nvarchar(150)
 set @servername = @@servername
--== We set another variable to create a subject line for the email. ==-- 
 declare @mysubject nvarchar(200)
 set @mysubject = 'Deadlock event notification on server '+@servername+'.'
 --== Now we will prepare and send the email. Change the email address to suite your environment. ==-- 
 exec master.dbo.xp_sendmail @recipients = 'DBA_Super_Hero@email.com',
 @subject = @mysubject,
 @message = 'Deadlock has occurred. View attachment to see the deadlock info',
 @query = 'select ERRORLOG from tempdb.dbo.ErrorLog where Id >= (select TOP 1 Id from tempdb.dbo.ErrorLog WHERE ERRORLOG Like ''%Deadlock encountered%'' order by Id DESC)',
 @width = 600,
 @attach_results = 'True',
 @no_header = 'True'
 END
 --== Clean up our process by dropping our temporary table. ==--
 DROP TABLE tempdb.dbo.ErrorLog

And for all other version, (2005, 2008, 2008 R2)

--== This is for SQL 2005 and higher. ==--
--== We will create a temporary table to hold the error log detail. ==--
--== Before we create the temporary table, we make sure it does not already exist. ==--
 IF OBJECT_ID('tempdb.dbo.ErrorLog') IS Not Null
 BEGIN
 DROP TABLE tempdb.dbo.ErrorLog
 END
 --== We have checked for the existence of the temporary table and dropped it if it was there. ==--
 --== Now, we can create the table called tempdb.dbo.ErrorLog ==--
CREATE TABLE tempdb.dbo.ErrorLog (Id int IDENTITY (1, 1) NOT NULL, 
logdate DATETIME, procInfo VARCHAR(10), ERRORLOG VARCHAR(MAX))
--== We create a 3 column table to hold the contents of the SQL Server Error log. ==--
--== Then we insert the actual data from the Error log into our newly created table. ==--
 INSERT INTO tempdb.dbo.ErrorLog
 EXEC master.dbo.sp_readerrorlog
--== With our table created and populated, we can now use the info inside of it. ==--
 BEGIN
--== Set a variable to get our instance name. ==--
--== We do this so the email we receive makes more sense. ==--
 declare @servername nvarchar(150)
 set @servername = @@servername
--== We set another variable to create a subject line for the email. ==-- 
 declare @mysubject nvarchar(200)
 set @mysubject = 'Deadlock event notification on server '+@servername+'.'
 --== Now we will prepare and send the email. Change the email address to suite your environment. ==-- 
 EXEC msdb.dbo.sp_send_dbmail @recipients='DBA_Super_Hero@email.com',
 @subject = @mysubject,
 @body = 'Deadlock has occurred. View attachment to see the deadlock info',
 @query = 'select logdate, procInfo, ERRORLOG from tempdb.dbo.ErrorLog where Id >= (select TOP 1 Id from tempdb.dbo.ErrorLog WHERE ERRORLOG Like ''%Deadlock encountered%'' order by Id DESC)',
 @query_result_width = 600,
 @attach_query_result_as_file = 1
 END
 --== Clean up our process by dropping our temporary table. ==--
 DROP TABLE tempdb.dbo.ErrorLog

Next Steps

In order to get those to work every time SQL Server encounters a Deadlock, we have to create a SQL Server Agent Job and a SQL Server Agent Alert. The basic approach is to create a Job that is called by the Alert service.

Let’s first create a SQL Agent Job. The job will have no schedule. Note that the script below needs to be edited. If you do not run your SQL Server in mixed mode, you will need to change @owner_login_name=N’sa’ to a user that can run the job. Also, note that the script contains an email address. You will have to enter a valid email address. This would be the email address of the person that will be troubleshooting the Deadlock occurrences. You will create this SQL Agent job on every instance you want to receive Deadlock info for.

SQL Server 2000 Job

USE [msdb]
GO
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END
DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'Deadlock Job', 
 @enabled=1, 
 @notify_level_eventlog=0, 
 @notify_level_email=0, 
 @notify_level_netsend=0, 
 @notify_level_page=0, 
 @delete_level=0, 
 @description=N'No description available.', 
 @category_name=N'[Uncategorized (Local)]', 
 @owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Deadlock has occurred.', 
 @step_id=1, 
 @cmdexec_success_code=0, 
 @on_success_action=1, 
 @on_success_step_id=0, 
 @on_fail_action=2, 
 @on_fail_step_id=0, 
 @retry_attempts=0, 
 @retry_interval=0, 
 @os_run_priority=0, @subsystem=N'TSQL', 
 @command=N'--== This is for SQL 2000. ==--
--== We will create a temporary table to hold the error log detail. ==--
--== Before we create the temporary table, we make sure it does not already exist. ==--
 IF OBJECT_ID(''tempdb.dbo.ErrorLog'') IS Not Null
 BEGIN
 DROP TABLE tempdb.dbo.ErrorLog
 END
 --== We have checked for the existence of the temporary table and dropped it if it was there. ==--
 --== Now, we can create the table called tempdb.dbo.ErrorLog ==--
 CREATE TABLE tempdb.dbo.ErrorLog (Id int IDENTITY (1, 1) NOT NULL,
 ERRORLOG VARCHAR(4000), ContRow int)
--== We create a 3 column table to hold the contents of the SQL Server Error log. ==--
--== Then we insert the actual data from the Error log into our newly created table. ==--
 INSERT INTO tempdb.dbo.ErrorLog
 EXEC master.dbo.sp_readerrorlog
--== With our table created and populated, we can now use the info inside of it. ==--
 BEGIN
--== Set a variable to get our instance name. ==--
--== We do this so the email we receive makes more sense. ==--
 declare @servername nvarchar(150)
 set @servername = @@servername
--== We set another variable to create a subject line for the email. ==-- 
 declare @mysubject nvarchar(200)
 set @mysubject = ''Deadlock event notification on server ''+@servername+''.''
 --== Now we will prepare and send the email. Change the email address to suite your environment. ==-- 
 exec master.dbo.xp_sendmail @recipients = ''DBA_Super_Hero@email.com'',
 @subject = @mysubject,
 @message = ''Deadlock has occurred. View attachment to see the deadlock info'',
 @query = ''select ERRORLOG from tempdb.dbo.ErrorLog where Id >= (select TOP 1 Id from tempdb.dbo.ErrorLog WHERE ERRORLOG Like ''''%Deadlock encountered%'''' order by Id DESC)'',
 @width = 600,
 @attach_results = ''True'',
 @no_header = ''True''
 END
 --== Clean up our process by dropping our temporary table. ==--
 DROP TABLE tempdb.dbo.ErrorLog
', 
 @database_name=N'master', 
 @flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
 IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO

SQL Server 2005 and higher Job

Note that the script below needs to be edited. If you do not run your SQL Server in mixed mode, you will need to change @owner_login_name=N’sa’ to a user that can run the job. Also, note that the script contains an email address. You will have to enter a valid email address. This would be the email address of the person that will be troubleshooting the Deadlock occurrences. You will create this SQL Agent job on every instance you want to receive Deadlock info for.

USE [msdb]
GO
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END
DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'Deadlock Job', 
 @enabled=1, 
 @notify_level_eventlog=0, 
 @notify_level_email=0, 
 @notify_level_netsend=0, 
 @notify_level_page=0, 
 @delete_level=0, 
 @description=N'No description available.', 
 @category_name=N'[Uncategorized (Local)]', 
@owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Deadlock has occurred.', 
 @step_id=1, 
 @cmdexec_success_code=0, 
 @on_success_action=1, 
 @on_success_step_id=0, 
 @on_fail_action=2, 
 @on_fail_step_id=0, 
 @retry_attempts=0, 
 @retry_interval=0, 
 @os_run_priority=0, @subsystem=N'TSQL', 
 @command=N'--== This is for SQL 2005 and higher. ==--
--== We will create a temporary table to hold the error log detail. ==--
--== Before we create the temporary table, we make sure it does not already exist. ==--
 IF OBJECT_ID(''tempdb.dbo.ErrorLog'') IS Not Null
 BEGIN
 DROP TABLE tempdb.dbo.ErrorLog
 END
 --== We have checked for the existence of the temporary table and dropped it if it was there. ==--
 --== Now, we can create the table called tempdb.dbo.ErrorLog ==--
CREATE TABLE tempdb.dbo.ErrorLog (Id int IDENTITY (1, 1) NOT NULL, 
logdate DATETIME, procInfo VARCHAR(10), ERRORLOG VARCHAR(MAX))
--== We create a 3 column table to hold the contents of the SQL Server Error log. ==--
--== Then we insert the actual data from the Error log into our newly created table. ==--
 INSERT INTO tempdb.dbo.ErrorLog
 EXEC master.dbo.sp_readerrorlog
--== With our table created and populated, we can now use the info inside of it. ==--
 BEGIN
--== Set a variable to get our instance name. ==--
--== We do this so the email we receive makes more sense. ==--
 declare @servername nvarchar(150)
 set @servername = @@servername
--== We set another variable to create a subject line for the email. ==-- 
 declare @mysubject nvarchar(200)
 set @mysubject = ''Deadlock event notification on server ''+@servername+''.''
 --== Now we will prepare and send the email. Change the email address to suite your environment. ==-- 
 EXEC msdb.dbo.sp_send_dbmail @recipients=''DBA_Super_Hero@email.com'',
 @subject = @mysubject,
 @body = ''Deadlock has occurred. View attachment to see the deadlock info'',
 @query = ''select logdate, procInfo, ERRORLOG from tempdb.dbo.ErrorLog where Id >= (select TOP 1 Id from tempdb.dbo.ErrorLog WHERE ERRORLOG Like ''''%Deadlock encountered%'''' order by Id DESC)'',
 @query_result_width = 600,
 @attach_query_result_as_file = 1
 END
 --== Clean up our process by dropping our temporary table. ==--
 DROP TABLE tempdb.dbo.ErrorLog
', 
 @database_name=N'master', 
 @flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
 IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO

Final step

Now we have to create a SQL Agent alert to call the job we created. Open SSMS and log into the instance we are capturing Deadlock information for and expand the SQL Server Agent. Right click on the word Alerts and choose “New Alert…”

On the General page it should look like this;

On the Response page it should look like this:

On the Options page it should look like this:

That’s all there is to it. The next time a Deadlock occurs on the instance you are monitoring you will you receive an email. The email you receive will have an attachment that will actually tell you what the deadlock was.

File management from inside SQL code

Writing a log, dumping data, looking for file to be used in Bulk insert…

There are many situations when you would want to have access to files from inside your SQL code on Microsoft SQL Server.

Did you know that you actually can do this? No? Check below for code snippets to perform various operations on files. It is presented in form of the functions but you are actually not limited to that

Prerequisites and assumptions

Your script should have sufficient rights to perform required access to files (not necessarily local).
Scripting.FileSystemObject should be present at your SQL Server location and accessible.

Check if file exists

CREATE FUNCTION [dbo].[ufn_IsFileExists] (
@FilePath VARCHAR(255)
)
RETURNS INT
AS
BEGIN
DECLARE
  @objFileSystem int,
  @hr int,
  @i int
EXEC @hr = sp_OACreate 'Scripting.FileSystemObject', @objFileSystem out
IF @HR = 0
BEGIN
  EXEC sp_OAMethod @objFileSystem, 'FileExists', @i out, @FilePath
  EXEC sp_OADestroy @objFileSystem
END
ELSE
BEGIN
  SET @i = -1
END
RETURN @i
END

Write string into file

CREATE FUNCTION [dbo].[ufn_WriteStringToFile] (
  @CreateFile int,
  @FilePath varchar(500),
  @String varchar(4000) )
RETURNS varchar(200)
AS
BEGIN
DECLARE
  @objFileSystem int,
  @objTextStream int,
  @objErrorObject int,
  @strErrorMsg varchar(1000),
  @Command varchar(1000),
  @HR int,
  @fileAndPath varchar(80)
SET @strErrorMsg = 'opening the File System Object'
EXEC @hr = sp_OACreate 'Scripting.FileSystemObject' , @objFileSystem OUT
IF @HR = 0
BEGIN
  SET @objErrorObject = @objFileSystem
  IF (@CreateFile = 1)
  BEGIN
SET @strErrorMsg= 'Creating file "' + @FilePath + '"'
EXEC @hr = sp_OAMethod @objFileSystem, 'OpenTextFile', @objTextStream OUT, @FilePath, 2, True
  END
  ELSE
  BEGIN
  SET @strErrorMsg = 'Opening file "' + @FilePath + '"'
  EXEC @hr = sp_OAMethod @objFileSystem, 'OpenTextFile', @objTextStream OUT, @FilePath, 8, True
  END
END
IF @HR = 0
BEGIN
  SET @objErrorObject = @objTextStream
  SET @strErrorMsg = 'Writing to the file "' + @FilePath + '"'
  EXEC @hr = sp_OAMethod @objTextStream, 'WriteLine', Null, @String
END
IF @HR=0
BEGIN
  SET @objErrorObject = @objTextStream
  SET @strErrorMsg = 'Closing the file "' + @FileAndPath + '"'
  EXEC @hr = sp_OAMethod @objTextStream, 'Close'
END
IF @HR <> 0
BEGIN
  DECLARE
  @Source varchar(255),
  @Description varchar(255),
  @Helpfile varchar(255),
  @HelpID int
EXEC sp_OAGetErrorInfo @objErrorObject, @Source OUTPUT, @Description OUTPUT, @Helpfile OUTPUT, @HelpID OUTPUT
  SET @strErrorMsg = 'Error: ' + coalesce(@strErrorMsg, 'Unknown') + ', ' + coalesce(@Description, '')
END
EXEC sp_OADestroy @objTextStream
RETURN @strErrorMsg
END

Read File As Table

CREATE FUNCTION [dbo].[ufn_ReadFileAsTable] (
@FilePath VARCHAR(255)
)
RETURNS @File TABLE ([LineNo] int identity(1,1), [Line] varchar(8000))
AS
BEGIN
DECLARE
@objFileSystem int,
@objTextStream int,
@objErrorObject int,
@strErrorMsg varchar(1000),
  @hr int,
  @String VARCHAR(8000),
@YesOrNo INT
SET @strErrorMsg = 'opening the File System Object'
EXEC @hr = sp_OACreate 'Scripting.FileSystemObject', @objFileSystem OUT
IF @HR=0
BEGIN
SET @objErrorObject = @objFileSystem
SET @strErrorMsg = 'Opening file "' + @FilePath + '"'
--Open for reading, FormatASCII
EXEC @HR = sp_OAMethod @objFileSystem, 'OpenTextFile', @objTextStream OUT, @FilePath, 1, False, 0
END
WHILE @HR = 0
BEGIN
  IF @HR=0
  BEGIN
SET @objErrorObject = @objTextStream
SET @strErrorMsg = 'Check if there is more to read in "' + @FilePath + '"'
EXEC @HR = sp_OAGetProperty @objTextStream, 'AtEndOfStream', @YesOrNo OUTPUT
IF @YesOrNo <> 0 BREAK
  END
  IF @HR=0
  BEGIN
SET @objErrorObject = @objTextStream
SET @strErrorMsg = 'Reading from the output file "' + @FilePath + '"'
EXEC @HR = sp_OAMethod @objTextStream, 'Readline', @String OUTPUT
    INSERT INTO @file(line) SELECT @String
  END
END
IF @HR=0
BEGIN
SET @objErrorObject = @objTextStream
SET @strErrorMsg = 'Closing the output file "' + @FilePath + '"'
EXEC @HR = sp_OAMethod @objTextStream, 'Close'
END
IF @hr <> 0
BEGIN
  DECLARE
@Source varchar(255),
@Description varchar(255),
@Helpfile varchar(255),
@HelpID int
EXEC sp_OAGetErrorInfo @objErrorObject, @Source OUTPUT, @Description OUTPUT, @Helpfile OUTPUT, @HelpID OUTPUT
SET @strErrorMsg = 'Error: ' + coalesce(@strErrorMsg, 'Unknown') + ', ' + coalesce(@Description, '')
INSERT INTO @File(line) select @strErrorMsg
END
EXEC sp_OADestroy @objTextStream
-- Fill the table variable with the rows for your result set
RETURN
END

Enjoy.

https://blog.dragonsoft.us/2008/02/15/sql-manage-files-from-inside-sql-code/

Script to Monitor SQL Server Memory Usage

udayarumilli.com/script-to-monitor-sql-server-memory-usage/

Introduction:

This post will takes you through the T-SQL Script to monitor SQL Server Memory Usage. In previous blog post we have explained the parameters involved in understanding sql server memory usage. There are total 7 scripts to monitor SQL Server Memory Usage.

Buffer Pool Usage
System Memory Information
SQL Server Process Memory Usage Information
Buffer Usage by Database
Object Wise Buffer Usage
Top 25 Costliest Stored Procedures – Logical Reads
Top Performance Counters

Script to Monitor SQL Server Memory Usage: Buffer Pool Usage

Results:

BPool_Committed_MB: Actual memory committed/used by the process (SQL Server).

BPool_Commit_Tgt_MB: Actual memory SQL Server tried to consume.

BPool_Visible_MB: Number of 8-KB buffers in the buffer pool that are directly accessible in the process virtual address space (SQL Server VAS).

Analysis:

BPool_Commit_Tgt_MB > BPool_Committed_MB: SQL Server Memory Manager tries to obtain additional memory

BPool_Commit_Tgt_MB < BPool_Committed_MB: SQL Server Memory Manager tries to shrink the amount of memory committed

If the value of BPool_Visible_MB is too low: We might receive out of memory errors or memory dump will be created.

/*********************************************/

–Script: Captures Buffer Pool Usage

–Works On: 2008, 2008 R2, 2012, 2014, 2016

/*********************************************/

— SQL server 2008 / 2008 R2

SELECT

(bpool_committed*8)/1024.0 as BPool_Committed_MB,

(bpool_commit_target*8)/1024.0 as BPool_Commit_Tgt_MB,

(bpool_visible*8)/1024.0 as BPool_Visible_MB

FROM sys.dm_os_sys_info;

— SQL server 2012 / 2014 / 2016

SELECT

(committed_kb)/1024.0 as BPool_Committed_MB,

(committed_target_kb)/1024.0 as BPool_Commit_Tgt_MB,

(visible_target_kb)/1024.0 as BPool_Visible_MB

FROM sys.dm_os_sys_info;

Script to Monitor SQL Server Memory Usage: System Memory Information

Results:

total_physical_memory_mb: Actual Physical Memory installed in OS

available_physical_memory_mb: Available Physical Memory

total_page_file_mb: Pagefile size on OS

available_page_file_mb: Available page file size

Percentage_Used: Physical Memory Percentage used

system_memory_state_desc: Memory current Health status

Analysis:

available_physical_memory_mb: Should be some positive sign based on total physical memory

available_page_file_mb: Should be some positive sign based on your total page file

Percentage_Used: 100% for a long time indicates a memory pressure

system_memory_state_desc: should be Available physical memory is high / steady

/*********************************************************************/

–Script: Captures System Memory Usage

–Works On: 2008, 2008 R2, 2012, 2014, 2016

/*********************************************************************/

select

total_physical_memory_kb/1024 AS total_physical_memory_mb,

available_physical_memory_kb/1024 AS available_physical_memory_mb,

total_page_file_kb/1024 AS total_page_file_mb,

available_page_file_kb/1024 AS available_page_file_mb,

100 – (100 * CAST(available_physical_memory_kb AS DECIMAL(18,3))/CAST(total_physical_memory_kb AS DECIMAL(18,3)))

AS ‘Percentage_Used’,

system_memory_state_desc

from sys.dm_os_sys_memory;

Script to Monitor SQL Server Memory Usage: SQL Server Process Memory Usage

Results:

physical_memory_in_use: Indicates the process working set in KB, as reported by operating system, as well as tracked allocations by using large page APIs

locked_page_allocations: Specifies memory pages locked in memory

virtual_address_space_committed: Indicates the amount of reserved virtual address space that has been committed or mapped to physical pages.

available_commit_limit: Indicates the amount of memory that is available to be committed by the process (SQL server)

page_fault_count: Indicates the number of page faults that are incurred by the SQL Server process

Analysis:

physical_memory_in_use: We can’t figure out the exact amount of physical memory using by sqlservr.exe using task manager but this column showcase the actual amount of physical memory using by SQL Server.

locked_page_allocations: If this is > 0 means Locked Pages is enabled for SQL Server which is one of the best practice

available_commit_limit: This indciates the available amount of memory that can be committed by the process sqlservr.exe

page_fault_count: Pages fetching from the page file on the hard disk instead of from physical memory. Consistently high number of hard faults per second represents Memory pressure.

/**************************************************************/

— Script: SQL Server Process Memory Usage

— Works On: 2008, 2008 R2, 2012, 2014, 2016

/**************************************************************/

select

physical_memory_in_use_kb/1048576.0 AS ‘physical_memory_in_use (GB)’,

locked_page_allocations_kb/1048576.0 AS ‘locked_page_allocations (GB)’,

virtual_address_space_committed_kb/1048576.0 AS ‘virtual_address_space_committed (GB)’,

available_commit_limit_kb/1048576.0 AS ‘available_commit_limit (GB)’,

page_fault_count as ‘page_fault_count’

from sys.dm_os_process_memory;

Script to Monitor SQL Server Memory Usage: Database Wise Buffer Usage

Results:

db_name: Name of the database in the given SQL server Instance

db_buffer_pages: Total number of corresponding database pages that are in buffer pool

db_buffer_Used_MB: Database wise Buffer size used in MB

db_buffer_Free_MB: Database wise Buffer Size Free (sum of free space on all pages) in MB.

db_buffer_percent: Database wise percentage of Buffer Pool usage

Analysis:

We can quickly find out the top databases which are consuming more Memory / Buffer Pool from the given SQL server Instance

/**************************************************************/

–Script: Database Wise Buffer Usage

–Works On: 2008, 2008 R2, 2012, 2014, 2016

/**************************************************************/

DECLARE @total_buffer INT;

SELECT @total_buffer = cntr_value

FROM sys.dm_os_performance_counters

WHERE RTRIM([object_name]) LIKE ‘%Buffer Manager’

AND counter_name = ‘Database Pages’;

;WITH DBBuffer AS

(

SELECT database_id,

COUNT_BIG(*) AS db_buffer_pages,

SUM (CAST ([free_space_in_bytes] AS BIGINT)) / (1024 * 1024) AS [MBEmpty]

FROM sys.dm_os_buffer_descriptors

GROUP BY database_id

)

SELECT

CASE [database_id] WHEN 32767 THEN ‘Resource DB’ ELSE DB_NAME([database_id]) END AS ‘db_name’,

db_buffer_pages AS ‘db_buffer_pages’,

db_buffer_pages / 128 AS ‘db_buffer_Used_MB’,

[mbempty] AS ‘db_buffer_Free_MB’,

CONVERT(DECIMAL(6,3), db_buffer_pages * 100.0 / @total_buffer) AS ‘db_buffer_percent’

FROM DBBuffer

ORDER BY db_buffer_Used_MB DESC;

Script to Monitor SQL Server Memory Usage: Object Wise Buffer Usage

Results:

Object: Name of the Object

Type: Type of the object Ex: USER_TABLE

Index: Name of the Index

Index_Type: Type of the Index “Clustered / Non Clustered / HEAP” etc

buffer_pages: Object wise number of pages is in buffer pool

buffer_mb: Object wise buffer usage in MB

Analysis:

From the previous script we can get the top databases using memory. This script helps you out in finding the top objects that are using the buffer pool. Top objects will tell you the objects which are using the major portion of the buffer pool.If you find anything suspicious then you can dig into it.

/**************************************************************/

–Script: Object Wise Buffer Usage

–Works On: 2008, 2008 R2, 2012, 2014, 2016

/**************************************************************/

;WITH obj_buffer AS

(

SELECT

[Object] = o.name,

[Type] = o.type_desc,

[Index] = COALESCE(i.name, ”),

[Index_Type] = i.type_desc,

p.[object_id],

p.index_id,

au.allocation_unit_id

FROM

sys.partitions AS p

INNER JOIN sys.allocation_units AS au ON p.hobt_id = au.container_id

INNER JOIN sys.objects AS o ON p.[object_id] = o.[object_id]

INNER JOIN sys.indexes AS i ON o.[object_id] = i.[object_id] AND p.index_id = i.index_id

WHERE

au.[type] IN (1,2,3) AND o.is_ms_shipped = 0

)

SELECT

obj.[Object],

obj.[Type],

obj.[Index],

obj.Index_Type,

COUNT_BIG(b.page_id) AS ‘buffer_pages’,

COUNT_BIG(b.page_id) / 128 AS ‘buffer_mb’

FROM

obj_buffer obj

INNER JOIN sys.dm_os_buffer_descriptors AS b ON obj.allocation_unit_id = b.allocation_unit_id

WHERE

b.database_id = DB_ID()

GROUP BY

obj.[Object],

obj.[Type],

obj.[Index],

obj.Index_Type

ORDER BY

buffer_pages DESC;

Script to Monitor SQL Server Memory Usage: Top 25 Costliest Stored Procedures by Logical Reads

Results:

SP Name: Stored Procedure Name

TotalLogicalReads: Total Number of Logical Reads since this stored procedure was last compiled

AvgLogicalReads: Average Number of Logical Reads since this stored procedure was last compiled

execution_count: Number of Times SP got executed since it was compiled

total_elapsed_time: Total elapsed time for this proc since last time compiled

avg_elapsed_time: Average elapsed time

cached_time: Time at which the stored procedure was added to the cache.

Analysis:

This helps you find the most expensive cached stored procedures from a memory perspective
You should look at this if you see signs of memory pressure
More number of logical reads means you need to check execution plan to find the bottleneck

/**************************************************************/

–Script: Top 25 Costliest Stored Procedures by Logical Reads

–Works On: 2008, 2008 R2, 2012, 2014, 2016

/**************************************************************/

SELECT TOP(25)

p.name AS [SP Name],

qs.total_logical_reads AS [TotalLogicalReads],

qs.total_logical_reads/qs.execution_count AS [AvgLogicalReads],

qs.execution_count AS ‘execution_count’,

qs.total_elapsed_time AS ‘total_elapsed_time’,

qs.total_elapsed_time/qs.execution_count AS ‘avg_elapsed_time’,

qs.cached_time AS ‘cached_time’

FROM sys.procedures AS p

INNER JOIN sys.dm_exec_procedure_stats AS qs

ON p.[object_id] = qs.[object_id]

WHERE

qs.database_id = DB_ID()

ORDER BY

qs.total_logical_reads DESC;

Script to Monitor SQL Server Memory Usage: Top Performance Counters – Memory

Results:

Total Server Memory: Shows how much memory SQL Server is using. The primary use of SQL Server’s memory is for the buffer pool, but some memory is also used for storing query plans and keeping track of user process information.

Target Server Memory: This value shows how much memory SQL Server attempts to acquire. If you haven’t configured a max server memory value for SQL Server, the target amount of memory will be about 5MB less than the total available system memory.

Connection Memory (GB): The Connection Memory specifies the total amount of dynamic memory the server is using for maintaining connections

Lock Memory (GB): Shows the total amount of memory the server is using for locks

SQL Cache Memory: Total memory reserved for dynamic SQL statements.

Optimizer Memory: Memory reserved for query optimization.

Granted Workspace Memory: Total amount of memory currently granted to executing processes such as hash, sort, bulk copy, and index creation operations.

Cursor memory usage: Memory using for cursors

Free pages: Amount of free space in pages which are commited but not currently using by SQL Server

Reserved Pages: Shows the number of buffer pool reserved pages.

Stolen pages (MB): Memory used by SQL Server but not for Database pages.It is used for sorting or hashing operations, or “as a generic memory store for allocations to store internal data structures such as locks, transaction context, and connection information.

Cache Pages: Number of 8KB pages in cache.

Page life expectancy: Average how long each data page is staying in buffer cache before being flushed out to make room for other pages

Free list stalls / sec: Number of times a request for a “free” page had to wait for one to become available.

Checkpoint Pages/sec: Checkpoint Pages/sec shows the number of pages that are moved from buffer to disk per second during a checkpoint process

Lazy writes / sec: How many times per second lazy writer has to flush dirty pages out of the buffer cache instead of waiting on a checkpoint.

Memory Grants Outstanding: Number of processes that have successfully acquired workspace memory grant.

Memory Grants Pending: Number of processes waiting on a workspace memory grant.

process_physical_memory_low: Process is responding to low physical memory notification

process_virtual_memory_low: Indicates that low virtual memory condition has been detected

Min Server Memory: Minimum amount of memory SQL Server should acquire

Max Server Memory: Maximum memory that SQL Server can acquire from OS

Buffer cache hit ratio: Percentage of pages that were found in the buffer pool without having to incur a read from disk.

Analysis:

Total Server Memory is almost same as Target Server Memory: Good Health

Total Server Memory is much smaller than Target Server Memory: There is a Memory Pressure or Max Server Memory is set to too low.

Connection Memory: When high, check the number of user connections and make sure it’s under expected value as per your business

Optimizer Memory: Ideally, the value should remain relatively static. If this isn’t the case you might be using dynamic SQL execution excessively.

Higher the value for Stolen Pages: Find the costly queries / procs and tune them

Higher the value for Checkpoint Pages/sec: Problem with I/O, Do not depend on Automatic Checkpoints and use In-direct checkpoints.

Page life expectancy: Usually 300 to 400 sec for each 4 GB of memory. Lesser the value means memory pressure

Free list stalls / sec: High value indicates that the server could use additional memory.

Memory Grants Outstanding: Higher value indicates peak user activity

Memory Grants Pending: Higher value indicates SQL Server need more memory

process_physical_memory_low & process_virtual_memory_low: Both are equals to 0 means no internal memory pressure

Min Server Memory: If it is 0 means default value didnt get changed, it’ll always be better to have a minimum amount of memory allocated to SQL Server

Max Server Memory: If it is default to 2147483647, change the value with the correct amount of memory that you can allow SQL Server to utilize.

Buffer cache hit ratio: This ratio should be in between 95 and 100. Lesser value indicates memory pressure

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

/**************************************************************/

–Script: Top Performance Counters – Memory

–Works On: 2008, 2008 R2, 2012, 2014, 2016

/**************************************************************/

— Get size of SQL Server Page in bytes

DECLARE @pg_size INT, @Instancename varchar(50)

SELECT @pg_size = low from master..spt_values where number = 1 and type = ‘E’

— Extract perfmon counters to a temporary table

IF OBJECT_ID(‘tempdb..#perfmon_counters’) is not null DROP TABLE #perfmon_counters

SELECT * INTO #perfmon_counters FROM sys.dm_os_performance_counters;

— Get SQL Server instance name as it require for capturing Buffer Cache hit Ratio

SELECT @Instancename = LEFT([object_name], (CHARINDEX(‘:’,[object_name])))

FROM #perfmon_counters

WHERE counter_name = ‘Buffer cache hit ratio’;

SELECT * FROM (

SELECT ‘Total Server Memory (GB)’ as Cntr,

(cntr_value/1048576.0) AS Value

FROM #perfmon_counters

WHERE counter_name = ‘Total Server Memory (KB)’

UNION ALL

SELECT ‘Target Server Memory (GB)’,

(cntr_value/1048576.0)

FROM #perfmon_counters

WHERE counter_name = ‘Target Server Memory (KB)’

UNION ALL

SELECT ‘Connection Memory (MB)’,

(cntr_value/1024.0)

FROM #perfmon_counters

WHERE counter_name = ‘Connection Memory (KB)’

UNION ALL

SELECT ‘Lock Memory (MB)’,

(cntr_value/1024.0)

FROM #perfmon_counters

WHERE counter_name = ‘Lock Memory (KB)’

UNION ALL

SELECT ‘SQL Cache Memory (MB)’,

(cntr_value/1024.0)

FROM #perfmon_counters

WHERE counter_name = ‘SQL Cache Memory (KB)’

UNION ALL

SELECT ‘Optimizer Memory (MB)’,

(cntr_value/1024.0)

FROM #perfmon_counters

WHERE counter_name = ‘Optimizer Memory (KB) ‘

UNION ALL

SELECT ‘Granted Workspace Memory (MB)’,

(cntr_value/1024.0)

FROM #perfmon_counters

WHERE counter_name = ‘Granted Workspace Memory (KB) ‘

UNION ALL

SELECT ‘Cursor memory usage (MB)’,

(cntr_value/1024.0)

FROM #perfmon_counters

WHERE counter_name = ‘Cursor memory usage’ and instance_name = ‘_Total’

UNION ALL

SELECT ‘Total pages Size (MB)’,

(cntr_value*@pg_size)/1048576.0

FROM #perfmon_counters

WHERE object_name= @Instancename+‘Buffer Manager’

and counter_name = ‘Total pages’

UNION ALL

SELECT ‘Database pages (MB)’,

(cntr_value*@pg_size)/1048576.0

FROM #perfmon_counters

WHERE object_name = @Instancename+‘Buffer Manager’ and counter_name = ‘Database pages’

UNION ALL

SELECT ‘Free pages (MB)’,

(cntr_value*@pg_size)/1048576.0

FROM #perfmon_counters

WHERE object_name = @Instancename+‘Buffer Manager’

and counter_name = ‘Free pages’

UNION ALL

SELECT ‘Reserved pages (MB)’,

(cntr_value*@pg_size)/1048576.0

FROM #perfmon_counters

WHERE object_name=@Instancename+‘Buffer Manager’

and counter_name = ‘Reserved pages’

UNION ALL

SELECT ‘Stolen pages (MB)’,

(cntr_value*@pg_size)/1048576.0

FROM #perfmon_counters

WHERE object_name=@Instancename+‘Buffer Manager’

and counter_name = ‘Stolen pages’

UNION ALL

SELECT ‘Cache Pages (MB)’,

(cntr_value*@pg_size)/1048576.0

FROM #perfmon_counters

WHERE object_name=@Instancename+‘Plan Cache’

and counter_name = ‘Cache Pages’ and instance_name = ‘_Total’

UNION ALL

SELECT ‘Page Life Expectency in seconds’,

cntr_value

FROM #perfmon_counters

WHERE object_name=@Instancename+‘Buffer Manager’

and counter_name = ‘Page life expectancy’

UNION ALL

SELECT ‘Free list stalls/sec’,

cntr_value

FROM #perfmon_counters

WHERE object_name=@Instancename+‘Buffer Manager’

and counter_name = ‘Free list stalls/sec’

UNION ALL

SELECT ‘Checkpoint pages/sec’,

cntr_value

FROM #perfmon_counters

WHERE object_name=@Instancename+‘Buffer Manager’

and counter_name = ‘Checkpoint pages/sec’

UNION ALL

SELECT ‘Lazy writes/sec’,

cntr_value

FROM #perfmon_counters

WHERE object_name=@Instancename+‘Buffer Manager’

and counter_name = ‘Lazy writes/sec’

UNION ALL

SELECT ‘Memory Grants Pending’,

cntr_value

FROM #perfmon_counters

WHERE object_name=@Instancename+‘Memory Manager’

and counter_name = ‘Memory Grants Pending’

UNION ALL

SELECT ‘Memory Grants Outstanding’,

cntr_value

FROM #perfmon_counters

WHERE object_name=@Instancename+‘Memory Manager’

and counter_name = ‘Memory Grants Outstanding’

UNION ALL

SELECT ‘process_physical_memory_low’,

process_physical_memory_low

FROM sys.dm_os_process_memory WITH (NOLOCK)

UNION ALL

SELECT ‘process_virtual_memory_low’,

process_virtual_memory_low

FROM sys.dm_os_process_memory WITH (NOLOCK)

UNION ALL

SELECT ‘Max_Server_Memory (MB)’ ,

[value_in_use]

FROM sys.configurations

WHERE [name] = ‘max server memory (MB)’

UNION ALL

SELECT ‘Min_Server_Memory (MB)’ ,

[value_in_use]

FROM sys.configurations

WHERE [name] = ‘min server memory (MB)’

UNION ALL

SELECT ‘BufferCacheHitRatio’,

(a.cntr_value * 1.0 / b.cntr_value) * 100.0

FROM sys.dm_os_performance_counters a

JOIN (SELECT cntr_value,OBJECT_NAME FROM sys.dm_os_performance_counters

WHERE counter_name = ‘Buffer cache hit ratio base’ AND

OBJECT_NAME = @Instancename+‘Buffer Manager’) b ON

a.OBJECT_NAME = b.OBJECT_NAME WHERE a.counter_name = ‘Buffer cache hit ratio’

AND a.OBJECT_NAME = @Instancename+‘Buffer Manager’

) AS D;

Script to Monitor SQL Server Memory Usage: DBCC MEMORYSTATUS

Finally DBCC MemoryStatus:

It gives as much as memory usage information based on object wise / component wise.
First table gives us the complete details of server and process memory usage details and memory alert indicators.
We can also get memory usage by buffer cache, Service Broker, Temp tables, Procedure Cache, Full Text, XML, Memory Pool Manager, Audit Buffer, SQLCLR, Optimizer, SQLUtilities, Connection Pool etc.

Summary:

These Scripts will help you in understanding the current memory usage by SQL Server. To maintain a healthy database management system:

Monitor the system for few business days in peak hours and fix the baselines
Identify the correct required configurations for your database server and make the required changes
Identify top 10 queries / procedures based on Memory and CPU usage
Fine tune these top 10 queries / procedures

Note:

These scripts are tested on SQL Server 2008, 2008 R2, 2012 and 2014. As we always suggests please test these scripts on Dev/Test environment before using them on production systems.

References:

Would like to thank famous MVPs / MCM / bloggers (Glenn Berry, Brent Ozar, Jonathan Kehayias, John Sansom) for the tremendous explanation on sql server internals. Their articles are very informative and helpful in understanding SQL Server internals.

Also Check:

SQL Server Memory Usage

SQL Server CPU Utilization

Content

Introduction

Links

Declarations by API function

GdipCreateBitmapFromFile

GdipCreateHBITMAPFromBitmap

GdipDisposeImage

GdiplusShutdown

GdiplusStartup

GetExitCodeProcess

OleCreatePictureIndirect

OpenProcess

Which Longs should become LongPtr?

Conditional compiling

Get-EXONMailbox references

Get-EXOCasMailbox

Get-EXOMailboxStatistics

Get-EXOMailboxFolderPermission

Get-EXOMobileDeviceStatistics

XML Tags Hierarchy

Example

XML CODE

Reference

python入门

实验楼简单实验

python(进阶)

实验楼有难度的实验+python爬虫

条形码分类

code128编码

code128编码分类

code128编码构成

code128编码表

code128检验位计算

Code128编码示例

Receive Deadlock detail via email.

Requirement number one

Requirement number two

Lets get started.

For SQL Server 2000

And for all other version, (2005, 2008, 2008 R2)

Next Steps

SQL Server 2000 Job

SQL Server 2005 and higher Job

Final step

udayarumilli.com/script-to-monitor-sql-server-memory-usage/

Introduction:

Script to Monitor SQL Server Memory Usage: Buffer Pool Usage

Results:

Analysis:

Script to Monitor SQL Server Memory Usage: System Memory Information

Results:

Analysis:

Script to Monitor SQL Server Memory Usage: SQL Server Process Memory Usage

Results:

Analysis:

Script to Monitor SQL Server Memory Usage: Database Wise Buffer Usage

Results:

Analysis:

Script to Monitor SQL Server Memory Usage: Object Wise Buffer Usage

Results:

Analysis:

Script to Monitor SQL Server Memory Usage: Top 25 Costliest Stored Procedures by Logical Reads

Results:

Analysis:

Script to Monitor SQL Server Memory Usage: Top Performance Counters – Memory

Results:

Analysis:

Script to Monitor SQL Server Memory Usage: DBCC MEMORYSTATUS

Finally DBCC MemoryStatus:

Summary:

References:

Also Check: