Convert Month Number to Month Name in SQL Server

One of my colleague asked me a simplest way to convert Month Number to Month Name. Here’s the simplest way in my opinion :

DECLARE @MonthNo smallint
SET @MonthNo = 11
SELECT DateName(mm,DATEADD(mm,@MonthNo ,-1)) as [MonthName]

There are alternate ways also to achieve this.Share your ideas.

Reference: Narendra Singh (http://blog.sikarnarender.com)

What is the difference between ‘classic’ and ‘integrated’ pipeline mode in IIS7?

Classic mode (the only mode in IIS6 and below) is a mode where IIS only works with ISAPI extensions and ISAPI filters directly. In fact, in this mode, ASP.NET is just an ISAPI extension (aspnet_isapi.dll) and an ISAPI filter (aspnet_filter.dll). IIS just treats ASP.NET as an external plugin implemented in ISAPI and works with it like a black box (and only when it’s needs to give out the request to ASP.NET). In this mode, ASP.NET is not much different from PHP or other technologies for IIS.

Integrated mode, on the other hand, is a new mode in IIS7 where IIS pipeline is tightly integrated (i.e. is just the same) as ASP.NET request pipeline. ASP.NET can see every request it wants to and manipulate things along the way. ASP.NET is no longer treated as an external plugin. It’s completely blended and integrated in IIS. In this mode, ASP.NET HttpModules basically have nearly as much power as an ISAPI filter would have had and ASP.NET HttpHandlers can have nearly equivalent capability as an ISAPI extension could have. In this mode, ASP.NET is basically a part of IIS.

Reference: Narendra Singh (http://blog.sikarnarender.com)

Comparision between IIS6.0, IIS 7.0 & IIS 7.5

IIS 7.0 has a modular architecture. Modules, also called extensions, can be added or removed individually so that only modules required for specific functionality have to be installed. IIS 7 includes native modules as part of the full installation. These modules are individual features that the server uses to process requests and include the following:

  • Security modules – Used to perform many tasks related to security in the request-processing pipeline, such as specifying authentication schemes, performing URL authorization, and filtering requests.
  • Content modules – Used to perform tasks related to content in the request-processing pipeline, such as processing requests for static files, returning a default page when a client does not specify a resource in a request, and listing the contents of a directory.
  • Compression modules – Used to perform tasks related to compression in the request-processing pipeline, such as compressing responses, applying Gzip compression transfer coding to responses, and performing pre-compression of static content.
  • Caching modules – Used to perform tasks related to caching in the request-processing pipeline, such as storing processed information in memory on the server and using cached content in subsequent requests for the same resource.
  • Logging and Diagnostics modules – Used to perform tasks related to logging and diagnostics in the request-processing pipeline, such as passing information and processing status to HTTP. sys for logging, reporting events, and tracking requests currently executing in worker processes.
  • IIS 7.5 includes the following additional or enhanced security features:

  • Client certificate mapping
  • IP security
  • Request filtering
  • URL authorization
  • Reference: Narendra Singh (http://blog.sikarnarender.com)

    Delete duplicate records using CTE, Sql Server

    You may use following query :

    WITH CTE(ORDER_ID,REQUEST_ID,ROW_No)
    as
    (
    SELECT ORDER_ID,REQUEST_ID,ROW_NUMBER() OVER(PARTITION BY ORDER_ID ORDER BY ORDER_ID) AS ROW_No
    FROM CRM.ORDER_COMPLIANCE WHERE ORDER_ID=397
    )
    SELECT ORDER_ID,REQUEST_ID,ROW_No FROM CTE
    –DELETE FROM CTE WHERE ROW_NO>1

    There are alternate ways also to achieve this.Share your ideas.

    Reference: Narendra Singh (http://blog.sikarnarender.com)

    How to get difference in year month and days from 2 date values ?

    Following query will give result as (X Years X Months X Days) :

    Declare @DOB DateTime
    DECLARE @yy INT
    DECLARE @mm INT
    DECLARE @dd INT

    SET @DOB=’2011-10-11 00:00:00.000′
    SET @yy = DATEDIFF(mm, @DOB, GETDATE())/12
    SET @mm = DATEDIFF(mm, @DOB, GETDATE())%12 – 1
    SET @dd = ABS(DATEDIFF(dd, DATEADD(mm,@mm , DATEADD(yy, @yy, @DOB)), GETDATE()))
    SELECT Convert(varchar(10),@yy) + ‘ Years ‘ + Convert(varchar(10),@mm) + ‘ Months ‘ + Convert(varchar(10),@dd) + ‘ Days ‘

    There are alternate ways also to achieve this.Share your ideas.

    Reference: Narendra Singh (http://blog.sikarnarender.com)

    WCF Interview Questions and Answers

    Disclaimer : The below list of questions and answers is not written by me. I collected these from different sites and blogs.

    Q1. What is WCF?
    WCF stands for Windows Communication Foundation. It is a Software development kit for developing services on Windows. WCF is introduced in .NET 3.0. in the System.ServiceModel namespace. WCF is based on basic concepts of Service oriented architecture (SOA)

    Q2. What is endpoint in WCF service?
    The endpoint is an Interface which defines how a client will communicate with the service. It consists of three main points: Address,Binding and Contract.

    Q3. Explain Address,Binding and contract for a WCF Service?
    Address:Address defines where the service resides.
    Binding:Binding defines how to communicate with the service.
    Contract:Contract defines what is done by the service.

    Q4. What are the various address format in WCF?
    a)HTTP Address Format:–> http://localhost:
    b)TCP Address Format:–> net.tcp://localhost:
    c)MSMQ Address Format:–> net.msmq://localhost:

    Q5. What are the types of binding available in WCF?
    A binding is identified by the transport it supports and the encoding it uses. Transport may be HTTP,TCP etc and encoding may be text,binary etc. The popular types of binding may be as below:

    WCF supports nine types of bindings.
    Basic binding
    Offered by the BasicHttpBinding class, this is designed to expose a WCF service as a legacy ASMX web service, so that old clients can work with new services. When used by the client, this binding enables new WCF clients to work with old ASMX services.

    TCP binding
    Offered by the NetTcpBinding class, this uses TCP for cross-machine communication on the intranet. It supports a variety of features, including reliability, transactions, and security, and is optimized for WCF-to-WCF communication. As a result, it requires both the client and the service to use WCF.

    Peer network binding
    Offered by the NetPeerTcpBinding class, this uses peer networking as a transport. The peer network-enabled client and services all subscribe to the same grid and broadcast messages to it.

    IPC binding
    Offered by the NetNamedPipeBinding class, this uses named pipes as a transport for same-machine communication. It is the most secure binding since it cannot accept calls from outside the machine and it supports a variety of features similar to the TCP binding.

    Web Service (WS) binding
    Offered by the WSHttpBinding class, this uses HTTP or HTTPS for transport, and is designed to offer a variety of features such as reliability, transactions, and security over the Internet.

    Federated WS binding
    Offered by the WSFederationHttpBinding class, this is a specialization of the WS binding, offering support for federated security.

    Duplex WS binding
    Offered by the WSDualHttpBinding class, this is similar to the WS binding except it also supports bidirectional communication from the service to the client.

    MSMQ binding
    Offered by the NetMsmqBinding class, this uses MSMQ for transport and is designed to offer support for disconnected queued calls.

    MSMQ integration binding
    Offered by the MsmqIntegrationBinding class, this converts WCF messages to and from MSMQ messages, and is designed to interoperate with legacy MSMQ clients.
    For WCF binding comparison, see http://www.pluralsight.com/community/blogs/aaron/archive/2007/03/22/46560.aspx

    Q6. What are the types of contract available in WCF?
    The main contracts are:
    a)Service Contract:Describes what operations the client can perform.
    b)Operation Contract : defines the method inside Interface of Service.
    c)Data Contract:Defines what data types are passed
    d)Message Contract:Defines whether a service can interact directly with messages

    Q7. What are the various ways of hosting a WCF Service?
    a)IIS b)Self Hosting c)WAS (Windows Activation Service)

    Q8. WWhat is the proxy for WCF Service?
    A proxy is a class by which a service client can Interact with the service.
    By the use of proxy in the client application we are able to call the different methods exposed by the service.

    Q9. How can we create Proxy for the WCF Service?
    We can create proxy using the tool svcutil.exe after creating the service.
    We can use the following command at command line.
    svcutil.exe *.wsdl *.xsd /language:C# /out:SampleProxy.cs /config:app.config

    Q10.What is the difference between WCF Service and Web Service?
    Web service is a part of WCF. WCF offers much more flexibility and portability to develop a service when comparing to web service. The following point provides the detailed differences between them :
    1. Hosting : Webservices can be host in IIS, whereas WCF services can be hosted in IIS, Windows Activation Service, Self Hosting.
    2. Encoding : Webservices uses XML 1.0, MTOM(Message Transmission Optimization Mechanism), DIME, Custom. WCF uses XML 1.0, MTOM, Binary, Custom.
    3. Transports : Webservices can be accessed using HTTP, TCP, Custom. WCF services can be accessed using HTTP, TCP, Named Pipes, MSMQ, P2P, Custom.
    4. Protocols : Webservices uses Security porotocols only. Whereas WCF services uses Security, Reliable Messaging, Transactions protocols.

    Q11.What is DataContract and ServiceContract?Explain
    Data represented by creating DataContract which expose the data which will be transefered /consumend from the serive to its clients.
    **Operations which is the functions provided by this service.
    To write an operation on WCF,you have to write it as an interface,This interface contains the “Signature” of the methods tagged by ServiceContract attribute,and all methods signature will be impelemtned on this interface tagged with OperationContract attribute.To implement these serivce contract you have to create a class which implement the interface and the actual implementation will be on that class.

    How to enable remote connection on Sql Server 2008

    To enable remote connections on the instance of SQL Server 2008, use the SQL Server Configuration Manager under configuration Tools. Go to Start > All Programs > Microsoft Sql Server 2008 > Configuration Tools > Sql Server Configuration Manager.

    • Start Sql Server Browser service, if it is not started. You can set its start mode Automatic using Right click on Sql Server Browser > Properties > Services > Start Mode.
    • Under Sql Server Network Configuration, select your Sql Server Instance and enable the TCP/IP protocol.

    For Step-by-Step information with screen shots follow the below links:
    1. http://mushtaqnaik.blogspot.in/2010/12/how-to-enable-remote-connection-on-sql.html
    2. http://blogs.msdn.com/b/walzenbach/archive/2010/04/14/how-to-enable-remote-connections-in-sql-server-2008.aspx

    Suggested links:
    Enable Remote Connection on Sql Server 2005

    Formatting Numbers in ASP.Net

    Formatting is the process of converting an instance of a class, structure, or enumeration value to its string representation. Anytime you display a DateTime or Numeric variables in an ASP.NET page, you are formatting that variable from its native type into some sort of string representation. How a DateTime or Numeric variable is formatted depends on the culture settings and the format string. Because dates and numeric values are formatted differently across cultures, the .NET Framework bases its formatting on the specified culture settings. By default, the formatting routines use the culture settings defined on the web server, but you can indicate that a particular culture be used anytime you format. In addition to the culture settings, formatting is also affected by a format string, which spells out the formatting details to apply.
    There are a couple of different ways to format a DateTime or numeric variable. The first way involves using the ToString method, which has a number of overrides. Formatting can also be performed using the String.Format method. The String.Format method accepts as input a format string that contains a number of placeholders, followed by the values to plug into those placeholders. Placeholders take the form {0}, {1}, and so on, and may optional include a format string like so: {0:formatString}.The following code snippet shows using the String.Format method.

    Dim CustName As String = “Narendra”
    Dim subtotal as Decimal = 245.13
    Dim output As String = String.Format(“{0}, your order subtotal is {1:c}.”, CustName, subtotal)

    Standard Numeric Format String
    Standard numeric format strings are used to format common numeric types. A standard numeric format string takes the form Axx, where A is an alphabetic character called the format specifier, and xx is an optional integer called the precision specifier. The precision specifier ranges from 0 to 99 and affects the number of digits in the result. Any numeric format string that contains more than one alphabetic character, including white space, is interpreted as a custom numeric format string.

    Format Name Code snippet Output
    1. Currency(“C” or “c”)
    Result : A currency value.
    Supported by : All numeric types.
    Precision Specifier : Number of decimal digits.
    123.ToString(“C”, New CultureInfo(“en-US”))
    3.14159.ToString(“C2”, New CultureInfo(“en-US”))
    $123.00
    $3.14
    2. Decimal(“D” or “d”)
    Result : Integer digits with optional negative sign.
    Supported by : Integral types only.
    Precision Specifier : Minimum number of digits.
    1234.ToString(“D”)
    1234.ToString(“D6”)
    1234
    001234
    3. Exponential(“E” or “e”)
    Result : Exponential notation.
    Supported by : All numeric types.
    Precision Specifier : Number of decimal digits.
    1052.0329112756.ToString(“E”, New CultureInfo(“en-US”))
    1052.0329112756.ToString(“E2”, New CultureInfo(“en-US”))
    1.052033E+003
    1.05E+003
    4. Fixed-point(“F” or “f”)
    Result : Integral and decimal digits with optional negative sign.
    Supported by : All numeric types.
    Precision Specifier : Number of decimal digits.
    1234.567.ToString(“F”, New CultureInfo(“en-US”))
    1234.567.ToString(“F4”, New CultureInfo(“en-US”))
    1234.57
    1234.5670
    5. General(“G” or “g”)
    Result : The most compact of either fixed-point or scientific notation.
    Supported by : All numeric types.
    Precision Specifier : Number of significant digits.
    123.4546.ToString(“G4”, New CultureInfo(“en-US”)) 123.5
    6. Number(“N” or “n”)
    Result : Integral and decimal digits, group separators, and a decimal separator with optional negative sign.
    Supported by : All numeric types.
    Precision Specifier : Desired number of decimal places.
    1234.56712.ToString(“N”, New CultureInfo(“en-US”))
    1234.56712.ToString(“N3”, New CultureInfo(“en-US”))
    1,234.57
    1,234.567
    7. Percent(“P” or “p”)
    Result : Number multiplied by 100 and displayed with a percent symbol.
    Supported by : All numeric types.
    Precision Specifier : Desired number of decimal places.
    1.ToString(“P”, New CultureInfo(“en-US”))
    12.56712.ToString(“P2”, New CultureInfo(“en-US”))
    100.00 %
    1,256.71 %
    8. Round-trip(“R” or “r”)
    Result : A string that can round-trip to an identical number.The round-trip (“R”) format specifier guarantees that a numeric value that is converted to a string will be parsed back into the same numeric value.
    Supported by : Single, Double, and BigInteger.
    Precision Specifier : Ignored.
    3.14159.ToString(“R”, New CultureInfo(“en-US”)) 3.14159
    9. Hexadecimal(“X” or “x”)
    Result : A hexadecimal string.
    Supported by : Integral types only.
    Precision Specifier : Number of digits in the result string.
    255.ToString(“X”, New CultureInfo(“en-US”))
    255.ToString(“X4”, New CultureInfo(“en-US”))
    FF
    00FF

    Custom Numeric Format String
    If the standard numeric format specifiers do not provide the type of formatting you require, you can use custom format strings to further enhance string output. A standard format string consists of a single alphabetic character optionally followed by a sequence of digits that form a value between 0 and 99; all other format strings are custom format strings.
    The following table shows the characters you can use to create custom numeric format strings and their definitions.

    Format Name Code snippet Output
    1. Zero placeholder(0)
    Description : If the value being formatted has a digit in the position where the
    ‘0’ appears in the format string, then that digit is copied to the result string.
    The position of the leftmost ‘0’ before the decimal point and the rightmost ‘0’
    after the decimal point determines the range of digits that are always present in
    the result string.
    3.14.ToString(“00.0000”, New CultureInfo(“en-US”))
    123.45678.ToString(“00.0000”, New CultureInfo(“en-US”))
    03.1400
    123.4568
    2. Digit placeholder(#)
    Description : If the value being formatted has a digit in the position where the
    ‘#’ appears in the format string, then that digit is copied to the result string.
    Otherwise, nothing is stored in that position in the result string.
    3.14.ToString(“#0.000#”, New CultureInfo(“en-US”))
    123.45678.ToString(“#0.000#”, New CultureInfo(“en-US”))
    3.140
    123.4568
    3. Decimal point(.)
    Description : The first ‘.’ character in the format string determines the location
    of the decimal separator in the formatted value; any additional ‘.’ characters are
    ignored.
    123.446.ToString(“#####.#”, New CultureInfo(“en-US”))
    12345.6.ToString(“####.##”, New CultureInfo(“en-US”))
    123.4
    12345.6
    4. Thousand separator(,)
    Description : If the format string contains a ‘,’ character between two placeholders
    (0 or #), then the output will have thousand separators inserted between each group
    of three digits to the left of the decimal separator.
    123456.ToString(“#,000.00”, New CultureInfo(“en-US”)) 123,456.0
    5. Number scaling(,)
    Description : If the format string contains one or more ‘,’ characters immediately
    to the left of the decimal point, then the number will be divided by the number
    of ‘,’ characters multiplied by 1000 before it is formatted.
    2000000.ToString(“0,,”, New CultureInfo(“en-US”))
    2000000.ToString(“0,”, New CultureInfo(“en-US”))
    2
    2000
    6. Percentage placeholder(%)
    Description : The presence of a ‘%’ character in a format string causes a number
    to be multiplied by 100 before it is formatted. The appropriate symbol is inserted
    in the number itself at the location where the ‘%’ appears in the format string.
    0.086.ToString(“#0.##%”, New CultureInfo(“en-US”))
    0.086.ToString(“#0%.##”, New CultureInfo(“en-US”))
    8.6%
    8%.6
    7. Scientific notation(E0,E+0,E-0,e0,e+0,e-0)
    Description : If any of the strings “E”, “E+”, “E-“, “e”, “e+”, or “e-” are present
    in the format string and are followed immediately by at least one ‘0’ character,
    then the number is formatted using scientific notation with an ‘E’ or ‘e’ inserted
    between the number and the exponent. The number of ‘0’ characters following the
    scientific notation indicator determines the minimum number of digits to output
    for the exponent. The “E+” and “e+” formats indicate that a sign character (plus
    or minus) should always precede the exponent. The “E”, “E-“, “e”, or “e-” formats
    indicate that a sign character should only precede negative exponents.
    86000.ToString(“0.###E+0”, New CultureInfo(“en-US”))
    86000.ToString(“0.###E+000”, New CultureInfo(“en-US”))
    86000.ToString(“0.###E-000”, New CultureInfo(“en-US”))
    8.6E+4
    8.6E+004
    8.6E004
    8. Section separator(;)
    Description : The ‘;’ character is used to separate sections for positive, negative,
    and zero numbers in the format string.
    1234.ToString(“##;(##)”, New CultureInfo(“en-US”))
    -1234.ToString(“##;(##)”, New CultureInfo(“en-US”))
    1234
    (1234)

    Enable Remote Connection on Sql Server 2005

    To enable remote connections on the instance of SQL Server 2005,use the SQL Server 2005 Surface Area Configuration tool. The Surface Area Configuration tool is installed when you install SQL Server 2005.You must enable remote connections for each instance of SQL Server 2005 that you want to connect to from a remote computer. To do this, follow these steps:

    • Click Start, point to Programs, point to Microsoft SQL Server 2005, point to Configuration Tools, and then click SQL Server Surface Area Configuration.
    • On the SQL Server 2005 Surface Area Configuration page, click Surface Area Configuration for Services and Connections.
    • On the Surface Area Configuration for Services and Connections page, expand Database Engine, click Remote Connections, click Local and remote connections, click the appropriate protocol to enable for your environment, and then click Apply.
      Note : Click OK when you receive the following message:
      Changes to Connection Settings will not take effect until you restart the Database Engine service.
    • On the Surface Area Configuration for Services and Connections page, expand Database Engine, click Service, click Stop, wait until the MSSQLSERVER service stops, and then click Start to restart the MSSQLSERVER service.

    Suggested links:
    How to enable remote connection on Sql Server 2008