SQL Datetime Convertion

You often required to convert string to sql datetime.

I found the following list in SQLUSA best practice site. Just crossposting this for reference.

— SQL Server string to date / datetime conversion – datetime string format sql server

— MSSQL string to datetime conversion – convert char to date – convert varchar to date

— Subtract 100 from style number (format) for yy instead yyyy (or ccyy with century)

SELECT convert(datetime, ‘Oct 23 2012 11:01AM’, 100) — mon dd yyyy hh:mmAM (or PM)

SELECT convert(datetime, ‘Oct 23 2012 11:01AM’) — 2012-10-23 11:01:00.000

— Without century (yy) string date conversion – convert string to datetime function

SELECT convert(datetime, ‘Oct 23 12 11:01AM’, 0) — mon dd yy hh:mmAM (or PM)

SELECT convert(datetime, ‘Oct 23 12 11:01AM’) — 2012-10-23 11:01:00.000

Read more of this post


System.ServiceModel.AddressAccessDeniedException: HTTP could not register URL

Windows Vista or later version of windows operating systems has a new security setting that restricts users to run service with default administrative privilege. As a consequence people are migrating or developing in VISTA or Win 7 or Windows 2008 server might encounter the following exception.

HTTP could not register URL http://+:8000/. Your process does not have access rights to this namespace (see http://go.microsoft.com/fwlink/?LinkId=70353 for details).

Read more of this post

The maximum message size quota for incoming messages exceeded exception in WCF

This is a common error you get when developing you are consuming WCF service operation that deals with large data.

To overcome this error you need to customize the bindings, service behavior and endpoint behavior of the service.

  1. Customize the binding:

First of all you need to set the maxbufferpoolsize and maxReceivedMessageSize in the binding.

MaxBufferPoolSize  gets or sets the maximum size of any buffer pools used by the transport.  The default is 524,288 bytes.

MaxReceivedMessageSize gets and sets the maximum message size that can be receivied. The default is 65536 bytes.

ReaderQuotas: Defines the contraints on the complexity of SOAP messages that can be processed by endpoint configured with a binding. You need to set the maxBytesPerRead, maxDepth, maxNameTableCharCount,maxStringContentLength.

Read more of this post

UDP Binding Example in WCF 4.5


UDP (User Datagram Protocol) is a connectionless protocol that runs on top of IP networks. It is similar to TCP/IP. However, it provides less error handling and recovery that TCP/IP. It provides a direct way of send and receive datagrams over an IP network, primarily used for broadcasting messages across networks.

UDP and WCF 4.5:

WCF 4.5 framework supports UDP bindings. It is one of the new features of WCF 4.5. Following is an example how to use UDP Binding in .Net 4.5

Service Configuration :




<behavior name=””>

<serviceMetadata httpGetEnabled=”true” httpsGetEnabled=”true”/>

<serviceDebug includeExceptionDetailInFaults=”false”/>

<serviceThrottling maxConcurrentCalls=”1000″ maxConcurrentInstances=”1000″ maxConcurrentSessions=”1000″/>





<service name=”UdpServiceHost.HelloService”>

<endpoint address=”soap.udp://localhost:8080/” binding=”udpBinding” contract=”UdpServiceHost.IService”/>



<add baseAddress=”http://localhost:8080/UdpServiceHost/”/&gt;





</system.serviceModel> Read more of this post

Drop database Error: ALTER DATABASE failed because a lock could not be placed on database

I was trying to drop a database from my development environment. But I was getting the following error.


Alter failed for Database ‘AdventureWorks’. (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1447.4+((KJ_RTM).100213-0103+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Alter+Database&LinkId=20476


An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)


ALTER DATABASE failed because a lock could not be placed on database ‘AdventureWorks’. Try again later.
ALTER DATABASE statement failed. (Microsoft SQL Server, Error: 5061)



This means there are some process already locked the database and you cannot drop it before that process release the lock.

To see which process is locking the database. Run the following  command

exec sp_who2

This will list all processes and uid that has a lock on databases.

In my case, I can see PID 52 was holding the AdventureWorks database. As it is my Dev environment and i know that nothing will harm any other system if I kill this process and eventually drop the database, I issued the following command.

kill <PID> 

KILL 52 //In my case 

After you have kill all the processes that are holding a lock in your database, you will be able to drop the database.



WCF Binding Comparison and Supported Features

Following table gives an overview of the different bindings in WCF service, a comparison and supported features.

Name Transport Message Encoding Message Version Interop Security Session TransactionFlow Duplex
HTTP/HTTPS Text SOAP 1.1 Basic Profile 1.1 None, Transport, Message, Mixed N
(wsHttpBinding, webHttpBinding)
[http, https]
HTTP/HTTPS Text, MTOM SOAP 1.2, WS-A 1.0 WS None, Transport,Message, Mixed None, Transport, Reliable Session No, Yes(WS-AT) N
[http, https]
HTTP/HTTPS Text, MTOM SOAP 1.2, WS-A 1.0 WS-Security,WS-Trust,WS-SC, WS-SP None, Transport,Message, Mixed None, Transport, Reliable Session No, Yes(WS-AT) N
HTTP Text, MTOM SOAP 1.2, WS-A 1.0 WS None,Message Reliable Session No, Yes(WS-AT) Y
[http, https]
HTTP/HTTPS Text, MTOM SOAP 1.2, WS-A 1.0 WS-Federation None,Message, Mixed None, Reliable Session No, Yes(WS-AT) N
[http, https]
HTTP/HTTPS Text, MTOM SOAP 1.2, WS-A 1.0 WS-Federation None,Message, Mixed None, Reliable Session No, Yes(WS-AT) N
TCP Binary SOAP 1.2 .Net None,Transport, Message, Mixed Transport, Reliable Session No, Yes(OleTx) Y
P2P Binary SOAP 1.2 Peer None,Transport, Message, Mixed Y
Named Pipe (IPC) Binary SOAP 1.2 .Net None,Transport None,Transport No, Yes(OleTx) Y
MSMQ Binary SOAP 1.2 .Net None,Transport, Message, Both No, Yes(OleTx) N
MSMQ * MSMQ None,Transport No, Yes N

Configuring certificates in IIS Express and Windows XP development server

We are on the age of Windows 7 and windows 2008 Server. Configuring certificates on Windows 7 and IIS Development server is lot easier. However, in corporate environment you often hit some limitation. You dont have the obvious choice of choosing your own technology stack. You are often governed by corporate policy and their computer licensing. That restricts your development environment as well. IIS Express gives you a nice compressed version of IIS for development. However it is often a tedious task to configure this when you are working on WINDOWS XP. When you are ask to enable security features in this development environment your task become for difficult. I have found many difficulties in configuring certificates in IIS Express in WINDOWS XP environment. So i thought of sharing this. You need to follow the following steps to configure certificates on IIS Express in WINDOWS XP operating system.

Step 1 : Make a directory in your computer. I Created c:\MyCerts
Step 2: Go to c:\MyCerts

Step 3: Clean up any exiting certificates from your MMC Certification Snap in. Execute the following two commands.
certmgr -del -r localmachine -s TrustedPeople -c -n myCert

certmgr -del -r localmachine -s My -c -n myCert

Above two commands will remove any certificates name ‘mycert’ from localmachine and trustedPeople certificate repository. We are going to create a new certificate name ‘mycert’ in the following steps and deploy it.

Step 4: Generate a new certificate
makecert.exe -sr localmachine -ss My -pe -sky exchange -n “CN=mycert” mycert.cer

Step 5: Import it to MMC Certificate Snap in using the following commands.
certmgr.exe -add -r localmachine -s My -c -n mycert -r localmachine -s TrustedPeople

This will import the certificate to you local computer. Check for mycert in both Personal\Certificates and Trusted People\Certificates. To check that go to MMC snap-in and load certificates.

Step 6: If you are using WIF for Security Token Service (STS), you need to update the thumbnail of the certificate in WebHost\Web.config file. To get the thumbnail of the certificate, go to Personal>Certificates. Double click on “MyCert”. Go to details. Click on Thumbnail. Write down the thumbnail .

Step 7:
Open webhost\web.config file … update the thumbnail … Remember to remove the space in between.

Step 8:
As, IIS Express on XP run under the context of localuser, we need to grant access to localuser on certificate machine key to.
Usually machine key are stored on the following location
C:\Documents and Settings\All Users\Application Data\Microsoft\Crypto\RSA\MachineKeys
Select the appropriate MachineKey (In my case, I don’t know which one was it so I grant access on both of the file).
Select the file. Right click à go to Properties -> go to security. -> Click add …
Grant Full control to ‘ASPNET’ and Your Log In account.
IIS-Express run in as a local account in your Windows XP. So you need to provide appropriate permission to your local account to access the machine keys.