When the Microsoft SQL Server Import and Export wizard is not enough

When importing data from Microsoft Access to Microsoft SQL Server you can use the Microsoft SQL Server Import and Export wizard, but some times you can not use this wizard because values in a column can’t be casted to the correct format.

 

Like a “Series” column that contains the values (1,2,3 and a ‘ ‘) to a int column. To quickly import the data, I created a temp table, with the exact same columns accept for the “Series” column. Edit the mappings and source table in the import and export wizard.

 

image

 

insert into Postcode (Postcode, Straatnaam, Plaats, RangeStart, RangeEind, PostcodetypeId)
select distinct pt.Postcode, pt.Straatnaam, pt.Plaats, pt.RangeStart, pt.RangeEind,
cast(
    case
        when pt.PostcodetypeId = ' ' then -1
        when pt.PostcodetypeId is null then -1
        when pt.PostcodetypeId = '' then -1
        else pt.PostcodetypeId
    end
as int)
from PostcodeTemp pt

Microsoft SQL Server 2005 Maintenance Plan Wizard

This post describes the way I used the Microsoft SQL Server 2005 Maintenance Plan Wizard.

When none default values are used, they will be listed below.

 

> To create a Microsoft SQL Server 2005 Maintenance Plan follow the steps:

> Start Microsoft SQL Server Management Studio

> On the Connect to Server Dialog:

Server type: Database Engine

ServerName: you’re servername like 127.0.0.1\ONTW

Authentication: Windows Aythentication

Click on Connect

> Expand Mangement in the Object Explorer

> Right click Maintenance Plans

> Choose Maintenance Plan Wizard

> On the Maintenance Plan Wizard dialog,

Enter a name

Enter description

Select “Single schedule for the entire plan or no schedule”

Click on Change … to change the schedule for example:

    Enter a name

    Occurs: Daily

    Occurs once at: 13:00:00

    Click on OK

Click on Next

> On the “Select Maintenance Tasks” dialog

Select “Check Database Integrity”

Select “Reorganize Index”

Select “Rebuild Index”

Select “Update Statistics”

Select “Clean Up History”

Select “Maintenance Cleanup Task”

Click on Next

> On the “Select Maintenance Task Order” dialog

Click on Next

> On the “Define Database Check Integrity Task” dialog

Select you’re database

Click on OK

Click on Next

> On the “Define Reorganize Index Task” dialog

Select you’re database

Click on OK

Click on Next

> On the “Define Rebuild Index Task” dialog

Select you’re database

Click on OK

Click on Next

> On the “Define Update Statistics Task” dialog

Select you’re database

Click on OK

Click on Next

> On the “Define History Cleanup Task” dialog

Click on Next

> On the “Define Maintenance Cleanup Task” dialog

Select Maintenance Plan text reports

Choose a folder (same as the folder in the following dialog)

Click on Next

> On the “Select Report Options” dialog

Write a report to a text file

Click on Next

> On the “Complete the Wizard” dialog

Click on Finish

> On the “Maintenance Plan Wizard Progress” dialog

Click on Close

IIS 6 versus IIS 7 dialog comparison

I was looking for the "Change Execute Permissions" in IIS 7.

In IIS 6 you would go to Web site > Properties > Home Directory Tab:

image

But I could not find it in IIS 7. I found the solution on http://msmvps.com/blogs/bernard/archive/2007/08/12/website-property-page-mapping-between-iis-6-and-iis-7.aspx

Features View pane – Handler Mappings – Edit Feature Permissions…
Check "Read", "Script" and "Execute" in the "Edit Feature Permissions" dialog.

This site gives you a comparison between IIS 6 and IIS 7 dialogs.

A tab that was missing in the comparison was the ASP .NET tab on a virtual directory.
This tab allows you to set the ASP .NET framework version (1.1 or 2.0)

ASP.NETVersionTabIIS6

 

In IIS6 is was misplaced and know it can be correctly found add an application pool in IIS7:
> start > run > inetmgr > servername > Application Pools > right  click applicationpool > Advanced Settings…

AppPoolAdvancedSettingsIIS7

Paging a ASP .NET 2.0 GridView with a LLBLGEN datasource

If you want to allow paging in a ASP .NET 2.0 GridView that uses a LLBLGEN datasource:

– set AllowPaging = True

image

image

– Create the PageIndexChanging event

/// <summary>
/// Change the page of the GridViewKlanten
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void GridViewKlanten_PageIndexChanging(object sender, GridViewPageEventArgs e)
{
    GridViewKlanten.PageIndex = e.NewPageIndex;
    GridviewKlantenBind();
}
       /// <summary>
       /// Get klanten from database and bind to GridView
       /// </summary>
       private void GridviewKlantenBind()
       {
           KlantComponent klantcompent = new KlantComponent(_logger);
           GridViewKlanten.DataSource = klantcompent.GetKlanten();
           GridViewKlanten.DataBind();
       }
 
Don't forget to rebind the GridView or you will not see any change.

Setting the GridView PagerStyle height in ASP .NET 2.0 with C#

When you set the PagerStyle Height property this will no allways set the correct height of the PageStyle.

<asp:GridView ID="Test">
    <PagerStyle  Height="10px" />
</asp:GridView>

To set the correct height use a CSS class:

<asp:GridView ID="Test">
    <PagerStyle  CssClass="PagerStyle" />
</asp:GridView>

In you’re CSS file add:

TR.PagerStyle TD
{
    height: 10px;
    margin: 0px;
    padding-top: 2px;
    padding-bottom: 2px;
}
TR.PagerStyle TD TABLE
{
    margin: 0px;
    padding-top: 2px;
    padding-bottom: 2px;
}

Delete IIS6 or IIS7 virtual directory on a website with hostheader, with C# and NANT

using System;
using System.DirectoryServices;
using System.Globalization;
using Ada.Configurator.Common;
using NAnt.Core.Attributes;
using NAnt.Core;

namespace AdaNantTasks
{
    /// <summary>
    /// Delete an IIS virtual directory on a website with a hostheader.
    /// </summary>
    [TaskName("deleteVirtualDirectoryTask")]
    public class DeleteVirtualDirectoryTask : Task
    {
        public NantLogger logger = new NantLogger();
        public string VirDirSchemaName = "IIsWebVirtualDir";

        private string m_server;
        [TaskAttribute("server", Required = true)]
        [StringValidator(AllowEmpty = false)]
        public string Server
        {
            get { return m_server; }
            set { m_server = value; }
        }

        private string m_virtualDirectoryName;
        [TaskAttribute("virtualDirectoryName", Required = true)]
        [StringValidator(AllowEmpty = false)]
        public string VirtualDirectoryName
        {
            get { return m_virtualDirectoryName; }
            set { m_virtualDirectoryName = value; }
        }

        private string m_websiteName;
        [TaskAttribute("websiteName", Required = true)]
        [StringValidator(AllowEmpty = false)]
        public string WebsiteName
        {
            get { return m_websiteName; }
            set { m_websiteName = value; }
        }

        /// <summary>
        /// Creates website
        /// </summary>
        protected override void ExecuteTask()
        {
            try
            {
                this.DeleteVirtualDirectory();
                Project.Log(Level.Info, string.Format("Virtualdirectory [{0}] created", m_virtualDirectoryName));
            }
            catch (Exception ex)
            {
                logger.Log(this.Project, Level.Error, string.Format("VirtualDirectory [{0}] not created, because an exception occurred [{1}]", m_virtualDirectoryName, ex.ToString()));
                throw new BuildException("Unable to create website", ex);
            }
        }

        /// <summary>
        /// Delete an IIS virtualdirectory
        /// </summary>
        public void DeleteVirtualDirectory()
        {
            if (string.IsNullOrEmpty(m_server)) { throw new Exception("Property [Server] can't be null or empty"); } else { Console.WriteLine(string.Format("DeleteVirtualDirectory: [{0}]", m_server)); }
            if (string.IsNullOrEmpty(m_virtualDirectoryName)) { throw new Exception("Property [VirtualDirectoryName] can't be null or empty"); } else { Console.WriteLine(string.Format("DeleteVirtualDirectory: [{0}]", m_virtualDirectoryName)); }
            if (string.IsNullOrEmpty(m_websiteName)) { throw new Exception("Property [WebsiteName] can't be null or empty"); } else { Console.WriteLine(string.Format("DeleteVirtualDirectory: [{0}]", m_websiteName)); }

            int websiteId = this.GetWebSiteId(m_server, m_websiteName);

            string iisPath = string.Format("IIS://{0}/w3svc/{1}", m_server, websiteId);

            using (DirectoryEntry w3svc = new DirectoryEntry(iisPath))
            {
                w3svc.RefreshCache();

                using (DirectoryEntry folderRoot = w3svc.Children.Find("Root", VirDirSchemaName))
                {
                    folderRoot.RefreshCache();
                    DirectoryEntry virtualDirectory = null;

                    try
                    {
                        folderRoot.Children.Find(m_virtualDirectoryName, VirDirSchemaName);
                        folderRoot.Invoke("AppDelete");
                        folderRoot.Children.Remove(virtualDirectory);
                    }
                    catch
                    {
                        Console.WriteLine("Virtual directory [{0}] already exists or error during delete", m_virtualDirectoryName);
                    }
                }
            }
        }

        /// <summary>
        /// Get website id on websitename
        /// </summary>
        /// <param name="serverName">Name of the IIS server e.g. localhost</param>
        /// <param name="websiteName">Name of the website e.g. test</param>
        /// <returns>
        /// Less the 0, site does not exist
        /// Id of the existing site
        /// </returns>
        public int GetWebSiteId(string serverName, string websiteName)
        {
            if (string.IsNullOrEmpty(serverName)) { throw new Exception("Parameter [serverName] can't be null or empty"); } else { Console.WriteLine(string.Format("GetWebSiteId: [{0}]", serverName)); }
            if (string.IsNullOrEmpty(websiteName)) { throw new Exception("Parameter [websiteName] can't be null or empty"); } else { Console.WriteLine(string.Format("GetWebSiteId: [{0}]", websiteName)); }
            int result = -1;

            using (DirectoryEntry w3svc = new DirectoryEntry(string.Format("IIS://{0}/w3svc", serverName)))
            {
                w3svc.RefreshCache();

                foreach (DirectoryEntry site in w3svc.Children)
                {
                    using (site)
                    {
                        site.RefreshCache();

                        if (site.Properties["ServerComment"] != null)
                        {
                            if (site.Properties["ServerComment"].Value != null)
                            {
                                if (site.Properties["ServerComment"].Value.ToString().Equals(websiteName, StringComparison.OrdinalIgnoreCase))
                                {
                                    result = int.Parse(site.Name);
                                }
                            }
                        }
                    }
                }
            }

            Console.WriteLine(string.Format("GetWebSiteId: [{0}]", result));
            return result;
        }
    }
}

Create IIS6 or IIS7 virtual directory on a website with hostheader, with C# and NANT

using System;
using System.DirectoryServices;
using System.Globalization;
using Ada.Configurator.Common;
using NAnt.Core.Attributes;
using NAnt.Core;

namespace AdaNantTasks
{
    [TaskName("createWebSite")]
    public class CreateWebsiteTask : Task
    {
        public NantLogger logger = new NantLogger();

        private string m_server;
        [TaskAttribute("server", Required = true)]
        [StringValidator(AllowEmpty = false)]
        public string Server
        {
            get { return m_server; }
            set { m_server = value; }
        }

        private string m_websiteName;
        [TaskAttribute("websiteName", Required = true)]
        [StringValidator(AllowEmpty = false)]
        public string WebsiteName
        {
            get { return m_websiteName; }
            set { m_websiteName = value; }
        }

        private string m_binding;
        [TaskAttribute("binding", Required = true)]
        [StringValidator(AllowEmpty = false)]
        public string Binding
        {
            get { return m_binding; }
            set { m_binding = value; }
        }

        private string m_webFolder;
        [TaskAttribute("webFolder", Required = true)]
        [StringValidator(AllowEmpty = false)]
        public string WebFolder
        {
            get { return m_webFolder; }
            set { m_webFolder = value; }
        }

        private string m_appPool;
        [TaskAttribute("appPool", Required = true)]
        [StringValidator(AllowEmpty = false)]
        public string AppPool
        {
            get { return m_appPool; }
            set { m_appPool = value; }
        }

        /// <summary>
        /// Creates website
        /// </summary>
        protected override void ExecuteTask()
        {
            try
            {
                int result = CreateWebsite();
                switch (result)
                {
                    case 0:
                        Project.Log(Level.Info, string.Format("Website [{0}] not created, because it already exists", m_websiteName));
                        break;
                    default:
                        Project.Log(Level.Info, string.Format("Website [{0}] created", m_websiteName));
                        break;
                }
            }
            catch (Exception ex)
            {
                logger.Log(this.Project, Level.Error, string.Format("Website [{0}] not created, because an exception occurred [{1}]", m_websiteName, ex.ToString()));
                throw new BuildException("Unable to create website", ex);
            }
        }

        /// <summary>
        /// Create an IIS website.
        /// </summary>
        /// <returns>Website id</returns>
        public int CreateWebsite()
        {
            if (string.IsNullOrEmpty(m_appPool)) { throw new Exception("Property [AppPool] can't be null or empty"); }
            if (string.IsNullOrEmpty(m_binding)) { throw new Exception("Parameter [Binding] can't be null or empty"); }
            if (string.IsNullOrEmpty(m_server)) { throw new Exception("Parameter [Server] can't be null or empty"); }
            if (string.IsNullOrEmpty(m_webFolder)) { throw new Exception("Parameter [WebFolder] can't be null or empty"); }
            if (string.IsNullOrEmpty(m_websiteName)) { throw new Exception("Parameter [WebsiteName] can't be null or empty"); }
            int websiteId = 1;

            using (DirectoryEntry w3svc = new DirectoryEntry(string.Format("IIS://{0}/w3svc", m_server)))
            {
                w3svc.RefreshCache();

                websiteId = this.GetWebSiteId(m_server, m_websiteName);

                if (websiteId < 0)
                {
                    //Create a website object array
                    object[] newsite = new object[] { m_websiteName, new object[] { m_binding }, m_webFolder };

                    //invoke IIsWebService.CreateNewSite
                    websiteId = (int)w3svc.Invoke("CreateNewSite", newsite);

                    // get newly created object
                    using (DirectoryEntry website = new DirectoryEntry(string.Format("IIS://{0}/w3svc/{1}", m_server, websiteId)))
                    {
                        website.RefreshCache();
                        // upgrade Scriptmap to 2.0.50272 (ASP.NET version)
                        for (int prop = 0; prop < website.Properties["ScriptMaps"].Count; prop++)
                        {
                            website.Properties["ScriptMaps"][prop] = website.Properties["ScriptMaps"][prop].ToString().Replace("v1.1.4322", "v2.0.50727");
                        }

                        // set application pool
                        website.Properties["AppPoolId"][0] = m_appPool;

                        // set Execute Permissions (0 - Scripts only, 512 - Script Only, 516 Script and Execute
                        website.Properties["AccessFlags"][0] = 512;

                        // set ReadAccess
                        website.Properties["AccessRead"][0] = true;

                        //// set Application Name (friendly name)
                        //// doesn't seem to work...
                        //w3svc.Properties["AppFriendlyName"][0] = siteDescription;

                        // Set Directory Security (Integrated Windows Authentication) 1 = AuthAnonymous, 2 = AuthBasic, 4 =AuthNTLM, 16 = AuthMD5(Digest), 64 = AuthPassport
                        website.Properties["AuthFlags"][0] = 5;

                        // save properties to IIS-metabase
                        website.CommitChanges();

                        // start website
                        website.Invoke("Start", null);
                    }
                }
                else
                {
                    websiteId = 0;
                }
            }

            return websiteId;
        }

        /// <summary>
        /// Get website id on websitename
        /// </summary>
        /// <param name="serverName">Name of the IIS server e.g. localhost</param>
        /// <param name="websiteName">Name of the website e.g. test</param>
        /// <returns>
        /// Less the 0, site does not exist
        /// Id of the existing site
        /// </returns>
        public int GetWebSiteId(string serverName, string websiteName)
        {
            if (string.IsNullOrEmpty(serverName)) { throw new Exception("Parameter [serverName] can't be null or empty"); }
            if (string.IsNullOrEmpty(websiteName)) { throw new Exception("Parameter [websiteName] can't be null or empty"); }
            int result = -1;

            using (DirectoryEntry w3svc = new DirectoryEntry(string.Format("IIS://{0}/w3svc", serverName)))
            {
                w3svc.RefreshCache();

                foreach (DirectoryEntry site in w3svc.Children)
                {
                    using (site)
                    {
                        site.RefreshCache();

                        if (site.Properties["ServerComment"] != null)
                        {
                            if (site.Properties["ServerComment"].Value != null)
                            {
                                if (site.Properties["ServerComment"].Value.ToString().Equals(websiteName, StringComparison.OrdinalIgnoreCase))
                                {
                                    result = int.Parse(site.Name);
                                }
                            }
                        }
                    }
                }
            }

            return result;
        }
    }
}

Dump IIS info with C#

To call the function use:  this.DumpIISInfo(new DirectoryEntry("IIS://localhost/w3svc"));
/// <summary>
        /// Dump iis info
        /// </summary>
        /// <returns>
        /// 0 = All IIS information is written to the console
        /// 1 = Parameter entry was null
        /// </returns>
        public int DumpIISInfo(DirectoryEntry entry)
        {
            if (entry == null) { return 1; }

            foreach (DirectoryEntry childEntry in entry.Children)
            {
                using (childEntry)
                {
                    Console.WriteLine(string.Format("Child name [{0}]", childEntry.Name));
                    foreach (PropertyValueCollection property in childEntry.Properties)
                    {
                        Console.WriteLine(string.Format("[{0}] [{1}] [{2}]", childEntry.Name, property.PropertyName, property.Value));
                    }
                    if (childEntry.Children != null)
                    {
                        this.DumpIISInfo(childEntry);
                    }
                }
            }

            return 0;
        }