Wednesday, 15 October 2014

An Absolute Beginner's Tutorial on Dependency Inversion Principle, Inversion of Control and Dependency Injection

In this article we will talk about the Dependency Inversion Principle, Inversion of Control and Dependency Injection.

Introduction

In this article we will talk about the Dependency Inversion Principle, Inversion of Control and Dependency Injection. We will start by looking at the dependency inversion principle. We will then see how we can use inversion of control to implement dependency inversion principle and finally we will look at what dependency injection is and how can it be implemented.

Background 

Before we start talking about Dependency Injection(DI), we first need to understand the problem that DI solves. To understand the problem, we need to know two things. First dependency Inversion Principle(DIP) and second  Inversion of Controls(IoC). let us start our discussion with DIP, then we will talk about IoC. once we have discussed these two, we will be in a better position to understand Dependency Injection, so we will look at dependency injection in details. Then finally we will discuss see how can we implement Dependency injection.

Dependency Inversion Principle 

Dependency inversion principle is a software design principle which provides us the guidelines to write loosely coupled classes. According to the definition of Dependency inversion principle:
  1. High-level modules should not depend on low-level modules. Both should depend on abstractions.
  2. Abstractions should not depend upon details. Details should depend upon abstractions.
What does this definition mean? What is it trying to convey? let us try to understand the definition by looking at examples. A few years back I was involved in writing a windows service which was supposed to run on a Web server. The sole responsibility of this service was to log messages in event logs whenever there is some problem in the IIS application Pool. So what our team has done initially that we created two classes. One for monitoring the Application Pool and second to write the messages in the event log. Our classes looked like this:
class EventLogWriter
{
    public void Write(string message)
    {
        //Write to event log here
    }
}

class AppPoolWatcher
{
    // Handle to EventLog writer to write to the logs
    EventLogWriter writer = null;

    // This function will be called when the app pool has problem
    public void Notify(string message)
    {
        if (writer == null)
        {
            writer = new EventLogWriter();
        }
        writer.Write(message);
    }
}
From the first look, the above class design seems to be sufficient. It looks perfectly good code. But there is a problem in the above design. This design violates the dependency inversion principle. i.e. the high level module AppPoolWatcher depends on EventLogWriter which is a concrete class and not an abstraction. How is it a problem? Well let me tell you the next requirement we received for this service and the problem will become very clearly visible.
The next requirement we received for this service was to send email to network administrator's email ID for some specific set of error. Now, how will we do that? One idea is to create a class for sending emails and keeping its handle in the AppPoolWatcher but at any moment we will be using only one object either EventLogWriter or EmailSender.
The problem will get even worse when we have more actions to take selectively, like sending SMS. Then we will have to have one more class whose instance will be kept inside the AppPoolWatcher. The dependency inversion principle says that we need to decouple this system in such a way that the higher level modules i.e. the AppPoolWatcher in our case will depend on a simple abstraction and will use it. This abstraction will in turn will be mapped to some concrete class which will perform the actual operation. (Next we will see how this can be done) 

Inversion of Control  

Dependency inversion was a software design principle, it just states that how two modules should depend on each other. Now the question comes, how exactly we are going to do it? The answer is Inversion of control. Inversion of control is the actual mechanism using which we can make the higher level modules to depend on abstractions rather than concrete implementation of lower level modules.
So if I have to implement inversion of control in the above mentioned problem scenario, the first thing we need to do is to create an abstraction that the higher levels will depend on. So let us create an interface that will provide the abstraction to act on the notification received from AppPoolWacther.
public interface INofificationAction
{
    public void ActOnNotification(string message);
}
Now let us change our higher level module i.e. the AppPoolWatcher to use this abstraction rather than the lower level concrete class.
class AppPoolWatcher
{
    // Handle to EventLog writer to write to the logs
    INofificationAction action = null;

    // This function will be called when the app pool has problem
    public void Notify(string message)
    {
        if (action == null)
        {
            // Here we will map the abstraction i.e. interface to concrete class 
        }
        action.ActOnNotification(message);
    }
}
So how will our lower level concrete class will change? how will this class conform to the abstraction i.e. we need to implement the above interface in this class:
class EventLogWriter : INofificationAction
{   
    public void ActOnNotification(string message)
    {
        // Write to event log here
    }
}
So now if I need to have the concrete classes for sending email and sms, these classes will also implement the same interface.
class EmailSender : INofificationAction
{
    public void ActOnNotification(string message)
    {
        // Send email from here
    }
}

class SMSSender : INofificationAction
{
    public void ActOnNotification(string message)
    {
        // Send SMS from here
    }
}
So the final class design will look like:

So what we have done here is that, we have inverted the control to conform to dependency inversion principle. Now our high level modules are dependent only on abstractions and not the lower level concrete implementations, which is exactly what dependency inversion principle states.
But there is still one missing piece. When we look at the code of our AppPoolWatcher, we can see that it is using the abstraction i.e. interface but where exactly are we creating the concrete type and assigning it to this abstraction. To solve this problem, we can do something like:
class AppPoolWatcher
{
    // Handle to EventLog writer to write to the logs
    INofificationAction action = null;

    // This function will be called when the app pool has problem
    public void Notify(string message)
    {
        if (action == null)
        {
            // Here we will map the abstraction i.e. interface to concrete class 
            writer = new EventLogWriter();
        }
        action.ActOnNotification(message);
    }
}
But we are again back to where we have started. The concrete class creation is still inside the higher level class. Can we not make it totally decoupled so that even if we add new classes derived from INotificationAction, we don't have to change this class.
This is exactly where Dependency injection comes in picture. So its time to look at dependency injection in detail now.

Dependency Injection

Now that we know the dependency inversion principle and have seen the inversion of control methodology for implementing the dependency inversion principle, Dependency Injection is mainly for injecting the concrete implementation into a class that is using abstraction i.e. interface inside. The main idea of dependency injection is to reduce the coupling between classes and move the binding of abstraction and concrete implementation out of the dependent class.
Dependency injection can be done in three ways.
  1. Constructor injection
  2. Method injection
  3. Property injection

Constructor Injection 

In this approach we pass the object of the concrete class into the constructor of the dependent class. So what we need to do to implement this is to have a constructor in the dependent class that will take the concrete class object and assign it to the interface handle this class is using. So if we need to implement this for our AppPoolWatcher class:
class AppPoolWatcher
{
    // Handle to EventLog writer to write to the logs
    INofificationAction action = null;

    public AppPoolWatcher(INofificationAction concreteImplementation)
    {
        this.action = concreteImplementation;
    }

    // This function will be called when the app pool has problem
    public void Notify(string message)
    {   
        action.ActOnNotification(message);
    }
}
In the above code, the constructor will take the concrete class object and bind it to the interface handle. So if we need to pass the EventLogWriter's concrete implementation into this class, all we need to do is
EventLogWriter writer = new EventLogWriter();
AppPoolWatcher watcher = new AppPoolWatcher(writer);
watcher.Notify("Sample message to log");
Now if we want this class to send email or sms instead, all we need to do is to pass the object of the respective class in the AppPoolWatcher's constructor. This method is useful when we know that the instance of the dependent class will use the same concrete class for its entire lifetime.

Method Injection

In constructor injection we saw that the dependent class will use the same concrete class for its entire lifetime. Now if we need to pass separate concrete class on each invocation of the method, we have to pass the dependency in the method only.
So in method injection approach we pass the object of the concrete class into the method the dependent class which is actually invoking the action. So what we need to do to implement this is to have the action function also accept an argument for the concrete class object and assign it to the interface handle this class is using and invoke the action. So if we need to implement this for our AppPoolWatcher class:
class AppPoolWatcher
{
    // Handle to EventLog writer to write to the logs
    INofificationAction action = null;

    // This function will be called when the app pool has problem
    public void Notify(INofificationAction concreteAction, string message)
    {
        this.action = concreteAction;
        action.ActOnNotification(message);
    }
}
In the above code the action method i.e. Notify will take the concrete class object and bind it to the interface handle. So if we need to pass the EventLogWriter's concrete implementation into this class, all we need to do is
EventLogWriter writer = new EventLogWriter();
AppPoolWatcher watcher = new AppPoolWatcher();
watcher.Notify(writer, "Sample message to log");
Now if we want this class to send email or sms instead, all we need to do is to pass the object of the respective class in the AppPoolWatcher's invocation method i.e. Notify method in the above example.

Property Injection

Now we have discussed two scenarios where in constructor injection we knew that the dependent class will use one concrete class for the entire lifetime. The second approach is to use the method injection where we can pass the concrete class object in the action method itself. But what if the responsibility of selection of concrete class and invocation of method are in separate places. In such cases we need property injection.
So in this approach we pass the object of the concrete class via a setter property that was exposed by the dependent class. So what we need to do to implement this is to have a Setter property or function in the dependent class that will take the concrete class object and assign it to the interface handle this class is using. So if we need to implement this for our AppPoolWatcher class:
class AppPoolWatcher
{
    // Handle to EventLog writer to write to the logs
    INofificationAction action = null;

    public INofificationAction Action
    {
        get
        {
            return action;
        }
        set
        {
            action = value;
        }
    }

    // This function will be called when the app pool has problem
    public void Notify(string message)
    {   
        action.ActOnNotification(message);
    }
}
In the above code the setter of Action property will take the concrete class object and bind it to the interface handle. So if we need to pass the EventLogWriter's concrete implementation into this class, all we need to do is
EventLogWriter writer = new EventLogWriter();
AppPoolWatcher watcher = new AppPoolWatcher();
// This can be done in some class
watcher.Action = writer;

// This can be done in some other class
watcher.Notify("Sample message to log");
Now if we want this class to send email or sms instead, all we need to do is to pass the object of the respective class in the setter exposed by AppPoolWatcher class. This approach is useful when the responsibility of selecting the concrete implementation and invoking the action are done in separate places/modules.
In languages where properties are not supported, there is a separate function to set the dependency. This approach is also known as setter injection. The important thing to note in this approach is that there is a possibility that someone has created the dependent class but no one has set the concrete class dependency yet. If we try to invoke the action in such cases then we should have either some default dependency mapped to the dependent class or have some mechanism to ensure that application will behave properly.

A Note on IoC Containers

Constructor injection is the mostly used approach when it comes to implementing the dependency injection. If we need to pass different dependencies on every method call then we use method injection. Property injection is used less frequently.
All the three approaches we have discussed for dependency injection are ok if we have only one level of dependency. But what if the concrete classes are also dependent of some other abstractions. So if we have chained and nested dependencies, implementing dependency injection will become quite complicated. That is where we can use IoC containers. IoC containers will help us to map the dependencies easily when we have chained or nested dependencies.

Point of interest

In this article we have discussed about Dependency inversion principle(Which is the D part of SOLID object oriented principle). We have also discussed how inversion of control is used to implement dependency inversion and finally we have seen how dependency injection help in creating decoupled classes and how to implement dependency injection. This article has been written from beginner's perspective. I hope this has been informative.


--------------------------------------------------------------------------------------------
This article is from www.codeproject.com

Friday, 29 August 2014

SQL Server Support for Linked Server and Distributed Queries against Windows Azure SQL Database

It is now possible to add a Windows Azure SQL Database as a Linked Server and then use it with Distributed Queries that span the on-premises and cloud databases. This is a new component for database hybrid solutions spanning on-premises corporate networks and the Windows Azure cloud.  
SQL Server box product contains a feature called “Distributed Query” that allows users to write queries to combine data from local data sources and data from remote sources (including data from non-SQL Server data sources) defined as Linked Servers. Previously Windows Azure SQL Databases didn’t support distributed queries natively, and needed to use the ODBC-to-OLEDB proxy, which was not recommended for performance reasons. We are happy to announce that Windows Azure SQL Databases can now be used through “Distributed Query”. In practical terms, every single Windows Azure SQL Database (except the virtual master) can be added as an individual Linked Server and then used directly in your database applications as any other database.
The benefits of using Windows Azure SQL Database include manageability, high availability, scalability, working with a familiar development model, and a relational data model. The requirements of your database application play an important role in deciding how it would use Windows Azure SQL Databases in the cloud. You can move all of your data at once to Window Azure SQL Databases, or progressively move some of your data while keeping the remaining data on-premises. For such a hybrid database application, Windows Azure SQL Databases can now be added as linked servers and the database application can issue distributed queries to combine data from Windows Azure SQL Databases and on-premise data sources.
 Here’s a simple example explaining how to connect to a Windows Azure SQL Database using Distributed Queries:
—— Configure the linked server
– Add one Windows Azure SQB DB as Linked Server
EXEC sp_addlinkedserver
@server=‘myLinkedServer’, — here you can specify the name of the linked server
@srvproduct=”,     
@provider=‘sqlncli’, — using SQL Server native client
@datasrc=‘myServer.database.windows.net’,   — add here your server name
@location=”,
@provstr=”,
@catalog=‘myDatabase’  – add here your database name as initial catalog (you cannot connect to the master database)
– Add credentials and options to this linked server
EXEC sp_addlinkedsrvlogin
@rmtsrvname = ‘myLinkedServer’,
@useself = ‘false’,
@rmtuser = ‘myLogin’,             – add here your login on Azure DB
@rmtpassword = ‘myPassword’ – add here your password on Azure DB
EXEC sp_serveroption ‘myLinkedServer’, ‘rpc out’, true;
—— Now you can use the linked server to execute 4-part queries
– You can create a new table in the Azure DBexec (‘CREATE TABLE t1tutut2(col1 int not null CONSTRAINT PK_col1 PRIMARY KEY CLUSTERED (col1) )’) at myLinkedServer
– Insert data from your local SQL Server
exec (‘INSERT INTO t1tutut2 VALUES(1),(2),(3)’) at myLinkedServer
 
– Query the data using 4-part names
select * from myLinkedServer.myDatabase.dbo.myTable
More information on Linked Servers and Distributed Queries is available here.

Recursive Triggers

A trigger may now call itself recursively if this option is set to on (Default) for a particular database.  Just like SQL Server 2012, the option can be configured via the following query:
ALTER DATABASE YOURDBNAME SET RECURSIVE_TRIGGERS ON|OFF;
For full information on recursive triggers, see the SQL Server 2012 Books Online Topic.

DBCC SHOW_STATISTICS Now Supported

DBCC SHOW_STATISTICS displays current query optimization statistics for a table or indexed view. The query optimizer uses statistics to estimate the cardinality or number of rows in the query result, which enables the query optimizer to create a high quality query plan. For example, the query optimizer could use cardinality estimates to choose the index seek operator instead of the index scan operator in the query plan, improving query performance by avoiding a resource-intensive index scan.  For more information click here.

Ability to Configure SQL Database Firewall Rules at the Database Level

Previously Windows Azure SQL Database firewall rules could be set only at the server level, either through the management portal or via T-SQL commands. Now, firewall rules can be additionally set at the more granular database level, with different rules for different databases hosted on the same logical SQL Database server.  For more information click here. 
For questions or more technical information about these features, you can post a question on theSQL Database MSDN Support Forum.

Thursday, 21 August 2014

Shell And Wait - Run External Application in VBA

Shell And Wait
This pages describes the VBA Shell function and provides a procedure that will wait for a Shell'd function to terminate.
ShortFadeBar
The Shell Function
The VBA Shell function can be used to start an external program or perform any operation for which you would normally use the Run item on the Windows start menu. The Shell function starts the command text and then immediately returns control back to the calling VBA code -- it does not wait for the command used in Shell to terminate. This page describes a function named ShellAndWait that will call the Shell function and then wait for the Shell'd process to terminate or for a prescribed timeout interval to expire.
The ShellAndWait Function
The ShellAndWait function calls Shell and then waits for the Shell'd process to terminate. You can specify an interval after which the function times out and returns to the caller. The declaration of ShellAndWait is as follows:
    Public Function ShellAndWait(ShellCommand As String, _
                        TimeOutMs As Long, _
                        ShellWindowState As VbAppWinStyle, _
                        BreakKey As ActionOnBreak) As ShellAndWaitResult
In the declaration, ShellCommand is the string that is passed to the VBA Shell function. TimeOutMs is the number of milliseconds to wait before returning a time-out result. ShellWindowsState is an item in theVbAppWinStyle enum that specifies the window style in which to open the Shell'd process. This value is simply passed through to the Shell function. BreakKey indicates how to handle the Application's Cancel key (CTRL BREAK). These parameters are described below.
ShellCommand
This parameter is the command text that is passed to the Shell function. This must be a valid command string; that is, a string that would work in the Run dialog on the Windows Start menu. If the text is invalid (such as attempting to execute a non-existant exe file), the function returns a result indicating failure.
TimeOutMs
This parameter specifies the number of milliseconds to wait for the Shell'd process to terminate. If the specified interval expires before the process ends, the wait is abandoned and the function returns a value indicating a time out occurred. If TimeOutMs is 0, the wait never expires and ShellAndWait will wait indefinitely for the process to terminate.
ShellWindowState
This parameter should be one of the values in the VbAppWinStyle enum that indicates the type of window (e.g., maximized or minimized) in which the Shell'd process should open. This value is simply passed through to theShell command. If this value is not valid, the function returns a result indicating failure.
BreakKey
This parameter should be one of the values in the ActionOnBreak enum to indicate how the code should respond if the user presses CTRL Break. If BreakKey is IgnoreBreak, the cancel key is ignored. If BreakKey isAbandonWait, the wait is terminated and the function returns a valid indicating that the user abandoned the wait. IfBreakKey is PromptUser, the function displays a vbYesNo message box asking the user whether to abandon or continue waiting. The ActionOnBreak enum is shown below.
    Public Enum ActionOnBreak
        IgnoreBreak = 0
        AbandonWait = 1
        PromptUser = 2
    End Enum
Return Values
The function returns as its result one of the values in the ShellAndWaitResult enum, shown below:
    Public Enum ShellAndWaitResult
        Success = 0
        Failure = 1
        TimeOut = 2
        InvalidParameter = 3
        SysWaitAbandoned = 4
        UserWaitAbandoned = 5
        UserBreak = 6
    End Enum
Success indicates that the Shell'd process terminated successfully before the TimeOutMs interval expired. M

Failure indicates that the Shell function failed, most likely due to an invalid command string in theShellCommand parameter.

TimeOut indicates that the interval specified by TimeOutMs expired before the process terminated. This result is returned only if TimeOutMs is greater than zero.

InvalidParameter indicates that one of the parameters passed to ShellAndWait is invalid.

SysWaitAbandoned indicates that Windows abandoned the wait operation for internal reasons.

UserWaitAbandoned indicates that the user abandoned the wait operation by pressing CTRL Break. This is returne only if BreakKey is set to AbandonWait.

UserBreak indicates that the user press CTRL Break to terminate the wait and responded "no" to the "continue to wait" prompt. This value is returned only when BreakKey is set to PromptUser.
You can download a module file containing the code.
The complete code module is shown below.
Option Explicit
Option Compare Text

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' modShellAndWait
' By Chip Pearson, chip@cpearson.com, www.cpearson.com
' This page on the web site: www.cpearson.com/Excel/ShellAndWait.aspx
' 9-September-2008
'
' This module contains code for the ShellAndWait function that will Shell to a process
' and wait for that process to end before returning to the caller.
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Private Declare Function WaitForSingleObject Lib "kernel32" ( _
    ByVal hHandle As Long, _
    ByVal dwMilliseconds As Long) As Long

Private Declare Function OpenProcess Lib "kernel32.dll" ( _
    ByVal dwDesiredAccess As Long, _
    ByVal bInheritHandle As Long, _
    ByVal dwProcessId As Long) As Long

Private Declare Function CloseHandle Lib "kernel32" ( _
    ByVal hObject As Long) As Long

Private Const SYNCHRONIZE = &H100000

Public Enum ShellAndWaitResult
    Success = 0
    Failure = 1
    TimeOut = 2
    InvalidParameter = 3
    SysWaitAbandoned = 4
    UserWaitAbandoned = 5
    UserBreak = 6
End Enum

Public Enum ActionOnBreak
    IgnoreBreak = 0
    AbandonWait = 1
    PromptUser = 2
End Enum

Private Const STATUS_ABANDONED_WAIT_0 As Long = &H80
Private Const STATUS_WAIT_0 As Long = &H0
Private Const WAIT_ABANDONED As Long = (STATUS_ABANDONED_WAIT_0 + 0)
Private Const WAIT_OBJECT_0 As Long = (STATUS_WAIT_0 + 0)
Private Const WAIT_TIMEOUT As Long = 258&
Private Const WAIT_FAILED As Long = &HFFFFFFFF
Private Const WAIT_INFINITE = -1&


Public Function ShellAndWait(ShellCommand As String, _
                    TimeOutMs As Long, _
                    ShellWindowState As VbAppWinStyle, _
                    BreakKey As ActionOnBreak) As ShellAndWaitResult
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' ShellAndWait
'
' This function calls Shell and passes to it the command text in ShellCommand. The function
' then waits for TimeOutMs (in milliseconds) to expire.
'
'   Parameters:
'       ShellCommand
'           is the command text to pass to the Shell function.
'
'       TimeOutMs
'           is the number of milliseconds to wait for the shell'd program to wait. If the
'           shell'd program terminates before TimeOutMs has expired, the function returns
'           ShellAndWaitResult.Success = 0. If TimeOutMs expires before the shell'd program
'           terminates, the return value is ShellAndWaitResult.TimeOut = 2.
'
'       ShellWindowState
'           is an item in VbAppWinStyle specifying the window state for the shell'd program.
'
'       BreakKey
'           is an item in ActionOnBreak indicating how to handle the application's cancel key
'           (Ctrl Break). If BreakKey is ActionOnBreak.AbandonWait and the user cancels, the
'           wait is abandoned and the result is ShellAndWaitResult.UserWaitAbandoned = 5.
'           If BreakKey is ActionOnBreak.IgnoreBreak, the cancel key is ignored. If
'           BreakKey is ActionOnBreak.PromptUser, the user is given a ?Continue? message. If the
'           user selects "do not continue", the function returns ShellAndWaitResult.UserBreak = 6.
'           If the user selects "continue", the wait is continued.
'
'   Return values:
'            ShellAndWaitResult.Success = 0
'               indicates the the process completed successfully.
'            ShellAndWaitResult.Failure = 1
'               indicates that the Wait operation failed due to a Windows error.
'            ShellAndWaitResult.TimeOut = 2
'               indicates that the TimeOutMs interval timed out the Wait.
'            ShellAndWaitResult.InvalidParameter = 3
'               indicates that an invalid value was passed to the procedure.
'            ShellAndWaitResult.SysWaitAbandoned = 4
'               indicates that the system abandoned the wait.
'            ShellAndWaitResult.UserWaitAbandoned = 5
'               indicates that the user abandoned the wait via the cancel key (Ctrl+Break).
'               This happens only if BreakKey is set to ActionOnBreak.AbandonWait.
'            ShellAndWaitResult.UserBreak = 6
'               indicates that the user broke out of the wait after being prompted with
'               a ?Continue message. This happens only if BreakKey is set to
'               ActionOnBreak.PromptUser.
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

Dim TaskID As Long
Dim ProcHandle As Long
Dim WaitRes As Long
Dim Ms As Long
Dim MsgRes As VbMsgBoxResult
Dim SaveCancelKey As XlEnableCancelKey
Dim ElapsedTime As Long
Dim Quit As Boolean
Const ERR_BREAK_KEY = 18
Const DEFAULT_POLL_INTERVAL = 500

If Trim(ShellCommand) = vbNullString Then
    ShellAndWait = ShellAndWaitResult.InvalidParameter
    Exit Function
End If

If TimeOutMs < 0 Then
    ShellAndWait = ShellAndWaitResult.InvalidParameter
    Exit Function
ElseIf TimeOutMs = 0 Then
    Ms = WAIT_INFINITE
Else
    Ms = TimeOutMs
End If

Select Case BreakKey
    Case AbandonWait, IgnoreBreak, PromptUser
        ' valid
    Case Else
        ShellAndWait = ShellAndWaitResult.InvalidParameter
        Exit Function
End Select

Select Case ShellWindowState
    Case vbHide, vbMaximizedFocus, vbMinimizedFocus, vbMinimizedNoFocus, vbNormalFocus, vbNormalNoFocus
        ' valid
    Case Else
        ShellAndWait = ShellAndWaitResult.InvalidParameter
        Exit Function
End Select

On Error Resume Next
Err.Clear
TaskID = Shell(ShellCommand, ShellWindowState)
If (Err.Number <> 0) Or (TaskID = 0) Then
    ShellAndWait = ShellAndWaitResult.Failure
    Exit Function
End If

ProcHandle = OpenProcess(SYNCHRONIZE, False, TaskID)
If ProcHandle = 0 Then
    ShellAndWait = ShellAndWaitResult.Failure
    Exit Function
End If

On Error GoTo ErrH:
SaveCancelKey = Application.EnableCancelKey
Application.EnableCancelKey = xlErrorHandler
WaitRes = WaitForSingleObject(ProcHandle, DEFAULT_POLL_INTERVAL)
Do Until WaitRes = WAIT_OBJECT_0
    DoEvents
    Select Case WaitRes
        Case WAIT_ABANDONED
            ' Windows abandoned the wait
            ShellAndWait = ShellAndWaitResult.SysWaitAbandoned
            Exit Do
        Case WAIT_OBJECT_0
            ' Successful completion
            ShellAndWait = ShellAndWaitResult.Success
            Exit Do
        Case WAIT_FAILED
            ' attach failed
            ShellAndWait = ShellAndWaitResult.Failure
            Exit Do
        Case WAIT_TIMEOUT
            ' Wait timed out. Here, this time out is on DEFAULT_POLL_INTERVAL.
            ' See if ElapsedTime is greater than the user specified wait
            ' time out. If we have exceed that, get out with a TimeOut status.
            ' Otherwise, reissue as wait and continue.
            ElapsedTime = ElapsedTime + DEFAULT_POLL_INTERVAL
            If Ms > 0 Then
                ' user specified timeout
                If ElapsedTime > Ms Then
                    ShellAndWait = ShellAndWaitResult.TimeOut
                    Exit Do
                Else
                    ' user defined timeout has not expired.
                End If
            Else
                ' infinite wait -- do nothing
            End If
            ' reissue the Wait on ProcHandle
            WaitRes = WaitForSingleObject(ProcHandle, DEFAULT_POLL_INTERVAL)
            
        Case Else
            ' unknown result, assume failure
            ShellAndWait = ShellAndWaitResult.Failure
            Exit Do
            Quit = True
    End Select
Loop

CloseHandle ProcHandle
Application.EnableCancelKey = SaveCancelKey
Exit Function

ErrH:
Debug.Print "ErrH: Cancel: " & Application.EnableCancelKey
If Err.Number = ERR_BREAK_KEY Then
    If BreakKey = ActionOnBreak.AbandonWait Then
        CloseHandle ProcHandle
        ShellAndWait = ShellAndWaitResult.UserWaitAbandoned
        Application.EnableCancelKey = SaveCancelKey
        Exit Function
    ElseIf BreakKey = ActionOnBreak.IgnoreBreak Then
        Err.Clear
        Resume
    ElseIf BreakKey = ActionOnBreak.PromptUser Then
        MsgRes = MsgBox("User Process Break." & vbCrLf & _
            "Continue to wait?", vbYesNo)
        If MsgRes = vbNo Then
            CloseHandle ProcHandle
            ShellAndWait = ShellAndWaitResult.UserBreak
            Application.EnableCancelKey = SaveCancelKey
        Else
            Err.Clear
            Resume Next
        End If
    Else
        CloseHandle ProcHandle
        Application.EnableCancelKey = SaveCancelKey
        ShellAndWait = ShellAndWaitResult.Failure
    End If
Else
    ' some other error. assume failure
    CloseHandle ProcHandle
    ShellAndWait = ShellAndWaitResult.Failure
End If

Application.EnableCancelKey = SaveCancelKey

End Function

---------------------------------------------------------------------
This page is from http://www.cpearson.com/excel/ShellAndWait.aspx

Sunday, 10 August 2014

Max length of SQL Server instance name?

Instance names for SQL Server are limited to 16 characters
It doesn't list SQL Server 2000 online but looking at the BOL that I have installed for SQL Server 2000 it is also limited to 16 characters.
SQL Server 2000 - Books Online
EDIT
Use this screen to add and maintain instances of Microsoft® SQL Server™ 2000.
Options
Default
  • When selected, a default instance of SQL Server 2000 is installed. Click Next to proceed with the install process.
  • When cleared, you can install or maintain a named instance of SQL Server 2000.
    Note If this check box is not enabled, Setup has detected a default instance of SQL Server on this computer. The default instance could be an installation of SQL Server 6.5, SQL Server version 7.0, or it could be the default instance of SQL Server 2000, already installed. Only one installation of SQL Server, any version, can be the default instance at any one time. For more information, see Multiple Instances of SQL Server.
Instance Name
Enter a new instance name, or the name of the instance to maintain. Review and follow the rules for instance names.
Important It is recommended that instance names be kept to less than 10 characters. Instance names can appear in the user interface of various SQL Server and system tools; shorter names are more readable.
*Instance Naming Rules *
  • An instance name is not case-sensitive.
  • An instance name cannot be the terms Default or MSSQLServer.
  • Instance names must follow the rules for SQL Server identifiers and cannot be reserved keywords.
  • Instance names are limited to 16 characters.
  • The first character in the instance name must be a letter, an ampersand (&), an underscore (_), or a number sign (#). Acceptable letters are those defined by the Unicode Standard 2.0, which includes Latin characters a-z and A-Z, in addition to letter characters from other languages.
  • Subsequent characters can be:
    • Letters as defined in the Unicode Standard 2.0.
    • Decimal numbers from either Basic Latin or other national scripts.
    • The dollar sign ($), a number sign (#), or an underscore (_).
  • Embedded spaces or special characters are not allowed in instance names. Neither is the backslash (), a comma (,), a colon (:), or the at sign (@).
Warning Only characters that are valid in the current Microsoft Windows® code page can be used in instance names in SQL Server 2000. If a Unicode character not supported under the current code page is used, an error occurs.

Friday, 1 August 2014

Search for a string in all tables of SQL Server Database

This article is from: http://gallery.technet.microsoft.com/scriptcenter/c0c57332-8624-48c0-b4c3-5b31fe641c58

Description
This is a simple stored procedure which can search through all the data in the SQL Server database tables. Also this has capability to search in the selected tables if the table names are specified with comma separated values. This has a capability to generate the SQL alone without executing the SQL. Enclosing the script version of it also.
Parameters and usage
@Tablenames -- Provide a single table name or multiple table name with comma seperated.
If left blank , it will check for all the tables in the database
@SearchStr -- Provide the search string. Use the '%' to coin the search.
EX : X%--- will give data staring with X
%X--- will give data ending with X
%X%--- will give data containig X
@GenerateSQLOnly -- Provide 1 if you only want to generate the SQL statements without seraching the database.
By default it is 0 and it will search.
Samples :
1. To search data in a table
EXEC SearchTables @Tablenames = 'T1'
,@SearchStr = '%TEST%'
The above sample searches in table T1 with string containing TEST.
2. To search in a multiple table
EXEC SearchTables @Tablenames = 'T2'
,@SearchStr = '%TEST%'
The above sample searches in tables T1 & T2 with string containing TEST.

3. To search in a all table
EXEC SearchTables @Tablenames = '%'
,@SearchStr = '%TEST%'
The above sample searches in all table with string containing TEST.
4. Generate the SQL for the Select statements
EXEC SearchTables @Tablenames = 'T1'
,@SearchStr = '%TEST%'
,@GenerateSQLOnly = 1

Script

SQL
IF OBJECT_ID('SP_SearchTables','P') IS NOT NULL 
    DROP PROCEDURE SP_SearchTables 
GO 
CREATE PROCEDURE SP_SearchTables 
 @Tablenames VARCHAR(500) 
,@SearchStr NVARCHAR(60) 
,@GenerateSQLOnly Bit = 0 
AS 
 
/* 
    Parameters and usage 
 
    @Tablenames        -- Provide a single table name or multiple table name with comma seperated.  
                        If left blank , it will check for all the tables in the database 
    @SearchStr        -- Provide the search string. Use the '%' to coin the search.  
                        EX : X%--- will give data staring with X 
                             %X--- will give data ending with X 
                             %X%--- will give data containig  X 
    @GenerateSQLOnly -- Provide 1 if you only want to generate the SQL statements without seraching the database.  
                        By default it is 0 and it will search. 
 
    Samples : 
 
    1. To search data in a table 
 
        EXEC SP_SearchTables @Tablenames = 'T1' 
                         ,@SearchStr  = '%TEST%' 
 
        The above sample searches in table T1 with string containing TEST. 
 
    2. To search in a multiple table 
 
        EXEC SP_SearchTables @Tablenames = 'T2' 
                         ,@SearchStr  = '%TEST%' 
 
        The above sample searches in tables T1 & T2 with string containing TEST. 
     
    3. To search in a all table 
 
        EXEC SP_SearchTables @Tablenames = '%' 
                         ,@SearchStr  = '%TEST%' 
 
        The above sample searches in all table with string containing TEST. 
 
    4. Generate the SQL for the Select statements 
 
        EXEC SP_SearchTables @Tablenames        = 'T1' 
                         ,@SearchStr        = '%TEST%' 
                         ,@GenerateSQLOnly    = 1 
 
*/ 
 
    SET NOCOUNT ON 
 
    DECLARE @MatchFound BIT 
 
    SELECT @MatchFound = 0 
 
    DECLARE @CheckTableNames Table 
    ( 
    Tablename sysname 
    ) 
 
    DECLARE @SQLTbl TABLE 
    ( 
     Tablename        SYSNAME 
    ,WHEREClause    VARCHAR(MAX) 
    ,SQLStatement   VARCHAR(MAX) 
    ,Execstatus        BIT  
    ) 
 
    DECLARE @SQL VARCHAR(MAX) 
    DECLARE @tmpTblname sysname 
    DECLARE @ErrMsg VARCHAR(100) 
 
    IF LTRIM(RTRIM(@Tablenames)) IN ('' ,'%') 
    BEGIN 
 
        INSERT INTO @CheckTableNames 
        SELECT Name 
          FROM sys.tables 
    END 
    ELSE 
    BEGIN 
 
        SELECT @SQL = 'SELECT ''' + REPLACE(@Tablenames,',',''' UNION SELECT ''') + '''' 
 
        INSERT INTO @CheckTableNames 
        EXEC(@SQL) 
 
    END 
 
    IF NOT EXISTS(SELECT 1 FROM @CheckTableNames) 
    BEGIN 
         
        SELECT @ErrMsg = 'No tables are found in this database ' + DB_NAME() + ' for the specified filter' 
        PRINT @ErrMsg 
        RETURN 
 
    END 
     
    INSERT INTO @SQLTbl 
    ( Tablename,WHEREClause) 
    SELECT QUOTENAME(SCh.name) + '.' + QUOTENAME(ST.NAME), 
            ( 
                SELECT '[' + SC.Name + ']' + ' LIKE ''' + @SearchStr + ''' OR ' + CHAR(10) 
                  FROM SYS.columns SC 
                  JOIN SYS.types STy 
                    ON STy.system_type_id = SC.system_type_id 
                   AND STy.user_type_id =SC.user_type_id 
                 WHERE STY.name in ('varchar','char','nvarchar','nchar','text') 
                   AND SC.object_id = ST.object_id 
                 ORDER BY SC.name 
                FOR XML PATH('') 
            ) 
      FROM  SYS.tables ST 
      JOIN @CheckTableNames chktbls 
                ON chktbls.Tablename = ST.name  
      JOIN SYS.schemas SCh 
        ON ST.schema_id = SCh.schema_id 
     WHERE ST.name <> 'SearchTMP' 
      GROUP BY ST.object_id, QUOTENAME(SCh.name) + '.' +  QUOTENAME(ST.NAME) ; 
     
 
      UPDATE @SQLTbl 
         SET SQLStatement = 'SELECT * INTO SearchTMP FROM ' + Tablename + ' WHERE ' + substring(WHEREClause,1,len(WHEREClause)-5) 
 
      DELETE FROM @SQLTbl 
       WHERE WHEREClause IS NULL 
     
    WHILE EXISTS (SELECT 1 FROM @SQLTbl WHERE ISNULL(Execstatus ,0) = 0) 
    BEGIN 
 
        SELECT TOP 1 @tmpTblname = Tablename , @SQL = SQLStatement 
          FROM @SQLTbl  
         WHERE ISNULL(Execstatus ,0) = 0 
 
         IF @GenerateSQLOnly = 0 
         BEGIN 
 
            IF OBJECT_ID('SearchTMP','U') IS NOT NULL 
                DROP TABLE SearchTMP 
                 
            EXEC (@SQL) 
 
            IF EXISTS(SELECT 1 FROM SearchTMP) 
            BEGIN 
                SELECT Tablename=@tmpTblname,* FROM SearchTMP 
                SELECT @MatchFound = 1 
            END 
 
         END 
         ELSE 
         BEGIN 
             PRINT REPLICATE('-',100) 
             PRINT @tmpTblname 
             PRINT REPLICATE('-',100) 
             PRINT replace(@SQL,'INTO SearchTMP','') 
         END 
 
         UPDATE @SQLTbl 
            SET Execstatus = 1 
          WHERE Tablename = @tmpTblname 
 
    END 
 
    IF @MatchFound = 0  
    BEGIN 
        SELECT @ErrMsg = 'No Matches are found in this database ' + DB_NAME() + ' for the specified filter' 
        PRINT @ErrMsg 
        RETURN 
    END 
     
    SET NOCOUNT OFF 
 
go 
 
 

Thursday, 31 July 2014

ThinStuff: remote connection is refused if you try to connect from Linux client, iOSx (iPhone, iPad), Android devices, etc which do not support NLA.

NLA and Windows 8 / 8.1


NLA (Network Level Authentication) is per default enabled since Windows 8 / 8.1 and Windows Server 2012.

Due to this option remote connection is refused if you try to connect from Linux client, iOSx (iPhone, iPad), Android devices, etc which do not support NLA.

If you are running Windows 8 Professional, Enterprise or Windows 2012 server you can easily uncheck NLA
"Computer" - right click "Properties" - "Remote Settings":



Unfortunately this user interface is not available with Standard version.

Here you have to modify the according registry key.
Open Registry-Editor (Start - Run - Enter: regedit) and move to path
HKLM\SYSTEM\CurrentControlSet\Control\Terminal Server\ Winstations\RDP-Tcp
in the right pane look for value "UserAuthentication" and change the value to "0"
(reboot required!)

Unable to send an email to multiple addresses/recipients using C#

message.To.Add("one@example.com, two@example.com"));
The e-mail addresses to add to the MailAddressCollection. Multiple e-mail addresses must be separated with a comma character (",").