function for spliting a string and return a table in SQL Server

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[unqSplit](@String nvarchar(4000), @Delimiter char(1))
RETURNS @Results TABLE (value nvarchar(4000))
AS

  --this function takes two parameters; the first is the delimited string, the second is the delimiter
    BEGIN
    DECLARE @INDEX INT
    DECLARE @SLICE nvarchar(4000)
    -- HAVE TO SET TO 1 SO IT DOESNT EQUAL Z
    --     ERO FIRST TIME IN LOOP
    SELECT @INDEX = 1
  
    IF @String IS NULL RETURN
    WHILE @INDEX !=0


        BEGIN    
            -- GET THE INDEX OF THE FIRST OCCURENCE OF THE SPLIT CHARACTER
            SELECT @INDEX = CHARINDEX(@Delimiter,@STRING)
            -- NOW PUSH EVERYTHING TO THE LEFT OF IT INTO THE SLICE VARIABLE
            IF @INDEX !=0
                SELECT @SLICE = LEFT(@STRING,@INDEX - 1)
            ELSE
                SELECT @SLICE = @STRING
            -- PUT THE ITEM INTO THE RESULTS SET
            INSERT INTO @Results(value) VALUES(@SLICE)
            -- CHOP THE ITEM REMOVED OFF THE MAIN STRING
            SELECT @STRING = RIGHT(@STRING,LEN(@STRING) - @INDEX)
            -- BREAK OUT IF WE ARE DONE
            IF LEN(@STRING) = 0 BREAK
    END

    RETURN
END

How to use the functions.

Declare @tempString nvarchar(max)
set @tempString = '''43C9D0A5-5FB7-45B9-BC2D-1D942B77A0CE'',''6c73f22f-7599-4e7a-bbdc-e71fe6e5f0f8'',''40d76edf-47d7-4e51-88fa-8bc3ae350d1c'',''3eece540-673e-42be-a123-17882e4e527f'''

-- '''6c73f22f-7599-4e7a-bbdc-e71fe6e5f0f8'',''40d76edf-47d7-4e51-88fa-8bc3ae350d1c'',''3eece540-673e-42be-a123-17882e4e527f'''


declare @tempTable table ( cid uniqueidentifier)
insert into @tempTable 
select convert(uniqueidentifier,  SUBSTRING(value,2,(LEN(value))))  from  dbo.unqSplit(@tempString, ',')

select * from  @tempTable

Country wise TimeZones SQL DB

This time zone list use only for .Net Users.. Because .Net has inbuilt class for converting date-time into UTC and UTC to specific timezone. For that, ‘TimeZone Unique Name’ should be needed which should match with Windows System TimeZone.

You can download sql script from here.

404 error WCF on server

Add below lines undser System.Server / handler section.

<add name="svc-ISAPI-2.0" path="*.svc" verb="*" modules="IsapiModule" scriptProcessor="%SystemRoot%\Microsoft.NET\Framework\v2.0.50727\aspnet_isapi.dll" resourceType="Unspecified" preCondition="classicMode,runtimeVersionv2.0,bitness32" />
     <add name="svc-Integrated" path="*.svc" verb="*" type="System.ServiceModel.Activation.HttpHandler, System.ServiceModel, Version=3.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" resourceType="Unspecified" preCondition="integratedMode" />

   

Import SSL, Auto redirect http to https, Custom Error Page lock violation

Import SSL in IIS 7

For importing SSL certificate, the certificate should have in PKCS #12 format (.pfx). If you have SSL certificate, its not in PKCS #12 format and you have Certificate and Private key you can generate the .PFX SSL certificate using OPENSSL tool or online by click here.

Once the .PFX certificate generated, go to IIS Manager by type ‘inetmgr’ in windows run. Select main not in left pane. And double click on “server certificates” from the middle pane. Then click on ‘import’ from the right pane. A dialog box will open select the SSL certificate which you have generated in .PFX format and enter the password which you had given at the time of generating the PFX certificate. Click on Ok button. Now, you can assign the certificate to your domain, using Binding.

Auto redirect http to https in IIS 7
Method 1 – Using Microsoft URL Rewrite Module

For this method of redirecting from HTTP to HTTPS, you will need to do the following;

1. Install the Microsoft URL Rewrite Module
2. Install your SSL certificate in IIS 7 and bind it to your website
3. Make sure Require SSL is NOT checked under SSL Settings for your website
4. Copy and paste the following code between the and tags in your web.config file in your website root directory.

<rule name="HTTP to HTTPS redirect" stopProcessing="true">
  <match url="(.*)" />
    <conditions>
      <add input="{HTTPS}" pattern="off" ignoreCase="true" />
    </conditions>
  <action type="Redirect" redirectType="Found" url="https://{HTTP_HOST}/{R:1}" />
</rule>

5. Test the site by going to http://www.yoursite.com and making sure it redirects

Method 2 – Setting up a Custom Error Page
The second method of setting up an IIS7 redirect HTTP to HTTPS is to Require SSL on the site or part of the site and set up a custom 403.4 error page. To do this, just following these steps:

1. Install your SSL certificate in IIS 7 and bind it to your website
2. In IIS, click on the site name, and go to the SSL Settings section
3. Check Require SSL checked under SSL settings of your website
4. After doing this, users will normally receive this error 403.
5. Create a new text file and paste the following into it:

<html>
<head><title>Redirecting...</title></head>
<script language="JavaScript">
function redirectHttpToHttps()
{
    var httpURL= window.location.hostname + window.location.pathname + window.location.search;
    var httpsURL= "https://" + httpURL;
    window.location = httpsURL;
}
redirectHttpToHttps();
</script>
<body>
</body>
</html>

6. Save the file as redirectToHttps.htm in your C:\Inetpub directory
7. Back in IIS, click on the site name and double-click the Error Pages option
8. Click Add… and enter 403.4 as the Status code. Browse for the redirectToHttps.htm file you just created and click OK
9. Select the error code and press Edit Feature Settings…
10. Click the Custom error pages option and again browse for the redirectToHttps.htm file
11. Test the site by going to http://www.yoursite.com and making sure it redirects

Custom Error Page lock violation

If your IIS 7 web server doesn’t already have it, install the IIS 7.0 Administration Pack from Microsoft.

Navigate to the root web server name in IIS, and open the Configuration Editor (part of the Administration Pack). Change the dropdown to system.webServer/httpErrors, right-click on defaultPath, and choose ‘defaultPath’ Attribute -> Unlock Attribute.

Then try to change the custom error handler page again. Navigate to your site, open Error Pages under the IIS group, click Edit Feature Settings on the right, select Custom error pages and finally, put in your path for the default page.

The Manual Way

I know you can accomplish all this by direct editing in notepad of the appropriate config file on the web server. And that may be required for your particular web hosting environment or company production web server change protocols. But why make it complicated if it doesn’t need to be? Plus, this way you can do it in the GUI, and compare the before and after to see what changes you truly have to make. But if you MUST do it manually, then:

Open the file %windir%\System32\inetsrv\config\applicationHost.config in Notepad. Run Notepad as administrator if you’re having problems.

You’ll see something like this:

<httpErrors lockAttributes="allowAbsolutePathsWhenDelegated,defaultPath">

Remove the ,defaultPath section and save.

You will be able to make the changes you need.

Windows store privacy statement

I got the error from Microsoft while I was submit the app. The error is, “The app has declared access to network capabilities and no privacy statement was provided in the Windows Settings Charm.”

The sad part is that this is so easy to fix. First you will need to create a new directory Privacy and Page Control under it Privacy, it lives my pages/ directory. The page control Privacy creates the privacy.html, privacy.js and privacy.css files under Privacy folder.

My Privacy.Html Code is here

http://www.codeproject.com/Articles/292336/Silverlight-A-Beginners-Guide-for-Advanced-Develop<!DOCTYPE html>
<html>
<head>
    <meta charset="utf-8" />
    <title>privacy</title>

    <!-- WinJS references -->
    <link href="//Microsoft.WinJS.1.0/css/ui-dark.css" rel="stylesheet" />
    <script src="//Microsoft.WinJS.1.0/js/base.js"></script>
    <script src="//Microsoft.WinJS.1.0/js/ui.js"></script>

    <link href="privacy.css" rel="stylesheet" />
    <script src="privacy.js"></script>
</head>
<body>
    <!-- BEGINSETTINGSFLYOUT -->
    <div data-win-control="WinJS.UI.SettingsFlyout"
        id="privacyPolicy"
        data-win-options="{settingsCommandId:'privacyPolicy', width:'narrow'}">

        <div class="win-ui-dark win-header">
            <button type="button" onclick="WinJS.UI.SettingsFlyout.show()" class="win-backbutton"></button>
            <div class="win-label">Privacy Policy</div>
        </div>
        <div class="win-content">
            <p>
                This application does not collect any information about users. This
application also does not collect any username or password that users enter to authenticate.
            </p>
            <p>This privacy policy is subject to change without notice and was last updated on 22-July-2013. If you have any questions feel free to contact us directly at <a href="mailto:sales@eplannerpro.com">sales@eplannerpro.com</a>.</p>
        </div>
    </div>
    <!-- ENDSETTINGSFLYOUT -->
</body>
</html>

Now you just need a quick way to add this to your app. Add the following code in your default.js file:

 app.onsettings = function (args) {


        args.detail.applicationcommands = {
            "privacyPolicy": {
                title: "Privacy Policy", href: "/Pages/Privacy/privacy.html"
            }
        };
        WinJS.UI.SettingsFlyout.populateSettings(args);
    };

Now, run the app. Go to setting pane, click on Privacy, you will show the privacy statement.

How to add reference System.Web.Optimization

The Microsoft.Web.Optimization package is now obsolete. With the final release of ASP.NET (MVC) 4 you should install the Microsoft ASP.NET Web Optimization Framework:

Install the package from nuget:

Right click on References, Select Manage Nuget Packages. Type Microsoft.AspNet.Web.Optimization in search box and press enter you will get the package. Click on install.

Create and configure bundle(s) in App_Start\BundleConfig.cs:

public class BundleConfig
{
    public static void RegisterBundles(BundleCollection bundles) {
        bundles.Add(new ScriptBundle("~/Scripts/jquery").Include(
            "~/Scripts/Lib/jquery/jquery-{version}.js",
            "~/Scripts/Lib/jquery/jquery.*",
            "~/Scripts/Lib/jquery/jquery-ui-{version}.js")
        );

        bundles.Add(new ScriptBundle("~/Scripts/knockout").Include(
             "~/Scripts/Lib/knockout/knockout-{version}.js",
             "~/Scripts/Lib/knockout/knockout-deferred-updates.js")
        );
    }
}

Call the RegisterBundles() function from Application_Start() in your global.asax.cs:

using System.Web.Optimization;

protected void Application_Start() {
     ...
     BundleConfig.RegisterBundles(BundleTable.Bundles);
     ...
}

In your view.cshtml include the Optimization namespace and render the bundle(s):

@using System.Web.Optimization

@Scripts.Render("~/Scripts/jquery")
@Scripts.Render("~/Scripts/knockout")

HTTP Error 405.0 – Method Not Allowed error while trying to make a DELETE request to a .ashx

Here’s the final web.config section. The thing I needed to add I was missing was changing the staticFileHandler from using “all verbs” to all of them spelled out.

 <?xml version="1.0" encoding="utf-8"?>
<configuration>
  <appSettings>
    <add key="StorageRoot" value="C:\temp\"/>
  </appSettings>
  <system.web>
    <compilation debug="true" targetFramework="4.0" />
    <httpRuntime maxRequestLength="2147480000" executionTimeout="3600"/>
    <pages controlRenderingCompatibilityVersion="3.5" clientIDMode="AutoID"/>
   
  </system.web>
  <system.webServer>
   
    <modules>
      <remove name="WebDAVModule" />
    </modules>
    <handlers accessPolicy="Read, Write, Execute, Script">
      <remove name="StaticFile" />
      <remove name="SimpleHandlerFactory-ISAPI-2.0" />
      <remove name="WebDAV" />
      <remove name="SimpleHandlerFactory-Integrated-4.0" />
      <remove name="SimpleHandlerFactory-Integrated" />
      <add name="SimpleHandlerFactory-Integrated" path="*.ashx" verb="GET,HEAD,POST,DEBUG,PUT,DELETE" type="System.Web.UI.SimpleHandlerFactory" resourceType="Unspecified" requireAccess="Write" preCondition="integratedMode" />
      <add name="SimpleHandlerFactory-Integrated-4.0" path="*.ashx" verb="GET,HEAD,POST,DEBUG,PUT,DELETE" type="System.Web.UI.SimpleHandlerFactory" resourceType="Unspecified" requireAccess="Write" preCondition="integratedMode,runtimeVersionv4.0" />
      <add name="SimpleHandlerFactory-ISAPI-2.0" path="*.ashx" verb="GET,HEAD,POST,DEBUG,PUT,DELETE" modules="IsapiModule" scriptProcessor="%windir%\Microsoft.NET\Framework\v2.0.50727\aspnet_isapi.dll" resourceType="Unspecified" requireAccess="Script" preCondition="classicMode,runtimeVersionv2.0,bitness32" responseBufferLimit="0" />
      <add name="StaticFile" path="*" verb="GET,HEAD,POST,DEBUG,PUT,DELETE" modules="StaticFileModule,DefaultDocumentModule,DirectoryListingModule" resourceType="Either" requireAccess="Read" />
    </handlers>
    <security>
      <requestFiltering>
        <requestLimits maxAllowedContentLength="2147480000"/>
      </requestFiltering>
      <authorization>
        <remove users="*" roles="" verbs="" />
        <add accessType="Allow" users="*" verbs="GET,HEAD,POST,PUT,DELETE,DEBUG" />
      </authorization>
    </security>
    <tracing>
      <traceFailedRequests>
        <remove path="*" />
        <add path="*">
          <traceAreas>
            <add provider="ASP" verbosity="Verbose" />
            <add provider="ASPNET" areas="Infrastructure,Module,Page,AppServices" verbosity="Verbose" />
            <add provider="ISAPI Extension" verbosity="Verbose" />
            <add provider="WWW Server" areas="Authentication,Security,Filter,StaticFile,CGI,Compression,Cache,RequestNotifications,Module,FastCGI" verbosity="Verbose" />
          </traceAreas>
          <failureDefinitions statusCodes="405" />
        </add>
      </traceFailedRequests>
    </tracing>
  </system.webServer>
</configuration>