Validate GUID format using SQL

In SQL Server, we have a in-built SQL function called “REPLICATE”. This SQL function is used to validate the number of time’s any specific character appears in a String or varchar. So using REPLICATE function we can validate the GUID format very comfortably. Below is the Query

replicate(‘[0-9A-F]’, 8) + ‘-‘ + replicate(‘[0-9A-F]’, 4) + ‘-‘ +

replicate(‘[0-9A-F]’, 4) + ‘-‘ + replicate(‘[0-9A-F]’, 4) + ‘-‘ +

replicate(‘[0-9A-F]’, 12)

Replicate accepts tow parameters first, set of chars to be validated and second, the number of time the chars should appear. So GUID is nothing but a format in 8-4-4-4-12 (these number here are count of alpha-numeric chars split by hyphen). The above query validates if the given input has GUID format. You can apply this query in columns as shown below

SELECT * FROM   Table WHERE  Column LIKE

replicate(‘[0-9A-F]’, 8) + ‘-‘ + replicate(‘[0-9A-F]’, 4) + ‘-‘ +

replicate(‘[0-9A-F]’, 4) + ‘-‘ + replicate(‘[0-9A-F]’, 4) + ‘-‘ +

replicate(‘[0-9A-F]’, 12)

Similarly you can use this on various data manipulations.

Happy Coding!!!

Please leave your comment or feedback 🙂

An error occurred while executing batch. Error message is: Exception of type ‘System.OutOfMemoryException’ was thrown

Message : An error occurred while executing batch. Error message is: Exception of type ‘System.OutOfMemoryException’ was thrown

Last Friday I was working on some SQL Script which needs to be delivered to Production environment to fix very huge duplicate data (Millions of rows). So I was writing some scripts and started executing in the development environment.  The script ran for 4 hrs and still executing. So I thought since there are millions of rows need to be corrected and each record has multiple child which also need to be updated, so it might take some 8 – 10 hrs to complete. So I left for the day thinking of can check on Monday.

Came to office on Monday, logged into development machine and saw it just processed only half of the records. So for more than 2 complete days it could process only half the records which cannot be tolerated by Production environment. So reworked the script and improved the performance. So while executing the script some 400+ records processed very quickly and  after that the following error has occurred “An error occurred while executing batch. Error message is: Exception of type ‘System.OutOfMemoryException’ was thrown.” At first point I thought some RAM issue or some Disk I/O issues. So checked those and everything seems to be fine. Again tried to run the script, now the above error occurred after processing 220 records…..

So after some googl’ing I could find the actual problem and also solution for the same. In my modified SQL script I used many #Temp tables and inserted some records in the #temp tables which happens in a loop. So each insert into #temp table on each loop the result is shown in Grid i.e., Results to Grid option is enabled. Then I realized the above exception is not due to SQL engine, it is due to .NET Framework (CLR). the Grid displayed below in SQL editor in SSMS is nothing but an .NET Grid. So due to heavy volume of data , the Grid utilizes more memory to occupy all the data. Hence CLR throws SystemOutOfMemory exception, not the SQL engine.

Solution:

Changed the Results to Grid option to Results to Text. So that the results were shown in Text editor, (Probably it should be an typical .NET TextBox) which consumes very less memory. Then the entire script ran flawlessly and got completed in just 2 hrs.

While executing any long running scripts try to show the results to text so that very less memory is consumed and the performance will be improved a lot.

One more point to note: I have included the main script inside the TRY/CATCH block. Inside the CATCH block included the code for Rollback, obviously TRAN in TRY block. So every time an exception occurred, CATCH block invoked hence transaction will be rolled back. But in the above SystemOutOfMemory exception scenario, the CATCH block did not invoked and hence none got Rollbacked. This is due to TRY/CATCH exception handling is handled by CLR Integrated with SQL. SystemOutOfMemory exception occurred due to non-availability of Stack space. So in TRY block due to high utilization of memory, Stack space is full and hence the exception thrown. Due to Non-availability of Stack space CATCH block won’t execute as it needs some stack space memory to execute. Hence CATCH block fails to handle the exception and obviously code inside CATCH block won’t execute.

Creating Proxy at runtime for WCF Service

Here i am going to explain creating proxies at runtime for WCF Service. So basically we use to generate proxies using Add Service Reference and then giving the Url of the WCF service then generate proxy files at client side. Ok, what if something gets changed at service? Do we need to regenerate the proxy files? Yes of course, we have to. How to over come such limitation? Here comes WCF handy in creating proxies at runtime. So we need not to create proxy using Add Service Reference option.

Actually speaking we are not going to create proxy at runtime, we are just invoking calls to WCF service using ChannelFactory class which is available under System.ServiceModel namespace. But to achieve this we need to expose the Interface(which define the service and operation) to the client. This option is more suitable if both the service and client is in your control. Ok lets start implementing the same. I ll create an small calculator service.

Define an Service Contract and Operation Contract. Create an separate class library so that it can be exposed to client.

namespace CalculatorServiceContract

{

  [ServiceContract]

  public interface ICalculator

  {

    [OperationContract]

    int Add (int a, int b);

  }

}

Next step is to implement the Interface in a class. Add reference to the CalculatorServiceContract dll in the Service class project. And implement as follows

namespace CalculatorService
{
    // Implementation of CalculatorServiceContract
    public class CalculatorService : ICalculator
    {
        public int Add(int a, int b)
        {
            return a + b;
        }
   }
}

Next step is to configure web.config at service.

<system.serviceModel>
    <behaviors>

      <serviceBehaviors>
        <behavior>
          <!-- To avoid disclosing metadata information, set the value below to false and remove the metadata endpoint above before deployment -->
          <serviceMetadata httpGetEnabled="true"/>
          <!-- To receive exception details in faults for debugging purposes, set the value below to true.  Set to false before deployment to avoid disclosing exception information -->
          <serviceDebug includeExceptionDetailInFaults="false"/>

        </behavior>
      </serviceBehaviors>
    </behaviors>
    <bindings>
      <basicHttpBinding>
        <binding name="basicHttpBinding_AtServer"/>
      </basicHttpBinding>
    </bindings>
    <services>
      <service name="CalculatorService">
        <endpoint address="http://localhost/CalculatorSample/CalculatorService.svc"
                  binding="basicHttpBinding" bindingConfiguration="basicHttpBinding_AtServer"
                  contract="CalculatorServiceContract.ICalculator"/>
      </service>

    </services>
    <serviceHostingEnvironment multipleSiteBindingsEnabled="true" />
  </system.serviceModel>

Once done with the above steps, build the project and host in IIS. You can also use Visual Studio built-in web server by doing appropriate changes to web.config. For better flexibility i am using IIS.

Next is to create client project.

Create a Client project. It can be ASP.NET or Windows app or even console app. After creating new project try adding reference to CalculatorServiceContract dll. By doing this you are exposing the definition of service and operation to client. Also add reference to System.ServiceModel.

Configure the web.config(in case of ASP.NET app) or app.config(Windows app or console app).

<system.serviceModel>
    <client>
      <endpoint binding="basicHttpBinding"
                address="http://localhost/CalculatorSample/CalculatorService.svc"
                contract="CalculatorServiceContract .ICalculator"
                 name="basicHttpBind"/>
    </client>
  </system.serviceModel>

Then try to invoke the Service calls using ChannelFactory class. ChannelFactory class is used to create endpoint listener. You can also have multiple endpoint listeners by creating channels  of same instance multiple times but with different endpoint configuration. Below is the code for the same.

public static void main(string args[])
{
/*Create a ChannelFactory instance of type ICalculator.
You need to create an channel by specifying the binding
name which is defined at web.config in client project
which is shown above. So the the below code will
create an endpoint listener*/
ICalculator calc = new ChannelFactory<ICalculator>("basicHttpBind").CreateChannel();
 int A = calc.Add(12, 15);
Console.WriteLine(A);
}

Here ChannelFactory will be invoking the service calls by using the endpoint binding information. Endpoint can be of any type. If you want to use netTcpBinding, then appropriate changes need to be done in web.config(server) and web.config(client).

The main advantage of following the above method is whenever any changes is done at service methods, client need not to worry about it. But if any endpoint configuration changes happen in service then appropriate changes has to be done to config file at client because service calls happens only through the endpoint configurations.


			

Handling Fault Exception to Silverlight Client

While working on Silverlight and WCF application, I faced one issue where fault exception occurred at WCF operation contract is not getting propagated to Silverlight client. So after searching net I came to know that it was behavior by default for silverlight client. And these issues can be addressed by little tweak. So, here I am writing on how to propagate fault exception from service to silverlight client.

Reason for this kind of behavior is if any fault exception is raised at operation contract, then the exception is converted to SOAP faults and returned to the client. Since the networking in Silverlight is performed based on browser’s HTTP stack. But this limitation is applicable to Silverlight 2. In Silverlight 3, the networking is performed using browser HTTP stack by default. But the new client HTTP stack allows you to bypass the browser and perform networking operation through operating system.

In Silverlight 2, SOAP faults cannot be propagated to client side because due to limitations in silverlight, it cannot access some of the messages in the browser HTTP stack bodies. So HTTP error code 500 which holds the SOAP faults is converted to 404(Page not found), and same is returned to client. So obviously client will be receiving “Remote Server not found”  exception.

In Silverlight 3, this limitation is solved. Since Silverlight 3 uses client HTTP stack, it is simple that networking is performed by API’s exposed by operating system itself. So you can tell Silverlight to use the client HTTP stack for requests made, instead of using browser HTTP stack. Below is the code use to inform the silverlight to use client http stack for requests call made for sites begin with http://.

1: HttpWebRequest.RegisterPrefix(“http://&#8221;, WebRequestCreator.ClientHttp);

Adding the above line code will make all subsequent call starts with http:// to client HTTP stack.

To support for specific site then add the below code

1: HttpWebRequest.RegisterPrefix(“http://xxxx.com&#8221;, WebRequestCreator.ClientHttp);

Ok, here another question kicked off my mind. What if my Silverlight client is creating web request to WCF service via proxies created at my client side and Silverlight is intended to receive SOAP faults regardless of the address. So do I need to extend my WCF behaviors, so that WCF service will be intelligent enough to convert the http error codes before sending back to client? The answer is YES. Here I will explain on how to achieve this.

My Service Contract and Operation Contract is defined as shown below

1:
2:
3:
4:
5:
6:
7:
 [ServiceContract]
public interface IService1
{
[OperationContract]
[FaultContract(typeof(ValidationException))]
string getData();
}

Data contract class which define data member with some validation rules. StringLength validator is available in System.ComponentModel.DataAnnotations.

1:
2:
3:
4:
5:
6:
7:
 [DataContract]
public class MyDataContract
{
[DataMember]
[StringLength(10, ErrorMessage=”String length should not be greater than 10″)]
public string Name { get; set; }
}

Contracts implemented in Service class

1:
2:
3:
4:
5:
6:
7:
8:
9:
public class Service1 : IService1
{public string getData()
{
MyDataContract dc = new MyDataContract();
dc.Name = “12345678901”; // string length greater than 10
return dc.Name;
}
}

here getData() method throws FaultException of type ValidationException, which inturn converted to SOAP fault. So in order to propagate the SOAP fault to silverlight client we need to extend the endpoint behavior so that SOAP faults can be sent to silverlight client. Below are the steps:

1.) Create a class which is inherited from System.ServiceModel.Configuration.BehaviorExtensionElement and also implemets System.ServiceModel.Description.IEndpointBehavior.

public class SilverlightFaultContractBehavior : BehaviorExtensionElement, IEndpointBehavior     {
        public void ApplyDispatchBehavior(ServiceEndpoint endpoint, EndpointDispatcher endpointDispatcher)
        {
            SilverlightFaultMessageInspector faultnspector = new SilverlightFaultMessageInspector();
            endpointDispatcher.DispatchRuntime.MessageInspectors.Add(faultnspector);
        }        

/*Create a nested class which implements IDispatchMessageInspector. Whenever an fault occurs FaultMessageInspector internally invokes the class which implements the said interface.*/

public class SilverlightFaultMessageInspector : IDispatchMessageInspector
        {          

   public void BeforeSendReply(ref Message reply, object correlationState)
            {
                if (reply.IsFault)
                {
                    HttpResponseMessageProperty property = new HttpResponseMessageProperty();

                    // Here the response code is changed to 200.
                    property.StatusCode = System.Net.HttpStatusCode.OK;

                    reply.Properties[HttpResponseMessageProperty.Name] = property;
                }
            }

            public object AfterReceiveRequest(ref Message request, IClientChannel channel, InstanceContext instanceContext)
            {
                // Dont do anything to incoming request.
                return null;
            }

        }

        // The following methods are not relevant. So doing dummy implementation of  IEndpointBehavior interface

        public void AddBindingParameters(ServiceEndpoint endpoint, BindingParameterCollection bindingParameters)
        {
        }

        public void ApplyClientBehavior(ServiceEndpoint endpoint, ClientRuntime clientRuntime)
        {
        }

        public void Validate(ServiceEndpoint endpoint)
        {
        }

        public override System.Type BehaviorType
        {
            get { return typeof(SilverlightFaultContractBehavior); }
        }

        protected override object CreateBehavior()
        {
            return new SilverlightFaultContractBehavior();
        }

2.) Do the appropriate changes to web.config file.

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
<system.serviceModel>
<extensions>
<behaviorExtensions>
<add name=”silverlightFaults”               type=”SilverlightApplication1.Lib.SilverlightFaultContractBehavior,
SilverlightFaultContractBehavior,
Version=1.0.0.0,
Culture=neutral,
PublicKeyToken=null”/>
</behaviorExtensions>
</extensions><behaviors>
<endpointBehaviors>
<behavior name=”SilverlightFaultBehavior”>
<silverlightFaults/>
</behavior>
</endpointBehaviors><serviceBehaviors>
<behavior name=””>
<serviceMetadata httpGetEnabled=”true” />
<serviceDebug includeExceptionDetailInFaults=”false” />
</behavior>
</serviceBehaviors>
</behaviors><services>
<service name=”SilverlightApplication1.Web.Service1″>
<endpoint address=””
binding=”basicHttpBinding”
contract=”SilverlightApplication1.Web.IService1″
behaviorConfiguration=”SilverlightFaultBehavior” />
</service>
</services><serviceHostingEnvironment aspNetCompatibilityEnabled=”True” multipleSiteBindingsEnabled=”true” />
</system.serviceModel>

3.) Client code:

SilverlightApplication1.ServiceReference1.Service1Client client = new SilverlightApplication1.ServiceReference1.Service1Client();
client.getDataCompleted += new EventHandler<getDataCompletedEventArgs>(client_getDataCompleted);
client.getDataAsync();void client_getDataCompleted(object sender, getDataCompletedEventArgs e)
{
if (e.Error != null)
{
if (e.Error is FaultException)
{
MessageBox.Show(e.Error.Message);
}
}
}

This is how you can propagate Fault Exceptions to Silverlight Client.